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.
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.
- Fourthly, I have divided the above result by C6 which is the Annual Interest Rate.
- Output: 6.710081399.
- 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
Get FREE Advanced Excel Exercises with Solutions!
