How to Create a Electricity Cost Calculator in Excel (Easy Steps)

Introduction to Electricity Cost Calculator in Excel

The energy cost calculator is a straightforward tool for determining the cost of energy used by homes and businesses. By using the results of this calculation, you can establish a budget. Additionally, this power cost calculator assists in creating family or workplace budgets and managing expenses. The equation for calculating the cost is as follows:

Equation to create an electricity cost calculator in Excel

Where:

W = Power (Watts)
H = Time (Hours)
R = Rate (KWH)

In this article, we’ll explore how to use SUM, IF functions, and Excel formulas for this calculation. We’ll also manually verify the calculator. Let’s proceed step by step.

Step 1 – Basic Information of the Electricity Cost Calculator in Excel

Add the following headers in column B: User, User Address, and Pay Per Unit.
Populate these headers with the correct values.

Add additional headers: Home Appliance, Quantity, Power Rating, Total Watts, Hours/Day, Daily Cost, Monthly Cost, Yearly Cost, Total Electricity Cost, and Tax (3%).
Fill in the appropriate values under Quantity, Power Rating, and Hours/Day.

Step 2 – Using Excel Formulas to Calculate Daily, Monthly & Yearly Power Bills

Calculate the total Watts used for each appliance:

In cell E9, enter the formula:

=C9*D9

- Here, we multiply Power Rating by Quantity to get the total Watts.
- Press Enter.

- Autofill this formula for the rest of the column.

Calculate the daily cost:
- In cell G9, enter the formula:

=(($C$6/1000)*F9*E9)*1

- Here, we divide the Pay per Unit 0.80 by 1000 and multiply it by 450 & 8.
- Lastly, we multiply by 1 as we wish to get the daily cost.
- Press Enter.

- Autofill this formula.

Calculate the monthly cost:
- In cell H9, enter the formula:

=(($C$6/1000)*F9*E9)*30

- Press Enter.

Autofill this formula.

Calculate the yearly cost:
- In cell I9, enter the formula:

=(($C$6/1000)*E9*F9)*365

- Press Enter.

- Autofill this formula.

Note

Use the absolute cell reference in C6 as we do not wish to change the cell.

Step 3 – Estimating Total Electricity Cost with the SUM Function

To estimate the total daily cost, use the SUM function in cell G17:

=SUM(G9:G16)

- Press Enter.

Similarly, calculate the total monthly cost in cell H17:

=SUM(H9:H16)

- Press Enter.

For the yearly cost, use the SUM function in cell I17:

=SUM(I9:I16)

- Press Enter.

Step 4 – Adding Tax Using the IF Function

Calculate Tax for Daily Cost (Cell G18):

In cell G18, enter the following formula:

=IF(G17>33,G17+G17*3%,G17)

- Explanation:
  - If the value in G17 (daily cost) is greater than 33, add a 3% tax to it.
  - Otherwise, keep the original daily cost.
Press Enter.

Calculate Tax for Monthly Cost (Cell H18):

In cell H18, enter this formula:

=IF(H17>1000,H17+H17*3%,H17)

- Explanation:
  - If the value in H17 (monthly cost) exceeds 1000, apply a 3% tax.
  - Otherwise, retain the original monthly cost.

Calculate Tax for Yearly Cost (Cell I18):

In cell I18, enter the following formula:

=IF(I17>12000,I17+I17*3%,I17)

Press Enter.

Step 5 – Testing the Electricity Cost Calculator

In the final step, enter the correct values under the Quantity header to properly run the electricity cost calculator in Excel.