How do you calculate a present value of annuity using Excel?A:
Calculating the present value of an annuity using Microsoft Excel is fairly straightforward if you know the interest rate, payment amount and duration of the annuity.
Fixed Vs. Variable Annuities
Before going to the trouble of entering the present value formula, make sure all the requisite variables are consistent. If the annuity pays annually, the interest rate and number of periods must also be expressed in terms of years.
For example, an annuity might pay $1,200 each year with an interest rate of 6% and a duration of 15 years. If, however, it pays $100 each month, the interest rate needs to be converted to the monthly rate by dividing by 12. Likewise, the number of terms must be multiplied by 12 to convert years to months. In this example, the present value formula for the annuity that pays $100 each month would use an interest rate of 6% / 12, or 0.5%. The number of periods would be 15 * 12, or 180.
The formula for calculating the present value of an annuity that generates identical payments at regular intervals, called a fixed annuity, is:
PV = P * ((1 - ((1 + R) ^ -N)) / R)
P represents the payment amount, R represents the interest rate and N represents the number of periods. Excel has a built-in function for calculating the present value of fixed annuities, but it is important to understand the mathematics behind the function.
Present Value in Excel
To calculate the present value of a fixed annuity in Excel, enter the interest rate, number of periods and payment amount into cells A1, A2 and A3, respectively.
In cell B1, enter the formula "=PV(A1,A2,A3)" to render the present value of this annuity.
Using the example above, input the variables for the 15-year annuity into Excel. Input the 6% interest rate into A1, the 15-year term into A2 and the $1,000 annual payment into A3.
In cell B1, enter the "=PV(A1,A2,A3)" formula to calculate a present value of $9,712.