Complete the following 4 questions (a thrud) Your plan to retire 5 years from today. At the end of year 5, you would like to receive your first annual retirement payment $28,000 You would like your retirement payments to increase 2.8% each year during your retirement (years 6 - 10), at which time your retirement account will be depleted. Assume your retirement fund account earns 6.2% per year each prior to retirement and 3.40% each year following retirement. INPUT DATA First Retirement Payment Annual Retirement Payment Growth Rate Rate of return on fund PRE-retirement Rate of return on fund POST-retirement (years 6 - 10) (years 1-4) (years 5 - 10) ** Enter Input values provided in the above description Into the input area column L) and reference these cells when completing tasks a thru d in the space provided. a) Complete the table (range G12:L12) to depict the annual retirement payment received in each year (years 5 thru 10). Year 0 1 2. 3 a 5 7 10 b) Calculate the value needed in your retirement account at the end of year 4 to fund your post-retirement payment. Place your work in the range A16:H23. HINT: what rate do you earn post-retirement? c) You plan to open a retirement account TODAY and make a single deposit. Calculate the amount you need to deposit today to fund the post-retirement payments. Place your work in the range B28:H32. d) Show the annual balance in your retirement account - you will need to account for the annual deposits, retirement payment withdrawals and interest earned on the account balance. Assume you will make your first annual payment at the end of year 1. Place your work in the range B34:L34. 0 2 10 Year Balance Complete the following 4 questions (a thrud) Your plan to retire 5 years from today. At the end of year 5, you would like to receive your first annual retirement payment $28,000 You would like your retirement payments to increase 2.8% each year during your retirement (years 6 - 10), at which time your retirement account will be depleted. Assume your retirement fund account earns 6.2% per year each prior to retirement and 3.40% each year following retirement. INPUT DATA First Retirement Payment Annual Retirement Payment Growth Rate Rate of return on fund PRE-retirement Rate of return on fund POST-retirement (years 6 - 10) (years 1-4) (years 5 - 10) ** Enter Input values provided in the above description Into the input area column L) and reference these cells when completing tasks a thru d in the space provided. a) Complete the table (range G12:L12) to depict the annual retirement payment received in each year (years 5 thru 10). Year 0 1 2. 3 a 5 7 10 b) Calculate the value needed in your retirement account at the end of year 4 to fund your post-retirement payment. Place your work in the range A16:H23. HINT: what rate do you earn post-retirement? c) You plan to open a retirement account TODAY and make a single deposit. Calculate the amount you need to deposit today to fund the post-retirement payments. Place your work in the range B28:H32. d) Show the annual balance in your retirement account - you will need to account for the annual deposits, retirement payment withdrawals and interest earned on the account balance. Assume you will make your first annual payment at the end of year 1. Place your work in the range B34:L34. 0 2 10 Year Balance
