Aggregate Adjustment Calculation help

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?

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?

B - First Payment Date C - Winter Taxes D - Summer Taxes E - HOI F- USDA Annual G - Monthly Escrow H - Aggregate I - Actual lnitial Deposit J - Winter Months K - Winter $ L - Summer Months M - Summer # N -HOI Months O -HOI $ P - USDA Months Q - USDA $ R - Totaled S - Agg Adjust T - Aggregate Calc
6/1/2021 $25.00 $180.00 $65.00 $270.00 -$370.00 $2,160.00 7 $175.00 12 $2,160.00 3 $195.00 $0.00 2,530.00 -$370.00 $2,160.00
4/1/2021 $5.75 $213.60 $75.00 $294.35 -$415.25 $1,974.50 5 $28.75 10 $2,136.00 3 $225.00 $0.00 2,389.75 -$415.25 $1,974.50
6/1/2021 $24.30 $51.94 $65.00 $23.86 $165.10 -$436.68 $623.28 7 $170.10 12 $623.28 3 $195.00 3 $71.58 1,059.96 -$436.68 $623.28
4/1/2021 $84.98 $56.59 $108.83 $58.31 $308.71 -$816.52 $617.42 5 $424.90 10 $565.90 3 $326.49 2 $116.62 1,433.91 -$816.49 $617.42
3/1/2021 $104.07 $247.46 $86.59 $438.12 -$1,248.03 $1,655.16 4 $416.28 9 $2,227.14 3 $259.77 $0.00 2,903.19 -$1,248.03 $1,655.16

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:

A - Closing Date B - First Payment Date C - Winter Taxes D - Summer Taxes E - HOI F- USDA Annual G - Monthly Escrow H - Aggregate I - Actual lnitial Deposit J - Winter Months K - Winter $ L - Summer Months M - Summer # N -HOI Months O -HOI $ P - USDA Months Q - USDA $ R - Totaled S - Agg Adjust T - Aggregate Calc
1/1/2021 3/1/2021 $104.07 $247.46 $86.59 $438.12 -$1,248.03 $1,655.16 4 $416.28 9 $2,227.14 3 $259.77 $0.00 2,903.19 -$1,248.03 $1,655.16

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:

March $438.12
April $876.24
May $1,314.36
June $1,752.48
July $2,969.52 -$778.92
August -$340.80
September $97.32
October $535.44
November $973.56
December $1,248.84 $162.84
January $1,039.08 -$438.12
February $0.00

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.

March $438.12
April $876.24
May $1,314.36
June $1,752.48
July $2,969.52 -$778.92
August -$340.80
September $97.32
October $535.44
November $973.56
December $1,248.84 $162.84
January $1,039.08 -$438.12
February $0.00

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.

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

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!

2 Likes

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

1 Like