[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.
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.
- 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.
- The percent error reflects how much each of the holdings deviated from their assigned allocation.
- The rebalanced asset value is a realignment of the holdings to their assigned allocation.
- 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.
- 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 charity. A sample problem is appended to the references of this post.
There are rebalancing calculators available on the internet at no charge to the reader. Here are a few of them:
- Free portfolio rebalancing calculator (1)
- Portfolio rebalance calculator(3)
- Portfolio rebalancing (4)
Copyright © 2013 Douglas R. Knight
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.
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.
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.