Homework SourseProblem 107 Lindsay is 25 years old and has a new job in web
Problem 10-7
Lindsay is 25 years old and has a new job in web development. She wants to make sure that she is financially sound in 30 years, so she plans to invest the same amount into a retirement account at the end of every year for the next 30 years.
| (a) | Construct a data table in Excel that will show Lindsay the balance of her retirement account for various levels of annual investment and return. If Lindsay invests $11,000 at return of 6%, what would be the balance at the end of 20th year in the account? | |||||||||||||||||||||||||||||||||||||||||||||||||||
| If required, round your answers to two decimal places. | ||||||||||||||||||||||||||||||||||||||||||||||||||||
| $ | ||||||||||||||||||||||||||||||||||||||||||||||||||||
| (b) | Develop the two-way table in Excel for the balance at the end of 30th year in the account. Consider annual investment amounts of $5000 to $20,000 in increments of $1000, and returns of 0% to 12% in increments of 1%. Note that because Lindsay invests at the end of the year, there is no interest earned on the contribution for the year in which she contributes. Complete the below table. | |||||||||||||||||||||||||||||||||||||||||||||||||||
| If required, round your answers to two decimal places. | ||||||||||||||||||||||||||||||||||||||||||||||||||||
|
Solution
a. The balance at the end of 20th year in the account is the future value of annual deposits for 20 years
FV of deposits for 20 years = PMT [(1+i) ^n – 1] /i
Where,
Future value of deposits (FV) =?
Annual deposits PMT = $11,000
Number of deposits n = 20 annual deposits
Annual interest rate I =6%
Therefore
FV = $11,000 * [(1+6%) ^20 -1]/6%
= $404,641.50
The balance at the end of 20th year in the account is $404,641.50.
b. Similarly we can use above formula to calculate in Excel for the balance at the end of 30th year in the account.
The annual investment amounts of $5000 to $20,000 in increments of $1000, and returns of 0% to 12% in increments of 1%.
Number of deposits n = 30 annual deposits
| The balance at the end of 30th year in the account | |||||||||||||
| Period of deposit (years) n = | 30 | ||||||||||||
| Annual Interest rate | |||||||||||||
| Annual Investment | 0% | 1% | 2% | 3% | 4% | 5% | 6% | 7% | 8% | 9% | 10% | 11% | 12% |
| $5,000 | $150,000.00 | $173,924.46 | $202,840.40 | $237,877.08 | $280,424.69 | $332,194.24 | $395,290.93 | $472,303.93 | $566,416.06 | $681,537.69 | $822,470.11 | $995,104.39 | $1,206,663.42 |
| $6,000 | $180,000.00 | $208,709.35 | $243,408.48 | $285,452.49 | $336,509.63 | $398,633.09 | $474,349.12 | $566,764.72 | $679,699.27 | $817,845.23 | $986,964.14 | $1,194,125.27 | $1,447,996.11 |
| $7,000 | $210,000.00 | $243,494.24 | $283,976.55 | $333,027.91 | $392,594.56 | $465,071.93 | $553,407.30 | $661,225.50 | $792,982.48 | $954,152.77 | $1,151,458.16 | $1,393,146.15 | $1,689,328.79 |
| $8,000 | $240,000.00 | $278,279.13 | $324,544.63 | $380,603.33 | $448,679.50 | $531,510.78 | $632,465.49 | $755,686.29 | $906,265.69 | $1,090,460.31 | $1,315,952.18 | $1,592,167.02 | $1,930,661.47 |
| $9,000 | $270,000.00 | $313,064.02 | $365,112.71 | $428,178.74 | $504,764.44 | $597,949.63 | $711,523.68 | $850,147.08 | $1,019,548.90 | $1,226,767.85 | $1,480,446.20 | $1,791,187.90 | $2,171,994.16 |
| $10,000 | $300,000.00 | $347,848.92 | $405,680.79 | $475,754.16 | $560,849.38 | $664,388.48 | $790,581.86 | $944,607.86 | $1,132,832.11 | $1,363,075.39 | $1,644,940.23 | $1,990,208.78 | $2,413,326.84 |
| $11,000 | $330,000.00 | $382,633.81 | $446,248.87 | $523,329.57 | $616,934.32 | $730,827.32 | $869,640.05 | $1,039,068.65 | $1,246,115.32 | $1,499,382.92 | $1,809,434.25 | $2,189,229.66 | $2,654,659.53 |
| $12,000 | $360,000.00 | $417,418.70 | $486,816.95 | $570,904.99 | $673,019.25 | $797,266.17 | $948,698.23 | $1,133,529.44 | $1,359,398.53 | $1,635,690.46 | $1,973,928.27 | $2,388,250.54 | $2,895,992.21 |
| $13,000 | $390,000.00 | $452,203.59 | $527,385.03 | $618,480.40 | $729,104.19 | $863,705.02 | $1,027,756.42 | $1,227,990.22 | $1,472,681.74 | $1,771,998.00 | $2,138,422.29 | $2,587,271.41 | $3,137,324.90 |
| $14,000 | $420,000.00 | $486,988.48 | $567,953.11 | $666,055.82 | $785,189.13 | $930,143.87 | $1,106,814.61 | $1,322,451.01 | $1,585,964.96 | $1,908,305.54 | $2,302,916.32 | $2,786,292.29 | $3,378,657.58 |
| $15,000 | $450,000.00 | $521,773.37 | $608,521.19 | $713,631.24 | $841,274.07 | $996,582.71 | $1,185,872.79 | $1,416,911.79 | $1,699,248.17 | $2,044,613.08 | $2,467,410.34 | $2,985,313.17 | $3,619,990.27 |
| $16,000 | $480,000.00 | $556,558.26 | $649,089.27 | $761,206.65 | $897,359.00 | $1,063,021.56 | $1,264,930.98 | $1,511,372.58 | $1,812,531.38 | $2,180,920.62 | $2,631,904.36 | $3,184,334.05 | $3,861,322.95 |
| $17,000 | $510,000.00 | $591,343.16 | $689,657.35 | $808,782.07 | $953,443.94 | $1,129,460.41 | $1,343,989.17 | $1,605,833.37 | $1,925,814.59 | $2,317,228.16 | $2,796,398.39 | $3,383,354.92 | $4,102,655.63 |
| $18,000 | $540,000.00 | $626,128.05 | $730,225.43 | $856,357.48 | $1,009,528.88 | $1,195,899.26 | $1,423,047.35 | $1,700,294.15 | $2,039,097.80 | $2,453,535.69 | $2,960,892.41 | $3,582,375.80 | $4,343,988.32 |
| $19,000 | $570,000.00 | $660,912.94 | $770,793.50 | $903,932.90 | $1,065,613.82 | $1,262,338.10 | $1,502,105.54 | $1,794,754.94 | $2,152,381.01 | $2,589,843.23 | $3,125,386.43 | $3,781,396.68 | $4,585,321.00 |
| $20,000 | $600,000.00 | $695,697.83 | $811,361.58 | $951,508.31 | $1,121,698.76 | $1,328,776.95 | $1,581,163.72 | $1,889,215.73 | $2,265,664.22 | $2,726,150.77 | $3,289,880.45 | $3,980,417.56 | $4,826,653.69 |
