ExcelInsider Team
Calculating the present value of an annuity is an important part of financial evaluations. Imagine you are entitled to receive a monthly pension for a fixed amount for a certain number of years. There are a couple of options for the amounts and years. You must be intrigued to know what the value of that money in the present value would be to decide the right combo. In this article, we will learn present value of annuity formula excel.
➤ Write this formula in the first cell of the column where you are calculating annuity: =PV(C2,A2,-B2)
➤ Replace C2 with the cell with interest rate, A2 with the payment period, and C2 with the amount you are entitled to receive for each period.
➤ Autofill other cells in the column to calculate every option.
Instead of using this function, you can calculate the present value of an annuity manually as well. Depending on your situation, you might want to learn how to calculate the annuity due. In this article, we will learn all the methods you would want to know regarding the present value of the annuity. Therefore, read the whole tutorial before starting your own calculation.
What is the Present Value of Annuity?
Let’s begin with an example. If you have a job, you likely receive a certain amount of salary every month. As time value of money goes by, the value keeps decreasing every year. While you might receive the same salary from your employer, it will actually be worth less. To calculate the present value of the amount you will receive in a certain period of time, you will need to calculate the present value of annuity.
There are two types of present value of annuity.
- Ordinary Present Value of Annuity: Ordinary present value of annuity is when you receive the payment at the end of the time period. As we mentioned in the example before, you will receive the salary at the end of every month so that you can calculate the present value of annuity.
- Annuity Due: As a representative of an insurance company, you must calculate the total amount of money in the present value that you will receive from an insurance holder. That’s where the present value of annuity due comes in. It works like the ordinary present value of annuity, but the money is received at the beginning of every period instead of the end.
Calculating the Present Value of Annuity Using the PV Function
Microsoft Excel provides us with its own function to calculate the present value of annuity. The function is easy to use, has only a few parameters, and works like a charm. To test the function, we have here a dataset with 8 rows. There are different payment periods and payment periods but the same interest rate. Let’s calculate the present value of annuity using the PV function:
➤ Create a new column for calculating the present value of annuity. We are selecting the D column for this.
➤ In the D2 cell, write the following formula:
=PV(C2, A2, -B2)
➤ Go to the bottom-right of the D2 cell to find the plus (+) sign. Click and drag it down to the D9 cell to autofill.
Manually Determining the Present Value of Annuity
Instead of using the function Excel provided us with, we can calculate the present value of annuity using a mathematical formula. For this method, we will be doing the calculation all by ourselves and learning how to actually calculate the present value of annuity instead of letting Microsoft Excel do it for us. Follow the steps below:
➤ We are using the same dataset as before, so write this formula in the D2 cell to do the calculation:
=B2*(1-((1+C2)^(-A2)))/C2
Here, P is the payment amount, r is the interest rate, and n is the number of periods. In Excel, P is in the B2 cell, r is in the C2 cell, and n is in the A2 cell.
➤ After pressing Enter, Excel will calculate the present value of the annuity in the D2 cell. We can now use our mouse to drag the cell to D9 to fill the whole table.
Using the PV Function to Calculate the Present Value of Annuity Due
We already know what the present value of the annuity due is. But you might be surprised to know that the present value of annuity due can be calculated using the same PV function provided by Microsoft Excel. We will have to make a few modifications to the formula to do that. Let’s begin:
➤ We will be using the same dataset we used for other methods in this tutorial. Therefore, we need to write the formula in the D2 cell where we want the present value of annuity due to show up:
=PV(C2, A2, -B2,0,1)
The fourth parameter is the future value, which is not needed for our calculation. However, we need to put 0 here, otherwise we won’t be able to continue to the next parameter.
The fifth parameter instructs Excel to assume that the payments are made at the beginning of the month. If we put 1, it will calculate the annuity due. If we set it to 0 or don’t set the parameter, Excel will calculate the ordinary annuity.
➤ Autofill the remaining rows in the D column using your mouse.
Measuring the Present Value of Annuity Due Manually
Instead of using the PV function, we can always use manual calculation in Excel. We will be putting the entire formula on our own and calculating the result. Here are the steps to do it:
➤ Use the formula below on your Excel sheet. We will be putting it in the D2 cell because we are using the same dataset for the whole article.
=B2*(1-((1+C2)^(-A2)))/C2*(1+C2)
First of all, P is the payment amount, which is in the B2 cell. The C2 cell contains the interest rate, which is r in the original formula. Finally, A2 is the payment period, indicated by n in the parent formula.
➤ Fill up the other rows by autofill.
Frequently Asked Questions
What is the formula for annuity rate in Excel?
Use the RATE function to calculate the annuity rate in Excel. The formula should be formatted as follows:
=RATE(A1, B1, C1)
Here, A1 is the number of payments that need to be made, B1 is the payment amount, and C1 is the present value of annuity.
How to calculate PV?
To calculate the present value, use this formula:
=A1/((1+B1)^C1)
Replace A1 with the future value, B1 with the interest rate, and C1 with the number of periods.
How to calculate FV of annuity in Excel?
The calculation of the FV of annuity in Excel is quite similar to the PV of annuity. Here is how you write the formula:
=FV(A1, B1, C1)
Similar to the PV formula, replace A1 with the rate of interest, B1 with the payment periods, and C1 with the payment amount.
Why do we calculate PV?
As an investor, you will want to know if the investment you are making today will give you more money in the future or not. You compare the money you will receive in the present value with the investment amount by calculating PV, and understand the worthiness of your investment.
What is the formula for PMT?
The formula for calculating PMT in Excel is structured like the following:
=PMT(A1, B1, C1)
In this formula, A1 is the rate of interest, B1 is the number of payments, and C1 is the present value.
Wrapping Up
In this article, we learned the present value of annuity formula in Excel. Download the workbook to see the formulas in action. Leave your feedback below so that we can provide even better tutorials for you in the future. Bookmark the site for more articles like this, and we will see you in another tutorial.
