Directions for using the asset allocation calculator to manage investment portfolios.

Asset Allocation Software Instructions

Go to the Main Asset Allocation Software Page Download the Free Asset Allocator Calculator Demo Using a Discount Broker to Manage Your Money Text to Help Understand the Presentation Reports Explanations of Investment Terms and Concepts.
Site Information
(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

Financial Software Price List

How to Buy Investment Software

Discounts for Financial Advisers
(and how to subscribe to the full Bundled Deal for $100 a month)

Lifetime and 5- to 15-year Extended Subscriptions

New Financial Planner Starter Kit

Professional Investment Portfolio Building Kit

Financial Planning Software Support

Financial Planner Software Updates

Site Map

Site Information, Ordering Security, Privacy, FAQs

Questions about Personal Finance Software? Call (503) 309-1369 or Send E-mail to support@toolsformoney.com

Free Downloads and Money Tools
(are listed below)

Free How-to Money eBook for DIY Investors, Critical Reading for Financial Advisers, and for Exposing Financial Myths

Free Sample Comprehensive Financial Plans

Free Money Software Downloads, Tutorials, Primers, Freebies, Investing Tips, and Other Resources

List of Free Financial Planning Software Demos

Selected Links to Other Relevant Money Websites

 

Free Retirement Calculators

Free 401(k) Calculators

Free Financial Calculators

Free Family Money Calculators

Free Real Estate Calculators

Free Debt Calculators

Free Investment Calculators

Free Tax Calculators

Free College Saving Calculators

Free Insurance Calculators

Free Business Owner Calculators

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 free demo, while following along as you read these directions. It also helps to download and read the text that explains the reports.

When you're using the asset allocation 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.

The asset allocator "demo" is the actual spreadsheet, non-functional, without the formulas, so it's much smaller file size. You won't be able to do anything but look at it. It also has four sheets missing to preserve content. 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 a 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).

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.

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 from these original files.

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

How to turn a zipped file attached in your e-mail (if any) 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. Here's many more free PC tips.

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.

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

About financial planning software support.

About getting custodian brokerage account data to download into spreadsheets.

About financial plan software updates.

About financial planning software integration (sharing data between modules)

Read how to present sample reports to prospects and clients (and it's the place to start if you're a financial adviser wanting to learn what's going on at the highest level).

How to send financial plans to clients via e-mail, without sending the whole program.

These are not the directions for the Model Portfolios, they are here.

How to get around Broker Dealer compliance if they don't approve our money management systems.

About investment asset allocation calculators.

Summary of the Asset Allocation Process

Current means what the investor currently holds, or "old." Proposed means recommended, suggested, or "new."

Here's a summary of the process:

• The beginning of the process is always financial discovery, in this case, using the Investment Fact Finder. This gets most all of the information you'll need to manage someone's money.

Answers to those very important questions are used as inputs into the asset allocation calculators. Having the Investment Fact Finder in front of you at all times when you're making asset allocation reports will help greatly in minimizing mistakes.

For investors doing this for their own money, you'll only need to answer and score the multiple-choice questions of the Investment Fact Finder to determine your investment risk tolerance category. You don't need to fill out anything else (nor do you need to use the Asset Fact Finder, Part 3).

Then you'll need to gather their current investment holdings to input. The two ways to do this are to either use piles of statements or use an Asset Fact Finder.

• Then you'll input data into the GAAC (Guideline Asset Allocation Calculator). This is done via the drop-down menus on the Input sheet. The results of this are in one of the three sheets to the right labeled Childless Mix Calculator or Family Mix Calculator, or Retired Mix Calc. This creates the recommended asset allocation mix between the eight major asset classes.

• Then you'll input what they currently own, and distribute the contents of (mixed) investments (like mutual funds) among the eight major asset classes.

• Then you'll use the pie-chart section, and/or the Sources and Applications of Funds area at the bottom of the Asset Allocator sheet, to compare their Current asset allocation mix, with the recommended allocation mix.

• Then after copying and pasting the Current section into the Proposed section, you'll shuffle investments around in the Proposed section to move their portfolio as close as you want to the recommended asset allocation mix. This is now the Proposed portfolio - or what you're going to recommend. For investors, this is what you would implement for yourself via your online discount broker.

• Then you can input rate of return estimates to show the difference in returns, and long-term growth of investment portfolios, before and after recommendations.

The whole point of this is that over time, your Proposed portfolio should have an overall better rate of return, unless you're deliberately trying to just reduce its risk exposure.

You can also input assumed future changes to the mix, contributions, and withdrawals annually.

With most all of this, you can see the differences between Current and Proposed.

• Then when you're done, and have dealt with the check numbers, and formatting, you'd just print, and add this asset allocation text to the rest of the financial plan. Tips for printing in Excel are here.

For money managers, you'd also put a copy of the printed reports in the clients' file.

Asset Allocation Calculator Directions

Step 1: Open the spreadsheet named Asset Allocation.xlsx.

Some people are confused because the file name for the demo is similar (asset_allocation.xlsx).

If you have the IFP, then the asset allocation module file name is Investments.xlsx.

Save it using a different name to preserve the original spreadsheet (but don't do this if you have the IFP as that will break the links). Here are tips for advisers saving and organizing client files on computer.

Step 2: The spreadsheet opens up to the Input sheet.

This part of the asset allocation software calculates the desired, recommended, suggested, or proposed allocation mix of eight major asset classes you're shooting for. This is the goal and where most of the value in the process is created.

Input the (clients') name as you want it to show on the top of the Asset Allocator sheet into cell G5. You don't need to do this if you're managing your own money. All it does is make this text show up in row 3 of the Asset Allocator sheet.

The remainder of the inputs are drop-down menu choices. When a cell is selected, a down drop menu arrow shows up at the bottom right asking you to select from the menu (so click on the black arrow pointing down). There are more instructions on how to make your selection in the text box that pops up for each of these input cells.

After you've made a selection for all of the input cells (G5 - G14) only one of the next three sheets will be active, and will display the recommended asset allocation mix at the bottom in row 37. The other two mix calculation sheets will be blank. This is the way it's supposed to work, to help keep the clutter and confusion down, so please don't ask for support when these sheets mysteriously go blank.

The numbers inside the matrix are the actual percentages to be held in each asset class. They will then populate the Guideline Asset Allocation Mix area of the Asset Allocator sheet (the middle row above the pie charts - row 50 on the demo).

If you'd prefer to customize your own asset allocation mix recommendation matrices, then there are the same three unprotected calculators on the far right sheet tab. How to use them is explained in a section at the bottom.

After input is complete, the guideline mix of both eight asset classes, and the "stockbroker mentality" mix of four asset classes, is displayed in columns F - K.

Assuming your input is the way you want it to be, proceed to step 3.

Step 3: Go to the Asset Allocator sheet.

Notice the row of colored cells in the middle section near the pie charts, between Current / Old and Proposed / New called Guideline Allocation. It's row 50 on the demo.

These cells are automatically populated from one of the three mix calculation sheets to the left. These cells are not protected, so you can just input whatever you want by typing over the formulas inside the cells. If you do this, then just be aware that these numbers will no longer change if and when you change inputs on the Input sheet.

Rows 49 - 51 on the demo's Asset Allocator sheet are the total asset class percentages for the eight asset classes. These are summed up in cells L49, L50, and L51. Row 49 is the Current portfolio's mix and 51 is Proposed mix (that you've created).

At the right on these three rows (column L, not on the demo) you'll see three numbers with percent signs. These are the "check numbers." Always check to make sure they are 100%, especially if you've manually changed anything in row 50 (of the demo).

All this is doing is adding up all of the individual percentage amounts in all of the eight asset classes to ensure it is 100%. If they don't, then you made a mistake somewhere and need to fix it before proceeding.

The same concept is used when you're distributing the contents of individual investments into asset classes in both the Current and Proposed sections. If the numbers are not 100% in column L, then it's usually because you entered a dollar amount in column C of the Asset Allocator sheet (not the GAAC sheet), and then forgot to distribute the proper values into the proper asset class(es) to the right.

Don't worry about the check numbers for now, as this is discussed in detail in Step 6.

Step 4: Inputting the Current investment assets.

If you're working from paper financial statements, first make piles organized by qualified (IRA) vs. non-qualified accounts. Then sort them by account (financial institution, or by different account within the same financial institution).

Separate all investments into tax-qualified / retirement investments, and personal non-tax-qualified investments. The reason for this is to input everything in a logical fashion, so they show up logically in the reports.

As you can see, there are two main sections on the Asset Allocator sheet, both in the Current and Proposed sections. On the demo, rows 8 -15 are for currently-held non-qualified personal investments. Rows 21 - 41 are for currently-held tax-qualified investments. This is the same in the Proposed section below.

Then it's also helpful to input accounts in alphabetical order, and then investments in alphabetical order within each account.

Enter all personal non-tax-qualified investments owned now into the Current Personal section of the Asset Allocator sheet. This is shown in rows 8 - 15 on the demo.

For the first investment, enter the investment name into cell B8. Then enter the current dollar value of that investment owned into cell C8.

If there are multiple accounts that you want to keep separate, then input the account name into B8, and then the name of the first investment into B9. For an example of this, see blank row 25, and then the start of a new account in row 26 of the demo.

Then when you're done, repeat the process with the tax-qualified investments into the section below (starting in row 21 of the demo).

Insert and delete rows as needed to enter all of the investments, and then delete all of the empty rows between the last investment and the total row (in the demo, the last investment row is 15 and the totaling row is 16) when you're done so it will look good for printing.

In the actual program, there would have been many rows waiting for asset input in between 15 and 16. These were deleted in the demo to show what it's supposed to look like when input is finished.

Tip for professional advisors: At this point in the program (where you've input their current holdings), we like to perform a very important time- and trouble-saving step called, "Data Confirmation."

If you've been doing this for a while, then you know that getting all of the current investments and accounts listed right can be the biggest battle (and no, making this all happen with online electronic data downloads into expensive software does not help, it makes it worse most of the time). We used to do this for a living full-time daily, and found that doing it all manually was much better than trusting an online download. There were way fewer "mistakes" that needed to be fixed during the data confirmation step, when doing it all manually compared to having data come in automatically from someone else's server.

Data confirmation is where you stop working, you print (or e-mail) what you did so far, then you submit the report back to the client(s), asking them to confirm that what you input so far is correct. This can be done by fax, printed page in the mail, by e-mail, or FTP file download.

Also, if you want to have investment data import from online sources, it's best to first make an intermediate spreadsheet to first capture the raw data. Then have the Asset Allocator sheet reference this data from the intermediate spreadsheet. This solves many problems, like formatting errors. It only takes a few minutes and will save you dozens of times that in fixing formatting and other errors later (that will come from online data flowing directly into the Asset Allocator sheet).

This is also a good time to point out that if you want to paste investment names and values from one source directly into the Asset Allocator sheet, using Paste Special, Values will also help prevent time wasted on reformatting later.

Most of the time, the clients will look it over and return it with changes. And most of the time, these changes will be mistakes made on their part, via trusting online data downloads, and/or changes in their lives since you first collected their data. This is normal, so don't be discouraged! Also if there was a big market move since you started inputting, this is needed just because of that, to input current values.

Even though this may delay the finished report, it's going to make you a hero to them, because you are doing the job they're paying you for - paying attention to detail, caring about getting things right and updated, and maybe exposing mistakes on their part.

When the finished report is submitted, the clients' review and sign off on it and the trades are made without incident, then that's all there is to it.

Most clients have dealt with several financial planners / investment portfolio managers in the past. If you're the first one to do this important work without screwing up, then you'll always have a warm place in their hearts, and will get referrals much easier.

This is also assuming that the only financial planning software module you're using for this client is the asset allocation software. If you also have modules like retirement software, then we recommend sending all of the first drafts to them in each of the modules to confirm.

So after inputting the data, and then linking them so they will be integrated with online downloads, get confirmation that everything is good when inputting Current data before proceeding to the next steps.

Only input the net equity of an investment real estate property. Do not input the market value as they really don't own this much because of the mortgage debt. The same for margin and other investment debt (which is not accounted for here). Input what you'd have net (before taxes) if you sold it. If you think this is not the way to go, and then input the gross value, it's up to you.

Also, don't input your personal residence, or other "utility investments," as there are not really investment assets. The same with your personal property, vehicles, boats, etc. Only input investment assets.

The test is this - is it able to provide some kind of paycheck to spend at retirement (or be sold for cash without huge losses)? If not, then it probably shouldn't be input here. You would only account for these personal items on the net worth software.

Step 5: Distributing the contents of investment vehicles into asset classes.

If you have access to Morningstar, or other investment database software that will give you a breakdown of what asset classes like a mutual fund really holds, then you can use that. For example, see row 24 of the demo.

The Morningstar Composition section of that Stock Market Fund says it holds 2% cash, 48% international stocks, and the rest in large-cap growth stocks.

Accounting for this level of detail will help tailor the portfolio to the client's life better, and will help result in more stable returns, more efficient risk reduction, less volatility, and the ability to yield enough income to avoid large redemptions. It may seem like these values are insignificant, but when added together, they usually are.

Please resist the temptation to type dollar amounts directly into columns D through K! We see this all the time. It may seem like the quickest and easiest way to do it, but it is not! It's going to cause errors, wasted time and trouble later in the process.

Always use basic Excel formulas here to make this easier:

Using the demo as an example, enter this formula into cell D24: =C24*0.02 to fill in the 2% cash allocation of that investment. This is not shown in the demo.

Then in cell J24: =C24*.48 to account for the 48% of the fund held in international stocks (with foreign stocks, it doesn't matter how much is growth or value, nor does size matter).

Then since the remaining stocks are assumed to be all large-cap growth stocks, account for the remainder like this in cell I24: =C24-D24-J24

When the accounting is all good, then the check number $0 will appear in cell L24 (this is also not shown in the demo).

If you don't know this information, you can just guess at what asset class the investment is really in, and in what percentages. Or you can always just forget about asset distribution and put 100% into the Large-Cap Growth bucket by entering into cell I24: =C24   Please do it like this, with formulas and not by typing dollar amounts directly into these columns, even if you are putting the whole contents into just one asset class.

Doing it like this correctly the first time will make everything automatic when you change the dollar amounts in the future. So even though it takes some tinkering, it will save you time, and having to go back to fix mistakes, later.

If you input dollar amounts into columns D through K, then this will cause much confusion, and duplication of effort later on when the dollar amounts of that asset change. So in the long run, it's much better to follow the directions here.

You can also get this asset class information from the prospecti, online via the mutual fund family's website, or by calling the fund. Calling always gets the most up-to-date numbers. This is an important step, and the value added is worth trying to get the data, as opposed to just lumping everything from a complex investment into one asset class.

But don't make a big fuss over getting it perfect. This is because the numbers you'll get change daily as the mutual fund trades on a daily basis. Getting them down to the nearest 2% is sufficient.

The size of the investment also makes it matter more. Don't make a fuss if you have a $100,000 portfolio, and you're trying to get data on a mutual fund worth $5,000. But if you have a $1M portfolio, and the investment is worth $100,000, then it's worth getting the most accurate and fresh asset class weighting data.

If you don't have access to this mutual fund composition data, or don't want to deal with this level of detail, you can just assume all U.S. equity mutual funds hold 5% in cash.

Then also assume all of the following types of U.S. equity mutual funds hold 10% in foreign equity: Large-cap Value, Large-cap Growth. Mid-cap, Small-cap, and Micro-cap.

Then just distribute everything left over completely into its appropriate asset class column. For example, a generic U.S. large-cap growth fund would hold:

Cash: 5%
Int'l: 10%
Large-cap Growth: 85%

If you just want to put the whole contents into one asset class, then you may want to make the Proposed portfolio hold a couple percent more in cash than recommended because the mix calculations are designed to account for mutual fund cash. This is also the answer to the question - "Why does the asset allocation software always recommend a few more percent in cash compared to the models, given the same risk tolerance?"

Repeat this process for the qualified investments in the section below.

Now deal with column L's check numbers: You'll know you did it right when all of the numbers in column L equal zero. If you've made a mistake, column L will tell you in which row, and how much you're off by, in dollars. All they do is compare the sum of columns D - K with column C. So if you've input the investment's value as $101,000 in column C and then made a mistake by telling it that $100,000 is in bonds, and then column L will display -$1,000.

The check numbers should always be zero, and not fixing them will cause other errors in other parts of the program. Each section has a separate check number totaler. The personal investments section's checker is shown in cell L16 (but it's not shown in the demo).

At this point, you're done inputting the recommended allocation mix and the Current portfolio. These two pie charts, and the data in the Sources and Application of Funds section should now display meaningful information.

Now it's time to move on to making the Proposed new portfolio.

Step 6: How to use the expanded asset class section for the far right bucket. This feature is in columns N through W.

After you've input assets and distributed their contents into asset classes using columns D through K, you can use columns N through W.

Using the demo as an example, go to N8. This cell uses validation to create a drop-down menu. Here K8 is all gold, so choose Natural Resources from the menu. The value in K8 will then flow to U8. After you've done that for the whole section, the percentage of that asset compared to the whole last bucket's totals (K16) will appear in U17.

The glitch is that when you delete all of the unused rows, you may need to fix the row numbers in the totalers. If you get errors, then manually change all of the references to be the same row. So it's safer to hide instead of delete.

For example, if this =IF(ISBLANK(N327),0,IF($N327="U.S. Small-cap Stocks",$K320,0)) looks like this and shows an error, then change it to this =IF(ISBLANK(N327),0,IF($N327="U.S. Small-cap Stocks",$K327,0)).

If there's more than one asset class, then manually overwrite the formula in columns O through W. For example, if something has both small-cap and micro-cap, and you want to account for half each, then using the demo again, input =K98/2 into O98. Then in P98, input =O98.

The point of this section is for you to make judgment calls about which asset classes to use in the last bucket. If someone is aggressive, then you'd use more tech and micro-cap. Conservative, use more real estate and water.

Step 7a: Set up to make the Proposed / new section of the asset allocation report.

After you've entered all of the Current investments (both personal and qualified), copy the Current investments into the Proposed part of the spreadsheet (using standard Windows copy / paste schemes; e.g., control C to copy then control V to paste).

First, ensure there are enough rows to accommodate the pasted data. If you make a mistake, and overwrite things, just press control Z (undo), press escape, insert enough rows into the Proposed section, and try again.

Using the demo as an example, highlight the range B8 to K15. Copying and pasting the whole row may cause problems, so it's best to only copy the exact range. Then press control C.

Then go to cell B81 and press control V. That copies all of the Current personal investments into the Proposed personal section.

Repeat the process for the qualified investments.

Then check your work at this point by looking at the pie chart section (rows 52 - 80 on the demo).

If you're getting a divide by zero error in row 566 on the original file, or 51 on the demo, then it's probably because you haven't input anything into the Proposed side yet, so it's dividing by zero.

The Current and Proposed numbers (shown in rows 49 & 51) should be exactly the same, and all three number sections above the pie charts should equal 100% in column L (L49, L50, & L51).

If anything doesn't equal 100%, then there is more than likely a problem with your asset class distribution (Step 5). If so, then you'll need to fix your input before proceeding. Don't feel bad if there are lots of things to fix, it's just a normal part of the process.

The last section of the Asset Allocation sheet, is called Sources and Applications of Funds (starting in row 152 on the demo). This tells you how much, both in percentages and dollars, need to be moved from one asset class to another to reach the calculated guideline asset allocation mix.

All this does is compare the Current mix to the Guideline mix (by subtracting row 49 from row 50).

Nothing you do on the Proposed section affects this part of the sheet.

You can look at the amounts in each asset class as a guide to how much to shuffle around in the Proposed sections. Use these discrepancies to justify your Proposed recommendations.

Step 7b: Set up to make recommendations (sell and buy trades) in the Proposed sections.

At this point, it's assumed that the Current data input is finished, copied into the Proposed sections, and everything checks out okay. So you will not be altering anything above (row 79 on the demo) the pie chart section anymore (other than tinkering with the graphs to make them look better).

Insert rows at the bottom of each account you're going to trade in to make room to recommend new investments. Each new investment vehicle is going to take up a row, plus add a few rows to play around with. Adding more than what's needed now is better than skimping, because it's only one step to delete all of these empty rows when you're done.

Now in the row at the bottom of each account, input the new investments' name, dollar amounts, and distributions, like you did with the Current investments.

In the demo, rows 89 - 96 are all newly inserted rows to accommodate the Proposed new investments (that were funded by selling the old Current investments above in rows 82 - 87).

Add new investments, and/or reduce / increase the dollar amounts in the Proposed section until you're happy with the new asset allocation mix (shown on the pie chart section and/or the numbers in row 51 of the demo).

How close you'd get the Proposed portfolio's percentages to the guideline is up to you (and client constraints). They are commonly off by large amounts stemming from multiple reasons.

An example would be having a rental property with net equity worth $100,000 (20%), when the total investment portfolio is only worth $500,000, and when the bucket in column J only recommends having 10%.

In this case, since they won't sell it, the allocation is just going to have to be off by that much (overweighted by 15% in column K). Leave it like that, so whenever they look at it, they'll be reminded that they have too much in real estate, and that you may think they should sell it off, buy a real estate mutual fund, and let them invest the after-tax proceeds into other asset classes with you (that will get better returns with less risk - like a tangibles fund).

Important: Ensure the dollar amounts in each account are the same in the Current and Proposed sections. It's easy to end up with money from one account being lost and put into another account during the Proposed shuffle.

These three check totalers are there when the program is sent. You want to leave them be until you're completely done, but then you want to delete them before printing (in the program, not the demo, see cells C677, C791, and C793).

In the Personal section of the demo, it would have been in cell C677, and the formula would have compared the totals of the Current Personal section with the Proposed Personal section (=C676-C335).

You can always go back to the original file and copy these formulas back in.

You can easily duplicate these check totalers to keep the funds in each account correct by using basic Excel summing formulas to keep all this in line as you work.

We like to insert a few extra rows while the work is being done to sum each account, so Current can be compared with Proposed. This works best if you do it all in column B, so it won't affect the sums in column C.

For example, using the demo, go to column L and make an account totaler for each account. This example uses Mary's IRA as the first account that gets sold off completely. First, sum this account up in cell M31, =sum(C27:C30)

Then when you're shuffling investments around in the Proposed section, and adding a new account and investments into cells C115 to C130, do this summing step again in cell M132, but this time add a subtraction math function to compare the Current vs. Proposed accounts: =sum(C112:C131)-M31

If you don't make a mistake, then this will always be $0. If you do make a mistake, then this $0 will change by the dollar amount of your mistake. Then as you're working, you can keep an eye on all of the accounts to ensure your shuffling isn't putting new money into different accounts.

Step 8: Make your Proposed recommendations.

In the Proposed section, shade all asset rows that have been reduced, or sold off completely, the orange color as shown at the bottom of the page (you can change colors to whatever you like). You don't have to do this, but it makes it flow nicely when processing the bottom lines of what to actually do in your head. This also makes it easier for clients to understand, agree, and then turn recommendations into trades.

Shade all new asset rows, and/or Current asset rows with Proposed increases in them, the blue color. This is an easy way to visualize the recommended changes in investment holdings.

The goal is to move things around (buy and sell between Current and Proposed assets) until the new / Proposed mix is close enough to the calculated guideline (close enough is up to you and client constraints).

To reduce the amounts of Current investments held, just reduce the dollar amounts in column C by that amount, or make them zero to sell them off completely. See cells C84 through C89 in the demo.

Cell C83 wasn't sold, because John's Gold isn't slated for sale for a few years into the future (in the sample financial plan, and this part of the program is a snapshot only of what's going on at the moment).

After you have reduced a bunch of investments, the total dollar amount of reductions will display in the check totaler in cell C677 (deleted in the demo). This is how much you have to work with when it comes to buying new investments in that account (that go into cells C84 to C89 of the demo).

NOTE: There are a few more check numbers that should also be "$0." They are under both Personal and Qualified Total cells, and also the Grand Total area of the Proposed section(s) that compares the Current totals to the Proposed totals. These are not shown in the demo.

This just compares the total dollar amount of Current investments vs. Proposed, and shows the difference so you can see how much your input was off by, so no money magically appears or disappears.

When they're equal, they'll all show $0. If it's not, you've made a mistake somewhere and need to fix it. How much it's off will show until all of the errors are fixed and it says $0. If the check number turns positive, then you've added too much to the Proposed new investments.

When you're done, just delete all three $0s of the check totalers so they won't show up on the printed report. You can also just hide column L so it won't want to print those extra pages.

Important! Please note that our mutual fund screening process does not look into when large capital gains distributions are about to occur. This usually happens in late fall. So before buying a mutual fund in a non-tax-qualified account, you should call the fund family and ask when they expect this to happen. If it's in a tax-qualified account (IRA), and then this doesn't matter.

The problem here is that if you buy a mutual fund in a non-tax-qualified account, then there's a large capital gains distribution, you pay tax on that and get no benefit (other than the increase in basis), because the value of the shares will fall by around the same amount.

You should wait until after these distributions occur before buying the mutual fund. Ignoring this issue can cause lots of annoying trouble for advisors (that should know better).

About Using 22 Asset Classes (and mutual fund picks) with the Eight Asset Class Buckets

As you can see, the asset allocation software only uses eight major asset classes, and we screen mutual funds for 22 asset classes (cash makes 22).

Here's the story on that:

Asset allocation is a science as well an art form. This way of doing things allows you to utilize the science as well as being an artist. How one distributes the 22 asset classes into the eight asset class buckets is more of an art than science.

The color-coding and arrangement of the major asset classes makes it easier to understand. Asset classes go from safe to risky as you read left to right. So safer asset classes are cool blue and risky investments are danger red.

The science says that the first bond bucket (U.S. Government (not agency) & Investment Grade U.S. Corporate Bonds) is a safer asset class (cash doesn't count). It also reveals that the correlation coefficients between these kinds of bonds and other asset classes are low, producing great diversification benefits.

However, the (three) types of bonds that fit into this bucket all behave too similarly to be worthy of making a separate bucket for them. So it's up to the artist to divvy them up depending on the client's particular circumstances.

This is now a good time to stop and read the page that explains the differences between the five risk tolerance categories.

For example, if one is Conservative, then Short-term bonds (with higher quality ratings) should be used more. If one is more Aggressive, then more longer term and Multisector bond funds should be utilized.

In the bucket, High Yield, Int'l, Agency, Zero Coupon, and All Other Bonds, international bonds are used more than high-yield and emerging market bonds for conservative investors. emerging market bonds are more for growth, and high-yield is more for income (both are for the more aggressive investor).

If the account is large, then both mid-cap value and large-cap value should be utilized in the High to Medium Yield & Value Style Stocks (U.S. Large- & Mid-Cap) bucket.

Value provides more income than growth, so it should be used more for conservative investors, and when portfolio income is needed.

The same goes with Low to No Yield & Growth Style Stocks (U.S. Large- & Mid-Cap). Mid-cap is a little more risky than large-cap. Large-cap also typically provides more income yield than the smaller caps.

In general, the larger the stock, the safer. Small- and micro-cap stocks are for the more aggressive investor. Sector funds, like technology, biotech, and Internet, are also more risky.

The reason why other sectors don't have the honor of their own asset class, is because there's probably not going to be a miraculous breakthrough in a sub-asset class, like finance, that changes the world (like there may be in technology, biotech, and Internet). The changes coming to the world is biotech is truly astounding. In Aug 2009, scientists were able to make an artificial eyeball, complete with a functioning retina, which are on the market in 2012. The same with robotic arms - which are already being used in the military. One wants to hold these stocks, if you can wait until profits come in.

"Innovation" in the financial services sector is usually nothing more than a geek at a bank writing a computer program that organizes checking account transactions that result in the maximum amount of overdraft fees. Everything that can be invented by Wall Street already has been, so the future consists only of creating new ways to "feed off each other" and "rip each other off;" So that's what there's no separate asset class for anything like that.

In the bucket, Int'l Stocks, Emerging Markets, & Other Int'l Equities; more international all-cap blend investments should be used if one is conservative. If one is aggressive, more international small-cap and emerging markets should be used.

Yes, you should always invest the recommended amounts into international investments even if you are a conservative investor, as these asset classes do fairly well most of the time when U.S. markets are down (the '08 to '09 and then July '11 debacle were all hopefully a once-in-a-lifetime thing).

In the bucket, Small-Cap, Precious Metals, Sector Funds, Real Estate, LPs, Misc. Equities; the asset classes utilized for more conservative investors would be real state, tangibles, and a little US small-cap. The more aggressive investors would utilize more micro-cap, Internet, technology, biotech, venture capital, private placements, etc.

If one is moderate, then a smartly-balanced mix of all 22 asset classes should be utilized (if the account has enough money to buy them all, of course. If not, then the, two small Model Portfolios may be a better tool to use than this asset allocation software).

The following list shows more details on how The 22 asset classes mutual funds are screened for fit into the eight major asset classes in this asset allocation software:

Asset Allocation Software and Mutual Fund Selection Buckets Asset Classes / Investment Vehicles Used
Cash Equivalents Taxable and tax-free cash equivalents: Bank and credit union savings and checking accounts, CDs, short-term T-bills, and money market funds. Loans coming due (that pay you) within the next three months.
U.S. Government (not agency) & Investment Grade U.S. Corporate Bonds Short-term / Intermediate / Long-term U.S. government bonds

U.S. investment grade corporate bonds, Multisector bond mutual funds

"Fixed" Investments such as Defined Benefit Plans, Fixed Annuities, GICs, and Preferred Stock (in qualified plans)

Municipal (federally tax-free) Bonds Federally tax-free (municipal) bonds: Short-term / Intermediate-Term / Long-Term
High Yield, Int'l, Agency, Zero Coupon, and All Other Bonds High-yield (junk) bonds, international bonds, and emerging markets bonds

International / Foreign / Emerging Markets, high-yield, convertibles, zero coupon, preferred stock (in personal, non-qualified accounts), ABSs (asset-backed securities, all agency pass-throughs like FNMA / Freddy Mac (FHLMC) / GNMA), Alternative Litigation Funding, and all other types of bonds that don't fit into any of the two above buckets

Large-Cap Value Equity Medium to large-cap, high-to-medium yield, "value" style U.S. equity securities
Large-Cap Growth Equity Medium- to large-cap, low-to-no yield, "growth" style U.S. equity securities
Int'l Stocks, Emerging Markets, and Other Int'l Equities All international (foreign) equity securities of all sectors, size, and level of development (both tangible, like real estate, and intangible)
Small-Cap Equity, Tangibles, Sector Funds, Real Estate, Limited Partnerships, Miscellaneous Equities All other types of U.S. equity securities than what's listed above:

U.S. small- and micro-cap stocks, real estate, private placements, venture capital, precious metals, sector mutual funds (technology, biotech / health care, Internet, water), limited partnerships, all derivatives, commodities, artwork and other tangible collectibles and collections

Step 10: Printing.

Tips for printing in Excel are here. Once you memorize the five clicks in Excel, you can get it to print anything you want in seconds, no problem. Write these steps on a sticky-note and stick it on your monitor until you remember.

First use Print Preview to see how many pages it wants to print. That, or going to Page Layout, clicking the tiny arrow at the bottom right, then Header and Footer, will make the printing dashed lines appear, so you can see what will print on each page. This is very helpful so your printer won't run amok and print tons of blank pages (or just the column L check numbers that you forgot to hide or delete).

You usually want to cut this number in half, and then use Print... to tell it to only print that many pages. If you just click Print, (without the ellipses ...) then it will print all of the pages with the check zeros in column L, which is annoying and wastes paper.

Use this method of printing for all of the following sheets. You can also hide the check numbers, by just hiding column L before printing. Basically, use Print Preview to control the pages you want to print so it won't print a bunch of blank pages. But to make sure you print only want you want, use the magic steps to print a range.

Tinker with the graphs (rows 51 - 76 on the demo) until everything is the way you like them. You will always need to tinker with the graphs. Detailed instructions on how to cope with Excel pie charts are here.

After you've implemented the new asset allocations in your portfolio (made the trades), and then your Proposed allocation now becomes your Current portfolio. So you may want to copy the contents of the Proposed portfolio into the Current section, and then save that using a different file name now.

This is basically it for creating an asset allocation report. The rest of the process, return / forecasting may not be needed for some people, so you may want to stop here to save time and work.

Step 11: Forecasting portfolio values into the future at the asset level.

Go to the Asset Returns sheet. Its purpose is to see weighted and unweighted returns of all portfolios' given rates of return input for each individual investment. In other words, you input returns for each individual investment, and it shows the whole portfolios' combined annual returns.

Then the asset class weightings from the Asset Allocator sheet ties everything together to display portfolio results. Results display on the Graphs and Asset Forecaster sheets.

Copy all of the Current and Proposed investment names and dollar amounts into the white part of the sheet. The Current part of demo is B8 : C15 and the Proposed part is B83 : C98.

To start copying, go to cell B6, input the equal sign, click the Asset Allocator sheet tab, and then click on the first row with an investment listed - or cell B8 (unless you added or deleted rows already).

Then press enter. Do the same with cell J5 of the Asset Returns sheet. When you have these two cells populated correctly, drag them down the sheet to copy all of your input from the Asset Allocator sheet. In case this is new to you, the drag handle is the little black cross at the bottom right of the cell. Move your cursor around until you see the black cross, and then drag your mouse, and the formulas will auto-populate. Once you get the hang of it, it's easy.

Input your estimated future rates of return for all investments, into column D, as shown in the demo. Make sure you have a dollar amount and a rate of return for all investments with non-zero dollar values.

You can use the actual returns of the 22 benchmark indices shown on the table of mutual fund returns if you don't know what to input here.

The formulas for doing most of the work on this sheet are in columns I & J and P & Q (so unhide them now). They are supposed to be hidden before printing because it's not something you want to show on the printed report. The program originally comes with protection on this sheet, to prevent accidents from wiping out formulas, but there is no password needed, so just go to Review, Unprotect sheet.

If you're inserting and deleting rows, ensure these formulas are still intact when you're done. Just unhide those rows and see if any formulas are missing. If they are, then you can just drag them up or down to replace them.

This will automatically generate the portfolio return numbers, shown in rows 25 - 25 and 75 - 79 of the demo.

Ensure there are formulas showing in columns D, E, L, and M in the last rows you're working with. If you have a lot of investments, and you inserted rows, then you'll need to drag those formulas down too to populate the new rows.

Don't forget to reformat the top and bottom borders after you've done this so the top border is bold (row 107).

When it's all good, rehide columns I & J and P & Q.

When you've checked things over to see if basic things are right (like the totals add up to 100%), and then this part is done. Print.

Go to the next sheet to the right - Asset Forecaster. Input the current year into cell C6. The rest is automatic, and you just print pages 1 - 4.

This sheet also comes protected, without a password needed, so you can do anything you want to with it.

Step 12: If you want to use the Asset Class Returns Forecaster sheet, input estimated future rates of return for all of the asset classes into the green-shaded cells in row 6 (C6 - J6 and U6 - AB6).

You can use the actual returns of the 22 benchmark indices shown on the table of mutual fund returns if you don't know what to input here.

The point of this page is to grow both portfolios over time using the asset class returns input and weightings from the Asset Allocator sheet. See Step 13 for more directions.

You normally want to use slightly higher numbers in the Proposed sections because you're on the ball and can do better than they have in the past. The rest is automatic, so just print.

Results display on the Graphs sheet.

These two sheets cannot be unprotected without the password.

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, then you can get a version sent to you that does that.

Step 13: Forecasting portfolios into the future at the asset class level.

The purpose, for example, is to be able to change the allocation weights and returns to account for things like being more conservative when retired, while accounting for contributions and withdrawals. This is actually the retirement software's job, but this will help give you a basic idea of what will happen without having to buy that module just to do that job.

If you want to use the detailed 75-year cash flow data input, ensure you input the current year into cell C6 of the Asset Forecaster sheet. Also, the first year's asset class return data needs to be input into the Asset Class Returns Forecaster sheet.

Then switch to the Future Data Input sheet.

This allows you to add future contributions and withdrawals, while being able to change the allocation weights and returns in any year.

Columns D - K and N - U are where you change the annual asset class mixes.

Columns Z - AG and AI - AP are where you'd change the asset class rates of returns.

Columns AT through BA are where you'd input annual contributions and withdrawals.

The first two large areas are where you'd input each year's changes in asset allocation mix (columns D through K and N through U). If you leave any cell blank, or input zero, and then it will default to the first year's allocation weights.

The first year's numbers in row 7 come from the Asset Allocator sheet, and can only be changed there.

Don't input negative numbers in this section, as it will cause errors.

Remember to keep an eye on the check columns - L and V. If your input doesn't add up to 100%, and then it will tell you. So if it adds up to 90%, then you forgot to add 10% somewhere.

The next two areas (columns Z - AG and AI - AP) are where you'd input the annual rates of return for each asset class in each year. If you want to use a zero rate of return, you'll need to input a tiny number, like 0.0001% so it won't detect a zero, and thus use the default rate. You can use negative rates of returns here.

The first year's returns are hard set to be what they are in the Asset Class Returns Forecaster sheet, and can only be changed there.

The last section (columns AT - BA) is where you'd input the annual contributions and/or withdrawals from each asset class for every year. Input negative numbers for withdrawals and positive for contributions. If you don't want to favor an asset class, and want to withdraw or contribute to the portfolio equally, and then divide the annual number by eight, and input the same numbers into each asset class.

The point is to input your expected cash flows, so that the future values of all of the portfolios will display on the Graphs sheet.

Step 14: How to tinker with the Graphs sheet to show the range of years you want to see:

They are currently set to show the first 25 years of data. More than that makes it too hard to see, and that's pretty far into the future to be forecasting investments anyway.

Here's how to change the graph's data range, so you can make it show any range of years you want within the 75-year window:

Click inside the middle of the top left chart. If nothing shows up in the formula bar (where you edit formulas), and then press the up or down arrow until it does.

See the data ranges $B$83: $B$107 and $G$83: $G$107? That's a 25-year range. To change it to a 50-year range, change these numbers to $B$83: $B$132 and $G$83: $G$132

You'll need to change all of the ranges in all of data series too. So on the first two top charts, after you change one series, press the up or down arrow until the next series shows up, and repeat. You can see what's going on as you do it.

Excel 2007 makes this easier than older versions, because you can also right click and choose Select Data, and do all of this like that.

The ranges where it gets the data from are hidden on the Asset Class Returns Forecaster sheet, so it's a little hard to tell which rows correspond to what year. So here's some help: Year #1 (current year) is row 83. Year 5 is row 87, year 10 is row 92, year 15 is row 97, year 20 is row 102, year 25 is row 107, year 30 is row 112, year 50 is row 132, year 75 is row 157.

The difference between unweighted portfolio return and weighted investment portfolio returns: Numbers in the unweighted returns column are just the eight rates of return added up and divided by eight. This is not a good way to look at it because if you have $1 million in one asset class, and $1 in another, both rates are treated equally. Weighting makes the money in the $1 million asset class a million times more important in calculating the true portfolio returns.

That's about it. After you tinker with it for a while, the light bulb will pop on over your head and see what it's all about, and you'll be adding new graphs and all that customizing to make it your own.

Creating Customized Asset Allocation Mixes

If you want to, you can ignore the automated Input & Mix Calculation sheets, and manually create your own asset allocation mixes. The Custom Mix Calculators sheet to do this is at the far right.

There are three matrices, just like there are for the automated ones.

You'll need to fill in all of the percent numbers manually. When that's done, then choose which one row in each of the five sections applies to the client's life.

For example, if someone's risk tolerance category scored Moderate, then first shade all of the cells in the Moderate row. Then unshade the row it was before.

For example, if they plan to retire in the next year, then use the Retired Guideline Mix Calc GAAC sheet. The biggest reason for this is that moving from a working to a retired investment portfolio usually means restructuring it for both less risk and income generation. If you do that now, and the market goes down between now and when they actually retire, then you're going to be a hero.

The worst that can happen is a major bull market. If this happens, don't worry - you're using asset allocation. This means that you're going still to participate in the bull market, just not as much as you would have.

The totaler cell J37 will add up to 100% when you're done, and when everything is working right.

Tip: To input the results into the Asset Allocator sheet correctly the first time, first copy the data from the GAAC sheet (cells C42 to J42 or C43 to J43) by highlighting it and pressing control C, or however you like to copy.

Then instead of doing what you usually do to paste, do this: After copying, highlight where you want to paste into (in the demo, it's cells C48 to J48 on the Asset Allocator sheet). Then right click and choose Paste Special, and then choose Values only from the Paste section, and then click OK. This will paste the data right the first time without having to mess with Excel glitches, like colors changing on you.

You can also change the names of the asset classes, the numbers, or anything to whatever you want. The numbers in the matrix are the actual percentage numbers used for each of the eight major asset classes. So if the Cash asset class has a total of 10%, then that's how much they should have in Cash / Bank accounts / Money Market Funds, etc.

The bottom-line answer from this sheet (row 37) is then input (copied) into row 48 of the Asset Allocator sheet. This sheet determines the mix of asset classes that is currently held, and then compares it to the calculated Guideline, so you can make changes (recommend buy and sells to reach the guideline). This is shown at the bottom in the Sources and Applications of Funds section.

So far, all you've done is manually perform Step 1, and selected which one of the three GAAC calculators to use. Now go back to Step 2 above and resume work.

Now manually copy and paste these numbers into the Asset Allocator sheet's Guideline Mix section.

If the row changed from row 48, it's the Guideline Allocation row between the Current / Old Allocation and the Proposed / New Allocation rows directly above the pie charts.

How to Rebalance Portfolios Quarterly

First, it needs to be said that you should not wait for your mutual fund pick updates to rebalance. Swapping funds and rebalancing are different things. It's common for investors to think, "I know I have to rebalance, but I'll wait until I get my new mutual fund picks. This will save me time because I'll be in the software trading already." Just don't do that. If it's past time to rebalance, just do it now (but see the tip below first!).

Rebalancing is the technical asset allocation term that basically means this: At certain pre-defined intervals, you reshuffle asset class (mutual fund) amounts back to their original recommended asset allocation weights.

Research shows the most effective time frame is quarterly. This must occur in order to get most of the benefits of asset allocation!

Explanation in English: Say you are brand new to the investment allocation and you're starting with $100,000, and you scored Moderate risk temperament. The allocation for large-cap growth is 15%. This 15% weighting does not change in response to anticipated market moves, so you're eliminating market timing by keeping the percentage of your total investments in the large-cap growth asset class at 15%.

This would be impractical and too expensive to do even monthly, so it's only done at the beginning of every new quarter (or when the weighting for that asset class changes - which rarely happens anymore).

So in this example, you would initially buy $15,000 of the large-cap growth mutual fund.

Say at the end of the current quarter, this large-cap growth mutual fund has grown to $20,000, but due to the other asset classes losing money, your total portfolio is still $100,000.

You are now overweighted in the large-cap growth asset classes by 5% or $5,000. Since the total portfolio is still $100,000 you have to also be underweighted in other asset classes.

So you'll need to sell $5,000 of this large-cap growth mutual fund, and put (reallocate) these funds into the asset classes (mutual funds) that went down over the quarter.

Assume everything else is flat, but the generic bond and small-cap asset classes are both down 2.5% each. In order to bring your total allocation in these two asset classes back up to its normal balance, you would take $2,500 and buy more of the generic bond's mutual fund, and also add $2,500 to the small-cap mutual fund.

You are now back to the original asset allocation weights, and this is called rebalancing. Before you did it, you were "out of balance."

One of the best advantages in the methodology is that it forces you to eliminate all of the emotion, market timing, gut feeling, intuition, tips, and all of the other things in the investment world that end up losing more money than it makes.

It forces you to sell some of the asset class that has went up recently, and at the same time, it forces you to buy more of the asset classes that have went down recently. The #1 saying in Wall Street is to "buy low and sell high" - and this is the best methodology ever invented to be sure you're always doing that, without having to fret over it. In other words, just do it, when you're supposed to, regardless of how you "feel" about it!

In reality, since the dollar amounts will be smaller for most investors, it may be impractical to buy and sell mutual funds with these small amounts. If you're investing your own money, then it's your call on whether the trades are worth it in terms of trading costs and taxes.

Professionals have the added problem of investors complaining about various things, even when they're doing everything right. A more detailed explanation of what to do about all of this is below.

Even though this may seem like a strange way to do things, it is very old hat in the investment world, is taught in every real investment school, and is practiced by the majority of financial planners and professional investment managers (although not very well in the Real World).

So you very much need to pay attention to rebalancing to get most of the benefits of asset allocation. If you bought support, then you can ask questions. The best questions are from people that ask if they should switch mutual funds and rebalance when they get their monthly mutual fund picks and investing model updates. You can send details of your situation and you'll get a Real World answer back.

Tip: Since millions of people, and automated computer programs, are rebalancing around same time, you may want to pick a different day to rebalance than the first day of the new quarter. You may make a few extra bucks by doing it a week before the new quarter.

That way when investors are selling things that went up, you'd be selling before everyone else, which means there will be less downward pressure on prices. And if you're buying asset classes that went down, doing it before everyone else will allow you to buy before everyone else, again beating the upward pressure. In general, it rarely pays to be in the herd. This is just a tip to be one-step ahead.

About Switching Mutual Funds when They're Changed

One of the mutual fund screening criteria is to weed out funds that have back-end redemption fees, (B-shares). So unless there's a Morningstar error, or you've made a trading mistake, there won't be any back-end loads or redemption fees to worry about when you switch mutual funds (so the only costs to selling a fund will be the trading costs and capital gains taxes in non-qualified accounts).

Recently some mutual funds have restrictions on how long you have to hold it before you can sell it, or they ding you in certain ways. This is too complex to give generic advice, so if you bought support, then send an e-mail explaining your situation and you'll get an answer back. Sorry, there's no screening function in Morningstar yet for this (we asked in 2006 and they said they would think about it someday). It probably wouldn't be added to the screens anyway.

It's impossible to give exact advice about when to switch a mutual fund, because there are too many factors to consider. The purpose of switching funds is to use a fund that will behave most like the asset class going forward.

The new mutual fund is not intended to "go up" the most. The new mutual fund is intended to act like the asset class the most, and at the same time, go up more than that asset class when that asset class is going up, and go down less than the asset class when that asset class is going down - over the next year or so. This isn't a Morningstar-type firm, where we're getting paid-off to tout a mutual fund (or mutual fund family) as being "the hottest thing since the invention of the wheel."

In general, it's usually best to switch the mutual fund in the Real World when a switch is made. It's also always best to not put any new money into the old mutual fund after the switch. However, there are numerous Real World obstacles when it comes to switching mutual funds.

So the first thing to do when you look at the spreadsheet Mutual Fund Picks.xlsx, is to look at the reason for the fund change. More details are in the comment section of that cell, and will display when you hover your mouse over it. If you want more details, and then see the text of the e-mail the updates were sent in. If there's a long version, it will be in the text of the delivery e-mail (if you're a Model Portfolio subscriber).

If a fund was sold because it closed, then it's best to keep it. The reasons for funds closing usually are not bad. You won't be able to add brand new money to a closed fund, but most of the time, you can add money to a fund if it's already held before it closed. This is fine. Just be aware that the fund will be off the screening radar, so if it goes bad in the future, you won't be notified.

If the fund was sold because its style or asset class drifted or changed, then it's best to change the mutual fund. Ditto with new fund managers.

If the mutual fund changed because of "performance," then you'd most always want to make the switch, as something bad happened to the mutual fund over the last few months or so, and experience has proven that it won't magically fix itself, so the new fund most always does better going forward.

Mutual fund families (the overall mutual fund company name, like Janus) will most always pull shenanigans with their mutual funds if they think they can make more money. So they let the mutual fund managers change the stated objective of the mutual fund without changing the mutual fund's investing strategy (and/or vice versa).

Fortunately, Morningstar's screening filter will catch most of these shenanigans. Mutual funds that use deceptive marketing tricks are most always screened out of the results.

The best reason to make a switch is when the reason is performance related. But at the same time, the difference in performance between the old and new fund may be so small that it may be impractical to switch. This is where you want to carefully read the longer explanation given in the cell comments and e-mail text.

More Real World Factors to Consider Are:

• If the clients' holdings are small, then the ticket charges may be too big as a percentage to make it feasible to switch. For example, if the holding is $1,000 and it costs $20 to make a trade, then it's going to cost 4% to make the switch (2% for the sell and 2% for the new buy). This is too painful for most investors, so you'll have to consider that.

• If the account has recently had a lot of trades, then the client could be annoyed with all of the trades.

• If the client is adverse to trades in general, then that needs to be considered.

• If your BD's compliance department is watching your activity, and giving you grief about trading too much, then having to waste time explaining trades to them would be a factor too.

• If the client is happy with your services, then switching will go better than if the client is grumpy. Unless they're grumpy about performance, then you can say you're making the trades to get better performance (which is truly the whole point of switching anyway). Sometimes the client will be happy if you do anything but nothing, and making trades to get rid of bad performing investments, is doing something (what you're supposed to be doing in their minds).

• If you have full discretion, then it's easier to make fund changes than if you don't. If you have to make a million phone calls, or send out letters / e-mails, to get permission to trade, then that's a huge factor. It's up to you whether to go through all of that to make trades.

Some Broker Dealer's are slowly getting with the program of asset allocation, and have come up the concept of "limited discretion." This means that if you're practicing asset allocation, then they'll let you make switches without having to notify the client when you are rebalancing. The trick is to always use buzzwords that your compliance people will understand - and "asset allocation rebalancing" is one of them. That usually gets them out of your hair the quickest.

• Obviously making trades costs time, money, work, and has various risks involved (the risk of making a trading mistake, then having to waste time fixing it, is always lurking about). So it's up to you whether it's worth it or not.

This is a complex issue, so feel free to tell your story and you'll get personalized advice. You're not bothering us, we want to hear from you and help you manage your portfolios as well as possible.

How to Create Model Portfolios with the Asset Allocation Software

About what Model Portfolios are.

Assuming that you now know what they are, here's how to do it using this Comprehensive Asset Allocation Software:

First you input a recommended mix of asset classes into the Guideline Allocation row on the Asset Allocator sheet. You can make them up yourself, or you can use the results of the existing calculation matrix sheets.

Then input the investments you want to fund the asset classes with into the Proposed section of the Asset Allocator sheet, as described above.

Input past returns into the future input sheet(s).

This is now pretty much the same thing as a model allocation - because it exists before it's invested in.

Now save the file using a name that represents the model - like Moderate Model.

Now whenever you want to work with a moderate model, you can just open and use it.

You can ignore / delete / hide / not print the Current section, where you input Current investments to get a comparison, or you can leave them blank. Or you can use the program normally, other than calculating the mix on a custom basis.

Nothing is protected, so you can make any changes you want to.

More decade-old generic advice about the mechanics of money management and trading is here.

Financial Planning Software Modules For Sale
(are listed below)

Financial Planning Software that's Fully-Integrated
(the IFP is the NaviPlan alternative for 1/6th the price)

Goals-Only "Financial Planning Software"
(the MoneyGuidePro alternative for 1% of their price)

Retirement Planning Software Menu: Something for Everyone
(the RWRs, RP, and SRP)

Comprehensive Asset Allocation Software

Model Portfolio Allocations with Historical Returns

Monthly-updated ETF and Mutual Fund Picks

DIY Investment Portfolio Benchmarking Program

Financial Planning Fact Finders for Financial Planners Gathering Data from Clients

Investment Policy Statement Software (IPS)

Life Insurance Calculator (AKA Capital Needs Analysis Software)

Bond Calculators for Duration, Convexity, YTM, Accretion, and Amortization

Investment Software for Comparing the 27 Most Popular Methods of Investing

Rental Real Estate Investing Software

Net Worth Calculator (Balance Sheet Maker) and 75-year Net Worth Projector

College Savings Calculator

Financial Seminar Covering Retirement Planning and Investment Management

Sales Tools for Financial Adviser Marketing

Personal Budget Software and 75-year Cash Flow Projector

TVM Financial Tools and Financial Calculators

Our Unique Financial Services
(are listed below)

We're Fee-only Money Managers: So you can hire us to manage your money, and/or financial advisers can hire us to manage client money, using our Model Portfolios and/or Asset Allocation Systems

Mr. Market Timer's Unique Market-neutral Stock Market Timing Services
(the hedge fund alternative)

Consulting Services: Hire Us to Make Your Financial Plan, Retirement Plan, Benchmarking Report, Whatever

Buy or Sell a Financial Planning Practice

Miscellaneous Pages of Interest
(are listed below)

Primer Tutorial to Learn the Basics of Financial Planning Software

About the Department of Labor's New Fiduciary Rules

Using Asset Allocation to Manage Money

Download Brokerage Data into Spreadsheets

How to Integrate Financial Planning Software Modules to Share Data

CRM and Portfolio Management Software

About Monte Carlo Simulators

About Efficient Frontier Portfolio Optimizers

Calculating Your Investment Risk Tolerance

About Discount Brokers for DIY Money Management

About 401(k) Plan Management

© Copyright 1997 - 2017 Tools For Money, All Rights Reserved