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.
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. This 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 in Excel
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 the annuity type.
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, period of payment, and time.
Now we will get it using 2 simple methods.
1. Calculating Present Value of Annuity with Mathematical Formula in Excel
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 Present Value of 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 Calculate Present Value of Future Cash Flows in Excel
1.2 Present Value of 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 Present Value in Excel with Different Payments
2. Using the 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 Present Value of 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 Present Value of Uneven Cash Flows in Excel
2.2 Present Value of 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 Present Value of Lump Sum in Excel (3 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.
Download Practice Workbook
Get the sample file and practice by yourself.
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.
Related Articles
- How to Calculate Future Value of Growing Annuity in Excel
- How to Calculate Future Value in Excel with Different Payments
- How to Calculate Future Value with Inflation in Excel
- How to Make a Time Value of Money Calculator in Excel (5 Ways)
- How to Calculate Future Value of Uneven Cash Flows in Excel
- Calculate NPV for Monthly Cash Flows with Formula in Excel
- How to Apply Future Value of an Annuity Formula in Excel
