Loan Pricing Calculator — Methodology & Audit

The Loan Pricing Calculator answers one question: given today’s market expectations for interest rates, what fixed rate is economically equivalent to a given floating rate — or, run in reverse, what floating spread is equivalent to a given fixed rate — and what either option earns the bank. It is a pricing and analysis tool. It is not a note, a rate lock, an amortization disclosure, or a payoff statement, and it does not price credit risk.

Data sources

The curve date used for a given quote is printed beside the Price Loan button and again on the results. Quotes produced on different days differ because the curve moves; reproducing a past quote requires the curve as of that date.

Rounding and precision

All internal arithmetic is carried in full double precision. Rounding occurs only at display: rates to three decimals, dollars to the cent (whole dollars in the income statement). A hand-built Excel model will therefore agree with the tool to within a cent or a tenth of a basis point — not necessarily to the last displayed digit.

A forward curve is the path of future short-term interest rates implied by today’s trading in SOFR futures and interest-rate swaps. It is not a forecast by this tool or by anyone else. It is the rate at which the market will presently contract to lend or borrow at each future date, which makes it the one rate path on which a fixed and a floating loan can be exchanged without either side giving up value. That property is what the entire tool rests on.

The source publishes a projected reset rate for each index on every future business day, roughly twenty years out. The tool needs one rate per loan month:

So forward(1) is today’s rate, forward(2) is the projected reset one month out, and so on. The curve is sampled, never interpolated — every monthly figure is an actual published reset, which keeps the audit trail to a single lookup per month.

Discount factors

Present values are discounted on the index forwards themselves, not on the all-in loan rate. This is the single most important detail when rebuilding the model: discounting at the loan rate would embed the credit spread in the discount factor and distort the comparison the tool exists to make.

DF(m) discounts a cashflow received at the end of loan month m. The divisor uses 1200 because the rate is an annual percentage and the period is one month (percent ÷ 100 ÷ 12).

Which index

1M Term SOFR and Daily SOFR are read directly from the published curve. WSJ Prime has no futures market — it is an administered rate that banks set, and since the mid-1990s it has moved in lockstep at the fed funds target plus 300 basis points. Its forward path is constructed by the source from the fed funds forward curve plus that administered spread, which is the standard dealer construction.

All-in rate

Floor and ceiling apply to the all-in rate, not to the index alone. A negative spread is permitted, which is how Prime-minus pricing is entered.

Interest accrual

Under Actual/360, days(m) is the actual number of calendar days in loan month m counted from the curve date (month-ends roll to the last valid day; leap days count). Actual/360 accrues about 365/360 = 1.39% more interest per year than 30/360, which is why it is the default — it is how most commercial notes are written.

Scheduled payment

Phase order: draw, then interest-only, then amortization

The amortization clock starts at the first amortizing payment, not at origination. A 60-month loan on a 300-month amortization with 24 interest-only months amortizes over a full 300 months beginning in month 25 — it does not compress into the remaining 276 months. Some banks paper it the other way; if yours does, the payment and balloon will differ from this tool and the difference is the convention, not an error.

The balloon is the balance remaining after the final scheduled payment. Floating loans re-amortize every month: each month’s payment is recomputed on the then-current balance, remaining amortization, and that month’s rate, which is standard for monthly-reset commercial loans.

The fixed quote is not a markup, a survey, or a judgment call. It is solved: the tool finds the single fixed rate whose cashflows have the same present value, on the same curve, as the projected floating cashflows.

Both legs use the same loan amount, term, amortization, interest-only period, draw schedule, accrual basis, and discount factors. Only the rate differs. Because both structures share the same disbursement schedule, the funding legs cancel and the comparison stays apples-to-apples.

PV is monotone in the rate (a higher fixed rate always means a higher present value), so the solution is unique. The tool finds it by secant iteration — starting from the average forward plus the spread and converging in a handful of steps, to a tolerance of 1×10^-7 of the loan amount. In Excel the same answer comes from Goal Seek or Solver, setting the PV difference to zero by changing the fixed rate cell.

What the fixed-equivalent rate means — and does not mean

The result is the risk-neutral indifference rate: a party who is fully hedged, or indifferent to rate risk, would be equally content with either option at inception. It is not a utility-based or preference-based price. It contains no risk-aversion parameter, so every user who accepts the same curve gets the same answer — which is precisely what makes it auditable.

A genuinely risk-averse borrower may rationally pay above this rate for the certainty a fixed rate provides, and dealers typically charge option value on top of a floor or ceiling beyond the intrinsic effect this tool captures. Those premiums are pricing decisions layered on top of the neutral base computed here; the tool deliberately does not assume them.

Solving in reverse — entering a fixed rate

The same equation can be solved for the other unknown. Selecting “I know the fixed rate” takes the all-in note rate as the input and solves for the spread over the selected index:

Present value rises monotonically with the spread — every month’s payment increases — so the solution is unique, and the relationship is very close to one-for-one: adding 100 bp to the spread raises the implied fixed rate by almost exactly 100 bp. The same secant iteration is used, and the two directions are exact inverses of one another. Entering a fixed rate below the projected index produces a negative spread, which is displayed as index minus the margin.

Reported averages

These are balance-weighted, not simple averages, so a draw period or an amortizing balance is properly reflected — months carrying more money count for more. The average index rate is reused in section 8 as the marginal (wholesale) funding proxy.

Example A is small enough to check on paper and demonstrates the entire present-value mechanism. Example B is a conventional commercial loan and confirms the flat-curve identity. Both were computed by hand and then verified against the live tool; the agreement is noted beneath each.

Example A — $100,000, 3 months, interest-only, balloon at maturity

Index forwards of 4.00%, 5.00%, 6.00% for months 1–3; spread 200 bp; 30/360 accrual. All-in rates are therefore 6.00%, 7.00%, 8.00%.

Interest is 100,000 × rate/100 × 1/12. Discount factors chain the index forwards: DF(1) = 1/(1+0.04/12), DF(2) = DF(1)/(1+0.05/12), DF(3) = DF(2)/(1+0.06/12).

Because this loan is interest-only, the fixed equivalent has a closed form — no Goal Seek needed:

Example B — $1,000,000, 60-month term, 300-month amortization, flat 4.00% curve, 250 bp spread, 30/360

Building the general case in Excel

1. Column A: loan month, 1 to term.
2. Column B: index forward for that month, read from the curve per section 2.
3. Column C: all-in rate — =MIN(MAX(B2+spread,floor),ceiling), or simply =B2+spread with no collar.
4. Column D: days in the month for Actual/360 — =EOMONTH(start,A2)-EOMONTH(start,A2-1) adjusted to your payment dates; use 30 for 30/360.
5. Column E: opening balance — the prior row’s closing balance.
6. Column F: interest — =E2*C2/100*D2/360 (or *1/12 for 30/360).
7. Column G: payment — =PMT(C2/1200, remaining_amort, -E2), or =F2 during a draw or interest-only month.
8. Column H: principal — =MAX(MIN(G2-F2,E2),0); closing balance =E2-H2.
9. Column I: discount factor — =I1/(1+B2/1200), seeded at 1.
10. Column J: PV — =G2*I2, plus the balloon times the final discount factor.
11. Solve for the fixed rate with Goal Seek: set (PV of the fixed column minus PV of the floating column) to zero by changing the fixed-rate cell.

The scenario table asks what the floating option would cost in total interest if rates ran a stated amount above or below the forward curve, holding the fixed option fixed.

The shifted index is floored at zero, and the collar is re-applied after the shift — which is why a collared loan shows scenario averages that stop at the floor and ceiling rather than continuing to move. The 0 bp row is the base case and will differ from the fixed option only by rounding, since that is precisely what the PV match sets equal.

Breakeven

Reported to the nearest basis point. If no crossing exists within ±500 bp — which happens when a tight collar caps the outcome — the breakeven line is omitted rather than reported inaccurately.

Entering an FDIC certificate number pulls that institution’s quarterly Call Report data and measures how its cost of funds has historically responded to SOFR. Nothing is stored; the data is public.

Quarterly cost of funds

This is cost of funding earning assets — total interest expense spread over earning assets — annualized by multiplying the quarterly figure by four. Quarters with missing or negative derived expense (which occurs around restatements and mergers) are skipped rather than estimated.

Funding betas

SOFR is averaged over each calendar quarter, aligned to the bank’s quarters, and the changes are regressed — levels would be spurious:

The combined beta is the share of a SOFR move that eventually reaches the bank’s cost of funds; the repricing lag is the portion of it that arrives the following quarter rather than immediately. The same regression is then run separately on rising and falling quarters:

Separating the regimes matters because deposits famously reprice asymmetrically — slowly on the way up, and often more slowly still on the way down. SOFR history begins in April 2018, so the usable window covers the 2022–23 hiking cycle and the subsequent easing, which is exactly the variation the regression needs.

Projecting cost of funds

The projection starts from the bank’s most recently reported cost of funds and walks it forward along the forward curve. Both the projected cost of funds and the forward SOFR path are plotted as dashed lines continuing their historical series, with a divider marking where reported history ends.

The income statement expresses the loan as a miniature bank: it earns interest and fees, pays for its funding, absorbs a share of overhead, pays tax, and returns what is left on the equity allocated to it. Every figure is an annual average over the loan term.

The two funding views

Every column appears twice because the answer depends entirely on which funding cost you charge the loan:

• Blended cost of funds — the bank’s projected all-in cost from section 7, averaged over the quarters spanning the loan term. This answers: what does this loan do to my reported net interest margin?
• Marginal (match-funded) funding — what it actually costs to fund this loan at the margin, which differs by option because the funding differs. This answers: what does this loan earn over what the next dollar of funding actually costs?

Match funding — why the two options are charged different costs

A bank funding a floating loan rolls short-term money and reprices alongside the loan, so its cost is the balance-weighted average of the index forwards from section 4. A bank funding a fixed loan buys term money to lock the margin and avoid taking rate risk against the loan, so its cost is the par swap rate for that loan’s own amortization schedule — the section 4 solve run at a zero spread.

Funding is matched to the loan’s whole cashflow profile rather than to a single point on the curve: every month’s outstanding balance is funded at that month’s rate, so amortization, interest-only periods, draws and the balloon are all reflected without choosing a maturity to look up. On a flat curve the two costs are identical. On an upward-sloping curve term funding prices below rolled short money; on an inverted curve it prices above. Any floor or ceiling on the loan is excluded from both figures — a rate cap on the asset does not change what the bank’s own liabilities cost.

Assumptions and their limits

• Overhead via the efficiency ratio applies the bank’s reported institution-wide ratio to this loan’s net revenue. It assumes the loan is as efficient to originate and service as the average dollar of the bank’s revenue — a reasonable default, but a small, hands-on credit consumes more and a large, clean renewal consumes less.
• Capital ratio is a flat user input, defaulting to 10%. It is not risk-weighted; a true regulatory allocation would vary with the exposure’s risk weight.
• Tax is a flat effective rate, defaulting to 23%. It does not reflect state taxes, tax-exempt income, or credits that move a particular bank’s effective rate.
• No credit cost. Neither provision nor charge-offs appear anywhere. A complete return measure would deduct expected loss, which for most commercial credits is a meaningful share of the spread. These ROE figures are therefore before credit cost and will overstate risk-adjusted return.

• It does not price credit risk. The spread is an input, not an output. The tool will not tell you whether 275 basis points is adequate compensation for a given borrower.
• It does not value optionality. A floor or ceiling is applied to projected cashflows at its intrinsic effect only. A dealer pricing the same collar would add option value derived from rate volatility, which this tool does not model. The same applies to prepayment rights: a borrower’s option to prepay a fixed-rate loan has value that is not priced here.
• It does not model actual draw timing. Construction draws are assumed straight-line. Real draws are lumpy and typically S-curved; the difference moves the answer by basis points, not percentage points, but it is an assumption.
• It assumes the loan is held to maturity as scheduled, with no prepayment, no modification, and no default.
• It is not a forecast. The forward curve is the market’s current pricing of future rates. Realized rates will differ from it — reliably so. The scenario table exists because of this, not in spite of it.
• It is not an official document. Results are indicative projections and do not constitute an offer, commitment, rate lock, amortization disclosure, or payoff statement. Actual interest and payments will vary with the terms of the executed note, including payment dates, day-count and rounding conventions, draw timing, prepayments, and actual index resets.