NPV with irregular cash flows
Advanced Financial AnalystThe standard Net Present Value (NPV) function assumes cash flows occur at regular intervals (typically annually). In reality, project cash flows rarely align with neat annual periods. The XNPV function solves this by calculating NPV for cash flows that occur on specific dates.
XNPV uses the actual dates to determine the precise time between cash flows, applying day-accurate discounting. This is critical for M&A deals, project finance, and any scenario where timing significantly impacts valuation.
XNPV syntax:
=XNPV(rate, values, dates)
- rate: The annual discount rate
- values: The cash flows (must include the initial investment as a negative value)
- dates: The dates corresponding to each cash flow
Your task:
You're evaluating a project investment with irregular cash flows spanning almost two years. The initial investment of $500,000 occurs on January 15, 2024, with returns arriving at various milestone dates.
Using a 12% annual discount rate, calculate the net present value in cell B16.
One row in the cash-flow table is a financing item and should be excluded from the base-case project valuation.
Note: Unlike NPV (which assumes the first cash flow occurs one period from now), XNPV discounts from the first date in your date range. Your initial investment should be included in the values range.
Need some help?
Hint 1
Use XNPV with three inputs: annual discount rate, cash flow values, and matching cash flow dates.
Hint 2
For base-case project valuation, include operating investment/project flows and exclude financing-specific rows.
Hint 3
Your selected date/value ranges should begin at the initial investment row and end at the final payment row, with both ranges covering the same number of rows.
NPV with irregular cash flows
Advanced Financial AnalystThe standard Net Present Value (NPV) function assumes cash flows occur at regular intervals (typically annually). In reality, project cash flows rarely align with neat annual periods. The XNPV function solves this by calculating NPV for cash flows that occur on specific dates.
XNPV uses the actual dates to determine the precise time between cash flows, applying day-accurate discounting. This is critical for M&A deals, project finance, and any scenario where timing significantly impacts valuation.
XNPV syntax:
=XNPV(rate, values, dates)
- rate: The annual discount rate
- values: The cash flows (must include the initial investment as a negative value)
- dates: The dates corresponding to each cash flow
Your task:
You're evaluating a project investment with irregular cash flows spanning almost two years. The initial investment of $500,000 occurs on January 15, 2024, with returns arriving at various milestone dates.
Using a 12% annual discount rate, calculate the net present value in cell B16.
One row in the cash-flow table is a financing item and should be excluded from the base-case project valuation.
Note: Unlike NPV (which assumes the first cash flow occurs one period from now), XNPV discounts from the first date in your date range. Your initial investment should be included in the values range.
Need some help?
Hint 1
Use XNPV with three inputs: annual discount rate, cash flow values, and matching cash flow dates.
Hint 2
For base-case project valuation, include operating investment/project flows and exclude financing-specific rows.
Hint 3
Your selected date/value ranges should begin at the initial investment row and end at the final payment row, with both ranges covering the same number of rows.