PMT, NPV, IRR and XIRR Excel Functions in Python

Finance teams live in Excel's financial functions: PMT for loan repayments, NPV and IRR for investment decisions, XNPV and XIRR for cash flows that do not arrive on neat anniversaries. When the calculation moves into a Python job — a model that runs over thousands of loans, a report that evaluates every project in a portfolio — the numbers must match the spreadsheets people already trust, to the cent. The numpy-financial package covers most of the functions with the same argument order as Excel. It also has one function whose definition differs from Excel's, and it lacks the dated versions entirely. This page, part of Excel Formula Equivalents in pandas, covers all of them; every number below was checked against the same formulas recalculated in LibreOffice.

Prerequisites

pip install numpy-financial pandas openpyxl

project.xlsx holds a project's cash flows: an investment of 10,000 on 1 January 2026 and three inflows on irregular dates.

import numpy_financial as npf
import pandas as pd

project = pd.read_excel("project.xlsx")
flows = project["cash_flow"].tolist()

Step 1 — Loan payments: PMT, FV, NPER and RATE

These map one to one, with the same arguments in the same order. Rates and periods must agree: for monthly payments, divide the annual rate by 12 and count months:

payment = npf.pmt(0.05 / 12, 360, 200_000)
print(round(payment, 2))                               # =PMT(5%/12, 360, 200000)

print(round(npf.fv(0.04 / 12, 120, -500, 0), 2))       # save 500 a month for 10 years at 4%
print(round(npf.nper(0.05 / 12, -1500, 200_000), 1))   # months to repay at 1,500 a month
print(round(npf.rate(360, -1073.64, 200_000, 0) * 12, 4))

The payment is -1073.64: the loan is received (positive), so the payments are negative, exactly as in Excel. Saving 500 a month grows to 73,624.90; paying 1,500 a month clears the loan in 195 months; and rate recovers the 5% annual rate from the payment. npf.rate needs the future value as an explicit fourth argument, where Excel's RATE defaults it to zero — pass 0.

Step 2 — Loan schedules for a whole portfolio

The functions accept arrays, so a DataFrame of loans gets its payments in one call — the reason to move this into Python at all:

loans = pd.DataFrame({"loan": ["L1", "L2", "L3"], "principal": [200_000, 150_000, 320_000],
                      "annual_rate": [0.05, 0.043, 0.061], "years": [30, 25, 20]})
loans["monthly_payment"] = -npf.pmt(loans["annual_rate"] / 12, loans["years"] * 12, loans["principal"])
loans["total_interest"] = loans["monthly_payment"] * loans["years"] * 12 - loans["principal"]
print(loans.round({"monthly_payment": 2, "total_interest": 2}).to_string(index=False))

Negating the result gives payments as positive amounts for a report; keep the signed version if the figures feed further cash-flow calculations. Build a Budget vs Actual Variance Report shows these figures in a finished report.

Step 3 — NPV: the difference that catches everyone

Excel's NPV(rate, v1, v2, …) assumes the first value arrives one period from now, so it discounts every value. numpy_financial.npv(rate, values) assumes the first value arrives today and leaves it undiscounted. Given the same list, they disagree:

print(round(npf.npv(0.10, flows), 2))            # first flow at t = 0
print(round(npf.npv(0.10, [0] + flows), 2))      # = Excel =NPV(10%, A2:A5)
print(round(flows[0] + npf.npv(0.10, [0] + flows[1:]), 2))   # = Excel =A2 + NPV(10%, A3:A5)

Recalculated in LibreOffice, =NPV(10%, A2:A5) gives 1,188.44 and =A2+NPV(10%, A3:A5) gives 1,307.29. The first matches npf.npv with a zero prepended; the second — the correct way to value a project with an investment today — matches npf.npv on the flows as they are. Many spreadsheets use the first form by mistake, discounting today's investment by a year. When porting a model, check which form the workbook uses and reproduce it deliberately, then fix it if it is the mistake.

Step 4 — IRR

npf.irr matches Excel's IRR: the rate at which the NPV of the flows, first one today, is zero:

irr = npf.irr(flows)
print(round(irr, 6), "| check:", round(npf.npv(irr, flows), 6))

The rate is 16.34%, and the check confirms that discounting at that rate gives an NPV of essentially zero. Like Excel, irr needs at least one sign change in the flows and can return nan when no rate solves the equation; unusual flow patterns with several sign changes can have more than one valid IRR, which is a property of the maths rather than of either tool.

Step 5 — XNPV and XIRR for dated cash flows

Real projects are paid on real dates, and Excel's XNPV and XIRR discount each flow by the actual number of days since the first one, over a 365-day year. numpy-financial does not include them, but both are a few lines:

def xnpv(rate, cash_flows, dates):
    start = dates[0]
    return sum(cf / (1 + rate) ** ((d - start).days / 365) for cf, d in zip(cash_flows, dates))

def xirr(cash_flows, dates, low=-0.99, high=10.0, tol=1e-10):
    f = lambda r: xnpv(r, cash_flows, dates)
    if f(low) * f(high) > 0:
        raise ValueError("no rate in range makes XNPV zero")
    while high - low > tol:
        mid = (low + high) / 2
        low, high = (low, mid) if f(low) * f(mid) <= 0 else (mid, high)
    return (low + high) / 2

dates = [d.date() for d in project["date"]]
print(round(xnpv(0.10, flows, dates), 2), round(xirr(flows, dates), 6))

The results — 2,230.71 and 27.36% — match =XNPV(10%, A2:A5, B2:B5) and =XIRR(A2:A5, B2:B5) exactly. Note that XNPV, unlike NPV, treats the first flow as happening today, so there is no leading-zero adjustment. Bisection is slower than the Newton method Excel uses but always converges when the range brackets a solution, which makes it the safer choice in a batch job; the ValueError reports the cases Excel would show as #NUM!.

Step 6 — Write results with the formulas alongside

For review, write both the Python figures and the equivalent Excel formulas, so a finance colleague can see that the two agree and the workbook keeps working if they change an input:

from openpyxl import load_workbook

project.to_excel("valuation.xlsx", index=False)
wb = load_workbook("valuation.xlsx")
ws = wb.active
rows = [("NPV at 10% (python)", flows[0] + npf.npv(0.10, [0] + flows[1:])),
        ("NPV at 10% (formula)", "=B2+NPV(0.1,B3:B5)"),
        ("XIRR (python)", xirr(flows, dates)),
        ("XIRR (formula)", "=XIRR(B2:B5,A2:A5)")]
for i, (label, value) in enumerate(rows, start=2):
    ws.cell(row=i, column=4, value=label)
    ws.cell(row=i, column=5, value=value)
wb.save("valuation.xlsx")
print([ws.cell(row=r, column=5).value for r in range(2, 6)])

Keeping one column of Python values and one of live formulas makes discrepancies obvious at a glance, which is the fastest way to win trust for a calculation that moved out of a spreadsheet.

Common pitfalls

• Excel NPV versus npf.npv. Prepend 0 to match NPV over all flows; use flows as they are for a proper project value.
• Mixing annual rates with monthly periods. Divide the rate by 12 and multiply the years by 12 together.
• Sign conventions. Receipts positive, payments negative; flip signs only for display.
• npf.rate without fv. It is required in Python; pass 0.
• Payments at the start of the period. Excel's optional type argument (1 for payments in advance) maps to when="begin" in numpy-financial; leave both at their defaults for ordinary loans, and set both when a model uses advance payments.

Performance notes

numpy-financial's functions are vectorised over arrays, so payments for a million loans take a fraction of a second. The pure-Python xirr evaluates xnpv a few dozen times per project; for tens of thousands of projects, vectorise xnpv with NumPy arrays of day offsets, or run the projects in parallel.

Conclusion

npf.pmt, fv, nper, rate and irr reproduce Excel's functions directly, with the same argument order and sign conventions (rate needs fv=0 passed explicitly). npf.npv differs from Excel's NPV: Excel discounts the first value, so prepend a zero to match it — and value projects as initial + NPV(later flows). For dated flows, short xnpv and xirr helpers match XNPV and XIRR exactly. Write the Python results next to live formulas when finance colleagues need to check them.

Frequently asked questions

Why is numpy-financial's npv different from Excel's NPV? Excel's NPV treats the first value as arriving one period from now, so it discounts every value. numpy_financial.npv treats the first value as arriving today, undiscounted. To match Excel, prepend a zero, or add the initial investment separately: initial + NPV(rate, later flows).

Why does PMT return a negative number? Both Excel and numpy-financial use a cash-flow sign convention: money you receive is positive and money you pay out is negative. Borrowing 200,000 (positive) means paying about 1,073.64 a month (negative).

Does numpy-financial have XNPV and XIRR? No. Both are short to write: XNPV discounts each cash flow by (1 + rate) raised to the number of days since the first flow divided by 365, and XIRR is the rate that makes XNPV zero, found by bisection.

Is numpy-financial still maintained? It is the package the financial functions moved to when they were removed from NumPy itself, and it is small and stable. Check the version you depend on and pin it, as with any dependency.

Related

• Up one level: Excel Formula Equivalents in pandas — every function, translated.
• Match Excel Rounding in pandas — rounding payments the way the spreadsheet does.
• Fix Floating-Point Noise Like 0.1 + 0.2 ≠ 0.3 in Excel Output — exactness in money calculations.
• Running Totals and Year-Over-Year Growth in pandas — cumulative cash-flow tables.