Your First Solar Model in a Spreadsheet: A 90-Minute Build

Every good decision I’ve seen a UK homeowner make about solar came out of a spreadsheet they built themselves, badly, before they knew what they were doing. Not a quote. Not an MCS installer’s estimate. Not one of the online calculators that asks for your postcode and roof pitch and returns a single confident number with no working shown.

The reason is simple enough. A quote tells you what someone wants to sell you. A solar panel spreadsheet model tells you what your own house does with sunlight, hour by hour, for a year. Those are different questions, and only one of them is yours.

So build the crude one first. Eight thousand seven hundred and sixty rows, seven columns, four formulas. It will be wrong in ways I’ll list at the end, and it will still be the single most useful artefact you own for the next twelve months, because everything sophisticated you do later is a patch on this thing. Battery dispatch logic, tariff comparison, degradation curves, an AI-assisted pipeline that pulls fresh meter data every night: all of it refines v1. You cannot refine something you haven’t built.

What you’re building

Seven columns. That’s the whole specification:

Col	Name	Source
A	`timestamp`	Your own date sequence, local time
B	`month`	`=MONTH(A2)`
C	`gen_kwh`	PVGIS
D	`load_kwh`	Your smart meter
E	`self_kwh`	`=MIN(C2,D2)`
F	`export_kwh`	`=C2-E2`
G	`import_kwh`	`=D2-E2`

Pick a non-leap year for the timestamp column. 2023 or 2025, not 2024, which has 8,784 hours and will silently misalign everything you paste into it.

Minutes 0 to 20: generation

Go to PVGIS, the European Commission’s photovoltaic tool at re.jrc.ec.europa.eu/pvg_tools/en/. It’s free, it has no signup, and its UK irradiance data is better than anything a salesperson will show you. Click the Hourly data tab, drop the pin on your actual roof, and fill in:

PV power estimation: ticked (this is the box people miss, and without it you get irradiance instead of output)
Installed peak PV power: your proposed array in kWp, say 4.0
System loss: leave it at 14%
Slope: your roof pitch, typically 35 to 40 degrees on a UK pitched roof
Azimuth: 0 is due south in PVGIS, negative is east, positive is west. A roof facing south-west is roughly +45.
Start and end year: same year, both.

Download the CSV. Open it and you’ll find ten or so header lines above the data, then columns time, P, G(i), T2m and friends. You want P, which is watts. Because each row covers one hour, watts and watt-hours are the same number here, so column C is =P/1000.

Two things will trip you. The timestamps read like 20230615:1211, not 20230615:1200, because that’s the satellite observation window, so strip the minutes. And PVGIS times are UTC. Your smart meter data is not.

For a 4 kWp array at 35 degrees facing south near Nottingham, you should land somewhere close to 3,712 kWh for the year.

Minutes 20 to 45: your actual load

This is the half of the model nobody else has, and it’s why your version beats the calculators. If you’re with Octopus, their API gives you half-hourly consumption directly:

GET https://api.octopus.energy/v1/electricity-meter-points/{mpan}/meters/{serial}/consumption/
    ?period_from=2025-01-01T00:00Z&page_size=25000

Basic auth, your API key as the username, blank password. Responses come back as interval_start, interval_end, consumption in kWh. Not an Octopus customer? Hildebrand’s Bright app with a CAD gives you the same half-hourly data through the DCC, and several suppliers now offer a plain CSV download in the account area. Availability shifts, so check what your supplier actually exposes today rather than what a forum post said in 2023.

You’ll get 17,520 half-hourly rows. Sum them in pairs to get 8,760 hourly values. If you only have twelve monthly bills, stop and go get the granular data. A monthly figure spread evenly across the hours will tell you your self-consumption is 62%, which is a fantasy, and the entire point of this exercise is not to believe fantasies.

Our example house uses 3,900 kWh a year, well above Ofgem’s typical 2,700, because there’s an EV on a 7 kW charger.

Minutes 45 to 55: the hour that eats models

British Summer Time. PVGIS hands you UTC; Octopus hands you local time with an offset in the string (2025-06-15T13:00:00+01:00). Paste one against the other without thinking and your generation curve sits an hour off your load curve for seven months of the year.

The effect is not small. Shifting generation one hour earlier against a household whose load peaks at 18:00 changed self-consumption by about 4% in the version of this I keep as a test case. That’s enough to move a payback estimate by half a year, which is enough to change a purchase decision. Convert everything to local time, then check one known point: midday on 21 June should be your peak generation hour, and it should read as hour 13, because BST.

Minutes 55 to 70: the four formulas

There are only four, and three of them are subtraction.

E2: =MIN(C2,D2)     self-consumption
F2: =C2-E2          export
G2: =D2-E2          import

Fill down to row 8761. That’s the model. The self-consumption logic really is MIN(generation, load): in any hour, you use the smaller of what you made and what you needed, you sell the rest, you buy the shortfall. No battery, no clipping, no diversion. Correct and crude, in that order.

Minutes 70 to 80: sanity checks

Run four before you believe anything:

=COUNT(C2:C8761) returns 8760. Not 8759, not 8784.
Annual generation divided by kWp gives your specific yield. In the UK that should be 800 to 1,000 kWh/kWp. Ours is 3,712 / 4 = 928. A result of 1,400 means you’ve picked up a Spanish test file.
=SUMIFS(C:C,B:B,...) for hours between 23:00 and 04:00 should be zero. Any night-time generation means a timezone or fill-down error.
Sum E, F and G, then confirm E+F equals total generation and E+G equals total load. If they don’t, a row somewhere has a blank.

Minutes 80 to 90: money

Pivot by month, or just twelve SUMIFs. Here’s the real output from the model above:

Month   Gen    Load   Self   Export  Import
Jan       88    400     55      33     345
Feb      160    355     82      78     273
Mar      300    350    128     172     222
Apr      440    310    160     280     150
May      510    285    178     332     107
Jun      520    265    175     345      90
Jul      515    265    177     338      88
Aug      445    270    162     283     108
Sep      345    290    133     212     157
Oct      220    330    103     117     227
Nov      107    375     65      42     310
Dec       62    405     37      25     368
-----------------------------------------
Year   3,712  3,900  1,455   2,257   2,445

Price it. Import avoided: 1,455 × 25p = £364. Export earned, at Octopus Outgoing Fixed’s 15p: 2,257 × 15p = £339. Total £702 a year. Against £6,200 installed, that’s an 8.8 year simple payback. Substitute your own tariff rates, obviously, and re-check them, because the unit rate you type in today will be wrong within six months.

What the crude version just told you

Look at that split again. Export is 48% of the annual value. Self-consumption, the thing every installer’s pitch is built around, is barely more than half the benefit, and only because this house has an EV inflating the load.

Now take the obvious next step in your head. A battery converts export into avoided import, and the spread between them is 25p minus 15p, so 10p per kWh. A 5 kWh battery that successfully shifts 1,200 kWh a year earns £120 from your solar. Against £4,000 of hardware. That is a thirty-year payback, and the crude model surfaced it in ninety minutes without a single line of dispatch logic.

The same battery on Intelligent Octopus Go, charging at 7p overnight and displacing 25p peak import, is working a 18p spread on a bigger volume. Different case entirely. You now know which question to actually model next, which is not a thing the calculators ever tell you.

What it’s deliberately wrong about

Hourly resolution overstates self-consumption. Real households spike: a kettle draws 3 kW for 90 seconds, a cloud passes over the array for four minutes. Averaging inside the hour hides both, and the published comparisons between hourly and one-minute data put the bias in the range of two to five percentage points, always in the optimistic direction. Fine. It’s consistent, it’s documented, and you can carry it as a known haircut.

One weather year is not the mean of many. Panels degrade about 0.5% a year. Nothing here handles inverter clipping, a diverter to the immersion, or the day your neighbour’s sycamore shades the east string from 3pm in September.

None of that is a reason to build something fancier today. It’s a list of the twelve improvements you’ll make over the next year, each one a column or a sheet added to this file, each one testable against the version you already trust. When you start automating the data pulls, scripting the tariff comparisons, or handing chunks of this to an LLM to extend, the pillar on AI tools, prompts and data pipelines covers how to wire that up without losing the audit trail.

Save the file as solar-v1-2026-09.xlsx and don’t edit it again. Copy it for v2. In six months you’ll want to know exactly how wrong the first one was, and the only way to find out is to have kept it.