Computing Future Balance in Retirement Savings Acct $($Special Feature: Creating Time Value Formulas$)$

This exercise involves someone who plans to deposit regular annual amounts into a tax$-$favored savings

plan such as the Roth Individual Retirement Account. $($With an untaxed account we can project the

money the saver will have at retirement without having to speculate on how the investment earnings

would be taxed in a taxable arrangement.$)$ The saver plans to make deposits at the start of each year for

$40$ working years, and then to retire at the end of the ${40}^{th}$ year. Thus the saver makes deposits from, for

example, age $25$ through $64($choose any age for the saver's first deposit, but make sure the template you

set up works correctly for $40$ beginning$-$of$-$year deposits$).$ Annual Roth IRA deposit ceilings change over

time, so just make reasonable assumptions about the amount the saver will deposit each year, and about

the average after$-$tax annual rate of return the saver will carn. But do assume that the saver's deposits

in years $7-30,$ and a third amount in years $31-40.($Do not submit the $$3,\frac{100}{\text{\$}}4,\frac{200}{\text{\$}}5,\frac{300}{4\mathrm{.}35}\%$

figures used in the sample output, which is there to help verify that your template is correct before you

choose final numbers for the version you submit; use other dollar and return values. And construct the

spreadsheet so if the user changes Input Section values all Output Section values change automatically;

cells the computations are based on should contain formulas or cell references and not just typed$-$in

numbers; we want this spreadsheet to be a versatile retirement planning tool. But do not use time periods

other than years $1-6,7-30,$ and $31-40$; otherwise the grader can not easily confirm your numbers.$)$

The key to analyzing a long$-$term savings plan is understanding compound interest or$,$ more generally,

compound rates of return. The amount to which a deposit grows over time can be computed as

Deposit x $(1+$ percentage periodic rate of return$)$ sumber of ine perios.

For example, if you put $$1,000$ in an account today and it grows at a $5\%$ average rate per year, then after

$4$ years the $$1,000$ will have grown to

$$1,000(1\mathrm{.}05{)}^4=$$$1,215,51$

Note that the $$1,000\mathrm{.}00$ grows to $($$$1,000\mathrm{.}001\mathrm{.}05)=$$$1,050\mathrm{.}00$ by the end of year $1.$

Then the $,$$$1,050\mathrm{.}00$ grows to $($$$1,050\mathrm{.}001\mathrm{.}05)=$$$1,102\mathrm{.}50$ by the end of year $2.$

Then the $,$$$1,102\mathrm{.}50$ grows to $($$$1,102\mathrm{.}501\mathrm{.}05)=$$$1,157\mathrm{.}63$ by the end of year $3.$

Finally, the $$1,157\mathrm{.}63$ grows to $($$$1,157\mathrm{.}631\mathrm{.}05)=$$$1,215\mathrm{.}51$ by the end of year $4.$

This relationship is the basis for the computations in this exercise, in which we introduce non$-$linear

relationships by using exponents $($indicated by the ${?}^{{?}^{?}}$ sign when you enter information in the spreadsheet

program$).$ So you should compound each expected deposit to its future value $($with the target future date

being the saver's retirement date$),$ and sum the individual future values to find the balance you expect to

see in the account when the saver retires. Then add the cell values representing expected retirement$-$day

totals for the groups of equal deposits $($compounded totals for deposits from years $1-6,$ years $7-30,$

and years $31-40).$ Finally, you should see that this type situation is an example of a future value of

a sequential annuity due problem, and should solve it as such in addition to summing the individual

"deposits' future values. Note that the expected retirement$-$day totals computed all three ways should be

the same if your template is designed correctly, and that the sub$-$totals for the groups of deposits should

be the same whether computed line$-$by$-$line or computed independently with the annuity approach.