Calculating monthly budget expenses

[Guest]

I am doing an annual budget my months. I'm looking for a formula to calculate
the monthly cost of salaries where there is an increase during the year.
Here is my example:
Annual salary $50,000
Annual increase $3,000
Date of increase Oct 1
Period of budget Jul 1 to Jun 30.
I need a formula to calculate each month's salary cost. The annual salary is
simply spread evenly over the year, one twelfth each month. Each month I need
to see whether there has been an increase during or prior to that month, if
yes, calculate the amount of that increase to be included in the month.
Very much appreciate any help you can give.
Best regards.

 

[Guest]

My general approach is:
=(base_salary + if(on/after increase_date,increase_amount,0))/12
So if each month is in its own column, say from B through K, with the
starting date for the month in row 2:
=(50000 + if(b$2>=date(2006,10,1),3000,0))/12
If you enter this in column B and autofill it into subsequent months, it
will pick up the increase beginning in October.

 

[Guest]

Thank you so much.
What if the increase date is during a month rather than the beginning of the
month? If I change the increase date to say Oct 15, then the formula brings
the increase in from Nov 1 whereas I'd like to bring in the proportion for
Oct 15 to 31 into October.

 

[vezerid]

I am assuming that the headings for the months are the first day of the
month. Otherwise we will need a somewhat more elaborate formula. I am
also assuming that increase starts counting on the day quoted. Then,
with the month-first-days being in cells J1:J12 and increase date being
in B2, you can use the following formula:

=(50000+IF(MONTH(J1)<MONTH($B$2),0,IF(MONTH(J1)>MONTH($B$2),3000,3000*(DATE(YEAR($B$2),MONTH($B$2)+1,1)-$B$2)/(DATE(YEAR($B$2),MONTH($B$2)+1,1)-DATE(YEAR($B$2),MONTH($B$2),1)))))/12

HTH
Kostis Vezerides

 

[Guest]

Just brilliant Kostis. Thanks so much for solving my dilemma.
regards from Australia
Warren

 

[Guest]

The formula works fine from July to December (my budget runs from July to
June). From January, however, the formula doesn't take into account any of
the increase amount, just the original annual salary. Appreciate your further
help please Kostis.