Question

You are planning to save for retirement over the next 30 years. To do this, you will invest $750 per month in a stock account and $250 per month in a bond account. The return of the stock account is expected to be 10 percent, and the bond account will pay 6 percent. When you retire, you will combine your money into an account with a return of 5 percent.

How much can you withdraw each month from your account assuming a 25-year withdrawal period? (Do not round intermediate calculations and round your answer to 2 decimal places, e.g., 32.16.) Withdrawal_______ per month

Answer 1

1st we will calculate the corpus created during the saving phase

Saving is in 2 ways, 1st $750 per month in stock with the expected return of 10% for 30 years

So Future value of the same can be calculated by using FV function of Excel, where

Nper (no. of period) = 30 Years or 30*12 Months

PMT (payment) = $750 per month

Rate = 10% annual or 10%/12 per month

Type = 0 (assuming the payment end of the month)

The FV function argument will be like as below :-

so the corpus collected through stocks is $1695365.94359704

1st we will calculate the corpus created during the saving phase

Similarly the 2nd saving is in bond by $250 per month with the expected return of 6% for 30 years

So Future value of the same can be calculated by using FV function of Excel, where

Nper (no. of period) = 30 Years or 30*12 Months

PMT (payment) = $250 per month

Rate = 6% annual or 6%/12 per month

Type = 0 (assuming the payment end of the month)

The FV function argument will be like as below :-

so the corpus collected through stocks is $251128.760613144

Total corpus collected at the time of retirement = Stock Corpus + Bond Corpus

= $1695365.94359704 + $251128.760613144

= $1946494.70421018

This corpus will earn 5% return annually

Withdrawal per month can be calculated by using the PMT function in excel.

Nper (no. of period) = 25 Years or 25*12 Months

Rate = 5% annual or 5%/12 per month

PV = $1946494.70421018 (as this will be PV of retirement corpus collected through Stock and Bond )

Type = 0 (assuming the payment end of the month)

The PMT function argument will be like as below :-

so the per month withdrawal will be $11379.0141992929 or $11379.01