PMT

Veldhuis Installatie BV took a bank loan of 250,000.00250.000,00 at 4.8% per year to finance a new workshop, repayable in 60 equal monthly installments. The loan agreement is in the audit file. You recompute the monthly installment to test the amount the client pays.

Formula in D2

=PMT(B2/12, C2, A2)

=PMT(B2/12; C2; A2)

Result: -€4,694.94€ -4.694,94

Watch out: Writing PMT(B2, C2, A2) without dividing by 12 treats 4.8% as a monthly rate. The result is far too high, but it still looks like a valid installment.

=-PMT(B2/12, C2, A2)

=-PMT(B2/12; C2; A2)

Veldhuis Installatie BV leases service vans. Discounted at an incremental borrowing rate of 3.6% per year, the present value of all 36 monthly payments is 84,000.0084.000,00.

The first payment is due on the commencement date, so the lease liability under IFRS 16 at that date is 84,000.0084.000,00 - 2,457.722.457,72 = 81,542.2881.542,28, because only payments not yet paid count. The contract states an installment of 2,457.722.457,72, and you check that figure.

Formula in E2

=ROUND(-PMT(B2/12, C2, A2, 0, 1), 2)

=ROUND(-PMT(B2/12; C2; A2; 0; 1); 2)

Result: €2,457.72€ 2.457,72

Watch out: Leaving out the last two arguments gives 2,465.102.465,10, which is 7.38 too high. It looks like a rounding difference but is a timing error.

=ROUND(-PMT(B2/12, C2, A2), 2)

=ROUND(-PMT(B2/12; C2; A2); 2)

The client's loan list shows three loans of Veldhuis Installatie BV, each with a balloon payment due together with the last monthly installment. You recompute each installment and compare it with the amount in the client's list. A difference of more than 5 cents (cell I2) needs follow-up.

Formula in G2

=IF(ABS(ROUND(-PMT(C2/12, D2, B2, -E2), 2) - F2) <= $I$2, "OK", "Investigate")

=IF(ABS(ROUND(-PMT(C2/12; D2; B2; -E2); 2) - F2) <= $I$2; "OK"; "Investigate")

Result: Investigate

Watch out: If the balloon is entered as +E2 instead of -E2, the installment goes up instead of down, and L-02 and L-03 flip to Investigate as well. A whole list failing at once points to a sign problem, not to the client.

=ROUND(-PMT(C2/12, D2, B2, -E2), 2) - F2

=ROUND(-PMT(C2/12; D2; B2; -E2); 2) - F2

Veldhuis Installatie BV leases a warehouse crane. Discounted at 5.2% per year, the present value of the 20 quarterly installments and the guaranteed residual value of 12,000.0012.000,00 is 180,000.00180.000,00.

The client pays in advance, so the first installment is paid at commencement and the liability recognized then is 180,000.00180.000,00 - 9,624.339.624,33 = 170,375.67170.375,67. You recompute the installment and the total finance cost over the term (total paid minus 180,000.00180.000,00), to test the total interest in the client's lease schedule.

Formula in G2

=LET(
  presentValue, A2,
  perYear, D2,
  rate, B2 / perYear,
  periods, C2 * perYear,
  residual, E2,
  timing, F2,
  payment, ROUND(-PMT(rate, periods, presentValue, -residual, timing), 2),
  payment * periods + residual - presentValue
)

=LET(
  presentValue; A2;
  perYear; D2;
  rate; B2 / perYear;
  periods; C2 * perYear;
  residual; E2;
  timing; F2;
  payment; ROUND(-PMT(rate; periods; presentValue; -residual; timing); 2);
  payment * periods + residual - presentValue
)

Result: €24,486.60€ 24.486,60

Watch out: Entering the residual with a plus sign (fv = +12,00012.000) raises the installment instead of lowering it. The result looks plausible, but the total finance cost is overstated. Always test the sign with a residual of 0 and see that it moves the installment down.

=ROUND(-PMT(B2 / D2, C2 * D2, A2, -E2, F2), 2) * C2 * D2 + E2 - A2

=ROUND(-PMT(B2 / D2; C2 * D2; A2; -E2; F2); 2) * C2 * D2 + E2 - A2