What Is a Coupon Rate?
The coupon rate is the rate of interest that is paid on the bond’s face value by the issuer. The coupon rate is calculated by dividing the Annual Interest Rate by the Face Value of the Bond. The result is then expressed as a percentage.
Coupon Rate=(Annual Interest Rate/Face Value of Bond)*1003 Examples to Calculate the Coupon Rate in Excel
We will use a dataset that contains the Face Value and Interest. We will use different frequencies of payments to calculate the coupon rate.
Example 1 – Determine a Coupon Rate in Excel with a Half-Yearly Interest
STEPS:
- Type 2 in Cell D5.
- Select Cell D8 and use the formula below:
=C5*D5
- Press Enter to see the result.
- Select Cell D10 and enter the formula below:
=(D8/B5)*100
- Hit Enter to see the Coupon Rate. In our case, the coupon rate is 2%.
Read More: How to Calculate Price of a Semi Annual Coupon Bond in Excel
Example 2 – Calculate the Coupon Rate with Monthly Interest in Excel
STEPS:
- Change the Face Value of the Bond in Cell B5.
- Write 12 in Cell D5.
- Select Cell D8 and type the formula:
=C5*D5- Hit Enter to see the Annual Interest Payment.
- Enter the following formula in Cell D10:
=(D8/B5)*100- Press Enter to see the coupon rate with monthly interest.
Read More: How to Calculate Face Value of a Bond in Excel
Example 3 – Coupon Rate Calculation in Excel with a Yearly Interest
STEPS:
- Select Cell D5 and type 1.
- Enter the annual interest payment formula in Cell D8 and click on the Enter key.
- Select Cell D10 and type the formula below:
=(D8/B5)*100- Pess Enter to see the desired result.
Determine the Coupon Bond in Excel
A coupon bond generally refers to the price of the bond. To calculate the coupon bond, use the formula below.
Coupon Bond = C*[1–(1+Y/n)^-n*t/Y]+[F/(1+Y/n)n*t]C = Annual Coupon Payment
Y = Yield to Maturity
F = Par Value at Maturity
t = Number of Years Until Maturity
n = Number of Payments/Year
We used the Coupon Rate to evaluate the value of the Annual Coupon Payment (C).
STEPS:
- Select Cell C10 and insert the formula:
=C9/C7*C5
- Press Enter to see the result of C.
- Select Cell C12 and enter the formula below:
=C10*((1-(1+(C6/C7))^-(C7*C8))/C6)+(C5/(1+(C6/C7))^(C7*C8))
- Hit Enter to see the result.
- C10 is the value of Annual Coupon Payment (C).
- ((1-(1+(C6/C7))^-(C7*C8))/C6) is the value of C*[1–(1+Y/n)^-n*t/Y].
- (C5/(1+(C6/C7))^(C7*C8)) is the value of [F/(1+Y/n)n*t].
Read More: How to Calculate Bond Payments in Excel
Calculate the Coupon Bond Price in Excel
STEPS:
- For a half-year coupon bond, select Cell C11 and use the formula below:
=PV(C8/2,C6,C5*C9/2,C5)- Hit Enter to see the result.
- For the price of a yearly coupon bond, select Cell C10 and insert the formula:
=PV(C7,C6,(C5*C8),C5)- Press Enter to see the result.
- To calculate the price of a zero-coupon bond, use the below formula in Cell C9.
=PV(C7,C6,0,C5)- And hit Enter to see the result.
Download the Practice Book
Related Articles
- Determine Yeild to Maturity in Excel
- Calculate Bond Price with Negative Yield in Excel
- How to Calculate Clean Price of a Bond in Excel
- How to Calculate Present Value of a Bond in Excel
- How to Calculate the Issue Price of a Bond in Excel
- Calculate Duration of a Bond 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!
