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.

**Table of Contents**hide

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

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

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

- Then, select the Cell
**C10**. - Next, insert the following formula.

`=C9-C5`

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

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

**Similar Readings**

**How to Calculate Clean Price of a Bond in Excel (3 Easy Ways)****Zero Coupon Bond Price Calculator Excel (5 Suitable Examples)****How to Calculate Bond Price in Excel (4 Simple Ways)**

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

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

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

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