On the Retirement Savings sheet, what is the name of the best function to enter
ID: 364771 • Letter: O
Question
On the Retirement Savings sheet, what is the name of the best function to enter in B17? On the Retirement Savings sheet, what is the best formula to enter in B17 On the Retirement Savings sheet, what is the value of John's 401k when he retires at age 67? Enter your answer in dollars and cents with no $ or commas. On the Retirement Savings sheet, will John have $1 million dollars saved by the time he retires at age 67? Yes No
401k Retirement Savings Planning Worksheet Name John Doe Birthday 10/1/1962 Current Age 55 Retirement Age 67 Years Til Retirement 12 Estimated Rate of Return 5% Initial Investment (Current Savings) $250,000 Annual Contribution to 401K $25,000 Value of 401K at RetirementExplanation / Answer
--
In the cell B17, enter the Payment formula: PMT(rate = 5%/12, NUMBER OF Periods = 12 * 12 , PV = $250,000)
= PMT(0.05/12, 12*12, 250000)
=PMT(B11/12, 12*12, B13)
Payment for John’s 401K is 2312.23 and hence at John’s retirement age of 67, the 401(k) balance would reach $1,039,724
this amount is equivalent to $729,241 in terms of the power of the money or the dollars purchasing power as of today
John had made a total contributionsof $376,575
This made a total interest of $1,101,880 and a total pay out of $1,728,455
John would have $550,000 as he initially contributed $250,000 and then $25,000 per annum for 12 years = 25000*12 = 300,000
300,000 + 250,000 = 550,000
So, Yes John will have saved $1 million before his retirement age of 67
Schedule
Age
Contribution
Interest
End Balance
56
$27,600.00
$15,828.00
$293,428.00
57
$28,221.00
$18,452.31
$340,101.31
58
$28,860.63
$21,271.90
$390,233.84
59
$29,519.45
$24,299.61
$444,052.90
60
$30,198.03
$27,549.11
$501,800.05
61
$30,896.97
$31,034.91
$563,731.93
62
$31,616.88
$34,772.42
$630,121.24
63
$32,358.39
$38,778.03
$701,257.65
64
$33,122.14
$43,069.12
$777,448.92
65
$33,908.80
$47,664.20
$859,021.92
66
$34,719.07
$52,582.89
$946,323.88
67
$35,553.64
$57,846.04
$1,039,723.56
Retired
Age
Payout
Interest
End Balance
68
($96,025.27)
$62,383.41
$1,006,081.70
69
($96,025.27)
$60,364.90
$970,421.33
70
($96,025.27)
$58,225.28
$932,621.34
71
($96,025.27)
$55,957.28
$892,553.35
72
($96,025.27)
$53,553.20
$850,081.28
73
($96,025.27)
$51,004.88
$805,060.89
74
($96,025.27)
$48,303.65
$757,339.27
75
($96,025.27)
$45,440.36
$706,754.35
76
($96,025.27)
$42,405.26
$653,134.35
77
($96,025.27)
$39,188.06
$596,297.13
78
($96,025.27)
$35,777.83
$536,049.69
79
($96,025.27)
$32,162.98
$472,187.40
80
($96,025.27)
$28,331.24
$404,493.37
81
($96,025.27)
$24,269.60
$332,737.71
82
($96,025.27)
$19,964.26
$256,676.70
83
($96,025.27)
$15,400.60
$176,052.03
84
($96,025.27)
$10,563.12
$90,589.88
85
($96,025.27)
$5,435.39
$0.00
Schedule
Age
Contribution
Interest
End Balance
56
$27,600.00
$15,828.00
$293,428.00
57
$28,221.00
$18,452.31
$340,101.31
58
$28,860.63
$21,271.90
$390,233.84
59
$29,519.45
$24,299.61
$444,052.90
60
$30,198.03
$27,549.11
$501,800.05
61
$30,896.97
$31,034.91
$563,731.93
62
$31,616.88
$34,772.42
$630,121.24
63
$32,358.39
$38,778.03
$701,257.65
64
$33,122.14
$43,069.12
$777,448.92
65
$33,908.80
$47,664.20
$859,021.92
66
$34,719.07
$52,582.89
$946,323.88
67
$35,553.64
$57,846.04
$1,039,723.56
Retired
Age
Payout
Interest
End Balance
68
($96,025.27)
$62,383.41
$1,006,081.70
69
($96,025.27)
$60,364.90
$970,421.33
70
($96,025.27)
$58,225.28
$932,621.34
71
($96,025.27)
$55,957.28
$892,553.35
72
($96,025.27)
$53,553.20
$850,081.28
73
($96,025.27)
$51,004.88
$805,060.89
74
($96,025.27)
$48,303.65
$757,339.27
75
($96,025.27)
$45,440.36
$706,754.35
76
($96,025.27)
$42,405.26
$653,134.35
77
($96,025.27)
$39,188.06
$596,297.13
78
($96,025.27)
$35,777.83
$536,049.69
79
($96,025.27)
$32,162.98
$472,187.40
80
($96,025.27)
$28,331.24
$404,493.37
81
($96,025.27)
$24,269.60
$332,737.71
82
($96,025.27)
$19,964.26
$256,676.70
83
($96,025.27)
$15,400.60
$176,052.03
84
($96,025.27)
$10,563.12
$90,589.88
85
($96,025.27)
$5,435.39
$0.00
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.