Question
Question: I need assistance in figuring out how to solve for a problem (step-by-step) so I can understand and explain it to another person. I need to be able to demonstrate what my recommendation is, as well as how I arrived at it. For reference, it's for educational planning.
Assumptions:
-There are two children, age 8 and age 6. Both will need projections.
-$28,000 can be invested the first year for both of them ($56,000 total).
-$12,000 can be invested the second year for both of them ($24,000 total).
-In the third year until they reach the age of 18, I need to figure out how much to tell the parents to invest per year to reach their funding goal.
-Assume the rate of return of investment is 7%
-Assume the rate of inflation on tuition is 4.5%
-Assume that in today's dollars they'll need $35,000 per annum for 4 years.
Tips:
-Create a table similar to that which is depicted, illustrating each of the steps of the calculation for each child.
-Based on all the information contained in the case, calculate the additional annual systematic investment needed for each child
-Pay particular attention to the proper mode of the calculator for each step
Explanation / Answer
For thechild whose current ageis 8: Amount needed per annum at current price $35,000 Number ofyears to reach ageof18 10 (18-8) N A Calculation PV=A/(1.07^N) Years from today Amount Present Value 10 Amount needed in the first year of education $ 54,354 (35000*((1+0.045)^10) 27630.78172 11 Amount needed in the second year of education $ 56,800 (54354*1.045) 26985.20271 12 Amount needed in the Third year of education $ 59,356 (56800*1.045) 26354.70732 13 Amount needed in the Fourth year of education $ 62,027 (59356*1.045) 25738.94313 SUM 106709.6349 Present Value of Total Educationalexpenditure $ 106,710 Present Value of Savingsof first year $ 26,168 (28000/(1.07) Present Value of Savingsof Second year $ 10,481 (12000/(1.07^2) Present Value of savings of first and second year $ 36,649 (26168+10481) Present Value of additionalsavings required $ 70,060 (106710-36649) Value of additionalsavings after 2 years $ 80,212 (70060*(1.07^2) Number of years of additionalsavings from third year 8 (10-2) Annualsavingsrequired from year 3 to 10 $13,432.92 (Using PMT function of excel with Rate=7%,Nper=8, PV=-80212) For thechild whose current ageis 6: Amount needed per annum at current price $35,000 Number ofyears to reach ageof18 12 (18-6) N A Calculation PV=A/(1.07^N) Years from today Amount Present Value 12 Amount needed in the first year of education $ 59,356 (35000*((1+0.045)^12) 26354.70732 13 Amount needed in the second year of education $ 62,027 (59356*1.045) 25738.94313 14 Amount needed in the Third year of education $ 64,818 (62027*1.045) 25137.56595 15 Amount needed in the Fourth year of education $ 67,735 (67735*1.045) 24550.23964 SUM 101781.456 Present Value of Total Educationalexpenditure $ 101,781 Present Value of Savingsof first year $ 26,168 (28000/(1.07) Present Value of Savingsof Second year $ 10,481 (12000/(1.07^2) Present Value of savings of first and second year $ 36,649 (26168+10481) Present Value of additionalsavings required $ 65,132 (101781-36649) Value of additionalsavings after 2 years $ 74,570 (65132*(1.07^2) Number of years of additionalsavings from third year 10 (12-2) Annualsavingsrequired from year 3 to 12 $10,617.09 (Using PMT function of excel with Rate=7%,Nper=10, PV=-74570) Additional annual systematic investment needed for each child Annualinvestment needed for 8 year old child $13,433 Annualinvestment needed for 6 year old child $10,617
