Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I want to calculate average payment of the house for every month, the average expense of each month. However, most of the fee is across months, they charge every 3 or 2 months. These are normal bills, e.g.: electricity bill, water bill, gas bill, etc.

I want to know the balance of each month to measure the usage of each kind of resource, so that they won't be overused, which may lead to bigger bill.


For example, I may receive a electricity bill this month, which calculate the usage of electricity in a period of time such as 18/09/2010 to 18/11/2010, and the fees is 1409.1 HKD. Of course I would not like to divide the fees into 3, ie : 1409.1 / 3 = 469.77 HKD. I would like to distribute the fee to each day in that peiod of time.

And after all, I'll get an average expense of each month.

I don't know how to do this in an Excel sheet, I don't like to open a row/column for each day.

share|improve this question
    
I suggest you start by dividing the electricity bill amount by 2 if it's from mid september to mid november. –  Daniel Beck May 22 '11 at 17:43
    
Also, I got the impression from the question title this was about mortgage payments. Since your question applies to mobile phone bills, car insurance payments and online game subscriptions as well, consider editing it to broaden its scope; this might attract more attention. –  Daniel Beck May 22 '11 at 17:47
    
NO, just normal bills, ie: electricity bill, water bill, gas bill, etc. –  lamwaiman1988 May 22 '11 at 18:32
    
I had the impression you were just looking for any solution that could handle recurring, fixed-interval bills with varying amounts payable as input. Didn't realize that the types of bills (electricity, water, and gas) had to be hard-coded in the software. My apologies. –  Daniel Beck May 22 '11 at 18:37
    
Arr, nothing about business/company, just personal need, I want to know the balance of each month to measure the usage of each kind of resource, so that they won't be overused, which may lead to bigger bill. –  lamwaiman1988 May 22 '11 at 18:50
add comment

1 Answer

I use a spreadsheet to do this. For example, I have the following columns:

   A           B           C                 D     E
1  Start       End         Electricity cost  Days  Electricity cost per day
2  18/09/2010  18/11/2010  1409.1            61    23.1

The formula for the "Days" column is simply:

=B2-A2

where B2 contains the end date and A2 contains the start date. The "cost per day" formula is:

=C2/D2

You could add another column to calculate the approximate cost per month:

=E2*30

This means that you only need to add a new row for each bill, and "drag copy" the formulas down. You could have separate files or worksheets for each type of bill. It also makes it possible to look at factors, for example, the average cost per kWh for each bill.

share|improve this answer
    
really I want the actual average money for each month, say 01/09/2010 to 30/09/2010, not just a approximate one. –  lamwaiman1988 May 23 '11 at 1:09
    
@gunbuster363 - If you divide the total by 2 you will be given the average. You could also request a more detail bill from the company in question. –  Ramhound Mar 7 '12 at 12:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.