# Calculation of monthly payments per employee

D

#### Deborah

I am looking for a formula that gives me the amount to be paid at the end of
each month per employee knowing that:

- If the month starts on 16th or ends on 15th, half the allowance should be
paid (1/2)
- The first month, the travel costs inbound have to be added to the allowance
- Travel costs outbound should be paid in the month that is inserted in
column J
- Contracts can be extended (the end dates are inserted in columns C, D, E)
the calculation should also include these dates if there are any

Column A: Start date contract (16/03/2010)
Column B: End date contract (30/06/2010)
Column C: End date extension 1 (15/09/2010)
Column D: End date extension 2 (empty)
Column E: End date extension 3 (empty)
Column F: Monthly allowance (600â‚¬)
Column G: Travel cost inbound (500â‚¬)
Column H: Travel cost outbound (450â‚¬)
Column I: Month payment outbound travel (October 2010)

SOLUTION
Column J: March 2010 (800â‚¬)
Column K: April 2010 (600â‚¬)
Column L: May 2010 (600â‚¬)
Column M: June 2010 (600â‚¬)
Column N: July 2010 (600â‚¬)
Column O: August 2010 (600â‚¬)
Column P: September 2010 (300â‚¬)
Column Q: October 2010 (450â‚¬)

I hope someone can help.

Thank you,

Deborah

Deborah,
The formula I created depends on having the Analysis Toolkit add-in
activated because it extensively uses the EoMonth() function.
To verify that the Analysis Toolkit is active, from the top menu, go to
When the Add-ins window appears, make sure the Analysis ToolPak is
checked. Hit OK to exit the Add-in window. This will allow the EoMonth()
function to work. [EoMonth stands for End-of-Month].

I created an if statement that works for the example you gave.
In English (sort of) it says...
- If the current month = Start Month then
if the Start Day is > 15 then
1/2 Allowance + Inbound cost
otherwise
100% Allowance + Inbound cost
ELSE
- If the current month = Outbound Travel Month then
Outbound cost
ELSE
- If the current month = End of Contract Month then
if the End Day is < 16 then
1/2 Allowance
otherwise
100% Allowance
ELSE
- If the current month < End of Contract Month AND
the current month >Start Month then
Allowance

Assuming that DATES appear in Row 1 starting in column J (ie: Mar-2010,
Apr-2010, etc)...
Assuming the data and formulas start in row 2...

Cell J2 should be
=IF(EOMONTH(J\$1,0)=EOMONTH(\$A2,0),IF(DAY(\$A2)>15,ROUND(\$F2/2,2)+\$G2,\$F2+\$G2),IF(EOMONTH(J\$1,0)=EOMONTH(\$I2,0),\$H2,IF(EOMONTH(J\$1,0)=EOMONTH(MAX(\$B2:\$E2),0),IF(DAY(MAX(\$B2:\$E2))<16,ROUND(\$F2/2,2),\$F2),IF(AND(EOMONTH(J\$1,0)<EOMONTH(MAX(\$B2:\$E2),0),EOMONTH(J\$1,0)>EOMONTH(\$A2,0)),\$F2,0))))

Copy this formula across as anm columns as desired.
It will work as long as...
- Columns A thru I are as described in your example
- Columns J thru ??? have a DATE in row 1

--
Hope this helps.
If it does, please click the Yes button.