Question

TVM Assignment

Please answer the questions in an excel spreadsheet with the formulas showing.

Part IV: Retirement Planning

You realize the wisdom of starting early at age 22 in saving for your retirement and plan on making 43 equal end of year annual deposits in an IRA account in hopes of having at least $1,000,000 once you retire at age 65 (immediately after your last deposit into the IRA account), but you think it would be best to have $1,500,000 at age 65 to retire. Answer the following questions based on this information.

I What rate compounded annually would you need to earn in order to have $1,500,000 upon retirement if your annual deposit is $4,000?

2. II What annual deposit would you need in order to accumulate $1,500,000 upon retirement if

   your IRA earns 7% compounded annually?

3. III Now you decide you might need more than $1,500,000 in your IRA once you turn 65 given the uncertainty of Social Security. How much money would you have in your IRA at age 65 after 43 end of the year deposits of $5,000 invested at an expected annual return of 8%?

4. IV Assume you carry out the plan described in the last question (#III) and expect to continue to earn an 8% annual return on your IRA account after retirement. Once you reach retirement at age 65, you decide you will live exactly 23 more years. At age 65, you want two things from your retirement fund: $250,000 to leave to your grandkids upon your death in 23 years and a 23-year annuity-due (23 equal annual withdrawals beginning immediately upon retirement). How large can your annual retirement annuity be while still leaving $250,000 to your grandkids upon your death at age 88 if your IRA account continues to earn 8% annually?

5. V In looking back at your answer #IV, you wonder if this retirement annuity will be enough to live on and you worry about potential loss of purchasing power after retirement. Let?s forget about leaving money to the grandkids, they never visit anyway, and make some different retirement income assumptions. Assume you want your first retirement withdrawal, taken immediately upon retirement, to be $145,000 at age 65. Also, let?s assume you want each of the remaining (22) withdrawals to increase by 3% annually. Assume your retirement account will still earn an 8% expected annual return.

   a How much will you need in your retirement account at age 65 to fund this desired retire- ment income stream?

   b How much will you have to deposit annually at the end of each year into your account over the next 43 years to fund this desired retirement stream?

Part V: Retirement Savings

You realize you are entering the last third of your career and you are considering retirement in 10 years. You are in a self-managed defined contribution pension plan and through automatic payroll deduction and employer matching - both based on mandated percentages of your salary - $1,350 per month is currently deposited into your pension plan. Due to the lack of recent raises at your company, you don’t plan on these monthly contributions increasing much, if any, over the next 10 years.

You currently have $545,000 in your pension plan account and you are somewhat concerned if this along with your mandated future $1,350 monthly deposits will adequately fund your retire- ment in 10 years. You are considering supplementing your pension plan by having automatic additional monthly deposits deducted into a 403c retirement plan. you have a monthly deposit amount in mind, but wants additional help in trying to figure out if this amount will be ade- quate. Given your current pension plan portfolio investment mix, you estimate a nominal annual expected return of 7.8% which translates to a 0.65% monthly expected return.

1. I You are considering having $900 per month which is about 10% of your monthly gross (pre- tax) salary deducted and deposited into the 403c for the next 10 years. This is in addition to the $545,000 already in your pension plan today and the estimated $1,350 monthly deposits into your current pension plan. What is the expected total value of your retirement ac- counts after making end of the month deposits of $2,250 for 10 years on top of your current retirement savings of $545,000 at your expected monthly return?

2. II You estimate you will live for 25 years after your planned retirement in 10 years and you want a monthly retirement annuity with the withdrawals at the end of each month once you retire. What is your expected monthly retirement income using your answer from #I and assuming you will continue to earn your expected monthly return after retirement?

3. III Upon hearing the amount of this monthly retirement annuity, you are happy with this figure because it’s higher than your current pre-tax income. However, you wonder if your expected monthly investment return is too optimistic and you want to change it to a 6% nominal annual rate, or 0.5% per month after retirement, but a 7.2% nominal annual rate, or 0.6% before. Also, you want a monthly income of $10,000 once you retire (for 25 years). How much extra above your estimated mandated $1,350 monthly pension plan amount would you need to deposit monthly into the 403c plan over the next 10 years to fund this retirement income goal?

Answer 1

Calculations for I to IV of Part IV:

Note:

S.No. Annual Deposit 4,000.00 6,053.84 5,000.00 Annual withdrawl 1,43,324.07 No. of years Future value Rate of Interest 43 15,00,000.00 431 15.00.000.00 43 16,47,915.03 No. of years Present Value Rate of Interest 23 16,05,336.21 (All amounts are in $) Formula used 8.45% RATE(nper, pmt, pv, [fv], [type], [guess]) 70 IONAT 7% PMT(rate, nper, pv, [fv], [type]) 8% FV(rate,nper,pmt, [pv], [type]) Formula used 8% PMT(rate, nper, pv, [fv], [type]) S.No. IV Note: Future Value at the end of 88 years Present Value at the end of 65 years Rate of Interest Formula used 2,50,000.00 42,578.82 8% PV(rate, nper, pmt, [fv], [type])

• In the table, Future value and present values are at the and of 65 years.
• In S.No. IV, $ 1647915.03 (Approx) is reduced by $ 42578.82 ( PV at the end of 65 years for the value $ 250000 at end of 88 years) as one has planned to give $ 250000 to Grand children at the end of 88 years.
• All the values appearing are approximate, as only two decimal places are visible.
• In S.No. IV, [type] is to be taken as 1, since one starts withdrawing the amount immediately after the retirement i.e., at the beginning of every year for 23 years.