College Savings Calculator Directions.

If you're still shopping, the best evaluation results are obtained by looking at the college plan demo, while following along as you read these directions.

When you're using the program, things go better if you first print out these directions.

The "demo" is the actual financial spreadsheet without the formulas, so you won't be able to do anything but look at it. You'll receive a working program after purchase.

Tips for working with and printing in Excel are here, here, and here. The pages on the free sample comprehensive financial plan print better than the demo.

How to turn the zipped file attached in your e-mail into an Excel spreadsheet is explained in the text of the delivery e-mail.

Basic concept for most all of these Excel spreadsheets: Input your data into the green-shaded cells of the input sheets. This data flows through the calculation sheets (to the right), which will then populate presentation sheets (to the left), where you look at the results. Then you fix mistakes, repeat, format, print. If a cell is not colored green, then it's not an input cell, so you won't be able to do anything with it (if it's not an input sheet). If you could, then you may end up damaging the spreadsheet. Don't input, or type over, any formulas on the presentation sheets because then they will no longer change or function when you change your input data. Input cells usually turn gray when a non-zero value is entered into them.

You can copy the input sheet to the unprotected Presentation sheet, then make notes there, so you'll know why you did what you did in the future.

You switch between the sheets (pages) that make up the workbook (AKA spreadsheet) by either clicking on the sheet tab names at the bottom left of the Excel window, or by pressing Control Page Up or Down (pressing the Control key and either the Page Up or Page Down key at the same time).

If you're seeing this: ####, then either increase your Zoom magnification number (using the slider at the bottom right), or make the column width wider. If you see this: #REF! or #DIV/0! after completing your input, please respond to get a new program immediately. This means data was lost either via e-mail, unzipping, or while making a CD.

If a red Error! dialog box comes up saying your input must be confined to a range of values, and your input was within that range, then the problem is that the input cell doesn't accept pennies. Try again rounding the value to the nearest dollar.

Before getting started, save an unused copy of all of the files in a separate folder, so you'll always have the original unaltered files somewhere. Then if you do something like delete a formula, you can easily fix it by copying it back using these original files. Then save files you've worked on using a different file name (and into a different folder). More tips for saving and organizing files are here.

Programs usually come without sample input data, but be sure to delete all of the sample / client input (everything in green-shaded cells) before inputting your data.

You should make CD (or at least USB flash drive) backups of all of your work at least on a monthly basis, and store the CD in a safe location, but not where your computer is. More generic free PC tips are here.

If you're an investing consumer (not a professional financial planner working with clients), then when you read "client, prospect, or they" just think "you." You would be both the advisor and the client (or spouse).

Read about financial planning software support here.

Read about financial planning software integration (sharing data between the different personal finance software modules).

How to send financial plans to clients via e-mail, without sending the whole program is explained on the Excel help page.

Read how to present sample reports to prospects and clients.

College Planner Calculator Software Instructions

People are interested in knowing how much they have to save to put children through college, or even go themselves. Even though the program was designed for that, it can be used for other things too.

Here are the sample directions for using it to put an 11-year-old through six years of college starting at age 18. It tracks with both the single-student demo, and Student #1 on the multiple-student demo.

The directions are the same for the five-student multiple college planner as it is for the single student college calculator. For the multiple-student college planner, just start at Kid #1 Input, and then continue with Kid #2 Input, etc. Then look at the single student results, and all of the student's results combined, using the two far left sheets.

The program will open up to the Input sheet. This is where you input most of the data.

About the only difference in input sheets on the multiple-student version is that cells B3, B4, & B6 only work on the Student #1 sheet. On the other four, this data is just referenced from there. This just make it easier so you won't have to type it in when it's the same all the time anyway.

• B3 is the input field that makes the date show up in row five of the Results sheets.

• B4 is when there's an advisor making reports for clients. You can leave this blank and/or delete it in several ways on the Results sheets.

• Input your name (professionals, input the client's full name) into cell B6. This makes it look nice on the Results sheets as it shows what you input here.

• Input basic student info into B7 through B17.

• Input how much there is saved up for college now into cell B12. Input zero or leave it blank if there's nothing saved currently. The program will calculate the need based on both nothing currently being saved, and/or no monthly payments currently being made.

• Input how much is planned to be saved into B14. It's in monthly format, so whatever you plan to save, figure it in monthly numbers. Input zero or leave it blank if you're not currently saving anything. The program will calculate the need based on both nothing currently being saved, and/or no monthly payments currently being made.

• Enter the rate of annual increase you plan to make in contributions into B15. For example, if you wanted to save $100 per month in one year, then $110 per month the next year, then input 10%. Input zero or leave it blank if you're not currently saving anything, or if you expect there to be no growth in contributions.

• Enter the year you plan to start saving (the monthly contributions) into B16. Input zero or leave it blank if you're not currently saving anything.

• Enter the year you plan to stop saving (the monthly contributions) into B17. You can continue to contribute to the cause throughout college, and even after college is over. Input zero or leave it blank if you're not currently saving anything.

If any of these are confusing, then just look at the Calculation sheets and tinker with it until you get what you want.

529 College Savings Plan Inputs

• Input the investment rate of return into B19. This is where the "Offset Factor" comes into play.

Once you buy a supported college calculator, then after you log into your state plan's website, you'll know the hidden list of investment options, track record of investment performance, and the fees and expenses. Then we'll supply an "Offset Factor" and an expense factor.

So if a plan has an Offset Factor of 3%, then that's how much more return is guesstimated that you'll probably get by having a monkey throw darts at a dozen Vanguard Index funds in a discount brokerage account, over that 529 plan's limited list of investment options.

As you'll see, when you add the Offset Factor to the total 529 plan expense factor (that is to be input into cell B21 of the Input sheet), then you'll get why this rant is here and why you should avoid 529 plans.

Every 529 plan costs money to use, so you'll know what the bottom line really is accounting for all "hidden fees and expenses" - like high 12b-1 fees on mutual fund B- and C-shares, AND the poorly-performing investment options. We won't know until you can log in and tell us, because the information is a well-hidden secret, and it changes all the time.

So if you bought support, then after you supply us with the current information, then we'll help you input it all (even down to the $25 initial setup fee) so everything is accounted for.

• Input the combined annual amount of fees and expenses the 529 plan charges into cell B21.

If you're funding it with mutual fund B- or C-shares, then add 1% to the combined 529 plan fees input in the step above. In general, always avoid these types of shares.

If you bought support, then you can ask about this and you'll get an answer.

• Input by using the drop-down menu, how you plan to pay for investments in the 529 plan. These plans are a "Wall Street innovation," so you'll be paying one way or another. If someone says there's no charge, then you're probably paying over 1% in 12b-1 mutual fund B- or C-shares. In this case, add this 1% to the expenses factor as explained in the previous step, and then delete all values in cells B27, B29, and B30.

• If you're paying an initial commission on investments (AKA front-end loads on mutual funds, or commissions on stocks or ETFs), then choose "Percent Commissions" in cell B23 then input this percentage into cell B25.

• If you're paying a Wrap fee, then choose "Wrap" in cell B23 then input this percentage into cell B27.

• If you're only paying ticket charges when buying no-load mutual funds, then choose "Flat Fee Per Trade" in cell B23. Then input the cost of one ticket into cell B29, then the estimated average number of annual trades into cell B30. The annual costs are just these two numbers multiplied together.

There's no taxes to consider in 529 plans with this calculator, so there's no inputs for that. They operate similarly to Roth IRAs - so you do not get an initial tax deduction, but the interest, dividend, and capital gains distributions are not taxed. Then the program also assumes that you'll be spending ALL of the withdrawals on "qualified expenses" so there's no taxes to pay on withdrawals (even though this isn't Real World). So this is another factor that make 529 plans not such a great deal compared to DIY.

Do-it-yourself (DIY) Savings Plan Inputs

• Input the average rate of return into cell B32 for the DIY plan. This is up to you, but a moderate well-managed portfolio may earn around 8% on average. People winging it themselves without an asset allocation plan usually get around 6% these days. If you think your brilliant market timing strategies using stock or ETF trading will do the trick, then input 5% (because it won't!). It's better to be more conservative by using lower numbers than being optimistic and using numbers more than 8%.

If you have more than one investment with a different rates of return, you can account for this blended rate like this:

Investment A is $50,000 growing at 5%
Investment B is $25,000 growing at 10%
Investment C is $25,000 growing at 7%

Investment A is 50% of the portfolio (0.5), and the other two are both 25% (0.25).

Here is how to do the math:
0.5 * 0.05 = 0.025
0.25 * 0.1 = 0.025
0.25 * 0.07 = 0.018

Add the three results = 0.068, or your blended rate of return is 6.8%.

So in this case, add all three investment values together to get $100,000 and then input 6.8% as the rate of return.

You can make these calculations on a calculator or a Results sheet.

• In cells B33 and B34: These are the annual taxable yields you expect to get with your investment strategy.

So if you think you're going to get 8% total return, but 25% of that will taxable dividend yield, or 2%, then input 25% here.

Repeat in cell B34 for the annual taxable realized capital gains distributions (profits the fund managers take inside mutual funds). These used to be so much they were annoying in the 20^th century, but they're insignificant nowadays.

These yields are shown in the text in the input cell, so just tinker with it until you get what you want.

• In cells B36, B37 and B39 and B40, input your average tax brackets for your situation. Remember that this average will be far less than what you probably want to input here - which are called "marginal rates." Few people actually pay marginal rates in the Real World. So do the math and you'll see it's usually around half this much. There is an average tax bracket calculator for this on the RWR retirement software and on the TVM Financial Tools.

Back to Generic Inputting

• Input the assumed rate of expense inflation into B52. You can contact the college and ask what their increases in expenses have actually been over time.

Historically, college costs have been going up two to three times the overall inflation rate. 6 to 10% is not unusual for some schools. This inflation rate will be applied to all of the expenses input into cells B56 - 77, if nothing is input into the inflation inputs in columns D and G.

When you use column D or G, whatever is in B52 is ignored (the manually inputted inflation rates on each expense overrides the global default rate).

• Input a conservative rate of investment return into B51. This is used to calculate how much more is needed if there is not enough money to fund the scenario (~4%). The higher the assumed rate of return, the lower the amount of money will be needed because it grows faster. This is shown on the bottom of a Results sheets. The Monte Carlo macro does not tinker with this.

Here is where the difference between public and private colleges comes in:

• In cell B56, start inputting all of your estimated expenses while attending a public college (or whatever scenario you're working with, it doesn't have to be public vs. private, but it's set up that way because that's what most people are going to use it for). This includes everything - tuition, fees, books, parking, living expenses, breaks, vacation, beer money, etc.

This is in today's dollars, meaning do not account for any inflation. The best thing to do is call the college (or look it up on-line). If you don't know the exact college, then call a few and get an average. You don't have to be limited to only comparing public vs. private college costs here; you can also compare living at home vs. dorm, etc.

• Input how much as a percent all of the expenses will be paid for out of pocket in cell B80. Then do the same in cell E80.

If you input 40%, then the program will use the other 60% to calculate how much the investment fund will need to have to reach the goal. This 40% is not accounted for anywhere in the calculation part of the program, as it's assumed that someone is just paying it out of pocket when the time comes. The program only solves for the investment fund part (the 60%). These are all shown annually in columns BD / BD or BU / BV of the Calculation sheets.

• Input annual inflation rates for each expense in columns D & G (rows 56 - 77). If you leave any expense inflation rate blank, that expense's inflation rate will default to the global rate input into cell B52. To inflate all expenses at the rate input into cell B52, don't input anything into these cells (this will save time).

How to change the data range in the charts: Even though the college planner has a thirty-year window, most people graduate long before then. So to get the most information out of the charts, only twenty years of data is shown when you get the program (and in the demo). You can use any data range you want to minimize clutter and/or show longer periods of time.

Here's how to do it: Go to a Results sheet. Click once on the inside of the chart.

Press the Up or Down arrow key until a long formula appears in the formula bar. Keep going up or down until the end of the formula is this: ",1)" This is the first data series of five.

You'll also need to do the same thing with all five series to make the chart look right.

You can also use the automatic Excel '07 feature by right clicking inside the chart and then choosing Select Data. Then you'll probably be able to figure it out from there by highlighting ranges of data.

There are usually three sets of data here. The single cell formula at the left grabs the column's name. The middle range is the age or year numbers. The range at the right is the actual data range. See where it goes from row 5 to 20? That's what needs to be changed. Row 20 is year 16 on the 529 Calculations sheets. So if you only want to show ten years of data, then change both 20s in the formula to be 14s. If you want to see 25 years, then change them all to 29s.

In each formula, both ranges need to be changed, and then the same needs to be done with the other four series of data in the chart. Up or down arrow to find them and change them all. When they're all good, then the chart will look like you want. Don't be afraid to experiment, as you can always undo, or go back to the original template (that you should have stored in a safe place once you got the attachment out of your e-mail or CD). Of course, if you have support, we'll help you with this.

The "Inflated Present Value of College Education with Taxes" (in range F24 - H27 on the DIY Results sheets) are the amounts of money you'd need to fully fund college now.

So if the parent and Grandma were sitting with a financial planner, and all Grandma wanted to know was how much to write a check to the financial planner for to fully fund an education, cells F27 or N27 on the DIY Results sheet, or F21 or N21 on the 529 Results sheets display these amounts. Note that these results cells are also what you would reference into the Life Insurance Needs Calculator (cells A35 - 40 of the Input sheet), if you want to insure for that. This is how much is needed today to fully fund the deal, given the assumed discount rate for the investment accounts you input into cell B51.

The Monthly Payments Needed to Fund Deficits is how much you'd need to save, and spend, all the way through the end of college. These amounts are in addition to how much you have already input into the lump sum available now and the monthly payments you input as planning to make. The same goes for the Lump Sum Needed Now to Fund Deficits - this is in addition to amounts you input as having saved already, and intend to save.

If you want to see how much in monthly payments it would take to fund college, but not have to contribute while the student is in college, then do this: First run the program given all of the usual assumptions, but with no monthly contributions, in order to generate the monthly contributions needed to fund college. Now set the last year to make contributions to be the year before college starts.

About Using Excel's Built-in Goal Seek Function

Now use Goal Seek to find the answer like this: Click on either cell G36 or O36 of a 529 Results sheet. Click Data, What-if Analysis, Goal Seek. Set the middle field to be zero. Click on the bottom field of Goal Seek, and then click on cell B14 of the Input sheet. Click OK. This will change the amount of monthly payments needed to result in an investment account large enough so that when the student starts college, it will be enough, and it will be depleted around the time it ends, without having to contribute while the student is in college. This answers the most-common question of, "If I have this much saved up now, and everything gets the average rate of return I input, then how much in monthly payments will I need to make from now until college starts, to fully fund everything?" When Goal Seek is done, the result will display on the Input sheet.

How to use Goal Seek to obtain the delta (difference needed to be at par with) between 529 and DIY: Click on either cell G51 or O51 of a DIY Results sheet. Click Data, What-if Analysis, Goal Seek. Set the middle field to be either G38 or O38 on a 529 Results sheet. Click on the bottom field of Goal Seek, and then click on cell B32 of the Input sheet. Click OK. The rate of return will iterate (change) until both of the funding deficits (or zero) are equal. B32 will show how much rate of return is needed in the DIY plan to be at par with the 529 plan - after the wonderful tax breaks of the 529 plan are taken into account.

Here's another example of using Excel's built-in Goal Seek functions: If you wanted to see how much rent you could afford, you'd click on cell G29 of a Results sheet, then go use, Goal Seek, click the middle field, input 0, go to the bottom field, then go to the Input sheet and click on the Rent input field, and it changes the amount of rent until you don't need to save anymore.

Using the Calculation Sheet Manual Override Input Fields

• Use Control Page Down (control Page Down, or hold down the control key while pressing the Page Down key) to move to a Calculation sheet. Or you can click on the sheet tab. The five sheet tab names are in the lower left part of the worksheet.

If you expect money to come in, or go out, at random; use the Annual Contribution Manual Override column K to account for this. Just keep in mind that amounts inputted wipe out the numbers that were already there - they do not just add to them.

If you want to model a different investment account rate of return in a certain year, just input that rate of return into column Q and it will be that for just that year. They can be negative here.

If you want to manually override all of the investment account expenses in a certain year, then use column X or W. This will override everything in columns U through W, but not L (commissions).

If you want to manually override dividends or capital gains distributions, or their taxes, then use those columns on the DIY Calculations sheets.

If you want to manually override the year's total amount of college expenses (not the investment account expenses), then use column AT or AC. There is also a text column next to these so you can input notes so you'll remember why you did what you did. Here's an example to show how this works:

This only has an effect on things when the student is In-College (shown on a few columns). On the single-student demo, public college, Junior has a total expense of $8,659 in the first year of college. If Junior wanted to take a $10,000 trip to Europe in that year, you'd input $18,659 into AC12.

If you really wanted to make a withdrawal before college, then use column X or W (the investment expense manual override). The numbers will show in the wrong results areas, but the math will be the same, because the money is just deducted from the investment account. If you want to account for taxes due on the these withdrawals, then just add them to the withdrawal amount, because they're not calculated from these areas..

That's pretty much it if you don't want to use any of the manual overrides on the Calculation sheet. Now you just switch over to a Results sheet and evaluate the results. Be sure to see the other charts below.

That's about it, you just input various scenarios, and look at the results.

Using the Monte Carlo Function

Go to the far right Monte Carlo sheet. Press control M (hold the control key down and press the M or m key). If that doesn't work, the go to View, Macros, View Macros, choose, Monte, then click Run. You have to be on the Monte Carlo sheet when you press control M or run it from the menu.

A macro runs (the screen flopping around is normal). When it stops and asks you if it's okay to delete the temporary sheet, click Delete. It then ends up on the 529 results cell with the resulting probability number. All of the probability numbers were calculated, not just the one it landed on.

Note that these numbers will remain until you run the macro again, even when you subsequently change other input data. So as soon as you change something after the macro runs, the Monte results will be “wrong.”

Also note than due to Excel limitations, Student's #4 and #5 don't have their results computed by the simulation (it only works on students #1 - #3).

The rates of return on all investments are incremented from getting an average rate of return from -10% to 10%. It then logs a true or false condition, based on if the amount of additional funding needed on the results sheet is zero or not. The proportion of times it’s true is then used to display the “probability of success” number.

It increments inflation and taxes too. The first iteration, uses a -10% average rate of return, a 4% inflation rate in all three input fields, and a 15% average tax bracket. The second uses -10% rate of return, 4% inflation, and 25% tax rate. The third uses -10%, 6% inflation, and 15% tax rate. The fourth iteration uses -10%, 6% inflation, and 25% tax rate. The fifth uses -10%, 8% inflation, and 15% tax rate. The sixth uses -10%, 8% inflation, and 25% tax rate. Then it cycles in one half percent increments more rate of return until all combinations of the three variables are solved for.

This is a total of 246 iterations. The maximum Excel can handle is 251. This bottom-line probability of success number displayed is just telling you the chances of not needing any more capital until the end of college.

We feel this is a superior methodology compared to what other vendors do. Others use average asset rates of return from -100% to 100%. In the Real World, the chances of losing all of your money in an investment portfolio in one year are just about as negligible as doubling your money in one year. So only our college planners iterates between the ranges that are most likely what’s going to happen in the Real World.

We don’t use rates of return over 10% because this is a long-term average rate of return, and few can get such a high average over the long-term (ten years or more - nobody has ever averaged that in the 21^st century so far). Also, the chances of getting a negative 10% rate a return every year for over a decade is also just as unlikely.

Our Monte Carlo results will probably show a much lower probability of success than what you may be used to seeing. This is because it paints a much less rosy scenario than other vendors. Our numbers are more realistic relative to what will probably happen.

The inflation and tax iterations are also just three and two examples of good to worst-case scenarios. So even though this is a different way of doing things, we feel that it makes the bottom-line probability number much more meaningful, and projects what’s going to happen in the future, better than what everyone else is doing.

Please note that all of these numbers are as good as computers can generate, but they will still all be wrong in the Real World.

Things to keep in mind:

• Rates of return, and inflation on individual expenses, input into the manual override columns will override simulated rates in those years. This means the simulation will not have any effect on those expenses in that year.

• You’ll need to keep the workbook unprotected so the macro can create, write the results to, and then delete the Scenarios sheet.

• You can’t add nor delete sheets between the far left and far right sheets (you can only add before the far left or add after the far right sheet). This is another Microsoft limitation because the macro counts the number of sheets when it moves around instead of working with sheet tab names. This means you can rename the sheet tabs, but not move them around in any way.