Zero Coupon Bond Price Calculator Excel (5 Suitable Examples)

What Is Zero Coupon Bond?

When a bond does not pay coupon payments or interest and trades but rather pays a bulk amount of money at the time of maturity, it is called a Zero Coupon bond. A Zero Coupon bond is also known as a “deep discount bond” or “discount bond”. The sum of money paid at maturity is called the face value. Since a Zero Coupon bond provides no coupons or interest and trades, its transaction occurs at a discount to its face value.

Zero Coupon Bond Price Calculator Excel: 5 Examples

The following table has Bond Terms and Value columns. We will use this table for the zero coupon bond price calculator in Excel.

Example 1 – Applying a Generic Formula to Create a Zero Coupon Bond Price Calculator in Excel

The generic formula for Zero Coupon Price Calculation = (Face Value)/〖(1+r)〗^t

Steps:

Use the following formula in cell C8.

=C5/(1+C6)^C7

Formula Breakdown

(1+C6) → adds 1 with cell C6.
(1+8%) → Therefore, this becomes
- Output: 1.08
(1+C6)^C7 → is (1.08)^10
(1.08)^10 → As a result, it becomes
- Output: 2.158924997279
C5/(1+C6)^C7 → divides 20000 by 2.158924997279
20000/2.158924997279→ Hence, it becomes
- Output: $9263.87

Press Enter.

Example 2 – Zero Coupon Bond Price Calculator for Compounding Periods

The generic formula including compounding periods per year= (Face Value)/〖(1+r/n)〗^t*n

We can see the Value for Compounding Periods Per Year (n) is 3. We will use the above formula for Zero Coupon Price Calculation.

Steps:

Use the following formula in cell C9.

=C5/(1+(C6/C8))^(C7*C8)

Formula Breakdown

(C7*C8) → It multiplies cell C7 with cell C8
(10*3) → Therefore, it becomes
- Output: 30
(C6/C8) → divides cell C6 by cell C8
(8%/3) → Then, it becomes
- Output: 0.026666666667
(1+(C6/C8)) → is adding 1 with 0.026666666667
(1+0.026666666667) → As a result, this becomes
- Output: 1.026666666667
(1+(C6/C8))^(C7*C8) → is (1.026666666667)^30
(1.026666666667)^30 → Then, it becomes
- Output: 2.2033739695385
C5/(1+(C6/C8))^(C7*C8) → is dividing C5 by 2.2033739695385.
20000/2.2033739695385 → becomes
- Output: $9081.26

Press Enter.

Example 3 – Using the PV Function to Create a Zero Coupon Bond Price Calculator in Excel

Steps:

Use the following formula in cell C8.

=PV(C6,C7,0,C5)

Formula Breakdown

PV(C6,C7,0,C5) → The PV function calculates the present value of a loan or investment based on a constant interest rate.
C6 is the rate, which is referred to as Yield to Maturity (YTM)
C7 is the nper, which is the total number of payment periods
0 is the pmt, that is the payment made on each period. For zero coupon bond, as there is no periodic payment, pmt is 0
C5 is the fv, which is the Future Value
PV(8%,10,0,20000) → Therefore, this becomes
- Output: -$9263.87, here the negative sign means outgoing cash flow.

Press Enter.

Example 4 – Using the PV Function to Make Zero Coupon Bond Price Calculator for Compounding Periods

We can see the Value of Compounding Periods Per Year (n) is 3.

Steps:

Use the following formula in cell C9.

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

Formula Breakdown

PV(C6/C8,C7*C8,0,C5) → The PV function calculates the present value of a loan or investment based on a constant interest rate.
C6/C8 is the rate, which is referred to as Yield to Maturity (YTM)
8%/3 → Therefore, it becomes
- Output: 0.026666666667
C7*C8 is the nper, which is the total number of payment periods
10*3 → As a result, becomes
- Output: 30
0 is the pmt, that is the payment made on each period. For zero coupon bond, as there is no periodic payment, pmt is 0
C5 is the fv, which is the Future Value
PV(0.026666666667,30,0,20000) → becomes
- Output: -$9081.26, here the negative sign means outgoing cash flow.

Press Enter.

Example 5 – Using the RATE Function to Calculate the Interest Rate for a Zero Coupon Bond

We will use the RATE function to calculate the Yield to Maturity-YTM (r), which is the interest rate (r) for a zero coupon bond.

Steps:

Use the following function in cell C8.

=RATE(C7,0,C6,C5)

Formula Breakdown

RATE(C7,0,C6,C5) → the RATE function returns the interest rate per period of an annuity.
C7 is the npr, which is the total number of payment periods

0 is the pmt, that is the payment made on each period. For zero coupon bond, as there is no periodic payment, pmt is 0
C6 is pv, which is the Present Value
C5 is fv, that is the Future Value
RATE(10,0,-12000,20000) → Therefore, it becomes
- Output: 5%