Build Your Own Solar ROI Calculator: Step-By-Step in Excel or Sheets

Introduction

Want to move beyond generic estimates and model your exact payback, NPV, and IRR?
Building your own solar ROI calculator in Excel or Google Sheets is easier than you think.

Set Up Your Input Table

Create a clear input section at the top of your sheet.

Parameter	Example Value	Notes
System Size (kW)	6.0	Total DC rating
Installed Cost ($/W)	3.00	Equipment + labour
Incentives ($)	5,000	Federal + provincial rebates/grants
Electricity Rate ($/kWh)	0.12	Your current blended rate
Inflation Rate (%)	2.0	Annual electricity-price increase
Degradation Rate (%)	0.5	Annual panel performance drop
Discount Rate (%)	5.0	For NPV & IRR calculations
Lifetime (years)	25	Typical panel warranty period

💡 Pro Tip: Name each input cell (e.g., Size_kW, Cost_per_W) to simplify your formulas.

Calculate Net Investment

Use this formula in a separate cell to compute the up-front Net Investment:

=(Size_kW * 1000 * Cost_per_W) – Incentives

Label it clearly—this value will feed your ROI and payback calculations.

Don’t want to build this?

Yearly Savings Formula

Use one row for each year (1 to 25). Here’s the formula:

=(Size_kW * 1100 * (1 – Degradation_rate/100)^(Year–1)) * (Electricity_rate * (1 + Inflation_rate/100)^(Year–1))

Breakdown:

• Size_kW × 1,100 gives first-year solar production (kWh)
• Subsequent years adjust for degradation & inflation

Let Solenery do the math:

Payback, NPV & IRR

Simple Payback Period

=Net_Investment / Year1_Savings

Net Present Value (NPV)

=NPV(Discount_rate/100, Range_of_Annual_Savings) – Net_Investment

Internal Rate of Return (IRR)

=IRR({–Net_Investment; Year1_Savings; Year2_Savings; ...})

Don’t want to crunch numbers?
Get your NPV and IRR instantly by entering your postal code on Solenery Insights.

Visualization & Sensitivity Analysis

Use Excel/Sheets tools to add:

• Line Chart: Cumulative savings vs. time
• Data Table: Two-variable table testing Inflation_rate vs. Degradation_rate

💡 Pro Tip: Add drop-down menus or sliders for input values to toggle scenarios.

Conclusion

Creating your own solar ROI calculator gives you full control over assumptions like:

• System size & cost
• Incentives
• Energy rate forecasts
• Degradation and discounting

Don’t want to build it yourself?