How to Build a Landed-Cost Spreadsheet Model, Step by Step

Most e-commerce importers price their products wrong. Not by a little — by 15% to 30%. They forget port fees, misclassify their HTS code, or ignore the first-mile cost from the factory. By the time the goods hit their 3PL, the margin they modeled is gone.

A landed-cost model fixes this. It tells you the total cost to get one unit from factory floor to your customer's door — before you place the purchase order. Here's how to build one in a spreadsheet, cell by cell.

What "Landed Cost" Actually Means

Landed cost = every cost incurred to move goods from your supplier to a defined end point. That end point is usually your U.S. warehouse or fulfillment center.

The formula at its simplest:

Landed Cost = Factory Cost + Export Charges + International Freight + Insurance + Import Duties & Fees + Customs Brokerage + Domestic Delivery

Each of those line items has sub-components. Miss one and your model is wrong.

Step 1: Set Up Your Spreadsheet Structure

Before entering a single number, get the architecture right.

Create two tabs:

Inputs — all your raw variables (unit cost, weight, freight quote, duty rate)
Model — formulas that pull from Inputs and calculate per-unit and per-shipment costs

This separation matters. When your freight rate changes or you reclassify your HTS code, you update one cell in Inputs and everything recalculates automatically.

In the Model tab, build two views:

Per-shipment total cost
Per-unit landed cost (divide shipment totals by unit quantity)

You'll use the per-unit figure for pricing. You'll use the per-shipment figure for cash flow planning.

Step 2: Define Your Cost of Goods (FOB or EXW)

Start with what you're paying the factory. Specify the Incoterm — this determines where the supplier's responsibility ends and yours begins.

EXW (Ex Works): You pay everything from the factory door. Most expensive for you to manage, but cheapest quoted price.
FOB (Free On Board): Supplier covers export clearance and loading onto the vessel. This is the most common term for China-origin goods.
CIF (Cost, Insurance, Freight): Supplier quotes you a price that includes freight and insurance to the destination port. Looks simple but hides markup.

Use FOB or EXW as your baseline. CIF obscures the real freight cost and makes it impossible to compare suppliers accurately.

In your Inputs tab, enter:

Unit FOB Price (USD) — e.g., $12.50
Units Per Shipment — e.g., 2,000
Total FOB Value = Unit FOB × Units (formula: =B2*B3)

Step 3: Add Origin / Export Charges

If you're on EXW terms, you're paying for the inland truck to the port, export customs declaration, and terminal handling charges in the origin country.

If you're on FOB, your supplier handles this — but you should still understand the cost so you can negotiate.

Typical origin charges for a 20-foot container (FCL) from Shenzhen, China:

Inland trucking to port: $150–$300
Export customs declaration (EX doc fee): $50–$80
Origin terminal handling charge (OTHC): $100–$200
Fumigation (if required for wood packaging): $60–$100

For LCL (Less than Container Load) shipments, these costs are often bundled into the freight quote. Ask your forwarder to itemize them.

In your spreadsheet, create a section called Origin Charges with a line for each fee. Sum them into a Total Origin Charges cell.

Step 4: International Freight

This is usually your biggest variable cost. Get an actual quote from a licensed freight forwarder — don't use a ballpark.

Key inputs:

Shipment mode: Ocean FCL, Ocean LCL, or Air
CBM (cubic meters): Length × Width × Height (cm) ÷ 1,000,000 × number of cartons
Gross weight (kg)
Chargeable weight: For ocean LCL, freight is usually charged per CBM or per 1,000 kg (W/M), whichever is greater. For air, it's volumetric weight vs. actual weight.

Example: 2,000 units, 24 cartons, each carton 60×40×40cm and 18kg gross.

Total CBM: (0.6 × 0.4 × 0.4) × 24 = 2.304 CBM
Total weight: 18 × 24 = 432 kg
Ocean LCL rate at $85/CBM = $195.84

Add a line for Bunker Adjustment Factor (BAF) or fuel surcharge — typically 10–25% on top of base ocean freight. Carriers bill this separately and it changes monthly.

Also add:

Bill of Lading fee: $50–$75
Destination port handling / Terminal Handling Charge (THC): $300–$500 for FCL at major U.S. ports (Los Angeles/Long Beach)
ISF Filing (10+2): $35–$50, required by CBP under 19 CFR Part 149 for all ocean imports

Step 5: Marine Cargo Insurance

Don't skip this. Marine cargo insurance is not included in most freight quotes unless you're on CIF terms.

Standard rate: 0.3%–0.6% of cargo value (CIF value)

Formula: =Total FOB Value * 1.1 * 0.004

The 1.1 multiplier adds 10% to account for freight costs (standard industry practice per Institute Cargo Clauses). The 0.004 = 0.4% premium rate.

On a $25,000 FOB shipment, that's roughly $110 in insurance — cheap protection against a total loss.

Step 6: Import Duties

This is where most importers make their biggest mistake. They guess the duty rate.

Don't guess. Look up your HTS code.

Go to hts.usitc.gov and find the 10-digit HTSUS classification for your product. The general rate of duty is in Column 1. If your goods are subject to Section 301 tariffs (most China-origin goods), there's an additional rate in a separate column — currently ranging from 7.5% to 25% depending on the list.

Example:

Product: Silicone kitchen spatulas
HTS: 3924.10.4000
General duty rate: 3.4%
Section 301 List 3 additional tariff: 25%
Total effective duty rate: 28.4%

Duty is calculated on the customs value, which is the transaction value (essentially your FOB price) under 19 CFR Part 152.

Formula: =Total FOB Value * (General Rate + Section 301 Rate)

On $25,000 FOB: $25,000 × 28.4% = $7,100 in duties

Also add:

Merchandise Processing Fee (MPF): 0.3464% of FOB value, min $31.67, max $614.35 per entry (19 CFR 24.23)
Harbor Maintenance Fee (HMF): 0.125% of FOB value — applies to ocean imports only

Step 7: Customs Brokerage

You need a licensed customs broker (19 CFR Part 111) to file your entry with CBP unless you self-file.

Typical broker fees:

Entry filing fee: $75–$150
ISF filing (if not already paid): $35–$50
Bond: Either a single-entry bond (~$50–$100 for a $25,000 shipment) or an annual continuous bond ($500/year for up to $50,000 annual duty liability)
ABI transmission fee: $25–$40
Document handling: $25–$50

Total brokerage cost for a small shipment: roughly $200–$400.

If you're importing regularly (more than 3–4 times per year), get a continuous bond. It pays for itself quickly.

Step 8: Domestic Delivery (Port to Warehouse)

The final leg is often underestimated.

Drayage (container pickup at port to your consolidator or devan facility): $350–$700 for a 20-foot container in LA/LB
Devan / unloading fee at 3PL: $150–$350 depending on palletization
Inland trucking to your warehouse: Varies widely. LA to a Chicago 3PL via truckload can run $1,800–$2,500 depending on fuel

For LCL shipments, drayage and delivery are usually bundled into the freight forwarder's quote as a door-to-door rate. Confirm this explicitly.

Step 9: Calculate Per-Unit Landed Cost

Once you have every cost line totaled, the per-unit calculation is straightforward:

Per-Unit Landed Cost = Total Shipment Cost ÷ Units Per Shipment

Example summary for 2,000 units at $12.50 FOB each:

Cost Line	Shipment Total
FOB Cost	$25,000
Origin Charges	$350
Ocean Freight (LCL)	$420
Marine Insurance	$110
Import Duties (28.4%)	$7,100
MPF	$86.60
HMF	$31.25
Customs Brokerage	$275
Drayage + Delivery	$650
Total Landed Cost	$34,022.85

Per-unit landed cost: $17.01

Your supplier quoted you $12.50. Your actual cost is $17.01 — a 36% increase. That's the number you price from.

Step 10: Build in a Buffer and Update Regularly

Add a 5–8% contingency line to your model. Freight rates change weekly. Duty rates can change with 30 days' notice. A container can sit at port for 10 days and rack up $150/day in demurrage.

Set a calendar reminder to refresh your freight quotes every quarter and check the HTSUS for any duty rate updates, especially if you're sourcing from China, Mexico, or countries with active trade proceedings.

Final Checklist Before You Price a Product

HTS code confirmed (not assumed)
Section 301 tariff checked
Freight quote is itemized, not bundled
Insurance line included
MPF and HMF calculated
Domestic delivery quote obtained
Per-unit cost confirmed against target margin

A landed-cost model isn't a one-time project. It's a living document. The brands that maintain it rigorously are the ones that stay profitable when market conditions shift.

Ready to stop guessing at your import costs? Get started with Regenerate Trade and let our team build and validate your landed-cost model before your next purchase order.