Who this is for: independent mortgage brokers and loan officers who run 5-15 borrower scenarios per deal and burn hours on re-quoting.
The pitch in one line: upload your scenario spreadsheet → get a borrower-facing calculator that runs every combination on its own. Same PITI logic, same lender overlays, same PMI breakpoints — just self-serve.
What your scenario worksheet looks like today
Every LO has a version of Scenario_Calc_2024.xlsx. The tabs usually go:
| Tab | What it does |
|---|---|
| Inputs | Purchase price, down payment $/%, loan term, note rate, points (or credits), property tax rate, hazard insurance, HOA, mortgage insurance type, occupancy, credit score band |
| Rate Sheet | Lender base rate by FICO × LTV grid (this is the one you re-paste from the wholesale daily PDF) |
| LLPA | Loan-Level Price Adjustments — FICO/LTV/occupancy adders in 1/8-point increments |
| PMI Grid | BPMI cost (%/yr of loan) by FICO × LTV. Drops off at 80% LTV. |
| PMT | =PMT(rate/12, term*12, -loan_amount) |
| Escrow | (tax_rate × price + hazard + HOA) / 12 |
| PITI | PMT + escrow + PMI |
| Cash to Close | down + closing costs + prepaids - lender credit |
| DTI | (PITI + other debts) / gross monthly income |
| APR | TILA-style blended APR |
You build the borrower a "Scenario A vs B vs C" sheet by duplicating the inputs tab, changing two numbers, copy-paste into an email. They reply "what if 25% down instead of 20%?" and you do it again. And again. Every deal is 8-12 rebuilds before they sign an LOI.
Half of borrowers never come back because the second scenario sat in your inbox for 4 hours.
What CalcForms gives you instead
Easiest path: drop your existing scenario worksheet into Claude
If you already have Scenario_Calc_2024.xlsx (and you do), drag it into a Claude chat with the CalcForms connector and say:
Build this as a borrower-facing CalcForms scenario tool. The borrower sees: purchase price, down payment %, loan term, FICO band, property tax %, hazard insurance, HOA, occupancy. Hide my internal markup, YSP, and LLPA grid — but use them in the rate calculation. Output PITI, cash to close, DTI (if they enter income), and total interest over the loan.
Claude reads every tab: identifies that the Rate Sheet and LLPA are lookup tables (FICO × LTV grids become lookup_tables with composite keys), the PMI grid becomes a 2-D LOOKUP, the PMT Excel formula gets translated into the CalcForms equivalent (loan * rate_monthly / (1 - (1 + rate_monthly)^-n)), and the escrow + PITI cells become straight calculations. The builder UI appears in the chat. You spot-check by entering a recent borrower's numbers — does it produce the same PITI as your old worksheet? If yes, ship it. If a lender overlay's off (e.g. "the 760+ FICO discount should be 0.125, not 0.25"), tell Claude and it updates the table inline.
Time from "drag the .xlsx" to "deployed URL": 15 minutes, most of it spot-checking against your last 3 closings.
From-scratch alternative
If you'd rather start clean (no LLPA, just a clean quick-quote):
Build me a borrower scenario calculator with:
- Purchase price (number, required)
- Down payment % (dropdown: 3, 5, 10, 15, 20, 25, 30)
- Loan term (dropdown: 15, 20, 30)
- FICO band (dropdown: 760+, 740-759, 720-739, 700-719, 680-699, 660-679)
- Occupancy (dropdown: Primary, Second home, Investment)
- Property tax rate (% of price, number, default 1.2)
- Annual hazard insurance (number, default 1500)
- Monthly HOA (number, default 0)
- Gross monthly income (number, optional — for DTI)
Rate sheet (30-yr base by FICO): 760+ = 6.50%, 740-759 = 6.625%, 720-739 = 6.75%, 700-719 = 7.00%, 680-699 = 7.375%, 660-679 = 7.75%. 15-yr is base - 0.625%. Investment adder +0.875%. Second home +0.25%.
PMI (BPMI annual %, when LTV > 80%): LTV 95% × FICO 760+ = 0.55%, 740 = 0.62%, 720 = 0.78%, 700 = 0.94%, 680 = 1.05%. Drop to 0 when LTV ≤ 80%.
Calculations:
- Loan amount = price × (1 - dp_pct/100)
- LTV = (1 - dp_pct/100) × 100
- Monthly rate = rate / 1200
- P&I = loan × monthly_rate / (1 - (1 + monthly_rate)^(-term*12))
- Monthly taxes = price × tax_rate / 100 / 12
- Monthly hazard = hazard / 12
- Monthly PMI = loan × pmi_annual / 100 / 12 (if LTV > 80)
- PITI = P&I + taxes + hazard + HOA + PMI
- Cash to close = price × dp_pct/100 + 8500 (rough closing cost stub)
- DTI = PITI / monthly_income × 100 (if income provided)
- Total interest = P&I × term × 12 - loan
8 minutes later the calculator is live. You text the borrower the URL: "play with it, send me a screenshot of the version you like."
They run 12 scenarios in 4 minutes (instead of 12 emails to you over 3 days). They text back: "can we do 20% down on the 30-year, $580K price, looks like PITI is $4,180 — does that work for the pre-approval letter?". You write the pre-approval. Deal moves.
The outcome
| Email-the-spreadsheet workflow | CalcForms self-serve scenarios | |
|---|---|---|
| Time per borrower scenario | 8-15 min (rebuild + email) | 4 seconds (they type into your URL) |
| Scenarios run per deal | 2-3 (more is rude to ask for) | 12+ (they explore on their own) |
| Re-engagement rate after scenario | ~50% (got distracted, lost momentum) | ~85% (they're already engaged — they built the scenario) |
| Time to pre-approval after first call | 2-4 days | Same day |
| Co-borrower / spouse alignment | "Let me forward this to my wife" → ghost | Both look at the URL together |
The wedge: borrowers do the scenario work themselves, in the 10pm window when they're actually shopping, not the 11am window when you're answering them on a 4-hour SLA.
Compliance note (important)
CalcForms outputs scenarios, not Loan Estimates. The disclosure stack you owe under TRID still happens in your LOS — this is for the "what if" conversation that precedes an application. Add a footer disclaimer in the form description: "Illustrative scenarios only — not a commitment to lend or an APR disclosure. Subject to underwriting and credit approval." You can paste that into the form's description field at build time.
Try it
Reference example (different industry, same engine): forms.badabingapp.cc/f/commercial-underwriting.
Open Claude with the CalcForms MCP connector, drag your scenario .xlsx, ship the borrower a URL by lunch. The fastest broker we've shipped this for cut their re-quote time from 9 hours/week to 40 minutes and credits it for closing 2 extra units in the following month.