Your spreadsheet probably shows the wrong average cost after you sell part of a position, and the error compounds every time you trade. This post breaks down exactly how entry-price-weighted averaging works with partial buys and sells, why the most common spreadsheet formula silently corrupts your cost basis, and how real-time P&L engines get it right.
What is weighted average cost basis?
Weighted average cost basis is the total amount you paid for a position divided by the total number of shares or units you still hold. It answers one question: what did each share currently in your account actually cost you on average?
The word weighted matters. If you buy 10 shares of AAPL at $180 and later buy 30 shares at $200, your average is not $190. It is weighted toward the larger lot.
- First buy: 10 x $180 = $1,800
- Second buy: 30 x $200 = $6,000
- Total cost: $7,800 across 40 shares
- Weighted average: $195.00, not the simple midpoint
Every real-time P&L calculation depends on this number being correct. Get the average cost wrong and your unrealized gain, your percentage return, and your allocation view all inherit the error.
How do partial buys change your average entry price?
A partial buy always pulls your average toward the price of the new lot, weighted by size. The formula adds the new cost to your existing total cost, then divides by the new total quantity.
The correct formula for adding to a position
The correct formula is new average = (existing total cost + new lot cost) / (existing shares + new shares). You never average the two prices directly, because that ignores how many shares each price applies to.
Continuing the AAPL example, suppose you add 20 more shares at $210:
- Existing cost: $7,800 on 40 shares
- New lot: 20 x $210 = $4,200
- Total: $12,000 on 60 shares
- New weighted average: $200.00
This scales to crypto too. If you dollar-cost-average into BTC-USD with weekly buys, each purchase reweights the average by its dollar size. PortfolioTrackr recalculates this on every fill so your entry price is never stale.
Why does average cost NOT change when you sell?
Your average cost per share stays exactly the same after a partial sell, because selling removes shares at your average cost, not at the market price. This is the single most misunderstood rule in cost basis math.
When you sell, two things happen and only one of them touches your average:
- Your share count drops by the quantity sold
- Your remaining total cost drops proportionally, at the average cost
- Your average cost per share is unchanged
- The difference between sale price and average cost becomes realized P&L
Say you hold 60 shares of AAPL at a $200 average and sell 20 shares at $230. You remove 20 x $200 = $4,000 of cost, leaving $8,000 on 40 shares. The average is still $200. You booked $600 of realized gain on the sale.
Understanding whether your gains are realized or unrealized also matters when you check whether your portfolio is actually beating the S&P 500, since paper gains and booked gains behave very differently.
Why do spreadsheet users get average cost wrong?
Spreadsheet users get average cost wrong because they let sells contaminate the average, usually by averaging the sale price into the cost formula or by re-deriving cost from a running net-quantity total. Both are silent errors that never throw a warning.
Mistake 1: averaging the sell price in
The most common error treats a sell as a negative buy and feeds its price into the weighted average. That distorts the average because the sale price has nothing to do with what your remaining shares cost. A sell at $230 should never lower or raise a $200 average.
Mistake 2: rebuilding cost from net shares
The second trap is computing average = running total spent / net shares held, where total spent mixes buy outflows and sell inflows. Subtracting sale proceeds from your cost total understates your basis and inflates your apparent gains. Your spreadsheet then reports a return that never happened.
These bugs hide well because the numbers still look plausible. This is a core reason we cover in our breakdown of portfolio tracker versus spreadsheet tradeoffs: manual formulas fail exactly when your trade history gets complex.
The correct spreadsheet approach
To do it correctly in a spreadsheet you must track two separate running totals that sells and buys touch differently:
- A total cost column that only buys increase, and sells decrease at the current average
- A share count column that both buys and sells adjust
- Average recomputed as total cost / share count after every row
Most people never build the sell logic this way, which is why the average drifts after the first partial exit.
Spreadsheet formula versus real-time engine: what breaks?
The difference between a naive spreadsheet and a real-time engine shows up the moment you have partial sells, multiple lots, or trades across brokers. Here is how the two approaches handle the same events.
| Event | Naive spreadsheet | Weighted engine |
|---|---|---|
| Partial buy | Often correct | Correct |
| Partial sell | Average drifts | Average unchanged |
| Realized P&L | Frequently missing | Booked per sale |
| Multi-broker same ticker | Manual merge | Auto-consolidated |
The multi-broker row is where retail investors lose the most accuracy. If you hold EMAAR.AE on one broker and add to it on another, your true average spans both. PortfolioTrackr consolidates identical tickers across accounts automatically, which is far harder to maintain by hand.
How does real-time P&L get calculated under the hood?
Real-time P&L is calculated by holding a live average cost per position and continuously comparing it against the latest market price for every open lot. The engine only recalculates the average when a buy or sell fill actually changes your holdings.
The sequence for each new trade looks like this:
- Classify the fill as a buy or a sell
- On a buy, add lot cost to total cost and shares to quantity, then recompute the average
- On a sell, remove shares at the current average, book the difference as realized P&L, and leave the average untouched
- On every price tick, compute unrealized P&L = (market price minus average cost) x shares held
Because US equities now settle T+1 since May 2024 and major crypto venues list well over 350 spot pairs trading around the clock, prices move constantly while your average stays fixed between trades. Separating the two is what makes the P&L both fast and correct.
Why entry-price weighting beats FIFO for a live view
Entry-price-weighted averaging gives you one clean cost number for a position, which is ideal for a live dashboard, while FIFO tracks individual lots for tax reporting. Many trackers show weighted average for your at-a-glance return and switch to lot-level accounting only when you need capital gains figures.
- Weighted average: one number, fast, great for real-time monitoring
- FIFO or specific-lot: lot by lot, required for accurate tax reporting in many jurisdictions
- Both can coexist: display the average, compute realized gains by lot method
How to keep your cost basis accurate across brokers
The reliable way to keep cost basis accurate across brokers is to connect each account to one tracker so every buy and sell flows through the same weighted-average logic. Manual entry across platforms is where drift and double-counting creep in.
Practical steps that prevent the classic spreadsheet errors:
- Record every partial sell as a realized event, not a cost adjustment
- Never let a sale price enter your average calculation
- Consolidate the same ticker held on different brokers into one position
- Reconcile your tracked share count against your broker statement monthly
If you are wiring up multiple accounts, our guide on connecting a brokerage account to a portfolio tracker walks through the setup, and the 2026 comparison of six portfolio tools shows which ones handle partial sells correctly.
The bottom line
Weighted average cost only changes on buys, and a partial sell should leave your average completely untouched while booking realized P&L. The spreadsheets that get this wrong do so by feeding sale prices into the average or by rebuilding cost from net shares, and the error quietly inflates your reported returns.
A real-time engine keeps a live average, recomputes it only on fills, and separates realized from unrealized gains on every price tick. PortfolioTrackr does this automatically across stocks, crypto, and UAE markets, so your entry price stays correct no matter how many partial buys and sells you make.
Track your portfolio in real time: free for 3 days
Live P&L across stocks, crypto, and global markets. WhatsApp and Telegram price alerts. AI trade import. Unified dividend tracking. No brokerage connection required.
Start Free Trial
Frequently asked questions
Does average cost change when you sell part of a position?
No, your average cost per share stays the same after a partial sell. Selling removes shares at your existing average cost, which lowers your total cost and share count proportionally. The difference between the sale price and your average becomes realized profit or loss, but the average itself is unchanged.
How do you calculate weighted average entry price with multiple buys?
Add the total cost of every buy together, then divide by the total number of shares bought. For example, 10 shares at $180 plus 30 shares at $200 equals $7,800 across 40 shares, giving a weighted average of $195. You never average the two prices directly.
Why does my spreadsheet show the wrong average cost after a sell?
Most spreadsheets get it wrong by treating a sell as a negative buy and averaging the sale price into your cost, or by rebuilding cost from net shares. Both let sale proceeds contaminate your basis. Sells should only reduce share count and total cost at the current average, never affect the average price.
What is the difference between realized and unrealized P&L?
Realized P&L is profit or loss locked in when you actually sell, calculated as sale price minus average cost times shares sold. Unrealized P&L is paper gain or loss on shares you still hold, calculated as current market price minus average cost. Only realized gains are typically taxable.
Can PortfolioTrackr calculate cost basis across multiple brokers automatically?
Yes, PortfolioTrackr consolidates the same ticker held on different brokers into one position and applies weighted-average logic to every buy and sell. This prevents the drift and double-counting that happen when you track cost basis manually across separate broker apps or spreadsheets.