Financial Planning using Excel Data Table: 1 variable

Ali is about 8 years away from retirement. He is trying to calculate how much money he needs to have before he can retire. Being a good friend and an Excel expert, you are helping him with numbers. Ali is making some simple assumptions as noted below:

A. Ali plans to invest all his savings in a fixed income plan earning a steady return of 4% per year. He plans to withdraw the interest, so the principal amount will not change from year to year. Ali does not want to touch the principal amount. He wants to pass it to his two children.

B. In addition, Ali will have a fixed $5000 income from Social security every year as long as he lives.

C. He expects his annual living expense to be $60,000 per year after retirement, but anticipates that the expense will go up by 3% every year due to inflation.

D. He also wants to leave $10,000 for his own funeral expenses.

Calculate how much total savings Ali needs if he lives 10 years after retirement. Next, vary Ali's expected life after retirement from 8 to 25 year (with 1 year increment). Then use Excel Data table to find out how much money Ali needs to have for each expected life span. Hint: Equate Ali's total income and expense over the life span (L). Assume Ali's savings at the beginning of retirement will be given by S. So, Ali's annual Income = S * 4% + 5000 (no compounding since he is withdrawing the interest) In L years, it will be = (L) (0.04S+5000) Recurring expense in L years = (60000) (1+3%) ^ L Total expense including funeral cost: (60000)(1+3%) ^ L + $10,000 So, equating Income and expense, (L) (0.04S+5000) = 60000(1+3%) ^ L + $10,000 Solve for S

3. Financial Planning using Excel Data Table: 2 variables Change the previous calculation to a 2 variable problem. Use the same 8-25 year life span after retirement. Also vary the return on savings from 3 to 8%. Create 10 such rates using RandBetween function. Then find out how much savings Ali needs for each combination of life span and rate of investment return.