Calculating the present value of an annuity using Microsoft Excel is fairly straightforward. However, you have to know the annuity’s terms: its interest rate, payment amount and duration. Also, the assumption here is that you’re dealing with a fixed annuity. Variable annuities offer a rate of return that fluctuates, usually in tandem with some stock market or money market index; this makes it difficult to value, as you’d have to guess at future rates.
Pricing a Fixed Annuity in Excel
The price of a fixed annuity is the present value of all future cash flows. In other words, what is the amount we must pay today in order to receive the stated rate of return for the duration of the annuity? For example, if we wanted to receive $1,000 per month for the next 15 years, and the stated annuity rate was 4%, what would this cost us?
Note: this calculation does not account for the income taxes due on the annuity payouts. (If the chart is hard to read, please right-click and choose “view image.”)