Calculating rental payable with multiple factors

[LofungMick]

Hello Mr. Excel and Team,

My Problem That Needs Solving: How to calculate the detention payable on a shipping container leased from a Shipping Line given a number of billable days and a daily rate which can either be a flat rate or tiered depending on type of container. So we deal with many Shipping Lines who in turn deal in many different types of shipping containers with each Shipping Line applying different free rental periods and different daily rates.

As a standard practice across all Shipping Lines, there will be a START DATE, centred round when the vessel arrives at the Port of Discharge, from which date a “detention free” period is given. After this free period has ended, a daily charge (called ‘detention’) is applied ending when the container is returned empty or “de-hired”. Think rental with a bonus ‘rent free’ period at the beginning. There is no charge if the container is returned within the free period.

Most of this can be solved using simple IF Functions involving dates, nested where necessary. But there are two calculations which require more sophisticated solutions.

The first is to identify the correct combination of Shipping Line and Type of Container in question and only then can you find the correct number of “Rental Free Days”. In turn this allows you to calculate the end of the free period and thus the date when the Shipping Line will start charging its daily rate applicable to that type of container.

This part I have worked out how to do using a look up table in three columns showing Shipping Line, Type of Container and the Number of Days for that combination. Then in the main worksheet an Index-Match with an array formula as follows:
{=INDEX('FREE DAYS LOOKUP'!$A$1:$C$90,MATCH(1,('FREE DAYS LOOKUP'!$A$1:$A$90=I2)*('FREE DAYS LOOKUP'!$B$1:$B$90=H2),0),3)}

Eventually you will end up with the number of ‘billable days’. At this point you multiply that number by the daily rate. The result is what we owe to the Shipping Line for that container and is the end of the process.

So this second solution is what I need help with. If done manually, we must first identify the Shipping Line and Type of Container (has to be the same combination as above in the Rental Free Days iteration). Then using a daily rates table you determine the different tiers for that combination, apply the applicable daily rate at each tier then add up the accumulated values. So there are many conditions/variables involved.

• As an observation, it would appear that the first part of any solution will be similar to the Free Days INDEX-MATCH because you would have to, once again, identify the correct Shipping Line and Type of Container combination. The steps after that would have to be similar to calculating tax payable against a progressive tax scale. But after that I am stuck.

Example Calculation. Let us say the combination established in that final step above is as follows:

The Shipping Line is “ANL” and the container in question is a “20’DY container”. And say total billable days are 90 days (i.e. after allowing for the free days). Therefore, using the rates for that combination of Shipping Line and Type of Container, the total detention payable is $4,125.00 calculated as follows:
Free Days (0-25 days) = 25 days @ $0.00 $0.00
Tier 1 (26-40 days) = 15 days @ $25.00 $375.00
Tier 2 (41-60 days) -= 20 days @ $45.00 $900.00
Tier 3 (61 ++ days) = 30 days @ $95.00 $2,850.00
Total Owing $4,125.00

A final question: If a Shipping Line changes any of the variables (types of containers, free days, tiers, daily rates), how would that be handled in the design? Any such changes must apply only from the effective date going forward; they must not be allowed to affect historical data. So in the “free days” look up/INDEX-MATCH solution, I suppose you could create a new look up table with a different name and amend the appropriate part of the formula in the main worksheet, then go forward from there.

An example of a rates table (part of) is below. Having trouble with HTML Maker showing a second example as a representative sample.

I would be most appreciative for any help or pointers. Thank you.


Excel 2012

	A	B	C	D

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #EDEDED"]ANL Detention Rates[/TD]
[TD="bgcolor: #EDEDED, align: right"][/TD]
[TD="bgcolor: #EDEDED, align: right"][/TD]
[TD="bgcolor: #EDEDED, align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #EDEDED"]Import Detention Free Days: 14 or 25 days depending on type of container, from date container is discharged full.[/TD]
[TD="bgcolor: #EDEDED"][/TD]
[TD="bgcolor: #EDEDED"][/TD]
[TD="bgcolor: #EDEDED"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #EDEDED, align: center"]Container
Type[/TD]
[TD="bgcolor: #EDEDED, align: center"]Free Time
(Days)[/TD]
[TD="bgcolor: #EDEDED, align: center"]After Free Time[/TD]
[TD="bgcolor: #EDEDED, align: center"]Rates[/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #EDEDED"]20FT GP[/TD]
[TD="bgcolor: #EDEDED, align: center"]25 Days[/TD]
[TD="bgcolor: #EDEDED, align: center"]26 - 40 days[/TD]
[TD="bgcolor: #EDEDED, align: center"]$25.00[/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #EDEDED"][/TD]
[TD="bgcolor: #EDEDED, align: center"][/TD]
[TD="bgcolor: #EDEDED, align: center"]41 - 60 days[/TD]
[TD="bgcolor: #EDEDED, align: center"]$45.00[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #EDEDED"][/TD]
[TD="bgcolor: #EDEDED, align: center"][/TD]
[TD="bgcolor: #EDEDED, align: center"]61 days onwards[/TD]
[TD="bgcolor: #EDEDED, align: center"]$95.00[/TD]

[TD="align: center"]7[/TD]
[TD="bgcolor: #EDEDED"]20FT RF[/TD]
[TD="bgcolor: #EDEDED, align: center"]14 Days[/TD]
[TD="bgcolor: #EDEDED, align: center"]15 days onwards[/TD]
[TD="bgcolor: #EDEDED, align: center"]$120.00[/TD]

</tbody>