Asset Allocation and Portfolio Management

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 https://www.portfoliovisualizer.com/monte-carlo-simulation#analysisResults 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:
[https://docs.google.com/spreadsheets/d/1WeYiipm3D6WHBUOcR64o1DS6eTyLXXLwFXj3oAYHtF0/edit?usp=sharing]

Not something I need, but wow, looks very nice, I’m sure many will be well served by it!

thanks. I am new to this board, but it seems like most people use Tiller for budgeting. I think it has a lot to offer for investing and financial planning. Working on extending this to a cash flow model for retirement planning.

1 Like

Thanks for sharing this @bill! This is exciting!

@bill
This. Is. Amazing.
Thanks so much for building it and sharing it.

Wow! Very intense. So much for the lazy portfolios advocated by the Bogleheads over at Vanguard, right? One thing is for sure, you cannot beat those tools at Portfolio Visualizer.

Your Monte Carlo analysis is spot on. To me the more important question is what should the portfolio look like in the first place from a risk vs return perspective using finance concepts like Sharp ratio, Efficient Frontier, and Black-Litterman, all of which are at Portfolio Visualizer. Any plan to incorporate this into your tool? I think you find the optimal portfolio first, then you run Monte Carlo to see how the portfolio holds up in the future using historical or forecasted returns given the demands that will be placed on the portfolio.

Regarding your comment about retirement planning, check out NewRetirement.com as Steve and his team are doing great things over there.

Good luck.

Thanks Steve I will check out NewRetirement.com immediately. I did add some functionality for retirement by building off of Tiller’s “Cash Flow Forecast” sheet. I added RMD’s for personal and Inherited IRA’s as well as the ability to assess tax impacts of Roth conversions. It is not in a format to readily share but if there is interest I can upgrade it. I am anxious to see what Tiller is going to offer in their new premium service for investment holdings.

I was not familiar with all of the other tools you mentioned. But I see that Portfolio Visualizer provides free access to these tools. I will definitely check them out. Thanks for the heads up!!

Thanks Brad. If you decide to use any of it and need help to integrate into your sheets just let me know.

1 Like

Just checked out NewRetirement.com. It looks great. I will at least try out the 14 day free trial.

1 Like

This looks fabulous. I am in need of an easier way to track asset allocation and retirement planning. Hopefully, over the next few weeks, I can determine how to integrate your sheets into my system and my investment plans will never be the same! Thanks for all your hard work.

That’s great Mark. If you have any trouble let me know. I am sure we can figure out how to get in direct contact. One issue I had was I needed to repoint the reference on the Tiller Net Worth Sheet. Once I realized what happen it was easy.

NewRetirement has a new Roth Conversion tool that is in beta. Let me know what you think. Blake

You mention new premium service. What is this about? I have not heard of this.

Money matters because life matters more.
:moneybag:Interested in investment holdings data in your spreadsheet?

Join our Beta for free early access to this new premium feature. Investment holdings data will power up your spreadsheet with more insights about your investment portfolio allowing you to truly track everything in one place.

While in Beta you can access this feature at no additional cost.

Join the waitlist

Thanks, Just joined waitlist. I am real curious. Blake

Pertty amazing work here. Thumbs up on focus on allcation and models and Portfolio Visualizer!!

I’m trhing to do this too and am new to Tiller. I am a little disappointed in its capabilities beyond budgeting and am specifically tryign to just get positions from Schwab or from my brokers. I am trying to automate what you show as your current allocation vs. target allocations.

As I’ve got some Python skills, Ive also considered trying to do this with some of the piece parts and open API platforms such as Plaid, Integromat, Zapier.

Big fintech opportunity to buiild tools for the DIY buy and hold investor using simple strategic allocation models and goals. You are proving it!

Lee

Thanks Lee. It seems like Tillers initial focus was on budgeting rather than investing. I am like you,my real interest is investing. It seems like they may be starting to address investors like us. What I like about Tiller is the ability to customize. It seems like you have some broader skills then I have, so good luck.

1 Like

Thank you this is exactly what I am looking for! I can’t get it to work though… I have assigned asset type for each of my account, but on column S it’s showing red errors. What am I doing wrong?


I am confused. What are you trying to do here? Is this the Accounts tab? If so, haven’t you disrupted/deleted some of the cells containing formulae which makes this all work? Blake

Yes this is in the Accounts tab. NVM i figured out. Looks like I need to enter group on column C in order to get column S to display right.