MAT $3330-$ Present and Accumulated Value of $]$Annuities

This excel assignment illustrates the present value and the accumulated value of annuities $-$ both immediate and

due.

Identify and keep four important inputs at the top of the page. We want the ability to change any of these

inputs and have the spreadsheet adjust accordingly. The four inputs are rate of interest, payment amount,

number of payments and immediate$/$due$.$ Put these in the upper left hand comer of your sheet and ideally

color the cells yellow identifying that these are inputs, not calculated cells.

Starting in cell $A10,$ enter the following column headings.

Cell A$10=$ Time $t$

Cell B$10=$ Payment Amount

Cell $C10=$ Present Value Factor

Cell D$10=$ Present Value of Payment

Cell E$10=$ Accumulated Value Factor

Cell F$10=$ Accumulated Value of Payment

In column $A,$ start time at $0$ in row $11$ and have it increase by $1$ in each subsequent row ending with $200$ in

row $211.$

Write a formula in column $C$ that generates the present value at time $t=0$ for a $$1$ payment at time $t.$ This

factor is dependent on time $t.$ Use the IF function so that the spreadsheet shows a factor only when there is

a payment at time $t.$ In Column $D,$ calculate the present value at time $t=0$ of each of the payments made

by multiplying the payment amount by the present value factor. Copy your formulas down to cells C$211$

and D$211.$ Be sure the cell references contain $ symbols where necessary.

Write a formula in column $E$ that generates the accumulated value at time $t=n(n$ is the input value for the

number of payments in the upper left hand part of the spreadsheet$)$ for a $$1$ payment at time $t.$ As with the

present value factor, this factor is time dependent and the spreadsheet should use the IF function to show

this factor only if a payment is made. In column $F$ calculate the accumulated value of each single payment

by multiplying the payment made at that time by the accumulated value factor. Copy your formulas down

to cells E$211$ and F$211.$

In row $9,$ put a total for cells D$11$ to D$211$ and cells F$11$ to F$211.$ Use the SUM function.

In row $8,$ use the Excel function $PV$ to calculate an annuity value. Check that the annuity value that the

spreadsheet develops is the same value that Excel generates through the PV function. The PV formula will

look something like:

interest rate, number of years, payment amount, $,$ immediate$/$due$)$

Use the IF function to get the proper parameter for immediate$/$due$.$ If the payment type$=$IMMEDIATE,

the excel parameter should equal zero otherwise the parameter equals one.

Also in row $8,$ use the Excel function FV to calculate an accumulated value. Check that the accumulated

value that the spreadsheet develops is the same value that Excel generates through the FV function. The

FV formula will look something like:

interest rate, number of years, payment amount, $,$ immediate$/$due$)$

Use the IF function to get the proper parameter for immediate$/$due$.$ If the payment type $=$ IMMEDIATE,

the excel parameter should equal zero otherwise the parameter equals one.