Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Calculate Bond Payments in Excel (2 Easy Methods)

Looking for ways to know how to calculate bond payments in Excel? Then, this is the right place for you. Here, you will find 2 different step-by-step explained ways to calculate bond payments in Excel.


Download Practice Workbook


What Is Bond Payment?

The interest paid by someone who issued a bond from a bondholder at each period of payment until the the bond matures is called a Bond Payment.

Depending on the negotiated time, the payment plan may be quarterly, semiannually, or yearly.


2 Ways to Calculate Bond Payments in Excel

We can calculate bond payments in 2 different ways in Excel. Here, you will find those step-by-step methods.


1. Using PMT Function to Calculate Bond Payments Per Month in Excel

We can use the PMT function to calculate bond payments per month in Excel.

Here, we have a dataset containing the data of Bond Amount, Annual Interest, and Period of Bonds (Years). Now, we will show you how to calculate Bond Payments using this dataset.

Using PMT Function to Calculate Bond Payments Per Month in Excel

Follow the steps to do it on your own.

Steps:

  • First, select Cell C8.
  • Then, insert the following formula.
=PMT(C6/12,C7*12,-C5)

Here, in the PMT function, we divided the value of Cell C6 by 12 to set it as the rate, multiplied Cell C7 by 12, and used it as nper. Finally, we used the negative value of Cell C5 as pv which is the present value.

  • After that, press ENTER to get the value of the Bond Payment Per Month.

Using PMT Function to Calculate Bond Payments Per Month in Excel

  • Next, select Cell C9.
  • Then, insert the following formula.
=C8*12*C7

Here, we multiplied Cell C8 by 12 and Cell C7 to get the value of the Bond Payment.

  • Now, press ENTER to get the value of the Bond Payment.

Using PMT Function to Calculate Bond Payments Per Month in Excel

  • Then, select the Cell C10.
  • Next, insert the following formula.
=C9-C5

  • Finally, press ENTER to get the value of Total Interest.

Using PMT Function to Calculate Bond Payments Per Month in Excel

Read More: How to Calculate Bond Price with Negative Yield in Excel (2 Easy Ways)


Similar Readings


2. Use of Generic Formula to Calculate Bond Payments in Excel

In the second method, we will show you how to calculate bond payment in Excel Manually.

Here, we have a dataset containing the data of Bond Amount, Annual Bond Rate, and Number of Payments Per Year. Now, we will calculate the Bond Payment of this dataset Manually.

Manually Calculating Bond Payments in Excel

Steps:

  • First, select Cell C8.
  • Then, insert the following formula.
=C5*(C6/C7)

Here, we first divided Cell C6 by Cell C7, then multiplied it by Cell C5 to calculate the Bond Payment.

  • Finally, press ENTER to get the value of the Bond Payment.

Manually Calculating Bond Payments in Excel


Things to Remember

  • Here, in the PMT function, we divided the Annual Interest rate by 12 for rate and multiplied the Period of Bonds by 12 for nper as we are calculating the Bond Payment Per Month. On the other hand, for Annual Bond Payment, you can use the Annual Interest Rate and Period of Bonds directly without dividing or multiplying by 12 respectively.
  • Additionally, the PMT function automatically returns a negative value as it represents payments being made from bond issuer to bondholder. So, here we used the negative value of pv to avoid the negative value.

Practice Section

You will get a dataset like the image given below in this article to practice on your own.

Practice Section


Conclusion

So, in this article, you will find 2 ways to calculate bond payments in Excel. Use any of these ways to accomplish the result in this regard. Hope you find this article helpful and informative. Feel free to comment if something seems difficult to understand. Let us know any other approaches which we might have missed here. And, visit ExcelDemy for many more articles like this. Thank you!


Related Articles

Arin Islam

Arin Islam

Hello, I'm Arin. I graduated from Khulna University of Engineering and Technology (KUET) from the Department of Civil Engineering. I am passionate about learning new things and increasing my data analysis knowledge as well as critical thinking. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo