Key Post Spreadsheet for Calculating Electricity Bills

M

mathepac

Registered User
Messages
8,077
Speaking to friends, tracking electricity use, and calculating bills in advance seems like a black art to them. Reading posts on AAM over the years electricity bills seem to have proven problematic on a regular basis.

Below is a link to a simple Microsoft Excel spreadsheet that helps track your electricity usage in units and in money terms and calculates the bills based on currently published ESB rates.

To make the spreadsheet work, all you need to do is type in two numbers and two dates.
  • Under the blue heading "Last Reading" (this appears on your last bill) type in your last meter reading. The cell currently displays "20789".
  • Under the next blue heading labelled "Current Reading" type in your current meter reading. Type this into the cell that currently displays "21441".
Next type in two dates.
  • Type the date of your last meter reading, again from your last bill, into the cell that currently displays "19-Jul-2017" You can use "date shorthand" when typing e.g. typing "19/7/17" will display as "19-Jul-2017".
  • Type the date of your current reading (or today's date if you want to do a "between readings estimate" of your usage) into the cell that displays "2-Sep-2017".
If there was a rounding credit done on your last bill, type this into the row labelled "Credit or Debit from last Bill" where "-0.31" appears. (To create a minus number, type the "-" first then ".31". Watch out for that decimal point)

That's it. The Balance Due is displayed in the row labelled "Balance Due (if no SW Credits)" or if you are credited with units etc by the Dept for Social Protection's Household Benefits Package, the amount you pay is displayed in the row labelled "Net Bill to Pay (if SW Credits apply)"

If you have any issues, problems, or spot mistakes, or have ideas to improve this little spreadsheet or my instructions for using it, please post in this thread. Do not PM me as the idea of Askaboutmoney.com is that we all learn, so with Brendan's permission, I will not be responding to PMs about the spreadsheet.

Aha, they cry, what about the bit at the end? That's for the next two-monthly bill, to make it easy to enter the last bill's information onto the current one, and I've duplicated the sheet to give another 4 months' worth of bills (two billing cycles) in the workbook.


For the technically minded, the spreadsheet will work with all versions of Excel / Microsoft Office back to 1997 and up to Office for Mac 2022 16.63.1. I've been unable to test with current versions of Windows Office.

It has also been tested and works with:
  • IBM's Symphony
  • NeoOffice
  • LibreOffice
  • OpenOffice
 
Last edited:
Some information on the Household Benefits Package, which includes the electricity allowance, often referred to as the "free electricity".

Allowance:

If the SW customer is with Electric Ireland (formerly ESB), the allowance is 1800 units per year (300 per 2-month bill), plus the standing charge, the PSO (public service obligation) levy, and VAT on the levy.

If the SW customer is not with Electric Ireland, then the cash equivalent of the above allowance is paid directly to the customer and not to the utility company.

Qualifying Criteria:

(A) Age over 70 or
(B) In receipt of Carer's Allowance or
(C) Age 66-70 and in receipt of one of the following:
- State Pension (Contributory or Non-Contributory)
- Widows/Widowers/Surviving Civil Partners Pension (Contributory)
- Garda Widow's Pension
- Equivalent Social Security Pension from abroad or
(D) Age 66-70 and satisfy a Means Test or
(E) Age under 66 and in receipt of one of the following:
- Invalidity Pension
- Blind Pension
- Disability Allowance
- Incapacity Supplement with Disablement Pension (at least 12 mths)
- Equivalent Social Security Pension from abroad

For conditions C, D and E above, the customer must live alone or with certain excepted persons (generally an adult dependant/qualified adult, children, a carer or caree).

Link to the Household Benefits information on SW website:

http://www.welfare.ie/EN/Schemes/HouseholdBenefits/Pages/hb.aspx
 
I've just posted V2 which calculates daily and weekly electricity usage in units and in financial terms, making it a bit easier to see the effects of reduced usage on bills.
 
Thanks, but just to sound a note of caution. Watch out for the rate changes as of 1-Dec-2012 and the change in the way credits from the Household Benefits Package are applied in 2013, cash instead of units for ESB. I'll update both elements in the New Year.
 
I've updated the spread-sheet to reflect the ESB Electric Ireland electricity rate-changes from last year and the change in SW credits for this year.

A note of caution: I have been unable to access any information about how unused or excess credits are brought forward (or is that carried forward?) so the spread-sheet is still incomplete.
 
Changes to the spreadsheet:

To make the spreadsheet work, all you need do is type in two numbers and two dates.

  • Under the blue heading "Last Meter Rdng" (this appears on your last bill) type in your last meter reading. The cell currently displays "24462".
  • Under the next blue heading labelled "Current Meter Rdng" type in your current meter reading. Type this into the cell that currently displays "24750".

I have updated the spreadsheet to calculate:

  • Average Weekly Electricity usage in both units and money terms
  • Average Daily Electricity usage in both units and money terms and the next
  • Projected Bill at Current Run Rates (Note: both the date and the money amount shown here are ESTIMATES. Please don't panic if your actual bill is different.)
 
Last edited:
Update 6 June 2017

Two additional lines were inserted to allow for rate changes to "Units Used" or "Standing Charge" in the middle of a billing period.
 
Last edited:
Update 17 August 2022



Below is a link to the updated Microsoft Excel spreadsheet that helps track your electricity usage in units and in money terms and calculates the bills based on currently published Electric Ireland, EI, rates. If you use a different supplier, you may need to change the values in Column E headed "Price/Unit/Day". You'll also need to change these values as EI responds to energy price changes, which is the main reason for introducing the changes.


Rows 2 - 6 allow for up to five different electricity rates within any billing period, assuming you get prior notification of the dates the new rates become effective and take the appropriate meter readings. You can see that I have used different rates in the sample spreadsheet supplied; those prior to 1-May-2022 and those charged by EI after that date. Simply enter the "Last Reading" and "Current Reading" values in the appropriate cells to have it calculate the monetary amount


Row 7 calculates your Pay on Time/Electronic Billing discount if these apply to your account. The row is labelled "Total Units (Pay On Time Credit)". Values can be one of 0%, 4.00%, or 4.50%. The discount is calculated on the total of the five values in cells E2 - E6


Rows 8 - 12 allow for up to five different rates for Standing Charges from one date to another as per the electricity rates in Rows 2 - 6, the difference being that you enter dates in the format DD/MM/YYYY rather than readings from your meter. The spreadsheet subtracts the dates and calculates the Standing Charge based on the resultant number of days.


Row 13 calculates the Total Days in the billing period and displays it in cell D13. This value is used to calculate Average Daily/Weekly Consumption, Burdened Cost/Unit, Forecast Bill, and the Forecast Next Bill Date in Rows 24 - 28.


Rows 14 - 16 calculate the Public Service Obligation Levy and the Public Service Obligation Rebate, if applicable, the latter being a credit against the account. Usually, the only work required is to change the values in cells D14, D15, or D16 to a number(s) to reflect the number of months being charged.


Row 17 totals all the charges against the account in the current billing period. The total appears in cell F17.


Row 18 labelled "VAT", calculates the VAT payable on the total in cell F17 and is currently set to 9% in cell E19. This will revert to 13.50% in October 2022 sometime so simply type in "13.5" on the appropriate date.


Row 19 Shows the VAT inclusive charges against the account in the current billing period.


Row 20 shows any credits or debits due against the account. Cell F20 will usually have a value of zero. To enter a credit, for example, an ex-gratia payment of €40, type "-40" into F20, reducing the "Balance Due (if no SW Credits)" in cell F21 by €40. If there is a balance outstanding from the last bill of €40, type "40" into cell F20, increasing the "Balance Due (if no SW Credits)" in cell F21 by €40


Rows 24 - 28 perform some statistical calculations that may prove useful. Smart meters were supposed to do this kind of stuff for us. I now know that unless I sign up for a special smart meter billing programme with no way to back out of the contract, it does nothing more than the old electro-mechanical meter did but without the entertainment supplied by the spinning wheel.


Rows 30 - 32 calculate the reduction in your electricity bill if you are entitled to the Home Benefits Package from SW. It also recalculates the statistical information in Rows 24 – 28 to reflect the reduced electricity cost.


Row 39 onwards enables you to store another 2-monthly bill on the same sheet if you want to. Another potential use is to help in tracking your Night Rate electricity meter charges by taking meter readings from it, if you have a Night Rate meter installed.


Give it a trial to see if it’s useful and drop any tips, suggestions, bugs (in my spreadsheets!!), or comments in here. No PMs for the reasons stated in the opening post.


Happy electricity calculations here.

Just to add the data in the sample spreadsheet are from the readings I took from my own meter(s)

The opening reading of zero in cell B41 in the lower part of the spreadsheet is where I had my new smart meter installed.

mathepac.
 
Last edited:
You must log in or register to reply here.
Back
Top