Question

Problem 5-36 Suppose that you were to receive a $30,000 gift upon graduation from your master's degree program, when you turn 31 years old. At the end of each working year for 34 years, you put an additional $5,000 into an IRA a. Assuming you earn an annual compounded rate of 7.5 percent on the Rift and the IRA investments, how much would be available when you retire at age 65? b. If you hope to draw money out of that investment at the end of every month for 30 years following retirement, how much could you withdraw each month? Assume that during the years you are retired, the money earns an annual rate of 6 percent compounded monthly You realize that if you draw out that amount each month there will be nothing left for your two children. You decide that you want to leave $250,000 to each of your children 30 years after you retire. How much would you have to invest at your retirement to fund your children's Inheritance? Assume that you will earn 7.5 percent compounded annually on the money invested for your children d. If you set aside the money for your children, how much could you draw out each month during your retirement if you can earn 6 percent per annum compounded monthly on the portion that is not set aside for the children? MacBook Air

Answer 1

Please see the table below. Figures in parenthesis, if any, mean negative values. All financials are in $. Adjacent cells in blue contain the formula in excel I have used to get the final output. I have maintained the sequence of calculation exactly i the same manner as you have given so that you can relate to it easily.

7 INPUT DATA 30,000 5,000 34 7.50% 8 Part A 9 Value of the gift - PV 10 IRA investment annuity - PMT 11 Years working - nperi 12 Annual return while working - rate 1 13 Part B 14 Years retired 15 Months retired - nper 2 16 Annual return while retired 17 Monthly return while retired - rate 2 18 Part C 19 Number of children 20 Amount per children 21 Total inheritance - FV 30 360 =12*014 6% 0.005'=C16/12 250,000 500,000 =C19*C20 $1,063,557 '=FV(C12,011,-C10,-C9,0) $6,376.56 '=PMT(C17, C15,-C26,0,0) 23 SOLUTIONS 24 Part A - Amount available for retirement 25 '=FV(rate, nper, pmt, pv, fv, type) 26 Retirement funds - FV 27 Part B - Monthly Retirement Income 28 =PMT(Rate, Nper, PV, FV, type) 29 Monthly retirement income - PMT 30 Part C-Amount for children's inheritance 31 =PV(Rate, Nper, PMT, FV, type) 32 Amount set aside at retirement - PV 33 Part D - Retirement income after inheritance 34 Original retirement funds 35 Less: Amount for children 36 Net retirement funds - PV 37 =PMT(Rate, Nper, PV, FV, type) 38 Revised monthly income - PMT 39 Monthly drop in retirement income $57,110.52 =PV(C12, C14,0,-C21,0) $1,063,557 '=C26 $57,110.52 '=C32 $1,006,446.82 =C34-C35 $6,034.16 =PMTC17, C15,-C36,0,0) $342.41 '=C29-C38