Goal seek: Getting to the outcome you want, using Excel

Microsoft Excel will not only let you play “what-if” with your choices, it will also help you figure out what you need to do to get where you want to be. You can work the problem backwards with a nifty little feature called Goal Seek.

Say you want to start a college fund for your new baby. You start with $10,000 in the fund and add $15,000 to it each year. You have it well invested so that it earns 8% each year. But college is expensive. You figure $140,000 for four years, and that grows at 9% a year. Your first question is whether you will save enough by the time the kid is 18. Here’s a spreadsheet to answer that question. It’s built using two bucket brigade formulas and one other, all AutoFilled down the columns. The inputs are all named. (Check out my previous posts if this doesn’t make sense.) You see that you’ll be more than $50,000 short when you compare the amount you’ve saved and the cost of college at age 18. By the way, you can download this spreadsheet from http://www.decisionspreadsheets.com/free-spreadsheets/ under Investment, Save for college.

Now you might try some “what-if.” Will I get there if I start with more money? if I add more each month? if I get a better return? if I choose a less expensive college? You can change any of the inputs and see what that does to the outcome.

Better yet, you can calculate exactly what you need to do to reach your goal. Say you decide to add more money to the account each year. You know $15,000 isn’t enough. Exactly how much will you need? Here’s how to get Excel to calculate it for you. Choose What-If Analysis > Goal Seek in the Data Tools group or select Goal Seek from the Tools menu. You’ll get a dialog box. You want to make the shortfall at age 18 (D26) become 0 by changing the added amount (B2). Put this in the dialog box by clicking on the cells as in the picture. ($B$2 is the same cell as B2.) Click OK and Goal Seek gives you your answer in cell B2: Adding $16,567 to the fund each year will do it. You can use Goal Seek to answer other questions, such as: How much do I need to put in to start? How much does my investment need to grow?
Goal Seek is a simple, but powerful, tool for decision makers to determine what they need to do to get where they want to be.

Have you used it? Please share your ideas and experiences.

Leave a reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s