Using the accompanying Retirement Calculator spreadsheet model, Claire wants to use Scenario Manager to compare the...

 

Transcribed Image Text:

Using the accompanying Retirement Calculator spreadsheet model, Claire wants to use Scenario Manager to compare the following retirement saving scenarios: Click here for the Excel Data File Scenario 2 Starting salary $ 55,400 $ 70,400 $68,400 $ 72,400 Increase rate 29 2% 38 38 Saving rate Return rate 158 108 15% 106 a. Which scenario generates the highest retirement savings at the end of 10 years? What is the amount? Note: Round "Amount" to 2 decimal places. Scenario generates the highest retirement savings Amount 7% 78 7 71 b. Which scenario generates the lowest retirement savings at the end of 20 years? What is the amount? Note: Round "Amount" to 2 decimal places. Scanario generates the lowest retirement savings Amount c. Which scenario generates the second highest retirement savings at the end of 30 years? What is the amount? Note: Round "Amount" to 2 decimal places. Scenario generates 2nd highest retirement savings Amount d. For Scenario 4, if Claire would like to have $1 million at the end of 30 years, what would her saving rate need to be? How about at the end of 20 years? Note: Round your answers to 2 decimal places. Savings rate at end of 30 years Savings rate at end of 20 years % % 2 3 Starting Salary: 4 Salary Increase Rate: 5 Saving Rate: 5 Company Match: 7 Return Rate: 8 9 10 11 12 13 14 15 16 17 18. 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 Year 0 1R 28 3 R 4R 54 6 73 8R 95 10 11 R 12 13 14 15 R 16 17 18 19 20 21 R 22 * 23 24 25 X 26 27 * 28 29 € 30 * Dationmat Salary Retirement Calculator 55,400.00 3% 88,900.74 91,567.76 94,314.79 97,144.24 100,058.56 103,060.32 10% 4% 5.9% Contribution Company Match 55,400.00 5,540.00 57,062.00 5,706.20 58,773.86 5,877.39 60,537.08 6,053.71 62,353.19 6,235.32 64,223.78 6,422.38 6,615.05 66,150.50 68,135.01 6,813.50 70,179.06 7,017.91 72,284.43 7,228.44 74,452.97 7,445.30 76,686.56 7,668.66 78,987.15 7,898.72 * 81,356.77 8,135.68 83,797.47 8,379.75 86,311.39 8,631.14 8,890.07 9,156.78* 9,431.48 9,714.42 x 10,005.86* 10,306.03 * 106,152.13* 10,615.21 K 109,336.69 10,933.67 * 112,616.79 11,261.68 * 115,995.30 11,599.53X 119,475.16 11,947.523 123,059.41 * 12,305,94 R 126,751.1912,675.12 * 130,553.7313,055.37 3 2,216.00 2,282.48 R 2,350.95 2,421.48 R 2,494.13* 2,568.95 2,646.02* 2,725.40* 2,807.16 * 2,891.38 2,978.12 3,067.46 X 3,159.49 * 3,254.27 3,351.90 3,452.46 3,556.03 3,662.71 R 3,772.59* 3,885.77 * 4,002.34* 4,122.41 * 4,246.09 4,373.47* 4,504.67 * 4,639.81* 4,779.01 R 4,922.38* 5,070.05 5,222.15 K Principal Returns 7,756.00 214.68 15,959.36* 707,68 24,895.38 1,245.18 34,615.76 1,830.32 45,175.52 2,466.44 56,633.29 3,157.14 69,051.50 3,906.20 82,496.59 4,717.67 * 97,039.335,595.87 112,755.02 6,545.39 129,723.837,571.10 148,031.058,678.22 * 167,767.479,872.27 11,159.13 189,029.68 211,920.46 * 12,545.08 236,549.14 14,036.77 * 263,032.01 15,641.30 * 291,492.80 17,366.21 ² 322,063.0819,219.51 354,882.79 21,209.75 t 390,100.7323,345.98 * 427,875.16*25,637.87 468,374.33 28,095.67 511,777.1430,730.28 * 558,273.76 * 33,553.30 * 608,066.41* 36,577.07 R 661,370.00 39,814.69 * 718,413.01 43,280.09 779,438.27 * 46,988.08 844,703.87 50,954.40 * Ending Balance 0 7,970.68 16,667.04 26,140.56 36,446.07 47,641.96 59,790.43 72,957.69 87,214.26 102,635.20 119,300.41 137,294.93 156,709.27 177,639.74 200,188.82 224,465.54 250,585.91 278,673.31 308,859.01 341,282.59 376,092.53 413,446.72 453,513.03 496,470.00 542,507.41 591,827.07 644,643.48 701,184.69 761,693.10 826,426.35 895,658.27 H Using the accompanying Retirement Calculator spreadsheet model, Claire wants to use Scenario Manager to compare the following retirement saving scenarios: Click here for the Excel Data File Scenario 2 Starting salary $ 55,400 $ 70,400 $68,400 $ 72,400 Increase rate 29 2% 38 38 Saving rate Return rate 158 108 15% 106 a. Which scenario generates the highest retirement savings at the end of 10 years? What is the amount? Note: Round "Amount" to 2 decimal places. Scenario generates the highest retirement savings Amount 7% 78 7 71 b. Which scenario generates the lowest retirement savings at the end of 20 years? What is the amount? Note: Round "Amount" to 2 decimal places. Scanario generates the lowest retirement savings Amount c. Which scenario generates the second highest retirement savings at the end of 30 years? What is the amount? Note: Round "Amount" to 2 decimal places. Scenario generates 2nd highest retirement savings Amount d. For Scenario 4, if Claire would like to have $1 million at the end of 30 years, what would her saving rate need to be? How about at the end of 20 years? Note: Round your answers to 2 decimal places. Savings rate at end of 30 years Savings rate at end of 20 years % % 2 3 Starting Salary: 4 Salary Increase Rate: 5 Saving Rate: 5 Company Match: 7 Return Rate: 8 9 10 11 12 13 14 15 16 17 18. 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 Year 0 1R 28 3 R 4R 54 6 73 8R 95 10 11 R 12 13 14 15 R 16 17 18 19 20 21 R 22 * 23 24 25 X 26 27 * 28 29 € 30 * Dationmat Salary Retirement Calculator 55,400.00 3% 88,900.74 91,567.76 94,314.79 97,144.24 100,058.56 103,060.32 10% 4% 5.9% Contribution Company Match 55,400.00 5,540.00 57,062.00 5,706.20 58,773.86 5,877.39 60,537.08 6,053.71 62,353.19 6,235.32 64,223.78 6,422.38 6,615.05 66,150.50 68,135.01 6,813.50 70,179.06 7,017.91 72,284.43 7,228.44 74,452.97 7,445.30 76,686.56 7,668.66 78,987.15 7,898.72 * 81,356.77 8,135.68 83,797.47 8,379.75 86,311.39 8,631.14 8,890.07 9,156.78* 9,431.48 9,714.42 x 10,005.86* 10,306.03 * 106,152.13* 10,615.21 K 109,336.69 10,933.67 * 112,616.79 11,261.68 * 115,995.30 11,599.53X 119,475.16 11,947.523 123,059.41 * 12,305,94 R 126,751.1912,675.12 * 130,553.7313,055.37 3 2,216.00 2,282.48 R 2,350.95 2,421.48 R 2,494.13* 2,568.95 2,646.02* 2,725.40* 2,807.16 * 2,891.38 2,978.12 3,067.46 X 3,159.49 * 3,254.27 3,351.90 3,452.46 3,556.03 3,662.71 R 3,772.59* 3,885.77 * 4,002.34* 4,122.41 * 4,246.09 4,373.47* 4,504.67 * 4,639.81* 4,779.01 R 4,922.38* 5,070.05 5,222.15 K Principal Returns 7,756.00 214.68 15,959.36* 707,68 24,895.38 1,245.18 34,615.76 1,830.32 45,175.52 2,466.44 56,633.29 3,157.14 69,051.50 3,906.20 82,496.59 4,717.67 * 97,039.335,595.87 112,755.02 6,545.39 129,723.837,571.10 148,031.058,678.22 * 167,767.479,872.27 11,159.13 189,029.68 211,920.46 * 12,545.08 236,549.14 14,036.77 * 263,032.01 15,641.30 * 291,492.80 17,366.21 ² 322,063.0819,219.51 354,882.79 21,209.75 t 390,100.7323,345.98 * 427,875.16*25,637.87 468,374.33 28,095.67 511,777.1430,730.28 * 558,273.76 * 33,553.30 * 608,066.41* 36,577.07 R 661,370.00 39,814.69 * 718,413.01 43,280.09 779,438.27 * 46,988.08 844,703.87 50,954.40 * Ending Balance 0 7,970.68 16,667.04 26,140.56 36,446.07 47,641.96 59,790.43 72,957.69 87,214.26 102,635.20 119,300.41 137,294.93 156,709.27 177,639.74 200,188.82 224,465.54 250,585.91 278,673.31 308,859.01 341,282.59 376,092.53 413,446.72 453,513.03 496,470.00 542,507.41 591,827.07 644,643.48 701,184.69 761,693.10 826,426.35 895,658.27 H