UNM CS-150L Lab 8 Fall 2008
Excel: Calculating Future Value of an Annuity and Retirement Income, using the
Due: Sunday November 9 at midnight.
The focus of this lab is the calculations associated with a retirement annuity. The term annuity is
used in finance theory to refer to any terminating stream of fixed payments over a specified
period of time usually in connection with the valuation of the stream of payments, taking into
account time value of money concepts such as interest rate and future value. Annuities are
classified by payment dates. The payments (deposits) may be made weekly, monthly, quarterly,
yearly, or at any other interval of time. An ordinary annuity is an annuity whose payments are
made at the end of each period (e.g. a month, a year).
In this lab you will create spreadsheets outline retirement annuities. These annuities have two
phases: the accrual phase, in which payments are made into the annuity account and the pension
phase, in which payments are made out of the annuity. You will be making an Excel workbook
with three worksheets titled “Research” “FV”, “Accrual table”, and “Variations”. The answers
and calculations listed below must be included in your workbook. It is up to you to organize,
format, color, and highlight the information in a professional looking, and easy to read manner.
Excel has the built-in function, FV(rate, nper, pmt), for calculating the future value of an
annuity. The Excel function performs the following calculation:
Where rate is the periodic interest rate (APR divided by the number of periods per year), nper is
the total number of periods of the annuity, and pmt is the amount contributed to the annuity each
period. One serious limitation to this equation, and to the Excel FV function, is that it assumes
that the contribution amount remains consta