Evaluate a project with cash flows at uneven intervals using the XNPV and XIRR functions, putting the investment inside the series, and tell a task for XNPV apart from a task for NPV.
XNPV and XIRR: cash flows on real dates
XNPV and XIRR: cash flows on real dates
NPV from lesson 4.2 assumes the cash flows come exactly once per period: the first in a year, the second in two. In practice, money arrives on different dates: a machine is bought in March, the first revenue comes six months later. For such cash flows, Excel has XNPV and XIRR.
=XNPV(rate, cash_flows, dates) takes an annual rate and discounts each cash flow by the days since the first date divided by 365. The first date is time 0, and its cash flow isn't discounted. So the investment goes INSIDE the range, as the first row — the opposite of NPV, where it's added outside. =XIRR(cash_flows, dates) is the rate at which XNPV equals zero; you don't give it a rate (the optional guess can be omitted).
An example. On January 1, 2026 (2026-01-01), you invest 1,000; on July 2 (2026-07-02), 182 days later, you receive 1,150. The 15% rate is in B1, the cash flows in B2:B3, the dates in A2:A3.
• =XNPV(B1,B2:B3,A2:A3) = −1000 + 1150 / 1.15^(182/365) ≈ 72.59 — the project is worth it.
• =NPV(B1,B3)+B2 = 0: NPV assumes the 1,150 came after a full year and loses half a year.
The opposite mistake is to take the investment out of XNPV. Then the first date becomes the date of the first inflow, the count shifts, the inflows are under-discounted, and the result is overstated.
Next: a new coffee machine and display case for the Zerno coffee shop — five cash flows in dollars on the grid below.
NPV vs. XNPV: how they differ
| What we compare | NPV | XNPV |
|---|---|---|
| Time-0 investment | outside: =NPV(…)+investment | inside the series, as the first row |
| Timing of a cash flow | position in the series: 1, 2, 3… | days since the first date / 365 |
| Rate | per period of the series | annual |
| When it fits | cash flows exactly once per period | cash flows on any dates |
| Return | =IRR(cash_flows) | =XIRR(cash_flows, dates) |
Lesson notes
XNPV and XIRR: cash flows on real dates
NPV from lesson 4.2 assumes the cash flows come exactly once per period: the first in a year, the second in two. In practice, money arrives on different dates: a machine is bought in March, the first revenue comes six months later. For such cash flows, Excel has XNPV and XIRR.
=XNPV(rate, cash_flows, dates) takes an annual rate and discounts each cash flow by the days since the first date divided by 365. The first date is time 0, and its cash flow isn't discounted. So the investment goes INSIDE the range, as the first row — the opposite of NPV, where it's added outside. =XIRR(cash_flows, dates) is the rate at which XNPV equals zero; you don't give it a rate (the optional guess can be omitted).
An example. On January 1, 2026 (2026-01-01), you invest 1,000; on July 2 (2026-07-02), 182 days later, you receive 1,150. The 15% rate is in B1, the cash flows in B2:B3, the dates in A2:A3.
• =XNPV(B1,B2:B3,A2:A3) = −1000 + 1150 / 1.15^(182/365) ≈ 72.59 — the project is worth it.
• =NPV(B1,B3)+B2 = 0: NPV assumes the 1,150 came after a full year and loses half a year.
The opposite mistake is to take the investment out of XNPV. Then the first date becomes the date of the first inflow, the count shifts, the inflows are under-discounted, and the result is overstated.
Next: a new coffee machine and display case for the Zerno coffee shop — five cash flows in dollars on the grid below.