excel online structured activity: required annuity payments\nyour father is 50 years old and will retire in…

excel online structured activity: required annuity payments\nyour father is 50 years old and will retire in 10 years. he expects to live for 25 years after he retires, until he is 85. he wants a fixed retirement income that has the same purchasing - power at the time he retires as $45,000 has today. (the real value of his retirement income will decline annually after he retires.) his retirement income will begin the day he retires, 10 years from today, at which time he will receive 24 additional annual payments. annual inflation is expected to be 3%. he currently has $245,000 saved, and he expects to earn 8% annually on his savings. the data has been collected in the microsoft excel online file below. open the spreadsheet and perform the required analysis to answer the question below.\nopen spreadsheet\nhow much must he save during each of the next 10 years (end - of - year deposits) to meet his retirement goal? do not round your intermediate calculations. round your answer to the nearest cent.

excel online structured activity: required annuity payments\nyour father is 50 years old and will retire in 10 years. he expects to live for 25 years after he retires, until he is 85. he wants a fixed retirement income that has the same purchasing - power at the time he retires as $45,000 has today. (the real value of his retirement income will decline annually after he retires.) his retirement income will begin the day he retires, 10 years from today, at which time he will receive 24 additional annual payments. annual inflation is expected to be 3%. he currently has $245,000 saved, and he expects to earn 8% annually on his savings. the data has been collected in the microsoft excel online file below. open the spreadsheet and perform the required analysis to answer the question below.\nopen spreadsheet\nhow much must he save during each of the next 10 years (end - of - year deposits) to meet his retirement goal? do not round your intermediate calculations. round your answer to the nearest cent.

Answer

Explanation:

Step1: Calculate the future - value of the current savings

The future - value formula for a single amount is $FV = PV(1 + r)^n$, where $PV=$245000$, $r = 0.08$, and $n = 10$. $FV_1=245000\times(1 + 0.08)^{10}$ $FV_1=245000\times2.158925$ $FV_1=$528936.63$

Step2: Calculate the real - value of the desired retirement income at retirement

The desired real retirement income is $$45000$ today. Using the future - value of a single amount formula for inflation, $FV_{income}=45000\times(1 + 0.03)^{10}$ $FV_{income}=45000\times1.343916$ $FV_{income}=$60476.23$

Step3: Calculate the present - value of the retirement income stream at retirement

The retirement income is an annuity - due (starts on the first day of retirement). The present - value of an annuity - due formula is $PV = PMT\times\frac{1-(1 + r)^{-n}}{r}\times(1 + r)$. Here, $PMT = 60476.23$, $r=0.08$, and $n = 25$. $PV_{income}=60476.23\times\frac{1-(1 + 0.08)^{-25}}{0.08}\times(1 + 0.08)$ Let $x=(1 + 0.08)^{-25}=\frac{1}{(1 + 0.08)^{25}}=\frac{1}{6.848475}\approx0.146933$ $\frac{1 - x}{0.08}=\frac{1-0.146933}{0.08}=\frac{0.853067}{0.08}=10.66333$ $PV_{income}=60476.23\times10.66333\times1.08$ $PV_{income}=60476.23\times11.516396$ $PV_{income}=$696447.77$

Step4: Calculate the future - value of the additional savings needed

The amount needed from additional savings at retirement is $PV_{income}-FV_1$. $FV_2=696447.77 - 528936.63=$167511.14$

Step5: Calculate the annual savings amount

The future - value of an ordinary annuity formula is $FV = PMT\times\frac{(1 + r)^{n}-1}{r}$. Here, $FV = FV_2 = 167511.14$, $r = 0.08$, and $n = 10$. $167511.14=PMT\times\frac{(1 + 0.08)^{10}-1}{0.08}$ Let $y=(1 + 0.08)^{10}-1=2.158925 - 1 = 1.158925$ $\frac{y}{0.08}=\frac{1.158925}{0.08}=14.48656$ $PMT=\frac{167511.14}{14.48656}$ $PMT=$11562.07$

Answer:

$11562.07$