Aggregate Adjustment Calculation help

[Doug_Ardy]

This is a big ask, but I just can’t figure out how to setup this formula. I’m trying to determine the Step 1 “Initial Trial Balance”. The initial trial balance is the lowest account balance for any given month in a 12 month period. The amount can change based on the following variables (other cells):

• First Payment date (Month)
• School Tax Amount
• School Tax Disbursement Date
• Property Tax Amount
• Summer Property Tax Disbursement Date
• Winter Property Tax Disbursement Date

This example scenario/figures can be found here: https://www.consumerfinance.gov/rules-policy/regulations/1024/E/

I can’t figure out how to create the formula so that it calculates correctly given the variables noted above. Anyone care to take a stab at it?

[Jeremy_Oglesby]

Sorry for so much back and forth on this, @Doug_Ardy, but can I also ask…

Is there a particular reason you are trying to use Airtable for this, as opposed to a spreadsheet?

I see you’re responding now… hopefully I can sneak this in – are you using a “Pro” subscription that gives you access to blocks apps?

[Doug_Ardy]

Jeremy_Oglesby:

Sorry for so much back and forth on this, @Doug_Ardy, but can I also ask…

Is there a particular reason you are trying to use Airtable for this, as opposed to a spreadsheet?

No worries, I just appreciate the help. I’m using Airtable for a couple of reasons…

1. I share the results with guests after the values are determined. These guests have view only access.

2. I create different views to make managing the data easy. For example, the Gallery View is great for sharing with the guests. They can view it on their phone or computer in a very easy to understand manner. If I want to run multiple scenarios for people, it is as easy as duplicate, update a few values, and then the Gallery View shares them both.

3. It is a centralized location where I can access from anywhere. I can share the base with other team members, and I have other projects in Airtable that I use (it is just more convenient).

I’m sure most of this could be accomplished with spreadsheets like excel or google sheets, and in some ways it may be easier. But the sharing abilities and views are what I really like about Airtable. I have things 95% complete, it is just this one item I can’t figure out.

I am not currently using a Pro subscription as I share the results with Guests. If I understand correctly, I would be unable to share Pro items with guests (view only). If that is incorrect, I am not against adding a pro subscription. I had always intended to do so at some point, but it seemed that the pro subscription didn’t work well with mu use case.

[Jeremy_Oglesby]

Is it possible for the first payment to hit in the first month of escrow accrual? If not, what is the earliest that could happen?

Say, in the example above, that the first escrow month were July. What would the “Summer Escrow Month” be?

[Doug_Ardy]

Great question. Yes, that is possible. If the first escrow month is July, then the summer taxes would still come out in July (the first month of the escrow accrual). If the first escrow month is August, then the summer taxes would be due in July (i.e. 11 months later).

[Jeremy_Oglesby]

Are the fields {Summer Escrow Months:} and {Winter Escrow Months:} the months that those escrow payments will be deducted from the account?

[Doug_Ardy]

No, those fields are more informational. Technically it represents the number of months from the First Payment Date, until the Due date (although I think I have the formula for summer and winter backwards).

The Summer Taxes are always In July, the Winter Taxes are always in December, and the HOI is always 1-year away from the Closing Date.

[Jeremy_Oglesby]

Ah, ok.

Going to be away from computer for a few hours, but I’ve gotten a start with a pattern that perhaps you can continue to get you moving along.

I’ve created a field for each month (so far months 1-5) that attempts to get the projected balance for that month. Here’s the formula in the first month field:
{__Month 1 Escrow Balance}

IF(
  {Monthly Escrow Amount:}, 
  {Monthly Escrow Amount:}
) - 
IF(
  MONTH({First Payment Due Date:}) = 7, 
  {Buyer's Share of Summer/Village Taxes:}
) - 
IF(
  MONTH({First Payment Due Date:}) = 12, 
  {Buyer's Share of Winter Taxes:}
)

And the second month, {__Month 2 Escrow Balance}

IF(
  {Monthly Escrow Amount:}, 
  {__Month 1 Escrow Balance} + {Monthly Escrow Amount:}
) - 
IF(
  MONTH(DATEADD({First Payment Due Date:}, 1, 'month')) = 7, 
  {Buyer's Share of Summer/Village Taxes:}
) - 
IF(
  MONTH(DATEADD({First Payment Due Date:}, 1, 'month')) = 12, 
  {Buyer's Share of Winter Taxes:}
)

These don’t account for HOI yet, just the escrow accrual and the Summer/Winter payments – but perhaps you can continue that pattern, and then find a way to account for HOI in the formula as well.

This is just the first step, as well. The next step will be creating a formula field that finds the minimum balance out of all the __Month fields.

[Doug_Ardy]

Thank you very much. I will review this and see if I can continue the formula.

[Jeremy_Oglesby · Accepted solution]

Alright, @Doug_Ardy – I think I got it:

So there are the 12 fields that accumulate the escrow payments, and deduct taxes and HOI at the appropriate times, ending with the final balance in month 12 being $0. Then, we find the MIN() value out of all those months, and combine the ABS() of that value with your cushion value.

Here’s the formula in __Month 12 Escrow Balance, which is representative of all the others:

ROUND(
  IF(
    {Monthly Escrow Amount:}, 
    {__Month 11 Escrow Balance} + {Monthly Escrow Amount:}
  ) - 
  IF(
    MONTH(DATEADD({First Payment Due Date:}, 11, 'month')) = 7, 
    {*Annual* Summer/Village Property Taxes:}
  ) - 
  IF(
    MONTH(DATEADD({First Payment Due Date:}, 11, 'month')) = 12, 
    {*Annual* Winter Property Taxes:}
  ) - 
  IF(
    AND(
      MONTH(DATEADD({First Payment Due Date:}, 11, 'month')) = {__HOI Month}, 
      YEAR(DATEADD({First Payment Due Date:}, 11, 'month')) = {__HOI Year}
    ), 
    {*Annual* Homeowners Ins:}
  ), 
  2
)

If you need any help understanding anything about that formula, let me know – if you’re going to maintain this base, it’s important you understand all your formulas!

[Doug_Ardy]

@Jeremy_Oglesby Indeed, it appears you’ve solved it! I added a few more records to the base and sure enough, the formulas you created resolved with the correct answer (except for one of them, but that is because that 1 has a 4th tax amount). I think I can add in that other amount to the mix.

I really can’t thank you enough. While I’m not one to give up, l have no idea how long it would have taken me to get to that point. A long time I’m sure (and less efficient).

I did go through the formulas you added and I understand the concept and can follow along. Some of the formulas are still a bit unclear, but I will go through it further when I have more time so I can better understand the details.

Thank you again, I know you spent a considerable amount of time helping me It is greatly appreciated.