## 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)*100`

## 3 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**