Spreadsheet to calculate payback time for solar hot water

Thanks to http://www.phillipmartin.info for the graphic.

I have put together a spreadsheet that models the following things for a household:

- hot water usage
- electric power consumption for heating this water
- cost of buying this electricity from Eskom by year with projected increases
- savings from using a solar geyser
- cost of financing the solar installation over a period
- a future projection to show break-even date

Here is the Google docs version of the spreadsheet model. Here is the Excel version.

Cells in green are inputs. Most of the defaults are set to "reasonable" values for a South African household. Important inputs to get right are the number of showers and baths run on an average day, the size of the hot water cylinder, and the thermostat setting (usually around 65 °C).

The sample data in the model comes from a colleague. His household uses around 5 showers per day and they have a 250 litre tank. The model tells us they use 395 litres of hot water each day, that this takes 11.8 kWh to heat, and that this costs R214 per month rising to R289 after the June 2010 Eskom increase. Assuming 80% of heating needs are met from solar power, the household could save R171 per month now, R231 after June.

If my colleague fitted a solar panel at an after subsidy cost of R13,000 and borrowed the capital at 10% over 10 years (e.g., he may have taken the money from his home loan), his repayment would be R171 per month over the 10 years, meaning the deal is cash neutral up till June, then he benefits R60 every month until the next increase when it goes up again.

Finally the table at the bottom of the spreadsheet shows that his savings will have completely offset the cost after 41 months, in June 2013.

Note that the Eskom subsidy will be reviewed (reduced) annually so you have a one-year window starting 11 Jan 2010 to benefit from the full rebate.