How Is the FV of an Annuity Calculated in Excel?
You might be considering an annuity as part of your retirement portfolio. You understand the basics behind this financial tool, and know that you are making an upfront investment in exchange for future payments. Depending on the type of annuity you select, you can choose a lump-sum payoff or receive payments on an annual, monthly or quarterly basis. However, you might be interested in determining the future value, or FV, of the annuity. Microsoft Excel can simplify this calculation.
Other People Are Reading
-
Collect Information
-
You will need to input certain information to calculate the future value of an annuity. Write down the interest rate that applies to the annuity, the total number of payments you will receive, how much each payment will be and the present value of the annuity. The present value is the amount you plan to invest in the annuity. If you are opting for periodic payments, you should note whether payments will be due at the beginning or the end of each period.
Enter Formula in Excel
-
Open a new or existing Microsoft Excel worksheet. In words, the formula is:
=fv(annual interest rate/12, number of payments, amount of each payment, present value, timing of payment)
The amount of each payment and the present value of the annuity should be entered as negative numbers; and if either number exceeds $999, do not enter the comma separator. The "12" in the equation is due to monthly compounding.
-
Example
-
As an example, suppose you invest $500 in an annuity offering a 6 percent annual interest rate. The annuity will pay you $200 on the first day of each of 10 periods. The last number in the formula should be a 1 if payments are due at the first of each period and 0 is due at the end. If you do not choose, the formula will assume that the payments are due at the end of each period. You would enter the following into Excel:
=fv(6%/12,10,-200,-500,1)
Excel should return a future value of $2,581.40 for this annuity.
Default Values
-
You can omit the amount for each payment or the present value of the annuity from the equation, but you cannot omit both. If you do not include the present value, the formula assumes that it is $0.
-
References
Resources
- Photo Credit Photodisc/Photodisc/Getty Images
