How to Create a Electricity Cost Calculator in Excel (with 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.

Basic Information of Electricity Cost Calculator in Excel

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

Creating Dataset of Basic Information of Electricity Cost Calculator in Excel

Read More: How to Create Fuel Cost Calculator Using Excel Formula


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.

Calculating total Watts to create electricity cost calculator excel

    • Autofill this formula for the rest of the column.

Total watts acquired

  • Calculate the daily cost:
    • In cell G9, use 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.

Using Excel formula to Find Daily Cost to create electricity cost calculator excel

    • Autofill this formula.

Daily Costs Acquired

  • Calculate the monthly cost:
    • In cell H9, use the formula:
=(($C$6/1000)*F9*E9)*30
    • Press Enter.

Monthly Cost to electricity cost calculator excel

  • Autofill this formula.

Output of Monthly Cost

  • Calculate the yearly cost:
    • In cell I9, use the formula:
=(($C$6/1000)*E9*F9)*365
    • Press Enter.

Use Excel formula to estimate Yearly cost

    • Autofill this formula.

Output of yearly cost

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.

Use of SUM function to total electricity cost for the Excel Calculator

  • Similarly, calculate the total monthly cost in cell H17:
=SUM(H9:H16)
    • Press Enter.

Use of SUM function to total electricity cost for the Excel Calculator

  • For the yearly cost, use the SUM function in cell I17:
=SUM(I9:I16)
    • Press Enter.

Use of SUM function to total electricity cost for the Excel Calculator

Read More: How to Create Shipping Cost Calculator in Excel


Step 4: Adding Tax Using the IF Function

Calculate Tax for Daily Cost (Cell G18):

  • In cell G18, use 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.

Use of IF function to add tax

Calculate Tax for Monthly Cost (Cell H18):

  • In cell H18, use 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.

Use of IF function to add tax to electricity cost for the Excel Calculator

Calculate Tax for Yearly Cost (Cell I18):

  • In cell I18, use the following formula:
=IF(I17>12000,I17+I17*3%,I17)
  • Press Enter.

Use of IF function to add tax to electricity cost for the Excel Calculator

Read More: How to Construct Cost Inflation Index Calculator in Excel


Step 5: Testing the Electricity Cost Calculator

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

Analyze data of the Electricity Cost Calculator in Excel


Download Practice Workbook

You can download the practice workbook from here:


Related Articles


<< Go Back to Cost Calculator Finance TemplateExcel Templates

Get FREE Advanced Excel Exercises with Solutions!
Yousuf Khan
Yousuf Khan

Yousuf Khan has been working with the ExcelDemy project for over one year. He has written 47+ articles for the ExcelDemy site. He also solved a lot of user problems with ExcelDemy Forum. He has interests in Excel & VBA, Desktop and mobile applications, and projects & templates. He completed his graduation and post-graduation in Information Technology from Jahangirnagar University. Currently, he works as a VBA & Excel Content Developer in ExcelDemy projects, writing unique and informative content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo