ModeldrillXNPV and XIRR
XNPV(rate, values, dates) and XIRR(values, dates) work like NPV and IRR but use the actual date of each cash flow, discounting by (1 + rate)^(days / 365). The first date is time zero, so unlike NPV, XNPV does not discount the first value.
Syntax
=XNPV(rate, values, dates) =XIRR(values, dates, [guess])| Part | What it is |
|---|---|
rate | Annual discount rate |
values | Cash flows; the first is usually the negative investment |
dates | Dates matching the values, as real Excel dates; the first date is the start |
Examples
-1,000 on 1 Jan 2025, 1,100 on 1 Jan 2026, rate 10%
=XNPV(10%, {-1000,1100}, {"2025-01-01","2026-01-01"})Result: 0.00
365 days at 10% discounts 1,100 to exactly 1,000.
-1,000 on 1 Jan 2025, 1,210 on 1 Jan 2027
=XIRR(values, dates)Result: 10.00%
730 days is exactly two years, so (1.21)^(1/2) - 1.
-1,000 on 1 Jan 2025, 500 on 1 Jan 2026, 600 on 1 Jan 2027
=XIRR(values, dates)Result: 6.39%
Common mistakes
- Dates stored as text: XNPV returns #VALUE!; convert with DATE or DATEVALUE.
- First cash flow is positive or the dates are not ascending: put the investment first with the earliest date.
- Adding the initial outlay outside XNPV like you would with NPV, which double counts it; XNPV already treats the first value as time zero.
Practice XNPV and XIRR
These drills check your answer on live data, free, no account needed.
- Dated cash flows with XNPV and XIRRThe cash flows arrive on irregular dates. Check XNPV by hand: fill the years from the first date (row 6: days since B4 divided by 365) and each discounted flow (row 7). Then fill XNPV in B9, the sum of the discounted flows in B10 (it should match) and XIRR in B11.
- Return on an investment with irregular datesAn investor put money in twice and received four distributions. Using the dates in column A and flows in column B, fill the XIRR (E5), the money multiple (E6: total received divided by total invested), the years held (E7: last date less first date, over 365) and the profit (E8).
Questions
Does XIRR use a 365 or 360 day year?
365 days, with no leap year adjustment. Over long periods that creates a tiny difference versus true calendar compounding.
Why does XIRR give a different answer from IRR?
IRR treats each row as one period regardless of dates. XIRR compounds over the exact days, so uneven spacing or monthly rows give different rates.
Related
- NPV in ExcelExcel NPV discounts every value one period, so the time zero investment goes outside the function: =NPV(rate, cash flows) + initial. Exact examples, errors.
- IRR in ExcelIRR is the discount rate at which NPV equals zero. How =IRR(values) works in Excel, exact worked examples, the guess argument, MIRR and fixing #NUM! errors.
- Discount factorA discount factor converts a future cash flow into present value: DF = 1 / (1 + r)^t. Worked examples, the Excel formula with absolute references, mid-year.
- Payback periodPayback period = years before full recovery + unrecovered amount / cash flow in the recovery year. Simple and discounted versions, exact examples, formula.