Excel 2007 : Subtract a cell based on current date

[nfavours]

I'm doing a budget spreadsheet where each line item has the amount that was budgeted for it for the year. At the end of my spreadsheet I've created a column that will show how much of the budget we expected is still owed irregardless to what we've actually paid. (i.e. we budget 1200 for the year, by October 31st this column should show 200). I want to write a formula that will automatically subtract 1/12 of the budget on the last day of the month. Is this possible?

[ozbrian]

I'm doing a budget spreadsheet where each line item has the amount that was budgeted for it for the year. At the end of my spreadsheet I've created a column that will show how much of the budget we expected is still owed irregardless to what we've actually paid. (i.e. we budget 1200 for the year, by October 31st this column should show 200). I want to write a formula that will automatically subtract 1/12 of the budget on the last day of the month. Is this possible?

if your budget is in cell A1, use the following formula

=A1-(((MONTH(NOW()))/12)*A1)

Let me know how it goes.

Regards
Brian

[nfavours]

It worked, Brian! Thank you so much for your help.