UPIN18 2020 In order to receive credit for your answers, use Excel Financial Functions or Algebraic Time Value of Money Equations to answer these questions in your spreadsheet. See General Guide- lines for Excel Group Assignments' in 'Excel Group Assignments' folder on CourseWeb. Please Type the Names of the Group Members at the Top of the First Page of your Workbook. 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/4 BUSFIN 1030: TVM Assignment II What annual deposit would you need in order to accumulate $1,500,000 upon retirement if your IRA earns 7% compounded annually? 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%? 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? 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? UPIN18 2020 In order to receive credit for your answers, use Excel Financial Functions or Algebraic Time Value of Money Equations to answer these questions in your spreadsheet. See General Guide- lines for Excel Group Assignments' in 'Excel Group Assignments' folder on CourseWeb. Please Type the Names of the Group Members at the Top of the First Page of your Workbook. 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/4 BUSFIN 1030: TVM Assignment II What annual deposit would you need in order to accumulate $1,500,000 upon retirement if your IRA earns 7% compounded annually? 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%? 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? 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