What to take into your next appraisal
- Identify the period before interpreting an IRR result.
- Annualise a monthly rate by compounding, not multiplying.
- Check signs, dates and NPV rather than trusting a displayed percentage.
Choose the calculation that matches the schedule
A monthly IRR is a monthly rate. Its effective annual equivalent is (1 + monthly IRR) raised to twelve, less one. A quarterly rate uses four periods. Multiplying the rate by the number of periods gives a different convention and is not the same effective annual return.
Microsoft documents XIRR for cashflows that are not necessarily periodic and uses a 365-day year in its discounting. Dates, signs and the initial guess can affect whether the iterative calculation converges. Keep the actual date series beside the cashflows.
Sources: Microsoft: XIRR function
Work through a dated two-cashflow example
An invented investor pays 1 million on 1 January 2027 and receives 1.1 million on 2 July 2027. The interval is 182 days. For these two cashflows, the annualised return is 1.10 raised to the power 365/182, less one, or approximately 21.06%.
A periodic IRR applied to the two amounts is 10% per assumed period. It is only a 10% annual return if that period is a full year. Both computations can be mathematically valid while answering different timing questions.
| Method | Timing assumption | Result |
|---|---|---|
| Two-value periodic IRR | One unspecified period | 10.00% per period |
| Dated annualised return | 182 days on a 365-day basis | 21.06% annually |
| Cash multiple | No timing information | 1.10× |
Verify the result independently
For a dated schedule, compute the sum of each cashflow divided by (1 + return) raised to days since the first cashflow divided by 365. At a valid XIRR solution, that sum should be near zero within a stated tolerance. For a simple two-payment example, compare with the direct exponent formula.
Reconcile gross contributions and distributions before reviewing the return. A missing initial cost or a duplicated sale receipt can generate a plausible-looking percentage. Confirm that spreadsheet dates are actual dates and not text labels, and that each amount still aligns with its date after sorting.
Recognise when a single IRR is misleading
Cashflows with multiple sign changes can have more than one internal rate of return or fail to produce a useful solution. Trying different initial guesses is a diagnostic, not permission to select the most attractive answer. Report NPV at the investor's stated hurdle and explain any ambiguity.
Very short holding periods can create striking annualised returns that are difficult to repeat. Keep the holding period and cash multiple visible. An annualisation is a mathematical comparison, not a prediction that identical investments can be reinvested for the rest of the year.
Preserve dates across model exports
When exporting a monthly model, state whether payments occur at month start, month end or on explicit transaction dates. A construction payment and a sale receipt in the same month need not have the same date. Where that difference matters, use a more detailed schedule rather than increasing displayed decimal places.
For quality control, test a one-year cashflow, the 182-day example, a zero-net-profit case and a schedule with no sign change. The last should produce an unavailable-return result with an explanation, not a fabricated zero. Preserve the cash schedule with the reported percentage so reviewers can reproduce it.
Sources and further reading
- XIRR function Microsoft
Published by Feasly. How we prepare our guides. Suggest a correction.
