Almost every dedicated UK DIY investor starts with a custom Google Sheet or Excel workbook. Combining GOOGLEFINANCE("LON:VWRP") with manual transaction rows is a genuinely good way to learn how your portfolio actually behaves, and plenty of experienced investors run one for years without serious issue. But as the portfolio matures over 3, 5, or 10 years — more accounts, more transaction types, more tax events — a few specific spots in the maths are worth knowing about, because they tend to fail quietly rather than loudly.
1. Five places spreadsheet portfolio math tends to go wrong
1. XIRR Algorithm Divergence & #NUM! Errors
The standard XIRR() function in Excel and Google Sheets uses the classical Newton-Raphson method with a hardcoded iteration limit (typically 100 iterations). When cash flows alternate between deposits, withdrawals, high dividend reinvestments, and sudden market volatility, the polynomial equation can produce multiple local roots or flat derivatives.
When that happens, the function either returns #NUM! or converges on a mathematically valid but misleading root. Mitigation: if you see #NUM!, try supplying an explicit guess argument closer to your expected return, or split the calculation across shorter date ranges — it narrows the search space enough to avoid most divergence cases.
2. The Section 104 Share Pooling Nightmare
Under UK tax law, capital gains in a GIA must be calculated using HMRC Section 104 average cost pooling, governed by same-day and 30-day "Bed & Breakfasting" matching priority rules.
Maintaining a running Section 104 pool in a 2D spreadsheet grid is doable, but usually needs a dedicated tab per holding plus careful formulas for partial disposals — a single misplaced row can throw off the cost basis for every future tax year. Mitigation: keep one pool tab per security (not one shared table for everything), and add a running-total check cell so a broken formula shows up immediately rather than silently.
3. Historical Multi-Currency FX Drift
If you bought US stocks (e.g. Apple or Microsoft) in 2021 when GBP/USD was 1.38 and sold in 2024 when it was 1.25, your actual sterling capital gain differs from the asset's dollar price movement. Formulas that pull a live FX rate will silently restate your historical purchase cost in today's exchange rate rather than the rate on the actual purchase date. Mitigation: record the GBP cost at the point of purchase as a static value, not a live formula — treat FX rate as a snapshot, not a lookup.
4. Deposit Masking (The Clean Cash Fallacy)
A common formula is (Current Value - Total Cost) / Total Cost. Adding £5,000 in cash today instantly drags down your reported percentage return, while regular saving can create the impression of strong portfolio growth even during flat markets. Mitigation: keep a running "net deposited" column separate from "current value", so you can eyeball how much of your growth is contributions versus market return, even without a full XIRR calculation.
5. Silent Feed Breakage & Web Scraper Fragility
Formulas relying on IMPORTXML or IMPORTHTML to scrape fund prices from third-party sites can break without warning whenever the host website changes its page structure. Mitigation: add a "last updated" timestamp cell next to any scraped price, so a stale feed is obvious rather than silently wrong.
2. When a spreadsheet is enough, and when it isn't
A spreadsheet is a perfectly reasonable choice for a single account with infrequent trades and no CGT complexity — the failure modes above mostly show up as your setup grows: multiple accounts, partial disposals, or several years of history. If you're weighing whether to keep maintaining your own sheet or move to a dedicated tracker, the honest trade-off is time versus control: a spreadsheet gives you full visibility into every formula, at the cost of having to build and maintain the five fixes above yourself.
That's the gap Omnicogi is built to close for UK investors who'd rather not maintain that math by hand: bisection-assisted XIRR that doesn't diverge on irregular cash flows, automatic Section 104 pooling with same-day/30-day matching, and Clean Cash vs Generated Wealth split out automatically from your transaction history — imported from a CSV, not a live feed.
Important notice: Omnicogi is a software tracking application designed to assist UK investors with mathematical portfolio analytics. It is not an FCA-regulated financial advisory service, tax consultancy, or wealth management firm. Always verify critical figures against official broker statements and consult a qualified independent financial adviser or accountant.