Calculate Who Owes What from a Joint Bank Account

[Lost-Merovingia]

I’m setting up a base that is hopefully going to help me organize my finances with my boyfriend. We have a joint bank account, and I need to be able to show how much money is going in and going out. It seems like he is not leaving enough money to pay for his bills.

So, I’m wondering if I can calculate who owes what in the account. Like … I import the CSV of the bank account, then create a table for people (which would be just two), then add up the balances of all the expenses that are the responsibility of that person …

What about stuff that is both our responsibility … would I create another person and then divide that into two …

I’m so confused. :crazy_face: :frowning_with_open_mouth:

[Justin_Barrett]

I suggest linking to both people if there is shared responsibility. Use a formula field to find the number of linked people (assuming that {Person} is the name of your link field):

IF(Person, LEN(Person) - LEN(SUBSTITUTE(Person, ",", "")) + 1)

Use another formula field to divide the record total by the number of linked people (or combine both into a single formula if you want), which will give you the amount owed per person. If it’s only linked to one person, the divided amount won’t change. For items where both are linked, it will be cut in half. (It’ll actually be a hair off in some cases because values with an odd number of cents won’t divide evenly, so maybe add the ROUNDUP() function around the division calculation to round to the nearest whole penny, which will put you on the high side of half. Still not ideal, but there’s not much else you can do.)

Over in your [People] table (whatever it’s named), you then roll up the total of that last formula using the SUM(values) aggregation formula. Where one person is linked, the full total will go to that person. Where there is a shared responsibility, the halved amount will be added to both of your totals.

[Lost-Merovingia]

I’m sorry … I think this has gone over my head. Where do I put the IF(Person)… formula you gave me?

[Justin_Barrett]

In a new formula field in the table where expenses are being tracked. Substitute “Person” with the reference to your link field that links to your [People] table.

[Lost-Merovingia]

Oh … gotcha … okay … that’s great. It then gives me a count of how many people are responsible for that transaction.

On to step 2 … thanks.

[Lost-Merovingia]

Logistical Question …
The Table with the Transactions has the Expenses the Deposits … like this:

Date | Person | In | Out | Balance | Responsibility (step 1)

So in the formula to divide the amount by the Responsibility … should I Add the in and the out then divide by the Responsibility?

wait … then I’d have to add the - (negative) to all the out entries.

So like …

ROUNDUP ((in+out) / Responsibily )

Forgive my lack of syntax, I don’t know how to formulate the equation but is that would I should be trying to do?

[Justin_Barrett]

My read of your situation is that you’re only dividing the responsibility for actual expenses. For example, if there’s a $150 utility bill to be paid, that gets split evenly and you each pay your portion toward it. I didn’t get the impression that income was also to be similarly divided.

[Lost-Merovingia]

Yeah, I have both, I wanted to make sure that everything balances out … so that all the deposits make up for the expenses … I’m sorry I wasn’t clear. it seems like there should be a template for this, but I looked but couldn’t find one. Maybe this is too basic for Airtable.

[Justin_Barrett]

Thanks for the clarification. In that case, I recommend using two formula fields in your main transaction table—one to calculate the split responsibility for {in} values, the other for {out} values—both using the count of linked people to divide the relevant number.

Over in your [People] table, you’ll then have two rollup fields—one each to summaries the totals for {in} and {out}—and you can then use a formula field to do the rest of the math on those aggregate values.