You can save money for your retired life. That’s why you need to know the calculation of Annuity Payments. In this article, I will explain how to calculate Annuity Payments in Excel.

**Table of Contents**Expand

## What Is Annuity Payment?

An **Annuity** is a fixed monetary amount which you will get either annually or monthly after a certain period of time. On the other hand, you can say that an **Annuity Payment** is basically a contract between you and an insurance company for getting money in your retired period of life.

Briefly, you will pay annually or monthly a monetary amount to an insurance company and they will return you the equivalent money either annually or monthly or at once in the future.

## How to Calculate Annuity Payments in Excel: 4 Suitable Methods

Here, I will describe the** 4 **methods on how to **calculate Annuity Payments in Excel**. Also, for your better understanding, I’m going to use the following dataset. Which contains **two **columns with **Payment Details**.

### 1. Use of PMT Function to Calculate Annuity Payments in Excel

You can apply **the PMT function** to calculate the **Annual Investment** for **Annuity Payments** in Excel. **PMT** function is a built-in function in Excel. Here, I will use the** PMT** function to get the amount of annual investment that I have to pay to the insurance company for getting an **Annuity Payment **of **$200,000** in the Future. The steps are given below.

**Steps:**

- Firstly, select a different cell
**C9**where you want to**calculate**the**Annual Investment**. - Secondly, use the corresponding formula in the
**C9**cell.

`=PMT(C6,C7,0,C5)`

**Formula Breakdown**

Here, I have used the** PMT** function which calculates the payment based on an** Annuity** with a constant interest rate and regular investment.

- In this function,
**C6**denotes the**Annual Interest Rate**of**8%**. -
**C7**denotes the total payment period in terms of the year which is**10**. **0**denotes the present value.

**C5**denotes the Future Value which is**$200,000**.

- Now, press
**ENTER**to get the**Annual Investment**.

Here, the** Minus** sign denotes that you have to pay this amount to the insurance company. This means **Annual Investment** is an outgoing monetary amount. Now, I will change the format.

- At this time, you need to press the
**CTRL+1**keys to open the**Format Cells**dialog box directly.

Also, you can use the **Context Menu Bar** or the **Custom Ribbon** to go to the** Format Cells **command.

*In the case of using the***Context Menu Bar**, select the data range >>**Right-Click**on the data >> choose the**Format Cells**option.*In the case of using***Custom RIbbon**, select the data range >> from the**Home**tab >> go to the**Format**feature >> choose the**Format Cells**command.

As a result, a dialog box named** Format Cells **will appear.

- Now, from that dialog box, you have to make sure that you are on the
**Number**command. - Then, you need to go to the
**Currency**option. - At this time, choose the
**fourth**option from the**Negative numbers**option. - Finally, press
**OK.**

Finally, you will see the following result.

### 2. Applying PV Function to Calculate Annuity Payments in Excel

Here, you can apply **the PV function** to calculate the** Annuity Payments in Excel**. In addition, with the **PV** function, you can see how much investment you should invest to get an **Annuity Payment** of** $20,000** annually for **10** years with an** 8%** interest rate. The steps are given below.

**Steps:**

- Firstly, you need to select a different cell
**C9**where you want to**calculate**the**Total Investment**. - Secondly, use the corresponding formula in the
**C9**cell.

`=PV(C6,C7,C5,,0)`

- Now, press
**ENTER**to get the**Total Investment**.

**Formula Breakdown**

Here, the **PV **function will return the **Present Value** of an investment.

**C6**denotes**rate**as the annual interest rate.**C7**denotes**NPER**as the total period of time.**C5**denotes the**PMT**as**Annuity Payment.**- As the
**Future Value**is unknown so**FV**will be**Blank**. **0**denotes**Types**as the ending of the period.

**Read More: **How to Calculate Annuity Factor in Excel

### 3. Use of FV Function to Calculate Annuity Payments in Excel

You can use **the FV function** to calculate the** Annuity Payments in Excel**. The steps are given below.

**Steps:**

- Firstly, select a different cell
**C9**where you want to**calculate**the**Annuity Payment**which is the**Future Value**. - Secondly, use the corresponding formula in the
**C9**cell.

`=FV(C6,C7,C5)`

- Now, press
**ENTER**to get the**Future Value**.

**Formula Breakdown**

- Here, the
**FV**function will return a**Future Value**of the periodic investment. - Now,
**C6**denotes the**Annual Interest Rate**. - Then,
**C7**denotes the total time period as**Year**. - Finally,
**C5**denotes the monetary value that you are paying at present annually to get an**Annuity Payment**in the future.

**Read More: **How to Calculate Deferred Annuity in Excel

### 4. Employing Generic Formula to Calculate Annuity Payments

You can employ the **generic** formula to calculate how much **Investment **you should pay at present in Excel to get certain** Annuity Payments.**

**Steps:**

- Firstly, select a different cell
**C9**where you want to**calculate**the**Total Investment**. - Secondly, use the corresponding formula in the
**C9**cell.

`=C5*((1-(1+C6)^-C7)/C6)`

**Formula Breakdown**

- Here,
**C6**denotes the**Annual Interest Rate**. - Firstly, I have added
**1**with the interest rate.**Output: 1.08.**

- Secondly, the
**Power (^)**sign raises it to a fixed power which is the negative value of**C7**cell. Also, that is the time period.**Output: 0.463193488.**

- Thirdly, I have subtracted the above output from
**1**.- Output:
**0.536806512**.

- Output:
- Fourthly, I have divided the above result by
**C6**which is the**Annual Interest Rate**.- Output:
**6.710081399.**

- Output:
- Here,
**C5**denotes the monetary value which you will get annually. Which means this is the**Annuity Payment**. - Finally, I have multiplied the
**C5**cell value with the above result**.****Output: $134,201.63.**

- Now, press
**ENTER**to get the**Total Investment**.

**Read More: **How to Do Ordinary Annuity in Excel

## Calculating Interest Rate of Annuity Payments by Using RATE Function

You can use **the RATE function** to calculate the Interest Rate of Annuity Payments in Excel. The steps are given below.

**Steps:**

- Firstly, select a different cell
**C9**where you want to**calculate**the**Interest Rate**. - Secondly, use the corresponding formula in the
**C9**cell.

`=RATE(C6,0,C5,C7,0)`

**Formula Breakdown**

Here, the **RATE** function will return the annual rate in percentage of investment.

- Now,
**C6**denotes the**NPER**as the annuity period. - Then,
**0**denotes that the annual payment is unknown. - Then,
**C5**denotes the total investment which you pay today to the insurance company. - After that,
**C7**denotes the equivalent**Future value**that you will get after the annuity period. - Finally,
**0**denotes the**type**as the ending of the period.

- Finally, press
**ENTER**to get the**Interest Rate**.

## Employing NPER Function to Calculate Annuity Period

You can use the** NPER **function to calculate the **Annuity Period** of **Annuity Payments** in Excel. The steps are given below.

**Steps:**

- Firstly, select a different cell
**C9**where you want to**calculate**the**Interest Rate**. - Secondly, use the corresponding formula in the
**C9**cell.

`=NPER(C6,C7,C5,,0)`

**Formula Breakdown**

Here, the **NPER** function gives the payment period as a year.

**C6**denotes the**Annual Interest Rate**.**C7**denotes how much you are paying per period.**C5**denotes the total investment which you pay to the insurance company.- As the
**Future Value**is unknown so**FV**will be**Blank**. **0**denotes the**type**as the ending of the period.

- Finally, press
**ENTER**to get the**Annuity Period**.

## 💬 Things to Remember

- There is one more feature to the
**RATE**function which is a**guess**. Basically, the**RATE**function will do a maximum of**20**iterations. So, when it does not give a result means this function has to do more than**20**iterations. In that case, you have to give**a guess**about the**interest rate**.

## Practice Section

Now, you can practice the explained method by yourself.

**Download Practice Workbook**

You can download the practice workbook from here:

## Conclusion

I hope you found this article helpful. Here, I have explained **4 **examples to** Calculate Annuity Payments in Excel.** Please, drop comments, suggestions, or queries if you have any in the comment section below.

**Related Articles**

- How to Calculate Growing Annuity in Excel
- How to Calculate Equivalent Annual Annuity in Excel
- How to Calculate Annuity Due in Excel

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