Why Your Spreadsheet Is Lying to You

Cost basis drift

Cost basis — what you originally paid for an asset — is the foundation of every capital gains calculation. It sounds simple, but it degrades over time.

The problem starts with events that adjust cost basis without generating a new "purchase": dividend reinvestments, stock splits, rights issues, bonus shares, return-of-capital distributions from funds. Each of these changes the effective cost per unit. If you don't record them correctly and immediately, your cost basis drifts from reality.

By the time you sell, your spreadsheet might show a cost basis of £12,000 for a holding you actually paid £9,400 for. That £2,600 error translates directly into an incorrect gain and, potentially, an incorrect tax return. Multiply this across a decade of holdings and the cumulative error can be substantial.

FX mistakes

If you hold any foreign-currency assets — US stocks in a GBP-based portfolio, a European property, a USD-denominated fund — your spreadsheet needs to handle currency conversion correctly. Most don't.

There are two different FX rates that matter: the rate at purchase (which determines your cost basis in your home currency) and the rate today (which determines your current value). A spreadsheet that pulls today's FX rate for everything — including the cost basis — is systematically wrong. For assets held through significant currency moves, this can produce wildly inaccurate return figures.

Manual entry errors

Spreadsheets require manual input. Manual input introduces errors. The errors are hard to detect because they look like everything else.

A transposed digit when entering a purchase price. A sell recorded in the wrong tab. A dividend entered twice. A row inadvertently deleted during a sort. None of these announce themselves — they sit quietly in your data, corrupting every calculation downstream.

Point-in-time snapshots that age badly

Most portfolio spreadsheets are built around today's values. But a portfolio is a history, not a snapshot. When you want to understand your actual performance — including realised gains, income, and the timing of your contributions — a static snapshot is the wrong tool.

Without time-series data, you can't calculate a meaningful return figure. If you put in £50,000 two years ago and it's now worth £60,000 but you withdrew £15,000 last year, the naive "20% gain" number is completely wrong.

The illiquid asset problem

Spreadsheets handle listed securities reasonably well because prices are available and updated daily. They handle everything else poorly.

How do you value a property you bought three years ago? How do you track a private equity investment through multiple rounds and a distribution? For investors with meaningful allocations to private assets, the spreadsheet is misrepresenting the majority of the portfolio.

What good portfolio tracking actually requires

• A transaction ledger as the source of truth — every purchase, sale, dividend, and corporate action recorded in full.
• Cost basis calculations that follow tax rules — Section 104 pools, FIFO, average cost — these aren't interchangeable.
• FX conversion at transaction date, not at valuation date.
• A valuation model for illiquid assets that separates the last known value from guesswork.
• Time-series NAV history so you can calculate actual money-weighted returns.