SHIPPING LINE DETENTION AND DEMURRAGE CALCULATION FORMULA NEED

[thanoj]

Hi All,

I need a formula to calculate the shipping line container demurrage and detention.

For Eg: I am working in a shipping line where we impor and export shipments. For import shipment my team is struggling to calculate Demurage and detention charges and we are loosing lot of money as well.

for ex -
Shipment arrived at destination port - 01/03/2021
free days for this shipment 14 days
consignee collected the container and returned it on - 25/03/2021
so basically he had the container more than 14 days.
in addition to 14 days he had kept the container extra 11 days.

for fir 5 days after free time shipping line charges USD 15
Next 5 days - USD 30
thereafter - USD 50

Since we have three line we do have three different detention rate as well.

If you can provide me the formula i can try and implement it in other line as well. thank you

 

[KRice]

Adriano, if you use the approach in post #6, then I would recommend changing the "Baseline" helper column values to 0. Previously I had used a formula to make "Baseline" the same as the "0-14 Days" column, although to form the correct marginal rate array, "Baseline" should always be 0. This issue became apparent when I entered $50/day on line 3 for the "0-14 Days" demurrage rate and obtained an unexpected answer for the total demurrage amount. By fixing "Baseline" at 0, we get a total demurrage amount of $1300 with $50/day in cell J3, but based on your initial problem statement (post #1), I believe the correct daily rate for the 0-14 Days column should be $0/day, which would give a total demurrage amount of $600 on line 3. Here is a corrected version of the approach described in post #6 (with the exception that cell J3 still shows $50 rather than $0).

MrExcel_20220309_Adriano Diogo.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1									threshold days over ->	0	14	30	45
2	Shipping Line	Container Size	Container Number	Arrival Date	Free Days	Demurrage Start Date	Deliver Container Date	Days of Demurrage	Baseline	1-14 Days	15-30 Days	31-45 Days	45> Days	Demurrage Amount
3	NileDutch	20 FT	TCKU3532266	11/25/2021	30	12/25/2021	1/21/2022	26	$0.00	$50.00	$50.00	$100.00	$150.00	$1,300.00
4	UAL	40 FT	TCKU4350440	11/5/2021	15	11/20/2021	1/2/2022	42	$0.00	$0.00	$50.00	$100.00	$150.00	$2,000.00
5	MSC	40 FT Open Top	MSCU5733990	10/8/2021	45	11/22/2021	1/22/2022	60	$0.00	$0.00	$50.00	$100.00	$150.00	$4,550.00
Diogo

Cell Formulas
Range	Formula
F3:F5	F3	=D3+E3
H3:H5	H3	=G3-F3-1
N3:N5	N3	=SUMPRODUCT(--(H3>$J$1:$M$1),(H3-$J$1:$M$1),J3:M3-I3:L3)

 

[Adriano Diogo]

Hi Kirk / Jtakw,

Thanks for your input and below we have a different scenario where i feel more clear.

Since we have a free days all calculation starts from column N5.

 

[KRice]

Hi Adriano,
Thank you for the example. It appears that the delivery must have occurred on the 57th day after arrival, and since there are 30 free days, there would be 26 days of demurrage. I think I understand what confused me earlier. It appears that those 26 days of demurrage are to be apportioned to the rate table bins beginning in whichever bin includes or follows the number of free days. So in this example, the 26 days of demurrage are distributed {0;0;15;11} rather than {15;11;0;0} (the latter being what my earlier approaches do). I'll have a further look.

 

[KRice]

Hi Adriano,
I'm still confused about the method used to apportion days to the various fee periods. In most examples that I've seen of these types of calculations, the demurrage days (i.e., days incurring fees) are apportioned to the lowest fee period immediately following the free period, and as the fee period's maximum number of days threshold is reached, any remaining demurrage days spill over into the next higher rate category, and so on until all demurrage days are apportioned among the fee periods. In your last example, this does not appear to be the case, as the lower fee period was skipped. It seems that the fee table in your post #13 example is disconnected from the number of free days. If I were to ignore the "Free Days" column, I would have concluded that the rate table was constructed for 15 Free Days because that is what the first row shows ($0 for days 1-15)...and the 16th day would begin to incur demurrage fees.

I have a few questions:

1. Can you offer any more clarity to address my confusion?
2. Do you count partial days?
3. If there were no Free Days and a container arrived on 11/25/2021 and was delivered on 11/28/2021, how many days of demurrage would be charged...3 or 4?
4. Is there any relationship between (free days, demurrage daily rates, or demurrage fee periods) and the (shipping line, shipping container type, or shipping container size)? I ask because a better approach might be to create a demurrage fee table that covers the various combinations, and your demurrage calculation table could then refer to the fee table to apply the relevant free period, fee periods, and daily rates.