Question

Most of you will work for companies that will no longer have pension systems and you may not be able to count on having Social Security at all in retirement. Therefore, you will need to generate your own retirement income by saving a large quantity of money in a 401K or other similar retirement account over your lifetime. The mistake many people make is to wait to start saving any significant amount of money until they are in their later 30s or 40s (or worse even later). In this problem, we will analyze several situations to see what the impact of those choices are on how much you must save each year to generate the retirement income you will need.

Most conventional wisdom says that when you retire, you want to be able to replace 65% of your take home earnings from your job (i.e. this basically assumes that 35% of your earnings were being used to pay the mortgage on your house and that you will have paid off your house by the time you retire, so to maintain your lifestyle you only need income to replace the other 65% of your take home earnings that you were spending on everything else). So how much do you need your retirement savings to generate in yearly income? Let’s assume you were graduating today, and you were just turning 22 years old. Also assume that as a Chemical Engineer your starting salary was $70,000 per year. If we assume that on average you earned a 2% raise per year at the end of each year (i.e. not an unreasonable raise to keep up with inflation), then if you wait until your 65th birthday to retire (i.e. you would work 43 complete years), your salary when you are 65 would be,

$70,000 x (1.02)43 = $164,023.25

Let’s assume that you will be paying an average personal income tax rate and social security tax rate combined of 22% at that time, so your take home earnings per year when you retire at 65 would be,

$164,023.25 x 0.78 = $127,938.14

So to replace 65% of your take home earnings your investments would need to generate a yearly cash flow of

Yearly Retirement Withdrawal Cash Flow = $127,938.14 x 0.65 = $83,159.79

Using a Future Value Cash Flow Table to Analyze a “Safe” Plan

Analyze the following scenario by using Excel to calculate a Future Value cash flow table to provide answers to the questions posed. In the PDF containing your answers, be sure to paste in copies of the relevant Excel tables you used to determine your answers.

Let’s assume you want to generate the required $83,159.79 per year in retirement by switching over your retirement savings into a very safe 4% interest yield account and that you only withdraw the interest to live on (i.e. you never deplete the balance of the account and can thus be secure no matter how long you live and also can leave it to your children, etc.). You would need to amass a final account value by the time you retire and transition it to the 4% yielding safe account of $95,853.89/0.04= $2,078,994.75 to generate that much interest to live on for the rest of your life. While you are working, let’s assume you use a moderate to aggressive risk investment account that can generate 7% interest per year on your savings.

A. If you start saving the same amount each and every year when you start your job by saving each year and making your first deposit on your 23rd birthday (i.e. you save up during the year from age 22 to age 23 and place your deposit in the bank at the beginning of each year starting at age 23), how much must you save each year to provide the needed income for retirement? Again, assume you work the year you are 65 and retire right at the end of that year, so you do not make a yearly deposit into your retirement account at the end of that year (i.e. the start of your age 66 year) and instead use any money you saved the year your are turning 65 to make your retirement transition. So your last retirement deposit will come at the beginning of your age 65 year from money you save the previous year and will only have one year to accrue interest before you have to convert your entire account over to the safe 4% yield interest account.

B. Do the same analysis as in Part A, but complete it using a Present Value Analysis instead. Using this analysis, how much should you save each year? Be sure to show your cash flow table in your answer.

Answer 1

Part-A:

Target Accumulation at the completion of 65 years = $2,078,994.75

Rate of Interest = 4%

Scenario-1

When the last Contribution is at the end of age 65 years.

The formula for Future value of annuity -

FVOA = A * [ (1+i)^n - 1 / i ]

where,

FVOA = $2,078,994.75

A = Amount of periodic deposit

i = 4%

n = number of contributions , 44 (first at end of 22 years age and last at end of 65 years age)

Putting the values in the formula we get :

$2,078,994.75 = A* [ (1+4%)^44 - 1 / 4% ]

$2,078,994.75 = A * [ 4.6165151/4%]

$2,078,994.75 = A * 115.41288

A = $2,078,994.75 / 115.41288 = $ 18013.54

Scenario-2

When the last Contribution is at the end of age 64 years.

In this case the target accumulation amount at the end of 64 years will be

$2,078,994.75 / 1.04 = $ 1999033.40 since along with interest of last year i.e. during the year you turn 65 the closing amount should be $2,078,994.75

Applying the same steps as in scenario 1 above except that FVOA is taken as $ 1999033.40 and n = 43

we get the follwoing

$1999033.40 = A* [ (1+4%)^43 - 1 / 4% ] = A* (4.4004953 / 4%) =   $ 18170.99

The above can also be cross verified by the following excel worksheets:(Interest at 4% annual compound)

PART-B

Scenario-1

When the last Contribution is at the end of age 65 years.

The formula for Present value of annuity -

PVA = A * [1 - (1+ r )^-n] / r

where

PVA = 2078994.75

A = Regular deposit yearly

r = 4%

n = 44

Applying the values into the formula we get

2078994.75 = A * [1 - (1+4%)^-44] / 4%

2078994.75 = A * 20.54884

A = $ 101173.30

Scenario-2

When the last Contribution is at the end of age 64 years.

The formula for Present value of annuity -

PVA = A * [1 - (1+ r )^-n] / r

where

PVA = 2078994.75

A = Regular deposit yearly

r = 4%

n = 43

Applying the values into the formula we get

2078994.75 = A * [1 - (1+4%)^-43] / 4%

2078994.75 = A * 20.54884

A = $ 102057.62

The PV table given hereunder shows the values caclulated