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?

[Doug_Ardy]

@Jeremy_Oglesby I ran a few more scenarios and found one issue with the formula. If the “Loan Program:” field = “USDA” then we have to add another amount to the 12 month minimum calculation. I was able to adjust the monthly escrow amount to account for this, but I’m having a hard time adding the fee to the 12 month calculation.

Long story short, I need to add the “__USDA Annual Fee” amount to the 12 month calculation on the 12 month from the “First Payment Due Date:” So if the First Payment Due Date is 4/1/21, it would be added on 3/1/22. This can impact the balance calculation.

I’m sure there is probably one line of code to add to each month to make this work, but I have having a hard time figuring it out. Do you mind taking one more look at it with me? I PM’d you the update share link.

[Jeremy_Oglesby]

Doug_Ardy:

on the 12 month from the “First Payment Due Date:”

It’s always in the 12th month?

If that’s the case, then you should be able to get away with only modifying the formula for {__Month 12 Escrow Balance}.

I appended this to the formula in that field:

- IF(
   AND(
      {Loan Program:} = "USDA", 
      {__USDA Annual Fee:}
   ), 
   {__USDA Annual Fee:}
)

That should do it, unless there are any cases where this fee could hit in a different month than the 12th from the First Payment.

Also, something to note about rounding errors… Airtable leaves all number values, whether they originated in currency fields or number fields, as Floating Point numbers behind the scenes, even if the field only displays precision to the hundredths. This can cause unexpected rounding issues, where you can end up off by a cent where you didn’t expect it - and this can compound on itself across multiple calculations (formula fields) to produce even greater errors down the road. I would encourage wrapping ALL calculations that produce a dollar value as output in a field in the ROUND() function, with a precision of 2. I think this will force the result of the calculation to round the number the field is holding behind the display to match the display (cutting all floating point numbers down to the hundredths precision).

See here:

Floating Point Error - Addition/Subtraction Formulas

The ROUND function can takes a “precision” argument, which, for two decimal places is “2”. Does the below formula work for you? =ROUND({Reference Instrument Temperature}-{DUT Temperature},2) Alternatively, there’s the… classic but dumb way of doing it, which is to multiply the numbers by 100 before any arithmetic, then dividing it by 100 at the end: =(ROUND({Reference Instrument Temperature}*100,0)-ROUND({DUT Temperature}*100,0))/100

[Doug_Ardy]

Thank you Jeremy, I will try adding that formula to the field and running a few tests. I believe it is always the 12th month, so that should work.

I also appreciate the info on the ROUND() function. I have noticed on occasion that a few of my calculations are off by a penny. I had bigger fish to fry than worry about that, but it sounds like good practice to get in the habit of that anyway. I will go back and add that function to my calculations as needed when I have more time.

Thank you for all your help and insight.

[Jeremy_Oglesby]

Doug_Ardy:

I will try adding that formula to the field

I already appended this to the field for you:

Jeremy_Oglesby:

I appended this to the formula in that field:

- IF(
   AND(
      {Loan Program:} = "USDA", 
      {__USDA Annual Fee:}
   ), 
   {__USDA Annual Fee:}
)

[Doug_Ardy]

I just noticed that, thank you. There is something that still isn’t working perfectly on the USDA ones. The Initial Deposit For Your Escrow account number is off. I verified the addition you made to the 12 month formula is working though and it appears it is. It must be related to one of the other steps, I will delve into it further and look at a few more examples. I’m sure it is something simple I am missing.

The none USDA loan program records are still working fine.