2. Enter the appropriate financial function command in cell B7 that solves Problem 14 in Chapter 5. This is the annual capital recovery payment for a 10-year ordinary annuity. 3. Enter the relevant values in cells B11:B20. Hint: Invoke a copy command, being mindful of the issue of absolute versus relative cell addresses. 4. Create an equation in cell c11 that calculates the remaining value of the account at the end of the first year. Create a modified equation in cell c12 and adapt it to cells c13:020, being mindful of the issue of absolute versus relative cell addresses. 5. Now assume that there will be 20 years of annuity payments, as indicated in cell 05. Enter the appropriate financial function command in cell D7 that solves for the annual capital recovery payment for a 20-year ordinary annuity. 6. Enter the relevant values in cells D11:030. Hint: Invoke a copy command, being mindful of the issue of absolute versus relative cell addresses. 7. Create an equation in cell e11 that calculates the remaining value of the account at the end of the first year. Create a modified equation in cell E12 and adapt it to cells E13:30, being mindful of the issue of absolute versus relative cell addresses. 8. Enter the appropriate financial function command in cell B39 assuming annuity due withdrawals are planned for 10 years and the rate of return is 7%. command in cell 039 9. Enter the appropriate financial function command in cell 039 assuming annuity due withdrawals are planned for 20 years and the rate of return is 78. 10. Enter the appropriate financial function command in cell B47 assuming annuity due withdrawals are planned for 10 years and the rate of return is 58. Note that the accumulated value is $100,000, not $80,000. 11. Enter the appropriate financial function command in cell 047 assuming annuity due withdrawals are planned for 20 years and the rate of return is 5%. Note that the accumulated value is $100,000, not $80,000. G20 F G 10 B D E 1 GREAT-UNCLE CLAUDE'S ACCOUNT LIQUIDATION ANNUITY 2 3 4 Accumulated Value: $80,000.00 $80,000.00 20 5 Years annuity provides funds: 6 Projected annual rate of retum: 10.00% 10.00% 7 Annual annuity withdrawal: 8 9 10 End of Year Annual Annuity Compound Value Annual Annuity Compound Value 11 1 12 2 13 3 14 4 15 5 16 6 17 7 18 8 19 9 20 10 21 11 22 12 23 13 24 14 25 15 26 16 27 28 18 29 19 30 20 31 32 33 SF 17 14 15 5 16 6 17 7 18 8 19 9 20 10 21 11 22 12 23 13 24 14 25 15 26 16 27 17 28 18 29 19 30 20 31 32 33 34 Alternative Analysis: Annuity Due 35 36 Accumulated Value: $80,000.00 37 Years annuity provides funds: 10 38 Projected annual rate of retum: 7.00% 39 Annual annuity withdrawal: 40 41 42 43 Alternative Analysis: Annuity Due. Assuming $100,000 Accumulation 44 Accumulated Value: $100,000.00 45 Years annuity provides funds: 10 46 Projected annual rate of retum: 5.00% 47 Annual annuity withdrawal: 48 49 50 51 $80,000.00 20 7.00% $100,000.00 20 5.00% Sheet1 Sheet2 Sheet3