Excel guide · 4 min read
How to calculate inventory carrying cost in Excel
=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.
| Row | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Carrying cost component | Annual rate | SKU | Avg units | Unit cost | Units sold / week | Avg value | Weeks of supply | Annual carrying cost | Max weeks | ||
| 2 | Cost of capital | 0.08 | Cordless drill | 120 | 54 | 14 | 6480 | 8.5714285714 | 1393.2 | |||
| 3 | Storage and handling | 0.06 | Drill bit set | 400 | 9.5 | 30 | 3800 | 13.333333333 | 817 | |||
| 4 | Insurance | 0.015 | Work gloves | 900 | 2.1 | 85 | 1890 | 10.588235294 | 406.35 | |||
| 5 | Shrinkage | 0.02 | 6 ft ladder | 60 | 48 | 2 | 2880 | 30 | 619.2 | |||
| 6 | Obsolescence | 0.04 | Shop vacuum | 85 | 72 | 3 | 6120 | 28.333333333 | 1315.8 | |||
| 7 | Total carrying rate | 0.215 | Tile saw | 22 | 210 | 0.5 | 4620 | 44 | 993.3 | |||
| 8 | Extension cord | 300 | 6.4 | 40 | 1920 | 7.5 | 412.8 | |||||
| 9 | Total | 27710 | 5957.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.
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.
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.
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.
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.
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.
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
- Excel formulas for inventory: stock balance, reorder and value
- How to calculate purchase price variance (PPV) in Excel
- How to calculate average inventory in Excel
- How to calculate weeks of supply in Excel
- How to calculate economic order quantity (EOQ) in Excel
- How to build an accrued expenses schedule in Excel
Updated