Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Mar 04, 2021 02:15 PM
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):
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?
Solved! Go to Solution.
Mar 10, 2021 04:29 PM
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!
Mar 09, 2021 12:50 PM
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 |
Mar 09, 2021 12:54 PM
Is that copy-pasted from a spreadsheet?
If so, what is the formula in “T” in your spreadsheet?
Mar 09, 2021 01:09 PM
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.
Mar 09, 2021 01:13 PM
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?
Mar 09, 2021 01:46 PM
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.
Mar 09, 2021 02:15 PM
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.
Mar 09, 2021 05:45 PM
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.
Mar 09, 2021 08:13 PM
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:
Mar 10, 2021 05:51 AM
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.