Guide to Calculating Residential Electricity Bills Using Excel

Buzz

Content

GUIDE TO CALCULATING RESIDENTIAL ELECTRICITY BILLS USING EXCEL

As per the announcement from the Ministry of Industry and Trade since December 1, 2017, the retail electricity price has changed compared to previous years, with an average increase to the new price of 1,720.65 VND/kWh, up 6.08% compared to the previous average electricity selling price. Please refer to the guide on calculating residential electricity bills using Excel to proactively calculate your electricity bills, especially for businesses/dealers in electricity.

In this article, I will guide you on how to create a table to calculate residential electricity bills in Excel and mainly use built-in Excel functions for calculation, specifically using the IF function.

GUIDE TO CALCULATING RESIDENTIAL ELECTRICITY BILLS USING EXCEL

Firstly, you need to create an electricity billing spreadsheet. For residential electricity bills, they are calculated based on tiered rates as follows:

- 1-50kW: 1549VND: Electricity consumption within the first 50 units from 1-50kW will be charged at 1549VND per unit.
- 51-100: 1600VND: Electricity consumption for the next 50 units from 51-100kW will be charged at 1600VND per unit.
- 101-150: 1858VND: Electricity consumption for the next 50 units from 101-150kW will be charged at 1858VND per unit

- 151-200: 2340VND:- 201-300: 2615VND:- 301-400kW: 2701VND:- 401 and above: 2271VND:

The above prices do not include 10% VAT.

We have a given data table as shown below and need to create a formula to calculate the electricity bill:

Step 1: First, you need to calculate the electricity consumed by subtracting the old reading from the new reading.

- You enter into cell F4 =E4-D4 then press Enter and drag down to complete the cells below.

Step 2: You start calculating electricity consumption at each price tier. Here, you need to understand and interpret these tiers to apply the IF function in Excel for calculation.

* Tier 1-50kWh: 1549VND

Enter into cell G4 the formula: =IF(F4<=50,F4,IF(F4>50,50)).If the consumption is less than or equal to 50, the result will be the value in cell F4, otherwise it will be 50.

* Tier 51-100kW: 1600VND

Enter into cell H4 the formula: =IF(F4<51,0,IF(AND(F4>50,F4<=100),F4-50,IF(F4>=100,50))). If the consumption is less than 51, the result will be 0. If the consumption is between 50 and 100, the result will be the consumption minus 50. If the consumption is 100 or more, the result will be 50.

* Tier 101-150kW: 1858VND

Enter into cell I4 the formula: =IF(F4<101,0,IF(AND(F4>100,F4<=150),F4-100,IF(F4>=100,50))). If the consumption is less than 101, the result will be 0. If the consumption is between 100 and 150, the result will be the consumption minus 100. If the consumption is 100 or more, the result will be 50.

* Tier 151-200kW: 2340VND

Enter into cell J4 the formula: =IF(F4<151,0,IF(AND(F4>=150,F4<=200),F4-150,IF(F4>=200,50))). If the consumption is less than 151, the result will be 0. If the consumption is between 150 and 200, the result will be the consumption minus 150. If the consumption is 200 or more, the result will be 50.

* Tier 201-300kw: 2615VND

Enter into cell K4 the formula: =IF(F4<201,0,IF(AND(F4>=201,F4<=300),F4-200,IF(F4>=300,100))). If the consumption is less than 201, the result will be 0. If the consumption is between 201 and 300, the result will be the consumption minus 200. If the consumption is 300 or more, the result will be 100.

* Tier 301-400kW: 2701VND

Enter into cell L4 the formula: =IF(F4<301,0,IF(AND(F4>=301,F4<=400),F4-300,IF(F4>=400,100))). If the consumption is less than 301, the result will be 0. If the consumption is between 301 and 400, the result will be the consumption minus 300. If the consumption is more than 400, the result will be 100.

* Tier 401kW and Above: 2271VND

Enter into cell M4 the formula: =IF(F4<401,0,IF(F4>400,F4-400)). If the consumption is less than 401, the result will be 0. If the consumption is more than 400, the result will be the consumption minus 400.

Step 3: You will calculate the Total Payment that the customer has to make, which equals the electricity consumption multiplied by its corresponding Unit Price.

Enter into cell N4 the formula: =G4*G3+H4*H3+I4*I3+J4*J3+K4*K3+L4*L3+M4*M3 (where G3, H3, I3, J3, K3, L3, M3 correspond to the Unit Price: 1549VND, 1600VND, 1858VND, 2340VND, 2615VND, 2701VND, 2271VND )

The result is that you have completed the calculation of electricity consumption and the amount that the customer needs to pay. From here, you can copy the formula for the rows below to calculate for all customers.

Frequently Asked Questions

How do I calculate my residential electricity bill using Excel?

To calculate your residential electricity bill in Excel, first enter your electricity consumption. Then, apply tier-based formulas for each consumption range using Excel's IF function to calculate the cost for each tier, considering unit prices. Finally, sum all the results to find the total bill.

What are the tier rates for residential electricity consumption?

The residential electricity rates are as follows: 1-50kW at 1549VND, 51-100kW at 1600VND, 101-150kW at 1858VND, 151-200kW at 2340VND, 201-300kW at 2615VND, 301-400kW at 2701VND, and 401kW and above at 2271VND.

Can I calculate electricity usage for different tiers using Excel formulas?

Yes, Excel allows you to calculate electricity consumption for different tiers using nested IF functions. These formulas handle each tier's specific limits, allowing the bill to be automatically calculated based on your usage.

How do I apply the IF function for tier-based electricity pricing in Excel?

You can use the IF function to determine the amount of electricity consumed in each tier. For example, for the 1-50kW range, use the formula =IF(F4<=50,F4,50). Adjust formulas for other tiers to match the consumption limits.

What should I do to calculate the total payment for electricity usage in Excel?

To calculate the total payment, multiply the units consumed in each tier by their respective unit price and sum the results. Use a formula like =G4*G3+H4*H3... to add up the costs from all tiers.

Mytour's content is for customer care and travel encouragement only, and we are not responsible.

For errors or inappropriate content, please contact us at: [email protected]