|Main Budget Tool Page||Download the Cash Flow Projector Demo||Budgeting Report Explanation Text|
(is listed below. The financial planning software modules for sale are on the right-side column)
Confused? It Makes More Sense if You Start at the Home Page
Discounts for Financial Advisers
Questions about Personal Finance Software? Call (503) 309-1369 or Send E-mail to firstname.lastname@example.org
Free Downloads and Money Tools
|First, here are some generic directions that apply to most of the personal finance software on this site:
If you're still shopping, the best evaluation results are obtained by looking at the budget and cash flow 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.
Click "Enable Editing" to make it so you can change inputs. If you don't do this, then "nothing will happen" when you input data. Any change is editing (according to MS). This occurs because the program is not in a "Trusted Location." Go to File, Options, Trust Center, to fix that.
If you need to print something on a password protected sheet, and you need to tinker with column and row sizes, and you bought support, and it's in Excel 2007 format, then you can get a version sent to you that does that.
The Cash Flow Projector 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. Here's the page with all of the sample plans.
Basic concept for most all of these Excel spreadsheets: Input your data into the green-shaded cells of the input sheets (or just the input areas if there are no separate input sheets). This data flows through the calculation sheets (or just areas if there are no separate 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 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 a light-purplish-gray when a non-zero value is entered into them.
About the colored cells: Medium or light gray is either a title heading, or an input cell after a non-zero value is input into it (it's green when either zero or empty). Peach (or pinkish, depending on your Excel version) is a (usually password protected) field that changes when input changes. Light gray cells are information-providing, non-input, cells that don't change with input. White cells usually don't have anything in them, except on the unprotected presentation sheets (and change when input changes). Anything red is usually bad (input error).
About the colored sheet tabs: Blue is a presentation sheet. Light green is an input sheet. Green is both an input sheet, and a presentation sheet. Orange is a calculation sheet. Purple is something else.
You can copy the input sheet to the unprotected Scratch Pad 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 change your Zoom magnification number (using the slider at the bottom right), or make the column width wider. If you see this: #REF! after completing your input, please respond to get a new program immediately. This usually 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. If it still happens, then send e-mail with the spreadsheet name, sheet tab name, and exact cell number, so it can be changed.
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 advisers saving and organizing client 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).
How to send financial plans to clients via e-mail, without sending the whole program.
Date conventions: There are several approaches to calculating how many weeks are in a month, and all that. To avoid confusion and to get the most accuracy, the number of weeks in a month, days in a year, etc. are done using exact numbers. So you may get some confusing results if you try to audit them using inaccurate methods.
For example (rounded to the second decimal place): There are 366.26 days in a year on average to account for leap year. Dividing this by 12 gets you an average of 30.44 days in a month, and an average of 4.35 weeks in every month. So if you input an expense of $100 per week, the monthly amount will be $436. So if you try to add things up like this and are off a little bit, don't fret (or send support e-mail), as this is the way things are set up to be, and it's the most accurate methodology.
The variable expenses part of the program works in weekly mode and not monthly mode. In other words, most data is presented weekly and not monthly.
There was debate about this, and the deciding factor was the fact that working people in the Real World get paid and do their shopping rituals on a weekly basis, and so family budget programs works best when they match the patterns of how people actually spend money in the Real World.
The Results sheet is protected to prevent a user input mistake from inadvertently destroying formulas, but there is no password, so you can unprotect it to make any changes to the results you want to. Its' recommended to reprotect it after making changes to protect the formulas.
Family Budget Tool Instructions
Cash Flow Projector instructions are below. But begin here, even if you have the Cash Flow Projector.
• After downloading the free budget software, open the spreadsheet personal_budget_software_demo.xlsx with MS Excel.
- or -
After getting it out of your e-mail and onto your hard drive, open Cash Flow Projector.xlsx using Excel.
The budget software will open to the Fixed Monthly Expenses section of the Budget (All Expenses) sheet. The Cash Flow Projector will open to the Income Forecaster 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 already 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 the View ribbon, then Freeze Panes, to turn it off and on (after unprotecting the sheet. If the sheet has a password, then you won't be able to fiddle with it. If you bought support, you can get a version that you can do this with).
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 comparisons with the disabled or long-term care scenarios: 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 exact same results in the disabled section.
If not, then track it down and fix it. When they match, then you'll know you did it right.
Then you can alter input in the disabled areas to match the disabled / nursing home scenarios (by stopping incomes and increasing medical expenses). Doing it as you go works better than doing it over when you're done.
• If you don't have the Cash Flow Projector, input your monthly incomes into the Results sheet, starting in cell AC5 for normal times, and AF5 for disabled times. Inputting incomes is different for the Cash Flow Projector, as you'll input incomes into the Income Forecaster sheet.
• Input your fixed monthly 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 here. Input them only into the green-shaded areas of the fixed expenses and debt payments areas. After inputting, the cell will turn gray.
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.
• 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 on the Budget (All Expenses) sheet.
You can change the names of the miscellaneous expenses from B21 to B40, but not any of the other ones (you can but it will mess things up in other places).
Note that using the Miscellaneous expense input areas to change expense names for variable expenses is done from column E on the Variable Expenses sheet, and not on the Budget (All Expenses) sheet.
• Input your monthly debt payments into the green-shaded areas starting at the top of column I.
• Go to the 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 don't want to enter detailed weekly expenses (into columns F - BF), then do not input anything into columns G to BF of the Variable Expenses sheet. Input 0 (zero) into cell E3 and then just input your estimated average weekly expense data into column F.
• Start at the top of the sheet in the green-shaded areas (cell F5) and start entering what you think are your averages per week. Continue down column F until you're all done.
• 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 input another week's column, 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 just the divisor used to calculate the weekly averages. This is not an automatic function because it gives the user control over calculating their 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 E3 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 in cell E3.
You can use the white cells (like cell F4) to write notes in. This text doesn't affect any calculations, so don't be afraid to jot down notes. Just don't input just numbers into white cells as they may be accounted for somewhere.
• 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 of the Variable Expenses sheet.
The monthly numbers you'd 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. The program will cease to function properly when it's complaining about a circular reference.
• 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. Failure to account for replacements long term will make your retirement nest eggs run out a few years earlier than most all retirement software will estimate.
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.
Then starting in row 28, all of these monthly total amounts are multiplied by 12 to get annual figures, then they are all summed up in row 50.
The total 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 things here are the interest rates. For example, in the Cash Flow demo, one may question why the current cost of carpet is only $464, but the present value of this cost is $2,000. This is due to the difference in interest rates you input into columns D and G (and was just an example to show this).
In this example, the cost of carpet goes up 10% a year, but you're only getting 4% in a bank savings account to save for this future expense.
So if you put $464 into a savings account today to have money to replace carpet in 100 months, 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 drops 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 $25,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.
Please note that just the first year's number is used in the budgeting program. The future year's number on the Replacements sheet are just FYI.
Also, you'll need to input at least $1 into cells B3 - G24. Most cells here do not accept $0 due to divide by zero and other errors.
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 effect 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 Budget (All 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 calculate an average weekly / monthly amount that you'll need to save from now until then (based on cell E3).
Computing Gas Mileage
This is all done on the Variable Expenses sheet.
First think of how much gas your Vehicle #1 holds and input that into cell H201. Then input what the total current cost of that would be into cell H203. You can use an Excel formula to guess like this (input this into cell H203 if gas is going for $3.50 per gallon): =H201*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 H202: =H201*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).
Now when you see your recent gas mileage decrease relative to the average, then you'll know it's time for a tune up.
Updating the Budget Calculator and Expense Sheets to New Years
Using past averages is important to seeing how life changes. You can use column F of the Variable Expenses sheet as it's intended, which is to account for prior years' category averages.
After you update, then here's how to continue using past year's data into the new version. You won't be able to do this with the free version as the workbook is locked, so you'll need to buy it.
Open the free add-on spreadsheet titled "Cash Flow Projector Updater," and follow those directions.
After the new Cash Flow Projector has all of the old version's data, and is saved, then all you'll need to do is update the Prior Year's Weekly Averages in column F of the Variable Expenses sheet.
Go to the new workbook's Variable Expenses sheet.
Go to cell E5. Copy. Go to F5 and paste. If you just regular pasted, then the colors will paste too, so it's best to Paste Special, Values only.
Repeat for all of the peach-colored (or pink depending on your Excel version)non-zero values in column E. These, like E5, just total up all of the items in its section.
Then delete everything from G5 to BF291. This will delete all of the prior year's weekly data, so you can start over.
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.
• Open the spreadsheet Cash Flow Projector.xlsx.
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 Tool Instructions section.
Most everything is projected automatically except global inputted 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 near future. Input the appropriate items into the green-shaded cells in column A. You can't input the income names on most of them, so hunt for appropriate sources.
For example, if someone is getting $1,000 a month in alimony from someone else, input $1,000 into cell A11. If you didn't change the global inflation rate in cell C2, 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 a year as shown in the demo's cell G12). So you'd input $3,377 into cell H13 (or =G12*.25).
Now input 0 into cell H14 to stop inflation for this year only.
If for whatever reason, John started getting $500 a 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 into cell L14.
You can use any method you want to control these figures down to the day.
The year numbers are shown in row 4, above the years shown in row 7, 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 dollar 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 white cross that turns into a black cross showing you can drag 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. 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 was input into cell M21 to have this Bonus income in December of year 10, and have no Bonus 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.
About First Year Plan Accuracy
Since the chances of you making a plan on January first are slim, getting the first year’s ending numbers to match reality takes a few extra steps. This is because RP does not work in monthly-mode (everything is annual).
So when inputting (everything - asset and liability values and cash flows), you can use the manual overrides on the projection sheets to dial each item down to where the end-of-year value is what you expect it to be.
For example, if you make a plan on 30 June and an asset is worth $10,000, and you input an annual rate of return of 10%, then the end-of-year balance is going to be $11,000. It should only be $10,500, because you're only getting half of the 10% annual total return.
So in this case, you'd change the annual rate of return from 10% to 5% using the rate of return manual overrides. For odd months, you can use standard Excel formulas like this: =0.1/12*6 So if your plan creating date is tax day in mid-April, then you'd input this: =0.1/12*8.5
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 176-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 are four income input areas each, that you can change the name of, to account for things like more than one pension. These are: Other Earned Income, Other Investment Income, Tips / Stipends, and Unemployment. Just type over those words and input whatever you want in these four areas.
There is another summing section showing everyone's individual totals, plus combined total, from rows 355 - 377.
At the bottom of every subheading there is a row that shows the percentage difference increase / decrease from the previous year (row 377).
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 dollar in every year with this family budget software, and basically can't control much of 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'll need to pay attention as you spend money, and input 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 a 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 C104 - C107 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. This sheet is similar to the income sheet in the way things work.
It starts with variable expenses, then it covers fixed, and then debt payments.
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 also called the "CPI" or Consumer Price Index.
• Cell D13 on the demo 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 things like cigarettes.
Input 5 into cell D13, 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'd need to do.
Then 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 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. 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, 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 Cash Flow 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: 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 (press F5 and type in A1033). 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.
If you chose the automatic taxing method, 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.
The biggest problem with calculating taxes far into the future is that taxes change way too much. With programs that calculate detailed 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 way allows you to control taxes in every year using both methods. With code-driven financial plan software, you're stuck forecasting current rates for fifty years, when you know they'll be going way up in the next year. About using tax software in financial planning is here.
How to Use the Disability Cash Flow Projection Sheets
In addition to creating a personal budget for normal times and retirement, you can also use the duplicated sheets to the right of the Results sheet to forecast what one's budget and cash flow would look like if one breadwinner, or all, were to become disabled (or whatever the term you want to use that describes not being able to work and earn an income anymore).
The sheets to the right of the Results sheets were just copied and renamed Disabled, so you can do all of the same detailed income and expense forecasting described above.
It helps to copy and paste all input you've made on the not-disabled input sheets into the disabled input sheets as you're inputting.
The short version of how to do this is: Copy normal times data in the disability section. Then in the disability section, delete all of the breadwinner's incomes that are a result of working, while keeping the ones that continue even if they couldn't work (e.g., pensions and investment portfolio income).
Then change expenses to reflect your estimates of what they would change to when disabled. This means increasing medical bills and home entertainment like upgrading cable TV, and also decrease things like travel and vehicle / commuting to work expenses. Then you'd change the inflation rates to reflect the changed expenses. For example, medical bills may increase at 20% for a few years, then decline, or stop.
When all of the numbers are summed up, the bottom-line answer of how much monthly disability insurance is currently needed to make your budget while disabled is given in cells N4 - N7 of the Disabled Results sheet.
N4 and N5 display how much is needed if both incomes were to be displaced via disability.
N6 and N7 displays monthly need numbers if just one were disabled. All four numbers are assuming all debt is still in place. Cells C16 - C25 display the same information, but assuming all debt was to magically go away.
If you want to determine how much disability insurance would be needed to maintain your current standard of living, just copy all of your input from the right parts of the workbook, to the left side. Then just delete the earned incomes that will stop, and compare the numbers in column C of the Results sheets. That's an easier way to do it, but it's more accurate to go through and estimate each income and expense to reflect what it will be if disabled.
Here's an example showing how to adjust expenses for disability. Since Medical Expenses will go up as a result of disability, this number was increased by raising the variable expenses in row 83 - 85 of the Disabled Variable Expenses sheet (and rows 123 - 134 of the Disabled Cash Flow Projections sheet). When you start inputting the amounts you spend on this expense when disabled, your true figures will appear.
Go through all of the income expense items and repeat this process until all of your income and expenses reflect what you think would happen if one or more breadwinners had to stop working because of a disability.
How to Change the Range of Years Shown in the Charts on the Results Sheets:
Most people won't be around 75 years from now, so showing the numbers in the charts for this long isn't good because it crowds out and steals attention span from the useful visual information of the chart.
These charts are set up to only show data for 40 years (the data range stops at column AZ, whereas the range of data goes to column BZ).
Here's how to change it to fit your needs: The first chart like this is the Expenses chart placed around cell E39. First, go to Review, and click Unprotect Sheet (no password is required).
Now click on the bottom light yellow area in the Expense chart (this is Total Annual Variable Expenses).
A long formula will appear in the formula bar. A little bit past the middle to the right, and at the end, the formula's ending data range is column AZ. AZ1489 is the range for the year numbers, and AZ1489 is the range of the Total Annual Variable Expenses. Both need to be changed.
Use the column heading on the Cash Flow Projections sheets to determine which year corresponds to which column. If you want to change this range to show the whole 75 years, change both AZs in the formula to be BZs. If you want to display only 26 years instead of 40, change both of the AZs to be ABs. The chart will look funny until you've changed all of the date range areas in the chart in the same manner.
To do this, press the up (or down) arrow key, and the next data range area's formula will appear in the formula bar, ready to be changed as well. When the chart looks right, you've done them all.
Excel 2007 made this easier than in previous versions, because now you can right click inside the chart, choose Select Data, and then manipulate the whole range using the top field - Chart Data Range.
If that doesn't work, then it's still easier to click on the Legend Entries (Series) one-by-one and then click Edit.
After you've determined and input your projected incomes, taxes, and expenses at retirement into the Cash Flow Projector, you can easily transfer this data into RP or RWR's Income Goal Manual Override column. This will get you better accuracy, which means you're taking much less risk if you rely on figures from financial planning software.
Ensure you've accounted for replacements before linking them!
There are four sets of data to work with here:
1) Column G: Grand total monthly expenses without taxes.
Use this if you did not input post-retirement incomes into the Cash Flow Projector (and/or you input them into RWR or RP), AND you want to let RWR estimate taxes due. With RP, this keeps the methodology of doing everything gross of taxes going.
2) Column H: Grand total monthly expenses with taxes.
Use this if you did not input post-retirement incomes into the Cash Flow Projector (and/or you input them into RWR or RP), AND you do not want to let RWR estimate taxes (set the global tax rate to 0% on all non-asset incomes if you do). With RP, this stops the methodology of doing everything gross of taxes, and allows you to account for taxes due, but only on non-asset incomes.
3) Column J: Grand total monthly expenses without taxes, minus incomes.
Use this if you input post-retirement incomes into the Cash Flow Projector, AND you want to let RWR estimate taxes just on asset incomes (non-asset incomes will not be taxed). So you'll probably want to input non-asset incomes into the Cash Flow Projectors after-taxes. With RP, this keep the methodology of doing everything gross of taxes going.
4) Column K: Grand total monthly expenses with taxes, minus incomes.
Use this if you input post-retirement incomes into the Cash Flow Projector, AND you do not want to let RWR estimate taxes (on non-asset incomes. You'll still need to input a global tax rate and then set a tax rate on each asset). With RP, this stops the methodology of doing everything gross of taxes, and allows you to account for taxes, but only on non-asset incomes.
With all four methods, ensure that you did not input the same incomes (and/or taxes) into both the Cash Flow Projector and the retirement software. This double-counting will greatly distort your scenarios.
On the Cash Flow Projections sheet (or the Disabled Cash Flow Projections sheet), go to cell G1579 (or row 1579 of the column of your choice, using the above as a guide).
This is where the row of Grand Total Monthly Expenses Less Taxes was converted into a column of the same data (starting in G1579).
This was done because the Cash Flow Projector generates data in rows, and RWR wants to see it in a column.
Be sure to not count college, and other expenses, twice. If you input these items as miscellaneous expenses in RWR, ensure you don't account for them in the Cash Flow Projector too. The best place to do all of that is in the Cash Flow Projector.
Go to the row that corresponds to the first year that the first person will retire. In the demo, this is year #11. This is cell G1589. Click on it.
Now open RWR and go to the Summing & Input sheet. Go to column BV (or CC), the Monthly Income Goal Override columns. The steps are similar for the RP retirement calculator (but different columns).
Delete all of the youngest person's data in column CC by going to cell CC20, and inputting 0. Then click on the little box at the bottom right until it turns into a black cross that highlights the active cell, then drag it all the way down until you get to cell CC89.
This will use zero amounts in the youngest person's income goal to prevent double-counting them. If you accounted for both people's expenses in the Cash Flow Projector, and referenced them here, then you'd be adding figures in column CC to that, so they need to be deleted by inputting 0.
Find the RWR cell in column BW that has the first year of retirement - year 11. This is cell BW30 in the demo.
Input an equal sign into cell BW30, then go to View, click on Switch Window, click on the Cash Flow Projector, then click on cell G1589 and press enter. This will reference the Grand Total Expenses of the Cash Flow Projector for that year into RWR's Income Goal Manual Override.
The point to remember is that the cells mentioned above are just examples. You want to start the whole linking process in the year retirement starts for the first person to retire.
In RWR or RP, nothing is accounted for before retirement other than growth of and additions to investments, so if you link years before retirement, nothing will happen in RWR or RP. It's not going to hurt anything if you do, but everything input before the first person retires will just be ignored.
Because of all of the different ways you can fiddle with this to get exactly what you want, it's best done via this manual process.
So if you bought support, then feel free to e-mail both the CFP and RWR or RP back to us (with your data input), and we'll take a look to see if you did everything correctly.
Important! Before you drag the formula down, you'll need to delete the dollar sign before the number in the formula. The dollar sign locks the number up, so if you drag it down with it in there, then you'll have made all of the numbers below be equal to the first year only. By deleting the dollar sign in the formula, the cell references will change as you drag them down.
You don't want this before you drag:
='[cash_flow_demo.xlsx]Cash Flow Projections'!$G$1545
You want it to be this before you drag:
='[cash_flow_demo.xlsx]Cash Flow Projections'!$G1545
Now click on the little black cross at the bottom right that highlights the active cell, then drag it all the way down until you get to cell BW89. Now you've copied all of the detailed Cash Flow Projections in RWR (or RP), and they will now be used as the income goals to solve for.
If you get any kind of an error message, it's because you tried to copy and paste the formatted boxes where the data is (in protected cells).
Now whenever you make changes in the Cash Flow Projector's data, your retirement plan will reflect these changes automatically (as long as you don't delete, rename, or move the Cash Flow Projector to a different folder).
The cost of living inflation rates you've input into RWR will be ignored in years where there is an income goal manual override.
How to Do "What If" Scenarios
Using Excel's built-in Goal Seek functions to do "What If" scenarios. You can easily do any "What If" you want to with just six mouse clicks.
For example, if you wanted to see how much you'd need to lower the amount of Federal Taxes you pay to turn a $100 monthly budget deficit into a $60 monthly surplus, all you do is this:
Click on cell C84 of the results sheet. Pretend, for sake of example, that this is currently displaying a $100 monthly budget deficit.
Now go to the top Excel menu, click Data, What-If Analysis, Goal Seek.
The Goal Seek dialog box comes up with the Set cell: value as C84 (because that's where you were when you started Goal Seek, you can now change it to C84 of the Results sheet, or any other cell, if need be).
Click on the middle input field, To value:. Goal Seek is asking you, "What do you want cell C84 to end up being after I figure this out?"
For this example, you want to change the $100 monthly deficit to a $60 monthly surplus, so input 60.
Then click on the bottom input field, then switch to the Budget (All Expenses) sheet, then click on cell C49 (Federal Taxes). Click OK, and Goal Seek will change the amount of Federal Taxes until the $100 deficit is turned into a $60 monthly surplus.
You can use any results field and any income or expense field you want to.
You can do any crazy "What If" you want to and it won't hurt anything, other than changing the cell value you input into Goal Seek's bottom input field.
For example, instead of clicking on cell C49 of the Budget (All Expenses) sheet, you could have clicked on any cell in row 14 of the Income Forecaster sheet, and John's annual rate of increase in his annual bonus would change to a crazy figure like, 600% a year, to make it so. This is not realistic, but you can do this, and any other crazy "What If" you can think of.
About the Automatic Updater:
This is a separate spreadsheet that makes updating to a newer version easy. It's just a macro that copies all of your inputs from the old expired program into the new version.
The directions on how to use it are on the updater spreadsheet. So you'll only get this updater spreadsheet after purchase.
|Financial Planning Software Modules For Sale
(are listed below)
Financial Planning Software that's Fully-Integrated
Goals-Only "Financial Planning Software"
Retirement Planning Software Menu: Something for Everyone
Our Unique Financial Services
Mr. Market Timer's Unique Market-neutral Stock Market Timing Services
Miscellaneous Pages of Interest
© Copyright 1997 - 2017 Tools For Money, All Rights Reserved