Sanjida Mehrun Guria
For a person working in the finance or banking sector, the calculation of present value is an important part. It is commonly used as the term time value of money. Microsoft Excel makes it simple. In this article, we will discuss how to apply the present value of the annuity formula in excel.
Download Workbook
Get the sample file and practice by yourself.
What Is Present Value of Annuity?
First, let us know what the word annuity means. For example, you are putting some money as a deposit to pay for something that requires payment in several installments such as a mortgage, loan etc. This interval of payments can be weekly, monthly or even yearly. Those series of payments with an equal interval is called an annuity.
Now comes the present value of the annuity. It is the value of those series of payments in the future with a specific rate of discount or rate of return and obviously within a certain period of time. The present value is also called the present discounted value as the relation between the discount rate and present value is proportional.
Ordinary Annuity vs. Annuity Due
Before getting to know the formula to calculate the present value of the annuity, the timing of the payment should be considered firsthand. It can be of two types:
- Ordinary Annuity: It determines that the cash flow is received at the end of the mentioned period. Use 0 for annuity type.
- Annuity Due: It determines that the cash flow is received at the beginning of that period. Use 1 for annuity type.
2 Simple Applications of Present Value Annuity Formula in Excel
Let us take an example to calculate the present value of the annuity formula in excel. Here the dataset represents the values of annual interest rate, number of years and period of payment and time.
Now we will get it using 2 simple methods.
1. Get Present Value of Annuity with Mathematical Formula
Before starting, here is the mathematical formula for ordinary annuity and annuity due for present value in excel.
PVA Ordinary = P * (1 – (1 + r/n)^-t*n) / (r/n)
PVA Due = P * (1 – (1 + r/n)^-t*n) * ((1 + r/n) / (r/n))
Here,
PVA = Present Value of Annuity
P = Periodic Payment
r = Interest Rate
t = Number of Years
n = Frequency of Occurrence in a year
1.1 Ordinary Annuity
Now, we will calculate the present value of the ordinary annuity.
- First, select cell C10.
- Then, insert this formula:
=C7*(1-(1+(C5/C8))^(-C6*C8))/(C5/C8)
- After that, press Enter.
- Finally, you will get the present value of the ordinary annuity.
Read More: How to Find Interest Rate in Future Value Annuity (2 Examples)
1.2 Annuity Due
Now we will apply the present value of the annuity due formula. Let’s see how it works.
- First, select cell C10.
- Next, insert this formula:
=C7*(1-(1+C5/C8)^-C6*C8)*((1+C5/C8)/(C5/C8))
- Now, press Enter.
- Finally, we have our final output.
Read More: How to Calculate Annuity Payments in Excel (4 Suitable Examples)
2. Use PV Function to Find Present Value of Annuity in Excel
In this second method, we will use the PV function to calculate the present value of the annuity. This function represents the present value of an annuity, loan or investment based on a constant interest rate. Here, we will find out both ordinary annuity and annuity due based on the present value.
2.1 Ordinary Annuity
Let us see how to calculate the present value of the ordinary annuity.
- In the beginning, select cell C11.
- Here, insert the formula below:
=PV(C5,C8,-C7,C9)
- After that, press Enter.
- Finally, we have our present value of the ordinary annuity.
Read More: How to Calculate Annuity in Excel (5 Practical Examples)
2.2 Annuity Due
The calculation process of the present value of annuity due is described below:
- First, select cell C11.
- Now, insert this formula:
=PV(C5,C8,-C7,C9)
- After this, press Enter.
- That’s it, we have our final result.
Read More: How to Calculate Annuity Factor in Excel (2 Ways)
Things to Remember
- Be sure about the units you use for specifying the rate and period of payment because the present value of the annuity is based on annual calculation.
- The payout amount will be negative (–) and the received amount will be positive (+) in the dataset.
- The Periods in a Year value cannot be blank or 0, otherwise, it will show a #DIV/0 error.
Conclusion
Therefore, I hope it was a helpful article for you on how to apply the present value of the annuity formula in excel with 2 simple methods. Let us know your suggestions in the comment box. Follow ExcelDemy for more excel blogs like this.
