Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation of monthly payments per employee
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation of monthly payments per employee
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 TOOLS ADD-INS... 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,R OUND($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:$E 2),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. Thanks in advance for your feedback. Gary Brown "Deborah" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation of monthly payments per employee
FYI,
If you're using Excel 2007, I understand that the Analysis ToolPak has been incorporated into the main functions and does NOT need to be activated. -- Hope this helps. If it does, please click the Yes button. Thanks in advance for your feedback. Gary Brown "Deborah" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mortgage with Quarterly Capitalisation & Monthly Payments | Excel Worksheet Functions | |||
TEMPLATE FOR TOTAL DEBT AND MONTHLY PAYMENTS | Excel Discussion (Misc queries) | |||
Amortization template for uneven monthly payments | Excel Worksheet Functions | |||
what template should i use for collecting monthly payments from m. | New Users to Excel | |||
Loan amortization schedule with bi-monthly payments | Excel Worksheet Functions |