Question

FUTURE VALUE ANNUAL DEPOSIT INTEREST RATE/GROWTH RA 4.00% ASSUMES DEPOSIT AT BEGINNING OF TIME-0 Compolunding Period TIME (YRS)DEPOSITEND OF YR FORMULATEXT SINGLE FORMULA FORMULATEXT END OF YR $43,167.12FV(SB$4,A27,-$B$3,,$B$6) 0 $1,500.00 $ 1,560.00 B8 (1+SBS4)AB6 1 $1,500.00 3,182.40(C8+B9)(1+SB$4)ASB$6 2 $1,500.00 $ 4,869.70 (C94B10)1+SB$4)n$B$ N THIS CASE THERE IS A DEPOSITY OF $1,500 EVERY YEAR. IN THE 3 $1,500.00 6,624.48 (C104811) (1 SB$4)ASB$6FV FUNCTION, WE ENTER THIS AS A NEGATIVE PAYMENT INSTEAD OF PV. 4 $1,500.00 $ 8,449.46 11+812)"(1+5B54)^SB$6 5 $1,500.00 10,347.44 (C12+B13) (1+$B$4)A$B$6 6 $1,500.00 12,321.34 (C13+B14) (1+5B$A) B$6 7 $1,500.00 14,374.19 (C14+815)1+BS4)B6 MADE OVER A SPECIFIC PERIOD OF TIME. "REGULAR ANNUITIES" MAKE 8 $1,500.00 $ 16,509.16 =(C15+816)"(1+5B$4)n$B$6 PAYMENTS AT THE END OF THEPERIOD, WHILE" ANNUITY DUE"IS AN ANNUITY 9 $1,500.00 $ 18,72953 C16+817)"(1+$8S4)n$B$6 wITH PAYMENTS AT THE BEGINNING OF EACH PERIOD. 10 $1,500.00 21,038.71 (C17+B18)*(1+B$4)AŞB$6 $3,182.40 FV(SB$A,A10,-B9,1 $4,869.70FV(SB$4,A11,-B10,,1) $6,624.48 FV(SB$4,A12,-B11,1) ALSO NOTE THAT INTERST WAS COMPOUNDED FROM THE BEGINNING OF TIME O RATHER THAN AT THE END OF YEAR 8,449.46FV(SB$4,A13,-B12,,1) $10,347.44 FV(SB$4,A14, B13,,1) THIS IS LIKE AN ANNUITY WHICH IS A SERIES OF EQUAL, PERIODIC PAYMENTS =FV($B$4,A16,-B15,I) $16,509-16zfy($B$4,A17,-B16,,1) $18,729.53 =FV($B$4,A18,-131 7.1) $21,038.71FV(SB$4,A19,-B18,1) $23,440.26 -FVISB$4,A20,-B19,1) $25,937.87 -FV(ŞB$4,A21,-B20,,1) $28,535.38 SB$4,A22,-B21,,1] $31,236.80 F(SB$4,A23,-B22,,1) $34,046.27 FVISBS4,A24,-B23,1) S36 968.12-RİSBS4A25,B241) $40,006.84 FV(SB$4,A26,-B25,,1) $43,167.12 -FVISBS4.A27, B26,1) $14,374.19 1,500.00 12 $1,500.00 13 $1,500.00 14 $1,500.00 15 $1,500.00 16 $1,500.00 17 1,500.00 18 $1500.00 11 23,440.26 (C18+B19)(1+BS4)AŞB$6 25,937.87 (C19+B20) (1+B$4)AŞB$6 $ 28,535.38 ニ(C20+B21)"(1+$8$4)ASB$6 $ 31,236.80 (C21+822)"(1+$BS4)л$BS6 NOTE THAT IN THE FV FUNCTION THE PAYMENT WAS ENTERED AS A NEGATIVE NUMBER SO THAT THE OUTPUT IS POSITIVE. 34,046.27 36,968 12 40,006.84 -(C24+825) (1+SB$4)ASB$6 IN EXCEL: PV, PMT, IPMT, PPMT (C22+B23) (1+SB$A)ASB$6 IF YOU INDICATE A POSITIVE PAYMENT, THE OUTPUT WILL BE NEGATIVE $ -C23+824)" 1+$8$4 ^SB$6 THIS PECULIARITY OF THE FV FUNCTION IS SHARED WİTH OTHER FUNCTIONS $ 43,167.12 (C25+B26)(1SBS4)ASB$6 $43,167.12 43167.11786 5557.13416 ...FV2 FV3 PV1 PV2 PV3 FUNDING FUNDING 2 FUNDING3

Answer 1

a.

Column B, that shows time, goes on till 119 (120 months). but the formula remains the same as all other entries. What we are trying to do is to calculate the cumulative value of deposits along with the earned interest. so, the very first entry i.e. period 0 takes in a deposit of $300 and simply calculates the end of month figure using the formula:

Future value = present value * (1 + interest rate)

From there on, we not only earn interest on the $300 deposited for that particular month but also on the cumulative total accumulated before that. For example, for period 1, you take the period 0 end of month figure plus the additional 300 deposited for period 1 and then apply the time value equation. This goes on till period 119 which finally gives the future value as $46,273.95.

This is the same that we can also using the FV function as shown in cell J4.

b.

Table starting from cell I9 shows the asked calculations. Deposits range from $50 to $500 in steps of $50. Their corresponding future values are calculated using FV function.

c.

Graph is plotted using the end of month values calculated in column D. They show how the amount in savings account grows over time.