TAX_EFFICIENT_REBALANCER

Overview

This function provides a tax-efficient rebalancing strategy for investment portfolios, inspired by the logic of tax-coordinated allocation. It helps Excel users optimally rebalance assets across taxable and tax-advantaged accounts (e.g., Taxable, Roth, Traditional) while minimizing tax impact and respecting account constraints.

Usage

To use the TAX_EFFICIENT_REBALANCER function in Excel, enter it as a formula in a cell, specifying your asset classes, current and target allocations, tax scores, and account capacities:


=TAX_EFFICIENT_REBALANCER(asset_classes, current_alloc, target_alloc, tax_scores, taxable_cap, roth_cap, trad_cap)

Parameters

Parameter	Type	Required	Description
asset_classes	2D list	Yes	Asset class names as a 2D list: [[“US Stocks”], [“Bonds”], [“Cash”]]
current_alloc	2D list	Yes	Current dollar allocations for each asset in each account: [[taxable, roth, trad], …]
target_alloc	2D list	Yes	Target total dollar allocation for each asset class: [[30000], [20000], [10000]]
tax_scores	2D list	Yes	Tax efficiency score for each asset: [[90], [60], [30]]
taxable_cap	float	Yes	Maximum dollars available in the taxable account.
roth_cap	float	Yes	Maximum dollars available in the Roth account.
trad_cap	float	Yes	Maximum dollars available in the Traditional account.

Return Value

Return Value	Type	Description
Allocation	2D list	New allocation for each asset: [[taxable, roth, trad], …]

Limitations

Assumes all assets are available in all accounts (no fund menu restrictions).
Does not account for transaction costs or wash sale rules.
Designed for educational and planning purposes; not tax advice.

Benefits

Automates tax-aware rebalancing for Excel users.
Reduces manual effort and potential for tax-inefficient trades.
Flexible for a variety of asset mixes and account types.

Example

Sample Input:

Asset Class	Current Taxable	Current Roth	Current Trad	Target	Tax Score
US Stocks	20000	5000	5000	30000	90
Bonds	10000	2000	8000	20000	60
Cash	5000	1000	4000	10000	30

Sample Call:


=TAX_EFFICIENT_REBALANCER(
  [["US Stocks"],["Bonds"],["Cash"]],
  [[20000,5000,5000],[10000,2000,8000],[5000,1000,4000]],
  [[30000],[20000],[10000]],
  [[90],[60],[30]],
  30000,8000,17000)

Sample Output:

Asset Class	Taxable	Roth	Trad
US Stocks	25000	3000	2000
Bonds	5000	2000	13000
Cash	0	3000	7000

All arguments except the account caps must be 2D lists (not 1D lists).

Source Code


def tax_efficient_rebalancer(asset_classes, current_alloc, target_alloc, tax_scores, taxable_cap, roth_cap, trad_cap):
    """
    Tax-efficient rebalancing of assets across Taxable, Roth, and Traditional accounts.
    All arguments and the return value must be 2D lists or scalars (no 1D lists).
    Args:
        asset_classes (2D list): [[asset_class1], [asset_class2], ...]
        current_alloc (2D list): [[taxable, roth, trad], ...]
        target_alloc (2D list): [[target1], [target2], ...]
        tax_scores (2D list): [[score1], [score2], ...]
        taxable_cap (scalar): Max dollars in taxable account.
        roth_cap (scalar): Max dollars in Roth account.
        trad_cap (scalar): Max dollars in Traditional account.
    Returns:
        2D list: New allocation [[taxable, roth, trad], ...]
    """
    try:
        def to_2d_list(x):
            if x is None:
                return []
            if isinstance(x, (str, bytes)):
                return [[x]]
            if isinstance(x, (int, float, bool)):
                return [[x]]
            if isinstance(x, list):
                if len(x) == 0:
                    return []
                if isinstance(x[0], list):
                    return x
                else:
                    return [[v] for v in x]
            return [[x]]
        def safe_float(x):
            try:
                if isinstance(x, (list, tuple)):
                    return float(x[0])
                return float(x)
            except:
                return 0.0
        # Parse inputs as 2D lists
        asset_classes = [str(row[0]).strip() for row in to_2d_list(asset_classes)]
        current_alloc = [list(map(safe_float, row)) for row in to_2d_list(current_alloc)]
        target_alloc = [safe_float(row[0]) for row in to_2d_list(target_alloc)]
        tax_scores = [safe_float(row[0]) for row in to_2d_list(tax_scores)]
        taxable_cap = safe_float(taxable_cap)
        roth_cap = safe_float(roth_cap)
        trad_cap = safe_float(trad_cap)
        n = len(asset_classes)
        # Calculate deltas
        deltas = [target_alloc[i] - sum(current_alloc[i]) for i in range(n)]
        # Sort assets by tax inefficiency (descending)
        sorted_idx = sorted(range(n), key=lambda i: -tax_scores[i])
        # Start with current allocation
        new_alloc = [row[:] for row in current_alloc]
        # Account capacities
        taxable_left = taxable_cap
        roth_left = roth_cap
        trad_left = trad_cap
        for i in range(n):
            taxable_left -= current_alloc[i][0]
            roth_left -= current_alloc[i][1]
            trad_left -= current_alloc[i][2]
        # Allocate deltas
        for idx in sorted_idx:
            delta = deltas[idx]
            if abs(delta) < 1e-6:
                continue
            # Tax-inefficient assets: fill tax-advantaged first
            if delta > 0:
                # Add to Roth, then Trad, then Taxable
                add_roth = min(delta, roth_left)
                new_alloc[idx][1] += add_roth
                roth_left -= add_roth
                delta -= add_roth
                add_trad = min(delta, trad_left)
                new_alloc[idx][2] += add_trad
                trad_left -= add_trad
                delta -= add_trad
                add_taxable = min(delta, taxable_left)
                new_alloc[idx][0] += add_taxable
                taxable_left -= add_taxable
            else:
                # Remove from Taxable, then Trad, then Roth
                remove_taxable = min(-delta, new_alloc[idx][0])
                new_alloc[idx][0] -= remove_taxable
                delta += remove_taxable
                remove_trad = min(-delta, new_alloc[idx][2])
                new_alloc[idx][2] -= remove_trad
                delta += remove_trad
                remove_roth = min(-delta, new_alloc[idx][1])
                new_alloc[idx][1] -= remove_roth
                delta += remove_roth
        # Ensure no negative allocations
        for i in range(n):
            for j in range(3):
                if new_alloc[i][j] < 0:
                    new_alloc[i][j] = 0.0
        return [row[:] for row in new_alloc]
    except Exception:
        n = len(asset_classes) if 'asset_classes' in locals() else 0
        return [[0,0,0] for _ in range(n)]