Compare Home Energy Upgrade Costs & Savings in Google Sheets

Photo by Tim Wildsmith on Unsplash

Why build a home energy upgrade calculator in Google Sheets?

Deciding whether to replace an ageing boiler, insulate a loft, or swap halogen downlights for LEDs means balancing a one-off installation cost against years of reduced energy bills. A spreadsheet makes those numbers transparent: change one assumption — say your electricity tariff rises — and the payback period updates instantly. The GSheetPress Calculator tool extends this further by turning your spreadsheet into a shareable web form that anyone can use without ever opening Google Sheets.

This guide builds a seven-input model covering electricity and gas appliances, insulation upgrades, and similar home improvements. What it does not cover: time-of-use tariffs, solar generation offsets, demand-side flexibility, or government grant eligibility. Those each require additional data and local expertise beyond the scope of a single calculator. All numerical assumptions — particularly runtime hours, tariff, and equipment efficiency — need verification against your own bills and equipment datasheets before you treat any result as a financial projection.

Key terms explained

• Payback period: The number of years it takes for cumulative energy bill savings to equal the upfront installation cost.
• Annual energy consumption (kWh): Kilowatt-hours used per year by a device or building element, calculated as power rating multiplied by hours of use divided by 1000 to convert watts to kilowatts.
• Energy tariff: The price your supplier charges for each kilowatt-hour, found on your energy bill. This guide uses pence per kWh; adjust for your local currency.
• Running cost: The annual cost of operating a piece of equipment, equal to annual consumption multiplied by the tariff.
• Annual saving: The difference between the current annual running cost and the upgrade annual running cost.
• Installation cost: The one-off expense of purchasing and fitting the upgrade, including materials and labour.
• Efficiency (%): The proportion of input energy converted to useful output. A 90% efficient boiler wastes 10% of the gas it burns. Heat pumps can exceed 100% because they move heat rather than generate it — a coefficient of performance (COP) of 3.0 is entered as 300%.
• Runtime hours per year: How many hours annually a device is switched on and drawing power. This is usually an estimate and is the single variable with the greatest effect on results.
• GS2Web add-on: The GSheetPress Google Workspace add-on that publishes a configured spreadsheet tab as a live web calculator.
• Calculator entitlement: The active GSheetPress subscription plan that unlocks the Calculator publish feature for your account.
• Runtime G column: Column G in the calculator sheet, labelled Inputs in the generated header. The web form writes each visitor's submitted value into G2:G8 at runtime. Result formulas must reference G cells, not E cells, to use live submitted data.

Prerequisites

• A Google account with Google Sheets access.
• A GSheetPress account at gsheetpress.com/admin/signup.html with a plan that includes the Calculator feature. Confirm your entitlements on the GSheetPress pricing page before you begin.
• The GS2Web add-on installed in Google Sheets via Extensions → Add-ons → Get add-ons, then search for GS2Web. Authorise it with the same Google account linked to your GSheetPress subscription so the entitlement check passes.
• A dedicated sample workbook created specifically for this exercise. Do not use a file containing personal financial data — publishing requires setting the spreadsheet to Anyone with the link can edit, which means anyone who obtains that link can modify the sheet.
• Your current energy tariff in pence or cents per kWh from a recent bill. Using a national average reduces accuracy significantly.

Plan the model before touching a cell

The calculator compares one existing item with one proposed upgrade across seven inputs:

1. Current equipment power rating in watts
2. Upgrade power rating in watts
3. Daily runtime in hours
4. Energy tariff in pence per kWh
5. Current equipment efficiency as a percentage
6. Upgrade efficiency as a percentage
7. Installation cost in your local currency

From these the model derives annual consumption for each scenario, running costs, annual saving, and payback period. All result formulas reference the G column cells that the web form populates at runtime. The E column holds form defaults used for design and testing only.

Set up the sample workbook

1. Open Google Sheets and create a new blank spreadsheet.
2. Name the file Energy Upgrade Calculator — Sample.
3. Rename the default sheet tab to EnergyCalc by double-clicking the tab name.
4. Leave row 1 entirely empty. The GS2Web add-on generates its own header row (A1:H1) when you create the calculator. Typing anything in row 1 before that step causes a conflict.

Build the calculator with GS2Web

Create the calculator structure

1. In your sample workbook open Extensions → GS2Web SaaS (or Extensions → GS2Web if you installed the classic add-on).
2. Select Calculator → New Calculator.
3. In the dialogue enter the name Home Energy Upgrade.
4. Set the number of inputs to 7. This is the count of user-editable fields only — do not include the Calculate button in this number.
5. When asked whether to include graphs, choose No. The payback figure communicates clearly as a plain number.
6. Click Create. The add-on inserts the generated header row into A1:H1 and scaffolds rows 2 through 12 and beyond.

With seven inputs, the layout after creation is:

Understanding the generated columns

The header row A1:H1 labels eight columns. Here is what each one does:

Input row 2 — Current equipment power (watts)

• A2 — Label: Current power (W)
• B2 — Input Type: Number
• C2 — Required: Tick the checkbox to make this field mandatory
• D2 — Data: Leave blank
• E2 — Default: 2000
• F2 — ToolTips: Enter the rated wattage shown on the existing appliance label or in its manual
• G2 — Inputs: Leave blank (the add-on manages this column at runtime)
• H2 — Rules: Leave blank

Input row 3 — Upgrade power (watts)

• A3 — Label: Upgrade power (W)
• B3 — Input Type: Number
• C3 — Required: Tick the checkbox
• D3 — Data: Leave blank
• E3 — Default: 500
• F3 — ToolTips: Enter the rated wattage of the replacement appliance or system
• G3 — Inputs: Leave blank
• H3 — Rules: Leave blank

Input row 4 — Daily runtime (hours)

• A4 — Label: Daily runtime (hours)
• B4 — Input Type: Number
• C4 — Required: Tick the checkbox
• D4 — Data: Leave blank
• E4 — Default: 4
• F4 — ToolTips: Average hours per day the device is in active use — this estimate has the largest single effect on results, so measure or log it if possible
• G4 — Inputs: Leave blank
• H4 — Rules: Leave blank

Input row 5 — Energy tariff

• A5 — Label: Tariff (p/kWh)
• B5 — Input Type: Number
• C5 — Required: Tick the checkbox
• D5 — Data: Leave blank
• E5 — Default: 24
• F5 — ToolTips: Find this figure on your energy bill — do not use a national average if your actual rate differs
• G5 — Inputs: Leave blank
• H5 — Rules: Leave blank

Input row 6 — Current equipment efficiency (%)

• A6 — Label: Current efficiency (%)
• B6 — Input Type: Number
• C6 — Required: Tick the checkbox
• D6 — Data: Leave blank
• E6 — Default: 100
• F6 — ToolTips: For direct electric heaters enter 100. For gas boilers enter the seasonal efficiency from the datasheet. For insulation comparisons keep both efficiencies at 100 and adjust the power ratings instead.
• G6 — Inputs: Leave blank
• H6 — Rules: Leave blank

Input row 7 — Upgrade efficiency (%)

• A7 — Label: Upgrade efficiency (%)
• B7 — Input Type: Number
• C7 — Required: Tick the checkbox
• D7 — Data: Leave blank
• E7 — Default: 300
• F7 — ToolTips: Heat pumps typically have a COP of 2.5 to 4.0 — enter COP multiplied by 100 (so COP 3.0 = 300). Standard electric = 100. New condensing gas boiler = approximately 92.
• G7 — Inputs: Leave blank
• H7 — Rules: Leave blank

Input row 8 — Installation cost

• A8 — Label: Installation cost (£)
• B8 — Input Type: Number
• C8 — Required: Tick the checkbox
• D8 — Data: Leave blank
• E8 — Default: 3000
• F8 — ToolTips: Total one-off cost including equipment and labour — obtain at least two quotes before treating this figure as fixed
• G8 — Inputs: Leave blank
• H8 — Rules: Leave blank

Button and separator rows — do not edit

Row 9 is the generated Calculate button. Column B in row 9 reads Button and column D reads main. Column E in row 9 reads Reset. The word in A9 is the button label visible on the web form — leave it as generated or change it to Calculate if the add-on left it blank. Do not alter B9, D9, or E9. Row 10 is the merged Result Fields separator — leave it completely untouched.

Enter result labels and formulas

Column A of each result row holds the label shown on the published calculator. The formula goes in column B of that same row. To enter a formula: click the target cell, type =, paste the remainder of the formula, and press Enter.

Important — test before publishing: Column G (Inputs) is overwritten by web form submissions at runtime, so it starts empty. To see your formulas return numbers while designing, copy E2:E8, right-click G2, choose Paste special → Values only, and press Enter. This fills G2:G8 with the defaults. Remove these test values before publishing or simply leave them — the web form overwrites them anyway on each submission.

Result headings in row 11

Click A11 and type Result. Click B11 and type Value. These become the visible column headings in the results panel.

How the consumption formula works

Annual consumption in kWh = (power in watts divided by efficiency percentage, multiplied by 100) multiplied by daily runtime hours, multiplied by 365 days, divided by 1000 to convert watt-hours to kilowatt-hours. Dividing by efficiency and multiplying by 100 converts the percentage back to a decimal factor. For example, a 2000 W device at 100% efficiency running 4 hours per day consumes (2000 / 100 * 100) * 4 * 365 / 1000 = 2920 kWh per year. A heat pump rated at 2000 W input but operating at 300% effective efficiency produces the same useful heat from (2000 / 300 * 100) * 4 * 365 / 1000 = 973 kWh of consumed electricity.

Formula for B12 — Current annual kWh

Click cell B12. Type or paste the following formula exactly, then press Enter:

=ROUND((G2/G6*100)*G4*365/1000,1)

Formula for B13 — Upgrade annual kWh

Click cell B13. Enter:

=ROUND((G3/G7*100)*G4*365/1000,1)

Formula for B14 — Current annual cost

The tariff in G5 is in pence per kWh. Dividing by 100 converts to pounds. Click cell B14. Enter:

=ROUND(B12*G5/100,2)

Formula for B15 — Upgrade annual cost

Click cell B15. Enter:

=ROUND(B13*G5/100,2)

Formula for B16 — Annual saving

Click cell B16. Enter:

=ROUND(B14-B15,2)

Formula for B17 — Payback period (years)

If the upgrade costs more to run than the existing equipment the annual saving is zero or negative, making division meaningless. The formula returns a plain-text message in that case instead of an error or a misleading negative number. Click cell B17. Enter:

=IF(B16<=0,"No saving — upgrade costs more to run",ROUND(G8/B16,1))

Note: the less-than-or-equal operator is written as <= in HTML. In your spreadsheet cell you type it as <= literally — that is, a less-than sign followed by an equals sign.

Label column A for result rows

Ensure the following labels are in column A:

Worked example with expected results

Use the default values already entered in E2:E8 to verify the formulas. After pasting those defaults into G2:G8 (Paste special → Values only) you should see:

These numbers represent a 2 kW electric storage heater replaced by a heat pump delivering a COP of 3.0, run four hours a day at a 24p tariff with a £3,000 installation cost. The 4.7-year payback assumes the tariff and runtime remain constant — both assumptions need local verification.

Alternative scenario — LED lighting upgrade

To model replacing a 50 W halogen lamp with a 7 W LED running 5 hours a day at 28p per kWh with a £15 installation cost, enter these values into G2:G8:

Expected results: current annual kWh = 91.3, upgrade annual kWh = 12.8, annual saving ≈ £22.00, payback ≈ 0.7 years. If your spreadsheet returns different figures, recheck that G2:G8 all contain plain numbers with no leading spaces.

Troubleshooting common problems

Formulas return zero or blank

The most common cause is that G2:G8 are still empty. The web form has not been submitted yet, so the cells contain nothing. Paste the E column defaults into G using Paste special → Values only as described in the testing step above. If the cells look populated but still return zero, click each G cell and check the formula bar — a cell formatted as Text will store a number as text and cause arithmetic to fail. Format G2:G8 as Number via Format → Number → Number.

Payback formula shows the message instead of a number

This means B16 (annual saving) is zero or negative. Check that the upgrade power in G3 is genuinely lower than the current power in G2 when efficiencies are equal. If you are modelling a heat pump, confirm G7 is greater than G6 — entering 300 in G7 and 100 in G6 is correct for a COP-3 heat pump replacing a direct electric heater.

Efficiency division error — DIV/0

If G6 or G7 is zero, dividing by it produces a division-by-zero error. Efficiency cannot physically be zero. Add a minimum-value check by wrapping the efficiency references: replace G6 in B12 with MAX(G6,1) and G7 in B13 with MAX(G7,1). The full protected formula for B12 becomes:

=ROUND((G2/MAX(G6,1)*100)*G4*365/1000,1)

And for B13:

=ROUND((G3/MAX(G7,1)*100)*G4*365/1000,1)

Results appear on the spreadsheet but not on the published calculator

Result rows must be below the merged Result Fields separator in row 10 and must have a non-empty label in column A. If a result row's column A is blank, the add-on may skip it. Also confirm you clicked Publish Calculator (not just Save) after editing the result rows.

The add-on menu is missing

Open Extensions → Add-ons → Manage add-ons and confirm GS2Web shows as installed and enabled. If it is listed but greyed out, remove it and reinstall. Ensure the Google account you are signed into matches the account linked to your GSheetPress subscription — a mismatch causes the entitlement check to fail silently. Visit the GSheetPress support page if the problem persists.

Frequently asked questions

Can I adapt this calculator for gas appliances with different units?

Yes. Gas consumption is often quoted in cubic metres or cubic feet on bills, but energy tariffs for gas are still expressed in pence or cents per kWh in most markets after a calorific value conversion. If your bill already shows a kWh unit rate for gas, enter it directly in G5. If it shows a volume rate, multiply by the calorific value factor shown on your bill (typically around 1.02264 for gross calorific value in the UK) before entering. The power rating inputs in G2 and G3 should still be entered in watts — check the appliance datasheet for the heat input rating. The UK government guidance on gas meter readings and bill calculation explains the conversion steps clearly.

How accurate is the payback period this calculator produces?

The figure is indicative, not guaranteed. It assumes the tariff, daily runtime, and efficiency remain constant for the full payback period — in practice all three change over time. It also excludes maintenance costs, residual value of the replaced equipment, inflation, and any available grants or incentives. Use the result as a starting point for comparison rather than a financial commitment. For a more sophisticated model you can extend the spreadsheet with additional columns for each year of projected savings, incorporate tariff escalation rates, and discount future savings to a net present value. See the GSheetPress table tool for structuring multi-year comparison tables.

Can I embed this calculator on my website?

Yes. After publishing (see the final section below), the GS2Web Calculator Link sheet contains an Iframe embed code in column D. Copy that code and paste it into an HTML block on your website. The iframe loads the public calculator directly. You do not need to republish each time a visitor submits the form — submissions update the spreadsheet in real time. If you change the input rows or formulas, republish the same calculator (which retains its existing link) and the embedded version updates automatically.

Publish and test your web calculator

Share the sample workbook

Before publishing you must make the sample workbook editable by anyone who has the link. This is a technical requirement of the GS2Web publishing process — the add-on needs write access to populate G column cells when visitors submit the form.

1. In Google Sheets click Share in the top-right corner.
2. Under General access change the setting from Restricted to Anyone with the link.
3. Set the role to Editor.
4. Click Done.

Security reminder: This setting means anyone who obtains the spreadsheet URL can view and edit the entire workbook. Use only the dedicated sample workbook created for this exercise — never apply this setting to a file containing personal financial data, client information, or anything sensitive.

Publish the calculator

1. Click on the EnergyCalc tab to make sure it is the active sheet.
2. Open Extensions → GS2Web SaaS → Calculator → Publish Calculator.
3. In the dialogue, select Home Energy Upgrade (the name you entered when creating the calculator).
4. Click Publish. Wait for the confirmation message before closing the dialogue.
5. The add-on creates or updates a sheet called Calculator Link in your workbook. Navigate to that tab.
6. Find the row whose Sheet Name column matches EnergyCalc.
7. Click Get Links if the link columns appear empty — this refreshes the published URL.
8. Column C contains the public Link. Click it to open the live web calculator. Do not use column B (Edit Link), which opens the internal edit view.
9. Column D contains the Iframe embed code if you want to embed the calculator on a website.

Test the published calculator

1. Open the public link (column C) in a browser tab. You should see the web form with the seven input fields pre-filled with the default values from column E.
2. Enter the worked example values: Current power 2000, Upgrade power 500, Daily runtime 4, Tariff 24, Current efficiency 100, Upgrade efficiency 300, Installation cost 3000.
3. Click the Calculate button.
4. Confirm the results panel displays: Current annual kWh = 2920.0, Upgrade annual kWh = 243.3, Current annual cost = 700.80, Upgrade annual cost = 58.40, Annual saving = 642.40, Payback period = 4.7.
5. Try the LED scenario (50 W current, 7 W upgrade, 5 h runtime, 28p tariff, both efficiencies 100, £15 installation) and confirm the payback period is approximately 0.7 years.
6. Test the edge case: enter identical power and efficiency values for current and upgrade. The payback field should display the message No saving — upgrade costs more to run.
7. Return to the spreadsheet and check that G2:G8 now contain the values you last submitted — this confirms the runtime write-back is working correctly.

If you later modify input rows, result formulas, or labels, return to Extensions → GS2Web SaaS → Calculator → Publish Calculator and publish again. Republishing the same calculator name retains the existing public link and iframe code, so any website embeds continue to work without updating.

For further help with the add-on, visit GSheetPress support.