Landed Cost Calculator in Excel: Test Tariff Changes With AI

Build the calculator in three blocks: an inputs sheet (unit cost, units, cartons, cubic metres, freight rate per CBM, insurance, duty rate, local charges, FX buffer), a calculation sheet that adds each cost in order, and a scenario sheet that changes the duty rate. AI drafts the formulas and checks them; the duty rate itself must come from the official tariff.

A landed cost is everything it costs to get one unit onto your shelf: the price paid to the supplier plus freight, insurance, duty, port and delivery charges, inspection, bank fees and a cushion for currency movement. Importers who price from the factory quote alone discover the gap on the first invoice. Build the sheet once on a worked example and it answers the question many importers face now: what happens to my margin if duty goes up?

Follow me on Instagram@sagnikteaches

The example: 2,000 bamboo kitchen organisers

Every figure here is illustrative. Say a small online shop buys 2,000 bamboo kitchen organisers from an overseas factory. The factory quotes $4.20 a unit FOB (free on board, at the named port of loading). They pack 20 units to a carton; each carton measures 0.06 cubic metres (60 × 40 × 25 cm) and weighs 12 kg. Here are the inputs:

Connect on LinkedInSagnik Bhattacharya

Notice the third column. Each input has a named source. That column is the most useful part of the sheet six months later, because it tells you which numbers to refresh and which to leave.

Subscribe on YouTube@sagnikteaches

Working the example by hand first

Do the sum once on paper so you can check the sheet against it.

1. Goods: 2,000 × $4.20 = $8,400.
2. Cartons and volume: 2,000 ÷ 20 = 100 cartons; 100 × 0.06 = 6.0 CBM. Weight is 100 × 12 kg = 1.2 tonnes. Many less-than-container-load rates are charged on the greater of cubic metres and tonnes, so 6.0 (volume) beats 1.2 (weight). Confirm the charging basis on your forwarder's quote.
3. Freight: 6.0 × $110 = $660.
4. Insurance: $8,400 × 110% × 0.35% = $32.34.
5. Customs value (CIF): $8,400 + $660 + $32.34 = $9,092.34.
6. Duty: $9,092.34 × 6.5% = $591.00.
7. Local costs: $240 + $180 + $150 + $45 = $615.
8. FX buffer: ($8,400 + $660) × 3% = $271.80.

Add them: $8,400 + $660 + $32.34 + $591.00 + $615 + $271.80 = $10,570.14, or $5.29 a unit. That's 26% above the factory's $4.20. If you had priced from the factory quote alone, every unit would carry a hidden $1.09 of cost.

Lay out the workbook

Three sheets keep the model readable: Inputs, Calc and Scenarios. Put every number you type on Inputs and nothing but formulas on Calc and Scenarios, so changing an assumption never means hunting through formulas.

Illustrative layout (cell addresses are examples; label every row in column A):

Inputs
B2  Units ordered          2000
B3  Unit cost (FOB)        4.20
B4  Units per carton       20
B5  Carton CBM             0.06
B6  Carton weight (kg)     12
B7  Freight $ per CBM      110
B8  Insurance rate         0.35%
B9  Insured value uplift   110%
B10 Duty rate              6.5%
B11 Destination charges    240
B12 Delivery to warehouse  180
B13 Inspection             150
B14 Bank fees              45
B15 FX buffer              3%
B16 Duty on (1 = CIF, 0 = FOB)  1

Calc
B2  Goods                  =Inputs!B2*Inputs!B3
B3  Cartons                =ROUNDUP(Inputs!B2/Inputs!B4,0)
B4  Volume (CBM)           =B3*Inputs!B5
B5  Weight (tonnes)        =B3*Inputs!B6/1000
B6  Chargeable             =MAX(B4,B5)
B7  Freight                =B6*Inputs!B7
B8  Insurance              =B2*Inputs!B9*Inputs!B8
B9  CIF value              =B2+B7+B8
B10 Duty base              =IF(Inputs!B16=1,B9,B2)
B11 Duty                   =B10*Inputs!B10
B12 Local costs            =Inputs!B11+Inputs!B12+Inputs!B13+Inputs!B14
B13 FX buffer              =(B2+B7)*Inputs!B15
B14 Total landed cost      =B2+B7+B8+B11+B12+B13
B15 Landed cost per unit   =B14/Inputs!B2

Three details earn their place. ROUNDUP on cartons stops a 1,990-unit order producing 99.5 cartons. MAX on volume and weight makes the chargeable quantity follow the forwarder's rule. The duty-base switch in B16 lets you see the effect of the valuation basis in one click. If any of these formulas are unfamiliar, the ChatGPT and Excel guide shows how to have AI explain a formula line by line.

Let AI build the first version, then audit it

AI is fast at the skeleton. Paste your inputs table and ask for the workbook layout and formulas:

I import goods and need a landed cost calculator in Excel. Build the layout as three sheets: Inputs, Calc, Scenarios. Put every typed number on Inputs with a label and a "source" column. Calc must contain only formulas.

My cost lines: goods at FOB unit price, freight charged per CBM (on the greater of volume and weight in tonnes), insurance as a percentage of 110% of goods value, duty as a percentage of either CIF or FOB value (a switch cell), destination charges, delivery, inspection, bank fees and an FX buffer applied to goods plus freight.

Give me: cell addresses, formulas I can paste, and a worked check using these figures: [paste the inputs table]. State the expected total so I can verify it.

Then reverse the roles. Ask the AI to audit the sheet you built, or one you inherited. Paste the formulas, not the data. An illustrative audit reply: "Insurance is calculated on goods only; many insurance policies value cargo at CIF plus 10%, so check the basis. The FX buffer is applied to goods and freight but not to the insurance line; confirm that is intended. Duty uses CIF with no option to switch." Every one of those points is a question for you, not an automatic fix, but they are the questions a careful colleague would ask. Excel's own =COPILOT() worksheet function was retired on 14 September 2026 (cells that still use it show #NAME? when they recalculate), so use the Copilot pane, the Claude add-in or a chat window with the formulas pasted in.

Where the duty rate comes from, and where AI must not guess

Duty depends on the tariff classification (the product code under the harmonised system, or HS, which more than 200 countries and economies use as the basis for their customs tariffs) and on the origin of the goods. AI can help with the first half of that journey: describe the product and ask for candidate headings, what each covers and what facts would separate them (material, use, how it is sold). It cannot give you a reliable rate. Rates change with trade policy, and a model's memory of them is out of date by default.

Use a four-step routine instead:

1. Ask AI for two or three candidate codes and the questions that decide between them.
2. Check each code against the official tariff schedule for your import market (the code's own text, not a summary).
3. Confirm with a customs broker or your forwarder before you place the order, especially if two codes carry different rates.
4. Record the code, the rate and the date you checked in the Inputs source column.

The paperwork side is covered in the importer product compliance checklist, and the proforma invoice that supplies your FOB figure is in the proforma invoice versus purchase order tutorial.

The tariff-change scenario

Now use the sheet for what it's best at. Suppose duty on this product class might rise. You want to know three things: the new landed cost, the new margin at your current selling price, and the price you'd need to hold your margin. Build a Scenarios sheet with the duty rate across the top and a formula that swaps just the duty line. Illustrative results with the example above and a $14.00 selling price:

The formula behind each column: total landed = base total − base duty + duty base × scenario rate. In Excel, with the base total in Calc!B14, the base duty in Calc!B11, the duty base in Calc!B10 (the CIF value while the switch is set to CIF) and the scenario rate in C2 on the Scenarios sheet, that is =Calc!$B$14-Calc!$B$11+Calc!$B$10*C2. The price for a target margin is =(per unit cost)/(1-target margin), so $6.42 ÷ 0.45 gives $14.27.

What the table says in plain terms: a 10-point rise costs you $0.45 a unit and about three points of margin, which is absorbable. A 25-point rise takes you below your 55% target at the current price, and holding it means a price of $14.27, a 2% rise on the $14.00 shelf price. That's useful: you now know that a duty shock of that size is a pricing decision, not a disaster, and you know the number to put in front of your accountant.

Duty isn't the only line a policy change touches. If your market also charges an import tax calculated on customs value plus duty, add a row for it that references the duty cell, so the tax moves with the scenario. For general scenario thinking beyond landed costs, see scenario planning with AI.

Using AI to write the scenarios, not just the maths

Ask the model to list what could move rather than only what you've thought of:

Here is my landed cost sheet structure and base figures: [paste]. List ten events that could change my landed cost per unit by more than 5%, grouped by line (goods, freight, duty, FX, local charges). For each, give the cell that would change and a plausible range I should test. Do not forecast; just give ranges to test.

A typical, illustrative reply includes: a freight rate doubling on a congested route (test $110 to $220 per CBM), a minimum charge on small shipments, a supplier price rise after the first order, a currency swing of 8%, a classification dispute that moves the duty rate to a neighbouring code, and a port delay adding storage charges. You choose which to build. Three to five scenarios on one sheet are plenty; more makes the sheet hard to read.

Parse the forwarder's quote into your inputs

Quotes arrive as PDFs and emails with charges in inconsistent order. Paste the text into an AI tool and ask it to return a table with the charge name, amount, basis (per CBM, per shipment, per container) and whether it is included in an all-in rate. Then check two things. Is each charge in your sheet exactly once? And does any charge say "subject to"? Those clauses, such as fuel adjustments or minimum volume, are where the final invoice differs from the quote. Ask the forwarder to confirm in writing what an all-in rate excludes.

Heavy goods and full containers: when per-CBM breaks down

An illustrative contrast. A landscaping company imports stone pavers: 22 tonnes in a 20-foot container. Volume is small, weight is the limit, and the forwarder quotes a flat price for the container, say $3,800 to the destination port. Freight per paver is the container price divided by the pavers in it (for example $3,800 ÷ 4,400 pavers = $0.86), not a CBM rate. The sheet's logic is the same, but the freight row is "container cost ÷ units" and the weight check is the limit to watch, since overloading a container means paying for another. Build the freight line as a choice: a switch cell for "LCL by CBM" or "container flat rate", and let the calculation follow it.

Setting the FX buffer from your own history

The 3% in the example is a placeholder. A better buffer comes from what has actually happened between agreeing a price and paying for it. Look at your last four or five orders and, for each, note the exchange rate on the day the price was agreed and on the day you paid. Illustrative history: +1.2%, −0.8%, +2.9%, +0.4%, +1.7% (positive means the goods cost you more in your own currency). The worst swing is 2.9%, the average of the adverse ones is about 1.5%, and a buffer of 3% covers the worst case you've seen. If your orders are paid in instalments (a deposit on order and the balance before shipping), apply the buffer to the balance only, since the deposit rate is already fixed.

A buffer isn't a forecast. Its job is to stop a normal swing from turning a profitable order into a loss. If the sheet shows the buffer unused after a shipment, the money is margin, and you can reduce the buffer next time. If you pay in your own currency or hold the supplier's currency in an account, set the buffer to near zero and say so in the source column.

Comparing two suppliers on landed cost, not factory price

A second use of the sheet is choosing between quotes. Say the same organiser is quoted by two factories. Factory A quotes $4.20 with 20 units per carton at 0.06 CBM. Factory B quotes $3.95 but packs 12 units per carton at 0.07 CBM, because its cartons are bigger and less tightly packed. At 2,000 units each, with freight at $110 per CBM and everything else identical:

• Factory A: 100 cartons × 0.06 = 6.0 CBM, freight $660; goods $8,400.
• Factory B: 2,000 ÷ 12 = 167 cartons (rounded up) × 0.07 = 11.69 CBM, freight $1,285.90; goods $7,900.

Before insurance, duty and the rest, A costs $9,060 and B costs $9,185.90. The cheaper factory price loses by $125.90 once freight is counted, and duty at 6.5% on the higher CIF value widens the gap a little further. The point isn't that B is worse; it's that the cost per carton matters as much as the cost per unit. If B will repack to 20 units in a smaller carton, ask for a new quote. The supplier-side conversation is covered in the negotiation emails tutorial.

From landed cost to a shelf price

Landed cost is a floor, not a price. Add the costs that come after the goods arrive: warehouse and pick and pack, packaging, payment processing, returns allowance, marketing and your own overheads. A quick way is to work backwards from the price. Illustrative: you sell at $14.00; payment fees take about 3% ($0.42); shipping to the customer is charged separately; a returns and damage allowance of 4% ($0.56); fulfilment is $1.80. That's $2.78 before landed cost, and $5.29 landed cost brings it to $8.07. Gross profit is $5.93 a unit, or 42.4%. At the 25-point duty scenario, landed cost is $6.42 and profit falls to $4.80 a unit, 34.3%. This second view tells you more about survival than the landed margin does, so build it as a fourth sheet linked to Calc.

Stock already on the water: mixed-rate batches

A duty change doesn't apply to goods already imported, only to those arriving after the change takes effect (check the exact rule, because some measures apply to goods in transit). If you hold 600 units bought at the old rate and 1,400 more arrive at the new one, your average landed cost is a blend. Keep a batch column in your stock records: units, landed cost per unit, date landed. Then average cost is the sum of units times cost divided by total units, and you can decide whether to price new batches at the blend or at replacement cost. Pricing at replacement cost protects your ability to reorder; pricing at the blend sells through faster. It's a business decision, and the sheet's job is to show both numbers.

Mistakes that make a landed cost look better than it is

An illustrative first shipment. A shop owner builds a calculator, pastes the factory price and the forwarder's all-in rate, and lands at $4.90 a unit. The invoice comes in at $5.40. What was missing: origin charges that the forwarder's "all-in" rate listed as excluded, the delivery leg from port to warehouse, the inspection fee and a payment fee. None was a surprise to the forwarder; all of them were a surprise to the buyer. The sheet above has a row for each, and the Source column for "all-in rate" now says "check what's excluded". Run this test on every quote: for each cost row, who invoices it, and do I hold a document that shows it?

Keeping the sheet honest over time

• Date every external number. Add a "checked on" column beside duty rate, freight rate and exchange rate. A rate older than the shipment's quote date is a risk.
• Compare estimate and actual. After each shipment, add a column for the invoiced cost per line and the difference. After three shipments you'll see which lines you always underestimate and can adjust the buffer line accordingly.
• Protect the formulas. Lock the Calc sheet so a stray typed number can't replace a formula.
• Keep the scenarios. A scenario you built last quarter is a starting point for the next trade-policy headline.
• Feed the result forward. Landed cost per unit is the input for pricing and reorder decisions, and for working out what a supplier's price cut or a lower minimum order is really worth.

Check the sheet in ten minutes

1. Rebuild the example's total from the inputs by hand; the sheet must give $10,570.14.
2. Change units to 1,990 and check the cartons round up to 100.
3. Set the duty base switch to FOB and check duty falls to $546.00 (8,400 × 6.5%).
4. Set duty to 0% and check the total falls by exactly the duty line.
5. Double the freight rate and check duty rises too, as CIF value includes freight.
6. Look for any typed number on the Calc sheet. There should be none.

If you'd rather build the sheet around your own product range with someone who has seen the pitfalls, that's what a 1:1 call is for.

Landed cost questions importers ask

Which duty value should I use: FOB or CIF?

It depends on how the market where you import values goods for duty. Many apply duty to the cost of the goods plus freight and insurance to the border (CIF), others to the goods price at the exporter's port (FOB). Ask your customs broker, and make the basis a switch in your sheet so you can change it.

Can AI tell me my duty rate?

Not reliably. Rates depend on the product's tariff classification code and change with trade policy. AI can suggest candidate codes and explain what a heading covers, but the rate must come from the official tariff schedule or your broker, and you should record the date you checked it.

How big should my FX buffer be?

Set it from your own history: look at how far the rate moved between agreeing a price and paying in your last few orders, then use the larger swing or a round figure above it. Three per cent is a placeholder in this tutorial, not a recommendation.

Does landed cost include the cost of unsold stock?

Not in the per-unit figure. Landed cost covers getting the goods to your door. Wastage, returns, storage and unsold stock belong in a separate margin or cash-flow view, though a small allowance for damaged units can sit in the sheet as its own row.

Further reads

• AI for Freight Business Analytics: Margin by Lane From a Spreadsheet — How freight margins look from the forwarder side.
• Find Overseas Suppliers With AI: Shortlists, RFQs and Red Flags — Get the supplier quotes this sheet compares.
• Pre-Shipment Inspection Checklist: AI Drafts Your Inspector Brief — Cost and brief the inspection line in your sheet.
• Demurrage and Detention: AI Tracks Free Days and Drafts Disputes — Costs that arrive after the quote.
• AI for Ecommerce Inventory Management: Reorder Points and Stockouts — Use landed cost in reorder decisions.
• 3PL Pricing Calculator: AI Quotes Storage, Pick and Pack Rates — Add warehouse costs after the goods land.
• Alibaba Sourcing With AI: Vet Suppliers and Plan a Sample Order — Vet factories and samples before you compare quotes.
• AI Freight Quoting: Turn Customer Emails Into Draft Quotes — See how forwarders build the quotes you parse.
• Supply Chain AI Software: What a Small Importer Needs First — A buying checklist for small importers covering data, lead times, supplier emails and stock sync, with one worked reorder to test any vendor against.
• AI for Coffee Roasters: Batch Logs, Wholesale Orders, Labels — A worked week for a small roastery: a roast log, a wholesale order email and label copy, with the checks that keep weights and allergens right.
• AI for Customs Brokers: Draft Client Updates and Entry Checks — Draft shipment status emails from your file notes and run a consistency pass on invoices and packing lists, with the licensed broker still signing the entry.
• Free Shipping Threshold: Set It From Your Average Order Value — A worked order-export example: find your average order value, price the shipping you give away, and test two thresholds with ChatGPT or Claude.
• Freight Invoice Audit: AI Checks Carrier Bills Against Quotes — Turn a carrier quote and invoice into two tables, let AI list every difference, sort the accessorial charges and send the dispute from a weekly routine.
• Freight Rate Sheets: AI Extracts Carrier Rates From PDFs — A step-by-step way to pull carrier rates out of PDFs and emails into one clean sheet, with validity dates, surcharges and a spot-check routine.
• Kickstarter Campaign Page: Draft the Story and Reward Tiers With AI — Draft the story and video script with AI, then price each reward tier so fees and shipping don't eat the margin.
• Can AI Schedule Production for a Small Maker? A Spreadsheet Test — A seven-job test of AI scheduling from a spreadsheet: setups, due dates, a rush order, how to check the result and when software is worth buying.
• Can ChatGPT Find the Right HS Code? How to Check Before You File — How to use ChatGPT for a first-draft HS code, a ten-product test you can run on your own goods, three worked products and the checks before filing.
• AI Tools and AI Development: The Complete 2026 Guide — the AI hub, including every tutorial in the AI-for-business series.

Sources: Incoterms names (FOB, CIF) and general customs-valuation practice, WCO guidance on the harmonised system, standard less-than-container-load charging by cubic metre or tonne, and Microsoft's support note that Excel's COPILOT function was retired on 14 September 2026. Duty rates in the example are illustrative; use your official tariff schedule.