# How to Calculate Coupon Rate in Excel (3 Ideal Examples)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will learn to calculate the coupon rate in Excel. In Microsoft Excel, we can use a basic formula to determine the coupon rate easily. Today, we will discuss 3 ideal examples to explain the coupon rate. Also, we will demonstrate the process to find the coupon bond price in Excel. So, without further delay, letâ€™s start the discussion.

## What Is 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 Bond. The result is then expressed as a percentage. So, we can write the formula as below:

`Coupon Rate=(Annual Interest Rate/Face Value of Bond)*100`

## 3 Ideal Examples to Calculate Coupon Rate in Excel

To explain the examples, we will use a dataset that contains the Face Value and Interest. We will use different frequencies of payments to calculate the coupon rate. Different frequencies mean different numbers of payments per year. First, we will calculate the annual interest payment. Then, we will use it to evaluate the coupon rate.

### 1. Determine Coupon Rate in Excel with Half-Yearly Interest

In the first example, we will determine the coupon rate with half-yearly interest. Half-yearly interest means you need to pay interest 2 times in a year. Letâ€™s follow the steps below to understand the example.

STEPS:

• First of all, type 2 in Cell D5. Here, we have typed 2 in Cell D5 because you need to pay 2 times with half-yearly interest.

• Secondly, select Cell D8 and type the formula below:
`=C5*D5`

• Press Enter to see the result.

• Thirdly, select Cell D10 and enter the formula below:
`=(D8/B5)*100`

• In the end, hit Enter to see the Coupon Rate.
• In our case, the coupon rate is 2%.

### 2. Calculate Coupon Rate with Monthly Interest in Excel

In the following example, we will calculate the coupon rate with monthly interest in Excel. This is pretty much the same as the previous example but with a basic change. Monthly interest means you need to pay the interest amount each month in a year. So, the number of payments becomes 12. Letâ€™s observe the steps below to know.

STEPS:

• In the first place, we will change the Face Value of Bond in Cell B5.
• After that, write 12 in Cell D5.

• Now, select Cell D8 and type the formula:
`=C5*D5`
• Hit Enter to see the Annual Interest Payment.

• Again, type the below formula in Cell D10:
`=(D8/B5)*100`
• Finally, press Enter to see the coupon rate with monthly interest.

### 3. Coupon Rate Calculation in Excel with Yearly Interest

In the last example, we will find the coupon rate in Excel with yearly interest. In yearly interest, one must pay the interest amount only 1 time. Here, we will use the previous dataset. So, letâ€™s pay attention to the steps below to know more.

STEPS:

• In the beginning, select Cell D5 and type 1.

• In the following step, type the annual interest payment formula in Cell D8 and click on the EnterÂ key.

• Lastly, select Cell D10 and type the formula below:
`=(D8/B5)*100`
• And press Enter to see the desired result.

## Determine Coupon Bond in Excel

In Excel, we can also calculate the coupon bond using a formula. A coupon bond generally refers to the price of the bond. To calculate the coupon bond, we need to use the formula below.

`Coupon Bond = C*[1â€“(1+Y/n)^-n*t/Y]+[F/(1+Y/n)n*t]`

Here, C = Annual Coupon Payment

Y = Yield to Maturity

F = Par Value at Maturity

t = Number of Years Until Maturity

n = Number of Payments/Year

In this case, we use the Coupon Rate to evaluate the value of the Annual Coupon Payment (C).

Letâ€™s follow the steps below to know how we can find the coupon bond.

STEPS:

• First of all, select Cell C10 and type the formula:
`=C9/C7*C5`

• Press Enter to see the result of C.

• After that, select Cell C12 and type the formula below:
`=C10*((1-(1+(C6/C7))^-(C7*C8))/C6)+(C5/(1+(C6/C7))^(C7*C8))`

• Again, hit Enter to see the result.

Here,

• 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 Coupon Bond Price in Excel

We have seen the method to calculate the coupon bond in the previous. A coupon bond generally describes the current price of the bond. In this section, we will use the PV function to calculate the coupon bond price in Excel. The PV function gets the present value of an investment. Here, we will calculate the coupon bond price of zero, annual, and semi-annual coupon bonds. So, without any delay, letâ€™s follow the steps below.

STEPS:

• In the beginning, we will determine the price of a semi-annual coupon bond.
• To do so, select Cell C11 and type the formula below:
`=PV(C8/2,C6,C5*C9/2,C5)`
• Hit Enter to see the result.

• In the following step, you can see the price of the annual coupon bond.
• For that purpose, select Cell C10 and type the formula:
`=PV(C7,C6,(C5*C8),C5)`
• Press Enter to see the result.

• Last of all, to calculate the price of a zero-coupon bond, type the below formula in Cell C9.
`=PV(C7,C6,0,C5)`
• And hit Enter to see the result.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mursalin Ibne Salehin

Hi there! This is Mursalin. I am currently working as a Team Leader at ExcelDemy. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I manage and help the writers to develop quality content in Excel and VBA-related topics.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF