Question

You are doing some long-range retirement planning. On the day you retire (23 years from now) you want to be able to withdraw $200,000. Then, you want to withdraw the following amounts at the end of each year after that (during your retirement period).

                           Years 1-4            $160,000

                           Years 5-9            $175,000

                           Years 10-15       $165,000

                           Years 16-26       $145,000

At the end of the 26^th year in retirement, you’d like to have $500,000 remaining in your retirement account available for withdraw. During your retirement years, you anticipate earning a 4.5% rate of return.

You currently have $275,000 that you are going to use to start your retirement savings today. In addition, you plan to save $700 at the end of each month for the next 8 years. At that point (8 years from today) you will add another $150,000 to your retirement fund. Then, over the remaining 15 years, how much must you save at the end of each month to reach your goal if you earn 8.9% as a rate of return during the first 8 years and 7.6% over the final 15 years in which you are saving for retirement?

Answer 1

We will start from the after retirement period and bring the funds required for withdrawals to period at the time retirement.

We will call that year as At the time of retirement and at that we need 200,000 for withdrawal. Rate of return = 4.5%

Next, the funds to be withdrawan at the end of each in 1-4 years which 160,000. This is an Annuity and as funds are to be withdrawan at the end of years, hence, time period will be 5 years as full 4th year is included.

Annuity formula = C/r * (1 - (1 + r)^-n)

PV of 1-4 years funds = Using Annuity formula and PV function in excel = 574,004.11

PV of 5-9 years funds of 175,000 at the end of each year will be calculated first with Annuity formula for 5 years to bring it at beginning of Year 5 and then discount for 4 years to bring the PV at Year 0

PV of 5-9 years funds at year 0 = 644,221.34

Similar to 5-9 years, PV of 10-15 years funds of 165,000 at the end of each year will be calculated first with Annuity formula for 6 years to bring it at the beginning of Year 10 and then discount for 9 years to bring the PV at Year 0

PV of 10-15 years funds at year 0 = 598,444.97

Next PV of 16-26 years funds of 145,000 at the end of each year will be calculated with Annuity first for 11 years to bring it at the beginning of Year 16 and then discount for 15 years to bring PV at Year 0

PV of 16-26 years funds at year 0 = 639,024.53

Lastly, we need to bring 500,000 which is required at the end of 26th year and bring it to Year 0 by discounting for 26 years as Present Value

PV of 26th Year retirement fund = 159,201.24

Total all the PVs at 0 = 574,004.11 + 644,221.34 + 598,444.97 + 639,024.53 + 159,201.24 = 2,614,896.19

The Total PV calculated above is the amount required at the time of retirement.

Now going to the time when we need to start saving. First payment is of 275,000 and 700 monthly for 8 years.

The rate of return for first 8 years is 8.9%

First calculate Future Value of 275,000 at 8.9% rate = 275000 * (1+8.9%)⁸ = 543,945.93

Next calculate FV of monthly payment of 700 using FV of annuity formula or FV function in excel (using monthly return rate 8.9% /12)

FV of 700 payment = 98,192.69

Total FV at 8 years = 543,945.93 + 98,192.69 = 642,138.62

We added additional funds of 150,000 and want to make monthly payment to achieve our goal of 2,614,896.19 at the time of retirements

Total funds at Year 8 = 642,138.62 + 150,000 = 792,138.62

Now to calculate the monthly payment required to reach our goal for next 15 years at a rate of 7.6%, we need to bring 2,614,896.19 to Year 8 by discounting it for 15 years at 7.6% discount rate.

PV of Retirement funds needed at year 8 = 2,614,896.19 / (1+7.6%)¹⁵ = 871,506.00

At year 8, we already have 792.138.62 and we need to reach 871,506, hence, we need 79,367.28 Present value of an annuity through monthly payments.

Using the PV of Annuity formula, find the payment required to reach the PV of 79,367.38 or use PMT function on Excel (used Rate as 7.6% / 12)

Monthly payment for next 15 years = 740.26