A reward points tracker needs only six columns: date, what happened, type, points, rupee value and a running balance. Earned points go in as positive numbers, redeemed or expired points as negatives, and one formula keeps the balance honest. Add a value-per-point column and you will know what the points are really worth to you.
What columns does the sheet need?
- A: Date of the entry.
- B: Entry, for example the statement month or the item you redeemed for.
- C: Type, using only Earned, Redeemed, Expired or Adjustment so formulas can filter on it.
- D: Points, positive when you gain points and negative when you lose them.
- E: Rupee value, filled only for redemptions: the cash price of what you received.
- F: Balance, a running total of column D.
- G: Value per point, calculated for redemptions only.
What do the sample rows look like?
The rows below are made up to show the shape. Notice that the redemption is a negative number of points and the value sits in column E.
| Date | Entry | Type | Points | Rupee value | Balance |
|---|---|---|---|---|---|
| 15 Jan | January statement | Earned | 1,200 | n/a | 1,200 |
| 15 Feb | February statement | Earned | 900 | n/a | 2,100 |
| 20 Feb | Headphones | Redeemed | -2,000 | 1,000 | 100 |
| 15 Mar | March statement | Earned | 1,500 | n/a | 1,600 |
Which formulas do the work?
Type these into row 2 and copy them down. Use ranges that end well below your last row, such as row 500.
- Running balance in F2: =SUM($D$2:D2)
- Value per point in G2: =IF(C2="Redeemed", E2/ABS(D2), "")
- Total points earned: =SUMIFS($D$2:$D$500, $C$2:$C$500, "Earned")
- Total points redeemed: =-SUMIFS($D$2:$D$500, $C$2:$C$500, "Redeemed")
- Your average value per redeemed point: =SUMIFS($E$2:$E$500, $C$2:$C$500, "Redeemed") / -SUMIFS($D$2:$D$500, $C$2:$C$500, "Redeemed")
Check the sample. The headphones took 2,000 points and were worth ₹1,000 in cash, so value per point is 1,000 ÷ 2,000 = ₹0.50. The balance after 20 February is 2,100 − 2,000 = 100 points, which is what the SUM formula gives.
How often should you add a row?
Add an Earned row when each statement arrives, using the points the statement shows, not your estimate. Add a Redeemed row the day you redeem. If points are credited in a separate line for a bonus or a reversal, record it as Adjustment with a clear label. A monthly sitting of ten minutes is enough.
Expired points go in as a negative row typed Expired, on the date the card removed them. Points taken back after you return a purchase go in as a negative Adjustment, labelled with the item. Entering these as they happen keeps column F equal to the real balance instead of drifting upwards.
What can the sheet tell you once it has a few months of rows?
- Whether your value per point is stable or falling, by reading column G down the page.
- How many points a typical month earns, by looking at the Earned rows.
- Whether points are sitting unused, if the balance only ever climbs.
- Whether a redemption was poor value compared with your own average, which tells you what to avoid next time.
To see points earned per ₹100 of spend for a month, divide that month's earned points by the month's card spend and multiply by 100. With 1,200 points on ₹60,000 of spend, that is 1,200 ÷ 60,000 × 100 = 2 points per ₹100. Use it as a check that the statement credited what you expected, not as a promise of any rate.
How do you know the sheet matches the card?
Once a month, compare the balance in column F with the balance your card app or statement shows. If they differ, the gap is the first thing to investigate; it is usually a missing row, a reversal or a delayed posting. The same check works when something looks wrong with a statement.
What if the sheet stops being enough?
A points sheet is a good tool for the points themselves. If you find the spend side is the hard part, such as collecting every card swipe from several statements, SpendixAI can take the card statement as a PDF, CSV or Excel upload, with the card chosen as the account, and keep the spending in one place. The points stay in your sheet.
A short setup checklist
- Create the seven columns and freeze the top row.
- Enter your current points balance as the first row, typed Adjustment.
- Add every statement's earned points from now on.
- Log each redemption with a rupee value you can defend.
- Compare column F with the card app at the end of each month.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


