Tariff Calculation Spreadsheet

Friends, as you may or may not know, Saturday night is the night of week I like spend documenting large spreadsheets. Lucky me!

In this article I will be describing how to use the spreadsheet I have developed over the last couple of weeks and used in the last couple of articles. The spreadsheet allows one to estimate the likely costs of using particular electricity tariffs – the Octopus GO, FLUX and COSY tariffs – if your dwelling has a domestic battery and solar PV panels. My hope is that it will help people make rational choices about which option is best for them.

Download

The Excel spreadsheet can be downloaded from this link. If you are downloading this macro-enabled file on a Windows computer, then the macros will probably be blocked by default. To change this you may need to first close the file in Excel, then right-click on the file’s icon and select the ‘Properties’ pane. Here you should see a tick box labelled “unblock”. If you unblock the file then it should work correctly.

Please note, the spreadsheet comes with no guarantee of anything at all, and it can at times be very slow to re-calculate.

Structure

The spreadsheet consists of two parts.

• At the top are several boxes that set the parameters of the simulation, and which show the results. I think of this as a ‘dashboard’
• Below this are 8,760 rows, one for each hour of the year. The spreadsheet proceeds through the year hour-by-hour simulating the flows of electricity from solar PV panels and the grid, into and out of batteries and your dwelling.

Throughout the spreadsheet, cells which are ‘inputs’ i.e. cells that you might reasonably want to change, have a yellow background with red text. Cells which are ‘outputs’ i.e. which contain the results of calculations have a red background with white or yellow text.

Click on image for a larger version. The visual appearance of ‘inputs’ and ‘outputs’.

The Dashboard: Overview

Click on image for a larger version.

The dashboard has controls in four regions.

• Regions 1 & 2 are about the tariff being simulated and the prices of the tariffs in each of their cheap, medium and high periods.
• Region 3 sets the parameters of the battery and the charging strategy, the amount of solar PV generation, and the likely household load.
• Region 4 contains the results of the simulation.

The Dashboard: Tariff Section

Click on image for a larger version.

After selecting the tariff to be investigated in the drop down menu, the selected tariff will appear in the box on the far right.

The prices of the different tariff stages can be changed if you desire.

The timings of the tariffs cannot be changed on the spreadsheet. They are set in a Visual Basic macro with the code below. If you understand this sort of thing then you can see how you could adapt the spreadsheet to a different set of tariff timings.

Click on image for a larger version.

The Dashboard: Main Settings

Click on image for a larger version.

The settings for the simulation have four main parts.

The Battery Settings are as follows

• Battery capacity describes the amount of electrical energy the battery can store.
• Round trip efficiency accounts for the energy lost as electricity is stored in the battery and then later drawn from the battery.
• Charge Rate is the rate at which battery charges. This limits how much electricity can be stored in the battery over a given period.
• The initial state of charge is the assumed state of charge at midnight on the 1st January.

Click on image for a larger version.

The cells referring to summer and winter should really be in the neighbouring box about charging strategy [Edit: they have now been moved]. Why are they there? For some systems, it can be sensible to switch between different charging strategies through the year, depending on the amount of solar energy available. For my own system:

• In winter I charge the battery at night using cheap rate electricity and the battery then runs down during the day.
• In summer, there is no need to charge at night because the battery is charged for free during the day by solar PV electricity.

The two boxes allow one to choose the days of the year on which to switch strategy.

In the ‘Grid Charging Strategy‘ box one can choose between charging when electricity is cheap, and/or charging in the hour before electricity becomes expensive. Either option can be chosen to be active in summer, winter or both.

In the solar factor box, one can set the expected amount of solar generation expected. This is generated by scaling the hour-by-hour solar data by the factor shown in the box.

Note that selecting a target amount will give an accurate result if the average solar generation from 2005 to 2016 is selected in the drop down menu at the bottom of the box. If one selects solar data from a particular year then the amount of generation will vary in line with that years variable output.

Click on image for a larger version.

The final box simulates how electricity demand from the household changes through the year.

Click on Image for a larger version.

Demand consists of two components: a steady consumption every day, and a component which peaks in mid-winter simulating the use of a heat pump.

The length of winter depends on a setting from 1 to 5 as shown in the figure below.

Click on Image for a larger version.

How the simulation works.

Each row of the spreadsheet from row 48 downwards calculates the state of charge of the battery, the household demand, and the solar PV according to the settings on the dashboard.

The simulation works row-by-row proceeding hour-by-hour through the year. The columns are as follows

• A: Index
• B: The day of the year expressed as a decimal day.
• C: The hour of the day, used for calculating the appropriate tariff
• D: The season of the year WINTER or SUMMER according to dashboard settings
• E: The Tariff Rate (cheap, medium, or high) determined by the Visual Basic code described above.
• F: G: & H: The hourly background & variable consumption – and their total.
• I: The daily average of the demand
• J: The time of day expressed as a fraction of a whole day
• K: Blank
• L: The 3-day running average of solar generation (used for plotting)
• M: The hour-by-hour solar data selected in the drop down menu in the SOLAR box. This is looked up from the data table in columns AE to AQ
• N: Modified demand: this is the difference between current demand and the amount of solar PV currently being generated.
• O: The amount of energy delivered to the battery (if positive) or drawn from the battery (if negative). This is calculated according to the current state of charge of the battery.
• P: The amount of energy drawn from the grid (if positive) or sent to the grid (if negative). This is calculated according to the current state of charge of the battery.
• Q, R, S, & T : Imports: If column P indicates that electricity has been imported, columns R, S and T list the amount of it in each tariff charging rate: these columns are then totalised at the top to calculate the annual amount to be paid.
• U, V, W, & X : Exports: If column P indicates that electricity has been exported, columns U, W and X list the amount of it in each tariff charging rate: these columns are then totalised at the top to calculate the annual amount of revenue accrued.
• Y: Hourly cost of grid imports
• Z: Hourly amount due from grid exports
• AA: Depending on the charging strategy this is when the battery charges from the grid. Household consumption is also met by the grid during these periods.
• AB: The amount of electrical energy sent to the battery.
• AC: The current state of charge of the battery.
• AD: Blank
• AE to AQ. Hourly solar data for the years 2005 to 2012 downloaded for the EU Sunshine database for my 4,000 kWh/year solar system in Teddington.

Graphs

Click on Image for a larger version.

Scrolling down  the spreadsheet will show four graphs which can be helpful in understanding what is happening.

The first graph shows three quantities charted across each day of the year:

• The 3-day average of the selected solar data
• The daily household demand
• The capacity of the battery

The second graph shows the state of charge of the battery charted across each day of the year.

The third and fourth graphs show the state of charge of the battery during a typical summer and a typical winter day.