Asset Allocation Model Instructions
|Go to the Main Model Portfolios Page||Using Asset Allocation to Manage Money||Download the Model's Demo||Using Discount Brokers to Manage Your Money|
(is listed below. The financial planning software modules for sale are on the right-side column)
Confused? It Makes More Sense if You Start at the Home Page
Discounts for Financial Advisers
Questions about Personal Finance Software? Call (503) 309-1369 or Send E-mail to email@example.com
Free Downloads and Money Tools
|First, here are some generic directions that apply to most of the personal finance software on this site:
When you're using the program, things go better if you first print out these directions.
Click "Enable Editing" to make it so you can change inputs. If you don't do this, then "nothing will happen" when you input data. Any change is editing (according to MS). This occurs because the program is not in a "Trusted Location." Go to File, Options, Trust Center, to fix that.
You switch between the sheets (pages) that make up the workbook (AKA spreadsheet) by either clicking on the sheet tab names at the bottom left of the Excel window, or by pressing Control Page Up or Down (pressing the Control key and either the Page Up or Page Down key at the same time).
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 from these original files.
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.
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.
How to send financial plans to clients via e-mail, without sending the whole program is explained on the Excel help page.
If you need to print something on a password protected sheet, and you need to tinker with column and row sizes, and you bought support, and it's in Excel 2007 format, then you can get a version sent to you that does that.
Tips for advisors saving financial planning client computer files are here. 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. Flash drives are not safe because they are vulnerable to EMP, eventually wear out, and are in general, too flakey. It's critical to back up your data, but not programs.
How to send financial plans to clients via e-mail, without sending the whole program.
This page has the directions for the Asset Allocation Models.
Here are the directions for the Comprehensive Asset Allocation Software program.
Most everything in the models are "hypothetical." This is the legal term that needs to be used in the Finra world of compliance.
It's not "actual" because the returns shown in the spreadsheet Models for Current Month.xlsx are not "linked" to account for past trades.
Returns shown are just those looking back in time using the current mutual fund picks (which were not there years ago).
To see "actual" returns that account for past trades, fees, rebalancings, and all that, see both the spreadsheet you received with the word "Linked Returns" in the file name, or the returns shown on the tables here and here.
The details about how model returns are calculated are below on this page.
The three spreadsheets you have with the words "Linked" in the file names are just for calculating historical returns, accounting for an advisor's fees, rebalancings, and for seeing the past mutual fund switches. They don't do anything else but supply this data, and are not used in these directions, so you can ignore all of them.
Once you're on a model sheet (e.g., Fee-Based Model) it's helpful to know that the only thing that's different between the five models (Conservative at the top through Aggressive at the bottom) are the Allocation Weights in column O.
Everything else is the same and is just referenced from the Conservative Model.
Directions for Investing Consumers (Non-professionals)
If you're using the asset allocation model for your own investing, you can ignore the directions in the section below for financial advisors.
Both of our do-it-yourself money management systems (these Models and the Comprehensive Asset Allocation software) give you the choice of opening your own discount brokerage account and make your own trades, or you can hire us to be your money manager to custody your accounts just like a local adviser.
You don't have to use the recommended mutual funds to fund these investing models. You can replace any of them in any asset class with mutual funds of your choice, ETFs, index funds, stocks, bonds, individual securities, life insurance company subaccounts, 401(k) options, or anything else you want to.
The only catch is you'll have to come up with the historical returns to make the return calculations work right.
Open the Excel spreadsheet named: Toolsformoney Models for (last month).xlsx
Please note the file name will be the current month, but return data will be as of the end of the previous month.
Step 1) Determine your Investment Risk Tolerance Category.
If you don't have a financial tool for doing that, and don't want to guess, then the Investment Fact Finder is the tool designed to do that job.
For example purposes here, let's assume you did that, and scored "Moderate," which means, average or middle-of-the-road risk tolerance.
Step 2) Choose which overall investor model you're going to use. The sheet tabs in the middle of the model program correspond to these methods of investing.
The seven models for non-professional investors are:
• Hybrid (mutual funds and ETFs).
• All No-load Mutual Funds.
• All ETFs.
• All Index Funds.
• Conservative No-load High-income Model (CHIM).
• All No-load Mutual Funds with less than $60,000.
• All No-load Mutual Funds with less than $20,000.
The Fee-Based and Load Models are for advisers and the Benchmark Index Models are for use as benchmark portfolios to compare passive-vs.-active investment management.
For the rest of the directions, we're assuming you chose either the Hybrid or the No-load Mutual Fund Models. Starting in 2017: HYBRID MODELS ARE RECOMMENDED AND THE NO-LOAD MODELS ARE NOT!
Go to that sheet in the Models spreadsheet by clicking on the sheet tab at the bottom left labeled, "Hybrid Models."
Now you can ignore the other sheets, as they're not used.
Please note that if the workbook asks you to Edit Links to another workbook, this is because some of the charts in the Models use data from the four Linked Returns workbooks. So just have those workbooks in the same folder, and it should just find it by itself and everything will be fine. If not, then put all files in the same folder, then refresh the links (Data, Connections, Edit Links). If you don't care about that, then cancel out of that box.
Step 3) If you want to keep track of the returns daily, then you can input each investment's return numbers (columns I, K, and M) from a source of financial data, like the stock / mutual fund section of most newspapers.
Remember not to input cumulative three-year returns. Input the average annualized three-year returns (the annual average over the last three years).
Finding the last 12 months and last three years annualized average returns are harder than finding the year-to-date numbers. You can get help by trying to find it online, calling the mutual fund, looking in the prospectus, contacting your custodian (e.g., Ameritrade).
Important: Remember to only edit the return numbers on the Conservative Model (rows 6 - 21), because the other four risk tolerance categories below just get their investment return numbers from there.
You'll almost never need to tinker with anything below row 21.
If you input new return data, use the Save As... function to save the workbook using a different name and date to preserve the original workbook.
Step 4) Go to the Fee-Based Models sheet and ensure there are 0 (zero) in cells AA1 and AA3 to make the Before and After Fee return numbers the same.
These fees are what a money manager would charge a client, so since you're doing it yourself, there are none of these fees.
Step 5) Assuming that you did Steps 1 - 4, you're now looking at the Hybrid Moderate Model by being on the sheet tab titled, Hybrid Model
Look over rows 90 - 122. This is the Moderate Model. If you scored Moderate, like we're assuming you did here, then you can ignore the two models above and the two below (Conservatives and Aggressives).
The only other thing of interest to you are the returns graphs staring in row 217, and where you'd input return data into the Conservative Model in rows 6 - 21.
If you're sure you're not going to care about the other four models, then can hide all of those rows, and input the returns directly into the Moderate Model in rows 91 - 106.
Now take the amount of money you plan to invest, and just start multiplying that amount by the Allocation Weights listed in column O (let's use $100,000 as an example).
Yes, all of this assumes that you do not already have investments, so all of the money in the account is in "cash."
Sorry, but you'll have to determine if something should be sold yourself.
So in these directions, we're assuming that you're starting off with $100,000 in cash (money market) with current no investments that need to be sold to cash.
And let's assume that you're using Scottrade as your custodian and you're going to use the Mutual Fund picks that come with the investing models. About opening a discount brokerage account so you can manage your own money
Don't do anything with the Cash allocation, because whatever is left over when you're done, will just remain in the money market account (a cash equivalent). These are sometimes called a "sweep" account.
Now switch to the Mutual Fund Picks spreadsheet that came with the Model Portfolios, as you'll need this to get the mutual fund ticker symbols to make buy trades. Ensure you're using the latest month's sheet tab, as shown at the top and on the sheet tab name.
First, look up the ticker symbol for the mutual fund used in the first asset class in the portfolio model (Short-Term Bond).
Take the corresponding ticker symbol, then tell Scottrade to buy that mutual fund, in the amount that you'll arrive at when you multiply the Asset Class Weight percentage in column O by the total amount of money you're working with ($100,000 in this example).
Don't give your trading people any trades to make until you're all done, and have checked your work! One little trading mistake will cause lots of future repair work, and could cost money too.
Continuing with the example, and assuming the Short-Term Bond allocation is 5%, you'd buy $5,000 of that mutual fund.
Repeat the process with the remaining 14 asset classes. When you're done, the remaining amount should be the cash allocation. You won't need to buy anything with that money, because Scottrade will just keep it in their cash equivalent money market mutual fund.
Don't worry if things are off by a percent or so, as asset allocation is not an exact science. If you're off, and thus have extra money to invest, buy more of the mutual fund in the asset class that's currently down the most (and vice-versa). If you don't know, don't want to guess, and you bought support, then just send e-mail to ask.
If you can't buy the recommended mutual fund via your custodian, and you bought support, then just send e-mail describing your situation, and you'll keep getting replacement suggestions until you can buy something in that asset class. If this can't be done, then you'll get advice on where to put the extra money.
Please note that getting replacements that are confined to a list is not covered by normal support. That falls under the category of making custom Models, and thus is consulting work for an hourly fee.
Conservative No-Load High-Income Model Investors (CHIM): If you're retired and using this for a paycheck, then be sure to set ALL of the mutual funds up to pay ALL distributions in cash (dividends and capital gains). In other words, do not "reinvest" anything.
The whole point of this is to collect as much cash as possible to get the biggest paycheck, so you won't have to redeem shares.
The same would go for the other models if you are retired wanting a conservative investment portfolio paycheck (for any reason, not just retirement).
For all other models, when you're not retired and this is not in a tax-qualified account (IRA), then it's your preference.
But in taxable accounts when you don't need the income to spend, we like to set everything up so dividends are reinvested, but all capital gains are not. The reasons are because these are taxable events, so you should use them to build up a little cash for several reasons (one would be to pay the taxes).
Next, dividends are more of a fixed income thing than an equity fund thing, so the vast majority of dividends will come from the bond funds. Next, capital gains distributions are usually much less than dividends.
Next, capital gains are more of a stock fund thing than a bond fund thing, so capital gains distributions on bond funds are insignificant.
Another reason would be to use these cash distributions to buy more shares of asset classes that are down when it comes to rebalancing time (to help minimize sales). So this money will sit for a while just to be available until rebalancing time.
These distributions are usually profits, so they usually happen when an asset class is up, meaning that will probably be the asset class you'll need to redeem shares from at rebalancing time anyway. This, in a small way, helps you to rebalance, and thus "buy low and sell high."
On the Moderately Aggressive and Aggressive Models, then you'll probably want to reinvest everything.
All of this is up to you, of course. It's hard to give generic advice because everyone is different. Feel free to e-mail your situation and more specific advice may be given.
What to do if you don't have enough money to make a minimum mutual fund purchase:
The Moderate Model requires the most money overall to invest in, because it uses the most number of asset classes (all of them). So the minimum is usually around $90,000 to $100,000.
As you move away from moderate, these minimums decline substantially.
If you don't have support, then here is what to do: First use either the $60,000 or $20,000 minimum no-load models.
If you don't want to do that, because you're close to having enough, or will soon, then start looking over the risky (sector) asset classes in rows 99 to 104. Pick the one that has the largest year-to-date return number.
Don't put any money into that asset class / mutual fund (since you'd be "buying high").
Since you're not going to use it, you now have extra money to work with. Use this to buy the asset class / mutual fund that you had a hard time meeting the minimum initial purchase requirement.
If it's still not enough, then repeat this process until it is. Ensure that the return numbers for the asset classes you didn't use are all either deleted or you input zeros. You'll also have to tinker with the asset allocation weights in column O to make the grand total returns results be correct. Just tinker with the numbers in column O based on how you shuffled money around until the number displayed in cell O107 is 100%. If you're confused, e-mail the file back and we'll check it for you to ensure you did it right.
That's about all there is to setting up your portfolio model. You now own a diversified array of mutual funds, and in approximate amounts, that match the most important investing life factor, called Investment Risk Tolerance.
Once you're set it up and invested everything, then all there is to do is monitor and change the mutual funds monthly (if you subscribed) and then rebalance quarterly.
Important! Please note that the 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 in the third quarter of the year, you should call the fund family and ask when they expect this to happen.
The problem is that if you buy a mutual fund in a non-tax-qualified account, and then there's a large capital gains distribution, you'd 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.
None of this matters in any form of an IRA or 401(k) account.
One should wait until after this distribution occurs before buying the mutual fund. If you have support, then just let us know and we'll work around it.
Step 6) As time goes on, the mutual funds used in the investing model will change. Any of them could change at any monthly updating cycle.
If you aren't subscribing to get these monthly updates, then you'll either continue to hold mutual funds that may not be "good" anymore, or you can guess and switch them out yourself ad hoc.
The problem with that is you won't know when a mutual fund stopped performing, because you don't have the mutual fund screening software needed to compare it to its peers or its benchmark index, or know how to use it if you did.
Most of the time, mutual funds go down just because that asset class went down. So swapping a mutual fund because it went down because of that may do more harm than good, for many reasons. So if you do this, you'll just be guessing all the way.
If you are a subscriber, then look at the Mutual Fund Picks spreadsheet that you'll get in your e-mail monthly (around the third day of the month). Look for color-shaded cells (examples of which are at the bottom of the workbook). Those are the mutual funds that changed from the previous month.
There should be comments that pop up on your screen when you hover your mouse over those cells (after clicking on them).
Just sell ALL of the old mutual funds and buy the new mutual fund recommendations with the sale proceeds. If a mutual fund changed on the both the models and fund picks spreadsheets, then that means you should sell the old one and buy the new one ASAP.
Detailed instructions of when a switch is needed or not is in the text of the e-mail that your monthly updates came in. The short version can be found by clicking on that cell. Text will be there (and will show up in the formula bar), then the "comment" box will appear with that text too.
Sometimes making the switch is not "critical." So if it's not, then you don't "have to" if you don't want to. If the word "Replace" is not there, then it's your call. If you have support, then you can ask about it and an answer more specific to your situation will be given. This is not an exact science in a perfect world.
If the reason for replacement is something lame, like, "current performance," then sorry - that's the reason. Its performance recently has not been as it "should be," relative to its asset class, benchmark index, and/or competing mutual funds you can hold. So it was fired just for that.
Please note that if a mutual fund has changed, and is not the current recommendation, the old one will not be monitored anymore (so you're on your own if you keep it). So first you want to read the text in the cell comment, and in the delivery e-mail to ensure the switch has the word "Replace" in it.
You can always question the mutual fund switch, and if you don't like the answer you get, just don't swap it out, it's totally up to you.
Also, if you bought support, and if you come across a mutual fund can't be bought via your custodian, then we'll find you another one.
Basically we'll help you maintain it in the Real World regardless of all of the usual roadblocks like these.
The low risk comes from extreme over-diversification. If you hold a Fee-Based Model, then you'll own a little tiny bit of over 1,000 securities of all different types and from all over the world.
One must remember that inflation needs to always be accounted for. So you'll most always lose money with most all bank investments, like CDs, because their after-tax returns are most always below inflation. So you need to actually "invest" to eliminate that huge risk.
Where do the "good returns" come from? Several places.
Asset allocation, done correctly, forces you to perform the primary law of investing - buy low and sell high.
This periodic forced trading ritual is called "rebalancing." For example, if there is a rally in Technology stocks the previous quarter, and the current month is a rebalancing month, then the percentage you're holding in the tech asset class will be more than recommended in the portfolio model allocation weighting.
For example, let's say that the model called for 2.5% in Technology, and now you own 5%.
You'll need to sell 2.5% (sell high) and use that money to buy what is currently down (buy low). At least one asset class will be below its Allocation Weight, so you'd just buy more of that (even if you "don't want to").
We know there are various trading charges associated with all of this that sometimes can't be avoided. You can make any rules you want to not spend too much money on this, but in general, if you're spending more than 2% to make a trade, then you're probably better off not making the trade (so if it costs you $25 to make a trade, then you would only want to make a trade if the amount of money your working with is $2,500 or more (2% of $2,500 is $50, because you have to make two trades, one buy and one sell, each costing $25).
Over time, you'll more than likely make more money by keeping up with the mutual fund selections, rebalancing, and paying the trading costs, than ignoring it.
The worst thing you can do is try to market time the mutual funds and/or asset classes. This is because the long-term performance of the mutual fund picks greatly outweighs the trading costs over time (as long as the trades don't cost you more than 2%).
Every quarter (January, April, July, and October) the asset allocator models are automatically rebalanced (on the returns.xlsx spreadsheets), so in order to keep up and reap the benefits of asset allocation, you'll need to rebalance.
Asset allocation mix rebalancing is explained in more detail below (the bottom part of the directions for advisors).
That's about it. The point is to make investment trades in your Real World investment portfolio to match the asset allocation model as closely as possible, keep up with the mutual fund changes, and then rebalance at least quarterly.
This is all you'd need to do to get performance that tracks well with the hypothetical and/or actual returns posted on the site.
Again, we want you to be happy with this service for a long time, so please contact us and ask questions. We've been through this in every way shape and form a thousand times, so you can get simple answers to even the most perplexing questions.
Important! If you're going to maintain all of the Asset Allocation Models as they are (meaning you didn't change anything anywhere), then only enter data (investment vehicles that fund the asset classes, and their return numbers) into the Conservative Fee-Based Model (the first / top model of the left sheet: Rows 6 - 21 of the Fee-Based Models sheet).
Next, starting in 2017, the Fee-Based Models are old news, and the Hybrid Models are the latest and greatest things. So unless you have a reason, then use the Hybrid Models from now on.
The other asset allocation models below just get their data from there. Some of the All Load, No-load, and minimum models get their market return numbers, and some investment vehicles, from there too. The ones that don't require that, you input them directly into those sheets.
To see which ones do; look at the formulas in the cells. If it's just a number, then the fund is not the same as what's used in the Fee-Based Model, so you'll have to input it directly. If it is, then you'll see a formula referencing the Fee-Based Models sheet. Don't overwrite this unless you know what you're doing!
You don't need to use the recommended mutual funds, or mutual funds at all, to fund the asset classes. You can use whatever you want to.
If you're going to be "totally with the program," then Steps 1 - 6 won't apply much - so you can usually start with Step 7.
If you're going to alter things, and or input fresh returns yourself, then Steps 1 - 6 apply.
Step 1) If you're going to use the Model Portfolios as is, then please read the directions above for investors now. They are pretty much the same for pros.
Not using it as it is, means you're going to change something (names of asset classes used, mutual funds used, allocation weights, the number of asset classes, input different returns based on different time frames, etc.).
If so, then reading the section above for investors will still help you understand what's going on.
If you're not going to use an asset allocation model as it is, you'll be overwriting the formulas in the models as you input your return numbers, and investment choices that fund the asset classes.
To be more specific, the formulas in columns O, and Q - T (which are hidden but not protected), and probably the return formulas in cells O30 / 31 / 33 / 34 / 36 / 37. To do that, please follow along using the Fee-Based Models sheet:
Enter the asset classes, and/or mutual funds, you want to use in the appropriate sections in columns B & E. Be sure to enter only bond funds, or individual bonds / fixed income subaccounts, in the bond section (rows 7 - 10), only true cash equivalents in the cash section (row 6), etc.
If you don't keep all of this straight (as delineated by the top and bottom border lines) then the numbers in the asset class breakdown sections will be off (e.g., rows 24 & 25).
Step 2) Although we think you're going to get the best investment returns with the asset class weights that came with the allocator models, you can easily modify them.
Just enter the Conservative Asset Allocation Weights you want to use in column O (cells O6 - O21). Make sure the total sums to 100% in cell O22. Do the same for the other four Risk Categories below (this is the only time you'd enter data in the other four Risk Categories, because the other four just get their data from the Conservative Model). The sheets to the right for the other four modes of investing just get their allocation weights from a hidden table in cells V7 - Y22. There is no protection, so you can unhide these columns are make you changes directly there.
Step 3) Enter the year-to-date rates of returns in column I to the right of that mutual fund (or whatever investment you choose to fund the asset classes). Remember to only input return data into rows 6 - 21 on the Conservative Model because the other four models just get their data from there. The only difference between the five models are the Asset Class Weightings in column O.
Step 4) Change the year-to-date returns for the six market indices in rows 27 and 28, making sure to use the same trading day that you got the other returns from. You'd only input these numbers in rows 27 & 28 of the Conservative Model on the Fee-Based Models sheet, and they'll automatically flow through to all of the other investor models.
Step 5) Enter the annualized one- and three-year return numbers in columns K and M. Remember not to input cumulative three-year returns. Input the average annualized three-year returns (the average over the last three years).
Step 6) After you've input all of the data into the models you'll be using, the only input left to do is the index returns for the graphs.
Go to the sheet: Return Graphs. Go to cell AR71 (press F5 then type in AR71, enter), and input the DJIA returns for the past year. Complete the remaining four index returns to the right. Repeat the process for the three-year average annualized returns starting in cell AR125. This will populate the chart data correctly.
Also, some graphs get data from columns BN - BT, so you may have to do something with those too.
Step 7) If you are charging fees in a managed account, edit the numbers in cells AA1 and AA3 to account for them. Input the amount of total annual fees, including trading charges, into cell AA3.
Then input how many weeks have passed already in the year into cell AA1. You do this because fees usually accrue weekly just like the cash returns. This way they will automatically be pro-rated throughout the year, instead of having them all deducted every month.
If you're not charging fees, then you can also go to the Fee-Based Models sheet and input 0 (zero) in cells AA1 and AA3 to make the Before and After Fee return numbers the same.
Then you can hide those rows and columns to minimize clutter.
For example, in the Conservative Model - rows 31, 34, & 37. Don't delete them, because then you'll have to edit formulas to make everything add up right in other places.
Step 8) Calculate the client's Investment Risk Tolerance Category using the Investment Fact Finder (or use what you're used to). This selects one of the five Risk Category Models.
Assume for the rest of the directions that the client scored Moderate.
Step 9) One of the first things to do is determine which mode of investing account the client will have - Fee-Based, all no-load mutual funds, all load mutual funds, all Index / ETF, CHIM, stocks / bonds / individual securities, whatever.
Step 10) Print out and show / give them only that one asset allocation model out of the 67 available (12 Investing Models times 5 Risk Tolerance Categories plus the three Conservative High-income Models and one Target Date Model).
Just giving this one page (Moderate Fee-Based Model) will avoid all of the questions and confusion about why they're in that Model Portfolio and not the others.
If you're still in show-and-tell mode, and you don't know their investment risk tolerance category yet, then it's okay to show them all five risk tolerance categories. But still, it's best to limit them to the five that belong to one of the model groups you'll know you'll be using (overall modes of investing - Fee-Based, all Load Mutual Funds, all No-load Mutual Funds, and Index / ETF).
Step 11) Then use the directions for investing consumers above (or continue reading below), to buy and sell in the clients' account to match the Asset Allocation Model as closely as possible.
Don't forget to rebalance quarterly by reading the section below!
Important! Please note that the 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 in the third quarter, you should call the fund family and ask when they expect this to happen.
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.
One should usually wait until after this distribution occurs before buying the mutual fund.
How to Rebalance at the Beginning of Every Quarter
See the Tip below (before the Target Date directions) before trading!
The rebalancing tool sheet is third from the left, titled, Comparison Models.
First delete all of the sample data in the green-shaded areas: Range C7 - G22 and B3 & B4.
This comparison portfolio is just a name for your actual current portfolio, or the clients' actual current portfolio.
You're comparing what is not in the model (the actual portfolio) to one of the Model Portfolios.
This sheet is used to input an investment portfolio to compare with the 23 models. These are the five models each for Fee-Based, No-Load, All-Load, ETF, then the Conservative High-Income Models. The Index Model was deleted because of lack of interest since ETFs came out.
Once you input the current portfolio (into range C7 - G22 or 14), it compares the asset allocation mix to any of the 22 models you choose (via drop-down menus in cells B3 & B4).
With a little tweaking, you can get this data to automatically populate from an online custodian download.
Please note that the Index Models here are really the Benchmark Index Models, not the Index Fund Models.
It then tells you how much, in both percentages and dollar amounts, to buy and sell in the actual portfolio to reach the target asset allocation mixes.
This is called rebalancing, and you're free to do it as often as you wish, as a lot of the great returns and low risk due to using asset allocation comes from this forced "selling high" and "buying low" process.
But at some point you're not going to want to pay all of the trading costs, so the industry standard is to rebalance the first day of every quarter. The returns posted on the site use quarterly rebalancing.
On this sheet, you can only input data into the green-shaded cells.
Note that all you'll need to do is input the amount of cash into cell D7, and it will tell you what to buy. You do not need to input any of the past returns, nor give the asset a name in column C. So if you're starting with nothing but cash, all you have to do is select a model via cells B3 & B4, then input the amount of cash, and it tells you what to do in column K.
Input the names of the investments (mutual funds) that are currently held into column C, to the right of the appropriate asset classes in column B (the asset class names in column B are referenced from the Conservative Fee-Based Model, so you'll need to make your changes there).
You can lump things together, but keep in mind that the important thing is to keep assets segregated by their correct asset class.
Next, input the year-to-date (YTD), last 12 months, and last three years annual average returns into columns E - G. The returns on the whole weighted portfolio will be automatically generated and displayed in cells H30 - H37.
Management fees that investment managers' charge to clients are input into cell AA1 and AA3 of the Fee-Based Models sheet. These are not mutual fund management, 12b-1, nor any other internal investment vehicle fees. Those are already accounted for; because the returns you input are after all of those types of internal fees have been deducted.
If you're using the portfolio models for your own investing, or you're a financial planner that doesn't charge direct investment management fees to clients, then input zero into these cells.
Then choose which of the 23 Model Portfolios you want to compare to, by choosing one of the five investment risk-tolerance categories in cell B3 using the drop-down menu. Then choose which one of the five portfolio models to use into cell B4.
These current asset allocation weights are then displayed in column H, and the difference between them and the actual current portfolio are displayed in columns J.
Then column K tells you exactly what to trade, with tickers.
It's recommended to just buy and sell using these dollar amounts ASAP, regardless of how you "feel" about it. Using any gut feel, market condition, intuition, or market timing, or anything else in regards to whether or not to make the trades, will just end up eroding your returns.
It's best to think of the asset allocation tools as an unemotional machine, and just do what it says to do. You may feel nervous about it in the short-term, but in a few weeks, you'll probably see that making the trades when you're were supposed to ended up making more money than following any other methodology.
About the only rational things to keep in mind when making the recommended trades, are the costs of the trades, and the tax implications.
Then look at the difference in returns between the selected Model Portfolio and the actual current portfolio in range I30 - J37.
There is also a matrix that shows the return differences between the actual current portfolio, and all 23 investing models, starting in cell C41.
All of the returns and differences are there, but it's a big table, which is why the feature of being able to manually select a benchmark portfolio, and easily see the results, is there too.
More About Rebalancing at the Beginning of Every Quarter
See the Tip below (before the Target Date directions) before rebalancing!
First, don't wait for your mutual fund pick updates to rebalance. Swapping funds and rebalancing are different things. It's common for people to think, "I know I have to rebalance, but I'll wait until I get my new mutual fund picks." Just don't do that. If it's past time to rebalance, just do it now.
Rebalancing is the technical asset allocation term that basically means this: At certain pre-defined intervals, you'll reshuffle asset class (mutual fund) amounts back to their original Asset Allocation Weights (column O).
Empirical research shows the most effective time frame is quarterly, and so that's what's used in the investment portfolio models. 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 model and you're starting with $100,000, and you scored Moderate risk temperament (so you're working from the Moderate model).
The allocation for large-cap growth is 15%. This does not change in response to market moves, so you're eliminating market timing (and emotions) by keeping the percentage of your total investments in the large-cap growth asset class at 15%.
This would be impractical to do monthly, and it doesn't need to stay at exactly 15% all of the time, so it's only done at the beginning of every new quarter (or when the model's 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 mutual fund by $5,000.
Since the portfolio is still $100,000, then you have to also be underweighted in other asset classes. So you 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 normal, 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.
This forces you to sell some of the asset classes off that have 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 some people, it may be impractical to buy and sell mutual funds with these small amounts. But since models are never Real World, the software (the Linked Returns spreadsheets you get that calculate the actual returns) mechanically rebalances at the end of every quarter, even if only a few bucks moves around.
Again, models are rarely Real World, which is why they're called "Models." The point is that even though there are numerous practical limitations, it's still one of the best ways to manage money - if you're a DIY investor or a professional money manager.
So if you're using the model allocations, you very much need to pay attention to rebalancing to get most of the benefits of asset allocation.
The Big Tip: Since dozens of thousands of investors and money managers are rebalancing at the same time (sometimes it's just done via computer program trading), 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 people are selling things that went up, you'll 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.
So the best time to do it is as soon as you get your updates in December, February, April, September, and are making mutual fund replacements anyway.
Directions for using the Target-Date Models
Go to Target-Date Input sheet.
Use the menu choices in all six input cells from C4 to C9 to select your life factors.
Now choose the funding vehicles for all 15 asset classes using cells C13 - G27. Input "y" or "Y" into each cell under the type of funding you want. For example, if you're a Fee-Based manager, then you'd not do anything as the default is all Fee-Based funds.
Individual investors would change all of the "y's to "n" (or delete them) everywhere but the under the No-load Picks column.
Yes, you can choose different things in any asset class.
But you will get the red error message in cell B10 if you chose more than, or less than 15 "y"s. Just play with it until you get what you want. The error will go away when you're done.
The results are on the sheet to the right, Target Date Models.
Sorry but due to the intense programming here, this is sheet is protected. Excel printing tips are here.
Rebalance quarterly, then go back to the input sheet and redo things if needed annually.
There are several types of returns listed, so this section helps explain them. If you still don't understand, then please e-mail with an explanation of what you don't understand, and things will be edited until everyone gets it.
Around the 10th of every month, the Mutual Fund Picks, Models, and the Portfolio Statistics sheet of the Comprehensive Asset Allocation software are all updated. Updates are sent to subscribers, then new returns are posted on the site the next day or so.
Model and Rate of Return Terms Explained
Hypothetical Returns: Returns shown on the spreadsheet "Toolsformoney Models for Month Year.xlsx" (that you'll get when you buy them), the Model portfolio's "demo," and those shown on the first row of the table on the Main asset allocation tutorial page, are all "hypothetical."
All this means is that the returns shown are just using the past returns of the current mutual fund picks. So accounting for past trades / holdings / fees / rebalancings are not accounted for. This is just the industry's "legal term" used to describe something that's "not real."
As you can see by comparing the first and second row of the table on the Main asset allocation page, hypothetical returns are usually much higher in shorter time frames (less than five years) than actual returns.
This is because fresh investment picks usually have better recent performance (this is one reason why they were picked). The investment selections that were replaced long ago are no longer there, so their performance is not account for.
Most advisors using their own models are usually showing these kinds of hypothetical returns, so judging performance using these numbers can be VERY misleading.
The reason they do this is not because they want to show higher returns to prospects than they've really been getting, but because they're not using expensive portfolio management software that calculates returns accounting for past trades / fees / rebalancings. Then also because it's just too much arcane and arduous work to calculate actual returns manually, like we do. There's just no other way to pragmatically do it.
There's nothing wrong with using hypothetical returns. Because of the price tag of portfolio management software, and the dozens of hours of tedious work needed to calculate actual linked returns manually, the vast majority of investment managers use them.
So BD / Finra Compliance people allow this, as long as all of the disclaimers are there.
It took several month's work and hundreds of dollars getting our models approved for use by a BD, Finra, and the SEC in 1999. After that, they sailed through the next BD's compliance people without problems in 2001.
You'll just need to be aware that when an advisor shows you their model returns, what their clients' actually got will most always be much less than that, and so will you if you hire them.
Hypothetical Returns Before and After Fees (AKA Hypo): When you see "fees" in all of this, it means investment management fees that a professional advisor would charge their clients. This is not an extra charge we as the model providers charge you for doing it yourself.
Individual investors using these models for investing their own money do not pay these fees, so you can ignore them all (delete or input 0 into these cells).
Internal mutual fund fees (management and 12b-1) are already accounted for in the returns, so no adjustment is done for them (mutual fund returns are always after all internal mutual fund fees and expenses). So when you see fees here, these are not referring to internal mutual fund fees.
The average fee is an advisor would charge their client is ~1%.
When we post after-fee returns, it's most always with a 1% advisor fee, then we add a 0.1% fee to account for average trading costs (ticket charges). So we'll most always subtract 1.1% in fees. In some places, these are pro-rated to account for how many weeks have went by so far in the current year.
After-fee returns are posted (and there is a place to input them into the models) because it gives advisors a way to compare to other money management systems, and to show clients before and after fee returns.
Advisors know what this is all about, and individual DIY investors can just input 0% and ignore it all.
So when hypo returns are shown after fees on the site, this means that if the model got 10% and the fee was 1.1%, then the Last 12 Months (and/or Last Three Years) return was really 11.1% (for a DIY investor or advisor that doesn't charge fees).
This 1.1% fee is also prorated weekly (divided by 52) and subtracted from the Year-to-date returns. So 0.55% was subtracted from the model's YTD returns if six months went by.
Everything on the spreadsheet Toolsformoney Models for Month.xlsx are hypo returns. The only other places that the above hypothetical text applies to, are the portfolio returns shown on the first row of the table here, then everywhere on the model demo. There are no actual returns shown on the spreadsheet Toolsformoney Models for Month.xlsx.
Actual Returns: Investment returns accounting for all past mutual funds held (which are not being used anymore and are not now in the models), quarterly rebalancings, and assumed average advisor fees and trading costs, are typically called "actual returns."
They are also called "linked returns," because the returns of the old investment selections are manually linked (AKA chained) to the returns of the new current investment picks.
In hypo returns, there is no linkage, as the new fund is just assumed to have been held all along, when the old pick could have held for a decade and the new one for a day.
This linkage makes the past returns as realistic as possible, and much more accurate than hypothetical returns. So these are the most important numbers to use when evaluating.
Linking returns uses the "Time Weighted" methodology, and is the standard used in accounting for past trades in calculating historical investment performance.
For example, if ABC Fund had decent returns for five years, then started getting dismal returns, it would be replaced by XYZ Fund - the current hot fund. So just by replacing that one fund, overall hypo model returns can easily be a third higher. These higher returns are then shown to potential investors, even though ABC Fund was used up until the last month.
So all one needs to do is replace one fund, then use the historical returns of the new fund, and their track record has been substantially "faked" to make the manager look much better than they actually are. So the new investor would think they bought into a manager that was getting an average of 10% over the last five years, when in really they didn't hold XYZ until last month, and so the results he got from ABC would have resulted in only a 7% average return.
So as you can see, this is very misleading and can distort the truth substantially. Always ask to see actual returns in addition to hypo. Just ask, "Are these returns adjusted for past trades, your fees, trading costs, and rebalancings, or is what's shown just the past returns of the current holdings?" More than likely, they will be the latter.
It takes a lot of care and work to maintain actual returns, so that's why it's rare to see them posted from an investment advisor. Actual returns will most always be much lower than hypothetical, so the advisor has little incentive to calculate them accurately (also because their BD and Finra will let them use hypothetical - in other words, it's a completely legal fail.
The vast majority of posted investment returns are hypothetical because investors are usually clueless about this very important distinction, and thus don't demand it.
Try to avoid money managers using hypothetical models if possible, as the differences can be enormous (sometimes almost double).
How we account for actual returns in our models:
The actual returns are from inception (1 January 1999) showing the returns as if you invested on the first trading day of 1999, then made no more deposits nor withdrawals, paid no taxes, automatically reinvested all capital gains and dividends, rebalanced on the first trading day of every new quarter and when allocation weights changed, and switched all of the funds on the first trading day after the switch was announced.
This never happens, and can't, so even actual returns are somewhat hypothetical. Even so, the grand distortion, lack of accounting for past holdings is removed; making things as good as they're going to get. The second grand distortion is not accounting for rebalancings.
Our actual returns are generated using these three spreadsheets that you'll get when you buy the models::
These are all locked up, but transparent, meaning you can see the formulas so you can see exactly what's going on, so you can do an audit yourself. But you won't be able to change anything (you can only change the amount of assumed fees in the Fee-Based Returns spreadsheet, by going to the far left input sheet). You can't because the sheet's protection requires a password, which is not given out.
The three webpages which show actual returns posted are here:
Table of returns assuming you charge your clients 0.5% annual management fees (low)
Table of returns assuming you charge your clients 1.0% annual management fees (average)
Table of returns assuming you charge your clients 1.5% annual management fees (high)
More about Return Calculations
The graph at the very bottom of the Index Model (also shown on the demo) shows how the Fee-Based Models have done when properly compared to their benchmarks. The yellow bars show the difference between returns of the models vs. the same model funded with indices.
We get Morningstar data the first work day of the month and then typically do the work of screening funds and updating models the next day (and updates are sent out the next day).
So when a fund changes, it wouldn't be "right" to assume that the fund was held at the beginning of the month (because it wasn't selected until a few days later). So the previous fund's monthly return is used in the calculations (and is shown as gray color in the linking spreadsheet).
So if you see a fund pick that's new and gray, then the previous fund's return was used in the calculations here (not the brand new fund's return). This is where the linking of returns happens.
So the first few days of the May, you'll get the Models updated with April data. The new fund picks are assumed to have happened at the beginning of May (end of April), which is already a few days late. So yes, the data you're getting assumes you made the trades at the end of April, but you didn't get your update until a few days into May, so you are correct in thinking that everything is delayed two to three days.
Actual Returns Before and After Fees: When investment manager fees are deducted from the model in actual mode, it's different than hypo mode. In hypo mode, they're just subtracted from the gross return (10% - 1% = 9%). The whole thing is a total fudge anyway in hypo mode.
Fees are deducted in actual mode automatically on the Linked Returns spreadsheet (only on the Fee-Based Models. No fees are deduced from the Load nor No-load models because this would be rare).
You'd just go to the far left sheet and input them, and everything is automatically adjusted.
Fees are accounted for in a way as close to reality as possible (billed in advance quarterly). At the beginning of every quarter, annual fees are divided by four, then applied to the gross account balance. So if the fee is 1%, and the account is worth $100,000, then $250 is the quarterly fee (1% divided by four). This $250 is deducted from the cash / money market account (row 35), before returns are calculated, so this debit is taken into account. If there is no cash, like in the Aggressive Model, then it's assumed to be taken from the safest bond mutual fund used in the model.
There are no mutual funds with redemption fees (B-shares) used in any of the model allocations, so there are no selling commissions assumed here.
The 0.1% in extra fees deducted account for average trading ticket charges.
While we're on the topic of redemption fees, this is a good point to bring up the topic of custodians dinging investors ticket charges for selling funds within 90 days of buying them. When this happens, just use some colorful metaphors and then wait until this period is over (so you won't get dinged) and then make the change using the current pick (which may be different again).. Realize what's going on here - the custodian is letting you buy these funds without transaction fees (ticket charges), so they're giving you a huge freebie. The deal is if you sell it before 90 days, they're just taking this freebie away from you. So there's nothing "bad" about this.
As you can probably see, we're doing everything possible to have the posted returns be as accurate as possible.
You can see the hypo returns of everything on one page on the models (after you buy them) on the sheet tab All Returns at a Glance. This sheet is also pasted into the model's "demo," along with graphs showing performance.
The only places you'll see hypothetical returns are on the spreadsheet "Models for Month Year.xlsx" (that you get when you buy them), the Model portfolio's "demo," and those shown on the first row of the table on the main asset allocation page. Everything else is actual (and they're labeled in the fine print).
All of the usual, and useless, portfolio statistics (Sharpe, Beta, Alpha, etc.) for the Moderate Fee-Based Model are maintained both on the far right sheets of the Comprehensive Asset Allocation software demo. These are also shown as screen prints in the Model's demo.
To see the latest actual returns on the spreadsheets, go to the bottom of the last month's area in the latest year's sheet tab in one the three Linked Model Returns.xlsx spreadsheets.
These three files don't do anything for your clients and are just FYI. This spreadsheet is protected, but the cell formulas are not hidden, so you can see and audit the formulas and returns.
This is also where the history of all of the fund changes are.
Input your hypothetical asset management fees into the far-left sheet of the Fee-Based spreadsheet.
Actual returns of the five Fee-Based Models, without fees, with market returns are on the table mid-page are here.
Actual returns of the five Fee-Based Models, with fees are here.
Actual and hypothetical returns of just the Aggressive Fee-Based Model, compared to the same Index Model, the markets, our American Funds model, are on the table mid-page here.
Hypothetical returns of each asset classes' current mutual fund pick, compared to its benchmark index are on the table mid-page here.
Hypothetical returns (not accounting for past trades) of all of the models, with fees deducted, are on the model demo Word docx here.
Actual returns (accounting for past trades) for the no-load models are here.
Actual returns (accounting for past trades) for the all-load models are here.
Please note that returns may change slightly in Jan and Feb. This is because the returns Morningstar post change slightly at the end of the year. We have no idea why.
So at the beginning of every year, we audit and re-input all of the monthly returns in all three Linked Returns spreadsheets. About 5% of these numbers change, but most of the changes are in the second decimal place.
About Switching Mutual Funds When They're Changed on the Mutual Fund Picks Spreadsheet
First of all, one of the screening criteria is to weed out mutual funds that have back-end redemption fees, (B-shares), so there won't be any back-end loads or fees to worry about when you sell mutual funds. The only thing to be wary of is capital gains taxes. See the paragraph above if you run into any early redemption fees.
It's impossible to give exact advice here 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.
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. They are given in the cell, and in the comment sections of the cell, and display when you hover your mouse cursor over them. More detailed text is in the e-mail that your update came in.
If you're a Fee-Based advisor, and can't get a fund in the Fee-Based list, then first try this: Fee-Based picks come from either the load or no-load list. So if the Fee-Based fund you can't get comes from the load list, then just use the no-load pick instead, and vice versa (please do this first before asking for a replacement). If that doesn't' work, then see if there's an ETF used in that asset class from the Hybrid Models. If so, and you can get it, then use that.
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 sometimes 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 (because we can't recommend it anymore because new investors can't buy it), so if it goes bad in the future, you won't be notified.
If the mutual fund was sold because its style or asset class drifted or changed, a new manager, then it's best to change the mutual fund.
If the mutual fund changed because of "recent performance," then you'd most always want to make the switch, as something "bad" happened to the mutual fund over the last month / quarter or so, and experience has proved that it won't magically fix itself, so the new fund most always does better going forward. However, the difference in performance may be so small that keeping the old fund is the most practical thing to do. The problem there is that the old fund is not monitored anymore going forward.
Mutual fund families (the overall mutual fund family 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. Fortunately, Morningstar's screening filter will catch most of these shenanigans. Mutual funds that use deceptive marketing tricks like this are most always screened out of the results.
The best reason to make a switch is when the trigger is performance related. But 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 people, 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 giving your 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.
• 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 switch mutual funds. 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.
• If you like to track one of the portfolio models closely, then making the switch is more important. If you're doing the best you can to match the investing models so the posted returns will be as close to your Real World as possible, then you should make the trades as soon as possible. On the other hand, if the model is already contaminated by using other investments, then switching loses some its relevancy.
• 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 are not bothering us (unless you call when all you bought was e-mail support), we want to hear from you and help you manage your models as well as possible.
About the Market Timing models
On the sheet Market Timed Models, there is some market timing going on.
These models are the same as the Fee-Based, No-load, and Load models when it comes to asset classes. But it uses the ETF picks. Then all of the allocation weights are subject to change monthly. Some asset classes may be changed too. For example, Biotech was replaced with an asset class that will magnify a sector for extra punch (or loss).
Other than that, just use these directions for that sheet as well.
About the only difference if you want to follow this investing model is that you'll have to "rebalance" monthly to realize these returns.
If you're here looking for the directions on how to use models to allocate 401k plans, then this text was removed and is part of the Money eBook now. If you're a model customer, then just ask and it will be sent to you for free.
Old generic advice about the Mechanics of money management and trading.
|Financial Planning Software Modules For Sale
(are listed below)
Financial Planning Software that's Fully-Integrated
Goals-Only "Financial Planning Software"
Retirement Planning Software Menu: Something for Everyone
Our Unique Financial Services
Mr. Market Timer's Unique Market-neutral Stock Market Timing Services
Miscellaneous Pages of Interest
© Copyright 1997 - 2017 Tools For Money, All Rights Reserved