SettleMate

SettleMate — Group Expense Calculator in Excel (No Apps, No Subscriptions)

Tired of chasing friends for money after a group trip? Here’s a free Excel workbook that does the math for you — automatically.

It starts the same way every time. You go on a trip with 5 friends. One person pays for dinner. Another covers the petrol. Someone else books the rooms. By the end of it, nobody remembers who owes who what.

I’ve been there more times than I can count. There are apps for this — Splitwise, Tricount, Settle Up — but they all require everyone to sign up, they have limits on free tiers, and honestly, I wanted something that lived in a file I could just open and close. No notifications. No ads. No subscription.

So I built one. In Excel.

The Problem

Group expense tracking has three hard parts:

1. Logging — Who paid what, and how should it be split?
2. Calculating — After 20+ transactions with 6 people, who owes who?
3. Settling — What’s the minimum number of transfers to get everyone to zero?

Most people stop at step 2. They stare at the numbers and say “just figure it out amongst yourselves.” But that’s where friendships get awkward.

The Solution

HOME — The dashboard. Buttons to add expenses, generate settlements, and export PDFs. Friend list goes here.

Home Sheet Screenshot

EXPENSES — Every transaction gets one row. You enter the date, description, amount, who paid, and how to split it.

Expenses Sheet Screenshot

You can split equal among all friends, or do unequal splits by amount or by percentage. The Validation column catches errors like unrecognized names or percentages that don’t add up.

REPORT — This is where the magic happens. Excel builds an OWS (One-Who-Spent) matrix automatically using formulas. Every cell = how much the row person owes the column person.

Report Sheet Screenshot

This REPORT sheet locked with a password so nobody accidentally breaks the formulas.

Settlement — The generated output. A VBA macro reads the matrix, computes net balances, and applies a greedy algorithm to produce the minimum number of transfers.

Settlement Sheet Screenshot

How the Math Works

Let me walk through the actual algorithm with real numbers from my workbook.

Step 1: Net Balances

From the OWS matrix, we compute what each person should receive minus what they owe:

Sum check: Total owed = Total received = ₹16,647 ✓

Step 2: Minimum Transfers (Greedy Algorithm)

Now comes the clever part. Instead of having everyone pay everyone (which would be 30 transactions for 6 people), we match the highest debtor with the highest creditor and settle in rounds:

1. Divagar (owes ₹2,519) pays Sridhar (owed ₹3,570) — Divagar is done
2. Naveen (owes ₹1,457) pays Sridhar ₹1,051 — Sridhar is done
3. Naveen still owes ₹406 — pays Karthi — Naveen is done
4. Bharath (owes ₹1,450) pays Karthi — done
5. Saravanan (owes ₹533) pays Karthi — everyone is at zero

Result: 5 transactions instead of 30.

Step 3: Verification

Every single person’s balance ends at exactly zero. The algorithm is mathematically guaranteed to settle all debts.

Export to PDF — Click a button and save both the Settlement and REPORT sheets as a single PDF. Perfect for sharing with the group.

Get Bharath Kumar Mani’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

History tracking — Every settlement generation is logged with a timestamp and total amount.

Customizable — Change the currency symbol, password, or category list from the Config sheet.

You can download the full workbook with all VBA macros from GitHub:

🔗 Download on GitHub

The repo includes:

• The patched .xlsm workbook (ready to use)
• Full README with instructions

1. Open the .xlsm file in Excel (desktop version)
2. Enable Macros when prompted

That’s it. Everything else is just clicking buttons.

What I Learned

Building this taught me a few things:

• Excel tables with structured references are surprisingly powerful for financial calculations. The T_TRANS[[#This Row],[Amount]] syntax makes formulas readable.
• VBA Type declarations must come before any Sub or Function — a mistake in the original template I had to fix.
• The greedy min-transaction algorithm is simple to implement (sort creditors and debtors in descending order, match the biggest) but produces optimal results for this use case.
• OLE file manipulation is a rabbit hole. The VBA compression format (MS-OVBA) is non-trivial — best to edit macros directly inside Excel.

Final Thoughts

This isn’t a startup idea or a SaaS product. It’s just a spreadsheet that saves you 15 minutes of awkward math after every group dinner or trip. If you travel with friends, split rent with roommates, or organize group gifts at work — this thing pays for itself in saved brain power.

Download it, try it, and let me know if you break it.

Built with Excel + VBA. No apps, no accounts, no subscriptions.