Aggregate Adjustment Calculation help

Doug_Ardy · ‎Mar 04, 2021

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 · ‎Mar 10, 2021

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!

See Solution in Thread

Doug_Ardy · ‎Mar 09, 2021

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

Jeremy_Oglesby · ‎Mar 09, 2021

Is that copy-pasted from a spreadsheet?

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

Doug_Ardy · ‎Mar 09, 2021

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.

Jeremy_Oglesby · ‎Mar 09, 2021

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?

Doug_Ardy · ‎Mar 09, 2021

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	G - Monthly Escrow	H - Aggregate	I - Actual lnitial Deposit	J - Winter Months	K - Winter $	L - Summer Months	M - Summer #	N -HOI Months	O -HOI $	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.

Jeremy_Oglesby · ‎Mar 09, 2021

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.

Doug_Ardy · ‎Mar 09, 2021

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.

Jeremy_Oglesby · ‎Mar 09, 2021

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…

Doug_Ardy · ‎Mar 10, 2021

Jeremy_Oglesby:

How often will you be creating these records that need to calculate the Aggregate Adjustment? – I will be creating these records multiple times per day.

How often will the values for monthly escrow accrual be added and/or changed? – The monthly escrow amounts will be different on each record. Each record will have a potentially different closing date and 1st payment date as well. In other words, each record can be completely unique.

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? – The monthly escrow accrual is a one-time calculation and will not need to be tracked or updated. Once we know the closing date and the summer/winter/HOI amounts (which I will know when creating the record), then the escrow accrual could be completed. It’s not something I would need to change unless for some reason the closing date changed.

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.