Question

Twenty years ago, you invested $10,000 in AAPL at $5.00 per share (split adjusted). Today the stock is trading for $110 per share.  You are now 50 and preparing to retire in ten years at age 60. Assume you need $7,000 per month starting the first month of retirement; you estimate that the $7,000 needs to increase by 3% annually until your life expectancy at age 90.  The annuity will earn an annual rate of 6.0%; use as the discount rate from today until retirement as well. What is the amount of money you need to fully fund retirement TODAY?

Answer 1

amount invested in AAPL = $10,000

share price of AAPL at the time of investment = $5

Number of shares of AAPL received = amount invested / share price = 10000/5 = 2000

Current share price per share = 110

current value of investment = share price X no. of shares = 110 X 2000 = $220,000 ----------------(1)

Annuity return = 6% per year

post-retirement inflation = 3% per year

Inflation adjusted return per year = [(1+6%)/(1+3%)]-1 = 2.9126%

Post-retirement income required = $7,000 per month = $84,000 per year

Time in retirement = 30 years

Retirement amount can be calculated using PV function in spreadsheet

PV(rate, number of periods, payment amount, future value, when-due)

Where, rate = annual inflation adjusted rate = 2.9126%

number of periods = 30

payment amount = annual payment = 84000

future value = 0

when-due = when is the income required each month = beginning = 1

retirement amount = PV( 2.9126%, 30, 84000, 0, 1) = $1,713,690.59 -------(2)

retirement amount in today's terms = retirement amount/ (1+discount rate)¹⁰

= $1,713,690.59/(1+6%)¹⁰

= $956,915.87 -------------(3)

Additional amount needed to fully fund retirement today = (3) - (1) = $956,915.87 - $220,000 = $736,915.87