Question

I ONLY NEED HELP IN SOLVING QUESTION #4, I'VE DONE THE REST JUST NEED FORMULAS TO #4. PLEASE SHOW WORK, I HAVE TO TURN IT IN ON EXCEL. THANK YOU.
Matt and Debra Baxter live in an upscale neighborhood in Orem, Utah. Matt is a partner in the family owned business. Debra stays home with their child, Brady, who is age 5. After visiting with their financial planner, the couple became concerned that they were spending too much and not putting enough funds aside for Brady's future educational needs. Matt earns $85,000 per year, but with the rising costs of education, they are concerned. Matt is an alumni of Duke University, a prestigious school with tuition and book expenses of approximately $18,000 per year. Debra graduated from Utah Valley University. The expense for tuition and books there is estimated at about $8,000 per year. When Brady turns 18, the couple wishes to send him to one of these two exceptional universities. They have a slight preference for Utah Valley University. The problem, however, is that with the rate at which tuition is increasing the Baxter's are not sure they can save enough money and they have decided they do not want to borrow to pay for Brady's education. Assume the tuition at both universities will increase at an annual rate of 5% from now until Brady starts college. Living expenses are currently estimated to be $9,000 per year at both schools. This expense is expected to increase at only 3% per year. Further, assume that Baxter's can deposit their money into a growth oriented mutual fund which has historically earned 12% per annum. The couple wishes to save by having a pre-determined amount automatically withdrawn from their bank account at the end of each month. They plan to contribute from now until Brady starts college. When Brady starts college, at the beginning of his freshman year, they will stop making contributions. They want to have enough in their account when Brady starts college so the principle and interest will cover all four years of his college expenses. They will make annual withdrawals from the account to cover both tuition and living expenses for Brady at the beginning of his freshman, sophomore, junior, and senior years. When the withdrawal for the senior year is made the account balance will be zero. Complete a thorough analysis and write a professional letter to the Baxter's (who don't understand finance) explaining the analysis you performed, why you performed it, and the results and conclusions. In the letter and attached schedules provide information that answers the following questions

Answer 1

As requested i am providing answer for question 4 in below screenshots. I have provided complete working with visible formulas in excel. Below are my workings:

Brady will join Duke University after 13 years on attaining age of 18 (5+13-18 years) DUKE University Total Freshmaan Sophomore Junior Senior Fees now (a) 18000 18000 18000 18000 Inflation (1+r)An (b) Fees then $ (a)*(b) Value at year 1st year of university Value at year 1st year of university (A) (1+5%)^13 (1+5%)A14 (1+5%)A15 (1+5%)A16 39,292 33,942 35,639 37,421 37421/(1+9% )A2 39292/(1+9% )A3 (35639/(1+9% ) 33,942 1,28,475 33,942 32,696 31,497 30,341 Living Expenses (p) Inflation (1+r)An (q) Expenses then $ (p)*(q) 9000 9000 9000 9000 (1+3% )A15 (1+3%)A13 (1+3%)A14 (1+3%)A16 14,022 13,217 13,613 14,442 Value at year 1st year of university (B) 1,03,430 13,217 32,696 29,997 27,520 Total Funds required at year 1 beginning (A)+(B) 2,31,905 Now As we know the want to invest in monthly investments for the next 13 years which comes to 156 months @9% For calculating SIP formula is FV- (P*((1+r)An)-1)} *(1 +r)/r here r-Rate of Return, Pis SIP amount, FV is desired amount, n is period 0.0075 r 156 n FVr/(1+r)/1+r)An)-1)) For P Solving 782

Brady will join Utah University after 13 years on attaining age of 18 (5+13-18 years) UTAH University Total Freshmaan Sophomore Junior Senior Fees now (a) 8000 8000 8000 8000 Inflation (1+r)An (b) Fees then $ (a)*(b) Value at year 1st year of university Value at year 1st year of university (A) (1+5 % )A14 (1+5%)A13 (1+5%)A15 (1+5%)A16 15,085 15,839 16,631 17,463 16631/(1+9% )A2 17463/(1+9% )A3 15,085 (15839/(1+9%) 15,085 14,531 13,998 13,485 57,099 Living Expenses (p) Inflation (1+r)^n (q) 9000 9000 9000 9000 (1+3 %)A16 (1+3%)15 (1+3%)A13 (1+3%)A14 Expenses then $ (p) (q) Value at year 1st year of university (B) 13,217 13,613 14,022 14,442 1,03,430 13,217 32,696 29,997 27,520 Total Funds required at year 1 beginning (A)+(B) 1,60,529 Now As we know the want to invest in monthly investments for the next 13 years which comes to 156 months @9% For calculating SIP formula is FV=(P*((1+rAn)-1)) *(1+r) /r here r-Rate of Return, P is SIP amount, FV is desired amount, n is period 0.0075 156 n FV*r/(1+r)/(1+r)An)-1)) For P Solving 541