Yes, Google Sheets is enough for a straightforward IOU tracker. One row per reimbursement. Log the lender, borrower, balance, due date, and payment status on that row, and the sheet handles the rest.
Do you need a dedicated app for this? Not for roommate utilities, a shared dinner, or a group trip. The formulas below cope with partial payments too, and they keep the original amount on the row so nothing gets erased.
Start with one row per reimbursement
Open a blank sheet and reserve row 1 for headers. Each row describes one person-to-person obligation, not the entire receipt, so if Alex paid for three people, Alex gets three borrower rows.
That keeps the math clean.
| Column | Header | What to enter |
|---|---|---|
| A | Lender | The person who paid upfront |
| B | Borrower | The person expected to reimburse the lender |
| C | Description | A short note such as Groceries or Concert parking |
| D | Amount Owed | The original amount assigned to the borrower |
| E | Paid Amount | The amount received so far; enter 0 for a new IOU |
| F | Balance Due | A formula that subtracts payments from the original amount |
| G | Due Date | The agreed reimbursement date |
| H | Due? | A formula that shows Open, Overdue, or Paid |
| I | Status | Pending, Partial, or Paid |
| J | Notes | A receipt link, payment reference, or short agreement note |
Two setup details matter here. Enter actual dates in column G, not text that only looks like a date, and format D through F as currency with G as a date. Then add a dropdown to I2:I with Pending, Partial, and Paid, using Data > Data validation or the sheet's Insert > Dropdown option. Consistent labels sound fussy until you notice the summary formulas depend on them.
Add balance and due-date formulas
Put these in row 2, then copy down. The dollar signs lock the column while letting the row number move.
Balance Due in F2
=IF($D2="","",MAX(0,$D2-$E2))Say the original amount is $45 and the borrower has paid $20. The balance shows $25. Once E2 holds the full amount, F2 drops to zero.
Due? in H2
=IF($G2="","",IF($I2="Paid","Paid",IF($G2<TODAY(),"Overdue","Open")))A due date of today still counts as Open. The label flips to Overdue after the date passes, unless the status already says Paid.
If you don't need partial payments, leave Paid Amount at zero until the IOU is settled. Keeping the column in place means a future partial payment won't force a redesign.
Highlight overdue IOUs
Select a range such as A2:J1000, open Format > Conditional formatting, and add this custom formula:
=AND($G2<>"",$G2<TODAY(),$I2<>"Paid")Pick a red fill or any style you'll actually notice. The rule checks the due date and the status for each row, so both Pending and Partial IOUs get flagged.
Thing is, a date check isn't a payment reminder. Someone still has to open the sheet. Use a calendar reminder or a group message separately, especially for rent, deposits, or travel costs.
A quieter rule helps finished rows fade back:
=$I2="Paid"Muted fill, maybe strikethrough. Settled rows get easier to scan that way. Treat the status and balance as the real record and the color as a visual aid only.
Calculate totals by person
Park a small summary area at L1, or use a second tab. Type a person's name into L2, and these formulas take it from there:
| Summary goal | Formula if the name is in L2 |
|---|---|
| Amount owed to that person | =SUMIFS($F$2:$F$500,$A$2:$A$500,$L2) |
| Amount that person owes | =SUMIFS($F$2:$F$500,$B$2:$B$500,$L2) |
| Unpaid amount owed to that person | =SUMIFS($F$2:$F$500,$A$2:$A$500,$L2,$I$2:$I$500,"Pending")+SUMIFS($F$2:$F$500,$A$2:$A$500,$L2,$I$2:$I$500,"Partial") |
For the outstanding balance across the whole sheet:
=SUM($F$2:$F$500)The common mistake is summing the wrong column. Use Balance Due, not Amount Owed, or that $45 IOU with a $20 partial payment still looks like $45 outstanding.
The example ranges stop at row 500. If your sheet grows past that, extend every range together so the totals stay honest.
Keep payment updates on the original row
One IOU, one row, updated in place. That's the whole rule.
- A new expense creates an IOU. Add one row with the original amount and a due date.
- A partial payment comes in. Update Paid Amount, leave the balance formula alone, and switch Status to Partial.
- A full payment arrives. Enter the amount received, confirm Balance Due shows zero, and change Status to Paid.
Turns out duplicate rows are the classic trap here. Don't add a second row for every payment unless you genuinely want a separate payment ledger. Duplicates can make both people look like they owe more than they do.
When an agreement changes, add a short note: a revised due date, a payment reference, the reason an amount is disputed. Keep the wording factual. Boring holds up.
Adapt the tracker for recurring or uneven expenses
Roommate rent usually needs one extra column called Period. Enter values like March 2026 or April 2026, with one row for each roommate's share in each period.
For a trip, use one row per borrower and expense share, so a hotel deposit, a gas purchase, and a shared grocery run each carry their own description, which makes it far easier to explain a total to the group weeks later when everyone's memory of who paid for what has gone soft.
Uneven splits fit this layout too. Enter the amount each person actually agreed to instead of dividing every receipt equally by default. The sheet records the agreement. It doesn't decide whether equal, usage-based, room-size, or income-based splitting is fair.
Share the sheet without losing formulas
Share one live file rather than emailing copies around. People who add expenses or confirm payments get edit access; anyone who only needs to check totals gets view access.
Protect columns F and H through Data > Protect sheets and ranges, and leave the entry columns editable, especially A through E, G, and I through J. A careless click then can't wipe out a formula.
To be honest, shared money records hold private information. Check who can access the file, and keep account numbers or sensitive payment details out of Notes.
Before a major cleanup, save a named version. If someone deletes a row or overwrites a formula, version history lets you review and restore an earlier state.
Common setup mistakes
A due date is not recognized. Reformat column G as a date and re-enter the value if needed. A date stored as plain text won't cooperate with TODAY().
Every row says Open. Check that G holds a real date and that the formula in H references its own row. H2 should read G2 and I2, H3 should read G3 and I3, and so on down the sheet.
Paid rows turn red. The conditional-formatting rule has to exclude the exact status in your dropdown. The formula above excludes Paid, so spelling and capitalization need to match.
Totals look too high. Sum Balance Due in column F. Summing Amount Owed in column D ignores partial payments.
A payment is counted twice. Search for duplicate descriptions and borrowers before adding another row. If the payment belongs to an existing IOU, update that row instead.
Know where a spreadsheet stops
This tracker records obligations, dates, balances, and updates. It doesn't move money, verify a transfer, or settle a disagreement.
It works well when a group wants a shared ledger and the occasional reminder. A dedicated tool earns its keep when you need frequent payment requests, automatic reminders, receipt scanning, or a more involved split calculation. The basic recordkeeping habits stay the same either way.
Start with one real IOU. Enter a test partial payment, confirm the balance and overdue formatting behave, and only then share the sheet.