Porting Excel PMT to the browser: two traps that only show up in money

jmshinhwa — Thu, 30 Jul 2026 04:45:23 +0000

We put a loan calculator on a landing page — no backend, no request, the visitor types four or five numbers and the answer appears. The whole thing is one small function. It still took two attempts, because both bugs are invisible until the output happens to be money.

Trap 1: PMT divides by zero at 0%

Excel's PMT(rate, nper, pv, fv, type) is a closed-form annuity payment:

function pmt(rate, nper, pv, fv, type) {
  if (nper === 0) return 0;
  if (rate === 0) return -(pv + fv) / nper;   // <- the branch people forget
  const f = Math.pow(1 + rate, nper);
  return -(pv * f + fv) * rate / ((f - 1) * (1 + rate * type));
}

Drop the rate === 0 line and the general formula gives you (f - 1) === 0 in the denominator, so a 0% promotional loan — the exact case a dealer advertises — returns Infinity or NaN. Excel has that branch. If you transcribe only the algebra from a finance textbook, you don't.

A second detail: the sign. pmt returns a negative number for a positive present value, because Excel models money leaving you. If you pass the balance in as -balance you get a positive payment back and the rest of your arithmetic reads normally.

Trap 2: Excel's ROUND and JavaScript's Math.round disagree

Math.round in JavaScript is half-up toward positive infinity: Math.round(-2.5) === -2. Excel's ROUND is half away from zero: ROUND(-2.5, 0) is -3. On a page that shows a savings figure, that is a cent of difference between the browser and the spreadsheet the user downloads — and a cent is enough for someone to conclude one of the two is broken.

function excelRound(x, digits) {
  const m = Math.pow(10, digits), v = x * m;
  const a = Math.abs(v), fl = Math.floor(a);
  const r = ((a - fl) >= 0.5 ? fl + 1 : fl) * (v < 0 ? -1 : 1);
  return r / m;
}

Why bother with parity at all

Because the spreadsheet is the product and the page is the demo. If the two disagree by a cent, the demo has told the visitor not to trust the product.

The live version is a car-refinance calculator. It runs 5 of the spreadsheet's 9 inputs and 2 of its 6 results, in the page, with the same formulas — the numbers you type never leave your browser. Two outputs are worth having even if you never download anything: how much the monthly payment actually drops, and how many months it takes for the closing costs to pay for themselves. On the default numbers that is $80.24 and 2.3 months.

Poke at it here: car refinance calculator

The input that decides the whole answer, by the way, is the payoff balance — and the figure in your lender's app usually isn't it. Call and ask for the payoff.

DEV Community: jmshinhwa

Porting Excel PMT to the browser: two traps that only show up in money

Trap 1: PMT divides by zero at 0%

Trap 2: Excel's ROUND and JavaScript's Math.round disagree

Why bother with parity at all