PROBLEM I 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 year. Thus the saver makes deposits from, for example, age 25 through 64 (you can choose any age for the saver's first deposit, but the template you set up will work correctly only for 40 yearly deposits, with deposits made at the beginning of each year). Annual Roth IRA deposit ceilings will change over time, so do not feel bound in your analysis by the current $6,000 ($7,000 for people over age 50) annual limit. Make reasonable assumptions about the amount the saver is able/permitted to deposit each year, and about the average after-tax rate of return the saver will earn. But do assume that the saver's deposits will increase or decrease, if you prefer) in stages: one amount in years 1 - 6, a different amount in years 7 - 30, and a third amount in years 31 - 40. (Do not use the $3,100/54,200/55,300/4.35% figures used in the sample output; use other dollar and return values that seem interesting/relevant to you. And do not use time periods other than years 1 - 6,7 30, and 31 - 40; otherwise the grader has to do a lot of extra work to verify 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) number of time periods 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 X (1.05)' = $1,215.51. Note that the $1,000.00 grows to ($1,000.00 x 1.05) = $1,050.00 by the end of year 1. Then the $1,050.00 grows to ($1,050.00 x 1.05) = $1,102.50 by the end of year 2. Then the $1,102.50 grows to ($1,102.50 x 1.05) = $1,157.63 by the end of year 3. Finally, the $1,157.63 grows to ($1,157.63 x 1.05) = $1,215.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 with the annuity approach