Free Online Tutorials on Spreadsheets

IRR function in Excel


The IRR (Internal Rate of Return) function categorized as one of the Financial function. It calculates the internal rate of return for an investment having subsequent cash inflows occurring at regular intervals, such as monthly or annually. The internal rate of return (IRR) is the discount rate at which the net present value of all the cash flows (both positive and negative) from an investment equals to zero.


IRR(values, [guess])

The IRR function has the following arguments:

value (Required) means the reference to the cell ranges that contain payments (negative values) and income (positive values) for which you want to calculate the IRR.

[guess] (Optional) means a number you guess is close to the result of IRR. If omitted, Excel uses 0.1 (10 percent) as default.


In the example below, an initial investment of 100,000/- is shown in cell B3 and positive income streams are shown in cells B4:B13. The IRR function in cell B15 shows the calculation after 10 years.


Function Errors

Error Occurs
#NUM! If the result is not close to what you expected, try again with a different value for guess.
If the supplied values reference does not contain at least one positive and negative value.
If the function unable to find a result after 20 tries.

Related Function

NPV function calculates the net present value of an investment by using a discount rate and a series of future payments.

XIRR function calculates the internal rate of return for an investment having cash flows occurring at irregular intervals.

Copyright © 2016 - 2019 | All Rights Reserved