needs to be in Excel

Problem 2 A father is planning a savings program for his daughter to pay for college. She is 14, she plans to enroll at the university in 4 years, and she should graduate in 4 years. Currently as of July (end of month), the annual cost of college including food, clothing, tuition, books and transportation, and so forth is $33,000, but these costs are expected to increase with inflation. Monthly inflation is given to you in the excel sheet. The college requires that the full tuition for one year is paid on the first day of class at the value of $33,000 adjusted for inflation at the end of August. She now has $55,000 in a college savings account. The college savings account is invested in the S&P 500 and the monthly returns are given to you in the excel sheet. The father wants to make a monthly deposit in the college savings account, the first deposit will be at the end of August and then at the end of each month. Each month he wants to increase the deposit by the average monthly inflation rate of 0.17% per month with the last deposit at the end of July. The discount rate or EAR is 5.9% How large must each deposit be? What is the size of the first deposit? What is the cost of college for the last year (senior year)? What is the total cost of college before his daughter sets foot on campus? (August value) 1 2 Now 3 4 5 6 7 8 10 10 12 13 14 Return as of last day of the month Return Inflation Cost of College College Savings Account ful $33,000.00 $55,000.00 Aug 0.09% O 06% Sep 0.42% 0.07% Oct 2.45% 0.11% Nov 2.32% 0.14% Dec 1.55% 0.14% Jan 3.77% 0.14% Feb -1.51% 0.17% Mar 191% 0.18% Apr 2 10N 0.18% May 0.62% 017 Jun 0.69N 0.13% Jul 4.31% 0.09N Aug -356% 0.1396 2.36% 0.13% Oct 2 BON 0 10% Nov 4,46% 0.08% Dec 2.97% 0.10N Jan -3.13% 0.13 Feb 495 0.17% Mer -1.50% 0.15 Apr 2085 0.12N May 1.81 0.09 Jun 3.60N 0.13 15 Sep 16 17 10 19 20 21 22 23 24 25 A D E G H M C 3.60% 111% 5,04% 0.71% 0.28% -1.98 2.42% 25 25 27 28 29 30 31 32 33 34 35 36 37 38 39 10 41 lun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May lun Jul Aug Sep Oct Nov Dec lan Feb M Apr May un 1.98% 1.26N 3.96% -6279 -0.75 313N 4065 436N 085 -0.51% 10.77 -7.18% -5 58 -2.15% 1,83% 135N 2.85 0 ION 0.13% 0.17% 0.135 0.14% 0.15% 1B 0.17 0.14% 0.12 0.145 0 14% 0.19% 023N 0.24 0.24% 0.255 0.28N 0.29 033 0.32% O SON 0.30N 0.30N 027 023 43 44 45 40 47 49 A D E G H K 1 M 49 unt in yo 51 57 D 0.23% 0.18 0.18 013 OORN ALE Sep Oct ES DION 54 55 > 57 58 59 0 105 3 20 226N 653N -0.23% 360 826 4.74% GBAN 5.39% 8 20% 1489 5889 285% 3.70 1.78% 5.74 -198N NON Dec Jan Feb Mar Agr May Jun Jul Aug Sep Oct NOV Dec Tan Feb Mar Age 61 62 63 61 65 66 67 68 69 70 71 72 73 0103N 0.09 0 10N 0.09% 0.17% 0.18% 0 19 0.189 0.22% 0.18N 0.20 0.239 022 023 02 DON 0.17% 0239 0.29 357% 3.365 7.41 0.02 5.SIN 9.39N 1545 May un A O B E G H 73 74 75 76 77 78 80 31 82 83 84 35 86 87 88 89 90 91 92 93 94 95 05 Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aut Sep Oct Now Dec Jan Feb Mar Apr May Tun 8 54 -10 99% -8.57% 0.78% -7.48% -16.94N 9.08 1.22% -0.99% -8.60% 1.07N 4.755 -0.50N -3.48% -6.12% -0.86% 4.40N 1.48% 3 SBN 1.29 -3.209 178 325 433 100N D 0.29% 0.36% 0.34% 0.36% 0.33% 033% 0.33% 0353 0.42 0.47N 0.45 0.41% 0.31% 0.09% 0.0195 0.00% 0.02N -0.03% -0.06% -0.11% -0.12% -0.18N -0.131 -0.11 -0.02 97 B H Jan Feb Mar Apr May Jun lul AU Sep Oct Nov Dec Jan Feb Mar C 1.22% -0.99% -8 60 1.07% 4.75 -0.60N -3.489 -6.12% -0.86N -4.40N 1.48N 3.58% 1.29% -320N 1.78 3.25N 4.33N 100% -218N 0.489 D 0.35% 0.42% 0.47% 0.45% 0.418 0.31% 0.09N 0.01N OOON 0.02N -0.03% -0.06% 0115 -0.12% -0.18 -0.13N 0.11N -0.02N 0.15% 0239 Apr May Fun Jul Aug Average 0.489 0.17% Problem 2 A father is planning a savings program for his daughter to pay for college. She is 14, she plans to enroll at the university in 4 years, and she should graduate in 4 years. Currently as of July (end of month), the annual cost of college including food, clothing, tuition, books and transportation, and so forth is $33,000, but these costs are expected to increase with inflation. Monthly inflation is given to you in the excel sheet. The college requires that the full tuition for one year is paid on the first day of class at the value of $33,000 adjusted for inflation at the end of August. She now has $55,000 in a college savings account. The college savings account is invested in the S&P 500 and the monthly returns are given to you in the excel sheet. The father wants to make a monthly deposit in the college savings account, the first deposit will be at the end of August and then at the end of each month. Each month he wants to increase the deposit by the average monthly inflation rate of 0.17% per month with the last deposit at the end of July. The discount rate or EAR is 5.9% How large must each deposit be? What is the size of the first deposit? What is the cost of college for the last year (senior year)? What is the total cost of college before his daughter sets foot on campus? (August value) 1 2 Now 3 4 5 6 7 8 10 10 12 13 14 Return as of last day of the month Return Inflation Cost of College College Savings Account ful $33,000.00 $55,000.00 Aug 0.09% O 06% Sep 0.42% 0.07% Oct 2.45% 0.11% Nov 2.32% 0.14% Dec 1.55% 0.14% Jan 3.77% 0.14% Feb -1.51% 0.17% Mar 191% 0.18% Apr 2 10N 0.18% May 0.62% 017 Jun 0.69N 0.13% Jul 4.31% 0.09N Aug -356% 0.1396 2.36% 0.13% Oct 2 BON 0 10% Nov 4,46% 0.08% Dec 2.97% 0.10N Jan -3.13% 0.13 Feb 495 0.17% Mer -1.50% 0.15 Apr 2085 0.12N May 1.81 0.09 Jun 3.60N 0.13 15 Sep 16 17 10 19 20 21 22 23 24 25 A D E G H M C 3.60% 111% 5,04% 0.71% 0.28% -1.98 2.42% 25 25 27 28 29 30 31 32 33 34 35 36 37 38 39 10 41 lun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May lun Jul Aug Sep Oct Nov Dec lan Feb M Apr May un 1.98% 1.26N 3.96% -6279 -0.75 313N 4065 436N 085 -0.51% 10.77 -7.18% -5 58 -2.15% 1,83% 135N 2.85 0 ION 0.13% 0.17% 0.135 0.14% 0.15% 1B 0.17 0.14% 0.12 0.145 0 14% 0.19% 023N 0.24 0.24% 0.255 0.28N 0.29 033 0.32% O SON 0.30N 0.30N 027 023 43 44 45 40 47 49 A D E G H K 1 M 49 unt in yo 51 57 D 0.23% 0.18 0.18 013 OORN ALE Sep Oct ES DION 54 55 > 57 58 59 0 105 3 20 226N 653N -0.23% 360 826 4.74% GBAN 5.39% 8 20% 1489 5889 285% 3.70 1.78% 5.74 -198N NON Dec Jan Feb Mar Agr May Jun Jul Aug Sep Oct NOV Dec Tan Feb Mar Age 61 62 63 61 65 66 67 68 69 70 71 72 73 0103N 0.09 0 10N 0.09% 0.17% 0.18% 0 19 0.189 0.22% 0.18N 0.20 0.239 022 023 02 DON 0.17% 0239 0.29 357% 3.365 7.41 0.02 5.SIN 9.39N 1545 May un A O B E G H 73 74 75 76 77 78 80 31 82 83 84 35 86 87 88 89 90 91 92 93 94 95 05 Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aut Sep Oct Now Dec Jan Feb Mar Apr May Tun 8 54 -10 99% -8.57% 0.78% -7.48% -16.94N 9.08 1.22% -0.99% -8.60% 1.07N 4.755 -0.50N -3.48% -6.12% -0.86% 4.40N 1.48% 3 SBN 1.29 -3.209 178 325 433 100N D 0.29% 0.36% 0.34% 0.36% 0.33% 033% 0.33% 0353 0.42 0.47N 0.45 0.41% 0.31% 0.09% 0.0195 0.00% 0.02N -0.03% -0.06% -0.11% -0.12% -0.18N -0.131 -0.11 -0.02 97 B H Jan Feb Mar Apr May Jun lul AU Sep Oct Nov Dec Jan Feb Mar C 1.22% -0.99% -8 60 1.07% 4.75 -0.60N -3.489 -6.12% -0.86N -4.40N 1.48N 3.58% 1.29% -320N 1.78 3.25N 4.33N 100% -218N 0.489 D 0.35% 0.42% 0.47% 0.45% 0.418 0.31% 0.09N 0.01N OOON 0.02N -0.03% -0.06% 0115 -0.12% -0.18 -0.13N 0.11N -0.02N 0.15% 0239 Apr May Fun Jul Aug Average 0.489 0.17%