State of Foundation Giving 2026

-- State of Foundation Giving 2026 — every statistic on the page, as run
-- (read-only) against Grantable's production database on 2026-10-04.
-- Results are frozen in state-of-foundation-giving-2026.json next to this file.
--
-- Table: master_organizations (one row per EIN). `financials` holds one entry
-- per fiscal year, keyed by the calendar year the fiscal year ENDS in, parsed
-- from IRS Form 990 / 990-PF e-files: grant_sum and grant_count are the sum and
-- count of the itemized grant list; distributable_amount and
-- qualifying_distributions are 990-PF Part XII / XIII figures.

-- ── The universe ────────────────────────────────────────────────────────
-- pf_all: every U.S. private foundation with a FY2024 990-PF on file. A year
--   whose form was not stored is treated as a 990-PF when it reports a
--   distributable amount or qualifying distributions (only the 990-PF has
--   those lines) — the same rule the site's funder profiles use.
-- pap: corporate patient-assistance foundations (donated medicine, reported
--   as one multi-billion "grant" line). Excluded from every figure.
-- r: the report universe — pf_all with itemized FY2024 grants > 0, minus pap.
with pf_all as (
  select m.id, m.name, m.state,
         coalesce((f->>'grant_sum')::numeric, 0)                as gs,
         coalesce((f->>'grant_count')::int, 0)                  as gc,
         coalesce((f->>'assets')::numeric, 0)                   as assets,
         coalesce((f->>'distributable_amount')::numeric, 0)     as da,
         coalesce((f->>'qualifying_distributions')::numeric, 0) as qd,
         coalesce((m.financials->'2023'->>'grant_sum')::numeric, 0) as gs23,
         (f ? 'months_covered')                                 as merged_year
  from master_organizations m, lateral jsonb_each(m.financials) as j(y, f)
  where j.y = '2024'
    and m.is_funder and not coalesce(m.is_archived, false)
    and coalesce(m.country, 'US') = 'US'
    and (f->>'form' = '990PF'
         or (f->>'form' is null
             and (coalesce((f->>'distributable_amount')::numeric, 0) > 0
                  or coalesce((f->>'qualifying_distributions')::numeric, 0) > 0)))
),
pap as (
  select id from pf_all
  where name ~* 'patient (assistance|access|foundation)'
     or (name ~* '\mcares\M' and gs >= 50000000)
),
r as (
  select * from pf_all where gs > 0 and id not in (select id from pap)
)

-- 1. Size of the universe, total dollars, total grants.
--    → 94,088 foundations · $105,842,307,900 · 1,581,740 grants
select count(*), sum(gs), sum(gc) from r;

-- 2. Concentration: rank by FY2024 grants paid; the top 1% is the first
--    ceil(n × 0.01) rows (941), the top 10% the first ceil(n × 0.10) (9,409),
--    the bottom half every row ranked below n × 0.5 (47,044).
--    → top 1%: $67,229,749,487 (63.5%) · top 10%: $93,957,639,220 (88.8%)
--    → bottom half: $1,177,216,951 (1.1%)
select sum(gs) filter (where rn <= ceil(n * 0.01))  as top1,
       sum(gs) filter (where rn <= ceil(n * 0.10))  as top10,
       sum(gs) filter (where rn > n * 0.5)          as bottom50
from (select gs, row_number() over (order by gs desc) rn, count(*) over () n from r) ranked;

-- 3. The typical foundation (medians across foundations).
--    → giving $71,253 · 6 grants · average grant $11,619 · assets $814,837
select percentile_cont(0.5) within group (order by gs),
       percentile_cont(0.5) within group (order by gc),
       percentile_cont(0.5) within group (order by gs / gc),
       percentile_cont(0.5) within group (order by assets)
from r;

-- 4. Size bands and payout by band.
select case when gs < 10000 then 'under $10K' when gs < 50000 then '$10K–$50K'
            when gs < 250000 then '$50K–$250K' when gs < 1000000 then '$250K–$1M'
            when gs < 10000000 then '$1M–$10M' else '$10M+' end as band,
       count(*), sum(gs), sum(gc),
       count(*) filter (where da > 0 and qd > 0)            as payout_reported,
       count(*) filter (where da > 0 and qd > 0 and qd < da) as below_minimum,
       percentile_cont(0.5) within group (order by qd / da) filter (where da > 0 and qd > 0) as median_payout
from r group by 1;

-- 5. Same-foundation change FY2023 → FY2024: foundations with itemized grants
--    in both years; a year merging two returns after a fiscal-year-end change
--    (merged_year) is left out.
--    → 87,893 foundations · $96,467,394,695 → $104,395,749,165 (+8.2%)
--    → 33,269 up more than 10% · 23,430 down more than 10% · median +1.0%
select count(*), sum(gs23), sum(gs),
       count(*) filter (where gs > gs23 * 1.1), count(*) filter (where gs < gs23 * 0.9),
       percentile_cont(0.5) within group (order by gs / gs23)
from r where gs23 > 0 and not merged_year;

-- 6. Payout: qualifying distributions ÷ distributable amount (the 5% minimum),
--    foundations reporting both.
--    → 82,935 foundations · median 108.9% · 32,475 (39.2%) below 100% in FY2024
select count(*), percentile_cont(0.5) within group (order by qd / da),
       count(*) filter (where qd < da), sum(qd), sum(da)
from r where da > 0 and qd > 0;

-- 7. By state (the foundation's state on its return).
--    Per-resident figures divide by the Census Bureau's Vintage 2024 estimates.
select state, count(*), sum(gs), sum(gc), percentile_cont(0.5) within group (order by gs)
from r group by state;

-- 8. First-time grantees, from funder_metrics (the same computation as the
--    profile pages; minimums in /funders/methodology). Each foundation's most
--    recent year with a grantee list — FY2024 for 10,420, FY2025 for 17,042,
--    FY2026 for 633 — because the metric needs that year plus prior years.
--    → 28,095 foundations · 287,540 of 1,014,304 grantees first-time (28.3%)
--    → median share 25% · first grants smaller than repeat grants at 16,086 (57%)
select count(*), sum(fm.new_grantees), sum(fm.latest_grantees),
       percentile_cont(0.5) within group (order by fm.new_grantee_share),
       count(*) filter (where fm.median_first_grant < fm.median_repeat_grant),
       percentile_cont(0.5) within group (order by fm.median_first_grant),
       percentile_cont(0.5) within group (order by fm.median_repeat_grant)
from r join funder_metrics fm on fm.funder_id = r.id
where fm.new_grantee_share is not null;

-- 9. What the universe leaves out.
--    → 117,684 FY2024 990-PF filers · 23,017 with no grants · 565 with a
--      reported total but no itemized list ($107,120,711)
--    → 14 patient-assistance foundations with $22,818,408,605
select count(*),
       count(*) filter (where gs = 0 and coalesce(rep, 0) = 0),
       count(*) filter (where gs = 0 and rep > 0), sum(rep) filter (where gs = 0 and rep > 0)
from (select pf_all.*, (select (financials->'2024'->>'grants_paid_reported')::numeric
                        from master_organizations where id = pf_all.id) rep from pf_all) x;
select count(*), sum(gs) from pf_all where id in (select id from pap) and gs > 0;

-- ══ Chapter 2: where the money goes (run read-only 2026-10-12) ══════════
-- Table: master_grants (one row per itemized grant on a return), joined to the
-- recipient's row in master_organizations when the grant is linked to one.
-- Same universe r as above. Results are frozen under "chapter2" in the JSON.
--
-- 10. Grants from the universe, FY2024, traced to a recipient organization.
--     → all: 1,588,202 grants, $105,929,192,181 (within 0.1% of query 1, which
--       sums each return's grant total rather than its grant rows)
--     → traced: 1,043,595 grants, $71,023,580,827 (67.0%), 220,956 recipients
--     → untraced: median grant $4,000
--
-- 11. Traced dollars by the recipient's cause and size.
--     Cause = first letter of the recipient's NTEE code (IRS Business Master
--     File); with no code, BMF foundation code 10 (church) = X, 11 (school) = B,
--     12–13 (hospital) = E; otherwise "not classified". Letters are grouped into
--     the NTEE's ten major groups. Universities = NTEE B40–B43.
--     Size = the recipient's total revenue on its FY2024 return, else FY2023,
--     else FY2022, and never less than the traced FY2024 grants it received (a
--     stale or misparsed revenue line otherwise puts a university in the
--     under-$500K band). Size figures leave out grants to other grantmakers (NTEE T).
--     → grantmakers (T): $17,321,603,774 (24.4%), of which one grant, Gates
--       Foundation Trust → Gates Foundation, was $8,096,000,000
--     → recipients under $1M: 61.8% of recipients with revenue on file, 7.1% of
--       their dollars · $100M or more: 1.4% of recipients, 31.9% of dollars
with g as materialized (
  select g.amount::numeric amt, g.grantee_organization_id gid
  from master_grants g join r on r.id = g.grantor_organization_id
  where g.fiscal_year = 2024 and g.amount > 0
),
recv as (select gid, sum(amt) received from g where gid is not null group by gid),
o as materialized (
  select o.id,
    upper(coalesce(nullif(o.ntee_code, ''), nullif(o.metadata->>'ntee_code', ''))) ntee,
    o.metadata->'irs_bmf'->>'foundation' bmf_f,
    coalesce(nullif(o.financials->'2024'->>'revenue', '')::numeric,
             nullif(o.financials->'2023'->>'revenue', '')::numeric,
             nullif(o.financials->'2022'->>'revenue', '')::numeric) rev
  from master_organizations o where o.id in (select distinct gid from g where gid is not null)
),
x as (
  select g.amt, g.gid,
    case when g.gid is null then '-'
         when o.ntee ~ '^[A-Z]' then left(o.ntee, 1)
         when o.bmf_f = '10' then 'X' when o.bmf_f = '11' then 'B' when o.bmf_f in ('12', '13') then 'E'
         else '?' end as letter,
    o.ntee ~ '^B4[0-3]' as university,
    case when g.gid is null then '-' when o.rev is null or o.rev <= 0 then 'unknown'
         when greatest(o.rev, recv.received) < 500000 then 'under $500K'
         when greatest(o.rev, recv.received) < 1000000 then '$500K–$1M'
         when greatest(o.rev, recv.received) < 5000000 then '$1M–$5M'
         when greatest(o.rev, recv.received) < 25000000 then '$5M–$25M'
         when greatest(o.rev, recv.received) < 100000000 then '$25M–$100M'
         when greatest(o.rev, recv.received) < 1000000000 then '$100M–$1B'
         else '$1B or more' end as band
  from g left join o on o.id = g.gid left join recv on recv.gid = g.gid
)
select letter, university, band, count(*), sum(amt), count(distinct gid),
       percentile_cont(0.5) within group (order by amt)
from x group by 1, 2, 3;