Tracking rent payments using built in app formulas

[AaronTerry]

I’m trying to write an in-app formula to calculate a tenant’s [Balance] based on the current [Rent] and payment(s) manually logged by a form in a different view and sheet called ‘Payments’ when a tenant e-transfers rent.

In my ‘Rentals’ sheet I was able to make it work by creating a Balance formula like this (=sum(X2:2)-sumif(Payments!A:A,[USER],Payments!G:G)) and a Charge formula like this (=IF(TODAY()>=[1/1/2021],[RENT]) for the first of the month but I’d prefer it in the app so the sheet columns don’t have to be formatted when a new user joins.

I’m thinking EOMONTH(TODAY(), -1) + 1 is involved and a slice might useful but I can’t quite figure it out!

Screenshots of relevant data columns to follow. Link to my previous thread for user roles if someone is building a similar app. Security filter for using 4 different roles, each one with a higher security than the other - #4 by ...

Any help or guidance would be greatly appreciated! Happy with my results so far!