Cash flow formula for nonprofit grant periods?

[Nikki_Marin_Bae]

Hey, everyone! Our nonprofit is trying to use airtable to keep track of many grants that span different grant periods. I’m trying to figure out if one of the tables we transfer to airtable from excel can be our grant cash flow, where we keep our list of funders, the total grant amount, and then the amount divided by the number of months in the grant period. each grant has a different grant period with a different start and end date. is this possible?

[Justin_Barrett]

Welcome to the community, Nikki! :grinning_face_with_big_eyes: That’s definitely possible. The way I’m picturing it, you would have two tables. One could be [Funders], where you track those who fund the grants and any details about them (contact info, website, etc.). The other would be [Grants], and would be an ongoing collection of all grants received. Each grant would link to a record from the [Funders] table, and have entry fields like {Amount}, {Start Date}, and {End Date}, a {Grant Period (Months)} formula field that uses the DATETIME_DIFF function to determine the number of months in the grand period, and an {Amount per Month} formula field that divides the total grant by the calculated number of months.

If the same funder offers multiple grants over time, you could create a view in the [Grants] table that shows only those active grants, in addition to the master list of all grants received since you began tracking them. Also, by linking to funders from the [Funders] table, that table would keep a running list of all grants that each funder offered over time.

Here’s how the [Grants] table might look:

Is that what you’re looking for?

[JonathanBowen]

Just to add to @Justin_Barrett’s solution, you could also create a table for months/years:

And back in the grants table, have a link field to this table where you can select the months that this grant will apply to:

Now, back in the months/years table, you can pull through a roll-up of the monthly amount to show your cash flow by month as the grants start and end:

What I’d really like to do is have the link field populate automatically based on the start date and end date (or grant period) but I don’t think this is possible without going outside of Airtable (maybe someone has a trick to do this?)

JB