The chart below shows basic bond particulars. We will look at how to calculate the bond price.
Method 1 – Using Coupon Bond Price Formula
Steps:
- Enter the following formula in cell C11:
=C10*(1-(1+(C8 /C7))^(-C7*C6 ))/(C8/C7)+(C5/(1 + (C8/C7))^(C7*C6)) 
- Press ENTER to display the Coupon Bond Price.
Zero-Coupon Bond Price Calculation
- To find the zero-coupon bond price, enter the following formula in cell C11:
=(C5/(1 + (C8/C7))^(C7*C6)) 
- Press ENTER to display the zero-coupon bond price.
Read More: How to Calculate Coupon Rate in Excel (3 Ideal Examples)
Method 2 – Using Excel PV Function
Steps:
Zero-Coupon Bond
- Enter the following formula in cell C10:
In the formula, rate = C8, nper= C7, pmt =0, [fv] =C5.
Annual Coupon Bond
- Enter the following formula in cell F10:
In the formula, rate = F8, nper= F7, pmt = F5*F9, [fv] =F5.
Semi-Annual Coupon Bond
- Enter the following formula in cell K10:
=PV(K8/2,K7,K5*K9/2,K5)In the formula, rate = K8/2 (as it’s a semi-annual bond price), nper= K7, pmt = K5*K9/2, [fv] =K5.
- After entering the respective formulas, you will see different bond prices, as depicted in the screenshot below.
Read More: Zero Coupon Bond Price Calculator Excel (5 Suitable Examples)
Method 3 – Calculating Dirty Bond Price
The dataset below shows Bond Prices: Annual or Semi-Annual Coupon Bond prices.
Steps:
- Enter the Accrued Interest formula in a blank cell (i.e., F9).
=(F8/2*F5)*(F6/F7)
- Press ENTER.
- Add the Clean Bond Price and Accrued Interest to get the Dirty Bond price. Enter the following formula into a blank cell (i.e. O9):
=O5+O6
- You will see the Dirty Bond Price.
Method 4 – Using the Excel PRICE Function
Steps:
- Enter the following formula in any blank cell (i.e., C12).
=PRICE(C5,C6,C7,C8,C9,2,0) 
- Press ENTER to display the Bond Price as depicted in the image below.
Download the Excel Workbook
Practise Bond Price calculation with the attached Dataset.
Related Articles
How to Calculate Bond Price in Excel: Knowledge Hub
- How to Calculate Price of a Semi Annual Coupon Bond in Excel
- How to Make a Yield to Maturity Calculator in Excel
- How to Calculate Coupon Rate in Excel
- How to Calculate Bond Payments in Excel
- How to Calculate Present Value of a Bond in Excel
- How to Calculate Clean Price of a Bond in Excel
- How to Calculate Bond Price with Negative Yield in Excel
- How to Calculate Face Value of a Bond in Excel
- How to Calculate Duration of a Bond in Excel
- How to Calculate the Issue Price of a Bond in Excel
<< Go Back to Excel Formulas for Finance | Excel for Finance | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
