Do not convert to “blocks”.
A financial spreadsheet to evaluate solar panels for your house.
Here’s a guide for working out whether getting solar panels for your house is good financially. This page offers:
- A method of financially evaluating getting panels
- A detailed explanation of the calculations, so you could do them with a calculator if you wanted
- A free spreadsheet to do the calculations
- Ideas on how to get the 14 items of data
- Suggestions on how to draw conclusions based on the evaluation of your base case (your most likely case) and based on “variant cases”.
- For each variant case, you change one or two of the assumptions of your base case.
The home solar panel evaluation method
The annual income and savings from getting solar panels are:
- The money you save in a year by using your solar panel electricity and so avoiding buying electricity from the grid, and
- The income earned in a year, when you sell unused solar panel electricity to the grid
The annual costs are:
- The depreciation cost: Initially the panels are worth what you pay for them, the capital cost. At the end of the life of the panels, they are worth nothing. The annual depreciation in value of the panels is the capital cost divided by the years of panel life.
- The annual maintenance cost
The evaluation encourages you to compare:
- Getting solar panels
- Leaving your money in the bank, and
- Paying off your mortgage.
The computer spreadsheet
You can follow the calculations described here with a hand calculator or use the computer spreadsheet provided here.
You can open my “home solar panel evaluation spreadsheet” on your computer by clicking here.
- After you open the spreadsheet, save it to a folder on your computer where you can insert your data, assess results – and find it later.
- The spreadsheet will run entirely on your computer
- It has no ability to send your data elsewhere
- The spreadsheet is an xls file created by Microsoft Excel
- Other spreadsheet programs may be able to read this
- It’s free / No cost / No obligation
- You can change this spreadsheet to suit your own situation.
Note: I corrected a bug in the spreadsheet, in the electricity price calculation, on 4 Nov 2015.
These evaluations are not easy
Most often people want to financially evaluate an item before they purchase. However, this is when they probably have little experience of the item – and it makes the evaluation more difficult. This is certainly true for the purchase of solar panels. It can be difficult to understand:
- Solar panel performance
- Their patterns of electricity use, which influence how much panel electricity they will use
- Spreadsheet programs – that can give a sensible evaluation framework, do the arithmetic and produce statistics like percent return per year
- Evaluating financial decisions using spreadsheets and statistics like percent return per year.
Evaluation by a panel supplier
Some people find it easier to find a panel supplier who will do the evaluation for them along with the quote for the system. The downside of this is people are still confronted with trying to understand the evaluation, its performance figures and graphs, and the assumptions embedded in the evaluation. And the supplier or salesman might choose assumptions that make the panels desirable.
A report from a supplier will often consider:
- A 25-year life span for the panels with each of the 25 years considered individually, and
- Only one possible future.
These financial analyses are comprehensive but can be hard to understand.
Supplier evaluation and then use this spreadsheet
Do consider this. Get a report from a supplier. Then run my spreadsheet, based on data you can extract from the supplier’s report. The advantages of doing this are:
- Getting data for the calculation is easier
- By extracting the data you check the supplier’s assumptions and conclusions
- You also focus on the key factors of panel economics which are identified by the spreadsheet
- The spreadsheet assumptions are clear. They are in the 11 items of data you enter.
- You can then vary some of the assumptions in the spreadsheet to see if the conclusions are robust to different assumptions, e.g. the assumed increase in electricity prices.
Solar panels an ok investment for me
I was surprised when the calculations showed that solar panels would be an ok investment for me. (That is in Melbourne Australia, October 2015.) This was because the income earned by new solar panels from selling electricity to the grid is was slashed to only about 6 cents per kilowatt-hour. This “feed-in tariff” had been about 66 cents/kWh.
What is covered by this panel evaluation
This “domestic solar panel evaluation” is viewed on one sheet of paper, as shown in the following table. Note:
- The information that you provide is shown by the word “data” in the right column of the table. And the data row text is coloured brown.
- In the spreadsheet, you insert your data, by changing the numbers in the brown data rows – and the spreadsheet immediately re-calculates and updates all figures.
- The rest of the figures are calculated, based on this data.
- The solar panel evaluation is on this sheet,
- So is the evaluation of a bank fixed interest investment.
|Home Solar Panel Financial Evaluation Tool|
|Average Daily Solar Electricity produced in first year||8.0||kWh/day||Data|
|Loss of Generation over system life||20%||Data|
|Av Daily Solar electricity over system life||7.2||kWh/day|
|Av Annual Solar Electricity Produced over system life||2628||kWh/year|
|Panel system purchase cost||$3,600.00||Data|
|Extra Costs like Meter change cost||$200.00||Data|
|Total cost of panel system||$3,800.00|
|Panel system life||25||years||Data|
|Depreciation cost per year over system life||144||$ / year|
|Current electricity cost from grid||24.37||cent/kWh||Data|
|Electricity price increase % a year (can be zero)||3.1%||% / year||Data|
|Electricity price increase over panel life||214.5%|
|Electricity price at end of panel life||52.28||cent/kWh|
|** Average electricity price over panel life||38.32||cent/kWh|
|Panel electricity used in home per day||3.00||kWh/day||Data|
|Panel electricity used in home per year||1095.00||kWh/year|
|Panel electricity used in home: % of panel generation||42%|
|Average total electricity used in home per day||9.50||kWh/day||Data|
|Panel electricity used in home: % of total electricity used||32%|
|From “Panel electricity used in home” & “av electricity price”|
|** Savings from avoiding buying electricity||$419.65||$ / year|
|Unused electricity sold to grid||1533.0||kWh/year|
|Income from sold panel electricity||6.40||cent/kWh||Data|
|** Annual Income from sold panel electricty||$98.11||$ / year|
|** Av Maintenance costs over system life||$50.00||$ / year||Data|
|Export income + Avoided costs – Depreciation – Maintenance||$323.76||$ / year|
|Return from solar panels: % of the system cost||8.5%||% / year||Result|
|Pay back period for solar panels||11.7||years||Result|
|Cost over system life including Maintenence||$5,050.00||$|
|Electricity generated over system life||65,700||kW|
|Cost of panel electricity over life of system||7.69||Cent/kWh||Result|
|***** Bank investment||3800|
|bank interest||2.7%||% / year||Data|
|bank interest before tax||$102.60||$ / year|
|Tax on the next dollar earned||36%||Data|
|Tax paid||$36.94||$ / year|
|Inflation rate||2.1%||% / year||Data|
|Loss due to inflation||$79.80||$ / year|
|Return after tax and inflation||-$14.14||$ / year|
|*** Return from bank investment||-0.4%||% / year|
The important information here is the “evaluation method”, not the results of the evaluation of my panels.
The solar panel system considered
Here I describe the solar panel system. I ordered the system through my local council “solar panel bulk buy program” with Positive Charge, SunEdison.
- 2 kW System of 8 panels
- On a north-facing second story tile roof in Melbourne Australia
- The angle of tile roof about 25 degrees
- Inverter: Sungrow 2kW
- No microinverters as there is no shading
The installing company:
- Guarantees the annual system production at a figure set on installation
- Compensates financially if there is any shortfall
- Services all your component warranties
- 25-year panel output warranty
- 10-year inverter warranty
- 10-year mounting system warranty
- 10-year installation workmanship warranty
The unit of electrical energy: kilowatt-hour
The unit of electrical energy used here is the kilowatt-hour or kWh.
You use one kWh of electricity when you run 10 of the old 100 Watt light globes for one hour.
You need to come up with your own data
You need to think through your own situation and come up with your own data. I do not have the experience to guide you with data. What I am providing is the “evaluation method” so you can do the calculations on your own data.
Look at the average year for your panels
This calculation examines a one-year period that is representative of the whole life of the solar panel system. In my base case, I used a system life of 25 years.
The average annual solar electricity produced over the life of the system
The electricity generated by your system will depend on factors including:
. Your location: latitude and climate
. The angle at which the panels are mounted on the roof
. The compass direction your panels face
. Shading of the panels
. The efficiency of your panels and inverter
The company that quotes for your system will know your details and be able to give you an estimate for “the average daily solar electricity produced in the first year of operation”. For my system, I put this to 8 kWh/day.
The system will degrade to over the 25 years. I used a 20% loss of generation over the life of the system. Again the company quoting for your panels could help here.
The average year will suffer half this loss, a 10% loss and so produce 90% of the “average generation in the first year”:
The electricity produced in the average year
= (100% – 0.5 * 20%) of 8 kWh/day
= 90% of 8 kWh/day
= 7.2 kWh/day
So the average annual solar electricity produced over the system life
= 365 * 7.2 = 2628 kWh/year
The system cost
Installation cost from my quote = $3,600
Meter reprogramming cost = $200
Total cost = $3,800
My two storey tile roof made installation expensive. For a one-story, tin-roof house the cost would have been:
= 3160 + 200 = $ 3360
Expected system life and depreciation cost
Solar panels wear out over time and eventually need replacing.
This can be considered as a yearly depreciation cost
= “Equipment cost” divided by the “Life of the equipment”
= 3600 / 25
= $ 144 a year.
The cost of buying electricity from the grid
When I buy electricity from the grid, my electricity supplier offers me a choice between (1) Green power at 30.36 cents/kWh and (2) Non-green power at 24.37 cents/kWh. This will not change when I get my panels going. My electricity supplier is Power Shop.
So, “Current cost of electricity from the grid”
= 24.37 cents/kWh
Electricity prices have risen sharply over the last few years, largely due to very large expenditure on the poles and wires of the electricity grid. I expect that the price of electricity will continue to increase faster than the current inflation of 2.1%. ( I discuss estimating inflation below.)
When we avoid using grid electricity, by using solar electricity, our solar panels save us money. And with increasing electricity prices, the savings will increase over time. In my base case, I arbitrarily put the electricity price increase to 1% above inflation, to 3.1 % a year, to help estimate these increasing savings over time. With the cost increasing by 3.1% a year:
- After one year the price will increase to 1.031 times the initial price
- After 2 years the price will increase to 1.031 x 1.031 = 1.063 times the start price
- After 3 years the price will increase to 1.031 x 1.031 x 1.031 = 1.096 times the start price
- After 25 years the price will increase to (1.031 multiplied by itself 25 times) = 2.145 = 214.5% times the initial price
This gives an electricity price in 25 years
= 214.5% of 24.37
= 52.28 cents/kWh
The representative year of this 25 year period will have a price
= the average of the current and future price
= 38.32 cents/kWh
Solar panel electricity used in the home
The next bit of data is the “average panel electricity used in the home over the panel life”. My smart meter helped me with this. It sends daily readings to my electricity supplier. So I can see my electricity use as a graph, day by day, over the past year.
My average daily electricity use is about 9.5 kWh/day.
To identify a low value for the panel electricity I would use at home, I looked at a week period when we were away from home. Over this week we used 4.5 kWh/day. (That is 47% of our average daily usage and seems high.) So the use during daylight hours, while we were away, would be about half of this, 2.3 kWh. So a low value for “panel electricity used in the home” would be this 2.3 kWh each day.
I considered putting “panel electricity used in the home” to 4 kWh/day, but this would be 56% of the average panel generation of 7.2 kWh/day. This seems to be a high value as there is only one person home here during the day on weekdays.
For my base case, I used “average panel electricity used in the home” = 3 kWh/day
This gives 3 * 365 = 1095 kWh/year
This is 42% of the 7.2 kWh/day “panel electricity generated”.
and 32% of the 9.5 kWh/day “total electricity used”.
The savings from avoiding buying electricity
When you avoid using and buying grid electricity, by using panel electricity, you save 38.32 cents/kWh.
The saving from avoiding buying grid electricity
= the “solar panel electricity used in the home” x “the average electricity price”
= 1095 * 38.32 / 100
= $ 419.65 a year
Income from selling unused panel electricity to the grid
Unused panel electricity is exported to the grid
= “Total panel electricity generated”
minus “Panel electricity used in the home”
= 2628 – 1095
= 1533 kWh/year
When I export electricity to the grid, my electricity provider will pay me 6.4 cents/kWh. You could think that this price has decreased a lot and so might decrease further. Alternatively, you might think it will now increase as electricity costs increase. In these calculations, I assume it stays constant.
So the income from this export = 1533 * 6.4 / 100
= $ 98.11 a year
The cost of maintaining the panels
Some people say they’ve had panels for years without maintenance costs, but I will allow for calling an electrician out a few times and some equipment replacement. In my base case, I set the cost of maintaining the panels at $ 50 a year.
This is $ 1250 over the 25 years. If the inverter crashed after the 10-year warranty replacement might cost about $ 1200.
Result: The return from getting solar panels
The return from getting the panels
= The savings from avoiding buying electricity
Plus the income from selling unused panel electricity
Less the depreciation cost
Less the maintenance cost
= $ 323.76 a year
Return as a percentage of the invested capital
= 100 x 323.76 / 3800
= 8.5 % a year
Result: The payback period
The payback period is the number of years it takes before you have the capital outlay of $3,800 back in your pocket
= 3,800 / 457.19
= 8.3 years
Result: Cost of panel electricity over the life of the system
The cost of the panels = the initial capital cost plus the yearly maintenance
= 3800 + 25 x 50
= $ 5,050
The total electricity generated by the panels over the 25-year life = 25 * 2628
= 65,700 kWh
Cost of panel electricity = 100 x 5050 / 65700
= 7.69 cents / kWh
The inflation rate
For an evaluation of bank savings and as a guide to the electricity price increases, we need an estimate of the inflation rate. You could:
- View the Reserve Bank of Australia quarterly Consumer Price Inflation rates
- Web site www.rba.gov.au/inflation/measures-cpi.html
- Take an average of the last 8 quarterly inflation figures
- That gave me a 2.1 % a year inflation rate
- This assumes that past inflation is a guide to future inflation
Evaluate a 2.7% bank term deposit
If you decided not to get the panels and to put that money into a two-year bank term deposit, they now earn about 2.7 % a year
The bank interest before tax = 2.7% of 3800 = $102.60 a year
Assuming that the next dollar earned is taxed at 36%
Tax paid = 36% of 3800 = 36.94 $ / year
Just as depreciation diminishes the value of the solar panels, inflation diminishes the value of our savings. As discussed above I set this at 2.1 % a year.
Loss due to inflation
= 2.1% of the term deposit investment of $ 3,800
= 2.1% of 3800
= $ 79.80
So, return after tax and inflation
= 102.60 – 36.94 – 79.80
= Loss of $14.14 a year or loss of 0.37%
Evaluate a 2 % bank savings account
To evaluated putting money into a savings account earning 2 % a year, just change the bank interest to 2%, this gives:
The loss after tax and inflation
= Loss of $31.16 a year
= Loss of 0.82 % a year
Evaluate putting money onto a mortgage
At the moment mortgage interest is about 6% a year. When you deposit money to decrease this mortgage, there is no inflation impact and no tax, so the return is 6 % a year
Making sense of the results
A good way of using a spreadsheet like this is to:
- Set up a base scenario which evaluates your best estimate of your situation, and then
- Run variants on this base scenario, changing one bit of data at a time
- Identify the impact of your various assumptions
If buying panels gives a favourable return and this is robust to various changes in your assumptions then you can get panels with some financial confidence.
Variants from my base case
Here are some variants from my base case
- The base case gave a return of 8.5% a year
- Increasing the solar power used in the home from 3 to 4 kWh/day gave a return of 11.6%.
- Increasing the electricity price escalation from 3.1% to 4 % a year gave a return of 10.3%
- Decreasing the electricity price escalation from 3.1% to 2.1% gave a return of 6.9%
- Decreasing the electricity price escalation from 3.1% to 0% gave a return of 4.5%
- Reducing the panel electricity produced in year 1 from 8 to 7.8 kWh/day gave a return of 8.4 %
- Reducing the equipment life from 25 to 20 years gave a return of 6.5%
- Reducing the equipment life from 25 to 15 years gave a return of 4.0%
- Putting “Panel electricity used in the home” to zero with 25 life gave a loss of $ 25.81 a year or a loss of 0.7 % a year
- Putting “Panel electricity used in the home” to zero with 15-year life gave a loss of $ 122 a year or a loss of 3.2 % a year
Here’s a summary of the returns from options avoiding solar panels:
- Pay down your mortgage: 6.0 % a year
- Invest in a bank term deposit: Loss 0.4% a year
- Invest in a bank savings account: Loss 0.8 % a year
Conclusions from this analysis
Again, the important thing here is how to do the financial evaluation and how to use the results of the variants considered, not the conclusions based on my data.
- Paying down a mortgage gives a certain 6% return. That makes it attractive.
- Leaving money in the bank, paying that tax and with that inflation, is a certain loss maker.
- Getting home panels to export all the electricity loses $26 a year. I’d call that breaking even. But assuming a 15-year life it returns a loss of 3.2 %.
- Return from the panels varies from 4% to 11.6% a year. That seems like a worthwhile, robust return.
Notes on using the spreadsheet
If you mistakenly enter your data in the wrong spot and overwrite some of the calculation instructions, you can try “control z” to undo this. Alternatively, you can print the spreadsheet to keep a record of your data, then download the spreadsheet from this website again, and re-enter your data.
A simpler method “The energy freedom home”
A simpler method is described in “The Energy Freedom Home”, a book put out by “Beyond Zero Emissions” in 2015, page 76.
You provide the system cost ($3,800) and the nominal capacity of the system (2kW).
The book gives a “conversion factor” for each Australian state, for Victoria it is 296.
From this you can calculate:
Lifetime solar energy cost = 3800 / (2 x 296)
= 6.4 cents/kWh
This calculation assumes: optimal orientation of panels, no shading, a 25-year system lifetime, 80% of the rated performance at the end of life and no discounting of cash over time. The book costs $40.
Available from Beyond Zero Emissions
Sunulator: An economic evaluation tool for solar generation systems
A more complex tool for assessing the economic feasibility of solar generation systems is called Sunulator.
Sunulator is a free simulation tool that can help you plan a grid-connected solar project. Unlike most other solar calculators, Sunulator uses half-hourly consumption and generation data over a whole year to estimate how much solar generation will be consumed on-site versus exported. Based on electricity tariff information, it then calculates the impact on your electricity bill and projects the savings over a 30-year time frame. Financial results include payback period, net present value and return on investment. Sunulator allows you to compare the results for several scenarios, for example, different sizes of a solar array or panel orientations, or different amounts of battery storage.
Renew (was the Alternative Technology Association)
Sunulator a free solar feasibility calculator
Your comments on this page
If you have some comments on this please let me know. I have already changed this page significantly following feedback from readers.
Article by Andrew Gunner
Author of the Feedback Reigns website
Updated 15 Nov 2015