You are planning to save for retirement over the next 30 years. To do this, you

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 11 percent per year, and the bond account will earn 6 percent per year. When you retire, you will combine your money into an account with an annual return of 8 percent. How much can you withdraw each month from your account assuming a 25-year withdrawal period? Could you list step by step on how to answer it using a calculator

Explanation / Answer

Future value of investment in stock market

Using future value function in MS excel

fv(rate,nper,pmt,pv,type) rate =11%/2 =.9166% nper =12*30 = 360 pmt =- 750 pv =0 type

fv(.9166%,360,-750,0,0)

$2,103,023.12

Future value of investment in bond market

Using future value function in MS excel

fv(rate,nper,pmt,pv,type) rate =6%/2 =.5% nper =12*30 = 360 pmt =- 250 pv =0 type

fv(.5%,360,-250,0,0)

$251,128.76

total value of investment at time of retirement

$2,354,151.89

Amount of monthly amount to be withdrawn from accumulated fund

Using PMT function in MS excel

pmt(rate,nper,-pv,fv,type) rate = .666% nper = 12*25 = 300 pv =-2354151.89 fv = 0 type = 0

PMT(0.666%,300,-2354151.89,0,0)

$18,157.25

Future value of investment in stock market

Using future value function in MS excel

fv(rate,nper,pmt,pv,type) rate =11%/2 =.9166% nper =12*30 = 360 pmt =- 750 pv =0 type

fv(.9166%,360,-750,0,0)

$2,103,023.12

Future value of investment in bond market

Using future value function in MS excel

fv(rate,nper,pmt,pv,type) rate =6%/2 =.5% nper =12*30 = 360 pmt =- 250 pv =0 type

fv(.5%,360,-250,0,0)

$251,128.76

total value of investment at time of retirement

$2,354,151.89

Amount of monthly amount to be withdrawn from accumulated fund

Using PMT function in MS excel

pmt(rate,nper,-pv,fv,type) rate = .666% nper = 12*25 = 300 pv =-2354151.89 fv = 0 type = 0

PMT(0.666%,300,-2354151.89,0,0)

$18,157.25

Related Questions

Navigate

• Browse (All)
• Browse Y
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.