XIRR and XNPV Equivalents in Python

Real cash flows do not arrive on neat annual dates. An investment is funded in January, pays a dividend in June, another the following January, and is sold in March two years later. Excel's NPV and IRR assume equally spaced periods; XNPV and XIRR take the actual dates. When a report that used to live in a spreadsheet moves to Python, these two functions are often the ones people worry about, because finance colleagues will compare the numbers to the decimal. The good news is that both are short formulas — XNPV is a sum, XIRR is the rate that makes that sum zero — and a few lines of Python reproduce Excel exactly. This page belongs to Excel Formula Equivalents in pandas.

Prerequisites

pip install pandas openpyxl scipy

cashflows.xlsx lists cash flows for three investments: money paid in is negative, money received is positive. Fund A has the five irregular flows from the introduction; Fund B has two contributions; Fund C never earns its money back.

Step 1 — XNPV is a sum with actual/365 discounting

Excel's documented formula discounts each cash flow by (1 + rate) raised to the number of days since the first date, divided by 365:

import pandas as pd

flows = pd.read_excel("cashflows.xlsx", parse_dates=["Date"])
fund_a = flows[flows["Investment"] == "Fund A"]

def xnpv(rate, values, dates):
    dates = pd.to_datetime(pd.Series(dates)).reset_index(drop=True)
    years = (dates - dates.iloc[0]).dt.days / 365.0
    return float((pd.Series(values).reset_index(drop=True) / (1 + rate) ** years).sum())

print(f"XNPV at 8%: {xnpv(0.08, fund_a['CashFlow'], fund_a['Date']):,.2f}")

Two details match Excel's convention and are easy to get wrong: the divisor is always 365 (leap years do not change it), and the base date is the first date in the list, not the earliest one. The result, 8,241.05, is the value of Fund A's flows as of 15 January 2026, discounted at 8% a year.

Step 2 — XIRR is the rate where XNPV is zero

There is no closed formula for XIRR; Excel searches for it numerically. A bracketing root-finder such as SciPy's brentq is robust and fast:

from scipy.optimize import brentq

def xirr(values, dates, low=-0.9999, high=10.0):
    f = lambda r: xnpv(r, values, dates)
    if f(low) * f(high) > 0:
        raise ValueError("XNPV does not change sign between the bounds; no XIRR found")
    return brentq(f, low, high, xtol=1e-12)

rate = xirr(fund_a["CashFlow"], fund_a["Date"])
print(f"XIRR: {rate:.6%}")
print(f"check: XNPV at that rate = {xnpv(rate, fund_a['CashFlow'], fund_a['Date']):.6f}")

Fund A's XIRR is 13.7315%, and plugging it back into XNPV gives zero — the definition of the internal rate of return. brentq needs two rates where XNPV has opposite signs; the default bracket from −99.99% to +1000% covers anything a realistic investment produces, and the explicit check turns "no solution" into a clear message instead of an obscure SciPy error.

Step 3 — Why NPV and IRR give a different answer

Excel's IRR treats the flows as one period apart, whatever their dates:

import numpy as np

def irr_equal_periods(values):
    f = lambda r: sum(v / (1 + r) ** i for i, v in enumerate(values))
    return brentq(f, -0.9999, 10.0)

print(f"IRR (equal periods): {irr_equal_periods(list(fund_a['CashFlow'])):.4%}")
print(f"XIRR (actual dates): {rate:.4%}")

IRR says 7.12%, XIRR says 13.73%. Fund A's five flows span just over two years, but IRR spreads them over four periods, as if each were a year apart — so the money appears to work for twice as long and the return halves. For any schedule with uneven gaps, only the date-aware functions answer the question people are asking.

Step 4 — XIRR for every investment

With a long table of flows, compute XIRR per group and keep failures visible rather than letting one bad group stop the run:

def safe_xirr(group):
    try:
        return xirr(group["CashFlow"], group["Date"])
    except ValueError:
        return np.nan

summary = flows.groupby("Investment").apply(
    lambda g: pd.Series({
        "Invested": -g.loc[g["CashFlow"] < 0, "CashFlow"].sum(),
        "Returned": g.loc[g["CashFlow"] > 0, "CashFlow"].sum(),
        "XNPV @8%": xnpv(0.08, g["CashFlow"], g["Date"]),
        "XIRR": safe_xirr(g),
    }), include_groups=False)
print(summary.round(4))

Fund B, with two contributions and two distributions, comes out at a sensible positive return. Fund C returned 22,000 of 40,000 and its XIRR is negative — still a valid answer, found because the bracket starts near −100%. A group whose flows are all negative or all positive has no XIRR at all; safe_xirr returns NaN for it, which shows up in the report as an empty cell to investigate.

Step 5 — Check against the spreadsheet

The strongest evidence for colleagues is the spreadsheet's own answer. Write Excel formulas next to the data, let LibreOffice calculate them, and compare:

import subprocess
from openpyxl import Workbook, load_workbook

wb = Workbook()
ws = wb.active
for d, v in zip(fund_a["Date"], fund_a["CashFlow"]):
    ws.append([d.to_pydatetime(), int(v)])
ws["D1"], ws["D2"], ws["D3"] = "=XNPV(0.08,B1:B5,A1:A5)", "=XIRR(B1:B5,A1:A5)", "=IRR(B1:B5)"
wb.save("check.xlsx")
subprocess.run(["soffice", "--headless", "--convert-to", "xlsx", "--outdir", "recalc", "check.xlsx"],
               check=True, capture_output=True)
sheet = load_workbook("recalc/check.xlsx", data_only=True).active
print("sheet  XNPV, XIRR, IRR:", [round(sheet[c].value, 8) for c in ("D1", "D2", "D3")])
print("python XNPV, XIRR, IRR:", [round(x, 8) for x in (xnpv(0.08, fund_a["CashFlow"], fund_a["Date"]), rate,
                                                        irr_equal_periods(list(fund_a["CashFlow"])))])

All three agree to eight decimal places. The same check against Excel itself is a matter of opening check.xlsx there; the formulas are standard.

Step 6 — XIRR for an investment that is still open

Portfolio reports usually ask "what has this returned so far?" about investments that have not been sold. The standard answer is to treat the current valuation as if it were received on the reporting date: append it as a final positive flow, then compute XIRR as usual.

valuations = pd.DataFrame({
    "Investment": ["Fund B", "Fund C"],
    "Date": pd.to_datetime(["2028-06-30", "2028-06-30"]),
    "CashFlow": [5000, 19000],          # value still held on the reporting date
})
as_of = pd.concat([flows, valuations], ignore_index=True).sort_values(["Investment", "Date"])
print(as_of.groupby("Investment")[["CashFlow", "Date"]]
      .apply(lambda g: round(safe_xirr(g), 4)).rename("XIRR to 30 Jun 2028"))

Fund C, which looked like a heavy loss when only its cash distributions counted, still holds 19,000 of value; with that included it has slightly more than its 40,000 back, and its return to date is about 1.3% a year. Fund B rises from 10.8% to 14.1% once the 5,000 it still holds is counted. Fund A has no valuation row because it is fully realised, and its XIRR is unchanged. Sorting by date before computing keeps the first listed date as the earliest, which matters for XNPV (Step 1) even though XIRR itself does not depend on the base date. Always label such figures with their reporting date — an "as of" XIRR changes every time the valuation does.

Common pitfalls

• Using 365.25 or calendar years. Excel divides days by 365, always.
• Sorting changes XNPV. The base date is the first listed date; sort by date first, or the result is expressed as of a different day (XIRR is unaffected). Excel's documentation says dates before the first one return #VALUE!; LibreOffice discounts them anyway.
• No sign change. All-negative or all-positive flows have no IRR; report it rather than returning a number.
• Several sign changes. Flows that switch between paying in and paying out more than once can have more than one XIRR; check the XNPV curve or use the modified IRR with explicit reinvestment rates.
• Rates as percentages. xnpv(8, …) means 800%; pass 0.08.

Conclusion

XNPV discounts each cash flow by (1 + rate) to the power of days since the first listed date over 365; XIRR is the rate that makes that sum zero, found with a root-finder such as brentq. Both reproduce Excel to many decimal places, unlike IRR, which assumes equal periods and badly misstates returns on irregular schedules. Compute XIRR per investment with groupby, return NaN where no rate exists, and prove the numbers by recalculating Excel's own formulas in LibreOffice.

Frequently asked questions

How do I calculate XIRR in Python? Define XNPV as the sum of each cash flow divided by (1 + rate) to the power of days since the first date divided by 365, then find the rate where it equals zero with scipy.optimize.brentq over a bracket such as -0.9999 to 10.

Does Python's XIRR match Excel exactly? Yes, when it uses the same convention: an actual/365 day count measured from the first listed date. The functions on this page agree with spreadsheet XIRR and XNPV results to at least eight decimal places.

Why is my IRR so different from XIRR? IRR assumes the cash flows are exactly one period apart. When the real dates are irregular, that changes how long the money is invested, so IRR and XIRR can differ a lot. Use XIRR for dated flows.

Why does XIRR fail for some investments? If all cash flows have the same sign there is no rate that makes the net present value zero, so no XIRR exists. Check for at least one negative and one positive flow and report missing results explicitly.

Related

• Up one level: Excel Formula Equivalents in pandas — Excel functions translated to Python.
• PMT, NPV and IRR in Python — the equal-period functions.
• FV, PV, RATE and NPER Equivalents in Python — the rest of Excel's time-value functions.
• Build a Cash Flow Forecast Report — where dated cash flows come from.