Buy Family Budget Software Now

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 program with non-protected input cells after you pay for it.

If you try to print the demo from Internet Explorer, or you just click Print in Excel, it probably 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.

If you have Excel 2007, then first save the workbook as an xlsx workbook. When you get the info dialog box about "features that are not supported," just click OK.

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, which will then 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 (if it's not a presentation sheet). If you could, then 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 at the same time).

If you're seeing this: ####, then either increase your Zoom magnification number (View, Zoom, or use the slider bottom right in Excel 2007), 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 back using these original files. Then save files you've worked on using a different file name (and into a different folder). 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 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.

Family Budget Tool Instructions

Cash Flow Projector instructions are below. Begin here, even if you have the Cash Flow Projector

º After unzipping it out of your e-mail, open the spreadsheet Budget.xls or Cash Flow Projector.xls using any version of Excel.

It will open to the Fixed Monthly Expenses section of the Budget (All Expenses) sheet. The Cash Flow Projector will open to the Income sheet because it's better to input incomes before expenses.

Feel free to use Excel's freeze panes function to make it easier to look at. A few of the sheets have the panes frozen to make it easier to work with. They are frozen on cells C6 of the Income Forecaster sheets, F4 on the Variable Expenses sheet, and C6 of the Cash Flow Projections sheets. Go to Window, Unfreeze Panes, to turn it off and on.

Common incomes and expenses are listed in alphabetical order.

º If you don't have the Cash Flow Projector, then input the current year into cell AD3 of the Results sheet. For the Cash Flow Projector, input the current year into cell C3 of the Income Forecaster sheet.

This is the best time to point out the best way to get budget comparison with the disabled scenario. When you're done inputting each section's data in the normal times area, copy and paste it into the disabled section. So when you're done inputting normal times data, you should see the same results in the disabled section. If not, then track it down and fix it. When they match, then you know you did it right. Then you can alter input in the disabled areas to match the disabled scenario. Doing it as you go works better than doing it when you're done.

º Input your incomes into the Budget (All Expenses) sheet, starting in cell AC5 for normal times, and AF5 for disabled times. Inputting incomes is different for the Cash Flow Projector as you input incomes into the Income Forecaster sheet.

º Input your fixed expenses into the green-shaded areas starting in cell C4 (of the Budget (All Expenses) sheet). Sections for fixed, variable expenses, and debts, list all of their components in alphabetical order. Input only into the green-shaded areas of the fixed expenses and debt payments areas.

Fixed expenses are things you pay for that have the same (fixed) costs every month. They may go up and down over time, but compared to the variable expenses (which change a lot in the short-run) they remain the same most all the time. One of the reasons they're separate is so you won't have to input expenses over and over again that are the same.

º Input your monthly debt payments into the green-shaded areas starting at the top of column I.

º If you want to have a fixed expense that's not already listed, use one of the miscellaneous input areas starting in cell B21 and C21. You can change the names of the miscellaneous expenses from B21 to B39, but not any of the other ones (you can but it will mess things up in other places).

º Go to the 2010 Variable Expenses sheet. Now decide whether you want to manually enter all of your variable expenses, or use guessing averages, which will save a lot of input.

º If you want to start using Real World numbers, use the appropriate column (weeks beginning on Sunday dates are all in row 3) and enter the variable expense amounts. Keep receipts of all money you spend, and once a week, input them all into the appropriate row and column. You can account for every dollar spent if you like that much detail.

Every time you use another week, increment cell E3 by 1. You can fiddle with E3 to look at how the average changes as time goes by. The number in cell E3 is the divisor used to compute the weekly averages. This is not an automatic function because it gives the user control over computing averages.

For example, if you're just starting out in the first week of a new year, all you're going to have are two sets of numbers to work with - the Prior Averages in column F and the first week's data in column G.

The amount you spend on variable expenses the first week will be miniscule compared to the prior averages, so to get more control over the average, you can set the number of weeks passed in the year to 1 to just add column F and G together, instead of getting an average by dividing them both by 2 (all of the column's of data (G-BF) are added up and then divided by this number.

So if you have it set to 1, all of them are just added up and the sum is average weekly expense. If you set it to 2, then the total is divided by two, which isn't realistic, because you're not going to have hardly any variable expenses to account for in only one week). When you're in the beginning of the year, you can account for these distortions by fiddling with cell E3 until you think the averages are correct. Yes, you can use decimals here.

You can use blank green or white cells to write notes in. This text doesn't affect any calculations, so don't be afraid to jot down notes. Just don't input numbers into white cells.

º If you want to compare what you're actually spending with what variable expenses you forecasted, estimated, or want to set a spending limit on, you can input these estimates into the green-shaded areas of column A. The monthly numbers you input are then divided by 4.35 to arrive at the weekly amounts in column B. This number is then subtracted from the expense average in column E, and the difference is reported in column D.

Starting in row 295, you can see the summed totals of all estimated expenses compared to the actual amounts you're spending.

If you're seeing red negative numbers, this means that you're actually spending more than you input as estimates/forecasts/spending limits.

If you want to get rid of an expense's red deficit number, here's what to do: Assume you don't want to see any red numbers in cell D65. Take the number shown in cell E65  and multiply it by 4.35 in cell A65, like this: =number in E65 typed in*4.35     You can't just do this:  =E65*4.35   in cell A65 because it's a circular reference. So if it says Circular at the bottom of the Excel pane, and gives goofy results everywhere, this is why.

º Lastly, you want to account for another important family budgeting factor that is commonly overlooked - the expenses of replacing things that wear out and need to be replaced over time. As you will see this is a major expense category, and one of the reasons budgets never seem to add up.

Go to the Replacements sheet. For each item listed in column A, input data in ALL of the green-shaded cells to the right of the expense item's name.

If any of the fields are blank (zero), then none of the figures will work to the right. So if you really want to input a zero amount, input 0.0001 instead. There is text in the column headings in row 2 to explain what each input cell is asking for, and why.

Then starting in row 28, all of these monthly total amounts are multiplied by 12 to get annual figures, and then they are all summed up in row 50. The totals in B50 is divided by 12, and is then sent to cell C44 of the Budget (All Expenses) sheet (and sent to the Cash Flow Projector sheet in the fixed expenses area under Replacements).

Cell C44 of the Budget (All Expenses) sheet, and the formulas in the Projector sheets, are not protected, so you can change this end result if you want to. Most people are surprised by how much this is, and so they reduce it. This is not a good thing to do as some things wear out faster than planned, and/or cost more to replace than estimated.

The most confusing thing here is the interest rates. For example, in the demo, one may question why the current cost of carpet is only $600, but the present value of this cost is $708. This is due to the difference in interest rates you input into columns D and G. In this example, the cost of carpet goes up 10% per year, but you're only getting 3% in a bank savings account to save for this future expense. So if you put $708 into a savings account today to have money to replace carpet in five years, it will grow at 3%, while the cost of carpet is going up at 10% every year. So the inflation rate is more than the savings rate, so more money is needed to close this ever-increasing gap. This affect is illustrated in reverse when it comes to the next item - computer replacement. The cost of computers drop every year, so the present value is less than the cost of replacing it today, because the bank savings rate is much more than the computer deflation rate. When you input the same numbers into columns D and G, then the present value will equal the replacement cost today.

Repeat the same input in the Disabled Replacements sheet, but keep in mind that you will probably want to tinker with these amounts to account for being disabled. For example, vehicle replacements went from $26,000 to $10,000 in the demo, because one will probably drive less, and care less about having an expensive vehicle, if one is disabled.

That's about it. Then you just look at the Results sheet and look at the numbers.

Budgeting for Future Expenditures or Goals

You can budget/plan/save for future expenditures/goals (in the same year) by simply inputting the expense in the future. For example, if you want to see what affect saving for a down payment on an auto will have on your monthly/weekly/annual "nut," just enter the weekly amount in future weeks in the Expenses sheet.

If you want to have $10,000 at the end of November, just input $10,000 as a weekly expense in the last week of November. The program will compute an average weekly/monthly amount that you'll need to save from now until then.

Computing Gas Mileage

This is all done on the 2010 Variable Expenses sheet.

First think of how much gas your Vehicle #1 holds and input that into cell G201. Then input what the total current cost of that would be into cell G203. You can use an Excel formula to guess like this (input this into cell G203  if gas is going for $3.50 per gallon): =G201*3.50

Then input how far you think you can go on a full tank of gas. You can do that like this: Guess the gas mileage and multiply that times the number of gallons it can hold. For example, if you have a small new car, and it holds 10 gallons, and you get 25 MPG, enter this into cell G202: =G201*25  (the answer is 250 miles)

Record your current odometer reading by inputting it into any green-shaded cell in row 202 (if you gas up weekly, then cell H202).

The next time you get gas, put this formula into that same cell in row 202 (after you recorded what it was. This finds how many miles you went since getting gas last). This example is in cell H202: =26400-26000  Then input the number of gallons into cell H201 (if that's the column you're using) and the cost into cell H203.

Your gas mileage for that tank will be computed, along with the average of all of your other input (shown in cell E205).

Updating the Budget Calculator and Expense Sheets to New Years

º Right click on the 2009 Variable Expenses tab (or whatever the latest year is on your version). Choose Move or Copy. Click the little box at the bottom that says, Create a copy. A new worksheet will be made with the same data as the one you copied.

º Right click on the new sheet and choose Rename. Then name it 2010 Variable Expenses (or the current year, or 2010 if you were going back in time). It may help now to move the old year sheet off to the right to get it out of the way and put the new sheet where the old one was.

º Ensure that cell E3 of the 2010 Variable Expenses sheet is set to 52.

º On the new sheet, input/copy the averages from column E from the 2010 Variable Expenses sheet into the Prior Year's Average column (column F of the old Variable Expenses sheet).

For example, the first section's average is in cell E5. Input that number directly into cell F5. Now delete all of the weekly input on the 2010 Variable Expenses sheet in row 5. So delete everything in white- and green-shaded cells from column F5 to BF5. In the next section, use the Misc. row (cell F48) to input the average shown in cell E66. Then delete everything in range G14:BF64. Repeat in the sections below until done. You can only delete data in cells that are white or green here. Keep cell E3 set to 52 until this step is done.

If you're going back more than one year, you would take all of the prior years' averages, and then average them. For example, if you have data of $21.00 for 2009 and $23.00 for 2010, enter $22.00 into column F. Actually, if you followed the instructions for year 2009, then that already accounted for 2009's averages on the 2010 Variable Expenses sheet, so you don't need to average them.

º Next change the week beginning dates on the 2010 Variable Expenses sheet. Go to cell G3 and edit the date to make it be the first Sunday in January (2010 would be 1/4/2010 in cell G3 and 1/11/2010 in cell H3). Then edit cell H3 to make it be the second Sunday in January. Then highlight both G3 and H3, then use the drag handle (the little black square at the bottom right of the highlighted cells, and drag it all the way to the end (the last week in the year). If you did it right, then all of the cells in row 3 will have changed to show the week ending Sundays for all years.

º Change the number of weeks that passed in cell E3 to 1 if you're doing this on the first of the year.

º Now you'll need to change some formulas on the Budget (All Expenses) sheet. Go to the Budget (All Expenses) sheet and unprotect it (Tools, Protection, Unprotect Sheet or Review in Excel 2007).

You can then use the global Find and Replace here to make this easy. Press Control H, and input "2009 Var" into the top field. Then "2010 Var" into the bottom field, then click OK. All of the references from the old 2009 Variable Expenses sheet have now been changed to the 2010 Variable Expenses sheet.

º Then just start over like you're doing now. As time goes on, you'll have a record of each year, and an average all of those years.

º For retirement and disability scenarios: Just enter the incomes and expenses you think you'll have under these scenarios. For example, if you're disabled, you can delete most vehicle expenses. And when you're retired, you can delete buying work clothes. This may work better if you save a different version with a different file name.

Remember that the whole point is to get averages that are meaningful to you.

Since the Cash Flow Projector just projects the numbers from the current budget, the instructions for inputting budget data are not repeated here. They are above, so if you jumped to here from scratch, some of the directions below won't make sense. Please start above at the Family Budget Instructions section.

Important: The program's formulas detect zeros in the input and manual override cells. So if you want to use a zero on any of the forecasting sheets, instead use a tiny number, like 0.0001. Inputting zero will result in it being displayed in red to let you know on the projection sheets.

Most everything is projected automatically except global inputting inflation rates, the current year, and people's names.

º First, go to the far left sheet tab - Income Forecaster. The best way to quickly go to the far left sheet tab is to press Control and then keep the Page Up key held down until things stop moving.

Input a default/global inflation rate into cell C2. This is the average you think all of the incomes will grow into the future. You can set any one income item to be different, in any year, as explained below.

º Finish basic input by entering the current year into cell C3, names into cells G3, K2, and K3, and current ages into O2 &O3.

º Scroll down column A to look for all sources of monthly income you, or your clients, currently have, or expect to have in the future. Input the appropriate items into the green-shaded cells in column A.

For example, if someone is getting $1,000 per month in alimony from someone else, input $1,000 into cell A11. If you didn't change the global inflation rate in cell C2, and then this $1,000 will grow at 3% annually for the whole 75 years.

This income is taxable, so choose "Y" from the drop down menu in cell A12.

You have three options on how to control the future of this income. First, at some point, this income will stop. When it does, you can stop it at any day of the year, instead of letting it go a whole year. The demo assumes alimony only lasts for five years and three months.

If you wanted to do this manually, go to cell H13 and input one quarter of the previous year's resulting amount (which is $3,377 per year as shown in cell G12). So you'd input $3,377 into cell H13 (or =G12*.25).

Now input 0.0001 into cell H14 to stop inflation for this year only. Don't input zero because the formulas as set to detect them, and it will be ignored. Now to stop it for good, input 0.0001 into cell I13.

You can use any method you want to control these figures down to the day. Remember to end it for good, input 0.0001 into the last year's manual override (in this example, cells I13 and W13). And also remember if you want to use exact figures like this, you'll need to input a tiny number into that year's inflation override to stop that years' figure from being inflated at the global inflation rate (in this case cells H14 and L14).

If for whatever reason, John started getting $500 per month in alimony again in year 10 (four years after getting nothing). Input $500 into cell L13 to restart it again. If you're working with exact annual dollar amounts like this, then remember to stop that year's inflation rate too. In this example, input 0.0001 into cell L14.

The year numbers are shown in row 4, above the years shown in row 7, and then the panes were froze on cell C8 to make it easier to see them as you move around the rest of the sheet.

Be sure to stop all incomes and expenses at the appropriate year, because in the Real World, most nothing lasts forever. Alimony is shown in the demo as starting back up in year ten, then stopping in year 21, just to illustrate how it works.

Next let's tinker with annual inflation rates as a way to control every number in every year, using the second income item "Bonus" starting in row 17. Let's assume that one's bonus inflates at 5% annual rate every year, and not the 3% global rate.

Input 5 into cell D22. Then use the drag handle (the tiny black box at the bottom right of the active cell) and drag it all the way over to column L. You can just drag it to have inflation be the same rate all the time, or you can input a different rate in every year. The point to remember is that every year has to have a non-zero number input into its Annual Inflation Rate override row, or it will default to the global rate in that year. So you can't just input 5% into cell D22 and expect it to use 5% instead of 3% for the rest of the 75 years.

In the demo, John retires in January of the 11th year, so 0.0001 was input into cell M21 to have this income in December of year 10, and have no income in January of year 11.

At the bottom of each income section, there is a corresponding tax question (see cell A12). For each income, choose Y if this income is taxed, or N if it's not.

Continue scrolling down column A and input all sources of incomes as described above.

There is a separate income input section for a spouse, starting after the first person's incomes are accounted for, and summed in rows 179-183.

The second input section can be anyone that contributes to the family budget, so it doesn't have to be a spouse. If you don't want to use two income sections, just add everyone's income up and input into the first section only.

There is another summing section showing everyone's individual totals, plus combined total, from rows 358-376. At the bottom of every subheading there is a row that shows the percentage difference increase/decrease from the previous year.

The same methodology is used for all incomes on this sheet (and the Disabled Income Forecaster sheet) and all expenses on the Cash Flow Projections sheets.

All of this may seem awkward compared to other family budget tools, but you can only control every number in every year with this family budget software, and basically can't control anything anytime with other budget software. Amounts change annually, and so for budget software to be able to handle all Real World scenarios, a little more cumbersome input is required.

You can also just ignore all of this and just use the global inflation rates and have every expense run amok forever, like other budget tools. That's not Real World, and all it takes is one thing to go wrong and your whole forecasted budget will be meaningless. So the more you want your budget software results to match your Real World life, the more you need to pay attention as you spend money, and input your data exactly like things happen in your Real World.

Special note about earned W-2 incomes: Inputting net paycheck income into the earned income areas may cause double counting of some expenses, undercounting income, and other distortions that will make the plan inaccurate.

So when you’re inputting work paycheck incomes, input GROSS figures. If you make $25 an hour, and work 40 hours, then your gross paycheck is $1,000 per week (so this is about $4,333 per month). Your paycheck will usually be a lot smaller than this because of all of the deductions. It’s important to input $4,333 and not the lower amount that’s commonly referred to as “take home pay.” You'll account for all of these payroll deductions (and taxes) in other areas, described below.

Cells C109 - C112 on the Results sheet display the percentage of income each person represents of total income.

We assume that you've already input your family budget data as described in the first section - Budget. This is the second section of the directions - Cash Flow Projecting.

º Now switch to the Cash Flow Projections sheet.

Common expenses are listed in alphabetical order.

Input a global expense inflation rate into cell C2. Try to use a figure a little more than the current reported inflation rate for the U.S. as a whole. This is called the "CPI" or Consumer Price Index.

º Cell D13 shows how you manually override the automatic global expense inflation rate for each expense. In the demo, the expense "Alcohol, etc." is inflated at a 5% annual rate, instead of the 3% global rate. This helps illustrate things like the ever-increasing taxes on cigarettes.

Input 5 into cell D13, and then drag it all the way over to the end of the sheet. The next expense, the cost of Bathroom/Kitchen Supplies, is forecasted to only increase at a 2% annual inflation rate, so the same thing was done here. You can get the most realistic picture of your future budget when you can accurately guess the price increases of all expense items. You'll never be right, but at least you'll be going in the right direction. Going in the right direction is the most important thing when you're dealing with financial planning software.

º If you don't want to manually override expenses or inflation, and don't care about letting them all run amok over the next 75 years, then that's basically all you need to do. You just look at the numbers on the Results sheet in the middle of the Workbook to analyze your results.

º Some common sense overrides that are important to use: When the mortgage stops, input 0.0001 into the appropriate year to stop that expense.

º For retirement and disability scenarios: Just enter the expense manual overrides, or inflation rate overrides, you think you'll have under these scenarios. For example, if you're disabled, you can delete most vehicle expenses by setting those expenses to be zero (0.0001). And when you're retired, you can delete, or reduce, buying work clothes and similar expense that will stop the same way.

In some of the demos, John and Mary retire in year 11, so you can look at how all of the incomes and expenses were manipulated to account for that. You may want to use these as generic guidelines when making your own financial plan. For example, expenses for hobbies, magazine subscriptions, travel, and the like will probably increase as you stop working and have more free time to play.

º You can start expenses from scratch that do not exist in the current year. For example, children's college expenses. Use the college expense row, or go to one of the expense rows not used, and then go to the column for the year the expense starts, and enter the estimated annual expense into the appropriate manual override cell. Use a higher inflation rate than you used for other things, as college costs go up about twice as much as the overall CPI index. Don't forget to stop it when it's over too.

Each income, expense, and result number on the Projections sheets has its own set of statistics: Standard Deviation, Maximum, Minimum, Average, and Median. These are shown at the end of the 75-year projections, or in other words, starting in column CA.

That's about it. Once you tinker with some input fields, and see what's going on, and get meaningful Real World results that will help keep you out of family budget trouble.

How to Control Taxes on the Cash Flow Projections Sheet

Switch to the Cash Flow Projections sheet.

There are two methods of dealing with taxes on the Cash Flow Projections sheet: Manual and automatic. Manual is discussed in a section below.

Everything on the Cash Flow Projections sheet is in joint-mode, so it’s not broken down by each person’s spending.

Using the automatic method of calculating taxes: Go to cell A1033 in the Fixed Expenses section. Here you will see the cells that control manual vs. automatic tax method selection for the four tax areas (Federal, State, FICO, and Misc./Local). You can control each of the four areas independent of each other.

Using the drop-down menu, choose “M” for manual, or “A” for automatic. Manual mode is discussed below.

Here’s how automatic mode works: Every income item that you chose Yes to be taxable on the Income Forecaster sheet, is totaled in each year. This gets total taxable income in that year.

You then choose an average tax bracket (not marginal), for each of the four tax areas. Then this percentage of total taxable income in that year is entered into the expense areas. Yes, you can manually override them in every year.

In some of the demos, 15% was input as the average tax bracket for Federal taxes. You can manually change these rates to be anything you want in any year.

If you chose the automatic taxing method, and then choose how much Social Security is to be included in taxable income in cell A136 and A315 of the Income Forecaster sheet via the drop-down menu. Be sure to stop this when retirement starts.

The biggest problem with calculating taxes far into the future is that taxes change way too much. With programs that calculate taxes owed, it goes off and just assumes that current tax law will be the same twenty years from now. This has never happened. So you're locked into using these assumptions, and they cannot be changed. So even though it does a good job for the first few years, you're then locked into using something that you know will be wrong after that.

This allows you to control taxes in every year using both methods. So if you think the Dems are going to win a future election, you can show this increase in tax rates then. With NaviPlan, you're stuck forecasting current rates for fifty years, when you know they'll be going way up in the next year.

How to Use the Disability Cash Flow Projection Sheets

For example, instead of clicking on cell C46 of the Budget (All Expenses) sheet, you could have clicked on any cell in row 19 of the Income Forecaster sheet, and John's annual rate of increase in his annual bonus would change to a crazy figure like, 600% per year, to make it so. This is not realistic, but you can do this, and any other crazy "What If" you can think of.

Real Estate Software

Net Worth Calculator

College Savings Calculator

Money eBook

Financial Planning Seminar

Financial Planning Marketing

Variable Annuity Tutorial and Optimizer

Services

Evolve into a Wealth Planner with this Unique Practice Management and Marketing System

Fee-only Consulting for Consumers, Investors, and Financial Planners

Building Custom Investment Benchmark Portfolios

Financial Planner Directory

How to Become Your Own RIA

Coaching for Financial Planners

Buy or Sell an Investment Management or Financial Planning Practice

Asset Allocation for 401(k) / 403(b) and Similar Retirement Plans