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

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.


Download Practice Book

Download the practice book here and exercise it to test your skills.


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 Value. 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.

Determine Coupon Rate in Excel with Half-Yearly Interest

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

Determine Coupon Rate in Excel with Half-Yearly Interest

  • Press Enter to see the result.

Determine Coupon Rate in Excel with Half-Yearly Interest

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

Determine Coupon Rate in Excel with Half-Yearly Interest

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

Determine Coupon Rate in Excel with Half-Yearly Interest


Similar Readings


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.

Calculate Coupon Rate with Monthly Interest in Excel

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

Calculate Coupon Rate with Monthly Interest in Excel

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

Calculate Coupon Rate with Monthly Interest in Excel


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.

Coupon Rate Calculation in Excel with Yearly Interest

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

Coupon Rate Calculation in Excel with Yearly Interest

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

Coupon Rate Calculation in Excel with Yearly Interest


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).

Determine Coupon Bond in Excel

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

Determine Coupon Bond in Excel

  • Press Enter to see the result of C.

Determine Coupon Bond in Excel

  • 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].

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.


Conclusion

In this article, we have demonstrated 3 examples of ‘Calculate Coupon Rate in Excel’. I hope this article will help you to perform your tasks easily. Moreover, we have also discussed the method to calculate the price of a coupon bond. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Visit the ExcelDemy website for more articles like this. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.


Related Articles

Mursalin

Mursalin

Hi there! This is Mursalin. I am an Excel and VBA content developer as well as an electrical and electronics engineer. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo