Question

You are planning to save for retirement over the next 40 years. To do this, you will invest $900 per month in a stock account and $500 per month in a bond account. The return of the stock account is expected to be 8 percent per year (compounded monthly), and the bond account will pay 6 percent per year (compounded monthly). When you retire, you will combine your money into an account with a 4 percent per year return (compounded monthly). How much can you withdraw each month from your account assuming a 25-year withdrawal period?

Answer 1

FV of an annuity can be calculted by excel formula FV. The inputs of this function are:

• Rate = interest rate per period
• Nper = Number of periods
• PMT = Annuity per period
• PV = present value of the money we start with

Please see the TABLE. Please be guided by the second column titled “Linkage” to understand the mathematics. The cells highlighted in yellow contain important figures. Figures in parenthesis, if any, mean negative values. All financials are in $.

Hence, total accumulated value on retirement = FV of Stock acoount + FV of Bond account = $3,141,907.8705 + $995,745.37 = $4,137,652.42

parameter	linkage	stocks account	bond account
PMT	A	900	500
rate of return	B	8%	6%
rate	C=B/12	0.006666667	0.005
years to maturity	D	40	40
Nper	E = D x 12	480	480
PV	F	0	0
FV	G =FV(B,E ,-F,)	$ 31,41,907.05	$ 9,95,745.37

Amount that can be withdrawn per month can be calculated using the PMT function of excel. Please see the white board. Please be guided by the second column titled “Linkage” to...

parameter	linkage	Retirement account
PV	A	4137652.415
rate of return	B	4%
rate	C=B/12	0.003333333
Withdrawls	D	25
Nper	E = D x 12	300
Amount withdrawl per month	=PMT(C,E,-A)	$ 21,840.05

SO withdrawl per month after retirement = 421,840