les and Trace, age 9, is in third third grade shown exceptionally its a charming personality and Aydis 18 who will begin collage today. Tyder, age 12, is in sixth grade an per calented students with excellent grades, Kylie has all of the children are academically talented students with by and Tyler has shown acoperoma mascara T he shown exceptional musical ability. Irace, the baby, exhibit smile, bur no interest in sports or music. He considers himself a work in s himself a work in progress. to send any of the children to college. Their hope was that each child shletic scholarship, or a com music scholarship and e William and Kase have not saved any money to send any of the child for financial aid. They believe it likely that Kylie will qualify for an athl munion of an athletic scholarship and financial Haris scholarship and financial aid, and Tyler will qualify for some sort of m facial aid. As their thoughts turned to Trace's unique qualities and lack of interest in s ised they might have to save money to pay for Trace to attend college. in sports and music they real e offered mainly to girls, fect, William and Kate The arched for scholarships to charm schools, which to their dismay, turned out to be offered Trece, being the precocious child he is, thought a scholarship to charm school would be perfect. Will dired, and are now on a mission to find ways to pay for each of their children to attend 4 years of Scare University, beginning at age 18. of college at The completed the FAFSA on-line and found some scholarship funds to offset more than half of the ener tion and fees, but the scholarship does not pay for books, room and board, transportation or meals while i way from home. The Windsor's need help identifying how to pay for Kylie's current college expenses while alex planning for Tyler's and Trace's future college expenses. While Viving They are willing to make changes to their finances to pay for Tyler and Trace to attend college, but also need your help determining how much they should save and where they should invest the money until the boys are ready to attend college Goals (in order of priority) 1. College education of children for 4 years, cach beginning at age 18, at the state university 2. Retirement at age 64 with 60% of preretirement income including Social Security. Life expectancy is age 96 for both. 3. Appropriate risk management portfolio, investment portfolio, and estate planning portfolio 4. Pay off their debt before retirement. "CATION & EDUCATION FUND mini case EXTERNAL INFORMATION draries General inflation (CP flation (CPI) is expected to be 3% annually on inflation is expected to be 6% annually. common law state that has no state income tax Education inflation is expe They live in a comm af Landing Rates Mortgage 30 years - conforming rate - 4.0% Mortgage 15 years - conforming rate = 3.5% TY Prime rate is 3.25%. They plan to stay in the to stay in their home through retirement and are more concerned about paying for their chil- dren's college education than refinancing their home. met Returns Expected . General market is expected to return 8.0%. The Windsor's required and expected rate of return is 8.0%. Fixed income investments are expected to yield 6%. T.Bills are expected to yield 3% per year and are expected to be the proxy for the risk-free rate of return. William and Kate scored a 22 on the Global Portfolio Allocation Scoring System (PASS) for Individual Investors. EDUCATION The Windsor's estimate that college costs at State University currently total $15.000 per child, per year in today's dollars for tuition, fees, room and board. State University is within commuting distance of William and Kate's home. If the children do not qualify for financial aid, and their parents cannot afford to pay the full cost of attending college and living on cam- pus, the children can enroll in classes while living at home, or enroll in classes at the local community col- lege to reduce the cost of admission. Kylie wants to live on campus and does not want to attend community college. William and Kate have been told that their state offers a 529 College Savings Plan as well as a unit-type prepaid tuition program. Each unit of pre-paid tuition purchased is redeemable for one percent of the resident undergraduate tui tion at the highest-priced public university in their state. Their State's pre-paid tuition program is not backed by the full faith and credit of the State, but the units may also be used for eligible educational institutions across the country. ASSUMPTION Education assumptions include four years of college at $15,000 per year in today's dollars. The education inflation rate is 6%. Financial Aid may be possible, but do not assume outside funding assistance from the children's grandparents or any other person. WILLIAM AY KATE WINDSOR 139 SUGGESTED SOLUTION needed to fund all four year cats of DETERMINING THE EDUCATION Use the traditional, three-step approach to determine expense for each child. Combining the education inflation 8%, yields a 1.8868% inflation-adjusted rate of return went rate often che three-step approach a EDUCATION COST USING THE TRADITIONAL APPROA abining the education inferion rate of 6% with an expected investm determine the lump sum needed to fund all djusted rate of return that is used in the first two steps of the Step 1 Calculate the inflation-adjusted, four-year cost of eac sted, four-year cost of each child's college education. PMTAD - $15.000 4 years 1.8868% FV PVAD $58,353.81 (this will be the same for each child) This is also the lump-sum cost of education for Kylie, age 18, who begins college today. bflow, financial aid and to The Windsor's will pay for Kylie's education as they go or through a combination of cash flow, finan breaks, if any are available. ollege. Traditional approach Step 2 Calculate the present value of the lump-sum cost of each child's four years of college. Traditional determine the cost of education for Tyler, age 12, and Trace, age 9, who will start school in 6 years and respectively. Trace FV PMT Tyler = $58,353.81 =0 = 1.8868 FV = PMT = i = $58,353.81 0 1.8868 N 9 PV = $52.162.89 PV - $49,318.28 Total PV for Tyler and Trace is $101,481.17 ($52,162.89 + $49,318.28). Total PV for Kylie, Tyler and Trace is $159,834.98 ($101,481.17 +58,353.81). ini case 3 fycar payment needed to fund each child's college educa skutere she end of education return of proach PV N Tyler - 352,162.89 6 Trace - $49.3182 FV FY - 0 PMTO - $11.283.64 PMTO - $7.894.86 annual cost of savings to fund Tyler's and Trace's education expen faces education expense - $19.178.50. Review the proof of education funding schedule. THE UNEVEN CASH FLOW APPROACH TO CALCULATE THE NPV OF 4 YEARS OF EDUCATION EXPENSE Persent value calculation for cost of education for all three children: TORIT Kontroles 15,000 CFI 4 ORANGE IN O [CFil 2 ORANGE IN 15,000 (CF) 3 ORANGE (Nj] 30,000 (CFj] 15,000 (CFj] ORANGE IN 1.08 [+] 1.06 [-] 1 [x] 100 [-] [I/YR] ORANGE (NPVj 12C Korbes 15.000 ( ICF 4 IRI INI 0 [gl (CF) 2 lgl [Nil 15.000 [g] (CF 3 lgl [Nil 30,000 RICHI 15.000 lg! (CF) 3 [8] Nil 1.08 [ENTER] 1.06 [+] 1 - 100 W A INPVI Answer: 159,835.05 Answer: 159,835.05 Recap The PV of Kylie's four years of education is $58,354. The PV of Tyler's four years of education is $52,163. de PV of Trace's four years of education is $49,318. The total present value of the inflated cost of education for the Windsor children is $159,835. See Education Savings and Withdrawal Schedule. ne importance of financial aid for the Windsor children becomes clearer to William and Kate as they realize that watever financial aid they receive will help them close the small annual present value deficit of funds needed to Meet their education funding goal and retire at age 64. AFSA purposes FINDING FINANCIAL AID The Windsors have already submitted their first FAFSA. The on eligible to receive a Pell Grant or other to $41,000, and their EFC was estimated to be nero. SA T i me FAFSA estimator indicated they al Grant or other federal financial aid. Their estimated Net Worth for FAFSA cholarships formal de The Windsors linked to the State stimate the of the SU Hope so funded by the Windsors linked to the State Universiry (SU Net Cost calculator to determine what, if any, ich her funds might be available to help them pay for Kylie's college expenses. They hope to receive a forma report (SAR), which will outline the financial aid they can expect. While they were trying to estima of pocket expenses, they used SU's Cost Calculator (see below) and discovered that Kylie is eligible for eh Scholarship, which is funded by the Lortery for Education, and possibly the ZM Scholarship also fim Lottery for Education, but based on merit. The best type of financial aid would be scholarships and grants because they do not have to be repaid. The cost of one year of school, 15 ruition hours or more, estimated below is $10,663. of her books, 258 for tuition, fees and The SU Hope Scholarship is estimated to pay for 85% of Kylie's tuition for the year, but none of her room and board expenses. If Kylie attends SU locally, the Windsor's will need to pay $4.258 for tuitin books Spring) 2017-18 for an Summary of the output from State University's On-Line. Cost Calculator for one year (Fall/Spring) 2017.10 incoming Freshman. Living at Living on Home Campus Tuition (in-state) $7.535.00 $7.535.00 Student Fees $2,128.00 $2,128.00 Books and Supplies $1.000.00 $1,000.00 Sub-total tuition, fees, books $10,663.00 $10,663.00 Housing, meals, transportation 0 $8,218.00 Total cost of attendance $10,663.00 $18,881.00 Less Scholarship ($6,404.75) ($6,404.75) Total cost of attendance $4,258.25 $12,476.25 If Kylie attends a state university where she must pay room, board, meals and transportation her total out of pocket expenses are estimated to be $12,476. This is slightly less than the Windsor's estimate of $15.000 but more than they can afford to pay from excess cash flow. Kylie's athletic ability and academic standing may qualify her for athletic as well as merit-based scholarships, such as the ZM Scholarship. However, the FAFSA quick check determined Kylie is not eligible for needs-based financial aid. After finding as much 'free' financial aid as possible, the Windsor's should consider tax credits and tax deductions they can claim each year on their federal income tax return. While these credits and deductions will not help them pay Kylie's tuition bills as they come due, they will help to offset or reduce the cost of education. Plus they can estimate the impact of any tax benefits and reduce their federal income tax withholding accordingly, which allow them to receive the projected tax benefit ratably during the year (i.e., in each pay check). 142 EDUCATION & EDUCATION FUNDING MINI CASE OO mini case 3 itional funds are needed Gants (FSEOG) and Fede needed, the Windsor's should consider Federal Supplemental Education Opportunity Federal Perkins Loans, which are both campus-based, student financial aid for students with Current rate of interest charged for Federal Perkins loans is five percent ERG. The curren also want to consider borrowing funds from their 401(k) plan. The rate charged is two percent The Windsor's may also Love the prime rate of interest. illam and Kate should way for their college educa were should also consider whether they want their children to contribute some of the funds needed to lee education. If so, the children should consider work-study options on their college campus. Once the Windsor's pay Kylie's current tuiti while attendine Windsor's have determined the type and amount of merit-based financial aid they can count on to help current tuition and fees, they will need to determine whether they should require Kylie to live at home endine college. The at-home cost of attendance is manageable and could be paid from their discretionary The additional $8,218 for room, board, meals and transportation is more than their discretionary cash d would require the Windsor's to consider taking out loans in order to fund Kylie's education. Klie's college expenses are most urgent, but William and Kate also need to prepare to send Tyler and Trace to college. They should continue saving the maximum amount needed to qualify for their employer's matching contribution and consider contributing excess funds (those over the percentage needed to gain their employer's maximum match) into 529 Plan accounts for each son. They should also remember to begin the search for financial aid as each boy enters their senior year of high school. Education Savings & Withdrawal William and Kate Windsor Age of Kylie Beginning Balance Ending Balance -$16,200 Year 2017 2018 2019 2020 2021 2022 Sum of Payments -$15,000 -$15.900 -$16,854 -$17.865 $16.200 -534.668 21 50 -88941 2023 Tuition @ 6% $15.000 $15.900 $16.854 $17,865 $18,937 $20,073 $21.278 $22.554 $23,908 $25,342 $26,863 $28,474 $30,183 $31.994 -3122.988 -$157.186 2024 2025 7036 2027 2028 2029 2030 withdrawal Withdrawal withdrawal Withdrawal Karning (Kylie) Tyler) (Trace) 08% -$15,000 -$1.200 $15.900 -$2.568 $16,854 54.122 -$17,865 $6.351 -$6,859 -$21,278 39.110 -$22.554 -$11,645 -$23.908 90 -514487 -$25.342 -$25,342 -$19.701 -$26,863 -$23,426 $28.474 -$27.578 -$30,183 -532.199 -$34.775 -$57,557 -$40,562 -343,807 -547.311 -$51.096 -$55,184 -$59.599 -$64.367 $69.516 -$55.644 $79,389 -$35,741 -$92,600 -$122.988 -$157.186 -$195,581 -$265.966 -5316,256 -$372.308 -5434.691 -5469.466 -$507.023 -3547585 -5591392 -5638.703 $689.799 -$744.983 -$804,582 -$868,949 -$938,465 -$265.966 -$316,256 -$372.308 -$21,278 -$22.554 -523.908 -550,684 -$26,863 -$28,474 -$30,183 2031 2033 2034 2035 2016 2037 2038 2039 -5469.466 -5507,025 -3547,585 -$591.392 -5638.703 -5689.799 -$744.983 -5804.582 $868.949 $35.948 $38,105 $10.392 $42.815 $45.384 $48,107 $50,993 $54,053 $58,354 $52,163 $49,318 $199.835 PV of tuition payments today as S159.85 Sum of payments -565,619 $93.082 $110,862 Age of Kylie Year Beginning Balance Sum of Payments 2017 2018 2019 $19,178 2020 2021 Proof of Education funding and Withdrawals for Tyler and Trace William and Kate Windsor Adming no lancial Aid End of year 529 withdrawal Withdrawal withdrawal Withdrawal Earnings p lan Ending (Kylie) (Tyler) (Trace) 0 Contributions Balance $19.178 $19,178 51.534 519.178 $59.891 53.191 $19.178 $62.261 54.981 $19.178 586420 $6,914 $19.178 $112.513 $9.001 $19.178 $140,692 $7.895 $136,862 59.145 $7.895 $131,347 $5,595 $7,895 $123.929 $25.342 -$25.342 55,860 $79,105 -$26,863 54.179 $2,236 530,183 -$30.183 Tuitione 6% $15.000 $15.900 $16,854 $17.865 $18,937 $20,073 22 2032 2023 2024 2025 $39.891 $62.261 $56,420 $112.513 $1402692 $136.362 $131.947 $123.929 $79,105 $56,421 25 -$21.178 -$50.684 2027 28 0,183 2029 20:30 -$28.474 -$30,183 2031 2032 $22.554 $23.908 525342 $26,863 $26.474 5.30,183 $31.994 $38.914 $35.948 $38.105 $40,392 $42.815 545,384 $45,107 550,993 $54,053 2034 35 2035 2036 2037 so 39 2038 2039 $0 $52,163 $49.318 $101481 PV of tuition payments today equal Sum of payments So $93.082 $110,862 Mini Case 3: William and Kate Windsor Instructions Follow the steps in the case solution and use the new information below to rework the case. I want to see all the steps involved and submit the solution as if you are showing it to the client. The use of the narrated ppt will get higher marks. Find attached a spreadsheet I created to help understand the math: Please note the following changes: 1 Education inflation is 5% and not 6% 2. The expected rate of return is 7.5% 3. Kylie is 17, Tyler is 11, and Trace is 10. 4. The current cost of education is 14,000 instead of 15,000 Other information remains the same. Education Planning.xlsx (13.98 KB) 9 M HD-1080 ad les and Trace, age 9, is in third third grade shown exceptionally its a charming personality and Aydis 18 who will begin collage today. Tyder, age 12, is in sixth grade an per calented students with excellent grades, Kylie has all of the children are academically talented students with by and Tyler has shown acoperoma mascara T he shown exceptional musical ability. Irace, the baby, exhibit smile, bur no interest in sports or music. He considers himself a work in s himself a work in progress. to send any of the children to college. Their hope was that each child shletic scholarship, or a com music scholarship and e William and Kase have not saved any money to send any of the child for financial aid. They believe it likely that Kylie will qualify for an athl munion of an athletic scholarship and financial Haris scholarship and financial aid, and Tyler will qualify for some sort of m facial aid. As their thoughts turned to Trace's unique qualities and lack of interest in s ised they might have to save money to pay for Trace to attend college. in sports and music they real e offered mainly to girls, fect, William and Kate The arched for scholarships to charm schools, which to their dismay, turned out to be offered Trece, being the precocious child he is, thought a scholarship to charm school would be perfect. Will dired, and are now on a mission to find ways to pay for each of their children to attend 4 years of Scare University, beginning at age 18. of college at The completed the FAFSA on-line and found some scholarship funds to offset more than half of the ener tion and fees, but the scholarship does not pay for books, room and board, transportation or meals while i way from home. The Windsor's need help identifying how to pay for Kylie's current college expenses while alex planning for Tyler's and Trace's future college expenses. While Viving They are willing to make changes to their finances to pay for Tyler and Trace to attend college, but also need your help determining how much they should save and where they should invest the money until the boys are ready to attend college Goals (in order of priority) 1. College education of children for 4 years, cach beginning at age 18, at the state university 2. Retirement at age 64 with 60% of preretirement income including Social Security. Life expectancy is age 96 for both. 3. Appropriate risk management portfolio, investment portfolio, and estate planning portfolio 4. Pay off their debt before retirement. "CATION & EDUCATION FUND mini case EXTERNAL INFORMATION draries General inflation (CP flation (CPI) is expected to be 3% annually on inflation is expected to be 6% annually. common law state that has no state income tax Education inflation is expe They live in a comm af Landing Rates Mortgage 30 years - conforming rate - 4.0% Mortgage 15 years - conforming rate = 3.5% TY Prime rate is 3.25%. They plan to stay in the to stay in their home through retirement and are more concerned about paying for their chil- dren's college education than refinancing their home. met Returns Expected . General market is expected to return 8.0%. The Windsor's required and expected rate of return is 8.0%. Fixed income investments are expected to yield 6%. T.Bills are expected to yield 3% per year and are expected to be the proxy for the risk-free rate of return. William and Kate scored a 22 on the Global Portfolio Allocation Scoring System (PASS) for Individual Investors. EDUCATION The Windsor's estimate that college costs at State University currently total $15.000 per child, per year in today's dollars for tuition, fees, room and board. State University is within commuting distance of William and Kate's home. If the children do not qualify for financial aid, and their parents cannot afford to pay the full cost of attending college and living on cam- pus, the children can enroll in classes while living at home, or enroll in classes at the local community col- lege to reduce the cost of admission. Kylie wants to live on campus and does not want to attend community college. William and Kate have been told that their state offers a 529 College Savings Plan as well as a unit-type prepaid tuition program. Each unit of pre-paid tuition purchased is redeemable for one percent of the resident undergraduate tui tion at the highest-priced public university in their state. Their State's pre-paid tuition program is not backed by the full faith and credit of the State, but the units may also be used for eligible educational institutions across the country. ASSUMPTION Education assumptions include four years of college at $15,000 per year in today's dollars. The education inflation rate is 6%. Financial Aid may be possible, but do not assume outside funding assistance from the children's grandparents or any other person. WILLIAM AY KATE WINDSOR 139 SUGGESTED SOLUTION needed to fund all four year cats of DETERMINING THE EDUCATION Use the traditional, three-step approach to determine expense for each child. Combining the education inflation 8%, yields a 1.8868% inflation-adjusted rate of return went rate often che three-step approach a EDUCATION COST USING THE TRADITIONAL APPROA abining the education inferion rate of 6% with an expected investm determine the lump sum needed to fund all djusted rate of return that is used in the first two steps of the Step 1 Calculate the inflation-adjusted, four-year cost of eac sted, four-year cost of each child's college education. PMTAD - $15.000 4 years 1.8868% FV PVAD $58,353.81 (this will be the same for each child) This is also the lump-sum cost of education for Kylie, age 18, who begins college today. bflow, financial aid and to The Windsor's will pay for Kylie's education as they go or through a combination of cash flow, finan breaks, if any are available. ollege. Traditional approach Step 2 Calculate the present value of the lump-sum cost of each child's four years of college. Traditional determine the cost of education for Tyler, age 12, and Trace, age 9, who will start school in 6 years and respectively. Trace FV PMT Tyler = $58,353.81 =0 = 1.8868 FV = PMT = i = $58,353.81 0 1.8868 N 9 PV = $52.162.89 PV - $49,318.28 Total PV for Tyler and Trace is $101,481.17 ($52,162.89 + $49,318.28). Total PV for Kylie, Tyler and Trace is $159,834.98 ($101,481.17 +58,353.81). ini case 3 fycar payment needed to fund each child's college educa skutere she end of education return of proach PV N Tyler - 352,162.89 6 Trace - $49.3182 FV FY - 0 PMTO - $11.283.64 PMTO - $7.894.86 annual cost of savings to fund Tyler's and Trace's education expen faces education expense - $19.178.50. Review the proof of education funding schedule. THE UNEVEN CASH FLOW APPROACH TO CALCULATE THE NPV OF 4 YEARS OF EDUCATION EXPENSE Persent value calculation for cost of education for all three children: TORIT Kontroles 15,000 CFI 4 ORANGE IN O [CFil 2 ORANGE IN 15,000 (CF) 3 ORANGE (Nj] 30,000 (CFj] 15,000 (CFj] ORANGE IN 1.08 [+] 1.06 [-] 1 [x] 100 [-] [I/YR] ORANGE (NPVj 12C Korbes 15.000 ( ICF 4 IRI INI 0 [gl (CF) 2 lgl [Nil 15.000 [g] (CF 3 lgl [Nil 30,000 RICHI 15.000 lg! (CF) 3 [8] Nil 1.08 [ENTER] 1.06 [+] 1 - 100 W A INPVI Answer: 159,835.05 Answer: 159,835.05 Recap The PV of Kylie's four years of education is $58,354. The PV of Tyler's four years of education is $52,163. de PV of Trace's four years of education is $49,318. The total present value of the inflated cost of education for the Windsor children is $159,835. See Education Savings and Withdrawal Schedule. ne importance of financial aid for the Windsor children becomes clearer to William and Kate as they realize that watever financial aid they receive will help them close the small annual present value deficit of funds needed to Meet their education funding goal and retire at age 64. AFSA purposes FINDING FINANCIAL AID The Windsors have already submitted their first FAFSA. The on eligible to receive a Pell Grant or other to $41,000, and their EFC was estimated to be nero. SA T i me FAFSA estimator indicated they al Grant or other federal financial aid. Their estimated Net Worth for FAFSA cholarships formal de The Windsors linked to the State stimate the of the SU Hope so funded by the Windsors linked to the State Universiry (SU Net Cost calculator to determine what, if any, ich her funds might be available to help them pay for Kylie's college expenses. They hope to receive a forma report (SAR), which will outline the financial aid they can expect. While they were trying to estima of pocket expenses, they used SU's Cost Calculator (see below) and discovered that Kylie is eligible for eh Scholarship, which is funded by the Lortery for Education, and possibly the ZM Scholarship also fim Lottery for Education, but based on merit. The best type of financial aid would be scholarships and grants because they do not have to be repaid. The cost of one year of school, 15 ruition hours or more, estimated below is $10,663. of her books, 258 for tuition, fees and The SU Hope Scholarship is estimated to pay for 85% of Kylie's tuition for the year, but none of her room and board expenses. If Kylie attends SU locally, the Windsor's will need to pay $4.258 for tuitin books Spring) 2017-18 for an Summary of the output from State University's On-Line. Cost Calculator for one year (Fall/Spring) 2017.10 incoming Freshman. Living at Living on Home Campus Tuition (in-state) $7.535.00 $7.535.00 Student Fees $2,128.00 $2,128.00 Books and Supplies $1.000.00 $1,000.00 Sub-total tuition, fees, books $10,663.00 $10,663.00 Housing, meals, transportation 0 $8,218.00 Total cost of attendance $10,663.00 $18,881.00 Less Scholarship ($6,404.75) ($6,404.75) Total cost of attendance $4,258.25 $12,476.25 If Kylie attends a state university where she must pay room, board, meals and transportation her total out of pocket expenses are estimated to be $12,476. This is slightly less than the Windsor's estimate of $15.000 but more than they can afford to pay from excess cash flow. Kylie's athletic ability and academic standing may qualify her for athletic as well as merit-based scholarships, such as the ZM Scholarship. However, the FAFSA quick check determined Kylie is not eligible for needs-based financial aid. After finding as much 'free' financial aid as possible, the Windsor's should consider tax credits and tax deductions they can claim each year on their federal income tax return. While these credits and deductions will not help them pay Kylie's tuition bills as they come due, they will help to offset or reduce the cost of education. Plus they can estimate the impact of any tax benefits and reduce their federal income tax withholding accordingly, which allow them to receive the projected tax benefit ratably during the year (i.e., in each pay check). 142 EDUCATION & EDUCATION FUNDING MINI CASE OO mini case 3 itional funds are needed Gants (FSEOG) and Fede needed, the Windsor's should consider Federal Supplemental Education Opportunity Federal Perkins Loans, which are both campus-based, student financial aid for students with Current rate of interest charged for Federal Perkins loans is five percent ERG. The curren also want to consider borrowing funds from their 401(k) plan. The rate charged is two percent The Windsor's may also Love the prime rate of interest. illam and Kate should way for their college educa were should also consider whether they want their children to contribute some of the funds needed to lee education. If so, the children should consider work-study options on their college campus. Once the Windsor's pay Kylie's current tuiti while attendine Windsor's have determined the type and amount of merit-based financial aid they can count on to help current tuition and fees, they will need to determine whether they should require Kylie to live at home endine college. The at-home cost of attendance is manageable and could be paid from their discretionary The additional $8,218 for room, board, meals and transportation is more than their discretionary cash d would require the Windsor's to consider taking out loans in order to fund Kylie's education. Klie's college expenses are most urgent, but William and Kate also need to prepare to send Tyler and Trace to college. They should continue saving the maximum amount needed to qualify for their employer's matching contribution and consider contributing excess funds (those over the percentage needed to gain their employer's maximum match) into 529 Plan accounts for each son. They should also remember to begin the search for financial aid as each boy enters their senior year of high school. Education Savings & Withdrawal William and Kate Windsor Age of Kylie Beginning Balance Ending Balance -$16,200 Year 2017 2018 2019 2020 2021 2022 Sum of Payments -$15,000 -$15.900 -$16,854 -$17.865 $16.200 -534.668 21 50 -88941 2023 Tuition @ 6% $15.000 $15.900 $16.854 $17,865 $18,937 $20,073 $21.278 $22.554 $23,908 $25,342 $26,863 $28,474 $30,183 $31.994 -3122.988 -$157.186 2024 2025 7036 2027 2028 2029 2030 withdrawal Withdrawal withdrawal Withdrawal Karning (Kylie) Tyler) (Trace) 08% -$15,000 -$1.200 $15.900 -$2.568 $16,854 54.122 -$17,865 $6.351 -$6,859 -$21,278 39.110 -$22.554 -$11,645 -$23.908 90 -514487 -$25.342 -$25,342 -$19.701 -$26,863 -$23,426 $28.474 -$27.578 -$30,183 -532.199 -$34.775 -$57,557 -$40,562 -343,807 -547.311 -$51.096 -$55,184 -$59.599 -$64.367 $69.516 -$55.644 $79,389 -$35,741 -$92,600 -$122.988 -$157.186 -$195,581 -$265.966 -5316,256 -$372.308 -5434.691 -5469.466 -$507.023 -3547585 -5591392 -5638.703 $689.799 -$744.983 -$804,582 -$868,949 -$938,465 -$265.966 -$316,256 -$372.308 -$21,278 -$22.554 -523.908 -550,684 -$26,863 -$28,474 -$30,183 2031 2033 2034 2035 2016 2037 2038 2039 -5469.466 -5507,025 -3547,585 -$591.392 -5638.703 -5689.799 -$744.983 -5804.582 $868.949 $35.948 $38,105 $10.392 $42.815 $45.384 $48,107 $50,993 $54,053 $58,354 $52,163 $49,318 $199.835 PV of tuition payments today as S159.85 Sum of payments -565,619 $93.082 $110,862 Age of Kylie Year Beginning Balance Sum of Payments 2017 2018 2019 $19,178 2020 2021 Proof of Education funding and Withdrawals for Tyler and Trace William and Kate Windsor Adming no lancial Aid End of year 529 withdrawal Withdrawal withdrawal Withdrawal Earnings p lan Ending (Kylie) (Tyler) (Trace) 0 Contributions Balance $19.178 $19,178 51.534 519.178 $59.891 53.191 $19.178 $62.261 54.981 $19.178 586420 $6,914 $19.178 $112.513 $9.001 $19.178 $140,692 $7.895 $136,862 59.145 $7.895 $131,347 $5,595 $7,895 $123.929 $25.342 -$25.342 55,860 $79,105 -$26,863 54.179 $2,236 530,183 -$30.183 Tuitione 6% $15.000 $15.900 $16,854 $17.865 $18,937 $20,073 22 2032 2023 2024 2025 $39.891 $62.261 $56,420 $112.513 $1402692 $136.362 $131.947 $123.929 $79,105 $56,421 25 -$21.178 -$50.684 2027 28 0,183 2029 20:30 -$28.474 -$30,183 2031 2032 $22.554 $23.908 525342 $26,863 $26.474 5.30,183 $31.994 $38.914 $35.948 $38.105 $40,392 $42.815 545,384 $45,107 550,993 $54,053 2034 35 2035 2036 2037 so 39 2038 2039 $0 $52,163 $49.318 $101481 PV of tuition payments today equal Sum of payments So $93.082 $110,862 Mini Case 3: William and Kate Windsor Instructions Follow the steps in the case solution and use the new information below to rework the case. I want to see all the steps involved and submit the solution as if you are showing it to the client. The use of the narrated ppt will get higher marks. Find attached a spreadsheet I created to help understand the math: Please note the following changes: 1 Education inflation is 5% and not 6% 2. The expected rate of return is 7.5% 3. Kylie is 17, Tyler is 11, and Trace is 10. 4. The current cost of education is 14,000 instead of 15,000 Other information remains the same. Education Planning.xlsx (13.98 KB) 9 M HD-1080 ad