Net Worth Calculator Instructions.

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

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

If you try to print the demo directly from your browser, or you just click Print in Excel, it probably won't work well. 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.

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).

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. 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).

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.

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

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

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

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 how to present sample reports to prospects and clients.

Tips for saving and organizing files are here.

Read about financial planning software integration (sharing data between modules)

Read about financial planning software support

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

Basic input concept for most all of these Excel spreadsheets: Input your data into the green-shaded cells of the input sheets (usually grouped in the middle of the workbook). This will make data flow to the calculations sheets, which are on the far right group of 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 (usually grouped in the left of the workbook), where you look at the results. After inputting, you fix mistakes, repeat, format, and 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. This prevents users from damaging the spreadsheet. Don't input, or type over, any formulas on the presentation sheets because then they will no longer change or function when you change your input data. Input cells usually turn gray when a non-zero value is entered into them.

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

Feel free to use Excel's freeze panes function to make some sheets easier to look at. Go to View, Freeze Panes.

Net Worth Calculator Instructions

• The program will open up to the Generic Input sheet. This is where you input most of the global default input data. Most is self-explanatory except for the client's names.

If you input "John" as the client's first name, "Mary" as the spouse's first name, and "Sample" as their last name, the program will display "John and Mary Sample" on the presentation pages.

To override, this, and make it put exactly what you want on the presentation pages, just input exactly what you want to display into cell C12.

• Input the current date into cell C3. This doesn't affect any calculations, it just shows on presentation pages.

• If you're going to want to see before- and after-capital gains tax values, then input the tax rates, as explained by the cell headings into cells C14 & C15. Try to use what is Real World for the client's situation.

• If you want an asset allocation mix snapshot, choose the names of the asset classes you want to use via the drop-down menus in cells C18 to C28.

Due to programming, you can't change the asset class codes (in cells D18 - D28), but you can use whatever names you want to go with each code for maximum flexibility (cells C18 - C28). Try to match the code letters to something that resembles the actual asset class, as shown in the demo.

• First organize all of the client's paper data into accounts (sort the piles of paper statements first by who owns it, then by the firm that holds the assets, then by account).

Start with the client's data (professionals will need to decide who is client and who is spouse in every situation).

• Switch to the Input Client's Asset sheet, either by clicking on the sheet tab at the bottom left, or pressing Control Page Down.

Enter the first account's name. For example, if the client gave you their statement of their Charles Schwab account, input "Charles Schwab" into cell C3. You can use whatever name you want to, but the point is to use a name everyone will recognize (keep in mind that part of the reason clients' hire advisors is to make some sense out of piles of their financial statements).

If the account is tax-qualified (e.g., IRA), enter a "y" into cell C4. All this does is make data appear, and not, in the Non-Qualified and Qualified Asset Presentation sheets. Any character input other than "y" or "Y" will tag the account to be non-qualified.

Start entering asset names (the actual stocks, funds, etc.) and their current values into columns B and C. Try not to skip rows, just to make it easier to manage. It helps to do this in alphabetical order.

If you want to compare the percent changes from prior time periods, first enter the name of the time period in row 4 (D4), and then the values in the corresponding columns (starting in cell D6). You can enter up to 12 date points of comparison. If you start, then try to do all assets for that account. Nothing will appear on the Asset Presentation sheets if you leave the fields blank.

Enter what you think the asset's class is - using the asset class names and code letters in the Generic Input sheet. This is only used for the Asset Class Presentation sheet.

Input the tax basis (what they originally paid for the asset) into the column Q. This is only used for the Tax Presentation sheet. Nothing will appear on the Tax Presentation if you leave the fields blank. You can leave it blank if you don't want to show this information.

If there is a liability owed on each asset (e.g., mortgage, margin, etc.), enter the current amount in cell R4. This is critical in determining net worth.

To input credit card, and other unsecured debt that does not have an asset associated with it, add it to the personal assets account. Personal investments are what people call "their stuff," and should be estimated and included as an asset. This is the most appropriate place to add unsecured debts, like credit cards.

Or, to show a debt with no associated asset with value, just make an asset in an account (it's best to have just one account with just one asset when you do this). Then input the name of the debt, for example, Student Loans. Then input a tiny non-zero number (0.0001) into its current value input field (zero values will be ignored).

Then input its current value into the liability input field. Now you can manipulate its future value just like any other debt, have it show up in the reports, and not have an asset value associated with it.

Scroll to the right to enter the other accounts owned by the client. Repeat until all of the client's assets are accounted for.

• Switch to the Spouse's Asset sheet. It doesn't matter if the husband or wife is the client - in fact you'll score some points at the data discovery interview by asking them what their preference is. Repeat the process above.

But if you're also using our retirement software, then keep in mind the client is usually tagged as being the oldest, and the spouse the youngest.

• Switch to the Jointly Held and Community Assets sheet. Repeat the above process for all investments legally held by both spouses.

• If you want to account for children's assets, switch to the Children's Assets sheet. Repeat.

The rest is sort of automatic - the program will populate all of the fields in the presentation sheets. The only thing left to do is check your work, and format it to print right on your printer.

Here's how to do that:

On all of the presentation pages, you'll see lots of rows with no asset data (nobody owns 500 assets). You want to hide all of these rows. Here's how to do that:

Put your mouse cursor on the actual number of the row (far left) until it changes to a big white cross.

Right click, and then select Hide.

You can select multiple sequential rows easily by dragging the mouse, and you can select non-sequential rows by holding the Control key down while you select rows. You can also put your mouse cursor on the lines between the rows, and when the cursor changes to a small black cross, drag it to make it bigger or smaller. You can also select row Height, and set it to 0, but that's more steps.

• After you've hidden all of the rows (many as you're finding out), resize everything to print right. Do the same as you just did when hiding rows, but select row Height and column Width to make everything the right size.

A tip is to use the View, Zoom numbers to make the whole thing appear the right size on your monitor.

Also, use Print Preview first to save paper. How to get things to print right in Excel.

Net Worth Projector Software Instructions

The Net Worth Projector just projects the data already input into the Net Worth Calculator into the future.

After you have populated the Net Worth Calculator with data, by using the steps above, the Net Worth Projector will automatically grow / shrink all 500 of the asset's values 75 years into the future - except for one catch. You need to at least assign a global default rate of return for each account used.

• The first of 20 global default rates of return input fields are on the Client's Net Worth Projector sheet in cell H6.

Entering an annual rate of return here will automatically make all of the assets used in the first account grow by that much annually. If you leave it blank, then all of the assets' values will be the same for 75 years.

The next Client account global default input cell is H140.

• The next thing to do is to deal with controlling the rates of return on all of the assets. You can do this on a year-by-year basis by inputting a rate of return for each year in the bottom green cell in that asset's row on the Net Worth Projector sheets.

For example, using the first asset in the first client's account, and after you have assigned a global default rate, input a different rate of return into cell B10.

The current end-of-year asset value has now grown by that much instead of the global default rate. This way you can control the asset's first year growth rate to take into account the fact that you're not running the report on 1 January. In other words, if you want to show the asset growing at 10% annually, but the report was generated on 1 July, then only half of the annual return should be used (in this case only 5% because only there is only half of the year left).

You can do the same thing at any year with any asset. This way you can show the estimated decline in assets as you draw upon them during retirement.

You can also use the market value manual overrides to make the end-of-year value be whatever you want. See row 9 in the demo. All of those values were manually inputted.

• Once you have all of the numbers the way you want them, go to the Projected Net Worth Tables sheet. Hide all of the rows that don't have assets in them. Just right click on the row number and select Hide.

• Next, you may want to tinker with the graphs to show the number of years you want. This is both complicated and easy. The program comes with charts populated with data up until 2067.

Say for example, you want to show data until 2050 instead. Click once on the inside of the graph. Then use the up or down arrow key until a long convoluted formula appears in the formula bar.

There are three data points in the formula. The first (far left) just tells which data point name it is, so don't mess with that. The middle formula tells the year range, and the last one is the data range.

Click on the formula bar to highlight it and then put your cursor between the $ and the 3 where it says $BF$3. Change the BF to AO.

Repeat this at the very end of the formula. This will now show years up until 2050 (column AV) instead of 2040 (column AO).

Then you'll need to change all of the rest of the values in the chart to be the same too. Excel 2007 made this easier than previous versions, because you may be able to right click on the chart, choose Select Data, and then edit the whole series there only once.

Nothing is protected on this sheet, so you can change anything you want to, make new tables, graphs, etc.

If you make a mistake, just press escape once, then control-Z (undo).

Accounting for Annual Changes in Liabilities

Switch to the Liability Input sheet.

Each of the five accounts for each of the four major categories (20 total) has an associated liability (the four main categories are, Client, Spouse, Joint, and Children).

Scroll down to input Spouse through Children. The fourth client account, Student Loans, has a liability (as shown in the demo).

The current loan value shown in cell T4 came from the input sheet.

Input the number of months left to pay on the loan into cell T5.

Input the APR, or annual loan interest rate into cell T6.

Look at the automatically generated end-of-year principal figures. They won't be right, because no software can do this right. So your choice is to either use this or tinker with the number of payments and/or the interest rate numbers until it's close enough, or break out the loan's actual amortization schedule, and manually input all of the end-of-year numbers showing how much is still owed at the end of each year into column T (starting in row 8).

Repeat for all liabilities for all 20 accounts.

Integrating Net Worth Data with the Retirement Planners (RWR or RP)

To get the report to integrate (share data) the same way as very expensive financial planning software, first run the RWR retirement software report. This way you'll have all of the account's year ending values after accounting for the future retirement cash flows.

There are three ways to do this (#3 is preferred):

1) Account for rates of growth manually each year.

Go to the RWR's asset page and calculate the rate of increase / decrease in the overall account. Assume the difference in value of the asset from one year to the next, after you start to withdraw money from it, is -5%.

Now for all of the investments in that one account of the Net Worth Projector, input -5% into all of the rate of return manual overrides. Do the same for all of the other years.

2) You can manually link each asset's end-of-year values directly by linking them via the manual overrides, to the end-of-year values on the asset sheets of the retirement planner.

In other words, click on the asset's manual override cell in the year you want to link data in the Net Worth Projector. Input the equal sign. Switch to RWR (click on the View ribbon, the Switch Window, then choose RWR) and click on the cell that has the asset's value that you want to use. You could have also just pressed Control Tab.

Press enter. The Net Worth Projector now will get that asset's end-of-year value directly from RWR (so when the values change in RWR, they will automatically change in the Net Worth Projector).

Ensure you're linking the same year on both programs.

3) Use the Retirement Plan Integration sheet of the Net Worth Projector.

This is for changing the account's end-of-year market value to be the same as it is in the retirement module.

For example, if the RWR report shows that an IRA's account balance has went up by 50% because of both market growth and annual contributions, or down 10% because of withdrawals, and so this is what you want to use in the Net Worth Projector, then you can do that so it will be automatically updated when you change values in RWR.

To do this, first have your RWR open and the asset sheet up on your screen.

Next determine the beginning year that you want to integrate. Let's say it's the current year. We'll also assume that you're using the RWR Oldest Asset #1 sheet.

Go to the Retirement Plan Integration sheet on the Net Worth Projector (far right sheet tab).

Put your cursor on cell D6, year #1 of Asset #1. Enter an equal sign. Now click View ribbon, the Switch Window, then choose RWR.

Click on cell BH29 on Oldest Asset #1 and press enter. This is the first year's ending market value for the asset after all contributions, growth, and withdrawals are accounted for.

Now you're back at the Net Worth Projector.

Use the cell's drag handle at the bottom right to drag the formulas down to account for all of the subsequent years. You can start dragging when the cursor changes from a white cross to a black cross when selecting the little box at the bottom right of the cell you're starting in.

Before you do this step, however, you'll need to delete the dollar sign that precedes the cell number!

If you don't then all of the cells below will reference the year #1 cell. For example, if the cell number is BH$29, change it to just BH29. The dollar sign creates an "absolute reference" which means when you drag it down, it will stay at BH29.

When you delete the dollar sign, it becomes a "relative reference," meaning it will change when you drag it down (so the cell below will reference cell BH30).

Now it's important to do another step.

For every column you integrated data into, you'll need to ensure all of the cells below the last one linked has a tiny non-zero number input into it.

For example, if you only linked the first 20 years, and didn't do anything to years 21+, then in year 21, the projector will be using the automatically generated numbers on the projection sheet.

You don't want that, so input a tiny non-zero number, like 0.001, into the last cell, then drag it all the way to the bottom.

Now you are using RWR's values in the Net Worth Projector, so they will change when you change something on RWR.

These numbers show up starting in cell B132 of the NWP's Client's Net Worth Projector sheet. All it did was override the sum of the asset values in column B above. These values are then displayed on the presentation sheets.

Reverse mortgages: What you're accounting for is the reduction in equity, or how much of the home you still own as time goes by, and you get an income from the lender.

No net worth software can do this correctly, so what you do is use the amortization schedule the reverse mortgage people give you, and use the end-of-year market value manual overrides they give you to account for it. This is the only way to do it right.

That's about it - just input data, input default rates of return, deal with all of the manual overrides and integration, look around to check your input, hide all of the rows without assets, save, and then format to fit your printer.

Personal Finance Software Modules For Sale
(are listed below)

Fully-Integrated Financial Planning Software

Menu of Retirement Planning Software

Asset Allocation Overview with Historical Portfolio Performance

Comprehensive Asset Allocation Calculator

Asset Allocation Models with Investment Track Record

Monthly Updated Mutual Fund Picks

TVM Financial Tools and Free Financial Calculators

Free Personal Budget Software

Financial Planning Fact Finders

Investment Policy Statement Software

Bond Calculators