Walk into your warehouse right now and look around. Some of that stock moved out yesterday. Some of it has been sitting on the same shelf since last Diwali. And somewhere in between, there is inventory that you have honestly forgotten you even have.
Every rupee tied up in that forgotten stock is a rupee that is not in your bank account. It is not earning interest. It is not funding your next purchase order. It is sitting on a shelf, slowly losing value — through damage, obsolescence, expiry, or simply going out of fashion — while you continue to pay rent on the warehouse space it occupies.
A stock aging report is the single most important tool for identifying exactly which inventory this is — broken down by how long each item has been sitting unsold, so you can take action before slow-moving stock becomes dead stock, and dead stock becomes a write-off.
This guide gives you a complete, free Excel-based stock aging report template — with every column, formula, and aging bucket explained — so you can build (or download) a working stock aging tracker for your business today. We also cover how to read the report, what actions to take based on the results, and how ERP Group's warehouse management system automates this entire process — so you never have to manually update an Excel sheet again.
What is a Stock Aging Report?
The Simple Definition
A stock aging report — also called an inventory aging report — is a structured analysis of your inventory that groups every item by how long it has been sitting in your warehouse without movement. Instead of just showing what stock you have, it shows how old that stock is — measured from the date it was received or the date it was last sold.
What Makes Stock "Aged"
Every item in your inventory has a clock running on it from the moment it arrives. As long as it keeps moving — being sold, transferred, or consumed — that clock effectively resets. But the moment an item stops moving, the clock keeps running. A stock aging report makes this invisible clock visible — for every single SKU in your warehouse.
Stock Aging vs Regular Stock Reports
A Regular Stock Report Shows
Current quantity on hand. Current stock value. Reorder levels. Location in warehouse.
A Stock Aging Report Adds
How long each item has been in stock. Whether the item is moving fast, slow, or not at all. Which items are approaching the "dead stock" category. The financial value tied up in aged inventory — broken down by age bracket.
A regular stock report tells you what you have. A stock aging report tells you what is going stale — and how much money is at risk.
Why Every Indian Business Needs a Stock Aging Report
The Working Capital Problem
For most Indian SMBs — traders, wholesalers, retailers, distributors — inventory is the single largest tied-up asset on the balance sheet. Every rupee spent on stock that does not move is a rupee that cannot be used to pay suppliers, fund new purchases, or cover operating expenses.
A Realistic Example
A trading business with ₹50 lakh in total inventory value. A stock aging report reveals that ₹8 lakh of this — 16% — has not moved in over 180 days. That ₹8 lakh is effectively dead capital. If even half of it could be liquidated through clearance sales, that is ₹4 lakh in cash flow that could be redeployed immediately.
The GST and Tax Implications
Inventory Valuation for GST and Income Tax
Stock valuation directly affects your reported profit — and therefore your tax liability. Aged stock that has lost market value but is still carried at original cost overstates your inventory asset value and can distort your profit and loss statement. A stock aging report is the starting point for identifying inventory that may need to be written down or written off — with appropriate accounting and GST treatment.
ITC Considerations for Written-Off Stock
Under GST law, if inventory is written off as obsolete or destroyed, the Input Tax Credit originally claimed on that inventory may need to be reversed. A stock aging report helps you proactively identify candidates for write-off — before they become a larger compliance issue at year-end audit.
The Operational Risk of Not Tracking Stock Age
Warehouse Space and Cost
Every square foot of warehouse space occupied by aged stock is space that cannot be used for fast-moving inventory. For businesses paying rent per square foot, aged stock has a real, ongoing cost beyond its book value.
Risk of Damage, Expiry, and Obsolescence
The longer stock sits, the higher the risk of damage from handling, pest infestation, expiry (for perishables, pharmaceuticals, and FMCG), and obsolescence (for fashion, electronics, and seasonal goods). A stock aging report flags these risks before they become losses.
Distorted Purchasing Decisions
Without visibility into aging stock, businesses often reorder items that are already overstocked — because the stock is sitting in a corner of the warehouse and nobody realizes how much of it remains. A stock aging report prevents this duplicate-buying mistake.
The Free Stock Aging Report Excel Template — Complete Structure
Template Overview
The stock aging report template below is designed for Indian trading, wholesale, and retail businesses — with columns that work for any product-based business, whether you sell electronics, garments, FMCG, pharmaceuticals, or industrial supplies.
Sheet 1 — Item Master Data
Column A — Item Code / SKU
The unique identifier for each product. If you use barcodes, this should match your barcode system.
Column B — Item Name / Description
A clear, recognizable description of the product — including size, color, or variant where applicable.
Column C — Category / Product Group
Group items into categories — Electronics, Garments, Hardware, FMCG, etc. This allows category-level aging analysis in addition to item-level.
Column D — HSN Code
For GST-registered businesses, include the HSN code for each item — useful for cross-referencing with your accounting software and GST returns.
Column E — Unit of Measurement (UQC)
Pieces, Kg, Litres, Boxes, etc.
Sheet 1 Continued — Stock Movement Data
Column F — Opening Stock Quantity (Start of Period)
The quantity on hand at the start of your analysis period — typically the start of the financial year or the start of the quarter.
Column G — Quantity Received (Purchases/Transfers In)
Total quantity received during the period — from purchases, production, or stock transfers.
Column H — Quantity Sold/Issued (Sales/Transfers Out)
Total quantity that has moved out — through sales, transfers, or consumption.
Column I — Current Stock Quantity (Closing Balance)
Current Stock = Opening Stock + Quantity Received − Quantity Sold/Issued
This should match your physical stock count — any mismatch indicates a stock discrepancy requiring investigation.
Column J — Unit Cost (₹)
The cost price per unit — used to value the inventory. For items with multiple purchase prices, use weighted average cost or FIFO/LIFO based on your accounting method.
Column K — Current Stock Value (₹)
Stock Value = Current Stock Quantity × Unit Cost
Sheet 1 Continued — The Aging Columns
Column L — Last Movement Date
The most recent date this item was either received (if it has never sold) or sold/issued. This is the critical date that determines the item's "age."
Column M — Days Since Last Movement
Days Since Last Movement = TODAY() − Last Movement Date
This is calculated automatically and updates every time you open the sheet.
Column N — Aging Bucket
A category label based on Days Since Last Movement — explained in detail in the next section. This is the heart of the stock aging report.
Column O — Aging Bucket Value (₹)
The stock value (Column K) attributed to its aging bucket — used for summary totals.
Column P — % of Total Inventory Value
% of Total = Stock Value (Column K) ÷ Total Inventory Value × 100
Shows how significant each item is relative to your overall inventory.
Column Q — Recommended Action
A manually entered or formula-driven suggestion based on the aging bucket — "Monitor," "Promote/Discount," "Clearance Sale," "Write-Off Review."
Sheet 2 — Aging Summary Dashboard
This sheet aggregates Sheet 1 data into a summary view — typically using SUMIF or SUMIFS formulas referencing the Aging Bucket column.
Summary Table Structure
| Aging Bucket | Item Count | Total Quantity | Total Stock Value (₹) | % of Total Inventory |
|---|---|---|---|---|
| 0-30 Days | =COUNTIF | =SUMIF | =SUMIF | =formula |
| 31-60 Days | =COUNTIF | =SUMIF | =SUMIF | =formula |
| 61-90 Days | =COUNTIF | =SUMIF | =SUMIF | =formula |
| 91-180 Days | =COUNTIF | =SUMIF | =SUMIF | =formula |
| 181-365 Days | =COUNTIF | =SUMIF | =SUMIF | =formula |
| Over 365 Days | =COUNTIF | =SUMIF | =SUMIF | =formula |
| TOTAL | =SUM | =SUM | =SUM | 100% |
Visual Dashboard Elements
A pie chart showing inventory value distribution across aging buckets. A bar chart showing item count by category and aging bucket. Conditional formatting (color-coded heat map) on Sheet 1 — green for 0-30 days, yellow for 31-90 days, orange for 91-180 days, red for over 180 days.
Stock Aging Buckets — How to Categorize Your Inventory
The Standard Aging Bucket Framework for Indian Businesses
Bucket 1 — 0-30 Days (Fresh Stock)
Items that arrived or last moved within the past month. This is your healthy, actively turning inventory. No action required — this is normal operating stock.
Bucket 2 — 31-60 Days (Normal Aging)
Items that have not moved in 1-2 months. Still within normal range for most product categories — but worth monitoring, particularly for fast-moving categories where 60 days without movement is unusual.
Bucket 3 — 61-90 Days (Watch List)
Items approaching the 3-month mark without movement. This bucket should be reviewed weekly — items here are candidates for promotional pricing, bundling, or repositioning before they become slow-moving.
Bucket 4 — 91-180 Days (Slow-Moving Stock)
Items that have not moved in 3-6 months. This is officially "slow-moving stock" — and requires active intervention. Consider discounting, bundling with fast-moving items, or returning to supplier if your purchase terms allow.
Bucket 5 — 181-365 Days (High-Risk Stock)
Items that have not moved in 6-12 months. At this stage, the likelihood of selling at full price is low. Aggressive clearance pricing, liquidation through secondary channels, or formal write-down consideration should begin.
Bucket 6 — Over 365 Days (Dead Stock)
Items that have not moved in over a year. This is dead stock — inventory that is, for practical purposes, no longer part of your active business. These items should be evaluated for write-off, donation (with appropriate GST treatment), liquidation at any recoverable price, or disposal.
Adjusting Buckets for Your Industry
Fast-Moving Categories (FMCG, Perishables)
For FMCG and perishable goods businesses, the aging buckets should be compressed — 0-7 days, 8-15 days, 16-30 days, 31-60 days, 61-90 days, over 90 days. An FMCG item sitting for 90 days is far more concerning than a hardware item sitting for 90 days.
Slow-Moving Categories (Industrial Equipment, Spare Parts)
For industrial supplies, machinery spares, and capital goods dealers, the standard buckets can be extended — 0-90 days, 91-180 days, 181-365 days, 1-2 years, over 2 years. A spare part for specialized machinery may legitimately sit for a year before the right customer needs it.
Fashion and Seasonal Categories
For garments, footwear, and seasonal goods, aging should be tracked against the selling season — not just calendar days. An item that has been in stock for 90 days but is still within its selling season is different from an item that has been in stock for 90 days and has now missed the season entirely.
The Formulas Behind the Template — Complete Explanation
Formula 1 — Days Since Last Movement
=TODAY()-L2
Where L2 is the Last Movement Date. This formula automatically recalculates every time the spreadsheet is opened — always showing the current age of the stock as of today's date.
Formula 2 — Aging Bucket Classification
=IF(M2<=30,"0-30 Days",
IF(M2<=60,"31-60 Days",
IF(M2<=90,"61-90 Days",
IF(M2<=180,"91-180 Days",
IF(M2<=365,"181-365 Days","Over 365 Days")))))
This nested IF formula reads the "Days Since Last Movement" value (Column M) and assigns the correct aging bucket label automatically.
Formula 3 — Current Stock Value
=I2*J2
Where I2 is Current Stock Quantity and J2 is Unit Cost.
Formula 4 — Percentage of Total Inventory Value
=K2/SUM($K$2:$K$1000)*100
Where K2 is the individual item's stock value, and the SUM range covers all items in the inventory list.
Formula 5 — Aging Bucket Summary (Total Value)
=SUMIF($N$2:$N$1000,"0-30 Days",$K$2:$K$1000)
This SUMIF formula on the Summary sheet adds up the stock value (Column K) for all items where the Aging Bucket (Column N) matches "0-30 Days." Repeat with the appropriate bucket label for each row of the summary table.
Formula 6 — Aging Bucket Summary (Item Count)
=COUNTIF($N$2:$N$1000,"0-30 Days")
Counts how many distinct items fall into each aging bucket.
Formula 7 — Conditional Formatting Rule for Visual Heat Map
While not a formula entered into a cell, conditional formatting rules applied to Column N (Aging Bucket) or Column M (Days Since Last Movement) create the visual heat map:
- Green fill: "0-30 Days" or "31-60 Days"
- Yellow fill: "61-90 Days"
- Orange fill: "91-180 Days"
- Red fill: "181-365 Days" or "Over 365 Days"
This allows anyone scanning the sheet to instantly identify problem items by color — without reading every cell.
How to Fill and Use the Stock Aging Report Template — Step by Step
Step 1 — Populate Item Master Data
Start by listing every active SKU in your inventory — Item Code, Item Name, Category, HSN Code, and Unit of Measurement. If you already maintain a product master in your accounting or billing software, export this data and paste it into the template.
Step 2 — Enter Opening Stock and Movement Data
For each item, enter the Opening Stock Quantity (from your last physical count or system balance at period start), Quantity Received during the period, and Quantity Sold/Issued during the period. These figures typically come from your purchase register and sales register.
Step 3 — Verify Current Stock Quantity
The formula-calculated Current Stock Quantity (Opening + Received − Sold) should match your physical stock count. If it does not match — investigate the discrepancy before proceeding. Common causes include unrecorded stock adjustments, damages, or theft.
Step 4 — Enter Unit Cost and Calculate Stock Value
Enter the unit cost for each item — using your standard valuation method (weighted average, FIFO, etc.). The Stock Value column calculates automatically.
Step 5 — Enter Last Movement Date for Each Item
This is the most critical and most labor-intensive step. For every item, determine the date of the most recent sale, transfer, or issue. If an item has never sold since it was received, use the receipt date.
Sourcing Last Movement Dates
From your sales register — filter by item and find the most recent transaction date. From your billing software — if your accounting software tracks item-level sales history, export the last sale date for each SKU.
Step 6 — Let the Formulas Calculate Aging
Once Last Movement Date is entered, Days Since Last Movement and Aging Bucket calculate automatically for every item.
Step 7 — Review the Summary Dashboard
Check the Aging Summary sheet — review the total value and item count in each aging bucket. Identify which buckets contain the highest value — these are your priority areas for action.
Step 8 — Assign Recommended Actions
For items in the "Watch List" (61-90 days) and beyond, assign a recommended action — Monitor, Promote, Clearance, or Write-Off Review. This becomes your action list for the coming weeks.
Step 9 — Repeat Monthly
A stock aging report is only useful if updated regularly. Schedule a monthly update — ideally at month-end, alongside your regular stock reconciliation — to keep the aging data current and actionable.
How to Read and Interpret Your Stock Aging Report
What a Healthy Aging Profile Looks Like
For most trading and retail businesses, a healthy stock aging profile shows the majority of inventory value (60-80%) in the 0-60 day buckets, a moderate amount (10-20%) in the 61-90 day bucket, and a small residual (under 10%) in buckets beyond 90 days. If your aging profile looks roughly like this — your inventory is turning at a healthy pace.
Warning Signs in Your Aging Report
Warning Sign 1 — Growing "Over 365 Days" Bucket
If the value in your "Over 365 Days" bucket has been increasing month over month — your dead stock problem is growing, not just existing. This requires immediate intervention — clearance, write-off, or both.
Warning Sign 2 — High-Value Items in Slow-Moving Buckets
A few high-value items sitting in the 91-180 day or beyond bucket can represent a disproportionate share of your tied-up capital. Even if the item count is small, the rupee impact can be significant — these deserve individual attention.
Warning Sign 3 — Entire Categories Aging Together
If an entire product category — not just individual items — is consistently appearing in slow-moving buckets, this may indicate a broader issue — declining demand for that category, a sourcing decision that needs to be reconsidered, or a pricing problem affecting the whole range.
Turning Insights Into Action
For 61-90 Day Items
Bundle with fast-moving items. Feature in promotional displays. Offer a small discount to accelerate movement before they become "slow-moving."
For 91-180 Day Items
Apply meaningful discounts (15-30%). Consider returning to supplier if your purchase agreement allows returns within a certain period. Move to a more visible location in the store/warehouse.
For 181-365 Day Items
Aggressive clearance pricing (40-60% off). Bulk sale to liquidators or secondary market buyers. Consider whether continued storage cost justifies holding versus disposal.
For Over 365 Days Items
Formal write-off evaluation — with appropriate accounting entries and GST ITC reversal where applicable. Donation (with GST implications considered). Scrap/disposal if the item has no recoverable value.
Stock Aging Report and GST — What Indian Traders Must Know
Inventory Valuation and Your Financial Statements
Your closing stock value — as reported in your financial statements — directly affects your reported profit and tax liability. A stock aging report helps you identify inventory that should be valued at "cost or net realizable value, whichever is lower" — a standard accounting principle that requires you to write down inventory whose market value has fallen below its cost.
ITC Reversal for Written-Off Stock
The Rule
Under Section 17(5)(h) of the CGST Act, ITC is not available for goods that are lost, stolen, destroyed, written off, or disposed of as gifts or free samples. If you originally claimed ITC on inventory that you now write off as dead stock, that ITC must be reversed.
Why Your Stock Aging Report Matters Here
A stock aging report gives you the documentation trail to support a write-off decision — showing exactly when the item last moved, how long it has been aging, and the basis for the write-off. This documentation is valuable if your write-off and corresponding ITC reversal is ever questioned during a GST audit.
For traders managing GST compliance alongside inventory, understanding how e-invoicing and inventory movements connect is important — read our complete guide on e-invoicing for traders India for the full compliance picture.
Stock Aging and Annual Stock Valuation for Tax Audit
For businesses subject to tax audit under the Income Tax Act, year-end stock valuation is a key audit area. A well-maintained stock aging report — showing systematic identification of slow-moving and obsolete stock with appropriate valuation adjustments — demonstrates sound business practice and supports your valuation methodology if questioned by tax authorities.
Common Mistakes When Building Stock Aging Reports in Excel
Mistake 1 — Using "Date Received" Instead of "Last Movement Date"
A common error is tracking age from the date stock was received — rather than the date it was last sold or moved. An item received 8 months ago that sold 90% of its quantity last week is not "8 months old" in any meaningful sense — only the remaining 10% is aging stock, and its age should be measured from its own last movement, not the original receipt date.
Mistake 2 — Not Updating the Sheet Regularly
A stock aging report prepared once and never updated becomes useless within weeks. The "Days Since Last Movement" formula will continue to calculate correctly (since it uses TODAY()), but the underlying Quantity and Last Movement Date data will become stale — leading to incorrect conclusions.
Mistake 3 — Mixing Units of Measurement Without Conversion
If your inventory includes items measured in different units (pieces, kg, boxes, litres) and your template does not account for this, stock value calculations and quantity comparisons across items become meaningless. Ensure unit costs are consistently applied per the same unit of measurement as the quantity columns.
Mistake 4 — Ignoring Partial Movement
An item where 80% of the quantity was sold 6 months ago, but the remaining 20% has been sitting since then, requires special handling. A simple "last movement date" for the whole SKU does not capture this nuance — some businesses track aging at the batch or lot level for this reason, particularly for FMCG and pharmaceutical inventory.
Mistake 5 — No Action Tracking
A stock aging report that identifies problems but does not track what action was taken — and whether it worked — provides limited ongoing value. Adding an "Action Taken" and "Action Date" column, reviewed in subsequent months, helps you evaluate whether your clearance and promotion strategies are actually reducing aged stock.
Mistake 6 — Single-User, Single-File Dependency
When the stock aging report exists as a single Excel file on one person's computer — updated manually and shared via email or WhatsApp — it quickly becomes outdated, exists in multiple conflicting versions, and is not accessible to the people who need it (warehouse staff, purchase managers, sales teams) in real time.
Limitations of Excel-Based Stock Aging — When You Need Automation
The Manual Update Burden
For a business with 50-100 SKUs, manually updating last movement dates, recalculating quantities, and reviewing the aging summary monthly takes 2-4 hours. For a business with 500+ SKUs — common for wholesale traders and distributors — manual stock aging in Excel becomes a multi-day exercise that is rarely completed consistently, which means it gets skipped, which means the insights are lost exactly when they matter most.
The Real-Time Visibility Gap
By the time a monthly Excel-based stock aging report is compiled, the data is already weeks old. An item that moved yesterday still shows as "aging" in last month's report. An item that has been sitting for 95 days when the report was prepared is now sitting for 125 days by the time anyone reviews and acts on the report.
The Multi-Location Problem
For trading businesses with multiple warehouses or retail locations, consolidating stock aging data across locations in Excel requires combining multiple files, reconciling different formats, and manually summing totals — a process prone to errors and delays.
The Disconnection From Sales and Purchase Data
An Excel-based stock aging report exists separately from your billing system, your purchase records, and your accounting ledger. Every time you update it, you are manually transferring data from one system to another — introducing delay and the risk of transcription errors.
How ERP Group Automates Stock Aging Reports
Real-Time Stock Aging — No Manual Updates Required
ERP Group's warehouse management system tracks every stock movement — purchases, sales, transfers, returns — in real time. The "Last Movement Date" for every SKU updates automatically the moment a transaction occurs. Your stock aging report is always current — not a snapshot from last month.
Automatic Aging Bucket Classification
ERP Group automatically classifies every item into aging buckets — using the same logic as the Excel template (0-30, 31-60, 61-90, 91-180, 181-365, over 365 days) — with the buckets configurable to match your industry (compressed buckets for FMCG, extended buckets for industrial spares).
Multi-Location Consolidated View
For businesses with multiple warehouses or retail outlets, ERP Group provides both location-wise stock aging (identifying which specific location is holding aged stock) and consolidated aging across all locations — giving management a complete picture without manually combining files.
Connected to Sales, Purchase, and Accounting Data
Because ERP Group is an integrated platform, stock aging data is automatically connected to your sales records (from POS software and billing), your purchase records, and your accounting software for inventory valuation. When you write off aged stock, the accounting entry — including any required GST ITC reversal — is generated automatically.
Automated Alerts and Action Triggers
ERP Group can be configured to automatically alert purchase managers when an item crosses into the "91-180 days" bucket — triggering a review before it becomes dead stock. Sales teams can receive automatic notifications about items in the "Watch List" bucket — enabling proactive promotion before items become slow-moving.
Recommended Action Workflows
Beyond just flagging aged stock, ERP Group supports configurable action workflows — automatically suggesting discount percentages based on aging bucket, generating clearance sale lists for the POS software to apply promotional pricing, and creating purchase return requests for items eligible for return to supplier.
Staff Accountability for Inventory Management
ERP Group's HR & Payroll software can be configured alongside warehouse roles to track which staff member is responsible for category-wise inventory health — supporting performance reviews and accountability for aging stock reduction targets.
From Sales Pipeline to Inventory Action
ERP Group's CRM software can identify customers who have previously purchased slow-moving items — enabling targeted outreach for clearance sales to customers most likely to buy specific aging stock, rather than generic discounting across your entire customer base.
Want the Actual Excel Template?
If you would like the working Excel file — with all formulas, conditional formatting, and the summary dashboard pre-built and ready to use with your own inventory data — let us know and we can generate a downloadable .xlsx file matching the structure described in this guide, ready for you to populate with your item master data.
For businesses managing inventory alongside GST compliance, our guide on automatic e-invoice generation software India explains how invoicing and inventory movement can be connected for complete visibility.
For trading businesses managing warehouse staff alongside inventory operations, read our guide on PF ESI deduction rules for traders India to understand the complete compliance picture for your warehouse team.
Conclusion — Start Tracking Stock Age Before It Becomes a Problem
Every business with physical inventory has aging stock somewhere in the warehouse — the question is whether you know exactly which items, how much value is involved, and what to do about it. A stock aging report transforms invisible, slowly-accumulating risk into a clear, actionable list — ranked by urgency, broken down by category, and tied directly to your bottom line.
The Excel template structure in this guide gives you everything needed to start tracking stock aging today — item master data, movement tracking, automatic aging bucket classification, and a summary dashboard. For businesses with growing SKU counts, multiple locations, or the need for real-time visibility, ERP Group's warehouse management system automates this entire process — connected to your accounting software, POS software, CRM software, and HR & Payroll software for a complete view of your business.
Stop letting capital sit silently on your shelves. Start tracking stock age — and start recovering the cash tied up in slow-moving and dead inventory.
Join 5,000+ Indian businesses managing inventory intelligently with ERP Group.
Automate Your Stock Aging Reports — Book Your Free Demo
👉 Book a Free Demo → — See ERP Group's automatic stock aging reports in action. No credit card required.
👉 Explore Warehouse Management System → — View all inventory management features and pricing for Indian businesses.
