Solar Energy Calculator

Here’s a free spreadsheet that will help you calculate your solar energy needs.

You simply plug in the data that applies to you, then the spreadsheet automatically calculates, for every day of the year, your energy situation.  Do you have enough energy generated each day for your daily needs?  Do you have spare?  If you have some energy storage, is it full, being filled, being emptied, or empty?

You can use it for ‘what if’ testing, for example, to see whether it would be better to add more solar panels or more batteries so as to get you through the worst of the winter months when you’re shortest of energy.

If you add the relative cost of adding extra solar capacity and adding extra energy storage, you can also see what the most cost-effective approach may be.

This spreadsheet will save you hours of time and potentially thousands of dollars of money.

Disclaimer :  We’ve been careful to design the spreadsheet and its formulas, but it is entirely possible there may be some ‘logic bugs’ in it which may create incorrect results.  When you are getting to the point of testing the most critical data, you should also do the calculations by hand.

If you believe you’ve discovered any errors in the spreadsheet, please let us know so we can correct them.

Always check that you are using the most up to date version of the spreadsheet, and download it from here only to be sure it is unaltered and valid.

Currently, our spreadsheet is version 1.0, released on May 12, 2014, and can be downloaded from here.

Instructions on How to Use the Spreadsheet

There are three tabs on this spreadsheet, named Constants, Daily, and Critical Month.  We have populated them with reasonably realistic data already (applicable to Coeur d’Alene, ID).

You should change all the values that are appear in orange, in the grey highlighted cells, to reflect your personal scenario.  To protect against inadvertently messing something up, we have protected the worksheets so that none of the other data can be changed by you (but if you really want to do so, you can simply type in the worksheet password to unprotect each sheet – the password is shown on the Constants page).

When you have put in all your values on the Constants page, you can first see the impacts that are shown at the bottom of that page – the results.

You can also see the day by day results by going to the daily page.  You should do this, and scroll up/down to find the month where your stored power is at its minimum (hopefully this is only for a few days of one month!).  This is your critical month, and we suggest you then run a daily test for that particular month, by entering in the values in the orange/grey boxes on the critical month page.  This gives you a much more careful and detailed ability to see just how much at risk you are of running out of power during this time.

For detailed instructions on how to enter and interpret the data in this spreadsheet, please click the three links below.

Detailed Instructions for the Constants Tab

Detailed Instructions for the Daily Tab

Detailed Instructions for the Critical Month Tab

Current Weaknesses in the Spreadsheet Model

1.  We are not happy with the way we handle days and months when no energy is being stored or added to the energy storage system.  On days when you use all the power you generate, we ignore your storage system and say that it is neither consuming nor providing energy.

Certainly it is true that it is not providing energy, but it might conceivably still be consuming energy.  An example of this would be a battery storage system.  You don’t want to ignore your batteries and do nothing with them for an extended time, or else they will continue to self-discharge to a ‘too low’ level and damage themselves.

On the other hand, with a water reservoir, if it is empty, it is empty, and there’s nothing more that needs to be done until you start pumping water back into it (assuming that the reservoir is of a design that can withstand the altered experience of being bone dry for days/weeks at a time).

It is probably acceptable to not do anything with your batteries for a week or even two, but if your model shows an extended time where there is no charge going into your storage system, then you will have problems which this model doesn’t yet allow for.

If you can think of other simplifications or omissions or issues that we should try to build into the model, please let us know and we’ll attempt to add them to a future version.

2.  On days when energy is being accumulated in whatever storage system you have, we do not consider if there are any limits on the maximum rate at which energy can be transferred into your energy store.  It is possible but not very likely that there are restrictions on how fast you can store the energy, and in a future version, we will add the ability to set a limit for this.

3.  We have a rather inelegant way of starting/ending the data series each year, with no provision for whatever power might be stored at the end of one twelve month period to be passed on to the start of the next twelve month period.  We have adjusted for that by attempting to start the series in a month when you will probably be accumulating rather than consuming energy, and most people will fill their energy reserve during the summer, whether they ‘carry forward’ the energy from last year or not, so currently this is not a major weakness in the spreadsheet, but it could be made more resilient if we had a ‘carry forward’ function.  We’ll add this in a future release.

4.  Currently the data is essentially done on a monthly basis, with large jumps occurring between the end of one month and the start of the next in terms of net energy flows.  This is of course not realistic, and while it doesn’t really matter too much when looking at an overall annual picture, it does make the specific day by day data slightly less accurate.

A future version of the model, while still based on monthly data inputs from you, will then blend and flow the monthly values, so that towards each end of each month, the numbers will start to move towards the values in the next month.

We don’t expect this enhancement will make major differences to the main conclusions and results, but it will ‘look nicer’ and feel more realistic at the daily level.

5.  Similar to point 2, for days with very low power generation, we don’t consider the implications of the power merely trickling in.  For example, if the power is being generated at a 100W rate, while that might total some useful amount of power, in theory, for a full day, it is useless if you want to run a 250W appliance, which could never be powered at any time during the day, short of routing the power generated into the storage system at a slow rate of charge then pulling it out quickly again at a higher rate of draw.

The implication of that is that if the energy has to go through a battery storage system to become usable, then we lose all the roundtrip power losses that are associated with being converted from electricity to battery/chemical energy and then back to electricity again.

So the ‘bad’ months are probably overstating the useful value of the energy being produced.

We’ll correct this in a future enhancement.