Seven spreadsheet mistakes in a buy-to-let model
The seven arithmetic mistakes that keep turning up in self-built buy-to-let models, each with the formula that fixes it and what they cost together.
Updated on 8 August 2026.
The short answer
Seven arithmetic mistakes turn up again and again in self-built buy-to-let models. Interest charged on the opening balance is one. A missing stamp duty surcharge is another. On a £192,500 Nottingham terrace, six of the seven land inside a single year and move it from £75 in the black to £1,054 in the red.
Cash after tax = £14,300 − £4,375 − £8,761 − £2,218 = −£1,054
None of the seven ever produces an error message. Each returns a number that looks entirely reasonable, right up to the moment you check it against a completion statement.
Why a spreadsheet never warns you
A spreadsheet computes exactly what you put in it. It checks syntax and never meaning. A wrong basis, a missing line or a mixed-up unit all return a value, and that value looks plausible until something outside the file contradicts it.
A sheet built for screening is fine, and four columns will do it. The trouble starts when the same file has to carry a purchase decision. Nothing in it was designed to flag what is simply absent.
BRIX Calc is built against these seven categories. The engine holds explicit guards: clamped percentages, separate parsers for amounts and for rates, and an order of calculation that does not depend on which field a user filled in first. That is why this article can print the formulas.
The seven mistakes, and the formula that fixes each
The seven mistakes below all appear in the Nottingham model in the next section. Each one is a single cell or a single missing line.
| # | What the sheet does | What it should do |
|---|---|---|
| 1 | Interest on the opening balance, every year | Interest on the balance as it falls |
| 2 | Stamp duty from the bands alone | Bands plus the 5% additional property surcharge |
| 3 | Mortgage interest deducted from rental profit | Tax on profit before interest, then a 20% credit |
| 4 | Weekly rent × 4 × 12 | Weekly rent × 52 |
| 5 | Voids sitting in the cost column | Voids taken off the rent line first |
| 6 | Agent fee at the headline percentage | Agent fee plus VAT at 20% |
| 7 | Return on equity over total investment | Return on equity over cash invested |
Mistake 1, interest on the opening balance: on a capital repayment loan the balance falls every month, so the interest has to fall with it.
Wrong: interest = £144,000 × 6.24% = £8,986, in every year Right: interest = the balance for that year × 6.24%
The Nottingham loan repays straight-line over 20 years, at £7,200 a year. In year 1 the average balance is £140,400, so the interest is £8,761 rather than £8,986. That gap is £225, small enough to shrug at. By year 10 the average balance is £75,600 and the correct interest is £4,717. The sheet still says £8,986, which is £4,269 too much in that year alone.
Mistake 2, stamp duty from the bands alone: buying an additional dwelling in England or Northern Ireland adds a 5% surcharge on the whole price, not on the top slice.
On £192,500 the banded duty is £1,350. The surcharge adds £9,625, so the real bill is £10,975. A sheet that walks only the bands understates the cash you need at completion by roughly seven times the banded figure. Source: HMRC stamp duty land tax guidance, checked on 8 August 2026.
Mistake 3, mortgage interest deducted from rental profit: since the 2020-21 tax year a private landlord cannot treat interest as an expense, and receives a credit worth 20% of it instead.
Wrong: tax = 40% × (£9,925 − £8,761) = £466 Right: tax = 40% × £9,925 − 20% × £8,761 = £2,218
For a higher-rate taxpayer the error equals 20% of the interest, which is £1,752 a year here. The 20% and 40% rates apply in England, Wales and Northern Ireland, while Scotland sets its own bands. Source: HMRC, tax relief for residential landlords, checked on 8 August 2026. BRIX Calc does not work your tax bill out either, so this sum stays yours.
Mistake 4, weekly rent multiplied by four: British agents quote rent per week as well as per calendar month, and four weeks is not a month.
Wrong: £275 × 4 × 12 = £13,200 Right: £275 × 52 = £14,300
The difference is £1,100 a year, or 7.7% of the rent line. Every ratio built on that cell inherits it: yield, cover, cash flow. This one is worth checking first, because it is the fastest to confirm and the easiest to type in twice.
Mistake 5, voids sitting in the cost column: a void allowance that appears only among the costs leaves every rent-based ratio untouched.
The £715 allowance in the Nottingham sheet is 5% of rent. Gross yield still reads 7.4%, because it divides £14,300 by £192,500. Take the haircut off the rent line first and the same property reads 7.1%. What a lender needs to see is a separate calculation again.
Mistake 6, agent fees without VAT: a letting agent quoting 10% for management usually quotes it before VAT, and VAT is 20%.
Ten percent of £14,300 is £1,430, and the invoice is £1,716. The £286 gap looks trivial in year 1 and reaches £5,720 over a twenty-year hold, before any rent growth at all. Source: HMRC standard rate of VAT, checked on 8 August 2026.
Mistake 7, return on equity over the wrong denominator: dividing the return by total investment instead of the cash you put in renames the ratio without telling you.
Cash invested here is £62,940: a deposit of £48,500 plus £14,440 of buying costs. Total investment is £206,940. The year 1 return is £8,364, being £1,164 of cash plus £7,200 of capital repaid.
ROE = £8,364 / £62,940 = 13.3% Same return over total investment = £8,364 / £206,940 = 4.0%
Both figures are real. Only one of them is return on equity. Put your own numbers in the return on equity calculator if you want the two side by side.
A worked example: what the seven cost together
The worked example is a mid-terrace in Nottingham at £192,500, let at £275 a week. Finance is a £144,000 capital repayment loan at 6.24%, straight-line over 20 years. The landlord already owns a home and pays higher-rate income tax.
| Cost line | In the sheet | Correct |
|---|---|---|
| Letting agent, 10% of rent collected | £1,430 | £1,716 |
| Landlord insurance | £245 | £245 |
| Repairs and maintenance | £1,375 | £1,375 |
| Gas safety, EICR and licensing | £324 | £324 |
| Void and arrears allowance | £715 | £715 |
| Ground rent and service charge (freehold) | — | — |
| Total | £4,089 | £4,375 |
Only one cost line differs, and it is the VAT. The agent invoices on rent actually collected, so the fee itself is identical in both columns. Everything else in the gap below comes from the rent line, the interest line and the tax line.
| Year 1 line | Sheet with the mistakes | Corrected |
|---|---|---|
| Rent | £13,200 | £14,300 |
| Running costs | −£4,089 | −£4,375 |
| Interest | −£8,986 | −£8,761 |
| Profit before tax | £125 | £1,164 |
| Tax | −£50 | −£2,218 |
| Cash after tax | £75 | −£1,054 |
Stamp duty sits outside that table, because it is paid once. The sheet budgets £1,350 and the solicitor asks for £10,975 on the day.
| Figure | Value | What it means |
|---|---|---|
| Cash after tax, sheet with the mistakes | £75 | Reads as a property that just about washes its face |
| Cash after tax, corrected | −£1,054 | You top the account up every year instead |
| Annual gap between the two | £1,129 | Six of the seven items land inside a single year |
| Stamp duty missing at completion | £9,625 | Cash needed on the day, absent from the deposit plan |
| Return on equity, wrong denominator | 4.0% against 13.3% | Two different numbers under one name |
| Year 10 interest overstated | £4,269 | Mistake 1 grows in every year it runs |
Sensitivity is worth one line. Correct all seven and the property still needs £1,054 a year of support at a 6.24% rate. Push the rate to 7.24% and the interest rises by £1,404, so the support roughly doubles.
How to tell a sound model from a plausible one
A sound model shows its workings in separate cells, and each of the seven mistakes has a visible tell. Run down this list before you trust any file, including one you built yourself.
| What to look for | Which mistake it blocks |
|---|---|
| Interest recalculated on the balance for each year | 1 |
| Stamp duty as bands plus a separate surcharge cell | 2 |
| Tax taken on profit before interest, then a 20% credit | 3 |
| Rent entered once, in one unit, derived everywhere else | 4 |
| Voids applied to rent before any ratio is worked out | 5 |
| Agent fees stored with VAT included | 6 |
| Cash invested and total investment as two named cells | 7 |
Then test the model at its edges. Set the rate to 0%, or the term to one month. A model with a hidden fault usually returns something absurd there, while a sound one stays boring.
A second test takes seconds. Change one input and check that only the outputs which should move actually move. Change the rate, and the stamp duty must not budge. If it does, a formula is pointing at the wrong cell, which happens easily once cells get copied instead of rebuilt.
Which two are hardest to spot
Interest on the opening balance is hardest to spot over time, because the gap starts small. Year 1 is £225 out, which nobody queries. Year 10 is £4,269 out, by which point the file has been trusted for a decade and the assumption behind it has stopped being visible at all.
The Section 24 treatment is hardest to spot at all, because it never looks like a formula error. The sheet subtracts interest from profit, which is what any business does with an expense. Nothing in the layout hints that the tax rules stopped allowing it in the 2020-21 tax year.
Check your own model
Run the same property through both. Enter the Nottingham figures, or your own, and set the two outputs side by side. Every difference points at one of the seven above, and at the cell holding it.
Your first project in BRIX Calc is free, and PRO lifts the limit once you want several open at once. Start in the rental calculator. The tool works stamp duty out from the bands and projects the full term. It gives no tax advice, and Section 24 sits outside it.
Frequently asked questions
Do these mistakes only affect Excel users?
No, they turn up in any model that stitches formulas together by hand, whether that is Excel, Google Sheets or a form someone built in-house. The weak point is not the software. It is the absence of a built-in check that a dedicated calculator can enforce on every single run.
Can I test for these without another tool?
Partly. Run the model at its edges: a rate of 0%, a term of one month, a purchase price of £1. Then type the rent in weekly and in monthly form and check the yield moves as it should. Stamp duty and Section 24 need a source rather than a test.
Why is there no eighth or ninth mistake here?
Because the seven above are arithmetic, and arithmetic can be shown wrong. An over-optimistic rent growth assumption is a different problem: the formula is fine and the input is a guess. No spreadsheet check catches that one, and no calculator catches it either.
Does a paid calculator avoid all seven automatically?
Not automatically, but it removes the chance to retype them. A tool built for property maths carries fixed formulas and tests that rerun on every change to the engine. A general spreadsheet has no such guard, so the care of whoever built the file decides the outcome each time.