Create three sheets: orders, returns/RTO and settlement charges. Tag each order as delivered, RTO or customer return, add reverse shipping fees and any lost or damaged stock value, then use COUNTIFS and SUMIFS to get RTO rate and total reverse logistics cost. Compare charges with your seller agreement and list mismatches for recovery.
Define RTO, returns and reverse cost first
Most trackers go wrong because RTO and customer returns share one column. They behave differently, so keep them apart.
- RTO (return to origin): the shipment was not delivered and came back to you.
- Customer return: the buyer received the order and sent it back.
- Reverse logistics cost: reverse shipping fees, any return-handling or related charges in your settlement, plus the value of stock that came back damaged, wrong or not at all.
Which charges apply, and when, depends on your seller agreement and the marketplace rate card. Read those first and do not assume a fee from another seller's sheet.
Set up the columns your tracker needs
Use one row per order line. Pull data from your marketplace order report, returns report and settlement report, and keep the order ID as the common key.
| Column | Source |
|---|---|
| Order ID, SKU, order date | Order report |
| Order value | Order report |
| Final status (Delivered, RTO, Customer return) | Order or returns report |
| Return received date and condition | Your warehouse or returns report |
| Reverse fee charged | Settlement report |
| Reverse fee expected | Your agreement or rate card |
| Stock cost of unit | Your costing sheet |
| Claim status | Your own tracking |
Format the data as an Excel Table (Ctrl+T) so formulas extend automatically when you paste next month's rows.
Formulas that calculate RTO rate and cost
Keep status labels in a small lookup area, such as H1 for RTO, so formulas avoid typed text. With a Table named Orders:
- RTO count: COUNTIFS(Orders[Status],H1)
- RTO rate: RTO count divided by total shipped orders
- Fee difference per row: Reverse fee charged minus Reverse fee expected
- Total reverse fees: SUMIFS(Orders[Fee charged],Orders[Status],H1)
- Lost stock value: SUMIFS(Orders[Stock cost],Orders[Condition],H2), where H2 holds Not received
Use XLOOKUP or VLOOKUP to bring the settlement fee into the order row by order ID. Rows with no match are your first review list, because they are either unsettled or missing from the report.
Check charges against your agreement
Add a column for expected fee using the terms you signed, then flag differences with a simple IF. Typical things to look for:
- Reverse fee charged on an order that was not an RTO.
- Fee higher than your rate card for that weight or category.
- Same order charged twice.
- Commission or forward fee not reversed on a returned order, if your terms say it should be.
- Returned units marked received by the marketplace but missing in your warehouse count.
Confirm the latest rate card and any policy updates directly with the marketplace, and ask your tax adviser how GST or TCS entries on returns should be treated in your books.
Build a monthly summary and recovery list
Create a summary sheet with a PivotTable by month, SKU and status. Track four numbers: RTO rate, average reverse cost per RTO, total fee variance and units pending recovery.
Then filter your tracker for rows where the fee variance is not zero or the condition is Not received, and move them to a recovery sheet with the claim date, ticket number, amount and outcome. Review it weekly, since marketplaces usually set time limits for raising disputes; check yours in the agreement.
When Excel starts to strain
This method works well up to a few thousand orders a month. Beyond that, merging multiple reports by hand is slow and errors creep in. MarketplaceMetrics by Fundle reconciles sales and settlements, checks fees against agreed terms and tracks returns and recovery cases for Indian brands. Myntra Excel reports are reviewed today, and other marketplaces are in scope discussion. Pricing is a tailored quote.
Worked example
Illustrative example only. The fee and cost figures below are made up to show the arithmetic; they are not marketplace rates.
| Item | Value |
|---|---|
| Orders shipped in the month | 500 |
| RTO orders | 40 |
| RTO rate (40 / 500) | 8% |
| Reverse fees charged (40 x ₹70) | ₹2,800 |
| Reverse fees expected per agreement (40 x ₹60) | ₹2,400 |
| Fee variance to dispute | ₹400 |
| Units not received back (3 x ₹450 stock cost) | ₹1,350 |
| Total reverse logistics cost (₹2,800 + ₹1,350) | ₹4,150 |
Here you would raise a claim for the ₹400 fee variance and the ₹1,350 of missing stock, and log both in the recovery sheet.
Questions and answers
What is the difference between RTO and a customer return in a tracker?
An RTO is a shipment that never reached the buyer and came back to you. A customer return was delivered first and then sent back. Track them as separate statuses because the causes, fees and recovery steps usually differ.
Which Excel formulas are most useful for RTO tracking?
COUNTIFS for RTO counts, SUMIFS for fees and stock value by status, XLOOKUP to match settlement charges to order IDs, and IF to flag fee differences. A PivotTable then summarises everything by month and SKU.
How do I know if a reverse logistics fee is correct?
Compare it with your signed seller agreement and the current marketplace rate card for that category and weight. Record the expected fee in a column, subtract it from the charged fee, and review every non-zero difference.
How often should I update the RTO tracker?
Update it whenever a settlement report arrives, and review open recovery items weekly. Regular updates keep you inside any dispute windows in your agreement and stop small fee errors piling up unnoticed.