Simulating a Year of Battery Dispatch on Half-Hourly Data

“A battery sounds good” is not a capacity decision. A 5 kWh unit, a 10 kWh unit and a 13.5 kWh unit all sound good. Only one of them is likely to be the best value for your house, and you can find out which with a solar battery simulation spreadsheet and about 17,500 rows of data.

This post builds that spreadsheet from scratch. It simulates state of charge (SoC) every half hour for a year, applies power limits and round-trip losses, then runs the same year against several battery sizes so you can see where the extra kWh stops paying for itself.

What you need before you start

Two columns of half-hourly data for a full 12 months:

• Import (kWh from the grid). Your smart meter’s half-hourly data is available from your supplier’s app or portal, or via Octopus’s API if you’re with them. n3rgy’s consumer data service is another route if your meter is in the DCC.
• Solar generation (kWh). This comes from your inverter app (SolarEdge, Enphase Enlighten, Fronius Solar.web) or, if you don’t have panels yet, a PVGIS estimate scaled to half-hours.

From these you need household demand and solar generation per slot. If you have export data too, then demand = import + generation − export. No panels yet? Use demand = import and a PVGIS-style generation profile.

A year is 17,520 slots (17,568 in a leap year). Google Sheets copes fine. Excel is quicker. If it crawls, turn off auto-recalculation while pasting.

The sheet layout

Put your assumptions on a separate tab called Inputs, so you can change them in one place:

Use named ranges (Cap, MaxCh, MaxDis, Eff, MinSoC, Slot) so formulas read like sentences. One-way efficiency of 0.96 gives a round trip of about 92%, which is a fair figure for a modern AC-coupled lithium iron phosphate battery. Check your datasheet; some quote 90%, some 95%.

The Sim tab has these columns, one row per half hour:

The formulas

Work out the physics for one slot. The limit on charging is the lowest of three things: the surplus available, the power rating, and the room left in the battery (allowing for losses).

Charge in (column F), energy drawn from the surplus:

=MAX(0, MIN(-D2, MaxCh*Slot, (Cap-E2)/Eff))

The -D2 term is the surplus when Net is negative. MaxCh*Slot is 3.6 × 0.5 = 1.8 kWh per slot. (Cap-E2)/Eff is the energy you’d need to draw to fill the remaining space, since only 96% of it is stored.

Discharge out (column G), energy delivered to the house:

=MAX(0, MIN(D2, MaxDis*Slot, (E2-MinSoC)*Eff))

Here D2 is the shortfall. You can’t deliver more than the stored energy above your minimum, times the efficiency.

SoC end (column H):

=E2 + F2*Eff - G2/Eff

Charging adds 96% of what you draw; discharging removes more than it delivers. The start of the next row is =H2. Seed the first row with your minimum SoC.

Grid import (I) is =MAX(0,D2)-G2. Grid export (J) is =MAX(0,-D2)-F2.

A guard worth adding: conditional formatting that turns H red if it ever falls below MinSoC or above Cap. If it never goes red across 17,520 rows, your limits work.

A worked slot

Take a spring afternoon slot. Demand 0.30 kWh, solar 1.50 kWh, battery at 4.0 kWh, capacity 10 kWh.

Net        = 0.30 - 1.50 = -1.20   (surplus)
Charge in  = MIN(1.20, 1.80, (10-4.0)/0.96=6.25) = 1.20
Discharge  = 0
SoC end    = 4.0 + 1.20*0.96 = 5.152 kWh
Export     = 1.20 - 1.20 = 0

Then an evening slot: demand 0.90, solar 0, battery at 5.152.

Net        = 0.90
Discharge  = MIN(0.90, 1.80, (5.152-0.5)*0.96=4.466) = 0.90
SoC end    = 5.152 - 0.90/0.96 = 4.215 kWh
Import     = 0.90 - 0.90 = 0

Notice what the losses did. 1.20 kWh went in and the house got back 0.90 from a store that dropped by 0.94. The gap is the tax you pay for time-shifting.

Turning it into money

Add a summary block:

Annual import   =SUM(I:I)
Annual export   =SUM(J:J)
Cost            =SUM(I:I)*ImpPrice/100 - SUM(J:J)*ExpPrice/100

(Add your standing charge separately; it doesn’t change with the battery.)

Run the sheet with Cap set to 0 first. That is your baseline, and every battery result gets compared to it. Do not skip this, because “the battery saved me £X” only means something against the same year without it.

Testing battery sizes

Here’s the payoff. For an example household (3,900 kWh a year demand, 4.2 kWp south-west roof, around 3,800 kWh generation), I ran the sheet with a Data Table (Excel: What-If Analysis; in Google Sheets use a small script or copy the tab) varying Cap. Power limit scaled at 0.36 kW per kWh up to 3.6 kW. Prices 27p import, 15p export.

These figures are from one modelled household, not a promise for yours. Your own shape will differ, but the pattern is nearly universal: the first 5 kWh earns about £20 per kWh per year, the last few earn about a pound.

At a typical installed price of, say, £500 to £700 per kWh for a retrofit, the 5 kWh unit (about £3,000) pays back in around 29 years on these numbers. That is worse than the battery’s warranty. The 10 kWh unit costs roughly double and saves only 35% more. On pure self-consumption at these prices, no size here is a clear win, and the simulation has just told you so before you signed anything.

Why the curve flattens

Look at the daily pattern. A 4.2 kWp array makes a surplus of maybe 6 to 8 kWh on a good summer day, but your evening and overnight use is around 4 to 5 kWh. Once the battery is big enough to cover the night, extra capacity sits full and unused. In winter nothing fills it anyway. The sheet shows this directly: add a column counting full cycles (=SUM(F:F)*Eff/Cap) and you’ll see roughly 290 cycles a year at 5 kWh falling to about 150 at 13.5 kWh.

Under-used capacity is the mistake a spreadsheet catches and a brochure never will.

Making the model more honest

A few refinements that move the answer:

1. Standby draw. Many batteries use 20 to 40 W continuously. That is 175 to 350 kWh a year, which is not trivial. Subtract 0.03*Slot from SoC each row, or add it to demand.
2. Degradation. Rerun with Cap multiplied by 0.9 to approximate year ten.
3. Price rises. Rerun at 32p import for a sensitivity check.
4. Winter sunlight. If your generation column is modelled rather than measured, test a 10% pessimistic version.

Keep each as a toggle on Inputs so the scenarios are one cell away.

Where tariffs change the answer

Everything above charges only from surplus solar. A time-of-use tariff lets the battery also fill from the grid overnight, which changes the economics a lot, since the arbitrage can pay on its own days with no sun. That needs one more column: a grid-charge rule keyed to cheap-rate slots. It’s covered properly in Battery and Tariff Optimisation, but you can bolt it onto this sheet by adding an import price column per slot and a target SoC for the cheap window.

Using an AI assistant for the grunt work

ChatGPT, Claude or Gemini are good at the boring bits here. Paste your header row and the formulas, and ask for a Python version with pandas so you can sweep twenty capacities in seconds rather than copying tabs. Ask it to cross-check your spreadsheet against the script on one week of data; if SoC differs by more than rounding, one of them has a bug, and finding it is the point. Do not accept a total without that cross-check.

What to do this week

Download a full year of half-hourly data, build the Sim tab with the three formulas above, and run Cap at 0, 5 and 10. Those three runs will tell you more than any quote.