Logistics & shipping

Freight Rate Comparison

Quotes from every carrier on one lane, landed to the same all-in number, with the surcharges that usually do the damage broken out

Download the template — free

✓ No account, no email · ✓ Excel 2016+ and Google Sheets · ✓ Free for commercial use

Freight quotes are not comparable as received. One carrier prices the ocean leg and adds six surcharges, another quotes all-in and is slower, a third is cheapest per container and charges more for the same THC at destination. The only honest comparison is a single landed figure per lane, with each surcharge visible so you can argue about the ones that are negotiable. That is all this sheet does, and it is usually worth more than the negotiation itself.

What the worksheet looks like

The actual columns, with sample rows

Quotes sheet: base rate and surcharges in, all-in and cost per day out.
A	B	C	D	E	F	G	H	I	J	K	L
1	Lane	Carrier	Equipment	Base	BAF	THC origin	THC dest	Docs	All-in	Transit	Per day
2	Tema → Rotterdam	MSC	40HC	1,850	310	145	190	65	2,560	22	116
3	Tema → Rotterdam	Maersk	40HC	1,690	395	160	240	90	2,575	19	135
4	Tema → Rotterdam	CMA CGM	40HC	2,050	0	150	185	45	2,430	26	93

The formulas that do the work

Every one of these is in the file you download

=IF($B5="","",SUM($D5:$H5)) All-in cost. The comparison that matters, and the one no quote arrives as.
=IFERROR($I5/$J5,"") Cost per transit day. A cheaper box that takes a week longer is not cheaper if the cargo is financed or the customer is waiting.
=IF($B5="","",IF($I5=SUMPRODUCT(MIN(IF($A$5:$A$204=$A5,$I$5:$I$204))),"Cheapest","")) Marks the lowest all-in on each lane, so the winner is read rather than eyeballed across a wide table.
=IF($B5="","",IFERROR($I5/SUMPRODUCT(MIN(IF($A$5:$A$204=$A5,$I$5:$I$204)))-1,"")) How much more than the cheapest, as a percentage. A 0.6% gap and a 19% gap are different conversations, and the raw totals hide which is which.
=IFERROR(AVERAGEIFS(Quotes!$E$5:$E$204,Quotes!$A$5:$A$204,$A5),"") Average bunker surcharge on the lane — the line most worth challenging, because it varies most between carriers quoting the same route.

The cheapest quote was the most expensive

A worked example with real numbers

Three carriers quote Tema to Rotterdam for a 40HC. Maersk has the lowest base rate at 1,690 and looks like the winner on the email. Landed, they are 2,575 — the highest of the three — because their bunker surcharge is 395 against MSC's 310 and CMA CGM's nothing, and their destination terminal handling is 240 against 185. CMA CGM lands at 2,430 and is 145 cheaper, but takes 26 days against Maersk's 19. At 93 per transit day against 135, that is the right choice for cargo that is not urgent and the wrong one for cargo that is. None of this is visible in the three emails the quotes arrived in.

What is in the workbook

Every tab, and what it holds

Every tab in the workbook and what it is for.
Tab	Contents
README	What to fill in and what is calculated, with the colour key.
Quotes	One row per carrier per lane, 200 rows, with all-in and cost per day derived.
By lane	Cheapest carrier, spread between best and worst, and average surcharges.
Surcharges	A reference list of the common codes and what each one is for.

Features and related templates

What is included, and what to look at next

What it does

Every quote landed to one all-in figure
Surcharges broken out so the negotiable ones are visible
Cost per transit day alongside cost per container
Cheapest marked automatically per lane
Excel 2016+ and Google Sheets

Related templates

Need it built around your own data?

Your own lanes, carriers and milestone names
Connected to your forwarder's exports
Delivered within 24 hours

Get your Excel job done

Questions about this template

The ones that come up most

Our quotes use different currencies. How do I handle that?

Convert before entering, or add a rate column and multiply the base and surcharges by it. Mixing currencies inside the all-in column is the one thing that will silently produce a wrong winner.

Should I include inland haulage?

Only if you are comparing door-to-door on every quote. The rule is that the all-in columns must cover the same scope for every row on a lane, or the comparison is meaningless — a port-to-port quote will always beat a door-to-door one.

How often should rates be refreshed?

Ocean spot rates move weekly and contract rates quarterly. Keep the old rows and add new ones with a quote date rather than overwriting: the history is what tells you whether a carrier's increases track the market or exceed it.