Incorrect Excel IRR formula results

How do I ensure that the IRR function in Excel is based on an annual result?

IRR in Excel assumes that all cash flows occur at equal time intervals. If the result is exactly half of the expected rate, the most common cause is that the cash-flow frequency and the interpretation of the result do not match.

Typical scenario:

• Cash flows in A1:A4 are monthly, but the expected IRR is an annual rate (or vice versa).
• =IRR(A1:A4) returns a periodic rate (per month, per quarter, etc.).
• If the expected “correct” result is an annual rate and the IRR is being compared directly without converting, the IRR can appear to be “half” (or otherwise off by a factor) depending on how the comparison value was derived.

How to fix/interpret:

1. Confirm the period of the cash flows in A1:A4 (monthly, quarterly, yearly).
2. Remember that IRR(A1:A4) returns the rate per period of those cash flows.
3. Convert the periodic IRR to the desired basis, for example:
   • If cash flows are semiannual and IRR returns 5% per period, the simple annual rate is =2*IRR(A1:A4) (≈10%).
   • If cash flows are monthly, an effective annual rate is =(1+IRR(A1:A4))^12-1.

Also ensure the input range meets IRR’s requirements:

• At least one negative and one positive value in A1:A4.
• Values are numeric (no text, no formatted currency symbols in the cells themselves).
• Cash flows are in the correct order (chronological).

If the numerical value is still not as expected, try supplying a different guess:

=IRR(A1:A4, 0.2)

Changing the guess can help IRR converge to a different valid root when multiple mathematical solutions exist.

References:

• IRR function
• How to correct a #NUM! error
• WorksheetFunction.Irr(Object, Object) Method