Question

Retirement Paths Compare the following retirement paths. For each retirement plan assume the individuals need 2.5 million dollars to retire. Assume a rate of return of 8% APR, and current balance of $0. Plan A: start making monthly contributions immediately (annuity due) for the next 35 years. Calculate the monthly contribution needed to achieve the retirement goal. Create a column illustrating the monthly account balance. Plan B: start making monthly contributions 10 years from today, (t=120 is first payment) and have the same retirement date as Plan A. Calculate the monthly contribution needed to achieve the retirement goal. Create a column illustrating the monthly account balance. Plan C: suppose you make the same monthly payment as Plan A, and have the same monthly contribution horizon as Plan B, meaning you start 10 years late. Calculate the retirement shortfall, and create column illustrating the monthly account balance. Plan D: redo Plan A, however, assume you have hired a financial advisor to help you invest. The advisors fee is 1% NAV. Calculate the retirment shortfall, and then recalculate the monthly contributions need under this plan. Plan E: start making monthly contributions beginning at the end of the first month for the next 35 years. The first monthly contribution is the same as Plan A. Each year you increase your monthly contributions by 1% (e.g. CF1 – CF12 are constant, C13 – CF24 are 1% greater than CF1). How quickly will you reach your retirement goal? How much more will you have if you make the contributions for the entire 35 years?

Answer 1

Since we need to show a separate colulmn with monthly balances, we will do these calculations in the excel sheet.

Plan A: We will first calculate the monthly contribution using the future value of annuity due formula as below:

FV =

where the FV is the future value (2.5 million) , r is the monthly interest rate (8%/12) and t is the time in months (35*12). Plugging in the values we get the Monthly Contribution under Plan A should be $ 1082.638. The excel sheet with monthly balances is as below:

?In case this excel sheet is not clear (since it is a large worksheet - pls make one like this : First column Months, second column Annuity Contribution of 1082.638, third column monthly interest rate of (8%/12) and fourth colun is the interest earned during the month (product of second and third column) and fifth column is Monthly Balance (sum of second and fourth column). In row 2, second column the amount should be the previous monthend balance from row 1 fifth column and add $ 1082.638 which is the Month 2 annuity contribution. Now just extrapolate / drag this formula worksheet till month 420 and you will have the desired results. The final amount will be $2500000.64 due to rounding off in the monthly contribution.

Plan B: We use the same FV annuity due formula but with t as (25*12) = 300 months. Then the monthly contributon required for $2.5 million will be = $ 2611.329. The monthly account balance worksheet is as below:

?Note that the last month balance 2499999.09 which is due the rounding up of decimal numbers .

Plan C: At the monthly contribution of Plan A and time period of Plan B, the final amount will be $ 1,036,481.43 only. We get this amount by plugging in the FV of annuity due formula the monthly contribution of $ 1082.638 and t of 300 months, as below:

? = $ 1036481.43. The monthly balance worksheet is as below:

?Plan D: We can modify the Plan A worksheet and add a column where we charge 1% p.a. of monthly account balance as advisor fee and deduct it from the monthly balance . Since the 1% is p.a. we will divide it by 12 and then multiply it to the monthly balance. The monthly balance to be carried forward to next month for interest earning will be net of advisor fee. We see in the worksheet that if we charge the advisor fee of 1% , the final balance is only $ 1,958,141.56 which is shortfall of $ 541858.44. We can also rework this same worksheet and use goal seek to arrive at the monthly contribution needed with 1% fee to reach $2.5 million which will be = $1382.226

Plan E: In this case the monthly contribution is from end of first month and amount is $ 1082.638 but the contribution increases each year by 1%. In this case the target of $ 2.5 million n month 398 which is 22 months earlier. By the end of 35 years this pool will become $2,937,437.80