A landed cost is the true total cost of an imported product once you add freight, insurance, customs duty, taxes, and handling fees to the supplier invoice price. Building a Google Sheets calculator lets you allocate those charges across every unit in a shipment and price imported goods accurately. This guide walks you through the spreadsheet formulas and then shows you how to publish it as a web calculator using GSheetPress so colleagues or customers can use it without touching your data.
Landed cost = supplier price + freight + insurance + customs duty + local taxes + handling • Allocate shared charges by unit weight, volume, or declared value • Distinguish recoverable taxes (claimable as input credit) from non-recoverable taxes (absorbed into cost) • Build the full model in Google Sheets then publish it as a web calculator with GSheetPress • Test all scenarios with realistic inputs before sharing publicly
How to Calculate Import Landed Costs in Google Sheets

Photo by Tim Mossholder on Unsplash

Key terms explained

  • Landed cost: The complete cost of a product at your warehouse door, including supplier price, international freight, insurance, customs duties, and all fees paid to clear and deliver the goods.
  • FOB (Free On Board): A trade term meaning the supplier's price covers goods up to the port of origin; you pay all costs from that point onward.
  • CIF (Cost, Insurance, Freight): A trade term where the supplier quotes a price that already includes freight and insurance to the destination port.
  • Customs duty: A percentage tax imposed by the importing country's government on the declared value of imported goods, also called an import tariff.
  • Ad valorem duty: Duty calculated as a percentage of the goods' declared monetary value, as opposed to a fixed charge per unit or per kilogram.
  • Specific duty: A fixed charge applied per unit of quantity (e.g., per kilogram or per litre) regardless of the goods' value.
  • VAT / GST on import: Value-added tax or goods-and-services tax charged at the border when goods enter a country, in addition to any customs duty.
  • Recoverable tax: An import tax (such as VAT or GST) that a registered business can reclaim as an input tax credit from its own tax authority, so it does not permanently increase the product's cost.
  • Non-recoverable tax: An import charge (such as excise duty or a non-VAT import levy) that a business cannot reclaim, so it must be absorbed into the landed cost and product price.
  • Allocation method: The rule you use to spread a shared charge (like freight) across multiple product lines or units in the same shipment — common methods are by value, by weight, and by volume.
  • CBM (Cubic Metre): A standard volumetric measure used by freight carriers to calculate shipping charges for bulky cargo.
  • Duty-paid value (DPV): The customs value of goods plus the applicable duty, used as the tax base when calculating import VAT or GST in some jurisdictions.
  • Exchange rate: The rate at which you convert a foreign-currency invoice into your local currency for accounting and cost purposes.

What this guide covers and what it excludes

This guide covers a single-shipment, single-currency-conversion model for physical goods. It assumes ad valorem customs duties expressed as a percentage of FOB or CIF value, a flat freight cost for the whole shipment, and a single import VAT or GST rate. The following are not covered and require specialist advice or additional research:

  • Specific (per-unit-weight) duties, anti-dumping duties, or compound tariff schedules
  • Free-trade agreement preference claims that reduce the duty rate to zero or a reduced rate
  • Excise duties on specific goods such as alcohol, tobacco, or fuel
  • Deferred duty or customs warehousing arrangements
  • Multi-currency shipments with more than one foreign invoice
  • Broker fees that vary by shipment complexity

Rates assumption: Duty rates and import VAT or GST rates vary by country, commodity code (HS code), and year. Always verify the applicable rate with your customs broker or the official tariff database of the importing country before using any figure in a live costing. For the United States, the Harmonized Tariff Schedule published by the USITC is the authoritative source for duty rates.

Prerequisites

  • A Google account with Google Sheets access.
  • A GSheetPress account with the Calculator entitlement active. If you have not yet signed up, visit GSheetPress sign-up and choose the plan that includes Calculator publishing. Check GSheetPress pricing to confirm Calculator is included in your selected tier.
  • The GS2Web SaaS add-on installed from the Google Workspace Marketplace, or the equivalent installed version authorised to your Google account.
  • A dedicated sample workbook created solely for this exercise. Do not use a file containing real supplier invoices, trading terms, or financial data.

Understand the landed cost formula

The core calculation is straightforward. For a single product line shipped at FOB terms:

Landed cost per unit =
  (FOB unit price × exchange rate)
  + allocated freight per unit
  + allocated insurance per unit
  + (customs duty rate × CIF value per unit)
  + (import VAT rate × duty-paid value per unit)
  + allocated handling and brokerage per unit

When goods are quoted CIF, the freight and insurance are already embedded in the supplier's price. In that case you should not add freight and insurance again, but you still need to know them separately to compute the correct customs value. The model below uses FOB terms with freight and insurance entered separately, which is the more flexible starting point.

Allocation by value (recommended for most shipments)

If a container holds multiple product lines, you must split the shared freight, insurance, and handling costs fairly. The simplest defensible method is value-based allocation:

Each line's freight share = Total freight × (Line FOB value ÷ Total FOB value of all lines)

For a single-product shipment this simplifies to 100 % of the costs being applied to that one product, which is what the calculator below assumes. If you need multi-product allocation, the same logic can be extended by adding a table of product lines.

Recoverable versus non-recoverable taxes

If your business is registered for VAT or GST and can claim import VAT back as an input credit, that import VAT is recoverable. It temporarily increases your payment at the border but is offset when you file your periodic return. In that case, import VAT should not be included in the landed cost you use for inventory valuation or pricing decisions.

If you are not VAT-registered, or if the charge is an excise duty or similar levy that cannot be reclaimed, it is non-recoverable and must be added to the landed cost. The worked model below includes import VAT and flags it so users can exclude it if they are registered importers who will reclaim it.

Build the spreadsheet model

Open your dedicated sample workbook and add a tab called LandedCostModel. This tab will hold helper data and formula results. The GSheetPress calculator tab (created later) will reference the runtime input values in its own G column.

Step 1: Plan your inputs

The calculator will accept seven user-supplied inputs:

  1. FOB unit price (in foreign currency)
  2. Quantity of units
  3. Exchange rate (foreign currency to local currency)
  4. Total freight cost (in local currency)
  5. Total insurance cost (in local currency)
  6. Customs duty rate (%)
  7. Import VAT / GST rate (%)

A flat handling and brokerage fee is included as an eighth figure but is treated here as a fixed default that the user can adjust, keeping the interactive inputs at seven for a clean form. You can expand this later.

Step 2: Add a named reference sheet (optional but recommended)

On a separate tab called Rates, note the duty rate and VAT or GST rate your research has identified so you have an audit trail. These are reference notes only; the live values are entered through the calculator form.

Build the GSheetPress web calculator

The steps below create the interactive calculator tab that GSheetPress will publish to the web. The add-on generates the column headers automatically; you fill in the data rows and add result formulas.

Create the calculator

  1. Open your dedicated sample workbook.
  2. Click Extensions in the Google Sheets menu bar.
  3. Hover over GS2Web SaaS (or the name of your installed add-on) and choose Calculator, then New Calculator.
  4. When prompted, type the calculator name: Import Landed Cost.
  5. When asked for the number of inputs, enter 7. This count does not include the Calculate button.
  6. When asked whether to include graphs, choose No for this model.
  7. Click Create (or the equivalent confirmation button). The add-on inserts a new sheet tab named after your calculator and auto-generates the header row in A1:H1 with the labels: Labels, Input Type, Required, Data, Default, ToolTips (some template versions spell this ToopTips — use whatever appears), Inputs, Rules.

Because you specified 7 inputs, the generated layout is as follows:

  • Rows 2–8: the seven input rows
  • Row 9: the Calculate button row (auto-generated)
  • Row 10: the merged Result Fields separator row (auto-generated)
  • Row 11: result column headings
  • Row 12 onward: result data rows

Column G holds the values a web user submits at runtime. Column E holds form defaults, which also serve as your test values before publishing. Never place result formulas in column G; they will be overwritten when a user submits the form.

Input row 2 — FOB unit price

  • A2 — Label: FOB Unit Price (foreign currency)
  • B2 — Input Type: Number
  • C2 — Required: Tick the checkbox (mark as required)
  • D2 — Data: Leave blank
  • E2 — Default: 50
  • F2 — ToolTips: Enter the supplier's FOB price per unit in the foreign currency (e.g. USD)
  • G2 — Inputs: Leave blank (the add-on manages this column at runtime)
  • H2 — Rules: Leave blank

Input row 3 — Quantity

  • A3 — Label: Quantity (units)
  • B3 — Input Type: Number
  • C3 — Required: Tick the checkbox
  • D3 — Data: Leave blank
  • E3 — Default: 500
  • F3 — ToolTips: Total number of units in this shipment
  • G3 — Inputs: Leave blank
  • H3 — Rules: Leave blank

Input row 4 — Exchange rate

  • A4 — Label: Exchange Rate (foreign to local)
  • B4 — Input Type: Number
  • C4 — Required: Tick the checkbox
  • D4 — Data: Leave blank
  • E4 — Default: 1.35
  • F4 — ToolTips: How many local currency units equal 1 unit of the foreign currency. For example, if USD 1 = AUD 1.52 enter 1.52
  • G4 — Inputs: Leave blank
  • H4 — Rules: Leave blank

Input row 5 — Total freight

  • A5 — Label: Total Freight Cost (local currency)
  • B5 — Input Type: Number
  • C5 — Required: Tick the checkbox
  • D5 — Data: Leave blank
  • E5 — Default: 2500
  • F5 — ToolTips: Total international freight cost for the whole shipment in local currency
  • G5 — Inputs: Leave blank
  • H5 — Rules: Leave blank

Input row 6 — Total insurance

  • A6 — Label: Total Insurance Cost (local currency)
  • B6 — Input Type: Number
  • C6 — Required: Tick the checkbox
  • D6 — Data: Leave blank
  • E6 — Default: 300
  • F6 — ToolTips: Marine or cargo insurance cost for the whole shipment in local currency
  • G6 — Inputs: Leave blank
  • H6 — Rules: Leave blank

Input row 7 — Customs duty rate

  • A7 — Label: Customs Duty Rate (%)
  • B7 — Input Type: Number
  • C7 — Required: Tick the checkbox
  • D7 — Data: Leave blank
  • E7 — Default: 5
  • F7 — ToolTips: Enter the duty rate as a whole number. For example enter 5 for 5%. Verify this rate against the official tariff schedule for your commodity code.
  • G7 — Inputs: Leave blank
  • H7 — Rules: Leave blank

Input row 8 — Import VAT / GST rate

  • A8 — Label: Import VAT / GST Rate (%)
  • B8 — Input Type: Number
  • C8 — Required: Tick the checkbox
  • D8 — Data: Leave blank
  • E8 — Default: 10
  • F8 — ToolTips: Enter the import VAT or GST rate as a whole number. Enter 0 if none applies or if you are a registered importer who will reclaim this tax.
  • G8 — Inputs: Leave blank
  • H8 — Rules: Leave blank

Button row (auto-generated — row 9)

Row 9 is generated automatically by the add-on. Confirm that column B reads Button and column D reads main. Column A should contain your chosen button label such as Calculate. Column E may read Reset — this is the reset button label and is separate from the Calculate label in A. Do not alter the Button row structure.

Result separator and headings (rows 10 and 11)

Row 10 is a merged result-fields separator generated by the add-on. Row 11 is for result column headings. Click cell A11 and type Cost Item. Click B11 and type Amount (local currency). These headings label the result table the user will see after submitting the form.

Result formulas (row 12 onward)

All formulas reference column G (runtime inputs) for the seven inputs. Before publishing, copy the defaults from column E (rows 2–8) into column G (rows 2–8) using Paste special → Values only so you can verify results without a live web submission. Remember that live web submissions overwrite G; never place result formulas in G.

Click each cell listed below, type the formula shown (starting with the equals sign), and press Enter.

A12 — Label for the first result row:

="FOB Value (local currency)"

B12 — FOB total value converted to local currency:

=IF(G3=0,"Enter quantity",ROUND(G2*G3*G4,2))

This multiplies the FOB unit price (G2) by quantity (G3) and the exchange rate (G4) to give the total FOB value in local currency. If quantity is zero it returns a plain-language message instead of dividing or multiplying by zero silently.

A13

="Freight per unit (local currency)"

B13

=IF(G3=0,"Enter quantity",ROUND(G5/G3,4))

Divides total freight (G5) by quantity (G3) to get the per-unit freight allocation.

A14

="Insurance per unit (local currency)"

B14

=IF(G3=0,"Enter quantity",ROUND(G6/G3,4))

A15

="CIF value per unit (local currency)"

B15

=IF(G3=0,"Enter quantity",ROUND(G2*G4+G5/G3+G6/G3,4))

The CIF value per unit is the converted FOB unit price plus per-unit freight and per-unit insurance. Customs duty is typically applied to this CIF figure, though some jurisdictions use FOB value. Verify which basis your customs authority uses.

A16

="Customs duty per unit (local currency)"

B16

=IF(G3=0,"Enter quantity",ROUND(B15*(G7/100),4))

Applies the duty rate (G7, entered as a whole number, divided by 100 to convert to a decimal) to the CIF value per unit.

A17

="Duty-paid value per unit (local currency)"

B17

=IF(G3=0,"Enter quantity",ROUND(B15+B16,4))

CIF value plus duty equals the duty-paid value, which is the tax base for import VAT or GST in many jurisdictions.

A18

="Import VAT / GST per unit (local currency)"

B18

=IF(G3=0,"Enter quantity",ROUND(B17*(G8/100),4))

A19

="Landed cost per unit — incl. VAT/GST (local currency)"

B19

=IF(G3=0,"Enter quantity",ROUND(B15+B16+B18,4))

This is the full per-unit landed cost including import VAT or GST. Use this figure only if the tax is non-recoverable.

A20

="Landed cost per unit — excl. VAT/GST (local currency)"

B20

=IF(G3=0,"Enter quantity",ROUND(B15+B16,4))

Use this figure if you are a VAT or GST-registered business that will reclaim the import tax as an input credit.

A21

="Total landed cost — entire shipment incl. VAT/GST"

B21

=IF(G3=0,"Enter quantity",ROUND(B19*G3,2))

A22

="Total landed cost — entire shipment excl. VAT/GST"

B22

=IF(G3=0,"Enter quantity",ROUND(B20*G3,2))

Worked example and result checks

Use the following inputs to verify your formulas before publishing. Copy the values in the Default column into column G rows 2–8 using Paste special → Values only.

InputDefault valueMeaning
FOB Unit Price50USD 50 per unit
Quantity500500 units in the shipment
Exchange Rate1.351 USD = 1.35 local currency
Total Freight2500Local currency total
Total Insurance300Local currency total
Customs Duty Rate55% ad valorem on CIF value
Import VAT/GST Rate1010% on duty-paid value

Expected results with these inputs:

Result labelExpected value
FOB Value (local currency)33,750.00
Freight per unit5.0000
Insurance per unit0.6000
CIF value per unit73.1000
Customs duty per unit3.6550
Duty-paid value per unit76.7550
Import VAT/GST per unit7.6755
Landed cost per unit incl. VAT/GST84.4305
Landed cost per unit excl. VAT/GST76.7550
Total landed cost incl. VAT/GST42,215.25
Total landed cost excl. VAT/GST38,377.50

To manually verify the CIF value per unit: (50 × 1.35) + (2500 ÷ 500) + (300 ÷ 500) = 67.50 + 5.00 + 0.60 = 73.10. Duty = 73.10 × 0.05 = 3.655. Duty-paid value = 73.10 + 3.655 = 76.755. Import VAT = 76.755 × 0.10 = 7.6755. Landed cost including VAT = 76.755 + 7.6755 = 84.4305 per unit. Total including VAT = 84.4305 × 500 = 42,215.25.

Alternative scenario

Change the customs duty rate to 0 (duty-free under a trade agreement) and the import VAT rate to 15. With all other defaults unchanged: CIF value per unit = 73.10. Duty = 0. Duty-paid value = 73.10. Import VAT = 73.10 × 0.15 = 10.965. Landed cost including VAT = 73.10 + 0 + 10.965 = 84.065 per unit. This shows the model correctly handles zero duty and a different VAT rate without formula errors.

FAQs and troubleshooting

Frequently asked questions

See also the GSheetPress support pages for add-on specific queries.

Q: My supplier quotes CIF, not FOB. How do I adjust the model?
A: If your invoice is CIF, the freight and insurance costs are embedded in the quoted unit price. Enter the CIF unit price in the FOB Unit Price field and enter 0 in both the Total Freight and Total Insurance fields. The CIF value per unit will then equal the converted supplier price, which is the correct customs value. Note that you lose visibility of the freight and insurance breakdown unless you request those figures separately from your supplier.

Q: Should I include the customs broker's fee in the landed cost?
A: Yes, broker and customs agent fees are real costs and should be included. In the current model you can add them to the Total Freight or Total Insurance field as a practical workaround, or extend the model by adding an eighth input row for Handling and Brokerage. The allocation method and formula pattern are identical to the freight rows.

Q: Can I use this model for multiple product lines in one container?
A: This model covers a single product line. For multiple lines you need an additional column for each product's FOB value and quantity so the shared freight and insurance can be allocated proportionally. For a detailed multi-line setup, the GSheetPress Calculator documentation explains how to extend the result section with additional rows and conditional logic.

Troubleshooting

SymptomLikely causeFix
B12 shows "Enter quantity"G3 is blank or zeroPaste the default value 500 from E3 into G3 using Paste special → Values only
Duty result seems too highDuty rate entered as a decimal (e.g. 0.05) instead of a whole number (5)Re-enter the duty rate as a whole number such as 5 for 5%
CIF value per unit looks too lowExchange rate entered as local-to-foreign instead of foreign-to-localInvert the rate. If USD 1 = AUD 1.52, enter 1.52, not 0.658
Total FOB value does not match expectationsFreight or insurance inadvertently entered in foreign currencyConvert all freight, insurance, and fee amounts to local currency before entering them
Calculator tab not visible after creationAdd-on may not be fully authorisedGo to Extensions → GS2Web SaaS → Manage and confirm the add-on is authorised for this document
Results show #REF! errorsA result formula references a cell that was deleted or the tab was renamedRe-enter the formula referencing the correct G column cell in the calculator tab

For further help with the add-on configuration, the GSheetPress support centre provides up-to-date guidance. For advice on adapting the model to handle more complex tariff structures or multi-product allocation, see the worked table features documentation for display options.

Publish and test your web calculator

  1. Confirm your result formulas return the expected values from the worked example by pasting the defaults from column E into column G (rows 2–8) with Paste special → Values only and checking the results in rows 12–22.
  2. Share the spreadsheet so the add-on can publish it. Click Share in the top-right corner of Google Sheets. Under General access, change the setting to Anyone with the link and set the role to Editor, then click Done. Important: This setting means anyone who obtains the spreadsheet link can edit the file directly. Use only your dedicated sample workbook — never a file with real business data, pricing agreements, or financial records.
  3. Click on the calculator tab you created (the tab the add-on inserted, not the LandedCostModel tab).
  4. Go to Extensions → GS2Web SaaS → Calculator → Publish Calculator.
  5. Wait for the confirmation message indicating the calculator has been published successfully.
  6. In the add-on panel or the generated sheet, locate the Calculator Link sheet. Find the row where the Sheet Name column matches Import Landed Cost.
  7. Open the public link found in column C (Link), not column B (Edit Link). If the link does not appear or seems outdated, click Get Links to refresh the list.
  8. In the public calculator, enter the seven worked-example values: FOB Unit Price 50, Quantity 500, Exchange Rate 1.35, Total Freight 2500, Total Insurance 300, Customs Duty Rate 5, Import VAT/GST Rate 10.
  9. Click the Calculate button and confirm the displayed results match the expected values in the worked example table above. Pay particular attention to the landed cost per unit figures and the totals.
  10. Optionally copy the iframe code from column D (Iframe) and paste it into an HTML block on your website to embed the calculator. If you later update and republish the same calculator, it retains the same public link; you do not need to update the embed code.

If the public results differ from your spreadsheet results, return to the calculator tab, verify that the G column cells contain the runtime values submitted by the form rather than hard-coded test values, and confirm each formula references the correct G cell row number.