College Funding 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 the directions. When you're using the program, things go better if you print these directions.

The "demo" is the actual spreadsheet, password protected, without the formulas so it's much smaller. You won't be able to do anything but look at it. You'll receive a working non-protected file after you pay for it.

If you try to print the demo from Internet Explorer, or you just click Print in Excel, it won't work well. Sorry, but to preserve content, the demos are password protected, and you have to do this to print.

If you're looking at a demo in your browser (if it says anything other than Microsoft Excel at the very top left of your screen), and want to print, then either save the spreadsheet to a folder on your hard drive, or go back and Right click on the demo link, and then use Save Target As... to save it to a folder on your hard drive, then open it with MS Excel. Tips for working and printing in Excel are here, here, and here. The pages on the free sample comprehensive financial plan print better than the demo.

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

Basic input for most all of these Excel spreadsheets: Input your data into the green-shaded cells of the input sheets. This will populate the presentation sheets, 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, and you may end up damaging the spreadsheet.

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 by either clicking on the sheet tab names at the bottom left of the Excel window, or by pressing *Page Up or Down (pressing the control key and either the Page Up or Page Down key on your keyboard at the same time).

If you're seeing this: ####, then either increase your Zoom magnification number (View, Zoom), 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. Round the value to the nearest dollar and try again.

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 from these original files. Then save files you've worked on using a different file name (and into a different folder).

If you don't like a file being read-only, go to Save As, and then go to Tools, General Options, uncheck the Read Only box, then click Save. 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 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 should make CD backups of all of your work on a monthly basis, and store the CD in a safe location, but not where your computer is. More 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 modules) here.

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

Read how to present sample reports to prospects and clients.

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

College Calculator 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 a 10-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.

There are no manual overrides shown in the multiple student college demo to minimize clutter. See the single-student college plan demo for manual override examples.

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

º Input your name (professionals, input the client's full name) in cell B3. This makes it look nice on the Presentation sheet.

º Input the current year into B4. This gives a point reference for calculations.

º Input basic student info into B6 through B8.

º Input how much you have saved up for college now into cell B9. Input zero or leave it blank if you don't have anything saved currently. The program will calculate the need based on both nothing currently being saved and no monthly payments currently being made.

º Input how much you plan to save into B10. 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 no monthly payments currently being made.

º Enter the rate of annual increase you plan to make in contributions into B11. 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. The program will calculate the need based on both nothing currently being saved and no monthly payments currently being made.

º Enter the year you plan to start saving (the monthly contributions) into B12. 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 no monthly payments currently being made.

º Enter the year you plan to stop saving (the monthly contributions) into B13. 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. The program will calculate the need based on both nothing currently being saved and no monthly payments currently being made.

º Input the investment rate of return into B14. This is up to you, but a moderate well-managed portfolio may earn around 10% on average. People winging it themselves without an asset allocation plan usually get around 7%.

Use the asset allocation sheets at the right to get both lower risk and better returns. If you're doing the bank thing (CDs or savings accounts), use whatever rates they offer. This is an after-tax rate of return, so set it to the assumed gross rate of return (before taxes) even if you have a Section 529 Plan. The differences in plans are accounted for automatically.

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

Asset A is $50,000 growing at 5%

Asset B is $25,000 growing at 10%

Asset C is $25,000 growing at 7%

Asset 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 asset 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 the Presentation sheet.

º Input the assumed rate of expense inflation into B15. 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 B21 - 42, if nothing is input into the inflation inputs in columns D and G.

º Input the assumed tax rate into B16. When withdrawals are made from the investment account, you'll either be selling things (hopefully at a profit) to raise money, or using bond/cash interest. All income is taxable in one way or another (unless you use municipal bonds) so take a conservative guess that you feel will work as an average. This is automatically set to be zero in the 529 Plan section.

º Input a conservative rate of investment return into B17. This is used to calculate how much more is needed if there is not enough money to fund the scenario. 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 the Presentation sheet.

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

º Starting in cell B21, 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 B45. Then do the same in cell E45. 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%). This is shown in columns AJ and AK of the Calculation sheets.

º Input annual inflation rates for each expense in columns D & G (Rows 21 - 42). If you leave any expense inflation rate blank, that expense's inflation rate will default to the global rate input into cell B15. To inflate all expenses at the rate input into cell B15, don't input anything into these cells.

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 the Presentation sheet. Click once on the inside of the top chart (for example, right under the title, Monthly Additional Sum Needed).

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 need to do the same thing with all five series to make the chart look right. There are three sets of data here. The single cell formula at the left grabs the column's name. The middle range are the age numbers. The range at the right is the actual data range. See where it goes from row 5 to 24? That's what needs to be changed. Row 24 is year 20. So if you only want to show ten years of data, then change both 24s in the formula to be 15s. 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" field is the amount of money you'd need to fully fund college now, assuming your savings earned a rate of return of 0%.

When you change this rate of return from zero to what you input as the "Investment Account Rate of Return" in cell B14, this huge present value number deflates into the PV displayed in cells G27 and G28. 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 G27 or G28 displays these amounts.

The Monthly Payments Needed to Fund Deficits are 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.

Now use Goal Seek to find the answer like this: Click on either cell G30 or G31 of the Presentation sheet. Click Tools, Goal Seek. Set the middle field to be zero. Click on the bottom field of Goal Seek, and then click on cell B10 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.

You can hide the blank rows, those without children, on the two far left sheets, to reduce clutter (Rows 7-9, 12-14, etc.).

To fund college when the student is currently in college, input amounts into the column F manual overrides.

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 the Presentation sheet and evaluate the results.

Note: If there are negative values on the End of Year Investment Fund graphs, you can ignore them. They're not meaningful because you can't have a negative investment fund. They also only show numbers until the last year of college, and then they go away. Use the lump sum and monthly needs amounts when it comes to deficit numbers on the presentation pages. If you want to see running need numbers, they are on column AX of the calculation sheets.

Here are the directions for using the investment asset allocation sheets

Using the Calculation Sheet Manual Override Input Fields

º *PageDown (control PageDown, or hold down the control key while pressing the Page Down key) to move to the 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 columns (F, P, & R) to account for this. You can enter a text note into column G to help keep things straight in your head (it doesn't' affect any calculations).

In the single demo, a Savings Bond matures and is added to the account at the student's age of 13. The Savings Bond is substituted for other contributions in the demo. If you wanted to add the $10,000 to what you already planned for the year, then you'd add $10,000 to whatever number was in cell H8 (before you input the $10,000). This is common when parents plan to add so much, and then someone else gives a gift.

º For whatever reason, when Junior is age 20, you expect to have 20% rate of expense increase instead of 10%. So 20 was entered into K15. You can enter any number you want, but be realistic. The next year was an expense decrease of 10%, so you'd input -10 into cell K16.

º When Junior is 19; he takes a $10,000 trip to Europe. So $10,000 was input into cell P14. The next year, he pays for it by having to cut back on his expenses. The $20,000 in cell R15 is $9,201 less than what was in cell S15 before $20,000 was entered into cell R15, so this is how much he had to cut back on beer.

º You can change the investment account rate of return in every year too in column W. In the demo, a 25% loss was forecasted in year number three. This could have been avoided by subscribing to our mutual fund picks to be used with the asset allocation sheet.

º Look at the results on the Presentation sheet. In this scenario, things didn't go well for Junior, and he ran out of money. Shouldn't have taken that trip to Europe! The amount of money needed today to make up the difference is shown in cells G27 and G29, and again in cells O27 and O29.

Be sure to see the other charts below.

That's about it, you just input various scenarios, and look at the results. Here's an 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 the Presentation sheet, then go to Tools, 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 Monte Carlo Function

Go to the far left Monte Carlo sheet. Press control M (hold the control key down and press the M key). A macro runs. When it stops and asks you if it's okay to delete the temporary sheet, click OK. It then ends up on the cell with the resulting probability number, and the minimum rate of return needed on all assets to barely reach the goal of funding college through the ending age input without running out of money.

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, it will be “wrong.”

The rates of return on all assets are increment 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 increment 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.

The accompanying number, Minimum Rate of Return on All Assets Needed for Success, is the average of all of the minimum rates of return that were successful.

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. So only our college planners iterates between the two 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). 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 lower probability of success. 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:

- Since all of your asset rates of return, inflation, and tax rates were overwritten, the last step of the macro replaces them with 8% for return, 8% inflation, and 17% tax rate.

- Rates of return, and inflation on individual expenses, input into the manual override columns will override simulated rates in those years.

- You’ll need to keep the workbook and the Assumptions and Additional Need sheet unprotected so the macro can write the results to it.

- You need to be on the appropriate Monte Carlo sheet for the macro to run.

You can read more about Monte Carlo here.

How to Use on Student's Surplus to Fund Another Student's Deficits

If a family has more than one student input into the Multiple College Planner, and one student has money left over after they graduate, this surplus can easily be used to fund a deficit situation for another student.

All you need to do is use column AM on the Calculation sheet for the student with the surplus. Start by inputting $1,000 in any of the first five years. This will take (an additional) $1,000 out of their investment fund. Taxes are assumed to be paid on the withdrawals. You can make adjustments to account for this.

Then input $1,000 in column G on the Calculation sheet for the student with the deficit. If there are already contributions in that year, then add the $1,000 in the manual override column to the existing amount.

This takes money from one student's investment fund and puts it into the other one.

All you need to do is ensure this is done in the same year, and that you add the amounts to any existing contributions, because using the manual overrides will wipe out existing contributions.

Product Information

Menu of Retirement Planning Software

Asset Allocation Overview with Our Historical Returns

Asset Allocation Software

Asset Allocation Models with Our Historical Returns

Our Monthly Updated Mutual Fund Picks

Financial Plan Tools

Family Budgeting Calculator

Financial Planning Fact Finders

Investment Policy Statement Software

Bond Calculators

Investment Software for Comparing 22 Methods of Investing