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

Method 1 – Using the PMT Function to Calculate Bond Payments Per Month in Excel

We have a dataset containing the data on Bond Amount, Annual Interest, and Period of Bonds (Years). We’ll calculate Bond Payments using this dataset.

Using PMT Function to Calculate Bond Payments Per Month in Excel

Steps:

  • Select Cell C8.
  • Insert the following formula.
=PMT(C6/12,C7*12,-C5)

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. FWe used the negative value of Cell C5 as pv which is the present value.

  • Press Enter to get the value of the Bond Payment Per Month.

Using PMT Function to Calculate Bond Payments Per Month in Excel

  • Select cell C9.
  • Insert the following formula.
=C8*12*C7

We multiplied Cell C8 by 12 and Cell C7 to get the value of the Bond Payment.

  • Press Enter to get the value of the Bond Payment.

Using PMT Function to Calculate Bond Payments Per Month in Excel

  • Select the cell C10.
  • Insert the following formula.
=C9-C5

  • 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 Present Value of a Bond in Excel


Method 2 – Use a Generic Formula to Calculate Bond Payments in Excel

We have a dataset containing the data of Bond Amount, Annual Bond Rate, and Number of Payments Per Year.

Manually Calculating Bond Payments in Excel

Steps:

  • Select the cell C8.
  • 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.

  • Press Enter to get the value of the Bond Payment.

Manually Calculating Bond Payments in Excel

Read More: Calculate the Issue Price of a Bond in Excel


Things to Remember

  • 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. For an Annual Bond Payment, you can use the Annual Interest Rate and Period of Bonds directly without dividing or multiplying by 12 respectively.
  • The PMT function automatically returns a negative value as it represents payments being made from the bond issuer to the bondholder. 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 the download file to practice on your own.

Practice Section


Download the Practice Workbook


Related Articles


<< Go Back to Bond Price Formula Excel|Excel Formulas for Finance|Excel for Finance|Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Arin Islam
Arin Islam

Anowara Islam Arin, a graduate of Civil Engineering from Khulna University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Arin exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Besides, she is also interested in Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo