I am confused on where to begin

B D E F G H 12 You are going to construct a calculator that finds the amount needed to be saved every year until your student starts college based on the following information. When done your calculator should solve for the yearly savings needed based on the following inputs: H ROR Expected (the expected rate of return on your investments) Education Inflation Rate (the expected inflation rate for college expenses) 15 6 Wage Inflation Rate (the rate you expect your salary to grow) - Years Until College Starts (self explanatory) 7 - Current Annual Cost of College (the current out of pocket estimate if college started now) 8 9 10 I have populated the 5 variables above with some sample data to make it easier to visualize while you build your calculator. 11 12 13 14 We are going to deploy some of the things we learned in the TVM module to figure out what SERIAL PAYMENT needs to be made each year if we want the payment to grow along with our salary (wages) and we want to start saving today (begin mode). We also want to have amassed all funding needed so that once college starts we no longer have to be putting aside any more money. Using proper Excel equation entering techniques, populate the cells from C12 to C24 with the equations needed to find the correct answer. 15 16 17 1. Use the FV function to find the estimate for out of pocket expenses when college starts (cell C12) Use the Education Inflation Rate and the number of years until college starts. 18 19 2. In cells C13-C15 enter equations to find the out of pocket expected costs for years 2-4 of college. Grow each year by the Education Inflation Rate.. 20 21 22 3. In cell C16 find the total amount we need at the start of college to fund all 4 years. Hint: it is NOT simply the sum of cells C12-C15. Remember we are earning a ROR on that invested money all through college. Therefore you need to find the Present Value (PV) of those 4 years of college expenses, discounted at our ROR expected. The cleanest solution is to use the NPV function, however you can always solve this as the sum of the 4 individual PV solutions. Either one works. 23 24 25 26 4. Since we are dealing with SERIAL PAYMENTS we need to use the FV in TODAY'S DOLLARS for the funding need. In cell C20 convert the FV to TODAY'S DOLLARS by discounting it back with the PV equation. We use the Wage Inflation rate for this type of PV calculation. Refer to Lecture 3 in Module 3 if you need a refresher on this topic. 27 28 29 30 6. When figuring the PMT on Serial Payments we use an IARR so calculate the IARR for this problem in cell C22. The IARR in this case uses the ROR Expected and the Wage Inflation Rate since the payments are assumed to grow at Wage Inflation. 31 7. Solve for the PMT using the proper Excel function in cell C24 32 33 34 35 36 37 38 34 AWN M a TICHE A MASSKARAZARA 16 17 18 19 20 21 22 23 24 25 26 27 Audit 28 29 30 31 BAXPX 32 31 36 Inputs: B ROR Expected Education Inflation Rate Wage Inflation Rate Years Until College Starts Current Annual Cost of College Future Cost of College Year 1 Future Cost of College Year 2 Future Cost of College Year 3 Future Cost of College Year 4 Total Needed at College Start Calculations for Serial Payment: PV of Funding Need IARR Payment to Meet Goal DO NOT ALTER ANYTHING BELOW THIS ROW #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A Calculations for Funding Goal: Future Cost of College Year 11 Future Cost of College Year 2 Future Cost of College Year 3" Future Cast of College Year 4 Total Needed at College Start" PV of Funding Need IARR Payment to Meet Goal Instructions 9.00% 6.00% 3.00% 18 $8,000 growing each year by 3.00% E