Cellwise Start free

Excel guide · 4 min read

How to calculate inventory carrying cost in Excel

Quick answerAnnual carrying cost = average inventory value x carrying cost rate. Build the rate from its parts in input cells (cost of capital, storage, insurance, shrinkage, obsolescence) and add them: =SUM(B2:B6). Then multiply each SKU's average units by its unit cost and the rate, or do the whole list at once with =SUMPRODUCT(E2:E8,F2:F8)*B7. Here 27,710 of average stock at a 21.5% rate costs 5,957.65 a year to hold.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

RowABCDEFGHIJKL
1Carrying cost componentAnnual rateSKUAvg unitsUnit costUnits sold / weekAvg valueWeeks of supplyAnnual carrying costMax weeks
2Cost of capital0.08Cordless drill120541464808.57142857141393.2
3Storage and handling0.06Drill bit set4009.530380013.333333333817
4Insurance0.015Work gloves9002.185189010.588235294406.35
5Shrinkage0.026 ft ladder60482288030619.2
6Obsolescence0.04Shop vacuum85723612028.3333333331315.8
7Total carrying rate0.215Tile saw222100.5462044993.3
8Extension cord3006.44019207.5412.8
9Total277105957.65

Download this sheet as an Excel practice file (.xlsx)

Build the carrying rate from its parts

Carrying cost (or holding cost) is what it costs you to keep stock on hand for a year, as a percentage of its value. A single rate copied from somewhere hides what's driving it, so list the parts in A2:B6 and add them in B7:

  • Cost of capital: the interest you pay on the money tied up in stock, or the return you'd expect from using it elsewhere.
  • Storage and handling: rent or a share of the warehouse, staff, equipment and utilities, divided by the average stock value.
  • Insurance on the stock.
  • Shrinkage: theft, damage and counting losses, from your own shrinkage history.
  • Obsolescence: stock that goes out of date, out of fashion or past its expiry and has to be written down or scrapped.

The total rate

The sample rates are 8%, 6%, 1.5%, 2% and 4%, which add to 21.5%. They're placeholders: every one of them depends on your financing, your building and your products, so fill them in from your own accounts. Rules of thumb vary widely between industries, and a rate you can explain line by line is worth more than a round number nobody can defend.

Turning a cost in money into a rate is a single division. If the warehouse costs 1,660 a year to run and the stock in it averages 27,710, storage is 1,660 / 27,710, about 6% of stock value.

=SUM(B2:B6)

Result: 0.215 shows as 21.5% with Percent format

0.215, shown as 21.5%: the total annual carrying rate from the five components.

Carrying cost per SKU

The SKU table in D:J has average units on hand in E, unit cost in F and units sold per week in G. H multiplies units by cost for the average value, and J multiplies that by the rate in B7, locked with $ so the formula fills down.

Use average inventory, not today's count, because carrying cost builds up over the whole year. For seasonal stock, average the month-end counts rather than just the start and end of the year; see average inventory in Excel.

=E2*F2*$B$7

Result: 1393.2 shows as 1,393.20 with Number format

1,393.20 a year to carry the cordless drills: 120 units at 54 each, 6,480 of stock, at 21.5%.

What does it cost to hold one unit for a week?

Unit cost x rate / 52. It's useful when you decide whether to buy a bigger batch for a discount: compare the discount per unit with the extra weeks you'll carry it.

=F7*B7/52

Result: 0.86826923077 shows as 0.87 with Number format

About 0.87 a week to hold one tile saw that cost 210.

Total carrying cost with SUMPRODUCT

You don't need the helper columns for a total. SUMPRODUCT multiplies each SKU's units by its cost and adds the results, which is the total average stock value. Multiply by the rate once at the end.

The answer is 5,957.65, the same as adding up column J. If you only need one number for a budget line, this single formula is all it takes, and it updates as soon as a unit count or cost changes.

=SUMPRODUCT(E2:E8,F2:F8)*B7

Result: 5957.65 shows as 5,957.65 with Number format

5,957.65: 27,710 of average stock across the seven SKUs at a 21.5% carrying rate.

The cost of slow movers

Carrying cost matters most on items you have far too much of. Column I shows weeks of supply, average units / units sold per week. Put your limit in M1, 26 weeks here, and add up the stock value of every SKU over it with SUMPRODUCT: the comparison (I2:I8>M1) gives 1 or 0 for each row.

Three items are over the limit: the ladders at 30 weeks, the shop vacuums at 28.3 and the tile saws at 44. Together they're 13,620 of stock, 49% of the value, costing 2,928.30 a year to hold. To highlight them in the table, add a conditional formatting rule with the formula =$I2>$M$1 on D2:J8.

=SUMPRODUCT((I2:I8>M1)*H2:H8)*B7

Result: 2928.3 shows as 2,928.30 with Number format

2,928.30 a year to carry the three SKUs with more than 26 weeks of supply.

How much of that is just the excess above the limit?

The slow movers would still need some stock. The excess is average units minus 26 weeks of sales. Multiply it by unit cost and the rate for the cost of the stock you didn't need.

=SUMPRODUCT((I2:I8>M1)*(E2:E8-G2:G8*M1)*F2:F8)*B7

Result: 597.27 shows as 597.27 with Number format

597.27 a year: the carrying cost of the 2,778 of stock above 26 weeks of supply on those three SKUs.

Common mistakes

Counting only the cost of capital

Interest is one part. Storage, insurance, shrinkage and obsolescence often add as much again.

Using today's stock count

Carrying cost accrues over the year. Use average inventory, and monthly averages for seasonal stock.

Burying the rate in the formula

Put each component in its own input cell and reference the total, so the rate is visible and easy to update.

Valuing stock at selling price

Carrying cost is on the money you've tied up, so use cost, not retail value.

FAQ

What is the formula for inventory carrying cost?

Average inventory value x carrying cost rate. The rate is the sum of cost of capital, storage and handling, insurance, shrinkage and obsolescence, each as a percentage of inventory value.

What is a typical inventory carrying cost percentage?

It varies a lot between industries and businesses, depending on interest rates, storage costs and how quickly products go out of date. Build yours from its components rather than borrowing someone else's figure.

How do I calculate carrying cost per unit?

Unit cost x annual carrying rate gives the cost to hold one unit for a year. Divide by 52 for a week or by 365 for a day.

Is carrying cost the same as holding cost?

Yes, the two terms are used for the same thing. The economic order quantity formula uses it as the annual holding cost per unit.

Functions used

Related guides

Updated