Aggregate Adjustment Calculation help

So maybe this will help to see an example. I pulled these actual examples that already has a solution. I’m trying to solve for for “T - Aggregate Calc” without knowing the answers to the following before hand:

H - Aggregate
I - Actual Initial Deposit
S - Agg Adjust

I can’t find the formula that resolves this equation for the life of me. Any ideas?

Is that copy-pasted from a spreadsheet?

If so, what is the formula in “T” in your spreadsheet?

It is. Formula “T” in the spreadsheet is column R+S. I was trying to work out the formula, but I have to do it without using columns H, I, or S unfortunately.

When you say “without using columns H, I, or S”, do you mean you can’t have those columns existing in your Airtable table at all, or do you mean that they are unknown values, and the formula should only output a result once they are supplied with values?

They are unknown values. Somehow I have to solve for “T” without knowing columns H, I, or S. In other words, we many to to solve for H, I, or S first in order to calculate T. I think I am narrowing in on a solution on how it is calculated, but I still don’t know the formula for Airtable.

Here is how I believe it should work. I will use this one as an example:

First, I have to figure out the ‘Cushion’. That isn’t a column on the spreadsheet currently. The cushion is fairly easy to figure out though. It is simply “G” x 12 x 16.67% = $876.42. Once we know the cushion, here is the hard part. I have to add the cushion to the lowest balance in the escrow account during the 12 month period. I know how it is calculated now, but I don’t know who to structure the formula in Airtable.

In order to calculate what that lowest balance is, we need to know column A- Closing Date, B - First Payment Date, G - Monthly Escrow, and a few other items that I will know (Summer tax due date/annual amount, winter tax due date/annual amount, and HOI due date/annual amount.

From there, I need to create a chart that shows the accruing balance in the escrow account. We start with the first payment month (In this example March) and add G - Monthly Escrow each month minus any expenses coming out of the account (summer/winter/HOI). Here is how it looks in excel:

The summer tax due date is always July. The winter tax due date is always December, and the HOI due date is always 1-year away from the closing date month. As you can see from the above, the lowest balance in the account is -778.92. If I take the positive value of that figure (778.92) and add that to the Cushion of $876.42 above, we get $1655.16.

Not sure if I made this clear enough to follow. Even though I now understand the logic behind it, I have no idea how to get the formula to work in Airtable based on different closing/first payment dates.

Hmm… well, in order to try and help, I think I’d need to know more about how you have your Airtable base structured.

In particular, how are the values for the 12 month period (needed for adding the cushion to) stored/structured? Are they stored in records in a separate table, which you will need to look up in the table in which you are making the calculations?

Since Airtable is a relational database, it’s going to behave a bit differently than the spreadsheet you are currently working with. That might make it a little more difficult than just doing a VLOOKUP on another sheet. You might need to have linked record relationships.

I really don’t know how to start approaching your dilemma without knowing how your data is structured.

Thank you for the help with this. Currently I have no other sheets or linked records in this Airtable Base. I have most of the data needed to calculate the above all contained within one record. I am missing the part that determines the lowest balance in the account for the next 12 months (i.e. the part below). I don’t know how to set this up in Airtable based on the variable with the first payment due date.

I have all the other necessary data/formulas within the base to determine the first payment due date, monthly escrow, tax due dates, etc. These are all formulas that change based on the inputted closing date. In other words, I enter the closing date, property tax amounts, the Airtable formulas calculate the rest.

I consider myself a novice at Airtable and formulas in general, but I eventually figure things out with lots of trial and error. I use the excel spreadsheet to help me wrap my mind around things when I get stuck. I haven’t ever used a linked record field or VLookup, but if that is the best way to accomplish this, I’m willing to read up on it.

I’m not sure this has answered your question sufficiently, but if you have any suggestions on how to proceed, I would sure appreciate any input. Thank you again for your time.

• How often will you be creating these records that need to calculate the Aggregate Adjustment?
• How often will the values for monthly escrow accrual be added and/or changed?
• Will the monthly escrow accrual be something tracked/projected on a monthly basis? In other words, if you were to track them on a table, would the records need to correspond to a Month-Year?

I think that in order to fit what you are wanting to accomplish into Airtable (as opposed to in a spreadsheet), you need to be able to map it to a data model. I’m not an accountant, but I’m a fair hand at building data models. However, I’m struggling to wrap my head around what your data modeling needs are here.

An example of what I mean by fitting this to a data model is like this - if I want to model a budgeting system in Airtable, I need, at minimum:

• the concept of an account - so I need a Table for Accounts, where each record represents an Account and can reflect that account’s balance
• the concept of a transaction - so I need a Table for Transactions; I want a transaction to belong to an account, so I need there to be a linked record relationship between the Transactions Table and the Accounts Table
  • a transaction needs to be able to be of the type Debit or Credit - so I need some way to model that in the Transactions Table, maybe with a Single Select field that can toggle the value as being positive (a credit to the Account it’s linked to) or negative (a debit from the Account it’s linked to)
• the Accounts table can now roll up its balance by SUM’ing all its credit and debit transactions
  … and so on…

Since the escrow accrual table is based on the closing/first payment date, I think there would only be 12 different table versions (closing date is January through December). The amounts will be different on each table though since the summer/winter/hoi will vary per record.

Based on your example above, could we get by with a table that just has 12 different escrow accrual tables and then somehow plug the values into that (and read the results)? I’m not sure how this works in Airtable. If you have a basic structure suggestion, I will research and try and figure it out. I’m lost on how to start. Thank you again for your time.

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?

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.

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?

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).

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

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.

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.

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:}
)

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:}
)

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

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
)

@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.

@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.

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:

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

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.

I already appended this to the field for you:

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.