Settle Up!

Expenses

Money Matchmaker

Past Payments

How it works

Explore

How it works

There are several intermediate steps to the Settle Up calculations, visible in the hidden page

Behind the curtain⁠

First, we generate a

Money Owed⁠

table listing each unique person mentioned as a payer or beneficiary in the Expenses table (we could also include someone who only appears in

Past Payments⁠

but I haven’t done that yet). The code that does that is triggered in the “Reset Money Owed” button (after it clears previously generated rows):

ListCombine(Expenses.Payer, Expenses.Beneficiaries)

.Unique()

.Filter(CurrentValue.IsNotBlank())

.FormulaMap(

AddRow(

[Money Owed],

[Money Owed].[Who?], CurrentValue

)

From there, the columns “Money Spent” and “Money Received” in

Money Owed⁠

have formulas to sum across all

Expenses⁠

and

Past Payments⁠

, and the difference between them is the total owed by the individuals to the group (and vice-versa). We put that in the ”Money Owed” column.

To go from that vague state of general “owing/owed”, we need to calculate specific suggested payment one-by-one. To track our progress we keep a “Remaining Owed” column on

Money Owed⁠

that is not defined by a formula, it’s only updated as we generate payments. The “Reset Money Owed” button sets its initial values to simply be the “Money Owed” column from the above calculation:

FormulaMap(

[Money Owed],

ModifyRows(

CurrentValue,

[Remaining Owed], Owed

)

The “Generate next payment” button is what creates a suggested payment. Each payment suggestion is made by having the most-in-debt person pay the person who is owed the most:

RunActions(

AddRow(

[Suggested Settleups],

[Suggested Settleups].From, [Most in debt].[Who?],

[Suggested Settleups].To, [Most owed].[Who?],

[Suggested Settleups].Amount, [Next Payment Amount]

ModifyRows(

[Most in debt],

[Money Owed].[Remaining Owed],

[Most in debt].[Remaining Owed] + [Suggested Settleups].Last().Amount

ModifyRows(

[Most owed],

[Money Owed].[Remaining Owed],

[Most owed].[Remaining Owed] - [Suggested Settleups].Last().Amount

)

The payment is added by the AddRow action, and then the 2 participants get their “Remaining Owed” column in

Money Owed⁠

updated to reflect the payment that was just suggested.

This logic heavily relies on the separate formulas that calculate the participants “Most in debt” and “Most owed”, and the “Next Payment Amount” that prevents an overpayment (in cases when the in-debt participant owes more to the group than the group owes the most-owed participant).

The final touch is populating

Suggested Settle Ups⁠

by having the “Generate next payment” button executed repeatedly (once per participant) by the “Suggest Some Settle Ups” button:

Sequence(1, [Money Owed].Count())

.FormulaMap(RunActions([Generate next payment]))

This magic button doesn’t even need to be clicked because it gets executed by the automations that listen for any changes in the

Expenses⁠

and

Who has Settled Up so far?⁠

tables.

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.