An Energy Cost Tracker in Excel: Value Savings at the Marginal Rate, Not the Average
On a tiered tariff the last kilowatt-hour costs 24 cents while the average is 18. Every efficiency saving comes off the top tier, so valuing it at the average makes a 2.7-year payback look like 3.5. Here is the sheet that gets it right.
The Rate You Save At Is Not the Rate You Pay
Most residential electricity tariffs are tiered: the first block of consumption is cheap and later blocks cost more. That means the average rate on your bill is not the rate that matters for any efficiency decision.
Every kilowatt-hour you avoid comes off the top of your consumption, so it is priced at the highest tier you reach. On the tariff below that is 24 cents against an 18 cent average, and using the wrong one understates every payback calculation by more than a third.
Try the appliance energy calculatorConvert a wattage and a usage pattern into monthly kilowatt-hours and cost.The basic formula
Swipe sideways to compare columns.
| Item | Watts | Hours a day | kWh a month | Cost |
|---|---|---|---|---|
| Central air conditioning | 3,500 | 6 | 639 | $108.67 |
| Electric water heater | 4,500 | 3 | 411 | $69.86 |
| Clothes dryer | 3,000 | 1 | 91 | $15.52 |
| Lighting | 300 | 5 | 46 | $7.76 |
| Refrigerator, from the label | — | — | 45 | $7.65 |
| Standby loads, always on | 60 | 24 | 44 | $7.45 |
| Total | — | — | 1,276 | $216.90 |
Tiered rates, and the SUMPRODUCT that handles them
Swipe sideways to compare columns.
| Block | Rate | Rate increment |
|---|---|---|
| First 500 kWh | $0.14 | $0.14 |
| Next 500 kWh | $0.19 | $0.05 |
| Above 1,000 kWh | $0.24 | $0.05 |
Swipe sideways to compare columns.
| Measure | Value |
|---|---|
| Total consumption | 1,276 kWh |
| Total cost | $231.24 |
| Average rate | $0.1812 per kWh |
| Marginal rate | $0.24 per kWh |
| Gap | 32% |
The tracking sheet
Swipe sideways to compare columns.
| Column | Contents |
|---|---|
| Read date | From the bill |
| Days in period | =ThisDate − PreviousDate |
| kWh used | From the bill |
| kWh per day | =Usage / Days |
| Cost | From the bill |
| Average rate | =Cost / Usage |
| Heating degree days | From a weather service, for the same period |
| kWh per degree day | =Usage / DegreeDays |
| Same period last year | =XLOOKUP(EDATE(ReadDate,−12), Dates, Usage, "", −1) |
The kilowatt-hours per day column matters because billing periods are not equal. A 34-day period looks like a 13% increase over a 30-day one with no change in behaviour at all, and that is the single most common false alarm in energy tracking.
Payback on an upgrade
Two adjustments make that estimate honest. Subtract any rebate from the cost, and add an escalation rate to the saving if electricity prices are rising. Both are one extra cell each and both move the answer more than the precision of the kilowatt-hour estimate does.
Try the home energy audit calculatorWork through a whole-house load list rather than one appliance at a time.What the tracker does not capture
- Time-of-use pricing. Where rates vary by hour, the relevant question is when you use power rather than how much, and a monthly total cannot answer it.
- Demand charges, which some tariffs levy on your highest fifteen-minute draw rather than on total consumption. Reducing peak draw then matters more than reducing total energy.
- Fixed connection charges, which do not fall when consumption does and therefore should not be in the marginal rate.
- Gas, oil, or propane. A heat pump replacing a gas furnace shifts load between fuels, and comparing on kilowatt-hours alone will mislead.
- The comfort trade-off. Reducing air conditioning saves real money and the model is silent on whether the house is then pleasant to be in.
- Nameplate wattage accuracy. Most appliances draw well below their rating most of the time, so a plug-in energy meter beats a specification sheet for anything you plan to spend money on.
- Rate changes. A tariff restructure can eliminate a payback that looked settled, and multi-year projections should say what rate they assumed.
Should I use the average or the marginal rate?
Marginal, for any decision about reducing consumption. Savings come off the top of your usage and are priced at the highest tier you reach. On the tariff above that is 24 cents against an 18 cent average, a 32% difference in every payback calculation.
How do I calculate the cost of running an appliance?
Watts divided by 1,000, times hours used per day, times 30.44 days, times the rate per kilowatt-hour. For anything that cycles on and off, such as a fridge or a freezer, use the annual kilowatt-hours from the energy label instead of the nameplate wattage.
Why did my bill jump with no change in usage?
Check the number of days in the billing period first. A 34-day period against a 30-day one is 13% more energy for identical behaviour. Then check the weather, since heating and cooling dominate most bills. Compute kilowatt-hours per day and per degree day before looking for anything else.
Is standby power really worth worrying about?
In aggregate, yes. Sixty watts of always-on load is 44 kWh a month, which in the example above is more than the refrigerator. Individually each device is trivial; collectively it is a real line, and switched power strips eliminate most of it for a few dollars.
How do I handle a tiered tariff in Excel?
Exactly like a tax bracket: =SUMPRODUCT(--(Usage>Floors), (Usage−Floors), RateIncrements), where the increments are the increase in rate at each tier rather than the tier rates themselves. Keep the tariff table separate so a rate change is a data edit.
What is a degree day and where do I get one?
A measure of how far the outside temperature sat below or above a base temperature, summed over a period. Weather services publish them by location and month. Dividing consumption by degree days separates what you changed from what the weather did, which is the only way to evaluate an efficiency measure honestly.
Written by
Do The Calculation Team
Do The Calculation
Do The Calculation is built by a small team of data analysts and spreadsheet developers. Where a guide depends on a published formula, standard, or government rule, the calculator it links to names that source directly so you can check the number yourself.
About the team