Should I use XIRR or NPV to decide if a project is worth doing?

Use XIRR to find the rate of return (useful for comparing projects); use NPV to find the present value at a specific discount rate (better for go/no-go decisions). In the example, 88% IRR looks excellent, but NPV depends on your cost of capital—if you can borrow at 10%, the project delivers strong positive NPV.

Can XIRR return negative values, and what does that mean?

Yes; a negative IRR means you lose money overall. For instance, if you received $36,100 upfront and paid $87,000 later, XIRR would return approximately -0.88, indicating an 88% loss over the period.

Why does my XIRR formula show #NUM! when I have valid data?

This typically means the cash flows lack a mathematical solution (e.g., all positive values, or an unsolvable payment pattern). Verify at least one value is negative. If data is correct, try the optional guess parameter: =XIRR(values, dates, 0.2) to start convergence from a different assumption.

How is annualized IRR different from simply dividing total profit by months?

Total profit divided by time (roughly 84% over 5 months) is simple arithmetic; XIRR compounds the effective rate and factors in exact timing of each cash flow. An early payment amplifies returns. XIRR's 88% reflects that the $38,000 arrives midway, concentrating returns earlier than if all $87,000 arrived at project end.

XIRR function

XIRR returns the annualized internal rate of return for cash flows at irregular date intervals, letting you evaluate project or investment profitability over time.

=XIRR(values, dates, [guess])

Generate a XIRR formula

Describe what you need. The generator will reach for XIRR where XIRR is the right tool, and tell you when it is not.

Arguments

How XIRR reads its arguments

Argument	Required	Description
values	Required	Required array of cash flows; must contain at least one negative value (representing costs or investment) and one positive value (returns or revenue); order must match dates.
dates	Required	Required array of dates matching each cash flow value; must be in chronological order and formatted as valid date values; determines timing for discount calculations.
guess	Optional	Optional starting estimate for the IRR iteration (default 0.1); provide only if XIRR returns #NUM! error or to override default convergence behavior.

Returns

A decimal value representing the annualized percentage rate (e.g., 0.88 for 88%); requires at least one negative and one positive cash flow.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Calculate overall project annual internal rate of return

Task	Owner	Start Date	Due Date	Hours
Planning & Setup	Alice	2024-01-08	2024-01-12	20
UI/UX Design	Bob	2024-01-15	2024-02-16	80
Frontend Development	Carol	2024-02-19	2024-04-12	120
Backend Integration	Alice	2024-02-26	2024-04-26	100
Testing & QA	Bob	2024-05-01	2024-05-24	60
Documentation & Launch	Carol	2024-05-27	2024-06-07	40

=XIRR({-36100, 21000, 38000, 28000}, {DATE(2024,1,15), DATE(2024,2,28), DATE(2024,4,30), DATE(2024,6,15)})

Result: 0.88

The project costs $36,100 (total labor: 20@$75 + 80@$85 + 120@$95 + 100@$75 + 60@$85 + 40@$95) and generates $87,000 in client milestone payments over 5 months. XIRR calculates that this irregular cash flow pattern yields an 88% annualized return rate.

2. Evaluate IRR for an early project phase completion

Task	Owner	Start Date	Due Date	Hours
Planning & Setup	Alice	2024-01-08	2024-01-12	20
UI/UX Design	Bob	2024-01-15	2024-02-16	80
Frontend Development	Carol	2024-02-19	2024-04-12	120
Backend Integration	Alice	2024-02-26	2024-04-26	100

=XIRR({-27200, 21000, 38000}, {DATE(2024,1,15), DATE(2024,2,28), DATE(2024,4,30)})

Result: 1.42

If the project delivered only the first three phases (Planning, Design, Frontend, Backend), the total cost drops to $27,200 with two milestone payments totaling $59,000 by April 30. This shorter timeline and faster payoff yields a significantly higher 142% annualized return, demonstrating how project scope affects IRR.

3. Assess IRR impact when final milestone payment is delayed

Task	Owner	Start Date	Due Date	Hours
Planning & Setup	Alice	2024-01-08	2024-01-12	20
UI/UX Design	Bob	2024-01-15	2024-02-16	80
Frontend Development	Carol	2024-02-19	2024-04-12	120
Backend Integration	Alice	2024-02-26	2024-04-26	100
Testing & QA	Bob	2024-05-01	2024-05-24	60
Documentation & Launch	Carol	2024-05-27	2024-06-07	40

=XIRR({-36100, 21000, 38000, 28000}, {DATE(2024,1,15), DATE(2024,2,28), DATE(2024,4,30), DATE(2024,8,15)})

Result: 0.53

With identical costs and total revenue, moving the final $28,000 payment from June 15 to August 15 extends the project timeline to 7 months. The delayed final payment dramatically reduces the annualized return to 53%, demonstrating how payment timing significantly impacts IRR calculations.

Common errors

Which XIRR error are you seeing?

Error	Why it happens	How to fix it
#NUM!	XIRR cannot mathematically converge on a solution, often because all cash flows are the same sign (all positive or all negative) or the cash flow pattern lacks an equilibrium rate.	Verify the values array contains at least one negative cash flow (initial investment) and one positive cash flow (return). Check for unrealistic values that prevent a valid IRR from existing.
#VALUE!	The values array contains text, logical values, or empty cells instead of numeric cash flows, or the dates array contains non-date values.	Ensure all values are numbers and all dates are properly formatted as DATE() functions or date serial numbers. Remove any text, blanks, or TRUE/FALSE values from both arrays.
#REF!	The formula references cells or ranges that have been deleted or moved, breaking the array references in the values or dates parameter.	Update the values and dates array references to point to valid cell ranges. Use absolute references (with $) if copying the formula to prevent range shifts.

Tips and when to use something else

Always include at least one negative cash flow (initial investment/cost) at the start and one positive cash flow (return/revenue) to calculate a meaningful rate.
Keep dates in strict chronological order from earliest to latest; XIRR will not reorder them and may produce incorrect results or #NUM! error if out of sequence.
For regular monthly or quarterly cash flows with consistent intervals, use IRR instead—it's simpler and automatically assumes regular spacing.
Combine XIRR with scenario analysis or IF statements to compare returns across different payment timings, amounts, or project scopes.

Related functions

NPVNPV returns the net present value of a series of cash flows discounted to their present value today, helping you decide if an investment creates value.XNPVXNPV returns the net present value of cash flows occurring at irregular intervals, discounting each flow by actual elapsed time.IRRReturns the internal rate of return for a series of regular periodic cash flows, measuring annualized investment profitability.MIRRMIRR returns the modified internal rate of return, accounting for different borrowing and reinvestment rates on cash flows over time.RATERATE calculates the interest rate per period for an investment or loan given regular periodic payments and present/future values.

Frequently asked questions

Should I use XIRR or NPV to decide if a project is worth doing?: Use XIRR to find the rate of return (useful for comparing projects); use NPV to find the present value at a specific discount rate (better for go/no-go decisions). In the example, 88% IRR looks excellent, but NPV depends on your cost of capital—if you can borrow at 10%, the project delivers strong positive NPV.
Can XIRR return negative values, and what does that mean?: Yes; a negative IRR means you lose money overall. For instance, if you received $36,100 upfront and paid $87,000 later, XIRR would return approximately -0.88, indicating an 88% loss over the period.
Why does my XIRR formula show #NUM! when I have valid data?: This typically means the cash flows lack a mathematical solution (e.g., all positive values, or an unsolvable payment pattern). Verify at least one value is negative. If data is correct, try the optional guess parameter: =XIRR(values, dates, 0.2) to start convergence from a different assumption.
How is annualized IRR different from simply dividing total profit by months?: Total profit divided by time (roughly 84% over 5 months) is simple arithmetic; XIRR compounds the effective rate and factors in exact timing of each cash flow. An early payment amplifies returns. XIRR's 88% reflects that the $38,000 arrives midway, concentrating returns earlier than if all $87,000 arrived at project end.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17