Asset Allocation Model

Last Wednesday I posted "Measuring Asset Allocation Across Your Entire Portfolio" discussing the importance of periodically reviewing your asset allocation across all your holdings. It was a significant project that I undertook to measure my asset allocation over all my investment holdings using three different measures (origin, capitalization and sector).

As with most investment analytical work, I added a new tab in one of my two massive spreadsheets and set it up so that I can update it by cutting-and-pasting information from Morningstar into my spreadsheet. From start to finish, the whole updating process can be completed in about 20 minutes.

After that post, I received a comment requesting that I make this spreadsheet available. So once again I pulled out my surgical knife and extracted a template that can be used as a starting point for reviewing your asset allocation across all your holdings. It is linked on the tools page as D4L-Asset-Allocaton.xls.


As with all the models I post, I assume that you have a working knowledge of Excel. Once you open the above-linked model you will note that I have stripped it down to Income ETFs, Income Stocks and Other. This will provide sufficient examples for you to expand the model to meet your specific needs considering the various types of available investments. As you will note, for each type of investments there is a $ and a % column. The dollars are accumulated then the percentages are calculated based on the dollars. You will also note that above the column headings you can expand and collapse the dollar columns either one at a time by press [+] or [-] or all with them by pressing the [1] or [2] buttons. To begin with, press the [2] button to expand all the columns.


Income Stocks
Let's look at the income stocks first since they are the easiest to work with. This section begins at cell A88. Column A is the ticker symbol; col. B is the capitalization Large, Mid or Small; col. C is the country of origin; col. D is the current market value. As noted in last week's article, I use Morningstar for all my investments to ensure classification consistency. Let's take a look at ACAS as an example.

The above link to ACAS will take you to the company's snapshot. Look at the Key Stats next to the performance graph. The line Morningstar Style Box will show the stock capitalization. In the case of ACAS it is listed as Mid Value so it is a "Mid-Cap" stock as entered in cell B90. It is important to use the capitalization terms exactly as I have them in the spreadsheet (Small-Cap, Mid-Cap and Large-Cap) since I use a sumif() formula to summarize the various capitalizations. This calculation occurs around cell G114.


Back to Moriningstar (still in Key Stats), move down a couple of lines to Sector. Here you will see ACAS is listed as "Financial Services", which is entered into cell D90. Again it is important to use the tags (e.g. Financial Services) just as they are in the model since I once again use a sumif() formula to add them up. This calculation occurs around cell H90.


In col. C is the country of origin. This can be determined by clocking on the Company Profile tab above the performance graph. For ACAS, Bethesda, MD would mean it is a "U.S." company. This is calculated at cell N89. For those outside the U.S. you can change the formula in column N to list your country as "domestic".


To view another company go to the top left and enter a new symbol in the Quote box. Individual stocks held in your IRA or other investment type would work identical to what was described above.


Mutual Funds and ETFs
Now let's look at mutual funds and ETFs. This is where the potential problem could have been. Since these type of investments are made up of multiple stocks across various sectors, this is where Morningstar really steps up and helps us through the process. Let's look at the ETF SDY as an example.


Note I use Internet Explorer instead of FireFox for the next section since IE does a much better job interacting with Excel via copying and pasting.


Go down below the Premium Features section to the Portfolio Analysis section. To the right you will see Sector Breakdown. Starting with the number to the right of "Utilities", use your mouse to highlight the table up through the icon to the left of "Information". Right click, copy, then paste into Excel at cell A134. Beginning at cell B127 you can see where the value associated with SDY is allocated across the various sectors. Repeat the process for your various other investments moving to the right. Everything is totaled up in beginning in cell Q157. A couple of items of note, AOD is a relatively new fund and when I first put this together it did not have a sector breakdown so I listed it as unclassified. Also, VNQ (Vanguard's Real Estate REIT) did not have a sector box so I set up a separate sector for Real Estate.

The capitalization and origin classifications work identical to those in the Income Stock section above. Obviously this is not a plug-and-play template. It will take some work on your part to customize it to meet your specific needs.

I hope you find this template as useful as I have.



Related Articles

• Time is My Friend
• Share Buybacks - Do they really help?
• Fishing in the Bathtub
• When Is A Lot of Cash A Bad Thing?
• Dynamic Dividend Investing