Is scripting a viable solution for Financial Interest Accruals?

[benhamilton]

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)   

1. List all active loans
2. Iterate through each loan
3. Get loan information
4. List all principle transactions from the first to last
5. Iterate through principle transactions
6. Get the first principal transaction
7. Find the next most recent principle transaction after (if none, use today)
8. Calculate the difference in days between the two (including the day the next day occurs on because we use beginning of day billing)
9. 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.
10. Hard code the resulted interest accrued on the latter of the two transactions.
11. 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.

[Dominik_Bosnjak]

Definitely possible to accomplish this with a script and a combination of computed fields, like you outlined.

It might feel a bit awkward to get through the door at first since you don't have any margin for error and would hence likely have to spend much more time testing edge-case behaviors than putting the actual prototype together. And while you'd definitely be able and tempted to delegate a lot of the prepwork to formulas, another alternative to consider is going for a more robust, largely standalone script that would be easier to keep relevant should your workflow ever change to any degree and for whatever reason.

So, save for maybe the unlikely scenario of having so much traffic that your entire setup is no longer sustainable solely through Airtable, your script idea is certainly viable, but could still end up feeling anything from inelegant to prototype to inflexible in face of changes. Obviously, not every new functionality you commit to can or needs to be perfectly reusable, but modularity should start coming into consideration by the time your Airtable workflows are complex enough to start warranting consideration as potential replacement for tools as deeply entrenched as your typical accounting software. 🙂