please use excel formulas

4. Your five-year-old daughter has just announced that she would like to attend college. The College Board has reported that the average cost of tuition, room, board, and other expenses at public four-year colleges is $21,370 in the 2018-2019 academic year.* The cost has risen 2.8% over the last year. You believe that you can earn a rate of 8% on investments to meet this goal. a. If costs continue to rise at 2.8% per year, how much will it cost for the first year of tuition in 13 years? b. Assuming that you plan to have enough money saved in 13 years to cover all four years of college costs, how much will you need to have accumulated by that time? Note that the cost of tuition, room, and board is a graduated annuity growing 2.8% per year, and assume that you will pay all costs at the beginning of each year. c. If you were to invest a lump sum today in hopes of covering your daughter's college costs, how much would you have to invest? d. If you decided to invest annually instead, how much would you have to invest each year? What if you make investments monthly? e. You just learned of a $10,000 inheritance and plan to invest it in your daughter's college fund. Given this new source of funds, how much will you now have to invest each year? A B Formula Bar E F K S S 21,370 2.80% 30,600 8% 13 4 10,000 S Period 1 2 3 4 5 s 6 7 8 9 10 11 12 13 14 15 16 PV G H I J Time Line When Cash Flows Start in Period 1 Cash Flow Cash Flow Cash Flow Cash Flow $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 30,599.55 31,456.34 - 32,337.11 33,242.55 $41,858.24 $0.00 $10,000.00 $0.00 - Cash Flow $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $10,000.00 . 1 Cost of College Today 2 Annual Rate of Increase 3 First Year Cost of College 4 Annual Rate of Return 5 Years until college 6 Years in College 7 Inheritance 8 9 Assuming First Cash Flow in Period 1 10 Amount needed in 12 years 11 Lump sum required today 12 Annual investment required (ending in year 12) 13 Annual investment with inheritance (ending in year 12) 14 Annual investment required (ending in year 16) 15 Annual investment with inheritance (ending in year 16) 16 17 18 19 20 21 22 23 22 24 Assuming First Cash Flow in Period 0 25 Amount needed in 13 years 26 Lump sum required today 27 Annual investment required (ending in year 12) 28 Annual investment with inheritance (ending in year 12) 29 Annual investment required (ending in year 15) 30 Annual investment with inheritance (ending in year 16) 31 32 33 Source: College Board. Retrieved 1 July 2019 34 35 36 37 38 39 40 41 42 43 Period 0 1 2 3 4 5 6 7 8 9 10 II 12 13 14 15 16 PV Time Line When Cash Flows Start in Period 0 Cash Flow Cash Flow Cash Flow Cash Flow Cash Flow $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 S0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 30,599.55 $0.00 $0.00 31,456.34 $0.00 $0.00 32,337.11 $0.00 $0.00 33,242.55 $41,858.24 $0.00 $10,000.00 $0.00 $10,000.00 4. Your five-year-old daughter has just announced that she would like to attend college. The College Board has reported that the average cost of tuition, room, board, and other expenses at public four-year colleges is $21,370 in the 2018-2019 academic year.* The cost has risen 2.8% over the last year. You believe that you can earn a rate of 8% on investments to meet this goal. a. If costs continue to rise at 2.8% per year, how much will it cost for the first year of tuition in 13 years? b. Assuming that you plan to have enough money saved in 13 years to cover all four years of college costs, how much will you need to have accumulated by that time? Note that the cost of tuition, room, and board is a graduated annuity growing 2.8% per year, and assume that you will pay all costs at the beginning of each year. c. If you were to invest a lump sum today in hopes of covering your daughter's college costs, how much would you have to invest? d. If you decided to invest annually instead, how much would you have to invest each year? What if you make investments monthly? e. You just learned of a $10,000 inheritance and plan to invest it in your daughter's college fund. Given this new source of funds, how much will you now have to invest each year? A B Formula Bar E F K S S 21,370 2.80% 30,600 8% 13 4 10,000 S Period 1 2 3 4 5 s 6 7 8 9 10 11 12 13 14 15 16 PV G H I J Time Line When Cash Flows Start in Period 1 Cash Flow Cash Flow Cash Flow Cash Flow $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 30,599.55 31,456.34 - 32,337.11 33,242.55 $41,858.24 $0.00 $10,000.00 $0.00 - Cash Flow $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $10,000.00 . 1 Cost of College Today 2 Annual Rate of Increase 3 First Year Cost of College 4 Annual Rate of Return 5 Years until college 6 Years in College 7 Inheritance 8 9 Assuming First Cash Flow in Period 1 10 Amount needed in 12 years 11 Lump sum required today 12 Annual investment required (ending in year 12) 13 Annual investment with inheritance (ending in year 12) 14 Annual investment required (ending in year 16) 15 Annual investment with inheritance (ending in year 16) 16 17 18 19 20 21 22 23 22 24 Assuming First Cash Flow in Period 0 25 Amount needed in 13 years 26 Lump sum required today 27 Annual investment required (ending in year 12) 28 Annual investment with inheritance (ending in year 12) 29 Annual investment required (ending in year 15) 30 Annual investment with inheritance (ending in year 16) 31 32 33 Source: College Board. Retrieved 1 July 2019 34 35 36 37 38 39 40 41 42 43 Period 0 1 2 3 4 5 6 7 8 9 10 II 12 13 14 15 16 PV Time Line When Cash Flows Start in Period 0 Cash Flow Cash Flow Cash Flow Cash Flow Cash Flow $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 S0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 $0.00 30,599.55 $0.00 $0.00 31,456.34 $0.00 $0.00 32,337.11 $0.00 $0.00 33,242.55 $41,858.24 $0.00 $10,000.00 $0.00 $10,000.00