PMT function in Excel

A rupee today is worth more than a rupee next year, because today's rupee can be invested to become 1 + r rupees. Compound that over n periods and a single amount today, PV, grows to a future amount, FV, by FV = PV × (1 + r)^n. Almost all of finance is this one relationship with a stream of equal payments, PMT, layered on top. So the five time-value functions are really five ways to solve one equation for its five unknowns:

PV × (1 + r)^n  +  PMT × [((1 + r)^n − 1) / r]  +  FV  =  0

Given any four of {PV, FV, PMT, r (rate), n (nper)}, Excel solves for the fifth:

• PV(rate, nper, pmt, [fv], [type]), today's value of a future amount and/or a payment stream.
• FV(rate, nper, pmt, [pv], [type]), what a stream and/or lump sum grows to.
• PMT(rate, nper, pv, [fv], [type]), the level payment that ties pv to fv.
• RATE(nper, pmt, pv, [fv], [type], [guess]), the per-period rate implied by the other four, solved iteratively.
• NPER(rate, pmt, pv, [fv], [type]): how many periods it takes.

How do the signs work in PMT, PV and FV?

One rule trips everyone up, and it's the cash-flow sign convention. Excel models these as your cash flows on a timeline. Money that comes to you is positive, and money that leaves you is negative. A loan you receive is a positive pv, because it's cash in now. The repayments are therefore negative, and PMT returns a negative number to say "this leaves your pocket." Feed pv and fv the same sign and Excel returns a #NUM! or a nonsensical answer, because the signs have to express the direction of each flow. type is 0 for end-of-period payments (an ordinary annuity). That's the default, and the usual case for loans. It's 1 for beginning-of-period payments, such as rent paid in advance (an annuity-due).

Worked example: PMT on a home loan

Work the mechanics on a home loan. Borrow ₹50,00,000 at a 9% nominal annual rate over 20 years, repaid monthly. First convert the annual rate and the term to monthly periods. That conversion is a classic source of errors in its own right:

rate = 9% / 12 = 0.75% per month
nper = 20 × 12 = 240 months
pv   = 5000000            (cash you received — positive)
=PMT(0.09/12, 240, 5000000)   →   -44,986.30

You get −₹44,986.30, the monthly instalment. It's negative because it leaves your account, and analysts usually display it as a positive number with =-PMT(...) or ABS. From this one cell you can read the whole loan. In total you pay 44,986.30 × 240 = ₹1,07,96,711.47, and ₹57,96,711.47 of that is interest. That's more than the principal itself, which is the sobering arithmetic of a long tenure. The closed form behind the cell is PMT = pv × r / (1 − (1 + r)^(−n)), and the function just spares you typing it.

Worked examples: FV, PV, RATE and NPER

Now the other three, which are quick. A ₹15,000 monthly SIP at 12% nominal for 10 years grows to =FV(0.12/12, 120, -15000, 0) = ₹34,50,580.34. The payments are negative because you pay them in, and the result is positive because it comes back to you. A promise of ₹10,00,000 in 5 years, discounted at 8% a year, is worth =PV(0.08, 5, 0, -1000000) = ₹6,80,583.20 today. Now take a ₹2,00,000 loan cleared by ₹4,000 a month over 60 months. Its implied rate is =RATE(60, -4000, 200000) = 0.6183% per month. Multiply the monthly rate by 12 and you get 7.42% nominal annual. Finally, ₹10,000 saved monthly into a 10% account reaches ₹10,00,000 after =NPER(0.10/12, -10000, 0, 1000000) = 73.04 months. Call it 74, since you can't make a fractional final payment. RATE and NPER solve iteratively and occasionally need a guess. The others are closed-form and instant.

Where are the financial functions in Excel?

You'll find these under Formulas ▸ Financial in the ribbon. You can also press Alt, then M, then I for the Financial gallery. Type the name directly and read the in-cell ScreenTip, which names each argument as you go. Or press Shift+F3 on a half-typed function to open the Function Arguments dialog. It earns a mention here because it labels pv/fv/type and shows the running result. To audit a finished formula, select a sub-expression in the formula bar and press F9 to see it evaluate. Esc backs out without committing.