I got stuck on this Please Retirement Calculator Starting Salary 55,400 00 Salary Increase Rate 3 Saving Rate 10 Company Match 4 Return Rate 5 9 Year Salary Contribution Company Match Principal Returns Ending Balance 0 0 1 55,400 00 5,540 00 2,216 00 7,756 00 214 68 7,970 68 2 57,062 00 5,706 20 2,282 48 15,959 36 707 68 16,667 04 3 58,773 86 5,877 39 2,350 95 24,895 38 1,245 18 26,140 56 4 60,537 08 6,053 71 2,421 48 34,615 76 1,830 32 36,446 07 5 62,353 19 6,235 32 2,494 13 45,175 52 2,466 44 47,641 96 6 64,223 78 6,422 38 2,568 95 56,633 29 3,157 14 59,790 43 7 66,150 50 6,615 05 2,646 02 69,051 50 3,906 20 72,957 69 8 68,135 01 6,813 50 2,725 40 82,496 59 4,717 67 87,214 26 9 70,179 06 7,017 91 2,807 16 97,039 33 5,595 87 102,635 20 10 72,284 43 7,228 44 2,891 38 112,755 02 6,545 39 119,300 41 11 74,452 97 7,445 30 2,978 12 129,723 83 7,571 10 137,294 93 12 76,686 56 7,668 66 3,067 46 148,031 05 8,678 22 156,709 27 13 78,987 15 7,898 72 3,159 49 167,767 47 9,872 27 177,639 74 14 81,356 77 8,135 68 3,254 27 189,029 68 11,159 13 200,188 82 15 83,797 47 8,379 75 3,351 90 211,920 46 12,545 08 224,465 54 16 86,311 39 8,631 14 3,452 46 236,549 14 14,036 77 250,585 91 17 88,900 74 8,890 07 3,556 03 263,032 01 15,641 30 278,673 31 18 91,567 76 9,156 78 3,662 71 291,492 80 17,366 21 308,859 01 19 94,314 79 9,431 48 3,772 59 322,063 08 19,219 51 341,282 59 20 97,144 24 9,714 42 3,885 77 354,882 79 21,209 75 376,092 53 21 100,058 56 10,005 86 4,002 34 390,100 73 23,345 98 413,446 72 22 103,060 32 10,306 03 4,122 41 427,875 16 25,637 87 453,513 03 23 106,152 13 10,615 21 4,246 09 468,374 33 28,095 67 496,470 00 24 109,336 69 10,933 67 4,373 47 511,777 14 30,730 28 542,507 41 25 112,616 79 11,261 68 4,504 67 558,273 76 33,553 30 591,827 07 26 115,995 30 11,599 53 4,639 81 608,066 41 36,577 07 644,643 48 27 119,475 16 11,947 52 4,779 01 661,370 00 39,814 69 701,184 69 28 123,059 41 12,305 94 4,922 38 718,413 01 43,280 09 761,693 10 29 126,751 19 12,675 12 5,070 05 779,438 27 46,988 08 826,426 35 30 130,553 73 13,055 37 5,222 15 844,703 87 50,954 40 895,658 27 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 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 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 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 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

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 19, 2024

I got stuck on this. Please! Retirement Calculator Starting Salary: 55,400.00 Salary Increase Rate: 3% Saving Rate: 10% Company Match: 4% Return Rate: 5.9% Year

I got stuck on this. Please! image text in transcribed

Retirement Calculator

Starting Salary:		55,400.00
Salary Increase Rate:		3%
Saving Rate:		10%
Company Match:		4%
Return Rate:		5.9%


Year	Salary	Contribution	Company Match	Principal	Returns	Ending Balance
0						0
1	55,400.00	5,540.00	2,216.00	7,756.00	214.68	7,970.68
2	57,062.00	5,706.20	2,282.48	15,959.36	707.68	16,667.04
3	58,773.86	5,877.39	2,350.95	24,895.38	1,245.18	26,140.56
4	60,537.08	6,053.71	2,421.48	34,615.76	1,830.32	36,446.07
5	62,353.19	6,235.32	2,494.13	45,175.52	2,466.44	47,641.96
6	64,223.78	6,422.38	2,568.95	56,633.29	3,157.14	59,790.43
7	66,150.50	6,615.05	2,646.02	69,051.50	3,906.20	72,957.69
8	68,135.01	6,813.50	2,725.40	82,496.59	4,717.67	87,214.26
9	70,179.06	7,017.91	2,807.16	97,039.33	5,595.87	102,635.20
10	72,284.43	7,228.44	2,891.38	112,755.02	6,545.39	119,300.41
11	74,452.97	7,445.30	2,978.12	129,723.83	7,571.10	137,294.93
12	76,686.56	7,668.66	3,067.46	148,031.05	8,678.22	156,709.27
13	78,987.15	7,898.72	3,159.49	167,767.47	9,872.27	177,639.74
14	81,356.77	8,135.68	3,254.27	189,029.68	11,159.13	200,188.82
15	83,797.47	8,379.75	3,351.90	211,920.46	12,545.08	224,465.54
16	86,311.39	8,631.14	3,452.46	236,549.14	14,036.77	250,585.91
17	88,900.74	8,890.07	3,556.03	263,032.01	15,641.30	278,673.31
18	91,567.76	9,156.78	3,662.71	291,492.80	17,366.21	308,859.01
19	94,314.79	9,431.48	3,772.59	322,063.08	19,219.51	341,282.59
20	97,144.24	9,714.42	3,885.77	354,882.79	21,209.75	376,092.53
21	100,058.56	10,005.86	4,002.34	390,100.73	23,345.98	413,446.72
22	103,060.32	10,306.03	4,122.41	427,875.16	25,637.87	453,513.03
23	106,152.13	10,615.21	4,246.09	468,374.33	28,095.67	496,470.00
24	109,336.69	10,933.67	4,373.47	511,777.14	30,730.28	542,507.41
25	112,616.79	11,261.68	4,504.67	558,273.76	33,553.30	591,827.07
26	115,995.30	11,599.53	4,639.81	608,066.41	36,577.07	644,643.48
27	119,475.16	11,947.52	4,779.01	661,370.00	39,814.69	701,184.69
28	123,059.41	12,305.94	4,922.38	718,413.01	43,280.09	761,693.10
29	126,751.19	12,675.12	5,070.05	779,438.27	46,988.08	826,426.35
30	130,553.73	13,055.37	5,222.15	844,703.87	50,954.40	895,658.27

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 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. 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. 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. 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