Method 1 – Applying a Conventional Formula
The present value can be calculated for a coupon bond or a zero-coupon bond. The conventional formula to calculate the present value for a coupon bond is,
The conventional formula to calculate the present value for a zero-coupon bond is
F= Face Value
C= Annual Coupon Rate
r = Yield to Maturity
n = Number of Compounding Per Year
t = Number of Years Until Maturity
1.1 For a Zero Coupon Bond
The zero-coupon bond is issued at a deep discount to its face value but doesn’t pay interest.
The dataset includes face value, Number of Years Until Maturity, Yield to Maturity, and Number of Compounding Per Year. Set the coupon rate to 0%.
To calculate the present value:
Steps
- Select C10.
- Enter the following formula in the formula box.
=C5/(1+(C8/C7))^(C7*C6)
- Press Enter.
Read More: How to Calculate Face Value of a Bond in Excel
1.2 For a Coupon Bond
There is a coupon rate and the bondholder will receive interest until the date of maturity.
To calculate the present value:
Steps
- Select C11.
- Enter the following formula in the formula box.
=C10*(1-(1+(C8 /C7))^(-C7*C6 ))/(C8/C7)+(C5/(1 + (C8/C7))^(C7*C6))
- Press Enter.
Method 2 – Using the PV Function
2.1 For an Annual Coupon Bond
To calculate the present value of the annual coupon bond:
Steps
- Select C9.
- Enter the following formula.
=PV(C7,C6,C5*C8,C5)
- Press Enter.
Read More: How to Calculate Bond Payments in Excel
2.2 For a Semi-Annual Coupon Bond
Steps
- Select C9.
- Enter the following formula.
=PV(C7/2,C6*2,C5*C8/2,C5)
- Press Enter.
Read More: How to Calculate Price of a Semi Annual Coupon Bond in Excel
2.3 For a Zero Coupon Bond
Set the coupon rate to zero.
Steps
- Select C10.
- Enter the following formula
=PV(C8,C7,0,C5)
- Press Enter.
Note
The negative value of the bond indicates the present cash flow or expenditure.
Read More: Calculate Bond Price with Negative Yield in Excel
Method 3 – Using the PRICE Function
The dataset includes settlement, maturity date, coupon rate, yield, redemption amount per $200 face value, and the number of coupon payments per year.
Steps
- Select C12.
- Enter the following formula.
=PRICE(C5,C6,C7,C8,C9,C10,C11)
- Press Enter.
Download Practice Workbook
Download the practice workbook.
Related Articles
- Calculate Clean Price of a Bond in Excel
- Calculate the Issue Price of a Bond in Excel
- Calculate Duration of a Bond in Excel
- How to Calculate Coupon Rate in Excel
<< Go Back to Bond Price Formula Excel|Excel Formulas for Finance|Excel for Finance|Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
