Term loans calculator: build a sheet that tests a payment

A term loans calculator can hide its assumptions, so check a lender's payment by hand before you accept it. A spreadsheet built from the loan documents shows what drives the cost, and the whole thing takes about ten minutes to build.

Name the variables, test whether the payment reconciles, find the interest basis, place each charge, then run the four cost lines. Keep the same symbols for every offer so the inputs stay comparable. That is worth more than a widget, because you end up understanding which input is doing the work.

What to take away

Two quantities decide everythingwhat lands in your account, and everything that leaves it.
A quoted rate is one input among several, and it means different things on different cost bases.
A payment that does not reconcile with the stated balance, rate and term is a question for the lender before you sign.
Build the sheet once, then reuse it with the same symbols for every offer.

Take eight variables off the documents

Take these off the documents, not off a phone call or a summary email. Eight symbols cover a scheduled term-loan offer, and N, the net cash that lands, can differ from A, the amount approved.

Variables to take off documents

A: amount approved
U: fees taken out of advance
N: net cash that lands
P: scheduled payment
n: number of scheduled payments
m: payments per year
R: recurring charges over term
E: charges at or after end

Symbol / What it is

A: The amount approved
U: Fees taken out of the advance before it reaches you
N: The net cash that actually lands, so N = A minus U
P: The scheduled payment
n: The number of scheduled payments
m: Payments per year, so the term in years is n divided by m
R: Recurring charges not included in P, over the whole term
E: Anything payable at or after the end, including exit and termination charges

If a fee is added to the balance rather than deducted from the advance, it does not reduce N. It raises what you owe instead, and it will show up inside P. Ask the lender which of the two applies to each fee.

Does the quoted payment reconcile?

A payment either reconciles with the lender's own balance, rate and term, or something is not what you were told. On an ordinary amortizing loan, with B as the balance financed and i as the periodic rate, meaning the annual rate divided by m, the payment follows from one identity.

Reconcile the payment

Does the payment reconcile with B, i, n?

Yes

payment matches the offer

fee inside balance, wrong rate, or not amortising

P = B times i, divided by (1 minus (1 + i) to the power of minus n)

The CFPB's explanation of amortization says lenders use a standard formula to calculate the monthly payment. It allows for just the right amount to go to interest and principal, to pay off the loan precisely at the end of the term.

Reconciliation. Put the lender's own B, i and n into the identity. In Excel or Google Sheets the same test is =PMT(i, n, -B). If the payment that comes out is not the payment on the offer, something is not what you were told. Either a fee is inside the balance, the rate is not what you assumed, or the loan is not amortizing in the ordinary way.

Ask which. A payment that will not reconcile is one of the term loans mistakes that costs the most later.

Solving backwards. Given P, n and B, there is no tidy formula for i. A spreadsheet finds it by iteration. =RATE(n, -P, B) returns i for one period, and multiplying by m gives the annual figure. Use it when you are handed a payment and a term and no rate at all.

Which basis is the quoted rate on?

A quoted rate means different things on different cost bases. On an amortizing loan, interest accrues on what is still outstanding. The balance falls, so the interest charge falls with it.

Amortising vs flat basis

Amortising

Interest base: Outstanding balance
Balance falls: Yes, interest falls
Same quoted rate: Lower C
Longer term: Gap stable

Flat or add-on

Interest base: Original balance
Balance falls: No, charge fixed
Same quoted rate: Materially larger C
Longer term: Gap widens

On a flat or add-on basis, the charge is computed on the original balance for the whole term and then divided across the payments. You keep paying on money you have already repaid.

A quoted figure that looks identical on both bases produces a materially larger C on the second one, and the gap widens the longer the term runs. So before you compare two quoted rates, establish which basis each one uses.

Then ignore both numbers and compare c, because c does not care how the offer describes itself. The behavior of an amortizing schedule over time is set out in term loans.

Where each charge lands in the algebra

Placement matters more than size. A charge that reduces N raises c twice over, because you pay for money you never received. Work out which bucket each item falls into and ask the lender to confirm it in writing.

Where each charge lands

Deducted from the advance at fundingreduces N.
Added to the balance at closingraises B, and therefore raises P.
Charged periodically and billed separatelygoes into R.
Charged only on early settlement or at the endgoes into E.

Where each charge lands

Deducted from advancereduces N
Added to balanceraises B and P
Billed periodicallygoes into R
Charged at endgoes into E

Four lines that cost the whole offer

Use these four lines for a scheduled term-loan offer. Here, R is the total of separately billed periodic charges, E is any charge due at the end of the term, and N is the net cash received.

Total cash paid over the scheduled term = nP + R + E.
Total cost in dollars = nP + R + E − N.
Cost per dollar received = (nP + R + E − N) ÷ N.
To annualize, solve for the periodic rate i in N = Σ(payment due in period t ÷ (1 + i)^t), for t from 1 through n; then multiply i by m, the payments per year.

In a sheet, list N as the first inflow and each payment as an outflow, then apply IRR; XIRR does the same when the payments carry exact dates.

Each period's payment in the equation includes P and any separately billed charge due then; include E in the period when it is due. For an early-settlement scenario, use the actual payments and charges through the settlement date instead of the full scheduled term.

Here c and C mean the same annualized nominal comparison rate; line three is the unannualized cost per dollar received.

Four lines to price any offer

Total outT = (P times n) + R + E
Cost in dollarsC = T minus N
Cost per dollar receivedc = C divided by N
Annualized costc divided by (n divided by m)

The annualized figure is only as reliable as the cash flows and timing you enter, but it is a useful sorting tool. Two offers that land close together need a closer read of their terms; two offers far apart have already told you what to do.

The same four lines work on any product that involves receiving money and paying more back. That is why they are the only basis on which a term loan, an equipment financing offer and a merchant cash advance can be set beside each other at all.

Test which input actually moves the answer

Once the sheet exists, change one variable at a time and watch c.

Test which input moves

Change n on its own. A longer term usually lowers P and raises C. Both facts are true at once, and only one of them is visible on the offer sheet.
Change U on its own. Upfront deductions hit shorter loans harder, because the same charge is spread over less time.
Change the rate on its own, and compare that movement against the two above. On short, fee-heavy offers the rate is frequently not the largest term in the expression.
Model an early settlement. On a time-based cost, paying early genuinely reduces C. On a fixed charge, it does not, and E may punish you for trying.

If the answer barely moves when you change something, stop negotiating it and go after whatever does move it. When two offers still land close together, the normalizing work in term loans comparison decides between them.

Where to check anything specific

Rates, fees and terms come from lenders, and only from lenders. For the shape of business financing generally, with nothing being sold, the FDIC's Money Smart for Small Business and the SBA business guide are neutral starting points.

The algebra also prices a line of credit, where you pay on money you have not drawn.

For a covered closed-end credit transaction, Regulation Z Appendix J says APR is determined using either the actuarial method or the United States Rule method. The annualized comparison rate calculated here is not automatically the legally disclosed APR, so do not treat the two as interchangeable.

Common questions

Why check a payment by hand instead of using a calculator?

Because a calculator hides its assumptions, and the assumptions are where offers differ. A sheet you built yourself shows every one of them.

Which single figure should I compare on?

Compare the annualized cash-flow rate c, calculated from the net cash received and the timing of payments and charges. It gives you a consistent comparison measure across offers, while the lender's legally disclosed APR may be governed by different rules.

My lender quoted a rate but the payment does not reconcile. What now?

Ask what is inside the financed balance and what cost basis applies. Both explain most gaps, and both are reasonable questions.

Is the annualized figure precise enough to rely on?

It is precise enough to sort offers, which is its job. When two land close together, decide on the terms rather than on a second decimal place.