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
- Calculate Selling Price Per Unit in Excel
- How to Calculate Selling Price in Excel
- Calculate Variable Cost Per Unit in Excel
- How to Calculate Projected Cost in Excel
How to Calculate Bond Price in Excel: Knowledge Hub
- Calculate Bond Price from Yield in Excel
- 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
- How to Perform Floating Rate Bond Valuation in Excel
<< Go Back to Excel Formulas for Finance | Excel for Finance | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
