How to Calculate Coupon Payment in Excel (4 Suitable Examples)

Get FREE Advanced Excel Exercises with Solutions!

Coupons are issued against the bonds. Coupon payments are given based on the interest rate. In this article, we will discuss how to calculate coupon payment in Excel with proper illustration.


Basic Formula to Calculate Coupon Payment

The coupon payment is the amount of interest given based on the interest rate with the bond. The equation is:

Coupon payments are of two types: Fixed coupon payment and variable coupon payment. In the case of fixed coupon payment, the interest rate is fixed all the time. As a coupon payment is also the same. On the other hand, in variable coupon payments, the interest rate changes from time to time. So, coupon payment also changes.


How to Calculate Coupon Payment in Excel: 4 Suitable Examples

In this article, we will show some examples to calculate coupon payments in Excel. We have taken a dataset of some values of objects.

How to Calculate Coupon Payment in Excel


Example 1: Calculate Yearly Coupon Payment

In this example, we will show how to calculate annual or yearly coupon payments. As we are going to calculate yearly coupon payments, we will get that only one time in a single year. So, the value of terms per year is 1.

📌 Steps:

  • Now, we add a new row to represent the coupon payment.

  • Then, put the following formula on Cell C9.
=C5*(C7/C6)

Calculate Yearly Coupon Payment

  • Finally, press the Enter button to get the result.

We get yearly coupon payments successfully.

Read More: How to Calculate Loan Payment in Excel


Example 2: Determine Half-Yearly Coupon Payment

In this example, we will determine the half-yearly coupon payment. That means we will get coupons yearly 2 times.

📌 Steps:

  • Copy and paste the following formula on Cell C9.
=C5*(C7/C6)

Determine Half-Yearly Coupon Payment

Here, the terms per year are 2.

  • After that, press the Enter button.

Finally, we get the half-yearly coupon payment.

Read More: How to Calculate Monthly Mortgage Payment in Excel


Example 3: Find Quarterly Coupon Payment

In this example, we will try to find out the quarterly coupon payment. Here, we will get payment 4 times a year. The difference in this example is that the value of terms per year is 4, which means we will get coupon payments in each quarter.

📌 Steps:

  • Put the same formula used previously on Cell C9.
=C5*(C7/C6)

Find Quarterly Coupon Payment

  • Then, press the Enter button.

Read More: How to Calculate Monthly Payment with APR in Excel


Example 4: Custom Coupon Payment Calculation

In this example, we will calculate custom coupon payments, not like in previous sections yearly, half-yearly, etc.

📌 Steps:

  • Here we want to get a coupon payment 6 times a year, which means every 60 days coupon payment will be provided.

  • Put the following formula on Cell C9.
=C5*(C7/C6)

Custom Coupon Payment Calculation

  • Finally, press the Enter button to get the result.

This amount of interest will provide every 60 days interval.

Read More: How to Calculate Monthly Payment on a Loan in Excel


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

In this article, we showed 4 examples explaining how to calculate coupon payments in Excel. I hope this will satisfy your needs. Please give your suggestions in the comment box.


Related Articles


<< Go Back to Calculate Payment in Excel | Excel for Finance | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo