How To Calculate the Present Value of an Annuity in Excel
The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.
An annuity is a series of regular payments made continuously over a specified period. Calculating the present value of an annuity can determine the cash value of such payments or evaluate the accuracy of payment with respect to its expected value. If you work in finance or investments, it can be helpful to understand this concept and how to calculate it. In this article, we define the present value of an annuity as a concept and a function, explain how to calculate it in Excel and provide some examples to guide your understanding.
What is the present value of an annuity?
The present value of an annuity refers to the current total value of a person's future annuity payments. This concept is based on the time value of money, which states that a particular sum of money is worth more in the present than at a future date because the money has earning potential during the interim. If, for example, you receive $50,000 today, you could deposit it in an interest-bearing account that builds on the deposit. If you receive another $50,000 in the future, the initial sum of money is more valuable because it has generated interest.
An annuity can be an ordinary annuity or an annuity due. Ordinary annuities are payments made at the end of each period, often monthly, quarterly, semiannually or annually. A dividend from a stock with a stable payout level is an example of an ordinary annuity, as the stockholder receives a consistent amount at consistent intervals. In contrast, an annuity due refers to payments made at the beginning of each period. A common example of an annuity due is rent. For the individual receiving the payment, the annuity due is an asset, while it's a liability for the one giving it.
What is the present value function in Excel?
In Excel, the present value function, specifically known as the PV function, is a predefined financial formula that calculates the current monetary value of future payments based on the assumptions of a constant interest rate and periodic, constant payments. Users commonly apply the PV function to determine how much a loan or investment is worth today. Functions in Excel consist of specific values, known as arguments, arranged in a certain order, called the structure. In the PV function, there are five arguments, two of which are optional:
The rate refers to the interest rate per period. Imagine, for example, that you obtain a loan with an annual interest rate of 12% and monthly payments. The period is a year, amounting to 12 months. Thus, the value you'd input for the rate would be 12%/12 or 1.00%. This value is a required argument for the formula.
Nper refers to the total number of payment periods in an annuity. In the example above, imagine that it's a three-year loan. The total number of payment periods would be the number of years multiplied by the number of months per year in which you make payments—3 times 12—resulting in an nper of 36. This value, too, is required.
Pmt is the payment amount per period, expressed a negative integer in the structure. Over the course of the annuity, this value can't change. The pmt value includes both the principal payment and the interest. For example, a $10,000 loan over three years at 12% interest would amount to $332.14 per month. The pmt, therefore, would be -332.14. Pmt is the third required value of the PV function but can be substituted by fv.
Fv is "future value," which refers to the cash balance you wish to have after making the final payment. Therefore, if you wish to have $25,000 left over once you've entirely fulfilled your loan payments, you'd input $25,000 as the fv. If you've supplied a pmt value, the fv is optional. Omitting the fv defaults it to 0.
Type refers to the annuity payment type. If you're dealing with an ordinary annuity, in which the payment is due at the end of the period, you'd input 0 or omit a value altogether. If it's an annuity due, in which the payment is given at the beginning of the period, you'd input 1.
How to calculate the present value of an annuity in Excel
You can follow these steps to calculate the present value of an annuity in Excel:
1. Open a new Excel document
Begin the process of calculating the present value of an annuity by opening a new Excel worksheet. Find and double-click the Excel icon on your desktop or applications folder, or click it once on your taskbar or your operating system's search feature. If the program prompts you to select a type of document, choose "Blank workbook."
2. Gather and input your data
As mentioned, the data required to calculate the present value of an annuity in Excel are the interest rate per period, the total number of payment periods in the annuity and the payment amount per period—rate, nper and pmt. If you'd like to add the future value and the annuity type, have those at hand as well. Then create a table that displays the data in a logical way. For example, in your blank workbook, you can input the payment amount in A1, the interest rate in A2 and the number of payment periods in A3.
3. Calculate an ordinary annuity
To calculate an ordinary annuity, highlight a cell outside of the table of data you've created. In the example above, the bottommost entry is payment periods in A3, so you'd highlight A4. Then, minus the quotation marks, type "=pv" followed immediately by an open parenthesis. Holding down "Ctrl" on your keyboard, select the cells for your data in the following order:
Finally, input a close parenthesis and hit "Enter" on your keyboard. The resulting figure in the cells is the present value of the ordinary annuity. Alternatively, you can manually input the individual figures in the above-mentioned order.
4. Calculate an annuity due
To calculate an annuity due, it's necessary to specify the fv and type values in addition to the three required arguments. To add them to the formula, proceed with the instructions as outlined in the third step but stop short of inputting a closed parenthesis. Instead, after the pmt value, input a comma, the number 0 for the fv, another comma and then the number 1 to indicate an annuity due. Then input the close parenthesis and hit "Enter" on your keyboard. Again, you can elect to input the figures manually instead.
Examples of the present value of an annuity of Excel
Consider these examples to help you understand how to calculate the present value of an annuity in Excel:
This example illustrates how to calculate the present value of an ordinary annuity:
An annuity has an interest rate of 5% over 12 years. The payment is $1,000 at the end of every year. A user inputs this data in an Excel table in this order:
*Cell A1: They input the payment amount in A1, which is $1,000.*
*Cell A2: They place the interest rate in A1, which is 5%.*
*Cell A3: In the third cell, they input the total number of payment periods. Because the payments are annual over 12 years, there are a total of 12 payment periods.*
To calculate the present value of the annuity in Excel, the user would select cell A4 and type "=fv" followed by an open parenthesis. Then, holding down "Ctrl" on the keyboard, they'd select A2, A3 and A1, respectively. Adding a close parenthesis and hitting "Enter" reveals a present value of $8,863.25. Alternatively, the user could have added the arguments manually, to read "=fv(.05,48,1000)" minus the quotation marks.
This example relates to the present value of an annuity due:
An annuity with an interest rate of 7% pays $15,000 at the end of every year for 20 years. A user creates an Excel workbook and inputs the relevant data in the same way as above—payment amount, interest rate and total payment periods, respectively. Thus, cell A1 contains the value $15,000, cell A2 contains the value 7% and cell A2 contains the value 20. To calculate the present value, they select a cell outside of the data table and input "=pv(A2, A3, A1,0,1)"—including the arguments for fv and type. Hitting "Enter" reveals a present value of $158,910.21.
Please note that none of the companies mentioned in this article are affiliated with Indeed.
Explore more articles
- 18 Identity Access Management Tools for Organizations
- FAQ: What Is Managerial Entrenchment?
- Steps for Recruiting Quality Candidates To Startups
- What Is a Financial Plan? (Plus How To Make One in 6 Steps)
- 21 Tools That Welders Use on the Job
- FAQ: What Is an MBA in Health Care Management? (Plus Salary)
- How To Use the FREQUENCY Function in Excel in 4 Simple Steps
- What Is Digital Media Planning? (With Tips)
- Topic Clusters: What They Are and How To Use Them Effectively
- How To Write a Letter of Recommendation for an Internship in 9 Steps
- What Is a Point of Contact? A Definitive Guide
- The Importance of Long-term Career Goals: A Definitive Guide