Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

Zero Coupon Bond Price Calculator Excel (5 Suitable Examples)

If you are looking for zero coupon bond price calculator in Excel, this article is for you. Here, we will demonstrate to you 5 simple, easy, and effective examples to do the task smoothly.

Download Practice Workbook


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 the time of 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.


5 Examples to Create Zero Coupon Bond Price Calculator in Excel

The following table has Bond Terms and Value columns. We will use this table for the zero coupon bond price calculator in Excel. For this, we will go through 5 easy and effective examples. Here, we used Excel 365. You can use any available Excel version.

Zero Coupon Bond Price Calculator Excel


1. Applying Generic Formula to Create Zero Coupon Bond Price Calculator in Excel

In this method, we will use the generic formula for Zero Coupon Bond Price Calculator in Excel.

We know, the generic formula for Zero Coupon Price Calculation = (Face Value)/(1+r)^t

Steps:

  • First, we will type 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
    • Explanation: Finally, $9263.87 is the Zero Coupon Bond Price.
  • After that, press ENTER.

As a result, you can see the calculated Zero Coupon Bond Price in cell C8.

Zero Coupon Bond Price Calculator Excel

Read More: How to Calculate Bond Price with Negative Yield in Excel (2 Easy Ways)


2. Zero Coupon Bond Price Calculator for Compounding Periods

In this method, we will describe the generic formula including compounding periods per year for Zero Coupon Bond Price Calculator in Excel.

We know, the generic formula including compounding periods per year= (Face Value)/(1+r/n)^t*n

In the following table, 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:

  • First of all, we will type the following formula in cell C9.
=C5/(1+(C6/C8))^(C7*C8)

Zero Coupon Bond Price Calculator Excel

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
    • Explanation: Finally, $9081.26 is the Zero Coupon Bond Price.
  • At this point, press ENTER.

Therefore, you can see the calculated Zero Coupon Bond Price in cell C9.

Read More: How to Calculate Bond Price in Excel (4 Simple Ways)


3. Using PV Function to Create Zero Coupon Bond Price Calculator in Excel

Here, we will use the PV function for Zero Coupon Bond Price Calculator in Excel.

Steps:

  • In the beginning, we will type the following formula in cell C8.
=PV(C6,C7,0,C5)

Zero Coupon Bond Price Calculator Excel

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.
    • Explanation: As a result, -$9263.87 is the Zero Coupon Bond Price.
  • Next, press ENTER.

Finally, you can see the calculated Zero Coupon Bond Price in cell C9.

Read More: How to Calculate Present Value of a Bond in Excel (3 Easy Ways)


4. Use of PV Function to Make Zero Coupon Bond Price Calculator for Compounding Period

In this method, we will show how to use the PV function when compounding periods per year are included for Zero Coupon Bond Price Calculator in Excel.

In the following table, we can see the Value of Compounding Periods Per Year (n) is 3. Now, we will describe steps for calculating zero coupon bond price.

Steps:

  • Firstly, we will type the following formula in cell C9.
=PV(C6/C8,C7*C8,0,C5)

Zero Coupon Bond Price Calculator Excel

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.
    • Explanation: As a result, -$9081.26 is the Zero Coupon Bond Price
  • Next, press ENTER.

Finally, you can see the calculated Zero Coupon Bond Price in cell C9.

Read More: How to Calculate Bond Payments in Excel (2 Easy Methods)


5. Using RATE Function to Calculate Interest Rate for Zero Coupon Bond

In this method, 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:

  • First, we type 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%
    • Explanation: Finally, 5% is the Yield to Maturity-YTM (r) for zero coupon bond price
  • Afterward, press ENTER.

As a result, we can see the Yield to Maturity-YTM (r) for zero coupon bond price in cell C8.

Zero Coupon Bond Price Calculator Excel


Practice Section

You can download the above Excel file to practice the explained methods.


Conclusion

Here, we tried to show you 5 examples of zero coupon bond price calculator in Excel. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below. Please visit our website Exceldemy to explore more.


Related Articles

Afia Kona

Afia Kona

Hello, I am Afia Aziz Kona. I graduated in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology (BUET). I have an immense interest in technical writing and content development, therefore, I am working as a content developer at Exceldemy. In my spare time, I travel, watch movies, and cook different dishes.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo