This Excel tutorial explains how to use Excel IRR Function to calculate Internal Rate of Return.
You may also want to read
Excel NPV Function to calculate Net Present Value
Internal Rate of Return
Internal Rate of Return (IRR) is the discount rate of cash flow at which an investment breaks even. Similar to Net Present Value (NPV), which discounts a series of cash flow back to present value.
For example, an initial investment is $10000, at the end of 1st year, the cash flow received is $1000, the 2nd year is $2000.
Year | Cash Flow |
0 | -10000 |
1 | 8000 |
2 | 10000 |
The net present value = -10000 + 8000/(1+IRR) + 10000/(1+IRR)2
We have to assume an IRR to calculate the net present value.
If we assume the net present value = 0, then we calculate IRR.
0 = -10000 + 8000/(1+IRR) + 10000/(1+IRR)2
IRR = 0.477
Excel IRR Function to calculate Internal Rate of Return
It is very difficult to calculate IRR without using financial calculator or computer. Excel has a function called IRR that you can use.
Syntax of Excel IRR Function
IRR(values, [guess])
values | Cash flow that contain at least one positive value and one negative value to calculate the internal rate of return. |
guess | Optional. A number that you guess is close to the result of IRR. If Excel fails to find a result after 20 tries, the #NUM! error value is returned. |
Example of Excel IRR Function