Tuesday, March 13, 2012

Portfolio Rebalancing Based on Expected Profit and Trading Costs

Please see the updated post on this subject that computes better thresholds in certain cases.

The idea of rebalancing a portfolio to maintain target asset allocation percentages is simple in theory, but tricky in practice. It is not obvious how far asset class percentages should be away from their targets before it makes sense to rebalance. I have developed a scheme that I use myself that I fully automated in a spreadsheet. Instead of obsessing over my portfolio’s returns, I can just check whether one cell is red to indicate that I need to rebalance.

Investors should use any new savings or withdrawals they make as opportunities to rebalance by buying low asset classes or selling high ones. However, as a portfolio grows, rebalancing with new savings and withdrawals is unlikely to be enough to maintain balance when asset classes have big swings.

Common advice is to rebalance a portfolio on a fixed schedule, such as yearly. This has the advantage of allowing investors to avoid obsessing over their portfolios all the time, but has the disadvantage of missing potentially profitable opportunities to rebalance. Computing thresholds automatically in a spreadsheet permits me to check one cell in the spreadsheet for a glowing red “rebalance” once or twice per week without having to look at anything else. This gives me the advantages of threshold rebalancing without the disadvantages.

When choosing rebalancing thresholds, most experts advise investors to either use percentage thresholds or dollar amount thresholds. For example, you might rebalance whenever you’re off target by more than 5%, or alternatively by more than $2000. However, these approaches don’t work for all portfolio sizes. Percentage thresholds lead to pointless rebalancing in small portfolios, and dollar amount thresholds lead to hourly trading in very large portfolios. We need something between these two approaches.

Computing Thresholds

When asset class A rises relative to asset class B, and then A drops back down again to the original level relative to B, rebalancing produces a profit over just holding. I compute rebalancing thresholds based on the idea that the expected profit from rebalancing should be 20 times the ETF trading costs.

All the mathematical details of how I compute rebalancing thresholds are in a 6-page paper “Portfolio Rebalancing Strategy”. I’ll just give the results here.

The spreadsheet starts by computing the following quantities for each ETF:

m – Current portfolio total value times the target allocation percentage. This is the target dollar amount for this ETF.
s – Bid-ask spread divided by the ETF share price.

Other parameters are

c – Trading commission.
f – Desired ratio of trading costs to expected profits. I use 0.05 so that the expected profits from rebalancing are 20 times the trading costs.

The dollar amount threshold for rebalancing then works out to the following formula which may seem a little intimidating, but it only has to go into a spreadsheet once.

t = [m/(2f)] * [s + sqrt(s*s + 8*f*c/m)].

So, it makes sense to rebalance an asset class if its dollar level is below m-t or above m+t. As long as there are at least two asset classes far enough out of balance (with at least one too high and at least one too low), it makes sense to rebalance.

Currency Conversion

When holding some ETFs denominated in Canadian dollars and others in U.S. dollars, rebalancing may involve currency conversion, which can be expensive. To deal with this, I actually think of the Canadian and U.S. portfolios as separate portfolios with their own asset allocations. Then I think of the sub-portfolios as asset classes in a meta-portfolio that has its own asset allocation.

So, I do the same calculations on the meta-portfolio using target allocation to each currency. An important difference is that the meta-portfolio has higher trading costs than the sub-portfolios have. When using the idea of converting currency by buying and selling a stock that trades in both Canadian and U.S. dollars, rebalancing requires at least twice as many trades, and the total spreads have to include the spreads on trading the inter-listed stock.

So, instead of c=$10 for regular rebalancing, I use c=$20 for currency rebalancing, and instead of a value close to s=0.0005 (2-cent spread on a $40 ETF), I use s=0.002 for rebalancing with the ETF DLR because the spreads are about 2 cents on a DLR price of about $10. The result is that rebalancing between currencies happens less often than rebalancing in the sub-portfolios.


It took me a while to work all this out, but now I don’t have to pay much attention to my portfolio. When I have some money to add, I buy the asset class that my spreadsheet says is furthest below its allocation, and I periodically check one of the cells for the word “rebalance” glowing in red. Only when I see red do I have to investigate further and make some rebalancing trades.

My own spreadsheet is too specific to my situation, but if there is interest I may put together a generic spreadsheet that captures the ideas here.


  1. Definitely interested in seeing a generic spreadsheet that I can mooch off of ;)

  2. @Erick: I can't promise anything too soon, but I'll add it to my to-do list.

  3. This looks real nice. A generic spreadsheet would be much appreciated and I understand about to-do lists so no pressure from me.

  4. I would definitely welcome a generic spreadsheet!

  5. Also interested in a generic spreadsheet. Thanks for the post and the linked article.

  6. Read your paper -- nice thinking in translating rule of thumb into mathematical thresh-hold trigger. I'm relatively unsophisticated in googledocs spreadsheet use. I have been manually looking up ETF prices for the day and plugging them in manually into my spreadsheet. What is the exact command to use to populate the ETF price fields automatically in real time? As I understand it, you also populate the latest bid-ask spreads into your cells too? What's the command for that?

    1. @sclim: Thanks. I use GoogleFinance(A1,"price"), where A1 is a cell reference that contains the ETF's ticker. I don't bother to try to get spreads automatically. I just enter them manually once and on those rare occasions when the spreadsheet says to rebalance, I confirm that the current spreads are the same.

    2. Thanks. This is brilliant!

      I'm trying to display VBR which is sold on the AMEX exchange. I have used VBR.IV which is its official listing, I think, and VBR.AMEX; neither works. Do you know if GoogleFinance works for US stock Exchanges?

  7. Never mind thanks -- I figured it out -- Just enter VBR, GoogleFinance seems to know where to find the appropriate market and Stock Exchange!

    1. A further cry for help here -- Google Finance works great most of the time, but seems to get hung up with Canadian ETFs that share an identical name with US ETFs (such as FXM and XEC). To the best of my searching, the fix should be to add the prefix "TSE:" However I tried this with TSE:FXM and TSE:XEC and I keep on getting error messages in my Google spreadsheets. What's the real fix?

    2. @sclim: Some tickers seem to work with .to appended (e.g., xic.to), but not others. A different solution is to use the =ImportXML("http://...","//span") function to access data from a web page that has the price you need and pick out the cell you want. Unfortunately, this breaks frequently as web page formatting changes.