Landed Cost Calculator (Excel and Google Sheets)
By Newtown Analytics · · 5 min read
For ops, supply chain & planning leaders
A landed cost calculator takes the supplier's price and every cost between the factory and your shelf and turns them into one cost per unit that you can trust for pricing and margin. This page shows the structure of a landed cost calculator that you can build yourself in Excel or Google Sheets in about an hour. We show the layout, the formulas and a worked sample inline, and we do not offer a downloadable file here. The calculator sits within unit economics and budgeting and forecasting. Read landed cost first for the meaning of each component, and continue to how shipping costs erode margin and the cost of returns for the costs that come after the sale.
How the sheet is laid out
Use three blocks on one sheet: inputs at the top, a calculation table in the middle and a summary at the bottom. Keep inputs in a distinct color, such as blue text, so that anyone using the sheet knows which cells to change.
Block 1: shipment inputs
| Input | Example value | Cell note |
|---|---|---|
| Shipment reference | PO-1042 | Text |
| Total container freight | $2,400 | From the carrier invoice |
| Insurance rate (percent of product value) | 0.5% | From your policy |
| Customs brokerage and fees | $350 | Per entry |
| Port and drayage | $600 | Per container |
| Inbound handling per unit | $0.15 | From your warehouse |
| Cost of capital (annual) | 10% | From finance |
| Weeks in transit and stock | 14 | Estimate |
Block 2: product lines
One row per product on the shipment.
| Column | Meaning |
|---|---|
| A: Product | Name or SKU |
| B: Units | Quantity on the shipment |
| C: FOB unit price | Supplier price per unit |
| D: Unit weight (kg) | From the spec sheet |
| E: Duty rate | From your broker |
| F: Product value | = B × C |
| G: Total weight | = B × D |
Block 3: calculated columns
| Column | Formula idea |
|---|---|
| H: Freight allocated | = Total freight × G / SUM(G) |
| I: Insurance | = Insurance rate × F |
| J: Duty | = E × F |
| K: Brokerage and port allocated | = (Brokerage + Port) × F / SUM(F) |
| L: Handling | = Handling per unit × B |
| M: Carrying cost | = (F + H + I + J + K + L) × Cost of capital × Weeks / 52 |
| N: Total landed cost | = F + H + I + J + K + L + M |
| O: Landed cost per unit | = N / B |
| P: Uplift over FOB | = O / C - 1 |
Freight is allocated by weight, because weight drives it. Duty uses value. Brokerage and port fees use value in this sketch, but you could switch them to a per-line or per-unit driver if that fits your contracts better. In a spreadsheet, each formula uses cell references, for example =$C$5*G12/SUM($G$12:$G$13) for freight, so that the sheet recalculates when inputs change.
A worked sample
Use the earlier container: product A has 1,000 units, a $5.00 FOB price, 0.5 kg per unit and a 6.5 percent duty rate. Product B has 500 units, a $12.00 FOB price, 2.0 kg per unit and a 4 percent duty rate. Freight is $2,400, brokerage plus port is $950 ($350 + $600), handling is $0.15 per unit, insurance is 0.5 percent and carrying cost is 10 percent a year over 14 weeks. All of these figures are hypothetical.
| Line | Product A | Product B |
|---|---|---|
| Product value | $5,000.00 | $6,000.00 |
| Total weight | 500 kg | 1,000 kg |
| Freight (by weight) | $800.00 | $1,600.00 |
| Insurance (0.5% of value) | $25.00 | $30.00 |
| Duty | $325.00 | $240.00 |
| Brokerage and port (by value) | $431.82 | $518.18 |
| Handling | $150.00 | $75.00 |
| Subtotal before carrying cost | $6,731.82 | $8,463.18 |
| Carrying cost (subtotal × 10% × 14 / 52) | $181.25 | $227.86 |
| Total landed cost | $6,913.07 | $8,691.04 |
| Landed cost per unit | $6.91 | $17.38 |
| Uplift over FOB price | 38.3% | 44.9% |
Check the key lines: A's subtotal is 5,000 + 800 + 25 + 325 + 431.82 + 150 = 6,731.82; B's is 6,000 + 1,600 + 30 + 240 + 518.18 + 75 = 8,463.18. Brokerage and port by value: 950 × 5,000 / 11,000 = 431.82 and 950 × 6,000 / 11,000 = 518.18. Carrying cost uses 10% × 14 / 52 = 2.6923%: 6,731.82 × 0.026923 = 181.25, and 8,463.18 × 0.026923 = 227.86. Totals: 6,731.82 + 181.25 = 6,913.07, and 8,463.18 + 227.86 = 8,691.04. Per unit: 6,913.07 / 1,000 = 6.91, and 8,691.04 / 500 = 17.38. Uplift: 6.91 / 5.00 - 1 = 38.3%, and 17.38 / 12.00 - 1 = 44.9%.
The lesson is plain. Product A, which looked like a $5.00 item, is a $6.91 item, and product B, which looked like $12.00, is $17.38. Pricing from the FOB figures would have understated cost by almost 40 percent in both cases.
How to use the sheet
- Create one tab per shipment or one tab per season, and copy the layout.
- Fill the blue input cells from the actual invoices once they arrive, and from quotes before then, marking which is which.
- Review the per-unit figures and compare them with the standard cost in your system.
- Send the per-unit landed cost to whoever sets prices and margins.
- Keep the history so that you can see trends and update standards.
Checks to build in
- A line that checks that allocated freight sums to total freight, and that allocated brokerage and port sum to the input.
- A flag if the uplift over FOB is outside a normal range, such as below 10 percent or above 60 percent, to catch missing items.
- A check on units and currency, for example that all prices are in the same currency.
- A date stamp on duty rates, so that stale rates are visible.
Extensions
You can extend the sheet to handle multiple currencies by adding an exchange rate input, to compare suppliers by building the same calculation for each quote, to model scenarios such as a freight rate rise or a duty change, and to feed the result into a price-and-margin sheet. Teams that import many SKUs often move from a single sheet to a small database or a Power BI model, but the logic stays the same.
Want a working version?
If you would rather have a landed cost model built for your products, suppliers and trade lanes, with your actual cost drivers and links to your ERP and purchasing data, contact us. We build analytics and planning tools of this kind as part of our Analytics and Planning Automation work.
Keep reading
This article is for general information. It is not accounting, tax, legal or investment advice, and examples use illustrative numbers rather than client data. Confirm decisions that carry financial or legal consequences with a qualified professional.