fin excel

Goal Seek for Financial Planning You value education and you want your child to receive the best possible education. But sending a child to private schools and Ivy League colleges is not cheap. You want to start saving money so that when you child need the money, you can support him/her. The current assumption is you will have your 1st child in 5 years and you wish to have $200,000 in today’s dollars when he/she is 15 years old. Based on current situation, you expect inflation to be stable at 2% in the foreseeable future. Your plan is to put down savings each year and you can save every year an amount that’s 2% more than the previous year. All your savings go to a stock fund that is expected to return 10% annually, of which 1.5% will be dividends and 8.5% will be capital gains. You pay 15% tax on dividend income as you receive them at the end of each year. You re-balance your fund every year, in other words, you sell all your positions and re-invest at the beginning of a new year, so capital gains are also realized every year, you pay 15% tax on capital gains as well. To have enough money for your child’s education in 20 years, how much money do you have to save in the first year? M7 Assignment - Goal Seek for Financial Planning Rubric Input section clearly labeling all the variables and current values 1 pt Set up year-by-year table including beginning balance, new savings and growth, dividend income, capital gains income, dividend tax, capital gains tax, ending balance 5 pts Use goal seek to estimate the initial saving amount 2 pts Total 8 pts