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

 

[Marcelo Branco]

Welcome to Mr Excel forum

Maybe something like this...

Observe the table in F1:I5

Pasta1
	A	B	C	D	E	F	G	H	I
1	Arrived	Collected	Days	Result		Lower	Higher	Charge $	Marginal Charge $
2	01/03/2021	25/03/2021	25	275		1	14	0	0
3	01/03/2021	14/03/2021	14	0		15	19	15	15
4	01/03/2021	19/03/2021	19	75		20	24	30	15
5	01/03/2021	24/03/2021	24	225		25		50	20
6	01/03/2021	26/03/2021	26	325
Plan2

Cell Formulas
Range	Formula
C2:C6	C2	=B2-A2+1
D2:D6	D2	=SUMPRODUCT(--(C2>=F$2:F$5),C2+1-F$2:F$5,I$2:I$5)
I2:I5	I2	=H2-N(H1)

Hope this helps

M.

 

[KRice]

Welcome to the Board! I have some questions:

1. How do you define a "day"...is it any partial calendar day, do weekends count, holidays? From your example, I believe any partial calendar day counts, but I wanted to clarify this. For example, 1 March to 25 March counts as 25 days, even though that spans weekends and the container may have been picked up/returned at any time during the begin/end day.

2. Are the fees described daily fees? If so, then here is another option.

Your inputs are the blue cells. Two computation methods are shown: one uses a Fee Schedule that shows how the hold-time days are apportioned among the different fee periods. The other does all of that with a single formula (cell B16). I see that my single formula suggestion is essentially the same as what @Marcelo Branco has offered above, except I hardwired the arrays for the fee period thresholds (in days) and the marginal fee rates into the SUMPRODUCT function so that a fee table is not needed.

Book5
	A	B	C	D	E	F	G	H
1
2
3	Date shipment arrival at destination port	3/1/2021
4	Date consignee collected container
5	Date consignee returned container	3/25/2021
6
7	Days consignee held container	25
8	Total hold-time fee (USD)	275
9
10	Fee Schedule:	Fee Periods	Days in fee period	Fee/day (USD)	Days >	Days <=	Hold time apportioned (days)	Fee period
11		0	14	0	0	14	14	0
12		1	5	15	14	19	5	75
13		2	5	30	19	24	5	150
14		3	9999999	50	24		1	50
15
16	Total hold-time fee (USD) (single formula)	275
Sheet1

Cell Formulas
Range	Formula
B7	B7	=B5-B3+1
B8	B8	=SUM(H11:H14)
G11:G14	G11	=MIN(C11,$B$7-SUM(G$10:G10))
H11:H14	H11	=G11*D11
F11:F13	F11	=E11+C11
E12:E14	E12	=F11
B16	B16	=SUMPRODUCT(--(B7>={0;14;19;24}),(B7-{0;14;19;24}),{0;15;15;20})

 

[Adriano Diogo]

 

[Adriano Diogo]

View attachment 59598

Hi Dear Team,

I Hope everything are well at moment.

Can i have your support on demurrage amount calculation above.

Column H: Are the numbers of days that shipping line needs to charges.

Column I, J and K: Are the rates by the Shipping line.

On line 2 my charges will be between H and I.
On line 3 my charges will be between H, I and J.
On line 4 my charges will be between H, I, J and K

 

[KRice]

Here is one approach. You'll see that I added helper columns and row (shaded yellow) so that some arrays can be easily formed to make the calculation easier using the SUMPRODUCT formula. Those helper columns and top row can be hidden from view so that your table will have the same appearance as what you originally posted. The "Baseline" column is used when forming an array of the marginal demurrage rates, which is necessary when using this approach.

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	$0.00	$50.00	$100.00	$150.00	$600.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
H3:H5	H3	=G3-F3-1
I3:I5	I3	=J3
F3:F5	F3	=D3+E3
N3:N5	N3	=SUMPRODUCT(--(H3>$J$1:$M$1),(H3-$J$1:$M$1),J3:M3-I3:L3)

 

[KRice]

I was curious about taking an alternative approach, where instead of forming an array of marginal demurrage rates (see post #6), an array is created showing how many "days of demurrage" are apportioned to each demurrage rate bin. If you are using Excel 365, the formula can be shortened somewhat using the LET function. This example still relies on a helper row at top and a column for the first rate bin (which is $0 for the first 14 days). Those helper cells are shaded yellow and can be hidden from view if desired. Note that the top helper row requires the addition of a large number that will always exceed the largest anticipated "Days of Demurrage" (I used 9999). The apportionment of days is created by the LET function. Taking row 4 as an example, where 42 days of demurrage are shown, these days are apportioned {14,16,12,0} by the LET function, and then multiplied by the demurrage rates {$0, $50, $100, $150} and summed (by the SUMPRODUCT function) to give $2000.

MrExcel_20220309_Adriano Diogo.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1								threshold days over ->	0	14	30	45	9999
2	Shipping Line	Container Size	Container Number	Arrival Date	Free Days	Demurrage Start Date	Deliver Container Date	Days of Demurrage	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	$100.00	$150.00	$600.00
4	UAL	40 FT	TCKU4350440	11/5/2021	15	11/20/2021	1/2/2022	42	$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	$50.00	$100.00	$150.00	$4,550.00
Diogo2

Cell Formulas
Range	Formula
F3:F5	F3	=D3+E3
H3:H5	H3	=G3-F3-1
M3:M5	M3	=SUMPRODUCT(I3:L3,LET(appdays,IF(J$1:M$1<=H3,J$1:M$1-I$1:L$1,H3-I$1:L$1),(appdays>0)*appdays))

 

[Adriano Diogo]

Hi Kirk,

Excellent, marvelous topics.

I have some question on H3.
The total amount of demurrage cost should be $1300 instead of $600.
H3 still in range of column J2 because we had only 26 days of demurrage.
We didn't exceed a 30 Days cannot be charged out of J2.

Cheers,
Adriano Diogo

 

[jtakw]

I have some question on H3.
The total amount of demurrage cost should be $1300 instead of $600.
H3 still in range of column J2 because we had only 26 days of demurrage.
We didn't exceed a 30 Days cannot be charged out of J2.

Cheers,
Adriano Diogo

Hi,

I'm confused, if H3 result should be 1300 instead of 600, why does your I1 say 15-30 Days ?
What about H4:H5, what are the expected results for those ?

 

[KRice]

Hi Adriano, I'm confused for the same reason described by @jtakw. In post #4, you have column headings in I1:K1 that appear to describe the day ranges during which certain demurrage rates apply. What rate applies for days 1-14? In my example, I assumed $0/day for days 1-14.