What is the goal of your workflow? What problem does it solve, or how does it help you?
I use Asset Allocation as an investment strategy to manage my portfolio. While Tiller does a great job of automatically updating my account balances it does not provide adequate detail to manage my investments using this methodology. I developed a workflow that allows me to easily allocate my investments within each of my accounts into up to 38 different buckets such as US Large Cap, US Small Cap, European Stocks, REITs, Corporate Bonds etc.
The asset value within each bucket is automatically updated allowing me to always be aware of the current allocations. Based on historical returns for each asset class the projected annual return and growth over the next 20 years is calculated. After assessing my current allocation I can select an alternate target allocation and compare the performance of this new portfolio with my current portfolio. The defined buckets are compatible with a free online site Monte Carlo Simulation that allows me to conduct a Monte Carlo Analysis of my portfolios to assess risk.
How did you come up with the idea for your workflow?
I have utilized Asset Allocation for a long time. Once or twice a year I would compile the necessary information to determine my current allocation and then rebalance. Over the years I have been contacted by many financial advisers who charge annual fees of 0.5 – 1% of your portfolio to essentially do the same thing. They determine your current allocation, recommend an improved allocation and based on historical returns project the performance of both portfolios. They also generally conduct a Monte Carlo Simulation to assess risk and the likelihood of reaching your financial goals. This is a useful part of the Asset Allocation Strategy because it gives you a sense of the potential downside of your decisions.
Please describe your workflow. What are the sheets? Does it use any custom scripts or formulas?
I modified a number of Tiller’s standard sheets and added several additional ones that I will describe below. I did not use any scripts but did design the sheets so they can easily be integrated into an existing spreadsheet and repair any broken links as a result of copying the sheets
Accounts: An “Asset Type” column is added to the Accounts sheet. This column has a drop down menu that allows the selection among 39 Asset Types. One Asset Type, “Multi Asset Type” is used to indicate that assets within an account and/or mutual fund are made up of multiple asset types. Provisions are made to allocate these funds into the appropriate buckets.
Asset Type: In this new sheet, available Asset Types and their annual returns are listed. I indicate which types I want to include as potential investment options in my current or alternate portfolios. Columns 3 and 4 show a summary of how my current portfolio is invested. Nominal and Real Annual returns were obtained from PortfolioVisualizer. The standard deviation is an indication of the asset’s volatility and integrated into PortfolioVisualizer for their Monte Carlo Simulation. The details of the annual returns are documented in “Detail Annual Returns”.
Brokerage_Mutual_Funds: This sheet is used to allocate the funds within accounts designated as “Multi Asset Type”. In this sheet select the Account (or Group) previously defined on the “Accounts” sheet from a drop down menu. Next in the orange area the details of assets in each account are entered. If it is a stock or mutual fund enter the Ticker Symbol, Name and # of Shares. If it is a mutual fund the Morning Star rating is reported. In the yellow area I assign how each line item is allocated by “Asset Type”. Only the # of shares need to be manually updated unless the asset does not have a recognizable ticker symbol then I need to also manually update the price.
Auto Asset Allocation: This sheets looks identical to the Tiller “Balance” sheet, but instead of being organized by “Group” it is organized by “Asset Type” assigned in the new “Accounts” sheet.
Rebalance Assets: This sheet shows the summary of investment by asset type and the current asset allocation (green arrow). Next a new asset allocation or “Goal” (blue arrow) is defined. The next column indicates how much money needs to be added or subtracted to obtain the goal allocation, followed by the total value of the asset after rebalancing.
Effect of Rebalancing: The contents of this sheet are automatically generated and show the changes in allocation and projected returns of the current and goal allocations. The allocations are represented in the charts below where similar assets are group to simplify the comparison.
Portfolios are rebalanced annually in the model. The results are shown in the plots below with and without the affects of inflation. In this case the goal allocation projects an increase in the nominal value of the new portfolio of $5,729.217 ($2,578,388 excluding inflation) over the current allocation. A concern is the potential increased risk of this reallocation and the potential downside. To assess these issues I use the “PortfolioVisualization” site referenced above to evaluate both portfolios.
On the “PortfolioVisualization” site enter asset allocations and starting portfolio value in the format shown below. “PortfolioVisualization” then runs a Monte Carlo Simulation. A summary of the results are shown below.
The 50th Percentile is most representative of the analysis produced by the Asset_Rebalance_Template. The template projects a value of $14,647,413 vs. $15,080,693 for the Monte Carlo Simulation. The simulation shows there is a 90% likelihood the portfolio’s value will be greater than $8,263,093 and a 10% chance it could exceed $25,000,000. The results of the simulation for nominal returns are shown graphically below. They are also available for Real Returns (Inflation adjusted). There is an 80% likelihood the value will lie between the blue and purple curves.
Anything else you’d like people to know?
If you are interested in integrating these sheets into your personal spreadsheet I can provide you detailed steps. But the general process is copy to existing sheet command. Highlight each sheet in the template and right click selecting “Copy to Existing Sheet”. This will create a duplicate sheet within your spreadsheet with “Copy of” appended to the name in the template.
Open each of the “Copy of ____” sheets. At the far right of each sheet you will see a familiar “Sheet References” section, in the green highlighted box select the “Copy of” version of the sheet in the drop down. This will heal the links between sheets. At the bottom of the figure below you can see the dropdown for the Brokerage_Mutual_Funds sheet is expanded and you can select either the original or the copy. In all cases select “The Copy”. Once this is complete you can remove the “Copy of” from the sheet name and repeat this process to link to the newly named sheet.
Is it ok for others to copy, use, and modify your workflow?
It is fine to copy and modify it any way you would like. If you have any problems contact me and I will try to help.
If you said yes above, please make a copy of your workflow and share the copy’s URL:
[Asset_Rebalance_Template_Rev4 - Google Sheets]
Interested in investment holdings data in your spreadsheet?
