Question

Barry and Samantha Harris – Retirement Savings

You must work on your own spreadsheet with Excel skills or concepts.

Barry and Samantha are starting to take their retirement planning seriously. They are both 45 and plan to retire in 20 years at the age of 65. They expect to live 15 years in retirement (a life expectancy of 80). Between their 401k accounts they currently have $66,000 in retirement savings.

They currently have a combined income of $80,000 per year and expect to be able to live comfortably in retirement with 80% of their current purchasing power. They expect inflation to be 2% per year for the rest of their lives. They also expect to earn an averaged 11% per year on their investments, both now and in retirement.

What amount of annual income will they need (after adjusting for inflation) in each of the fifteen years of retirement to have the purchasing power of 80% of their current income?

Assuming they will continue to earn 11% on their investments, how much money will they need to have in their retirement accounts when they retire so that it will provide the fifteen years of income?

Taking into account what they currently have in savings, how much will they have to save each month to meet their retirement needs?

Sensitivity analysis: Redo the analysis assuming that they only earn 8% per year on their investments, instead of 11%. Determine the needed amounts so they have the money they need in retirement.

Note: Assume that all payments will be made at the end of the period (ordinary annuity).

Answer 1

Part 1:

Combined income = $80,000

80% of current puchasing power = $80,000 * 80%

= $64,000

Inflation rate = 2%

Puchasing power at the beinging of retirement i.e. at age of 66 = 80% of current puchasing power * (1+Inflation Rate)^{Number of Years}

Puchasing power at the beinging of retirement i.e. at age of 66 = $64,000 * (1+2%)^20

= $95,100.63

Similarly, we can calculate the annual income for the remaining retirement period:

Part 2:

For calculating the retirement corpus, we have to use the Goal Seek function in excel. This tool can be found in :

Excel > Data > What if Analyis > Goal Seek

Here we have assumed that the corpus at the beginning of age 66 (Year 1 or retirement) to be as 1, and termed it as Retirement Corpus - Opening

Annual expenses we have calculated in the perious part, are 80% of the current purchasing power increased at the rate of inflation.

Retirement Corpus - Closing is the balance remained invested by meeting the annual expenses.

Opening balance of the next year will be calculated = Retirement Corpus - Closing * (1+Return on investment)

After running the goal seek function we'll get the following output:

Hence the corpus needed at the time of retirement is $842,977.84.

Part 3:

Current savings = $66,000

Rate of return = 11%

Future Value of Current savings = Current Savings * (1+ Return on Investment)^{Number of Years}

Future Value of Current savings = $532,112.56

Total Retirement Corpus = $842,977.84

Deficit in corpus to be funded by monthly savings = $842,977.84 - $532,112.56

= $310,865.28

We can find the value of monthly savings by using Future Value of Annuity equation:

Future Value of Annuity = P * [(1+R)^N - 1]/R

Future Value of Annuity = Deficit retirement Corpus i.e. $310,865.28

R = Rate of interest i.e. 11%/12 months = 0.92%

N = Number of periods i.e. 20 years * 12 months = 240

P = Periodic payments

$310,865.28 = P * [(1+0.92%)²⁴⁰ - 1]/0.92%

P (Monthly savings needed)= $359.12

Part 4:

Rate of retun on investment changed to 8% from 11%.

New retirement corpus calculation done with the help of goal seek are as follows:

New retirement corpus = $985,534.62

Current savings = $66,000

Rate of return = 8%

Future Value of Current savings = Current Savings * (1+ Return on Investment)^{Number of Years}

Future Value of Current savings = $307,623.17

Total Retirement Corpus = $985,534.62

Deficit in corpus to be funded by monthly savings = $985,534.62 - $307,623.17

= $677,911.45

We can find the value of monthly savings by using Future Value of Annuity equation:

Future Value of Annuity = P * [(1+R)^N - 1]/R

Future Value of Annuity = Deficit retirement Corpus i.e. $677,911.45

R = Rate of interest i.e. 8%/12 months = 0.667%

N = Number of periods i.e. 20 years * 12 months = 240

P = Periodic payments

$677,911.45 = P * [(1+0.667%)²⁴⁰ - 1]/0.667%

P (Monthly savings needed)= $1,150.91

Do let me know in case of any doubt.