Calculating the present value of an annuity using Microsoft Excel is a fairly straightforward exercise, as long as you know a given annuity’s interest rate, payment amount, and duration. But it’s important to stipulate that calculating this value is only feasible when dealing with fixed annuities.
This same calculation cannot be made with variable annuities, due to the simple fact that their rates of return fluctuate, usually in tandem with a stock market index or a money market index. This makes variable annuities more difficult to value accurately, and leaves investors in the untenable position of having to blindly 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, an investor would have to know the amount of money they must pay today in order to receive the stated rate of return for the duration of the annuity.
For example, if an individual wished to receive $1,000 per month for the next 15 years, and the stated annuity rate was 4%, they can use Excel to determine the cost of setting up this offering.
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.”)
Read the original article on Investopedia.