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 corner of your sheet and ideally

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

Starting in cell A$10,$ enter the following column headings.

Cell A$10=$ Time $t$

Cell B$10=$ Payment Amount

Cell C$10=$ Present Value Factor

Cell D$10=$ Present Value of Payment

Cell $E10=$ 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.$

Column B shows the payments in the annuity. The spreadsheet needs to interpret three of the four input

items. The three items are i$)$ the payment amount, ii$)$ the number of payments and iii$)$ whether payments

are IMMEDIATE or DUE. Cell B$11($associated with time $t=0)$ should contain the input value for

payment amount in the upper left hand corner of the spreadsheet IF the annuity type is set to DUE,

otherwise cell B$11$ should be zero. Likewise, payments should end at time $t=$ payment number if the

payment type is set to IMMEDIATE and should end at time $t=($payment number $-1)$ if payment type is

DUE. The formula will use the IF function in a nested fashion. The formula will look something like: $=$ IF

$($ annuity type $=$ DUE, IF $($time number of payments $-1$

I need to know what this would be in excel please