Excel guide · 6 min read
How to calculate landed cost in Excel
Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Item | Units | Unit cost | Kg per unit | Duty rate | Selling price | Product value | Line weight | Freight | Insurance | Fees | |
| 2 | Stoneware mug | 1200 | 2.1 | 0.45 | 0.08 | 14 | 2520 | 540 | 696.65271967 | 80.116571731 | 58.418333554 | |
| 3 | Glass carafe | 300 | 6.8 | 1.2 | 0.04 | 32 | 2040 | 360 | 464.43514644 | 64.856272354 | 47.291031925 | |
| 4 | Linen napkin set | 500 | 4.25 | 0.3 | 0.12 | 24 | 2125 | 150 | 193.51464435 | 67.558617035 | 49.261491588 | |
| 5 | Cast iron trivet | 240 | 3.6 | 1.6 | 0 | 18 | 864 | 384 | 495.39748954 | 27.468538879 | 20.029142933 | |
| 6 | Total | 2240 | 7549 | 1434 | 1850 | 240 | 175 |
Download this sheet as an Excel practice file (.xlsx)
What goes into landed cost, and the layout
The supplier's invoice is only part of what an imported product costs. Freight, cargo insurance, customs duty, and broker or port fees all have to be paid before the goods are sellable. Price from the invoice alone and every margin you calculate is too high. For a domestic purchase where freight is the only extra, the simpler cost per unit formula, (invoice + freight) / units, is enough.
The sample is a homewares shop's sea shipment with four products. A:F are typed: units, unit cost from the supplier, weight per unit, the duty rate for that product and your selling price. Shipment-level costs sit in input cells in S2:S4: 1,850 freight, 240 insurance, 175 broker and port fees. H:P do the sharing out, and row 6 holds the totals.
The duty rates in column E are placeholders for the example. The real rate depends on each product's tariff code and the countries involved; look it up with your customs authority or broker and type it in. If the supplier invoices in another currency, convert the unit costs first: see convert currency in Excel.
- Type the order lines in A2:F5 and the shipment costs in S2:S4.
- In H2 enter
=B2*C2(product value) and in I2=B2*D2(line weight). Fill both down and total them in row 6. - Add the allocation formulas from the next sections in J2:P2 and fill down.
Freight: share it by weight, not by value
Each line's share of a cost is the line's part of the total, times the cost. For freight, the carrier charges by weight (or volume), so share it by weight: line weight / total weight x freight. Lock the cost cell and the total with $ so it fills down.
This choice matters most for heavy, cheap items. The cast iron trivets are 11% of the order's value but 27% of its weight. Shared by value they'd carry 211.74 of the freight; shared by weight they carry 495.40. Get it wrong and the trivet's landed cost comes out at 4.68 instead of 5.86, so it looks far more profitable than it is while the mugs quietly pay for its shipping.
Result: 495.39748954
495.40 of the 1,850 freight goes to the trivets: 384 kg of the shipment's 1,434 kg. The mugs (540 kg) carry 696.65.
My freight is charged by volume
For sea freight in a shared container, the charge is often by cubic metres. Add a Volume per unit column and share by line volume instead of weight; the formula is the same shape.
Insurance, fees and duty
Cargo insurance is priced on the value of the goods, so share it by value: =$S$3*H2/$H$6. Broker and port fees are usually a flat charge per shipment; sharing them by value is the common choice, and sharing by units is fine too, as long as you do it the same way every time.
Duty is different: it isn't a shipment cost to share, it's charged per product at that product's rate. So each line's duty is its own value times its own rate in column E, and a duty-free line (the trivets here, in the example rates) pays nothing.
Result: 201.6
201.60 of duty on the mugs: 2,520 of product value at the example rate of 8%. Total duty on the shipment (M6) is 538.20.
Is duty charged on the freight too?
It depends on the country. Some charge duty on the goods' value alone; others on the value plus freight and insurance to the border. If yours does, add the line's freight and insurance shares to the value before multiplying. Ask your customs broker which applies.
Result: 263.74154331
263.74 for the mugs if duty is charged on value plus freight and insurance, instead of 201.60.
Landed cost per unit, and the check cell
Add the line's product value and every share: =SUM(H2,J2:M2) in N2. Divide by the units for the landed cost per unit in O2. That's the number to use in your stock valuation and pricing, not the supplier's unit price. If the imported part goes into a product you make, enter its landed cost as the unit cost in a bill of materials, or in the price list of a BOM cost roll-up so every product that uses it picks up the new cost.
Then check that nothing leaked. The freight, insurance and fee shares must add back to exactly the shipment costs you typed. If someone sorts the lines, adds a row outside the totals, or forgets a $, this cell stops showing 0.
Result: 0
0: the shares add back to the 2,265 of freight, insurance and fees. With 538.20 of duty, the shipment costs 2,803.20 on top of 7,549 of goods, 37% more than the invoices.
What's each product's landed cost per unit?
The mug goes from 2.10 to 2.96, the carafe from 6.80 to 8.99, the napkin set from 4.25 to 5.38 and the trivet from 3.60 to 5.86.
Result: 2.9639896875
2.96: the mug's landed total of 3,556.79 over 1,200 units.
Landed margin vs the selling price
Margin on the supplier price flatters every product. The mug sells for 14.00: on its 2.10 invoice price the margin looks like 85%, but on its landed cost of 2.96 it's 78.8%. The heavy trivet drops furthest, to 67.4%.
Margin is (selling price - landed cost) / selling price. Compare it with markup, which divides by cost instead, in calculate markup in Excel.
Result: 0.67432982145 shows as 67.4% with Percent format
0.674 for the trivet: of the 18.00 selling price, 5.86 is landed cost.
Common mistakes
Sharing freight by value
Carriers charge by weight or volume. Share freight on that basis, or heavy low-value items look more profitable than they are.
Sharing duty across the shipment
Each product has its own rate. Calculate duty per line from its own value and rate.
Typing costs into the formulas
Keep freight, insurance, fees and duty rates in input cells so the next shipment is a matter of changing numbers.
No check cell
A missing $ or a row outside the totals silently loses part of a cost. Make the shares add back to the shipment costs.
FAQ
What is the formula for landed cost?
Landed cost = product cost + freight + insurance + duty + fees. Per unit, divide each line's landed total by its units, after sharing the shipment costs across the lines. Landed cost per unit is the unit cost to use when you value stock with the inventory formulas.
How do you allocate freight to multiple products in Excel?
Divide each line's weight (or volume) by the total and multiply by the freight bill: =$S$2*I2/$I$6. Fill down and check that the shares add back to the freight.
Should landed cost include duty?
Yes. Duty, freight, insurance and import fees are all part of what it costs to get the goods to you. Which costs go into your inventory value for the accounts is a policy to confirm with your accountant.
Can I use SUMPRODUCT for the total weight?
Yes. =SUMPRODUCT(B2:B5,D2:D5) gives the shipment weight without the helper column, and you can use it in place of $I$6. A purchase order template gives you the quantity and price lines to start from.
Functions used
Related guides
- How to calculate cost per unit in Excel (formula and examples)
- How to calculate cost of goods sold in Excel
- How to calculate markup in Excel
- How to convert currency in Excel
- How to calculate bill of materials (BOM) cost in Excel
- How to build an accrued expenses schedule in Excel
Updated