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

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

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

- Select the cell
**C10**. - Insert the following formula.

`=C9-C5`

- Press
**Enter**to get the value of Total Interest.

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

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

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

## Download the Practice Workbook

## Related Articles

- Determine Yeild to Maturity in Excel
- How to Calculate Clean Price of a Bond in Excel
- Calculate Face Value of a Bond in Excel
- How to Calculate Coupon Rate in Excel
- Calculate Bond Price with Negative Yield in Excel
- Calculate Duration of a Bond in Excel
- How to Calculate Price of a Semi Annual Coupon Bond in Excel

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