Building a Payback Model That Survives Scrutiny

Most solar payback numbers fall apart the moment someone asks a second question. The quote says nine years. You ask which export rate that assumes, and it turns out to be a rate you can’t get because the installer isn’t the supplier. You ask what self-consumption percentage was used, and nobody knows, because the figure came from dividing the install price by an annual saving that was itself an assumption.

A model that survives scrutiny isn’t more complicated than that. It just makes every assumption visible and separable, so that when one of them turns out to be wrong (and one always does), you can change a single cell and see what happens. This page walks through building that model for a UK house, with real data sources and a worked example you can copy. For the wider picture of how this fits with reading quotes, comparing bids and spotting padded line items, the Cost Models and AI Quote Analysis pillar covers the territory around it.

Simple payback is a summary statistic, not a model

Solar panel payback calculation in the UK usually gets reduced to one division: capital cost divided by first-year saving. That number has its uses as a sanity check, but it hides four things that materially move the answer.

It assumes year one repeats forever. Panels degrade, roughly 0.4% to 0.55% a year under most modern linear warranties. Electricity prices move, and not in the same direction as export prices. Your hybrid inverter will probably want replacing somewhere between year 10 and year 15, at £900 to £1,500 fitted. And money you spend now has an alternative use, whether that’s an offset mortgage at 4.5% or a cash ISA.

Build the model as a year-by-year cash flow instead. Twenty-five rows in a spreadsheet, one column per assumption, and three outputs at the bottom: simple payback, discounted payback, and net present value. Excel and Google Sheets both have XNPV and XIRR, which handle irregular dates properly and stop you fudging the mid-year convention.

Start from your own half-hourly consumption, not a national average

The single biggest error in most DIY models is using “typical domestic consumption” from Ofgem’s TDCV figures. Your solar savings depend on when you use electricity, not how much, and a 2,700 kWh household that works from home beats a 4,200 kWh household that leaves at seven and returns at six.

You already have the data. Three routes to it, all free:

• n3rgy gives you direct half-hourly consumption from the DCC using your MPAN and the serial number on your smart meter, as a CSV or an API.
• Hildebrand’s Bright app and the Glowmarkt API do the same, and if you have their CAD you get near-real-time readings too.
• The Octopus Energy API exposes a /consumption/ endpoint per meter point if you’re an Octopus customer, which pairs nicely with their tariff endpoints.

Pull a full twelve months. You want 17,520 rows, not a monthly summary, because everything downstream depends on overlap between a generation profile and a demand profile at half-hourly resolution.

Getting a generation estimate you can defend

PVGIS is the default here, from the EU Joint Research Centre. Use the PVGIS-SARAH3 database, enter your exact latitude and longitude, set the slope and azimuth from your roof (azimuth is measured from south, so a west-facing roof is +90), and leave system losses at 14% unless you have a specific reason to change it. The hourly radiation tab will export a full year of hourly output as CSV, which is what you actually want.

Cross-check it against two others. Renewables.ninja uses MERRA-2 reanalysis and tends to run a few percent different. If your installer is MCS certified, their quote must include an estimated annual generation calculated under MCS MIS 3002, which uses the SAP-derived kWh/kWp tables plus a shading factor. MCS figures are usually the most conservative of the three, often 5% to 10% below PVGIS. If a quote’s generation number is above PVGIS, ask why.

For a Midlands roof at 35° pitch facing 20° west of south, expect something around 830 to 880 kWh per installed kWp. Scotland runs lower, the south coast higher.

Self-consumption is where models quietly break

Here is the line that does the most damage: assuming you use most of what you generate. Without a battery, a typical UK household directly consumes 25% to 35% of its solar output. The rest exports. That matters enormously, because the value of an exported kWh (15p on Octopus Outgoing Fixed, around 16.5p on E.ON Next Export Exclusive for their own customers) is well below the value of a self-consumed one.

Don’t guess the percentage. Join your two CSVs on timestamp and compute it. In half-hourly terms, for each period: self-consumed = min(generation, demand), exported = generation − self-consumed, imported = demand − self-consumed.

A battery adds a state-of-charge variable and a greedy dispatch rule: charge from surplus up to capacity, discharge to cover demand down to your depth-of-discharge floor, apply round-trip efficiency (88% to 92% for a modern LFP unit with its inverter losses included). That’s about forty lines of Python with pandas, or a column of IF statements if you’d rather stay in the spreadsheet.

Running that against real data typically lands somewhere like this:

System: 4.2 kWp array + 5.2 kWh LFP battery
Generation (PVGIS-SARAH3, 14% loss):   3,570 kWh
Household demand (n3rgy, 12 months):   3,900 kWh

                        No battery    With battery
Self-consumed (kWh)          1,000           2,213
  as % of generation            28%             62%
Exported (kWh)               2,570           1,357
Imported (kWh)               2,900           1,687

Worked example: a 4.2 kWp system in the East Midlands

Ten 420 W panels, a 3.68 kW hybrid inverter, a 5.2 kWh battery. Quoted at £6,200 for the PV and £3,900 for the battery, £10,100 total. Zero-rated for VAT under the current domestic relief, which runs until 31 March 2027 and then reverts to 5%, so a quote you accept in April 2027 is materially different from the same quote in March.

Assume 25.8p/kWh import and 15p/kWh export. Take your actual import rate off a recent bill rather than the headline cap, since the cap is a cap on unit rates and standing charges, not a price you necessarily pay.

Now layer on the things simple payback ignores. Degradation at 0.45% a year. Electricity price growth at 3% nominal, export price growth at 2%. Maintenance and an insurance uplift at £25 a year. Inverter replacement at £1,100 in today’s money, in year 13. Discount rate 4.5%, matched to the mortgage the money would otherwise overpay.

Over twenty years that gives an NPV of roughly £1,450, an IRR a shade over 6% nominal, and a discounted payback of about 16 years against the 13 you started with. Three years of difference, entirely from assumptions nobody put on the quote.

Give the battery its own payback line

Look again at the table. The battery costs £3,900 and improves the annual benefit by £131. On its own terms that is a thirty-year payback on a unit with a ten-year warranty, and no amount of optimism fixes it.

The reason is arithmetic, not product quality. A battery’s job in that model is to move a kWh from the export bucket (15p) to the self-consumption bucket (25.8p), a spread of 10.8p. It cannot earn more than the spread times the kWh it shifts.

Tariff arbitrage is what changes the picture. On Intelligent Octopus Go, overnight import runs around 7p. Charging 5 kWh a night for 300 nights displaces electricity you’d otherwise buy at the day rate, worth roughly £280 a year on top. Octopus Flux does something similar from the other end, paying a premium for export in the 16:00 to 19:00 window.

Two warnings before you bank that. Cheap-night tariffs usually carry a higher day rate, so you must re-run the whole model on the new tariff rather than bolting the arbitrage saving onto your old numbers. And check the throughput warranty: an extra 1,500 kWh a year through the cells counts against it.

Where AI genuinely helps, and where it will mislead you

Use an LLM to write the model, not to be the model. Ask Claude to write the pandas script that joins your PVGIS hourly CSV to your n3rgy export, simulate battery dispatch, and produce the self-consumption split. That’s a well-specified programming task and it’ll do it well. Attach the actual files.

What you must not do is ask it for your generation figure, your self-consumption percentage, or current SEG rates from memory. Those are exactly the numbers it will produce confidently and wrongly. Rates change quarterly; a model built on a hallucinated 20p export tariff is worse than no model.

The other strong use is adversarial. Paste your finished assumption sheet in and ask it to argue that the system will never pay back, then make it name the specific assumption doing the work in each argument. You’ll find the soft spots faster than by staring at the spreadsheet.

Two structural things worth checking before you commit: SEG payments generally require MCS certification of the install, so a self-installed array won’t earn export income from most suppliers, and your DNO needs a G98 notification (or G99 approval above 3.68 kW per phase) regardless.

Re-run it in month thirteen

Set a calendar reminder for twelve months after commissioning. Export a full year of actual generation from your inverter portal (SolarEdge, GivEnergy, Sunsynk, Fox all allow CSV download), pull another year of half-hourly consumption, and re-run exactly the same sheet with measured numbers in place of modelled ones.

The gap is the interesting part. If real generation is within 5% of PVGIS you have a well-sited system and a trustworthy model. If self-consumption came in at 48% rather than the 62% you assumed, something about your battery’s dispatch logic or your daily routine doesn’t match the simulation, and that’s worth chasing before you conclude the investment underperformed. A model you update is worth more than a model that was right first time.