Solar PV ROI Calculator

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.

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

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.

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

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.

Check your pm. ;)