How to Automate Your Stock Portfolio Rebalancing in Google Sheets
Are you tracking 10-20 stocks and wondering if your portfolio is still balanced? Most DIY investors check their allocation once a year and find one position has grown to 30% of their portfolio when they wanted 10%. That's portfolio drift — and it's costing you money.
I built a simple Google Sheets template that:
- Calculates your current allocation automatically
- Compares it to your target allocation
- Tells you EXACTLY how many shares to buy or sell
The Problem with Manual Tracking
You probably have a spreadsheet with columns for ticker, shares, and price. But do you know if AAPL is now 25% of your portfolio when you wanted 15%? Do you know when to sell Microsoft to buy more Starbucks?
Portfolio drift is silent. A position that doubles in price naturally becomes overweight. Without tracking variance, you end up with unintended risk concentration.
The Solution: Automated Rebalancing
My template uses three key formulas:
Current Allocation: =MarketValue/SUM(MarketValues) — shows each stock's current weight
Variance: =CurrentAllocation - TargetAllocation — positive means overweight
Trades Needed: =(TargetWeight * TotalValue - CurrentValue) / CurrentPrice — positive means buy
How to Set It Up (3 Minutes)
- Make a copy of the template
- Type your tickers and shares
- Set target percentages (must sum to 100%)
That's it. The sheet calculates everything automatically. Check it quarterly or whenever you add money.
Free Template
I published the exact template I use (with sample data for GOOGL, MSFT, SBUX, AAPL). It's called the Stock Portfolio Tracker & Rebalancer and it's available for a small fee that supports future updates.
No macros, no scripts, no API keys needed. Just Google Sheets formulas and a few minutes of setup.
Have questions about rebalancing strategies? Drop them in the comments. I personally use threshold rebalancing (rebalance when any position drifts >5%) but calendar rebalancing works too if you check quarterly.
