Excel College Savings Estimator Spreadsheet

Is your financial life clouded by thoughts of how you're going to pay for your kids' college?

If so, you're not alone. College is darn expensive, and it's not getting any cheaper.

However, one of the best things you can do to combat the Looming College-Expense Nightmare is to sit down, take a deep breath, pour yourself a stiff drink, and begin planning.

Number-crunching opportunities like this are where spreadsheets excel in the personal-finance realm. And it's why I created my College Savings Estimator spreadsheet. We have a young daughter, and while we've been tucking away savings for college for a while now, I'd never really pumped any hard numbers into a calculator. It was time to try and project what we'll have saved up by the time she's ready for college, and how those savings might fare against ever-rising college expenses.

My College Savings Estimator is a 2-page spreadsheet. The first page looks like so (in Excel 2007):

And page two appears thusly (also in Excel 2007):

Actually, I think it's sort of a fun spreadsheet to play around with. Grab your copy below...

Using the College Savings spreadsheet should be pretty self-explanatory. But just in case, I'll add a few more details and explanations here.

DATA ENTRY Worksheet: Entering Your Numbers

There are only six inputs for this spreadsheet, and they're all on the DATA ENTRY worksheet. These cells should be light blue in color. If there's data in them already, simply overwrite these numbers with your own.

1. Years Before Child Enters College
   Enter the number of years between today and the date you expect your child to enter college.
2. Your Savings Contribution per Month
   How much can you contribute each month toward your child's education savings? Whatever the number, enter it here.
3. Current Balance of Any College Savings
   If you already saved some amount of money toward your kid's education, enter the amount of those savings here.
4. Expected Annual Investment Return
Yes, it's impossible to know this for sure. But plug in the annual rate of return you'd expect your college savings to earn.
5. Cost of College Today, per Year
   YOu can usually find this information (or an estimation thereof) on your chosen college's website.
6. Expected Inflation Rate for College Expenses
   Your guess at this is as good as mine. Where college costs are concerned, I suspect we can pretty well assume an inflation rate of anywhere from 6 to 10 percent annually.

Obviously, the nice thing about doing this sort of math in a spreadsheet (as opposed to, say, an online calculator) is that you can easily change your inputs and instantly see how the changes affect your results.

DATA ENTRY Worksheet: What It Tells You

The most basic calculations happen at the bottom of the DATA ENTRY worksheet. Here you can see an estimate of what four years of college would cost you, broken down by year, by the time your child starts his or her higher education. Beneath that:

1. Total Contributions to Savings (w/out Interest)
   This is how much out-of-pocket money you will have set aside — not counting any earnings or interest — by the time your kid starts college.
2. Estimated Total Savings in __ Years (With Interest)
   Here it is, interest, earnings, and all. When your kid starts school, this will be the ammo you'll have available at that point (assuming you stopped saving at that point, which you probably wouldn't).

CASH FLOW Worksheet: What It Tells You

Here's the detailed, year-by-year breakdown of your College Savings Adventure. At the point at which your child begins college, the spreadsheet then assumes that you'll continue depositing funds toward Junior's education. So you can also see how the finances look after each of five years of college.

In the screenshot above, our Example Kid will begin college 13 years from now. Excel will shade that 13th and final year of savings in green, so we can see just exactly where Crunch Time falls. The calculations then continue for five more years. This can help give us some idea of how our savings will match up, year by year, to college expenses at that time.

Questions? Comments? Contact Me!

Got a question or issue? Don't hesitate — drop me a line. I'll get back to you as soon as possible!