Nazmul Hossain Shovon
In this tutorial, I am going to show you 5 practical examples of how to calculate annuity in excel. Finding out the various aspects of an annuity is a fairly simple task if you know the annuity’s interest rate, total amount, and duration period. But it is important to mention that calculating this value is only feasible when you are dealing with fixed annuities.
Download Practice Workbook
You can download the practice workbook from here.
What is Annuity?
An annuity is a contract between two parties where one party invest some amount at the start and will get an annual payment for a certain period of time from the other party. There are many variations of the annuity calculator formula. One basic type is as follows:
P = C * [(1 – (1 + r)^-n) / r]
Where,
P= Present value of the annuity
C= Future cash flow stream
r= Interest rate and
n= Number of periods which can be months or years.
5 Practical Examples of How to Calculate Annuity in Excel
In the following examples, we will be mainly working with 4 parameters to calculate annuity in excel. These are periodic annuity, number of periods, interest rate, and present or future value of total money. The monetary values are inserted in dollars. Also, we have taken months as unit time periods. For the interest rate, we have used the percentage format which you can find in the Number section under the tab Home.
1. Calculating Annuity Using PV Function
The PV function in excel is a financial function that can calculate the present value of an investment. In this method, we will try to calculate the present amount that we need to deposit to get a fixed annuity in excel for the next 10 months.
Steps:
- First, enter the required data as shown in the image below.
- Next, double-click on cell C8 and enter the following formula:
=PV(C7,C6,C5)
- Then, press Enter.
- As a result, you will get the amount of investment needed at present for the required future annuity.
Read More: How to Apply Present Value of Annuity Formula in Excel
2. Applying PMT Function
The PMT function in excel returns the periodic payment or annuity for a current loan. For this, we need to know a few parameters like time periods, interest rate, etc to calculate annuity in excel using this function. Let us see how we can do this.
Steps:
- To begin with, enter the Loan Amount in cell C5.
- Similarly, enter the time period and the interest rate in cells C6 and C7Â respectively.
- Now, click on cell C8 and type in the following formula:
=PMT(C7,C6,C5)
- Then, press Enter key, and excel will calculate the required annuity in dollars.
Read More: How to Calculate Equivalent Annual Annuity in Excel (2 Examples)
3. Using FV Function
If we want to calculate the future value of an investment then the FV function in excel can help. This function assumes constant annuity in excel and constant interest rates. Follow the steps below to use this function.
Steps:
- First of all, enter the monthly deposit amount in dollars in cell C5.
- Then, type in the time periods and interest rate in cells C6 and C7.
- Next, double-click on cell C8 and enter the following formula:
=FV(C7,C6,C5)
- Then, press Enter or click on any empty cell.
- Consequently, you will get the future amount that will accumulate according to the annuity.
Read More: How to Apply Future Value of an Annuity Formula in Excel
4. Finding Annuity Period Using NPER Function
The NPER function is one of the most used excel financial functions. The function can calculate the time period required to pay off a loan at a fixed annuity.
Steps:
- To begin this method, insert suitable data in the proper cells as shown in the following image.
- Now, click on cell C8 and insert the following formula.
=NPER(C7/12,C6,-C5)- Note that, we have a negative sign in the third parameter. This is to denote that we are lending the money to the other party.
- Also, we have divided the first parameter by 12 to convert the interest rate to monthly.
- Finally, press Enter and the formula will calculate the time periods required in months.
Read More: How to Calculate Annuity Factor in Excel (2 Ways)
5. Utilizing RATE Function
The RATE function is used to calculate the rate charged on a loan at a constant annuity in excel. It can also find out the rate of return needed to cover a certain amount of an investment over a given period. Let us see this function in action.
Steps:
- At first, navigate to cells C5, C6, and C7 and enter the appropriate data in each one of them.
- Next, click 2 times on cell C8 and type in the below formula:
=-RATE(C7,-C6,C5)- Note here that there is a negative sign before the second parameter of this formula. This is because we are giving away money as a monthly deposit.
- Finally, press the Enter key and excel will calculate the required interest rate.
Read More: How to Find Interest Rate in Future Value Annuity (2 Examples)
Conclusion
I really hope that you found the methods that I showed in this tutorial on how to calculate annuity in excel to be useful. I would suggest downloading the practice workbook and experimenting with different values for the required parameters. Lastly, to learn more excel techniques, follow our ExcelDemy website. If you have any queries, please let me know in the comments.
