Level 6 · Financing a packing line with PMT and NPER

Daniel’s question is the first one any finance quote raises: what will this cost us each month? Excel has a family of functions for money that moves at a steady rate over time. PMT gives the payment, NPER the number of payments, and in the next level FV and PV give the end value and the starting value. They share the same arguments and the same sign rule.

PMT: the payment

=PMT(rate, nper, pv, [fv], [type])
=PMT(E3/12,E4*12,-E7)

Argument	Meaning	Here
rate	interest rate per period	7.5% a year ÷ 12 = 0.625% a month
nper	number of periods	4 years × 12 = 48 months
pv	present value: the loan amount	$33,400
fv	value left at the end (optional, default 0)	0, the loan is fully repaid
type	0 = pay at the end of each period (default), 1 = at the start	0

The rate and the number of periods must use the same unit. A monthly payment needs a monthly rate and a count of months. This is the most common slip with these functions: =PMT(E3,E4,-E7) returns $9,972.15, a yearly payment, and nothing in the cell says so.

With the units right, E8 shows $807.58. Over 48 months that is $38,763.62 paid for $33,400 borrowed: $5,363.62 of interest.

The sign convention

Excel’s finance functions look at cash from your side: money you receive is positive, money you pay is negative. A loan is cash in (positive), the payments are cash out (negative). So =PMT(E3/12,E4*12,E7) returns -807.58: correct, and correctly negative.

Reports usually show a payment as a positive amount. Two equivalent ways:

=PMT(E3/12,E4*12,-E7)
=-PMT(E3/12,E4*12,E7)

Pick one and use it throughout the sheet. The sign matters even more in NPER, where a wrong sign gives a wrong answer instead of an error.

Cost per order

The payment alone doesn’t tell Daniel whether it is a lot. Divided by the store’s volume it does: in 2025 the store completed 357.92 orders a month on average, so

=E8/AVERAGE(B2:B13)

gives $2.26 per order. That is a number operations can weigh against what the packing line saves on each order.

NPER: how long at a fixed payment

=NPER(rate, pmt, pv, [fv], [type])
=NPER(E3/12,-E5,E7)

NPER turns the question around: the payment is fixed and the number of periods is the answer. The payment is cash out, so it goes in negative, and the loan is cash in, so it stays positive. E10 shows 37.6: 37 full payments of $1,000 and a smaller 38th. Paying $1,000 instead of $807.58 finishes the loan about ten months early.

If the payment doesn’t even cover a month’s interest ($33,400 × 0.625% = $208.75), the loan can never be repaid, and NPER returns #NUM!. Give the payment and the loan the same sign and you get -30.4, a negative count of months that answers a different question: watch for it.

Common mistakes

• Yearly rate with monthly periods, or the other way round. Divide the rate by 12 and multiply the years by 12.
• Typing 7.5 for the rate. E3 holds 0.075, formatted as 7.5%; 7.5 would mean 750%.
• Losing track of signs. Payments out are negative, money in is positive; flip one with a minus sign for display.
• Rounding NPER’s answer down. 37.6 months means 38 payments.