#SmallTradesPortfolioREBALANCER

March 24, 2012

[Revised on 9/4/2013 by addition of a sample problem]

The Small Trades Portfolio Rebalancer is a clone of existing calculators (1-3)  that is uniquely designed to rebalance a portfolio of 10 or fewer holdings.  DOWNLOAD the Excel file program by clicking on rebalance_calculator, then work from left to right, top to bottom of the spreadsheet.  A sample problem is provided in the appendix.

Instructions

DATA ENTRY.  Two worksheets offer the option of entering every holding in the portfolio (i.e., REBALANCER price) or groupings of holdings(i.e., REBALANCER volume).  A grouping of holdings is made by entering the sums of asset values and asset volumes.  Both worksheets require 6 steps of data-entry.

  • Step 1 is optional, for use in timing the rebalance based on the “rebalance signal” published in the Portfolio Designer program.
  • Step 2 is a listing of the portfolio’s holdings.  Each holding is a unique investment referred to as an “asset”.
  • Step 3’s allocations were assigned at the inception of the portfolio.
  • Step 4’s Value and step 5’s Price (REBALANCER price worksheet) are current market data. “Today” refers to the day that the portfolio is rebalanced.
  • Step 5’s Volume (REBALANCER volume worksheet) is the number of asset units currently held in the portfolio.  This is typically the number of shares of stock, investment fund, or other exchange traded security.
  • Step 6 requires the desired portfolio value.  It can be: 1) the current value when the only goal is to rebalance assets, 2) a higher value when more money is being invested, or 3) a lower value when money is being withdrawn.
  • Both worksheets have 4 error messages that appear in yellow script when Steps 3-6 are incomplete.

RESULTS.  Both worksheets offer 5 results.

  1. The current portfolio value is the sum of all values entered in Step 4.  Note: If the current portfolio meets the calculator’s criteria for a rebalance, based on data from Step 1, a “Rebalance” notification will appear underneath the portfolio value.
  2. The percent error reflects how much each of the holdings deviated from their assigned allocation.
  3. The rebalanced asset value is a realignment of the holdings to their assigned allocation.
  4. The recommended action is “buy” or “sell”.  Caution: It’s impractical to take the recommended action on small volumes of assets when trading fees must be paid.
  5. The volume is the number of asset units to buy or sell when rebalancing the portfolio.

RECOMMENDATIONS.  You can download this program free of charge by clicking on this link, rebalance_calculator.  If the program inspires your investing to support the betterment of society, consider making a tax-deductible contribution to your favorite charity or my favorite charityA sample problem is appended to the references of this post.

Other calculators

There are rebalancing calculators available on the internet at no charge to the reader.  Here are a few of them:

Copyright © 2013 Douglas R. Knight

References

1.  Provident Planning, Personal Finance for Life in the Kingdom.  Free portfolio rebalancing calculator, by Paul Williams.  December 29, 2009.  ©2009-2011 Provident Planning Inc.  http://www.providentplan.com/1044/free-portfolio-rebalancing-calculator/

2.  Invest-it-Yourself.com.  Portfolio rebalancing.  Copyright ©2010 Invest-it-Yourself.com.  http://invest-it-yourself.com/rebalancing.php#

3.  Portfolio rebalance calculator, by Bob Beeman. May 25, 2007. ©2006 Bob Beeman.  www . bee-man.us  http://www.bee-man.us/finance/portfolio_rebalance.htm

4.  Invest-it-Yourself.com.  Portfolio rebalancing.  Copyright ©2010 Invest-it-Yourself.com.  http://invest-it-yourself.com/rebalancing.php#

Appendix: Sample problem

Suppose your allocation plan calls for 10% cash and 90% securities, with the securities split 40/60 between a bond fund and stock fund.  The first chart illustrates a hypothetical portfolio.  The rebalance signal was obtained by testing a set of historical data in the Portfolio_Designer program [no rebalance signal is required for rebalancing the portfolio according to schedule].  Notice that the bond and stock funds are equally valued at $3,333.33 rather than the planned 40/60 split, clearly indicating that the portfolio is unbalanced.

rebal1

In the second chart (below), Result 1 shows that today’s unbalanced portfolio value is $10,000.  The program automatically recommends a rebalance of the portfolio based on the Rebalance signal in step 1.   In step 6 the choices for a desired value  are between retaining today’s portfolio value, increasing the portfolio value by adding cash, or decreasing the portfolio value by removing cash.

rebal2

In the third chart (below), Result 2 illustrates the extent to which each holding deviates from the allocation plan [notice that the absolute values for 2 of 3 errors in result 2, –233.3% & -38.3%–, exceeded the Rebalance signal in step 1, –37%–, which prompted the recommendation to rebalance the portfolio in result 1].  Result 3 shows that if you choose to rebalance the portfolio at $10,000 with a 10% weighting for cash, the rebalanced portfolio will hold $1,000 of cash.  Likewise, the bond fund’s 36% weighting would rebalance the bond fund to $3,600 and the stock fund’s 54% weighting would rebalance the stock fund to $5,400.  Results 4 and 5 show the trading needed to rebalance the portfolio.

rebal3


Rebalancing an investment portfolio

March 23, 2012

[Updates:  10/21/2014, the inclusion of 2 useful references at the conclusion of this article.  9/4/2013, a new discussion of the rebalancing process and transfer of a sample problem to the #SmallTradesRebalancer ]

Introduction

Your investment portfolio is balanced when its assets are funded according to your allocation plan.  Suppose the plan assigns weightings of 0.6 (i.e., 60%) to stocks and 0.4 (i.e. 40%) to bonds.  The portfolio is balanced when for every $100 of portfolio value there is $60 of stock value and $40 of bond value.  Otherwise, the portfolio is out of balance and may need to be re-balanced.  There’s no universally accepted time or signal for rebalancing the portfolio.

There are a number of reasons why portfolios drift out of balance.  The commonest is daily fluctuation of market prices during trading hours.  Another is the purchase or sale of assets.  For whatever reason, an unchecked drift could defeat your plan for seeking risk-adjusted returns from a balanced portfolio.  The purpose of this article is to explain the rebalancing process.

Allocation plan

Investment portfolios hold one or more financial assets that are traded in financial markets.  At the time of inception, the market value of the portfolio (P) is the total cash payment for all assets.  The original portions of assets are called the portfolio’s allocation plan.

Consider the model portfolio that holds 2 assets.  The market value of the first asset (A) plus the market value of the second asset (B) are equal to P.  The portfolio equation is written P = A + B.  Furthermore, the value of A is denoted aP; and likewise for the value of B (bP).  Written as an equation, P = aP + bP.  Constants a and b are called the assigned weightings; they are the portfolio’s allocation plan.   The allocation plan is written 1 = a + b to emphasize that the sum of assigned weightings is always 1.0.

Portfolio drift

Market forces typically cause the values of portfolio assets to shrink or grow with the passage of time.  In other words, the effect of time revises the original portions of portfolio assets.  The effect of time on the portfolio equation is written PT = AT + BT.  The effect of time on the portions of portfolio assets is written (AT/PT) and (BT/PT).  The observed weightings (aT & bT) represent portions of the portfolio; (aT=AT/PT) and (bT=BT/PT).  The difference between observed and assigned weightings is called portfolio drift.  The portfolio drift equation is written 0 = (aT-a) + (bT-b).

Allocation error

An asset’s allocation error is determined by calculating the quotient of its drift and assigned weighting.  The allocation errors for both assets are written (aT-a)/a and (bT-b)/b; they represent the percentage difference from the allocation plan.

Rebalancing signal

The portfolio is rebalanced according to a schedule or signal.  One way of generating a signal is to compare allocation errors to an assigned boundary [for example, the #SmallTradesDesigner].  The best chance for assigning an effective boundary is to perform trial-and-error tests on a historical portfolio of financial assets.  In preparation for testing, the historical allocation errors are converted to absolute values and searched for the maximum allocation error (see example in next paragraph).  Then the test boundary is arbitrarily set between 0% and 100% of the maximum error.  The test portfolio is rebalanced when any allocation error exceeds the test boundary.  In order for a rebalancing signal to work on future returns, all allocation errors must be converted to absolute values.

Example: Suppose the test portfolio’s greatest allocation error is -82%, which converts to an absolute value of 82%.  Also suppose that the test boundary is arbitrarily set to 50% of the greatest allocation error.  Then 50% of 82% is 41%.  In this way, the test boundary represents a rebalancing signal of 41%.  Any allocation error with an absolute value exceeding 41% will incite a test rebalance of the historical portfolio.

The best rebalancing signal produces the highest total return of the historical portfolio.  Lower rebalancing signals increase the number of rebalancing episodes and higher signals decrease the number of episodes.  Consider what happens when testing the extreme rebalancing signals of 0% and 100%.  The 0% signal would allow every allocation error to trigger a rebalance and the trading costs would be prohibitive.  The 100% signal is restrictive by preventing all chances for a rebalance.

Correcting the cash-equivalent drift

The goal of rebalancing a portfolio is to restore drifting assets to their allocation plan.  The rebalancing process involves calculating a cash-equivalent drift for every asset.  Asset AT’s cash-equivalent drift is the product of (aT-a) and PT.  Asset A is rebalanced by purchasing additional units (‘shares’) when the cash-equivalent drift is a negative value and selling surplus units when the cash-equivalent drift is a positive value.  The number of units traded is equal to the cash-equivalent drift divided by unit price.  Asset A’s cash-equivalent drift is corrected when (aT-a) is restored to 0.  Asset BT is rebalanced in the same way.

Conclusion

An investment portfolio is always balanced at inception when all assets are simultaneously purchased according to an allocation plan.  Market forces inevitably cause the assets to drift from the allocation plan in which case the rebalancing process restores the allocation plan.  The rebalancing process requires an allocation plan, rebalancing strategy, and correction of the cash-equivalent drift.

Two financial assets were used to illustrate the basic principles of rebalancing an investment portfolio.  The same process applies to portfolios with a greater number of assets but not to the portfolio with one asset.  The portfolio with one asset can’t be rebalanced.  Market forces may change the single asset’s market value, but can never change its assigned weighting of 100%.

October, 2014, update:  I recently discovered (for the first time) two very excellent papers that explain better than I the concepts of allocating and rebalancing investment portfolios. Please read them in order to gain a better insight into portfolio management.

Copyright © 2013 Douglas R. Knight


%d bloggers like this: