Level 3 · The payment that came out twelve times too big

One rule: match the units

PMT, FV and NPER don’t know whether a period is a month or a year. They assume the rate is “per period” and the count is “how many periods”. The payment frequency decides the unit:

Paid	Rate argument	Periods argument
yearly	7.5%	5
monthly	7.5%/12	5*12 = 60
quarterly	7.5%/4	5*4 = 20

Brian’s formula mixes nothing up internally, it just answers a different question: five yearly instalments of $11,863.91.

Why not divide by twelve?

Dividing the yearly payment by 12 gives $988.66. The real monthly payment is $961.82, and the difference is not rounding. With yearly payments, the whole $48,000 accrues interest for a full year before anything is repaid. With monthly payments, the balance starts shrinking after month one, so less interest builds up. Over five years that is $59,319.55 paid on the yearly schedule against $57,709.29 on the monthly one.

The sign convention

Excel treats cash you receive as positive and cash you pay as negative. A $48,000 loan (pv positive) produces a negative payment; a savings deposit entered as negative produces a positive future value. Mixing signs inside one formula, like a positive deposit and a positive target in NPER, can return an error or a nonsense negative count. In Python you decide the convention yourself; keep payments positive and say so.

A ten-second sanity check

Before trusting any payment, divide the principal by the number of payments: $48,000 / 60 = $800. Interest makes the real payment somewhat higher, so $961.82 is plausible and $11,863.91 is obviously not a monthly figure. Then multiply the payment by the count and compare with the principal: the gap is the total interest, here $9,709.29, which is the number to show the person deciding.

Common mistakes

• Annual rate, monthly periods (or the reverse): the most common spreadsheet finance error.
• Changing only one of the two. 7.5%/12 with 5 periods is a five-month loan.
• Treating the minus sign as an error and “fixing” it by flipping the loan amount’s sign in the middle of a model, which breaks every formula that uses it.
• Comparing loans by payment only. A longer term lowers the payment and raises the total interest.