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'h 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 earn. But do assume that the saver's deposits will increase or decrease, if you prefer) in these stages: one amount in years 1-6, a different amount in years 7 30, and a third amount in years 31 - 40. (Do not submit the $3,100/$4,200/$5,300/4.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 wants to change input values all the output 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 more 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) 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)4 = $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 computed independently with the annuity approach. 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'h 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 earn. But do assume that the saver's deposits will increase or decrease, if you prefer) in these stages: one amount in years 1-6, a different amount in years 7 30, and a third amount in years 31 - 40. (Do not submit the $3,100/$4,200/$5,300/4.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 wants to change input values all the output 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 more 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) 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)4 = $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 computed independently with the annuity approach