value: 1.00 points Problem 6-32 Calculating Annuities [LO1 You are planning to s

Question

value: 1.00 points Problem 6-32 Calculating Annuities [LO1 You are planning to save for retirement over the next 35 years. To do this, you vwill 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 11 percent, and the bond account will pay 7 percent When you retire, you will combine your money into an account with a 8 percent return. How much can you withdraw each month from your account assuming a 30-year withdrawal period? (Do not round intermediate calculations and round your answer to 2 decimal places, e.g, 32.16.) per month Withdrawal ReterenceseBook & Resources Worksheet Problem 6-32 calculating Annulties ILO11

Explanation / Answer

Future value of investment in stock at the time of retirement

Using future value function in MS excel

fv(rate,nper,pmt,pv,type) rate = 9/12 = .75% nper = 35*12 = 420 pmt = -900 pv =0 type =0

FV(0.75%,420,-900,0,0)

$2,647,606.03

Future value of investment in bond at the time of retirement

Using future value function in MS excel

fv(rate,nper,pmt,pv,type) rate = 7/12 = .75% nper = 35*12 = 420 pmt = -500 pv =0 type =0

FV(0.5833%,420,-500,0,0)

$900,441.49

total future value of investment at time of retirement

$3,548,047.52

PMT = Monthly annuity payment

Using PMT function in MS excel

pmt(rate,nper,pv,fv,type) rate = 8/12 = .666 nper = 12830 = 360 pv = -3548047.52 fv =0 type =0

PMT(0.666%,360,-3548047.52,0,0)

$26,014.53

Future value of investment in stock at the time of retirement

Using future value function in MS excel

fv(rate,nper,pmt,pv,type) rate = 9/12 = .75% nper = 35*12 = 420 pmt = -900 pv =0 type =0

FV(0.75%,420,-900,0,0)

$2,647,606.03

Future value of investment in bond at the time of retirement

Using future value function in MS excel

fv(rate,nper,pmt,pv,type) rate = 7/12 = .75% nper = 35*12 = 420 pmt = -500 pv =0 type =0

FV(0.5833%,420,-500,0,0)

$900,441.49

total future value of investment at time of retirement

$3,548,047.52

PMT = Monthly annuity payment

Using PMT function in MS excel

pmt(rate,nper,pv,fv,type) rate = 8/12 = .666 nper = 12830 = 360 pv = -3548047.52 fv =0 type =0

PMT(0.666%,360,-3548047.52,0,0)

$26,014.53

Related Questions

Navigate

• Browse (All)
• Browse V
• Subjects

---

---

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