How to calculate average monthly costs for all my years bills?

Hi folks,

I'd like to calculate the average household costs per month based on my fixed rate bills.
That I'd nee for saving always the same amount of money each month and being able to pay all regular bills based on that savings.

For an example, let's assume the year only has 4 month (Jan - April):

Please Login or Register  to view this content.

I therefore have to save 1100,- each month for being able to pay all bills during the year from it.
So far, so easy.


Problem: I now want to calculate how much money should be left on the saving account at the end of each month!

Without a forumlar, the solution would be based on the following thoughts:
- At the end of January, I need 200 bucks left, because in Feb I need additional 1300-1100=200
- in February, there is 0 left because 1300-1100-200(from Jan) = 0
- in March I save 1100-1000 = 100
- in April I dont save anything 1100-1100 = 0, but with the savings from March it's still 100 left.
- Again in January I save 1100-1000 = 100, plus the 100 from April = 200.

The the final table is:
Fixed saving rate: still 1100

Please Login or Register  to view this content.

Now when I think like that I can find the solution. But of course I'd like to have a forumlar for calculation of the remaining amount at end of each month.

Question: could you help creating that formular? I'm completely stuck here...

The complication is that you are trying to do a circular reference... Feb is based on Jan which is based on Apr which is based on Mar which is based on Feb etc.

You have to have a starting point somewhere with a manual value of a cash injection. If you only start your savings account in Jan, how do you get the extra 100 from April when in the history of the savings account it never happened. You will have to yourself put 200 in your savings account to begin with to cover the negative balance for Feb.

To avoid the circular reference it's better to explicitly state each month. So you would have a table with Jan-18, Feb-18, Mar-18, Apr-18, Jan-19, Feb-19 etc. The figure for Jan-18 will be manual but the figure for Jan-19 would come from Apr-18.

See the attached spreadsheet for how you can get your remaining amount for each month.

The paradoxical question would be which came first, Jan or Apr?

---

If that takes care of your original question, please select Thread Tools from the menu link above to mark this thread as SOLVED.
To say thanks to the user(s) who contributed towards the solution, you can use the "Add Reputation" button on their helpful post(s).
Thanks!