Skip to content
Tutorials Center
My learning

Topics

Lesson 37 of 93 ยท Statistics and Math

Financial Functions: PMT, NPV, IRR and More

Use Excel financial functions PMT, IPMT, PPMT, FV, PV, RATE, NPER, NPV, IRR, XNPV and XIRR correctly, including sign conventions and a loan schedule.

Loans, savings plans and investments all follow the same idea: money has a time value, so a dollar today is worth more than a dollar next year. Excel packs that idea into a family of financial functions. Once you know the five ingredients (rate, number of periods, payment, present value and future value), most questions become a single formula: "What is the monthly payment?", "How much will I have in 20 years?", "Is this project worth doing?"

The functions are easy to call and easy to misuse. The two classic mistakes are mixing up periods (an annual rate with monthly payments) and getting the sign wrong. This lesson explains both, then lets you run stand-ins that implement the standard formulas in JavaScript and Python. Excel cannot run on this site, so these are our own models of the documented behavior; the formulas shown in plain blocks are what you type in Excel.

Five ingredients

Rate Interest per period.
Nper How many periods.
Pmt Equal payment each period.
Pv Value today.

The fifth one, Fv, is the value at the end.

1 / 2

Signs: money in and money out

Excel's financial functions use a cash-flow sign convention: money you receive is positive and money you pay out is negative. A loan of 200,000 you receive is +200000 (present value), and the payments you make are negative. That is why =PMT(0.05/12, 360, 200000) returns a negative number: the payment is money leaving your pocket.

If you want a positive payment, put a minus in front of the function (=-PMT(...)) or enter the loan amount as a negative number. If a result looks wrong by sign, or a function returns #NUM!, check that at least one cash flow is positive and one is negative. A loan where you "receive" and also "receive" payments has no solution.

Rate and periods must match

If payments are monthly, use the monthly rate and the number of months. For an annual rate of 6% and a 30-year loan:

TEXT
=PMT(6%/12, 30*12, 250000)

Dividing the annual rate by 12 is the usual, simple convention (it ignores compounding between the nominal and effective rate; lenders usually quote it this way). Quarterly payments would use /4 and *4.

The optional type argument is 0 (the default) for payments at the end of each period and 1 for the start, as with rent or an annuity-due.

The loan family: PMT, IPMT, PPMT

PMT(rate, nper, pv, [fv], [type]) is the level payment. Each payment is part interest and part principal. IPMT(rate, per, nper, pv, [fv], [type]) is the interest in period per, and PPMT(...) is the principal in period per. They always add up: PMT = IPMT + PPMT. Early payments are mostly interest, and the share of principal grows every period.

A schedule is just a table: period, payment, interest, principal, balance. Put =IPMT($B$1/12, A5, $B$2, $B$3) and =PPMT(...) in each row, with the period in column A, and the closing balance is the previous balance plus the (negative) principal.

The savings family: FV, PV, NPER, RATE

  • FV(rate, nper, pmt, [pv], [type]): what a savings plan reaches. Saving 200 a month for 10 years at 4% a year: =FV(4%/12, 120, -200) (the payments are negative because you pay them in; the result is positive).
  • PV(rate, nper, pmt, [fv], [type]): what a stream of future payments is worth today. It tells you how large a loan a given payment supports.
  • NPER(rate, pmt, pv, [fv], [type]): how many periods it takes, for example how many months to pay off a card.
  • RATE(nper, pmt, pv, [fv], [type], [guess]): the interest rate per period. It is found by iteration, so it can return #NUM! when the guess is far away or the cash flows have no solution.

Run it: the payment formulas in JavaScript

The stand-in implements these functions from the closed-form annuity formulas, with RATE found by bisection. The loan numbers are the ones you would type into Excel.

JS PMT, IPMT, PPMT, FV, PV, NPER and RATE

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The last line confirms the idea behind a loan: after all payments the balance is zero (the tiny leftover is only floating-point rounding; Excel would show 0.00 with the usual format).

Investments: NPV, IRR, XNPV, XIRR

Net present value discounts each cash flow back to today and adds them up. A positive NPV means the project earns more than the discount rate. Excel's NPV(rate, value1, ...) has a trap: it assumes the first value is one period from now, not today. So the initial investment, which happens today, goes outside the function:

TEXT
=NPV(10%, C3:C6) + C2     (C2 = initial investment, negative)

IRR(values, [guess]) is the discount rate at which the NPV is exactly zero: the project's annual return. Unlike NPV, IRR treats the first value as time zero, so you include the investment in the range. IRR needs at least one positive and one negative value and is solved by iteration, so unusual cash flows (several sign changes) can have several answers or none.

NPV and IRR assume equal gaps between payments. For cash flows on real dates, use XNPV(rate, values, dates) and XIRR(values, dates, [guess]). They discount by the actual days divided by 365, and the first value counts at the first date. Both have rate as a yearly rate.

Run it: NPV, IRR and XIRR

JS NPV, IRR, XNPV and XIRR

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Both projects have a positive NPV at 8%, so both beat that rate, and project A is better on both measures. The two measures can disagree when projects differ in size or timing: NPV is in money and is usually the better guide, while IRR is a rate and ignores how much you invest.

Python: a loan schedule with extra payments

Python has no built-in PMT, but the formula is short. This real-Python TryIt builds an amortization table with pandas and shows the effect of an extra monthly payment. It is the same arithmetic your Excel schedule performs.

Python A loan schedule in pandas

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Practical advice

  • Label your inputs. Put the annual rate, years and loan amount in labeled cells and refer to them, instead of typing numbers into formulas. See ranges and named ranges.
  • Show the sign on purpose. Use -PMT(...) for a payment you want to show as positive and say so in a note.
  • Check by round trip. A loan's FV after all payments should be about zero; the NPV at the IRR should be about zero.
  • Rounding. Banks round each payment to cents. Wrap PMT in ROUND(..., 2) if your schedule has to match a lender's; the last payment then absorbs the difference. See percentages and rounding.
  • Use What-If tools to ask questions like "what rate gives a payment of 1,400?" with Goal Seek: What-If Analysis.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why does =PMT(5%/12, 360, 200000) return a negative number?

    You receive the loan (positive) and pay it back (negative). Put a minus in front of PMT if you want a positive display.
  2. 2. Your cash flows are in C2:C6, where C2 is the investment made today. Which formula is the correct NPV at 10%?

    NPV treats its first value as one period away, so the value at time zero is added outside the function.
  3. 3. You pay a loan monthly and the quoted rate is yearly. What should the rate and nper arguments be for a 15-year loan?

    Rate and number of periods must use the same unit. Monthly payments need the monthly rate and the number of months.
  4. 4. Which function fits cash flows that happen on irregular calendar dates?

    XIRR (and XNPV) take a date for each cash flow and discount by the actual days, while IRR and NPV assume equal intervals.
  5. 5. What is true about the interest part of a level loan payment?

    Interest is charged on the remaining balance, which falls with each payment, so the interest part shrinks and the principal part grows.

Summary

  • Money received is positive and money paid is negative, so PMT usually returns a negative number.
  • Use the same unit for rate and nper: monthly rate with months, yearly rate with years.
  • PMT = IPMT + PPMT; early payments are mostly interest.
  • FV, PV, NPER and RATE solve for the missing ingredient of a savings plan or loan.
  • NPV starts discounting at period 1; IRR starts at period 0. Use XNPV and XIRR for real dates.

Next: Correlation, Regression and Forecasting.