Travel and vehicles

Flight refund status tracker: airline, date, amount and mode

A flight refund tracker sheet with PNR, dates, amount paid, refund promised and received, mode and follow-up date, plus formulas to flag refunds to chase.

Editorial illustration for Flight refund status tracker: airline, date, amount and mode

A flight refund tracker is a simple sheet with one row per cancelled booking: PNR, airline, the dates, what you paid, what the airline promised back, what has actually arrived, how it is coming and when you will check next. Two formulas then tell you how much is still owed and which rows need a nudge. Here is the layout and the formulas, ready to type into Excel or Google Sheets.

The columns to set up

  1. A: PNR or booking reference.
  2. B: Airline.
  3. C: Booking date.
  4. D: Cancellation date.
  5. E: Amount paid.
  6. F: Refund promised.
  7. G: Refund received.
  8. H: Mode: original payment mode, airline credit, or voucher.
  9. I: Follow-up date.
  10. J: Status: Open or Closed.
  11. K: Still due, a formula.
  12. L: Cancellation loss, a formula.

Put headings in row 1 and start data in row 2. The formulas below assume data runs down to row 200, which is plenty for most households.

The formulas that do the work

  • Still due in K2: =F2-G2
  • Cancellation loss in L2: =E2-F2
  • Chase flag in M2: =IF(AND($J2="Open",$I2<TODAY()),"Chase","")
  • Total still due across open rows: =SUMIFS($K$2:$K$200,$J$2:$J$200,"Open")
  • Count of rows to chase: =COUNTIF($M$2:$M$200,"Chase")

Copy K2, L2 and M2 down the column. TODAY() recalculates each time you open the sheet, so the chase flag moves on its own as dates pass. In Google Sheets the same formulas work without change.

A worked example

The rows below are made up.

PNRPaid in ₹Promised in ₹Received in ₹ModeStatus
AB12CD9,8007,6007,600Original payment modeClosed
EF34GH5,2004,9000Original payment modeOpen
IJ56KL12,40012,4006,000Airline creditOpen

Row 1: the cancellation loss is 9,800 minus 7,600, which is ₹2,200, and nothing is owed. Row 2: ₹4,900 is still due. Row 3: ₹6,400 is still due, because only ₹6,000 of the ₹12,400 has arrived. The total still due is 4,900 plus 6,400, which is ₹11,300. Closed rows drop out of the total because SUMIFS counts only Open ones.

What does refund mode mean for the record?

A refund to the original mode returns money to the account or card you paid from. Airline credit or a voucher is not money: it can only be spent with that airline, usually within a time limit stated in the terms. Record it in the Mode column, put the credit's expiry date in the follow-up date, and treat it as an asset you must use rather than cash you have recovered.

Where does the received amount come from?

From your statements, not from the airline's email. When a credit arrives, match it to the row by amount and date and type it into column G. Part payments are fine: add them up in the cell, for example =3000+3000, so the history is visible.

When a sheet stops being enough

If you want the debit, the credit and the rest of your month in one place, SpendixAI lets you import the bank or card statement as PDF, CSV or Excel, review the rows before saving, and see each transaction against its account. The tracker is still handy for the promised-versus-received comparison.

What to do when the sheet shows a gap

A gap is simply promised minus received. Before you chase, check three things: whether the refund period has really passed, whether the credit went to a different account than you expected, and whether it arrived under a payment-gateway name that you did not recognise. Searching your statement by amount usually settles the second and third in a couple of minutes.

Checklist

  • One row per booking, never per passenger unless amounts differ.
  • Fill the cancellation date and the follow-up date on the same day.
  • Update G the day you see a credit.
  • Close a row only when received equals promised or you have written off the difference.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports