Investment decisions
Dated cash flows: XNPV and XIRR
Value cash flows that land on real calendar dates, and know exactly what XNPV and XIRR assume.
Learning objectives
- Explain the XNPV and XIRR conventions: actual/365 from the first date
- Build dates with DATE() and discount by exact year fractions
- Calculate XNPV and XIRR and compare them with NPV and IRR
NPV() and IRR() assume cash flows exactly one period apart. Real deals aren't like that: a property is bought in February, rent starts in August, a buyer pays the balance next March. Treating those dates as "year 1, year 2…" can badly misstate value.
XNPV() and XIRR() take a date for every cash flow and discount by the exact time elapsed. They are the standard for private equity, real estate and project-finance returns.
XNPV = Σ CFᵢ ÷ (1 + r)^((dᵢ − d₁) ÷ 365)
- CFᵢ, dᵢ
- each cash flow and its date
- d₁
- the first date in the list: its cash flow is not discounted
- 365
- the day count: actual days ÷ 365, even in leap years
- r
- an effective annual rate; XIRR also returns one
Included with a Formula Quest membership
The rest of this lesson is for members. Members get the full lesson, including its 3 graded exercises, and every other Finance Quest lesson.
That's the last lesson in Time Value of Money
Prove what you've learned with this track's assessed work — it counts towards your certificate.