Net Worth Software and Balance Sheet Program 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 the directions.

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

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

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

You switch between the sheets by either clicking on the sheet tab names at the bottom left of the Excel window, or by pressing *Page Up or Down (pressing the control key and either the Page Up or Page Down key on your keyboard at the same time).

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

If you don't like a file being read-only, go to Save As, and then go to Tools, General Options, uncheck the Read Only box, then click Save. More tips for saving and organizing files are here.

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

You should make CD backups of all of your work on a monthly basis, and store the CD in a safe location, but not where your computer is. More PC tips are here.

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

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

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.

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

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

Read about financial planning software support here.

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

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 done on the far right group of sheets. This will 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.

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 it easier to look at.

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 effect any calculations, it just shows on presentation pages.

· Input the tax rates, as explained by the cell headings, into cells C14 & C15. The ordinary income rate applies if one sells an asset held less than one year. 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 crazy 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.

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 assets are what people call "their stuff," and should be estimated and included as an asset. This is the most appropriate place to add unsecured debt like credit cards.

To do this, 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.001) 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 next 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.

· Switch to the Jointly Held and Community Assets sheet. Repeat the above process for all assets 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 you 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), you want to 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 is explained here.

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 assign a global default rate of return for each of the accounts that you 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 thing to do is to deal with controlling the rates of return on all of the assets. You 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.

· 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 2040.

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 $AL$3. Change the AL to AV.

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

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

Input the current loan value into cell T4.

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 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 rate 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 assets 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 main Window menu, and choose RWR) and click on the cell that has the asset's value that you want to use.

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 on the main Window menu, and choose RWR.

Click on cell BE29 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.

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

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

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

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 have using RWR's values in the Net Worth Projector.

These numbers show up starting in cell B131 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.

Product Information

Menu of Retirement Planning Software

Asset Allocation Overview with Our Historical Returns

Asset Allocation Software

Asset Allocation Models with Our Historical Returns

Our Monthly Updated Mutual Fund Picks

Financial Plan Tools

Family Budgeting Calculator

Financial Planning Fact Finders

Investment Policy Statement Software

Bond Calculators

Investment Software for Comparing 22 Methods of Investing