Rooftop Solar Savings & Payback Calculator in Google Sheets

Photo by Aaron Lefler on Unsplash

What this guide covers and what it excludes

This guide shows you how to build a rooftop solar savings and payback calculator inside Google Sheets and publish it as a web calculator using the GSheetPress GS2Web add-on. You will estimate annual energy generation, the portion you consume directly (self-consumption), income from energy exported to the grid, maintenance costs, and optional loan financing. You will then calculate both a simple payback period and a discounted cash flow measure called Net Present Value (NPV).

What is excluded: This guide does not cover battery storage modelling, time-of-use tariff optimisation, shading analysis, structural roof assessments, or any form of financial advice. All tariff figures, feed-in rates, and solar irradiance values used below are illustrative placeholders. You must replace them with figures that apply to your location and energy supplier before drawing any conclusions.

Key terms explained

• Peak kilowatt (kWp): The rated power output of a solar panel system under ideal laboratory conditions. A 5 kWp system would produce 5 kilowatts in perfect sunlight.
• Specific yield: The annual energy a system produces per kWp of installed capacity, expressed in kilowatt-hours per kWp per year (kWh/kWp/year). It varies by location, roof tilt, and orientation. The European average is roughly 900–1,100 kWh/kWp/year; sunnier climates can reach 1,400 or more.
• Self-consumption rate: The fraction of generated solar electricity that is used immediately in the building rather than exported. A household that uses 40 % of its solar output has a self-consumption rate of 0.40.
• Feed-in tariff (FIT) or export rate: The price per kWh paid to you (or credited on your bill) for electricity you export to the grid. This varies widely by country, utility, and scheme.
• Simple payback period: Total upfront cost divided by annual net saving. It ignores the time value of money but gives a quick headline figure.
• Discount rate: The annual percentage rate used to convert future money into today's value. A higher discount rate makes future savings worth less today.
• Net Present Value (NPV): The sum of all discounted future cash flows minus the initial investment. A positive NPV means the project creates value at the chosen discount rate.
• Panel degradation: Solar panels lose a small amount of efficiency each year, typically around 0.5 % per year. After 25 years a panel rated at 100 % efficiency might operate at around 88 %.
• System lifetime: The period over which savings are projected, usually 20–30 years for quality panels.
• Inflation escalator: An annual percentage increase applied to electricity prices to reflect the assumption that energy costs will rise over time.

Prerequisites

• A Google account with access to Google Sheets.
• The GSheetPress GS2Web add-on installed from the Google Workspace Marketplace, or access via your GSheetPress account with a plan that includes the Calculator entitlement. The Calculator feature is a paid entitlement; confirm it is active under your plan before starting.
• A dedicated sample workbook created solely for this calculator. Do not use a workbook containing private financial data, because publishing requires setting sharing to anyone with the link as an Editor.
• Basic figures for your location: system size in kWp, expected specific yield, your electricity import tariff, and your export or feed-in rate. Contact your energy supplier or consult your country's solar energy association for verified figures. The EU PVGIS tool provides free location-based irradiance and yield estimates.

Spreadsheet design overview

The calculator uses seven user-facing inputs. Helper cells in a separate area of the sheet compute intermediate values such as annual generation and year-by-year savings. The result rows display simple payback, 25-year total saving, and NPV. All result formulas reference the runtime input cells (column G) so they update automatically when a visitor submits the web form.

Build the solar savings calculator

Step 1 — Create the workbook and install the add-on

1. Open Google Sheets and create a new blank spreadsheet. Name it something like Solar Savings Calculator – Sample.
2. In the menu bar choose Extensions → Add-ons → Get add-ons and search for GS2Web SaaS. Install it and grant the requested permissions. If your organisation has already installed the add-on, skip this step.
3. After installation, choose Extensions → GS2Web SaaS → Calculator → New Calculator.
4. In the dialog that appears, enter a name such as Solar Savings, set the number of inputs to 7, and choose whether to include a graph (this guide does not require one, so you may leave it off). Click Create.
5. GS2Web will generate a tab (sheet) with pre-built column headers in row 1: Labels, Input Type, Required, Data, Default, ToolTips, Inputs, Rules in columns A through H respectively. Do not rename or delete these headers.

Because you specified seven inputs, the template layout is: input rows 2–8, button row 9, merged Result Fields separator row 10, result headings row 11, and result values starting from row 12.

Step 2 — Configure the seven input rows

Work through each row below. Column G (Inputs) is populated at runtime by web form submissions. Column E (Default) holds the value that pre-fills the form. To test formulas before publishing, copy the values in E2:E8 and paste them into G2:G8 using Paste special → Values only; this avoids overwriting formulas. Never store result formulas in column G.

Row 2 — System size

• A2 — Label: System size (kWp)
• B2 — Input Type: Number
• C2 — Required: Tick the Required checkbox
• D2 — Data: Leave blank
• E2 — Default: 5
• F2 — ToolTips: Enter the total rated output of your solar system in kilowatt-peak (kWp).
• H2 — Rules: Leave blank

Row 3 — Specific yield

• A3 — Label: Specific yield (kWh/kWp/year)
• B3 — Input Type: Number
• C3 — Required: Tick the Required checkbox
• D3 — Data: Leave blank
• E3 — Default: 1050
• F3 — ToolTips: Annual energy produced per kWp. Check PVGIS or your installer's estimate for your location.
• H3 — Rules: Leave blank

Row 4 — Self-consumption rate

• A4 — Label: Self-consumption rate (0–1)
• B4 — Input Type: Number
• C4 — Required: Tick the Required checkbox
• D4 — Data: Leave blank
• E4 — Default: 0.4
• F4 — ToolTips: Enter a decimal between 0 and 1. For example, 0.4 means 40% of your solar output is used in the building.
• H4 — Rules: Leave blank

Row 5 — Import tariff

• A5 — Label: Import tariff (currency per kWh)
• B5 — Input Type: Number
• C5 — Required: Tick the Required checkbox
• D5 — Data: Leave blank
• E5 — Default: 0.28
• F5 — ToolTips: The price you pay per kWh from the grid. Check your electricity bill for the current unit rate.
• H5 — Rules: Leave blank

Row 6 — Export rate

• A6 — Label: Export rate (currency per kWh)
• B6 — Input Type: Number
• C6 — Required: Tick the Required checkbox
• D6 — Data: Leave blank
• E6 — Default: 0.08
• F6 — ToolTips: The price paid for each kWh you export to the grid. Check your feed-in tariff or smart export guarantee rate.
• H6 — Rules: Leave blank

Row 7 — Total installed cost

• A7 — Label: Total installed cost (currency)
• B7 — Input Type: Number
• C7 — Required: Tick the Required checkbox
• D7 — Data: Leave blank
• E7 — Default: 7500
• F7 — ToolTips: Full upfront cost including panels, inverter, mounting, and installation. Obtain quotes from certified installers.
• H7 — Rules: Leave blank

Row 8 — Annual maintenance cost

• A8 — Label: Annual maintenance cost (currency)
• B8 — Input Type: Number
• C8 — Required: Tick the Required checkbox
• D8 — Data: Leave blank
• E8 — Default: 150
• F8 — ToolTips: Typical annual costs for cleaning, inverter warranty extension, and inspection. Leave as 0 if you have no maintenance plan.
• H8 — Rules: Leave blank

Step 3 — Verify the button row

Row 9 is the generated button row. Check that it reads: A9 = Calculate (or similar label already filled in), B9 = Button, D9 = main. Cell E9 should read Reset — this is the reset button label; do not change it. Leave all other cells in row 9 as generated. Do not delete or modify this row.

Step 4 — Add result row headings

Row 10 is the merged Result Fields separator generated automatically. Row 11 is the result headings row. Click cell A11 and type the heading label for your first result. You need three result rows, so add the following headings:

1. Click A11 and type: Result
2. Click B11 and type: Value

Results will occupy rows 12, 13, and 14, with labels in column A and formulas in column B.

Step 5 — Add result labels

1. Click A12 and type: Year 1 net saving (currency)
2. Click A13 and type: Simple payback period (years)
3. Click A14 and type: 25-year NPV (currency)

Step 6 — Add helper calculations in a separate area

To keep the result formulas readable, place intermediate calculations in an unused area of the same sheet, for example columns J and K starting at row 2. These cells are not visible to web visitors; they simply hold intermediate arithmetic that the result formulas reference.

The key intermediate values are:

• Annual generation (kWh): system size × specific yield
• Self-consumed energy (kWh): annual generation × self-consumption rate
• Exported energy (kWh): annual generation × (1 − self-consumption rate)
• Import saving (currency): self-consumed energy × import tariff
• Export income (currency): exported energy × export rate
• Year 1 gross saving (currency): import saving + export income
• Year 1 net saving (currency): year 1 gross saving − annual maintenance cost

Add labels in column J and formulas in column K:

1. Click J2 and type: Annual generation kWh
2. Click K2, type the following formula, and press Enter:

   =G2*G3

3. Click J3 and type: Self-consumed kWh
4. Click K3 and enter:

   =K2*G4

5. Click J4 and type: Exported kWh
6. Click K4 and enter:

   =K2*(1-G4)

7. Click J5 and type: Import saving
8. Click K5 and enter:

   =K3*G5

9. Click J6 and type: Export income
10. Click K6 and enter:

    =K4*G6

11. Click J7 and type: Year 1 gross saving
12. Click K7 and enter:

    =K5+K6

13. Click J8 and type: Year 1 net saving
14. Click K8 and enter:

    =K7-G8

Step 7 — Add the NPV helper table

NPV requires discounting each year's net saving back to today's value. This guide projects 25 years and applies a fixed discount rate of 5 % and a panel degradation rate of 0.5 % per year. Because both rates are fixed assumptions rather than user inputs, they are hard-coded into the formulas. If you wish to make them inputs, add two more input rows and reference the corresponding G cells instead.

Place the year-by-year table starting at column M:

1. Click M1 and type: Year
2. Click N1 and type: Degraded net saving
3. Click O1 and type: Discounted saving

Now enter years 1 through 25 in column M. The quickest method is:

1. Click M2 and type 1. Click M3 and type 2. Select M2:M3, then drag the fill handle (the small square at the bottom-right of the selection) down to M26. This fills years 1 to 25.

In N2 enter the degraded net saving for year 1 (no degradation yet):

=K8*(1-0.005)^(M2-1)

This formula multiplies the year 1 net saving by (1 minus 0.5 %) raised to the power of (year minus 1). In year 1 the exponent is zero so the value equals K8 exactly. In year 2 it is multiplied by 0.995, and so on.

Select N2 and copy it down to N26 (covering years 1–25).

In O2 enter the discounted value:

=N2/(1+0.05)^M2

Select O2 and copy it down to O26.

Step 8 — Enter the three result formulas

Now populate the result cells that the web calculator will display. These reference the helper cells you built above.

1. Click B12 and enter the Year 1 net saving formula:

   =IF(G2*G3=0,"Enter inputs to calculate",ROUND(K8,2))

   This checks whether the two most essential inputs have been entered. If not, it shows a message rather than a confusing zero or error.

2. Click B13 and enter the simple payback formula:

   =IF(K8<=0,"Check inputs – saving must exceed maintenance cost",ROUND(G7/K8,1))

   Simple payback = total installed cost ÷ year 1 net saving. The formula guards against a zero or negative denominator, which would occur if maintenance cost equals or exceeds the gross saving.

3. Click B14 and enter the NPV formula:

   =ROUND(SUM(O2:O26)-G7,2)

   This sums the 25 discounted annual savings and subtracts the initial installed cost. A positive result means the project returns more than the 5 % discount rate over 25 years at these assumptions.

Step 9 — Test before publishing

To test the formulas with the default values:

1. Select E2:E8.
2. Copy the selection (Ctrl+C or Cmd+C).
3. Click G2.
4. Choose Edit → Paste special → Values only.
5. Check that K2 through K8 and B12 through B14 show plausible numbers (see the worked example below).

Worked example

Using the default inputs:

Intermediate calculations:

Results:

Alternative scenario: Increase self-consumption to 0.70 (a household with daytime occupancy or EV charging). Import saving rises to 5,250 × 0.70 × 0.28 = 1,029. Export income falls to 5,250 × 0.30 × 0.08 = 126. Year 1 net saving = 1,029 + 126 − 150 = 1,005. Simple payback improves to approximately 7.5 years and NPV rises substantially, illustrating why maximising self-consumption is financially significant.

For a deeper look at how structured calculators handle complex financial inputs, see the GSheetPress Calculator feature overview.

Result checks

• If B12 shows zero when G2 and G3 are non-zero, check that K2's formula correctly references G2 and G3 rather than E2 and E3.
• If B13 shows a very large number, the annual net saving (K8) is very small. Verify that the maintenance cost in G8 is not larger than the gross saving in K7.
• If B14 is a large negative number, the NPV sum in O2:O26 is smaller than G7. At a 5 % discount rate and low tariffs this can be genuine — it means the project does not break even in present-value terms. Consider whether your tariff assumptions are realistic.
• If column O shows errors, check that M2:M26 are numbers (1 through 25), not text.

Frequently asked questions

Can I add electricity price inflation to make the model more realistic?

Yes. Add an eighth input row for annual electricity price escalation rate (for example, 0.03 for 3 % per year). In the NPV helper table, replace the fixed import tariff in the degraded saving formula with one that compounds each year. For year Y the effective tariff becomes the base tariff multiplied by (1 + escalation rate) raised to the power (Y − 1). Reference the new G cell for the escalation rate. This makes the NPV more sensitive to energy price assumptions, so document the assumption clearly in the calculator's tooltip.

How do I account for a solar loan rather than an upfront cash purchase?

Add inputs for loan amount, annual interest rate, and loan term in years. Use Google Sheets' PMT function to compute the annual loan repayment: =PMT(annual_rate, term_years, -loan_amount). Subtract the annual repayment from the gross saving in place of (or in addition to) the maintenance cost to get a net annual cash flow. The simple payback concept changes when financing is involved — it is more meaningful to track the year when cumulative net cash flows turn positive. The GSheetPress Table feature can display a year-by-year cash flow schedule alongside your calculator.

Why might my published calculator show different numbers from my sheet?

The most common cause is that the web form is populating column G with values, while your sheet formulas accidentally reference column E (the defaults). Confirm that every helper formula in columns K and O references G2 through G8, not E2 through E8. A second cause is that column G contains leftover text or formula content from testing; clear G2:G8 before publishing, then paste the defaults from E again using Paste special → Values only to re-verify.

Troubleshooting

If you encounter add-on issues not covered here, the GSheetPress support page provides contact and documentation resources.

Publish and test your web calculator

1. Share the workbook for publishing. In Google Sheets click Share (top-right). Under General access change the setting to Anyone with the link and set the role to Editor, then click Done. Important: this setting allows anyone who obtains the spreadsheet link to edit its contents. Use only the dedicated sample workbook — never a file containing personal or confidential data.
2. Open the Calculator publish dialog. With the calculator tab selected, choose Extensions → GS2Web SaaS → Calculator → Publish Calculator. Wait for the confirmation message.
3. Retrieve the public link. After confirmation, choose Calculator → Get Links (or the equivalent option in the add-on menu). This opens or refreshes a sheet listing your published calculators. Find the row where the Sheet Name column matches your calculator tab name.
4. Open the public URL. In that row, use the link in column C (Link) — not column B (Edit Link). Column C is the visitor-facing public URL. Click it to open the live calculator in your browser.
5. Test with the worked example inputs. Enter the default values from the worked example above: system size 5, specific yield 1050, self-consumption 0.4, import tariff 0.28, export rate 0.08, installed cost 7500, maintenance 150. Click Calculate. Confirm the results match: year 1 net saving 690.00, simple payback approximately 10.9 years, and a positive 25-year NPV.
6. Test an alternative scenario. Change self-consumption to 0.70 and confirm the payback improves to approximately 7.5 years as described in the worked example.
7. Embed optionally. If you want to embed the calculator in a website, copy the iframe code from column D of the Links sheet and paste it into an HTML block on your site. See the GSheetPress plans page for embedding entitlements.
8. Republishing. If you update formulas or labels and need to republish, repeat the Publish Calculator step. The public link in column C remains unchanged; visitors will automatically see the updated version.