A present value of annuity analysis template calculates the value today of equal payments made at regular intervals, using a specified discount rate. It helps you explore how payment size, payment timing, and the number of periods affect that value.
The free Excel workbook below also includes sections for solving an interest rate and the number of payment periods. Its calculations assume equal periodic payments and a constant rate. They do not predict market prices or guaranteed investment returns.
Download the Free Present Value of Annuity Excel Template
Download format: ZIP archive containing one Excel workbook (.xlsx). Included: Three calculation sections: present value, interest rate, and number of periods, with a shared payment-timing selector.
Preview of the annuity calculation worksheet. Select the image to view it at full size.
Extract the ZIP and open the workbook in Microsoft Excel. Save a working copy before editing. A separate Word or PDF file is not included. Review our Terms & Conditions for usage information.
What Is the Present Value of an Annuity?
For this calculation, an annuity is a series of equal payments at regular intervals. Present value discounts those future payments to a value at the start of the calculation period.
For a positive discount rate, a payment received later has a lower present value than an equal payment received sooner. Holding payment amount, number of payments, and timing constant, increasing the discount rate reduces present value.
This mathematical model is distinct from the features of a particular annuity product. Fees, taxes, changing payments, guarantees, and other contract terms are not automatically included.
Ordinary Annuity Versus Annuity Due
Ordinary annuity: Payments occur at the end of each period.
Annuity due: Payments occur at the beginning of each period.
In this workbook, enter 1 in F5 for an ordinary annuity or 2 for an annuity due. This setting controls all three calculation sections.
Set it explicitly. The original helper formula maps values other than 1 to beginning-of-period timing, so leaving the selector blank does not reliably select an ordinary annuity.
Excel’s native functions use a different convention: type 0 means end-of-period and type 1 means beginning-of-period. The workbook translates its 1/2 selector into those values.
How to Calculate Present Value in the Workbook
Choose payment timing in F5. Enter 1 or 2 as described above.
Enter Payment Per Period in F6. Use the same amount for every payment in the modeled stream.
Enter Number of Periods in F7. Use the total count of payment intervals.
Enter Interest Rate Per Period in F8. Enter a percentage such as 8%, not the number 8.
Review the result in F10. The formula uses Excel’s PV function and negates the payment input to display a positive present value for positive inputs.
The original formula displays a result only when the payment, period count, and rate are positive. It has no input for a separate terminal lump sum.
For scheduling actual receipts and payments, use the cash budget worksheet. Present value is a discounted calculation, not a cash balance.
Match the Rate to the Payment Period
Use annual rates with annual payments, monthly rates with monthly payments, and a period count in the same units. Five years of monthly payments means 60 payment periods.
If the stated rate is a nominal annual rate compounded monthly, its monthly rate is the annual rate divided by 12. If it is an effective annual rate, the equivalent monthly rate is =(1+annual_rate)^(1/12)-1. Identify the rate convention before converting it.
Keep one currency throughout. Changing a currency symbol changes display, not values. For a separate borrowing calculation, review the common loan calculator.
Checked Present Value Example
Suppose five payments of 500 occur at the end of each year and the annual discount rate is 8%. Enter F5 = 1, F6 = 500, F7 = 5, and F8 = 8%.
The ordinary-annuity formula is PV = Payment × [1 − (1 + rate)^(-periods)] / rate.
The result is approximately 1,996.36. The equivalent Excel formula is =PV(8%,5,-500,0,0). The undiscounted payment total is 2,500; present value is lower because the payments arrive over time.
For the same five payments at the beginning of each year, the annuity-due value is approximately 2,156.06. Use selector 2 or the Excel formula =PV(8%,5,-500,0,1).
These figures demonstrate the calculation, not a quoted product price or recommended return.
Use the Interest-Rate and Period Calculators
Calculate an interest rate
Enter Payment Per Period in F14, Present Value in F15, and Number of Periods in F16. The RATE formula in F18 solves for a rate per payment period, using the same timing selector.
A monthly result is a monthly rate. Do not label it as an annual rate without stating the conversion convention. RATE uses an iterative calculation and may return an error if it cannot find a solution.
Calculate the number of periods
Enter Payment Per Period in F22, Present Value in F23, and Interest Rate Per Period in F24. The NPER formula in F26 solves for the number of payment periods using the shared timing setting.
The result may be fractional. If you need a whole-payment schedule, calculate the final payment separately rather than assuming that rounding leaves all payments unchanged.
Limitations and Input Checks
Zero rate: The original positive-input checks suppress a zero-rate calculation. For present value at zero rate, equal payments total payment amount multiplied by period count; Excel also supports =PV(0,5,-500,0,0).
Changing payments: The model does not value an irregular or growing payment stream.
No terminal balance input: The workbook’s formulas omit a separate future-value amount.
Sign conventions: The workbook handles signs internally. Enter its positive inputs as labeled rather than copying signed arguments from another formula.
Formula results: Check timing, units, and representative calculations after editing or importing the file.
Pingback: Free Bond Valuation Template for Excel | BlueLayouts
Pingback: Free Excel Financial Calculator: PV, FV, Annuities & Loans