Solar PV ROI Calculator

JHet · 20-10-2022 11:28pm #1

Solar PV ROI Calculator uses production data from PV GIS to model a variety of different ROI scenarios.

PV GIS: https://re.jrc.ec.europa.eu/pvg_tools/en/tools.html

Features include:

API support for PV GIS.
- Enter your data in terms Latitude, Longitude, Database, Losses, Slope, and Azimuth
- Once all data is entered click the Data tab -> Refresh All or CTRL+ALT+F5
Support for up to 2 Strings (e.g. east/west, north/south etc) including a configurable split of kWp between strings
Modelling for many different scenarios including:
- PV Only
- PV and Battery
- Battery only
Daily/Monthly and Annual Import/Export requirement based on consumption
Support for Day Rate, Night Rate, EV Point Rate and FIT/CEG
Projections based of the changes in Unit Rate, system size, and system costs per kWp and per kWh etc
The link is currently private but has been shared with a number of experienced members who have provided significant feedback. Just PM me if you are interested in providing feedback to help improve the tool and I can share a link.
The current version in circulation will likely be the last excel version. For the next releases I will seek to port the current solution to google apps. There will likely be a bit of a gap between releases as a result.

idc · 21-10-2022 8:55am

I'd suggest change night and FIT to be fields to fill in. They are not fixed percentages of day rates.

Does this take into account filling the battery from grid in winter to use during the day? even just allowing for a different percentage per month. eg 0 in summer, but say 40% spring/autumn and 80-100% in winter

JHet · 21-10-2022 10:13am

https://www.boards.ie/discussion/comment/119773926#Comment_119773926

I can make those fields configurable, that's easy. I agree with you on the FIT but for the night rate I think this will usually scale with the day rate, maybe not linearly, but I think its a reasonable heuristic for the purposes of this exercise, we can change the percentage though.

Not exactly. Its based off the estimated daily and monthly consumption taken from the European Commission site. It assumes if you have an estimated production shortfall, and you have a battery, that you will charge with the night rate to meet the shortfall (whatever that % ends up being), where your battery is big enough or take a combination of the day and night rate where the battery is not big enough (so optimal conditions - probably not real world I know.) You can't configure the %'s you charge the battery yet per se, it just assumes you'll charge to match the % required to meet estimated average daily consumption for that month.

JHet · 24-10-2022 3:58pm

For those I have shared the link there is a new version up:

Replaced time of use losses with "Charging/Discharging Losses"
Made various fields more granular
Changed Night and FIT fields from fixed percentage to configurable
Added Night and EV Import in kWh
Added EV point Rate in €
Added a google maps link based on your long/lat
Added more columns - see the legend in file for an explanation.

MODS: is there any way I can update the original post?

JHet · 25-10-2022 6:52pm

MODS: is there any way I can update the original post?

New version up with a new link also. I've sent it to the people who pm'd but I have a lot of messages so if I missed you just send me a quick pm:

Added API support for PV GIS. You will no longer need to download a CSV and paste your data in from the website.

Make sure to Enable content when you first enter the spreadsheet

Enter your data latitude, Longitude, Database, Losses, Slope, and Azimuth

Once all data is entered click the Data tab -> Refresh All

This will pull in your data direct from PV GIS. Note: YOU ONLY NEED TO DO THIS ONCE. Probably best we don't hammer the site with API requests. :) The sheet will scale the data from here for any modelling you needs.
Added support for 2 Strings (e.g. east/west, north/south etc)
- Change the Strings dropdown to 2

Input the Slope and Azimuth for String 2 (S2) also. Make sure to configure as per PV GIS. They take South not North as the origin i.e. 0 = South so everything is relative to South.

Added support to configure the kWp split across strings. For example 50/50, 60/40 etc. S1 is the first figure and S2 is the second figure.
- Use the scroll bar to select the correct % split between Strings for your system.

Lots of other minor enhancements

Manion · 25-10-2022 8:08pm

Hey

Thank you for the link to the document. It's very cool and I like how I can play around with unit cots and see how the RoI changes. Your figures are coming out at 9.5 Years for ROI for me which is very similar to what I calculated however I wondering about the assumptions around the battery as I've included factors missing from the sheet.

The sheet seems to be assuming that regardless of battery size (or even if it exists) that you'll consume 100% of what is produced. Predicting this is hard but SEAI have a stat that on a pure PV system 40% of power will be exported to the grid due to the lumpy nature of generation. The big purpose of the battery is to spread out that consumption to increase your overall consumption rate. There are probably assumptions you can make such that if someone has ~40% of there daily production covered by a battery then they will export zero to the grid, but then you should probably exclude E_d * .4 of the battery capacity from your consumption offloading calculation, if that makes sense? Since you'll need to keep that capacity free to make the most use of your solar.

I do like your House Battery charging requirement (I guess to hit 100% solar + Night rate?) I was missing what NR_d means though?

In my own sheet I factored in inflation for Electricity costs, I believe in economics it's called the future value of current consumption. See below. The cost of the system remains fixed over the payback period but you've effectively compound interest on electricity costs each year. I took a very optimistic view of average 4% increase year on year.

JHet · 25-10-2022 10:30pm

Thank you for the link to the document. It's very cool and I like how I can play around with unit cots and see how the RoI changes. Your figures are coming out at 9.5 Years for ROI for me which is very similar to what I calculated however I wondering about the assumptions around the battery as I've included factors missing from the sheet.

Thank you for taking the time to provide feedback. That's exactly the type of critique I'm looking as I don't have a system myself yet, I expected that there would be gaps in my assumptions, but its a great learning opportunity before I take the plunge myself.

The sheet seems to be assuming that regardless of battery size (or even if it exists) that you'll consume 100% of what is produced. Predicting this is hard by SEAI have a stat that on a pure PV system 40% of power will be exported to the grid due to the lumpy nature of generation. The big purpose of the battery is to spread out that consumption to increase your overall consumption rate.

No exactly, as it does provide for excess production which goes back to grid with FIT/CEG. I suppose the overriding assumptions are that the user will:

Seek to estimate their daily consumption and production
Calculate what the daily delta between the two is
Charge the battery to meet demand (where required)
Excess is sold (probably a weak assumption for those with big batteries - that could be improved)

Yes, these are the exact type of things I need to build in, a factor to deal with this lumpy nature of generation, that is something that is definitely not catered for YET. So the fact that 40% of power will go back on a pure PV is obviously a major disadvantage of not having battery. I did have a time of use losses in a prior version which basically calculated a the E_d * (% Loss factor * (Day Rate - FIT) and that was applied as a penalty for those without batteries but it didn't seem right to me so I took it out. There is probably a much better way to do it.

There is probably assumptions you can make that if some has like 40% of there daily production covered by a battery then they will export Zero, but then you should probably exclude E_d * .4 of the battery capacity from your consumption offloading calculation, if that makes sense? Since you'll need to keep that capacity free to make the most use of your solar.

That seems like a reasonable approach. The 40% essentially acts as a float, allowing the battery to trickle charge in a lumpy fashion to meet the remaining demand. That could definitely be built in. So that 0.4 of the daily demand essentially needs a reserve in the battery, and I suppose depending of the size of your setup that could be represent a tiny amount of your battery of perhaps it far exceeds your battery capacity.

I do like your House Battery charging requirement (I guess to hit 100% solar + Night rate?) I was missing what NR_d means though?

NR_d and NR_m are hidden. It was getting a bit cluttered, although you can unhide them in the new version. NR_d is the night rate daily consumption for those with night meters who know their usage and haven't transitioned to solar yet. CR_d is just really the daily delta with a penalty applied for losses which is configurable. I take night and EV import out of these calculations as you're not going to charge your battery to use at night with the same night rate, similiarly with the ev point rates, they to be very low in some cases lower than the night. The one thing I wasn't sure about was whether these EV point rates were restricted to the charge point only or if you could essentially use that rate to charge your battery. Incidentally, I didn't build it that way, its assumes the night rate if you need to charge the battery to meet a shortfall 1) because i wasn't sure and this was the safest approach 2) if providers cottoned on that people could do that I'm sure the loophole would be closed pretty sharpish. If someone knows please post here though.

In my own sheet I factored in inflation for Electricity costs, I believe in economics it's called the future value of current consumption. See below. The cost of the system remains fixed over the payback period but you've effectively compound interest on electricity costs each year. I took a very optimistic view of average 4% increase

I had thought about adding a inflation factor, I've seen this done in other peoples sheets. That would be relatively easy to add it I think, although to be the devils advocate I should allow for the factor being negative (deflation), there is a obviously generally an inflationary bias, but we live in strange times, who knows what's round the corner. It will be fun to play with the figures positive and negative in any event.

Manion · 26-10-2022 8:27am

OK thanks for the reply, I'm still not sure about CR_d being Db - EV-d - NR_d, specially why NR_d is being taken away (or in the calculation). I just zero'ed out the EV Point Rate as I charge my EV on the night rate. I guess this relates to a very specific Smart meter tariff? In the fine print for those deals you'll often find they are limited to 80 units or less a month import at the discounted rate. A user of the sheet would just need to be aware of any upper limit.

On suggestion, which you'll probably address when you move to google sheet would be to limit to two places of decimal points what shows (not the calculations but what appears in the table).

JHet · 26-10-2022 11:26am

If you are getting an Error connecting to PV GIS:

Go to Data tab -> Get Data -> Query Options

Select privacy and select Privacy -> "Always ignore privacy level settings"

Note: this just relates to the outbound query to PV GIS, not anything else. Excel gets fussy around privacy and API's but there is no privacy issue here as its data that's is publicly available. You can always set it back once you're done with the sheet. It won't be an issue once I go to the cloud.

Let me know if this solutions works for you. Thanks

JHet · 26-10-2022 11:36am

https://www.boards.ie/discussion/comment/119794295#Comment_119794295

Yes - very specific, some people got in early on the new tariffs for ev point rates, I believe energia was one of the providers. Some even got fixed price, not fixed discount, which is an quite a deal at the moment. However I think a lot of those deals are closed now, someone correct me if I'm wrong. They may well be restricted as you say in the t&c's, to be honest I don't know, it was another user who requested the feature.

On the dp's yeah that's no problem, I agree, it really doesn't add any value being able to see more than 2.

Manion · 26-10-2022 1:46pm

Thanks. I had internalized D_d as daytime as opposed to daily, I now see why you're removing NR_d from the CR_d calculation.

After playing around a fair bit, does the sheet factor daytime demand being met from the "night units" stored in the battery if the battery can only partially meet the daytime demand. Consumption shifting/off loading is a significant factor, I estimate it will generate a cost savings of 400 euro a year resulting in 2.2 years shorter payback period (for me) but with the same set up your sheet is saying it's only worth 180 Euro to me.

It worth noting that the user of the spreadsheet should adjust upwards NR_d and downwards Day rate if using batteries to off load daytime consumption. Off loading is a significant complication because in the scenario where you're using the batteries to offload a lot of the daytime consumption the payback period starts getting longer because Solar more and more offsets the night rate costs not the daytime costs. @unkel might have some insights to share on this as he's hitting nearly 100% offloading of daytime consumption to Solar + Night.

Really you need to compare two different scenarios with the spread sheet. For instance without offload my Annual costs are ~3450 but with off loading 3K units of consumption a year, my Annual costs to ~300.

So my actual payback period is 6.5 years, not 9.63 since the actual costs in a world without this installation would have been 3.5K annually, not 3K. if that makes sense.

Without offloading

With offloading

After playing around with it:

Conclusions -

Still coming in around that 8-9 year pay back.

Should I get a bigger battery?

Probably, looks like 15Kwh would get me to 95+ of my daily consumption being met from night rate + solar with only a fractional increase in payback period.

Galego · 26-10-2022 3:44pm

Great work! I think it is a great tool. It will help a lot people see their ROI.

Thanks!

JHet · 26-10-2022 5:26pm

Thanks. I had internalized D_d as daytime as opposed to daily, I now see why you're removing NR_d from the CR_d calculation.

After playing around a fair bit, does the sheet factor daytime demand being met from the "night units" stored in the battery if the battery can only partially meet the daytime demand. Consumption shifting/off loading is a significant factor, I estimate it will generate a cost savings of 400 euro a year resulting in 2.2 years shorter payback period (for me) but with the same set up your sheet is saying it's only worth 180 Euro to me.

It does cater for this, yes. The Shrt_w_B (Shortfall with Battery) column caters to this scenario.

if CR_d < = 0 or Battery Size = 0, Return 0, i.e if you've no charge requirement or you've no battery return 0

if Battery Size >= CR_d, Return (Night Rate * CR_d * Days in Month) , i.e if your battery is big enough to meet the charge requirement for the day you charge the battery to meet the requirement with night rate .

And lastly your scenario, where the battery can only partially meet demand:

(Night Rate * Battery Size * Days in Month) + (CR_d - Battery Size) * Day Rate * Days in Month) , i.e if your battery is not big enough to meet the charge requirement for the day you charge the battery to full with the Night Rate and for the difference between the charge requirement and Battery size, you take the day rate.

It worth noting that the user of the spreadsheet should adjust upwards NR_d and downwards Day rate if using batteries to off load daytime consumption.

Ok, quick clarification the NR_d, which is a hidden column for others reading, is only really to capture current night rate consumption, and not offloading, perhaps that's not clear, its probably isn't actually. NI_d would probably have been a better name in hindsight but I didn't think anyone would look :)

So the original idea for the sheet was for people who don't have solar currently but seeking to acquire it, a green field kind of scenario. However I know its flexible, so can be used to model a lot of other things too.

So you have your AS-IS and your TO-BE. The AS-IS would be your current consumption, so conceivably someone without solar or a battery, could be running washing machines, dryers, dishwashers, bitcoin miners the sky's the limit overnight! So you may have some existing consumption there. If you have a night meter its easy to find out what that is, and similarly easy with a smart meter so you can simply pop that into the sheet (green field scenario). So basically all the import fields are essentially AS-IS fields.

The rate fields are a mix of AS-IS and TO-BE. They are used to calculate your current overall annual cost based on current consumption. However, they are also used for future modelling calculations so the Night Rate would be the main one obviously in relation offloading daytime consumption.

Off loading is a significant complication because in the scenario where you're using the batteries to offload a lot of the daytime consumption the payback period starts getting longer because Solar more and more offsets the night rate costs not the daytime costs. @unkel might have some insights to share on this as he's hitting nearly 100% offloading of daytime consumption to Solar + Night.

It certainly is a significant complication, and there are likely a myriad of other factors that could be incorporated to provide a more accurate view. I would love to hear from @unkel and others who have similar setups so that we might factor those elements in. Studying the results I was getting was certainly interesting. There is definitely a sweet spot when it comes to the battery, size, the size if array and ROI. Small Array with big battery, big array with small battery, same size system and even battery on its own were not in line with was I was expecting for ROI. Now that could be because the calculations are inaccurate or it could be that its just complex and hard to predict.

Really you need to compare two different scenarios with the spread sheet. For instance without offload my Annual costs are ~3450 but with off loading 3K units of consumption a year, my Annual costs to ~300.

So my actual payback period is 6.5 years, not 9.63 since the actual costs in a world without this installation would have been 3.5K annually, not 3K. if that makes sense.

Yeah, so further the above re NR_d, don't put your offloading in Annual Night Rate Import in kWh field - this is for current consumption patterns only. Your first image looks more like what you should be doing (assuming you had that much night rate consumption before installation). The sheet will calculate your offloading requirement for you in Shrt_w_B per above and you've factored in some losses so that will be up the ROI a bit, but there is still a bit of a delta there between 7.92 and 6.5 and would be interested in closing that down if you wanted to share more details offline. I wonder is it because you have an inflation factor built in?

After playing around with it:

Conclusions -

Still coming in around that 8-9 year pay back.

Yeah so that's probably wrong but hopefully the whole NR_d thing is clear now.

Should I get a bigger battery?

I would say it depends on what your goals are. For some they would be happy with a partial offsetting of import costs, for others they are striving for utopian 100% offset. If ROI is your main concern you may be willing to compromise on the the system. It depends. The biggest consideration if the latter is your main goal is how low you can get that per kWp and per kWh, which is predictable I suppose, but it really is most significant thing apart from the unit rate. I mean I was surprised how good a battery only situation could be, in particular if you go DIY, but then if unit rate goes towards €1 per kWh, solar is so much cheaper than your energy bill, it becomes a no brainer.

Probably, looks like 15Kwh would get me to 95+ of my daily consumption being met from night rate + solar with only a fractional increase in payback period.

Yep, I mean if you can optimise your offloading it really does start to make sense. One thing I haven't thought about is how easy it is to administer all that. Is it manual, semi manual or could it be fully automated? I won't know till I get a system but will certainly be seeking to automate as much as possible.

Manion · 26-10-2022 7:36pm

Ah yeah, I'm one of those engineering/data people, always looking at the workings ;).

OK I could see that the shortfall with battery was some what accounting for it, but the issue is the annual cost and savings are based on a fixed view of night time and daytime consumption, moving battery size up and down had no impact on the cost calculation, only the saving calculation. For instance I use 13 units a night currently, adding in a battery that will go to 21 units a night and my day time consumption will reduce by the corresponding amount.

In my set up I can off load 2.8K units annually (8 Kwh batter) at a rate difference of 16 cent giving a total of 410 Euro saving at 90% efficiency. I can generate 5K solar annually which as we said the sheet assumes 100% consumption is 1575, if get 1985 saving a year or a payback period of roughly 6.8 years, so that's the most optimistic. The most pessimistic is I have to keep 40% of my solar generation capacity free in the batter to maximise consumption giving a figure of 1720 Euro savings a year or 7.9. Which is in and around what the sheet is calculating in savings, hence my thinking that it's missing some of the offloading value.

This is complicated unfortunately because we have two variables which are inter-related when you have PV and a battery. I actually found that with one way of accounting for offloading additional batteries made little impact on RoI while positively shifting assumings around consumption (for my set up). I should say I've a north facing roof so the battery is a much bigger factor in making the system viable due to the fact I'll be under producing 5 months a year all the value of the system during that period will come from consumption off loading.

redmagic68 · 26-10-2022 10:09pm

Hi,

could I get a link to the roi calculator please. Looks very comprehensive.

thanks

paddy

JHet · 26-10-2022 11:11pm

https://www.boards.ie/discussion/comment/119797147#Comment_119797147

Ah those pesky summer units. Basically there are 736 units you can't offload because you have no charge requirement. Your daily delta is between 7 and 8 for May - July but 10 - 11 of these units are related to night consumption so the charge requirement is zero. That's 736 units of untapped offloading that can't be gained back and ultimately will have to be paid for later on.

So your real offloading units are 2184 and you lose 10% so 1965 max offloading units. Given you're maxed out offloading for the rest of the year, you are going to have to pay for those units as they are part of your consumption @ 31.5c.

After these adjustments we are quite close. Let me know if this makes sense.

Manion · 27-10-2022 11:46am

That's bang on, it shows the importance of peering up when doing complex tasks. The flaw in my thinking is clear now that you've pointed it out, I was so focused on what was happening in the winter months I wasn't thinking that I won't be charging the battery at night for daytime use when I'm generating a lot and in fact probably using that solar into the night time hours. Thank you.

What impact does another battery have?

An extra battery pushes out my RoI from 7.9 to 8.19, so 3.6 months on an 8 year ROI.

What Impact does a 40% increase in Electricity cost have?

This is actually less than what the current best deals are. Not looking forward to my renewal date. RoI reduces to 5.67 years

What Impact does a 40% decrease in Electricity cost have?

26cent daytime and 9cent night. RoI reduces to 9.15 and another battery pushes that to 9.33 years.

So I think I will go with the 12KwH battery scenario, for a total RoI of 8.19 years. When I layer on top inflation in electricity costs of 4% I get ~7.5 Years.

Thank you, this has been really helpful to copper fasten some decisions. I want to emphasis I really appreciate you putting it together and making it available.

johndoe11 · 27-10-2022 12:40pm

Thanks very much for the work on this, it is very good. I played around a bit with the first version, but have a bit of an issue with the second version. When I click "enable content" as advised above, it asks me for a further password for write access. I have tried read only, but this doesnt seem to update from the PV GIS website. Any idea what I'm doing wrong? Thanks.

Manion · 27-10-2022 12:43pm

You have to click the refresh data button to get it to pull in new generation stats. You need to do this everytime you change your setup(but not your total size)

mhtc · 27-10-2022 10:08pm

Hi @JHet, this looks great, fair play. Could I get a link please?

Thanks

Gerry · 28-10-2022 6:31am

Hi I'd like the link

redmagic68 · 29-10-2022 11:33am

https://www.boards.ie/discussion/comment/119798277#Comment_119798277

This is an excellent tool. One suggestion I might make If possible would be to give the user the ability to alter the monthly usage. While for simplicity averaging the annual usage across the months makes sense it would definitely affect the ROI as the high demand months will be November through March where production is lowest and people load swift with their battery, making a change to the battery economics and size required. Perhaps difficult and may not be appropriate to all it depends very much on your circumstance but for those of us with heat pumps and no EV this would likely be the case.

Again though a really really useful tool for people to check and alter their system design. Thank you.

Manion · 29-10-2022 12:33pm

I guess when the google sheets version goes up you'll be able to override it. I've found I'm using perhaps an extra 200 units a months in the winter months for the heat pump versus the summer month with the heat pump optimized to do it's start and legionnaires cycles on night rate so maybe 30 to 50 euro a month. It might be just the weather we're having these days but the heating is very seldom kicking on (A2 rated with heat pump and south facing front), though our heat pump is also providing hot water daily.

One thing the sheet allowed me to easily do was to play around with what the difference is between North, South, East and West Solar in a very easy way. I had started the thread: Is north facing solar a complete waste a while back and it reaffirmed what I'd worked out manually. For large systems at the right price point the the difference between North facing and East or West facing is only a 1 year on the RoI. Going from a 6Kw Solar with 4Kw battery system to an 8Kw Solar with a 8Kw battery system made no significant difference on RoI North facing (Still ~8 years) but for a East facing system the RoI went from 5.8 years to 6.7, added on nearly a year to the RoI. The big difference is that with you get a better spread of Solar generation from may right up to August with nearly 100% of daytime solar demands met, whereas with North facing the generation is much more tightly centered around July. North facing generating 21.7% less solar power annually than east and 33.9% less than South.

Given that the Installation cost is actually cheaper than the quotes I was getting in December and January this year for a bigger system (came up ~ at the guidance), and electricity has gone up nearly 50%, it seems like a sound decision.

redmagic68 · 29-10-2022 12:56pm

The instal and battery costs have risen but if your payback comes in between 8-10 years it’s a sound investment as the energy price rises are going to keep coming both for political and climate reasons. Batteries will help manage the peak tariffs also once were forced to smart meters and help balance the loss of our extended night rates in my opinion. Still stink it’ll be grid tied EV’s that will change everything though.

Manion · 29-10-2022 2:30pm

For me ROI is the key as opposed to absolute costs. Bigger systems are more likely to hit the RoI number I think but with bigger risk due to the higher outlay.

redmagic68 · 29-10-2022 4:24pm

https://www.boards.ie/discussion/comment/119800009#Comment_119800009

I've the same issue, when you click the data tab and refresh it gives an error with column s1 and then s2. the password to open is not the password to unprotect so you have to use it in read only mode. Any idea what I'm doing wrong as it wont import the updated solar production data.

Manion · 29-10-2022 4:32pm

You have to change privacy settings.

redmagic68 · 29-10-2022 5:26pm

https://www.boards.ie/discussion/comment/119808924#Comment_119808924

Still getting this

JHet · 29-10-2022 6:45pm

https://www.boards.ie/discussion/comment/119809117#Comment_119809117

Check your pm. ;)

Galego · 30-10-2022 10:16am

https://www.boards.ie/discussion/comment/119808021#Comment_119808021

I will be very curious to see your production in a north facing. I was always under the assumption that was 50% less than a true south when comparing apples with apples.

What slope are your panels going to be installed? Any potential shading from anywhere?

Manion · 30-10-2022 11:33am

Not to derail this thread as I've another about north facing but slope is about 22 degrees. No shading, not even a chimney, and no possibility of ever being in a shade. Perfect setup really 62 square meters. Just facing the total wrong way.