Excel’s IRR() function is a straightforward way to calculate the rate of return for investments which have regular return/payment dates with amounts which might vary. Such as a simple fixed-interest bond, where cash flows occur at regular intervals such as monthly or yearly.
When expressed as a percentage return, it’s a lot simpler to understand. Generally, you would consider a project if the IRR is greater than the cost of capital.
IRR has been in Excel for well over a decade. Excel 365, 2021, 2019 and 2016 for Windows and Mac. Excel online (in a browser). Excel 2013, 2010 and 2007 for Windows
For investments with non-regular returns, use XIRR() instead, see Easily calculate stock and asset returns in Excel
Simple IRR() example
Here’s a simple example of IRR. An initial investment of $10,000 on the 1st of June 2015 and repaid with $1,000 interest on 1 July seven years later. The rate of return is 10% (obviously) over the total time.
Dates don’t matter in IRR()
Here’s another example with the $1,000 income paid separately and IRR assumes that comes at the halfway point in time between the two $10,000 capital movements.
You might think this calculation is wrong and the result should be 10% – if the $10,000 is repaid the same day as the interest. But no, the calculation is correct because IRR() doesn’t care about dates.
Look at the data passed into IRR(), it’s just a list of values. IRR considers each value to represent another point in a unstated time period. As far as IRR is concerned there’s a single period of time starting with the first value and ending with the last with the third value being half-way between the two.
The time period can be anything – a day, month, four-weeks, quarter, year or even decade! As long as the intervals are equal. (If not, turn to XIRR() which handles both dates and values).
Practical IRR() example
Let’s move on to more practical examples of IRR.
This example shows costs at $2,500 per year, the rate of return becomes -5.79% or -0.579.
All we’ve done is passed into IRR() the full range of net money movements along row 16 and it’s done the calculation. Yes, the columns are headed ‘Year’ but as we’ve mentioned, it could be any length. Note: IRR has used the Net Cash Flow row only. If we’d passed in each value (e.g B12:G16) that would be wrong since each value would be assumed to be a new time period.
An initial cost of $3000, Income (inflows) of $3000 plus each period, with Costs (Outflows) of $2500 plus each year. See how the rate of return IRR changes each period/year.
This produces a rate of return at 4.8% or 0.048 after 5 years without including any value from the sale of the asset at the end.
IRR() syntax and rules
The IRR syntax is
IRR(values, [guess])
Values The cash flows, starting with the initial investment as a negative number. Investments are negative values while any returns (interest, dividends, sale of the asset) are positive values.
The order of the values is important.
Guess is optional and defaults to 10% (0.1) but can be vital if the result might be well beyond that. It’s a number that you think is close to the result of IRR.
IRR() Notes
- Values must contain at least one positive value and one negative value to calculate the internal rate of return.
- IRR is quite tolerant of empty rows and just ignores them.
- The order of values matters. Enter payments and incomes in correct order, one for each time period.
The IRR guess can be important
The guess value can be important because Microsoft has limited IRR to 20 tries or iterations. After 20 tries, IRR returns a #NUM error if it can’t get a result.
If you’re getting a #NUM error using IRR, one possible reason is the guess. Trying to add a reasonable guess value if the possible result is very high or very low.
The guess only has to be very roughly correct. The default is 10% or 0.1 in these days of low or even negative returns a guess of 0.01 (1%) might be necessary.
In theory, a more accurate guess allows Excel to run faster (i.e fewer iterations). Still, it’s unlikely to make a difference unless you have many, many IRR functions in a workbook.
Easily calculate stock and asset returns in Excel
Three new performance boosts for Excel 365
Clever picture borders in Excel and PowerPoint
