I'm looking to see if the solution below is viable to develop as a script inside Airtable.
We use a loans table and a transactions table to manage a ledger in Airtable. In the transactions table, users enter either capital advances (loans out to clients), or capital reductions (payments against the principle). We also have interest only payments and a few other types for bounces and reverses in this table, but for this all we really care about is the principal impacting records.
This goal of this script is to calculate the accrued interest between any two principal transactions and run this daily to update and hard code it so we always have a daily total of interest accrued on each account.
Here is the rough flow I made in make.com (although it takes too many operations)
- List all active loans
- Iterate through each loan
- Get loan information
- List all principle transactions from the first to last
- Iterate through principle transactions
- Get the first principal transaction
- Find the next most recent principle transaction after (if none, use today)
- Calculate the difference in days between the two (including the day the next day occurs on because we use beginning of day billing)
- Rollup SUM all previous principle impacting transactions prior to the first to calculate the principle balance and then multiply the daily interest rate times the number of days between the two.
- Hard code the resulted interest accrued on the latter of the two transactions.
- Repeat until all principle impacting records have an interest
This is basically an attempt to see if we can replicate our accounting software in airtable. The way it works in the accounting software is it generates a record for every day for that loans daily accrual. Then, if principle is updated in the past or modified it regenerates all of the daily accruals between and overwrites them or creates an adjustment on the next month based on accounting. Due to the record demands and opportunities for automation failure, I don't see this as an effective method for reconstructing it in airtable. With the structure presented above we are not generating any new records and are just appending the principle impacting records with the accrued interest in a column and if we run this daily or weekly we can have the same result with way less management.
Do you think this is viable as a script? Any feedback on the flow? I'm not a programmer, but do a ton of automation work in make, but I would love to hear from the community if this is viable and if so, if anyone is interested in working with us to write the script.