Solar ROI Calculator in Excel: A Step-by-Step Guide for Canadians

A solar ROI calculator in Excel or Google Sheets lets you test your own numbers instead of relying on a salesperson’s estimate. With one input table and a few formulas, you can estimate your yearly savings, payback period, net present value (NPV) and internal rate of return (IRR) for a home solar system in Canada. This guide walks you through each step, using NRCan’s solar data for your city.

Step 1: Set up the input table for your solar ROI calculator in Excel

Put every assumption in one place at the top of the sheet, so you can change it later. The example values below are for illustration only; replace them with the figures from your own quote and electricity bill.

Input	Cell name	Example value	Where to find it
System size (kW)	Size_kW	6	Your installer’s quote
Installed cost ($ per watt)	Cost_per_W	2.80	Your quote (total price ÷ watts)
Incentives ($)	Incentives	0	Programs you actually qualify for
Annual production per kW (kWh/kW)	Yield	1,163	NRCan photovoltaic potential for your municipality
Electricity rate ($/kWh)	Rate	0.13	Your bill: total cost ÷ kWh used
Electricity price increase (% per year)	Escalation	2	Your own assumption
Panel degradation (% per year)	Degradation	0.5	Panel warranty sheet
Discount rate (%)	Discount	5	The return you could get elsewhere
System life (years)	Life	25	Panel warranty term

Tip: in Excel, select a cell and type a name in the Name Box (left of the formula bar) so formulas can use names like Size_kW instead of cell references.

Step 2: Get your local solar production

Your production depends heavily on where you live. Natural Resources Canada publishes photovoltaic potential and solar resource maps with the expected yearly output per kilowatt of panels for more than 3,500 municipalities. For south-facing panels tilted at the latitude angle, NRCan lists, for example:

City	kWh per kW per year
Regina	1,360
Calgary	1,291
Winnipeg	1,272
Ottawa	1,200
Montréal	1,190
Toronto	1,163
Halifax	1,073
Vancouver	1,007

If your roof faces east or west, or is shaded, your output will be lower. Ask your installer for a site-specific production estimate and use that instead.

Step 3: Calculate your net cost

Every solar ROI calculator in Excel starts from what you actually pay after incentives.

=(Size_kW * 1000 * Cost_per_W) - Incentives

Name this cell Net_Investment. With the example inputs, it’s $16,800.

Step 4: Build a yearly savings table

Make a table with years 1 to 25 in column A. In column B, calculate each year’s savings:

=Size_kW * Yield * (1 - Degradation/100)^(A2-1) * Rate * (1 + Escalation/100)^(A2-1)

This reduces production a little each year for panel degradation and raises the value of each kWh for electricity price increases. In the example, year 1 savings are about $907.

Step 5: Calculate payback, NPV and IRR

Simple payback

=Net_Investment / B2

This divides the cost by first-year savings. It’s quick but ignores price increases. For a more accurate payback, add a cumulative savings column and find the year it passes your net cost.

Net present value (NPV)

=NPV(Discount/100, B2:B26) - Net_Investment

A positive NPV means solar beats your alternative investment at the discount rate you chose.

Internal rate of return (IRR)

Put the negative net cost in the cell above year 1 (for example, =-Net_Investment in B1), then:

=IRR(B1:B26)

IRR is the annual return your solar investment earns over its life.

Prefer not to build a spreadsheet? Enter your postal code to see local estimates.

A worked example (hypothetical)

These results come from running the illustrative inputs above through the solar ROI calculator in Excel described here, not from a real quote. A 6 kW system in Toronto at $2.80/W:

Scenario	Net cost	Year 1 savings	Payback	NPV at 5%	IRR
No incentive, $0.13/kWh	$16,800	$907	about 16.5 years	about −$2,000	about 3.9%
$5,000 incentive, $0.13/kWh	$11,800	$907	about 12 years	about +$3,000	about 7.2%
$5,000 incentive, $0.16/kWh	$11,800	$1,116	about 10 years	about +$6,400	about 9.6%

The example shows why your own inputs matter: incentives and electricity rates can swing the result from “not worth it at 5%” to a strong return.

Tips for a more accurate solar ROI calculator in Excel

• Use your real rate. Divide your total bill, including delivery charges, by your kWh. How much of that you actually save depends on your utility’s credit rules; see the solar interconnection process in Canada.
• Only include incentives you qualify for. Check current programs in NRCan’s directory of provincial and territorial incentives. For businesses, see our guide to commercial solar incentives in Ontario.
• Add costs. Include an inverter replacement partway through the system’s life and any maintenance you expect.
• Test scenarios. Use Excel’s Data Table (What-If Analysis) to vary the electricity rate and escalation and see how NPV changes.
• Check the size. Our guides to what kWp means and how much a 5 kW solar system produces help you sanity-check a quote.

The bottom line

A solar ROI calculator in Excel is only as good as its inputs. Use NRCan’s production data for your area, your real electricity rate, and only incentives you’re sure of, then compare payback, NPV and IRR across a few scenarios before deciding.

Frequently asked questions