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?
| 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 |