How to Calculate the Present Value of a Bond in Excel

What Is a Bond?

A bond is an investment security in which the issuer owes the holder a specific interest and principal payments on the maturity date.

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.

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

Use the PV function to calculate the present value of a bond.

2.1 For an Annual Coupon Bond

Bondholders will receive yearly interest.

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.

2.2 For a Semi-Annual Coupon Bond

The interest rate is half the annual interest rate.

Steps

Select C9.

Enter the following formula.

=PV(C7/2,C6*2,C5*C8/2,C5)

Press Enter.

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.

Method 3 – Using the PRICE Function

Use the PRICE function to calculate the present value of a bond. It returns the price of a par value ($200) of investment which pays successive interest.

The dataset includes settlement, maturity date, coupon rate, yield, redemption amount per $200 face value, and the number of coupon payments per year.