"""Reproduce the historical cost and funding tests. No estimated premiums.
Run with bundled Python; xlrd is needed only to read the original IRS file.
"""
import json
import html
from pathlib import Path
import openpyxl
import xlrd

ROOT = Path(__file__).parent
CMS = 'https://www.cms.gov/files/zip/nhe-tables.zip'
IRS = 'https://www.irs.gov/pub/irs-soi/23in12ms.xls'
CBO = 'https://www.cbo.gov/system/files/2020-12/56811-Single-Payer.pdf'
LANCET = 'https://pubmed.ncbi.nlm.nih.gov/32061298/'
FTC = 'https://www.ftc.gov/news-events/news/press-releases/2025/01/ftc-releases-second-interim-staff-report-prescription-drug-middlemen'
RAND = 'https://www.rand.org/content/dam/rand/pubs/research_reports/RRA1100/RRA1144-2-v2/RAND_RRA1144-2-v2.pdf'

def cms_table(prefix):
    return list(openpyxl.load_workbook(next((ROOT/'cms-tables').glob(prefix+'*')), data_only=True).active.values)

t = cms_table('Table 02 ')
def cost(row, year):
    return t[row-1][t[1].index(year)] * 1e9

categories = [(7,'Hospital care'),(9,'Physicians and clinical services'),(10,'Other professional services'),
              (11,'Dental services'),(12,'Other health, residential and personal care'),(13,'Home health'),
              (14,'Nursing facilities and continuing care'),(16,'Retail prescription drugs'),
              (17,'Durable medical equipment'),(18,'Other nondurable medical products')]
care_rows = [{'category':label,'source_excel_row':row,'2023':cost(row,2023),'2024':cost(row,2024)} for row,label in categories]
irs = xlrd.open_workbook(ROOT/'irs-2023-source.xls').sheet_by_index(0)
brackets = []
for row in range(9,28):
    brackets.append({'label':irs.cell_value(row,0),'returns':irs.cell_value(row,1),
                     'agi_dollars':irs.cell_value(row,2)*1000,'source_excel_row':row+1})

# Thresholds coincide with published bracket boundaries: no midpoint inference.
excess25 = sum(x['agi_dollars']-25000*x['returns'] for x in brackets if x['source_excel_row']>=16)
excess200 = sum(x['agi_dollars']-200000*x['returns'] for x in brackets if x['source_excel_row']>=22)
rate100 = 100*12/(65000-25000)
revenue100 = rate100*excess25
budgets = [1e12,2e12,3e12]
tests = [{'budget':b,'rate_above_25000':b/excess25,
          'monthly_at_65000':b/excess25*40000/12,
          'extra_rate_above_200000_to_keep_65000_at_100':(b-revenue100)/excess200} for b in budgets]
national_2023 = cost(4,2023)
care_2023 = cost(6,2023)
national_2024 = cost(4,2024)
target = 320e6*100*12
for test in tests:
    assert abs(test['rate_above_25000']*excess25-test['budget'])<.01
    assert abs(revenue100+test['extra_rate_above_200000_to_keep_65000_at_100']*excess200-test['budget'])<.01
assert abs(sum(x['2023'] for x in care_rows)-care_2023)<.21e9
assert abs(sum(x['2024'] for x in care_rows)-cost(6,2024))<.21e9
assert abs(sum(x['agi_dollars'] for x in brackets)-irs.cell_value(8,2)*1000)<5000
assert target == 384e9

result = {'as_of':'2026-10-16','analysis_year':2023,'latest_cost_year':2024,
 'scope':'Historical national spending and tax-return funding feasibility. Not a citizen-specific actuarial price model.',
 'sources':{'cms':CMS,'irs':IRS,'cbo':CBO,'lancet':LANCET,'ftc':FTC,'rand':RAND},
 'costs':care_rows,'national_2023':national_2023,'national_2024':national_2024,
 'personal_healthcare_2023':care_2023,'personal_healthcare_2024':cost(6,2024),
 'irs_brackets':brackets,'income_above_25000_per_return':excess25,'income_above_200000_per_return':excess200,
 'policy_test':{'allowance_per_return':25000,'base_rate':rate100,'revenue':revenue100},'budget_tests':tests,
 'flat_100_test':{'people_assumed':320000000,'annual_revenue':target,'share_of_2023_care_spending':target/care_2023},
 'formulas':{'excess_income':'sum(bracket AGI dollars - threshold * bracket return count), over brackets at or above threshold',
 'monthly_contribution':'rate * max(0, AGI - allowance) / 12',
 'revenue':'rate * aggregate excess income',
 'extra_high_income_rate':'(target budget - base revenue) / aggregate income above $200,000',
 'fund_budget':'care payments at negotiated prices + added use and benefits + fund operations + reserve addition + transition costs - committed outside funding'}}
(ROOT/'cost-pay-analysis.json').write_text(json.dumps(result,indent=2))

def dollars(x): return f'${x:,.0f}'
def billions(x): return f'${x/1e9:,.1f}B'
def trillions(x): return f'${x/1e12:,.3f}T'
def pct(x): return f'{x*100:.1f}%'
def link(url,label): return f'<a href="{html.escape(url)}">{html.escape(label)}</a>'
def table(headers,rows):
    return '<div class="scroll"><table><thead><tr>'+''.join('<th>'+h+'</th>' for h in headers)+'</tr></thead><tbody>'+''.join('<tr>'+''.join('<td>'+str(c)+'</td>' for c in r)+'</tr>' for r in rows)+'</tbody></table></div>'

cost_table = table(['Care category','2023 actual','2024 actual'],[(r['category'],billions(r['2023']),billions(r['2024'])) for r in care_rows]+[('<strong>Total personal healthcare</strong>',trillions(care_2023),trillions(cost(6,2024)))])
other_table = table(['Other national spending','2023','2024'],[(label,billions(cost(row,2023)),billions(cost(row,2024))) for row,label in [(19,'Government administration'),(20,'Nonmedical insurance expenditures'),(21,'Government public health'),(22,'Investment')]])
fund_table = table(['Annual budget to fund*','Rate on AGI above $25,000','Monthly at $65,000 AGI'],[(trillions(r['budget']),pct(r['rate_above_25000']),dollars(r['monthly_at_65000'])) for r in tests])
progressive_table = table(['Annual budget to fund*','Additional rate on income above $200,000','Total marginal contribution above $200,000'],[(trillions(r['budget']),pct(r['extra_rate_above_200000_to_keep_65000_at_100']),pct(.03+r['extra_rate_above_200000_to_keep_65000_at_100'])) for r in tests])
world=json.loads((ROOT/'international-health-costs-2023.json').read_text())['rows']
peers=[next(r for r in world if r['iso3']==iso) for iso in ['USA','ESP','ITA','JPN','GBR','FRA','CAN','DEU']]
peer_table=table(['Country','Total system cost / month, PPP','Direct out-of-pocket / month, nominal USD'],[(r['country'],dollars(r['ppp_monthly']),dollars(r['oop_monthly'])) for r in peers])
income_table=table(['2023 AGI bracket','Tax returns','Aggregate AGI'],[(html.escape(r['label']),f"{r['returns']:,.0f}",billions(r['agi_dollars'])) for r in brackets])

body=f'''<!doctype html><html lang="en"><meta charset="utf-8"><meta name="viewport" content="width=device-width, initial-scale=1"><title>AmericanHealthFund — Cost and funding analysis</title>
<style>body{{margin:0;color:#132c48;background:#f3f6fa;font:17px/1.65 Arial,sans-serif}}main{{max-width:1080px;margin:auto;padding:45px 24px 80px}}header{{border-top:8px solid #ce1731;padding:26px 0}}h1{{font-size:44px;line-height:1.1;margin:12px 0 20px}}h2{{font-size:27px;line-height:1.25;margin-top:0}}section{{background:white;padding:30px;margin:22px 0;border:1px solid #d6e0ec}}p{{max-width:90ch}}.eyebrow{{font-size:13px;letter-spacing:1.5px;font-weight:bold}}.callout{{border-left:5px solid #ce1731;background:#eaf0f9;padding:18px 22px}}.small{{font-size:14px;color:#4b5d71}}table{{border-collapse:collapse;width:100%;font-size:15px}}th,td{{text-align:right;padding:12px;border-bottom:1px solid #d9e1ec;vertical-align:top}}th:first-child,td:first-child{{text-align:left}}th{{background:#eaf0f9}}a{{color:#175ba1}}.scroll{{overflow-x:auto}}code{{white-space:normal;background:#eff3f9;padding:3px}}li{{margin:10px 0}}@media(max-width:600px){{h1{{font-size:34px}}section{{padding:20px}}main{{padding:20px 12px}}}}@media print{{body{{background:white}}section{{break-inside:avoid}}main{{padding:0}}}}</style>
<main><header><div class="eyebrow">AMERICANHEALTHFUND · RESEARCH · OCTOBER 16, 2026</div><h1>What would care cost?<br>Who would pay how much?</h1><p>A reproducible historical cost and funding analysis. Uses 2023 for the income/cost comparison, with 2024 spending shown separately. No fabricated 2026 actuals, negotiated prices, or personal premiums.</p></header>
<section><h2>The finding</h2><p>Removing unnecessary spending can reduce the budget. Making contributions affordable also requires redistributing that budget by ability to pay. These are two separate calculations.</p><div class="callout"><strong>$100/month for a person earning $65,000 is a funding policy we can test. $100/month on average for everyone supplies only $384 billion a year under the proposed 320-million-member assumption.</strong> The evidence reviewed does not establish that this can buy comprehensive care nationwide.</div><p>The analysis below tests the idea without treating today’s prices as inevitable or assuming that all spending above a desired target can disappear.</p></section>
<section><h2>1. Separate care from insurance overhead</h2>{cost_table}<p class="small">Source: {link(CMS,'CMS NHE Tables, Table 2')}. Nominal dollars. Subcategories can differ from the published total by rounding. Hospital and provider payments include their own overhead and margins; “personal healthcare” does not mean every dollar is efficient or clinically necessary. Hospital-administered drugs are included in provider categories, not the retail-drug line.</p>{other_table}<p>In 2024, national spending totaled {trillions(national_2024)}. The care subtotal was {trillions(cost(6,2024))}; the remaining categories are shown above. Removing a category from the fund’s scope is not automatically a saving for society. Public health and capital still need funding somewhere.</p><p>Nonmedical insurance expenditures include more than profits. Eliminating that entire category is an unrealistic upper-bound exercise because a replacement fund still needs claims processing, systems, staff and advocacy. Provider billing savings are a separate opportunity within care payments.</p></section>
<section><h2>2. What evidence supports savings?</h2><ul>
<li><strong>Negotiating provider prices:</strong> RAND found that sampled private plans paid about 254% of Medicare prices for hospital services in 2022. This supports investigating large reductions in commercial prices; it does not imply a 61% cut to all national spending. Medicare and Medicaid already pay different rates. {link(RAND,'RAND report')}.</li>
<li><strong>Drug middlemen:</strong> FTC documented over $7.3 billion above estimated acquisition cost on the specialty generics it examined during 2017–2022. This is evidence of a real pricing problem, not a national annual savings estimate. Acquisition-cost markups also are not identical to net profit. {link(FTC,'FTC findings')}.</li>
<li><strong>Whole-system reform:</strong> CBO’s five illustrative systems changed projected 2030 spending from a $743 billion decrease to a $290 billion increase against a $6.631 trillion baseline. Lower payment rates and administration reduced spending; greater use raised it. Its option including expanded long-term services and supports increased total spending. These are 2020 projections for government systems, not quotes for a private fund. {link(CBO,'CBO, Exhibit 1-1, printed page 12')}.</li>
<li><strong>A favorable universal-care study:</strong> Galvani and colleagues estimated about 13% net savings while expanding coverage. Its financing includes existing government allocations as well as employer/household money. That financing cannot simply be carried over to a member-only private fund. {link(LANCET,'The Lancet, 2020')}.</li></ul>
<p><strong>Accounting rule:</strong> do not add insurer profit, shareholder dividends, executive salaries, administrative savings and PBM markups as if they were all independent. Some are nested in the same spending. Savings require identifying a payment once, reducing it once, and retaining the cost of necessary replacement services.</p></section>
<section><h2>3. Test the $100 average</h2><p><code>320,000,000 people × $100 × 12 = $384,000,000,000</code></p><p>That equals {pct(target/care_2023)} of 2023 personal healthcare spending. Relative to that historical nationwide care subtotal, reaching $384 billion would mean a {pct(1-target/care_2023)} reduction, before replacement fund operations, reserves or newly met needs. This is a scale comparison, not an actuarial estimate for citizens: the historical spending includes noncitizens, and 320 million is the proposed enrollment assumption.</p><p>Even an arithmetic stress test cutting all 2023 care payments in half leaves {trillions(care_2023*.5)}. That 50% cut is <strong>not a forecast</strong>; it demonstrates how much more ambitious the $384 billion total is. Current spending is not a minimum-cost floor, but the reviewed evidence does not support a reduction of roughly nine-tenths.</p></section>
<section><h2>4. Test payments against actual income</h2><p>The IRS reports about {trillions(irs.cell_value(8,2)*1000)} in 2023 adjusted gross income across 160.6 million returns. Using published brackets, income above a hypothetical $25,000 allowance per return totals <strong>{trillions(excess25)}</strong>.</p><p><strong>Policy experiment, not an adopted fund rule:</strong> exempt the first $25,000 of AGI on each return, then apply a single contribution rate to the rest. The allowance is a transparent choice for this test; it is not a measured cost or a recommendation. Its effective burden rises with income.</p><p><code>Annual contribution = rate × max(0, AGI − $25,000)</code><br><code>Annual revenue = rate × {trillions(excess25)}</code></p>{fund_table}<p class="small">*These are funding requirements to test, not estimates that care can be supplied for $1T, $2T or $3T. Dollar figures round to the nearest dollar. All calculations use 2023 reported income with full participation and collection.</p><p>A 3% rate produces exactly $100/month at $65,000 AGI. Applied to the IRS distribution it raises <strong>{billions(revenue100)} a year</strong>. That is a demonstrated revenue result under this policy experiment, not enough evidence to promise coverage at that price.</p><p class="small">Source: {link(IRS,'IRS SOI Table 1.2, tax year 2023, all returns')}. A return can represent one or two adults and dependents; it is not an individual member. IRS filers include noncitizens and omit nonfilers. AGI is not salary or all economic income. This is a financing feasibility proxy, not the final citizen-only, individual, age-and-income model.</p></section>
<section><h2>5. Can $65,000 earners stay at $100/month?</h2><p>Yes, arithmetically, by collecting more elsewhere. To make the tradeoff visible, keep the 3% base contribution and add another marginal rate only on income above $200,000 per tax return. The IRS distribution contains <strong>{trillions(excess200)}</strong> above that threshold.</p><p><code>Extra rate = (budget − {billions(revenue100)}) ÷ {trillions(excess200)}</code></p>{progressive_table}<p>These are fund contributions <strong>in addition to existing taxes</strong>, unless laws and financing arrangements change. The example leaves $65,000 filers at $100/month, but larger budgets require substantial contributions at higher incomes. A voluntary fund also cannot assume those contributors will all join and remain. There is no invented participation discount here; the table explicitly assumes 100% collection.</p><p>Other designs can distribute the burden differently. They cannot eliminate the need for total receipts to cover the budget. A final individual model needs citizen-level age and income data, family-income treatment, and explicit affordability rules.</p></section>
<section><h2>6. International evidence: much lower costs are real</h2>{peer_table}<p class="small">2023 WHO Global Health Expenditure Database via World Bank WDI. Total spending includes public and private financing. PPP international dollars adjust for general purchasing power; they are not nominal U.S. dollars available to a fund. Out-of-pocket excludes taxes and insurance premiums. These columns measure different things and should not be subtracted. {link('https://data.worldbank.org/indicator/SH.XPD.CHEX.PP.CD','Total spending definition')} · {link('https://data.worldbank.org/indicator/SH.XPD.OOPC.PC.CD','Out-of-pocket definition')}.</p><p>Several countries deliver care for much less than the United States. Their results are evidence that the U.S. spending level is not inevitable. They are not proof that U.S. wages, service capacity and the fund’s broader benefits can be purchased for $100 per person monthly. A small bill at the doctor excludes the taxes and compulsory contributions financing that system.</p><p>{link('international-health-comparison.html','Open the full country comparison')}</p></section>
<section><h2>7. A fund budget must reconcile</h2><p><code>Required member revenue = care at negotiated prices + added use/benefits + fund operations + reserve addition + transition costs − committed outside funding</code></p><p>This analysis assumes no automatic employer payments, tax redirection or transfer of Medicare/Medicaid money. Those are not revenues available to a private fund merely because someone enrolls.</p><ul><li><strong>Care prices:</strong> obtain procedure-level utilization and contracted prices, including geographic and age differences. Use cost reports to test provider viability rather than assuming current prices equal necessary costs.</li><li><strong>Benefits and access:</strong> price additional dental, vision, fertility, mental-health and long-term care, plus care currently deferred because of cost. Do not count unpaid family caregiving as a free substitute for promised paid care.</li><li><strong>Operations:</strong> budget claims, eligibility, drug procurement, logistics, legal advocacy, fraud controls and staff explicitly.</li><li><strong>Reserves:</strong> distinguish a one-time target balance from annual replenishment. Do not charge the full reserve target every year.</li><li><strong>Funding:</strong> calibrate the individual age/income formula to actual eligible members. Age weights redistribute the budget; they do not create savings. Do not use diagnoses or preexisting conditions to surcharge members.</li><li><strong>Start-up:</strong> test enrollment mix, early high-cost claims, capital and collection risk separately from a hypothetical nationwide steady state.</li></ul><p><strong>What this establishes:</strong> a sourced spending baseline, independently documented savings opportunities, and contribution formulas that reconcile to specified budgets. It does not establish a viable 2026 fund premium. That uncertainty belongs in the model; another arbitrary discounted dollar figure would repeat the original mistake.</p></section>
<section><h2>Audit trail</h2><p>{link('cost-pay-analysis.json','Download inputs, outputs and formulas (JSON)')} · {link('build_cost_analysis.py','Reproduction script')} · {link(CMS,'Original CMS workbook archive')} · {link(IRS,'Original IRS workbook')}</p><p>The script reads the source tables and tests budget/revenue reconciliation. Thresholds align with IRS bracket boundaries, so it does not estimate income using bracket midpoints. All money in the IRS source is multiplied by 1,000 as specified in its header. Component rounding is retained.</p><details><summary>Show the IRS input distribution</summary>{income_table}</details><p class="small">Prepared October 16, 2026. Historical analysis in the dollars of each stated year. No inflation escalation to 2026 or actuarial certification is claimed.</p></section></main></html>'''
(ROOT/'cost-pay-analysis.html').write_text(body)
print(json.dumps({'status':'all arithmetic checks passed','income_base':excess25,'base_revenue':revenue100,'tests':tests},indent=2))
