Talk

Advanced search

Excel formulas for household budget...

(11 Posts)
SenseAndIrritability Sun 09-Aug-09 21:25:02

My household budget has been wiped from my computer (grrr) and I now relise that I've completely forgotten how to use Excel.

Basically, we have some bills that are paid weekly, and some that are paid monthly, and I want to be able to use Excel to show me what each costs on a weekly, monthly and yearly basis - without having to individually calculate, so if a bill changes I can just add it in and Excel will do the calculations.

So, where a bill for, say, £10, goes out weekly, I need a formula for the monthly slot that will give me £10 times 52 divided by 12. And for the yearly slot, I just need £10 times 52.

And where the bill is monthly - say for £100 - I need a formula to go in the weekly slot that gives me £100 times 12 divided by 52. And, of course, £100 times 12...

Just trying to explain this has given me a headache. Anyone out there who will/can take pity on me?

catinthehat2 Sun 09-Aug-09 21:42:35

I would do something like this.
Line 1 headers
Col A Type
Col B £
Col C Frequency

then
Line 2
Column A "Food Shopping"
Column B 100
Column C Weekly

Line 3
Column A "Gas bill"
Column B 80
Column C Monthly

etc, (so you can easily change the amounts, add bills etc)

THen back to line 1
Column D I would call cost/week
Column E I would call cost/month
Column F I would call cost/year

Weekly bills
Col D type = B2
Col E type = B2/7*30 (or 31 or 28 or however many days you consider a month to be)
Col F type = B2/7*365

Monthly bills
Col D type = B3/30 * 7 (or 31 or 28 or however many days you consider a month to be)
Col E type = B3
Col F type = B3*12

THis would give you a 2 line spreadsheet with 1 of each of a weekly or a monthly bill. Copy lines to insert more bills.

YOu can customise and elaborate this to make it far more efficient, but this will give you a basic table

zebramama Mon 10-Aug-09 11:30:42

Cat - I would modify what you have done slightly, so that you don't need to tweak the formulae for each row dependent upon the frequency and you can use a wider variety of frequencies...

Assume:
Column A = Item Description
Column B = Cost per Frequency
Column C = Frequency

Frequency: Use numbers as follows
Weekly = 52
Fortnightly = 26
4 Weekly = 13
Monthly = 12
Bimonthly = 6
Quarterly = 4
Half-yearly = 2
Yearly = 1

Then:
Weekly Cost in Column D = B x C / 52
Monthly Cost in Column E = B x C / 12
Yearly Cost in Column F = B x C

Ping me if you want an example emailing to you... (DH of zebramama)

Mousey84 Mon 10-Aug-09 11:51:26

moneysavingexpert.com has a fab budget planner hereand you can download it and save a new one every month. Much simpler

FAQtothefuture Mon 10-Aug-09 11:53:55

agree with mousey - MSE one is fab smile

catinthehat2 Mon 10-Aug-09 12:33:08

Er, ZebraDad, I think the little lady wanted to do her own fluffy little spreadsheet using the most basic version possible as a reminder accrding to her instructions. She can then colour it pink and add little kitten pictures round the edges as required.

But thanks for explaining , because - heck - it's unlikely she would have managed to work anything else out herself, being a girly and all that.

Because after all, Excel IS rocket science isn't it?

I'm sure you meant well, but honestly...

The MSE one looks quite a god start, but you lose a lot of detail. Simple example, one line for haircuts, rather than being able to keep tabs on the cost of everyone's haircut individually.

zebramama Tue 11-Aug-09 09:48:45

Ouch! And I thought I was being helpful!
sad
ZD

quidnunc Tue 11-Aug-09 10:16:02

ZD - glad I didn't offer any suggestions. Just deleted my ideas as I saw the response you recieved.

Tin hats on.

SenseAndIrritability Tue 11-Aug-09 11:44:35

Ouch! Didn't realise this had kicked off in my absence. Should have realised that if anything would cause a ruck it would be Excel technique ... and money...

Thanks ZD and Cat for your tips, I've got my budget back up and running now, so won't have to resort to the MSE one. If looks amazing, Mousey and FAQ, but I think it's almost too complicated for me. But I think I might graduate to it one day... for now I am going to merely retreat to the corner to weep over our advanced, irreversible state of broke-ness. How DID this creep up on us!?

(Now there's a sentiment we can all agree on. Or can we?)

FAQtothefuture Wed 12-Aug-09 11:47:25

Sense - it's actually incredibly easy to use - if I can use it - anyone can smile

You just put the numbers in and it works it all out for you smile

catinthehat2 Wed 12-Aug-09 20:26:55

ZebraDad - I was unneccessarily harsh.

Sorry.

Catinthehat.

Join the discussion

Join the discussion

Registering is free, easy, and means you can join in the discussion, get discounts, win prizes and lots more.

Register now