CELLWRIGHT

Calculator › Guide

How to Calculate Landed Cost per Unit for a Multi-SKU Import Shipment (Excel & Google Sheets Formulas)

Freight allocation by CBM, FOB vs CIF, stacked tariffs, fee caps and target-margin pricing

Cellwright · Updated 22 Nov 2026 · 7 min read

If you import physical products, the number on the supplier's PI is not your cost of goods. Your real cost is that invoice converted at the rate your bank actually gave you, plus your share of the freight bill, insurance, port and broker charges, base duty, any extra tariff, and the per-entry processing fee — divided by the units you can actually sell.

This walkthrough builds that calculation in a spreadsheet, step by step, for a whole multi-SKU shipment. It uses plain Excel / Google Sheets functions (SUM, IF, VLOOKUP, MIN, MAX) so it works anywhere. Every number below comes from one worked example: 12 SKUs of homewares, 15,800 units, one ocean FCL container.

If you only need one SKU right now, the free landed cost calculator does steps 1–6 in your browser.

The shipment we're costing

Item Value
SKUs / units 12 SKUs, 15,800 units, 55.51 CBM
Goods (after FX) $70,007
Ocean freight $4,850
Insurance $310
Origin, port/terminal, broker, inland, other $180 + $640 + $295 + $520 + $150 = $1,785
Customs basis FOB (US)
Processing fee 0.3464% of customs value, min $32.71, max $634.62 per entry (illustrative)
Port/harbour fee 0.125% of customs value, ocean only (illustrative)

The duty and tariff rates in this example are illustrative. Always use the rates on your own entry or from your broker.

Step 1 — Convert goods to your home currency (with a buffer)

Put an FX table on a Settings tab: currency code in column E, "home currency per 1 unit" in column F. Then, per SKU:

Goods value = Qty × Unit cost × FX rate × (1 + FX buffer)
=F5*G5*VLOOKUP(E5,Settings!$E$5:$F$14,2,FALSE)*(1+Settings!$B$9)

The 2% buffer is not padding for fun. The rate you see on a website is the mid-market rate; the rate your bank or payment provider settles at is worse. If you price off mid-market, every margin you calculate is slightly fictional.

Example: SKU CW-101, 1,200 bamboo board sets at ¥38.50, CNY rate 0.139 → 1,200 × 38.50 × 0.139 × 1.02 = $6,550.24.

Step 2 — Pick a freight allocation basis (this is where most sheets go wrong)

You get one freight invoice for the container. You have to split it across SKUs. Most templates split it by value because that's the column they already have. But ocean freight is bought by volume (CBM), and air freight by chargeable weight.

Make the basis a dropdown (Value, Weight, Volume, Units) and compute a driver per row:

Driver = IF(basis="Value", GoodsValue, IF(basis="Weight", Qty×kg, IF(basis="Volume", Qty×CBM, Qty)))
Share  = Driver ÷ SUM(all drivers)
Allocated freight = Share × Freight

Here's why it matters. Look at two SKUs from the shipment:

SKU Share of goods value Share of CBM
CW-111 Spice rack (bulky, cheap) 6.4% 12.6%
CW-106 Silicone mats (flat, dense) 5.8% 4.9%

The spice rack takes up twice as much of the container as its value suggests. Split by value, its landed cost is $12.07 per unit and it looks marginal (1.1% net margin at a $27.50 price). Split by volume — the way the forwarder actually charged you — it's $12.95 and it is losing $0.57 on every sale.

Same shipment, same total cost. Only the split changed, and it changed a pricing decision.

Apply the same share to insurance and to the other shipment charges (port, broker, inland).

Step 3 — Build the customs value correctly (FOB vs CIF)

Duty is a percentage of customs value, and which costs go into customs value depends on the importing country:

Customs value = GoodsValue + IF(Settings!$B$7="CIF", AllocFreight + AllocInsurance, 0)

Make it a dropdown, not a hardcoded assumption. A template that silently assumes one basis is wrong for roughly half the people who download it.

Step 4 — Stack duty and extra tariffs additively

Each tariff programme is a percentage of the same customs value. They add; they don't compound.

Base duty    = CustomsValue × BaseDuty%
Extra tariff = CustomsValue × ExtraTariff%
AD/CVD       = CustomsValue × ADCVD%

A 3.2% base rate plus a 30% extra tariff is 33.2% of customs value — not (1.032 × 1.30) − 1 = 34.2%. Keep each programme in its own column. When a tariff changes, you want to see exactly which line moved.

For CW-101 on an FOB basis: $6,550.24 × 3.2% = $209.61 base duty, and $6,550.24 × 30% = $1,965.07 extra tariff.

Across the whole shipment: $4,502 base duty, $17,683 extra tariffs and $1,319 AD/CVD (on the cast-iron skillet line).

Step 5 — Apply per-entry fees with their min and max

Processing fees such as the US Merchandise Processing Fee are charged per entry with a floor and a ceiling. Applying 0.3464% to each SKU separately ignores the cap. Compute the fee once for the shipment, then allocate it:

Shipment fee = MIN(MAX(TotalCustomsValue × 0.3464%, 32.71), 634.62)
SKU fee      = SKU customs value ÷ total customs value × Shipment fee

Our shipment: $70,007 × 0.3464% = $242.50, which sits between the min and max, so $242.50 is allocated. On a $250,000 entry, the cap would kick in and the flat-% approach would overstate the fee by about $230.

The ocean port/harbour fee is simpler — a straight percentage, but only when the mode is ocean:

=IF(LEFT(Settings!$B$6,5)="Ocean", CustomsValue × 0.125%, 0)

If you're VAT/GST-registered and reclaim import VAT, leave it out of landed cost (it's a cash-flow item, not a cost). If you can't reclaim it, add it in.

Step 6 — Divide by sellable units, not ordered units

Total landed  = Goods + Freight + Insurance + Other + Duty + Tariff + ADCVD + Fees (+ VAT if not recoverable)
Sellable units = Qty × (1 − Shrink%)
Landed / unit  = Total landed ÷ Sellable units

Damaged cartons, QC rejects and samples are real. CW-107 (glass jars) has 4% breakage: dividing by 700 units instead of 672 understates its unit cost by 4%.

Shipment result: $70,007 of goods becomes $100,785 landed — a 44% uplift. Average landed cost: $6.49 per sellable unit. Duties and tariffs alone are 23.3% of landed cost.

Step 7 — Turn landed cost into a price

Now the part that actually changes your listings. With marketplace + payment fees as a % of price (f), a fixed fee per order, fulfilment cost per unit and a target net margin (m):

Break-even price = (Landed + Fulfilment + FixedFee) ÷ (1 − f)
Target price     = (Landed + Fulfilment + FixedFee) ÷ (1 − f − m)

Guard the denominator with IF(1-f-m<=0, 0, …) so a typo doesn't throw #DIV/0! across the sheet.

With 15% referral + 2.9% payment fees, $0.30 per order and a 25% target margin:

SKU Landed / unit Current price Margin Target price Status
CW-101 Board set $7.98 $34.99 40.1% $25.71 On target
CW-105 Skillet $15.03 $39.95 19.2% $44.02 Below target
CW-107 Glass jars $15.14 $34.00 9.0% $43.51 Below target
CW-111 Spice rack $12.95 $27.50 −2.1% $40.55 LOSS

Add a status column (IF(profit<0,"LOSS",IF(margin<target,"Below target","On target"))) and conditional formatting, and the SKUs you need to reprice jump out.

Step 8 — Stress-test before the next tariff announcement

Copy the landed-cost columns into a Scenarios tab with three levers: extra tariff +X points, supplier currency +Y%, freight +Z%. Recompute customs value (FX changes it, and on CIF so does freight), then duties at (base + extra + AD/CVD + shock).

On this shipment, +10 points of tariff, +5% FX and +30% freight adds $13,498 (13.4%) to landed cost. The spice rack would need $43.89 to hold a 25% margin. That is the number to have ready before you place the next PO, not after the container lands.

Checklist: the five mistakes this fixes

  1. Pricing at mid-market FX instead of your settlement rate.
  2. Splitting ocean freight by value instead of CBM.
  3. Hardcoding FOB or CIF.
  4. Compounding tariffs instead of adding them.
  5. Dividing by ordered units instead of sellable units.

Get the sheet

Rates in this article are illustrative and not customs advice. Confirm rates and valuation rules with your tariff schedule or a licensed customs broker.