# 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.

---

- **Canonical URL:** https://dothecalculation.com/blog/templates/excel-energy-cost-tracker
- **Category:** Templates
- **Author:** Do The Calculation Team
- **Published:** 2026-08-03
- **Reading time:** 10 min read
- **Publisher:** Do The Calculation (https://dothecalculation.com)
- **Methodology:** https://dothecalculation.com/methodology

---

## 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.

Tool: [Try the appliance energy calculator](https://dothecalculation.com/calculators/appliance-energy-calculator) — Convert a wattage and a usage pattern into monthly kilowatt-hours and cost.

## The basic formula

**Cost of running one appliance**

```
=Watts ÷ 1000 × HoursPerDay × 30.44 × RatePerKwh
```
- 30.44 is the average days per month, 365 ÷ 12. Using 30 understates by 1.5%.
- Watts divided by 1,000 gives kilowatts, which is what the tariff is priced in.
- For appliances that cycle, such as fridges and freezers, use the annual kWh from the energy label rather than the nameplate wattage.

**A household audit, at a flat 17 cents**
| 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 |

> **The standby row is the surprising one** — Sixty watts of always-on load, which is a games console, a set-top box, a few chargers, and a smart speaker, costs $7.45 a month and 44 kWh. It is more than the refrigerator. It is also the cheapest thing on the list to eliminate, because a switched power strip costs $15.

## Tiered rates, and the SUMPRODUCT that handles them

**A typical tiered tariff**
| 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 |

**Tiered cost in one formula**

```
=SUMPRODUCT(--(Usage>Floors), (Usage−Floors), RateIncrements)
```
- Floors are 0, 500, and 1,000. Increments are 0.14, 0.05, and 0.05.
- At 1,276 kWh: 1,276×0.14 + 776×0.05 + 276×0.05 = 178.64 + 38.80 + 13.80 = $231.24
- The same technique as a progressive tax bracket, which is exactly what a tiered tariff is.

**Average against marginal**
| 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% |

> **Use the marginal rate for every savings decision** — Switching 300 W of lighting to 60 W of LED saves 36.5 kWh a month. At the average rate that is $6.61; at the marginal rate it is $8.76. Over a five-year fixture life the difference is $129, which is the whole cost of the bulbs. The average rate is for reading the bill; the marginal rate is for making decisions.

**Extracting the marginal rate from the tariff**

```
=XLOOKUP(Usage, Floors, Rates, , −1)
```
- Returns the rate of the tier the current usage falls into.
- Put it in a named cell and reference it from every savings calculation in the workbook.
- It updates automatically as consumption changes tier, which happens seasonally in most households.

## The tracking sheet

**One row per bill**
| 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.

> **Normalise for weather before concluding anything** — Heating and cooling dominate most bills and both track the weather rather than your habits. Kilowatt-hours per degree day is the figure that isolates what you changed from what the weather did. Without it, a mild winter looks like a successful efficiency programme.

## Payback on an upgrade

**Simple payback**

```
=Cost ÷ (kWhSavedPerMonth × MarginalRate × 12)
```
- A $1,900 heat pump water heater replacing the electric one saves about 60% of 411 kWh a month, or 247 kWh.
- 247 × 0.24 × 12 = $711 a year, giving a payback of 2.7 years.
- At the average rate it computes as $537 a year and 3.5 years, which is 32% pessimistic.

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.

Tool: [Try the home energy audit calculator](https://dothecalculation.com/calculators/home-energy-audit-calculator) — Work 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.

---

_Source: [Do The Calculation](https://dothecalculation.com/blog/templates/excel-energy-cost-tracker). Quote freely with attribution and a link to this page._