Rebalancing my portfolio used to feel complicated and time-consuming, so I decided to create a simple tool to make the process easier. It’s nothing fancy, but it has helped me stay on track with my target allocation and manage my investments more efficiently.
In this post, I’ll share how the tool works and how you can use it too. It offers four rebalancing methods to fit different needs. I hope you’ll find it as useful as I do!
In the menu, click File. Make a copy.
Tool Structure and Functionality
The tool is divided into several key sections, each serving a specific purpose in the rebalancing process.
Assets
- asset: Lists all investment vehicles in your portfolio (stocks, ETFs, bonds, etc.)
- value: Shows the current monetary value (in €) of each asset
- current: Displays the current percentage allocation of each asset in your portfolio
- target: This is where you input your desired percentage allocation for each asset
Rebalancing Strategies
The tool offers four different rebalancing strategies, moving from left to right:
MANUAL REALLOCATION
- Allows for manual entry of buy/sell amounts
- proj. alloc: Shows the resulting allocation if you execute your entered transactions
- realloc: Red cells where you enter the amount to buy (positive) or sell (negative) for each asset
- The tool calculates the resulting allocation based on your inputs
ALLOCATION ON BUDGET
- An optimization-based approach for limited investment amounts
- proj. alloc: The achievable allocation using only your specified budget
- realloc: The amount to invest in each asset within budget constraints
- Uses five iterations of optimization to approach the optimal solution
- Ideal for regular investment plans (PAC – Piano di Accumulo di Capitale)
- The budget amount (€60,000.00 in the example) is entered in the red cell
MINIMUM POSITIVE ALLOCATION
- A buy-only approach that doesn’t require selling any current holdings
- proj. alloc: The achievable allocation percentages with only buying
- realloc: The amount to purchase of each asset (all positive or zero values)
- Particularly useful for investors who prefer not to sell existing positions
MINIMUM REALLOCATION
- This represents the standard rebalancing approach – buying and selling assets to precisely match your target allocation
- proj. alloc: Shows the projected allocation percentages after rebalancing
- realloc: Indicates the exact amount to buy (positive values) or sell (negative values) for each asset
How to Use the Tool
- Initial Setup: Enter your current asset or taxonomies in the “asset” column
- Set Values: Enter asset values in the “value” column
- Set Targets: Define your desired portfolio allocation in the “target” column
- Choose a Rebalancing Method:
- For complete rebalancing: Use the “MINIMUM REALLOCATION” section
- For buy-only rebalancing: Use the “MINIMUM POSITIVE ALLOCATION” section
- For budget-constrained rebalancing: Enter your budget in “ALLOCATION ON BUDGET” section
- For manual adjustments: Enter specific amounts in the “MANUAL REALLOCATION” section
- Evaluate Results: The bottom row shows the RMSE (Root Mean Square Error) percentage between your target allocation and what’s achievable with each method – lower values indicate solutions closer to your target allocation
Practical Application
This tool is particularly valuable for:
- Periodic portfolio reviews (quarterly or annual rebalancing)
- Implementing new investment strategies with existing portfolios
- Making efficient use of additional investments (PAC method)
Remember that you should only interact with the cells highlighted in red in the spreadsheet. These include the assets with their value, the target allocation percentages, the manual reallocation amounts, and potentially the budget amount for the budget-constrained optimization.
The example assets shown (3 Stocks, 2 ETFs, 1 Bond, Gold) are provided as samples and should be replaced with your actual investment holdings when using the tool. This tool can only manage up to 20 assets.