IRR Function in Excel (Table of Contents)
IRR in Excel
Firstly, IRR is the short form of “Internal Rate of Return”. If you are a first time user then you must be wondering what this Internal Rate of Return is? So, let us understand about IRR Function in Excel.
IRR function is a measure used in financial modeling to estimate the profitability of the investment. In simple terms it the rate of return on your investment. You can think of IRR as the growth a project expected to generate.
Before straight head into the IRR concept, it is important to understanding Net Present Value (NPV). The amount we have today is worth more valuable than the amount we will receive after 3 years, after 5 years or after 10 years. Because money now is more valuable than money later on. Therefore, before investing it is always a good practice to know about how much that money worth today. This value is called Net Present Value (NPV).
IRR is the rate of interest that makes the sum of all cash flows zero and is useful compare one investment to another one. IRR will return the Internal Rate of Return for a series of cash flows. It does not matter whether it is a negative inflow of positive inflow.
IRR function is a financial function and very useful in financial modeling. It is often used by corporate people to compare and decide between capital projects. In excel all the outflows are denoted by negative numbers and all the inflows are denoted by positive numbers.
IRR Formula in Excel
Below is the IRR Formula in Excel :
Explanation of IRR Function in Excel
IRR Function in Excel consists of two things one is VALUES & GUESS.
4.5 (898 ratings)
View Course
- VALUES: This is a targeted range of your cash outflows and inflows. The range consists of initial investment and series of cash inflows (it could be positive or negative).
- GUESS: It is a guessed number by the user, which is close to the anticipated to the nearest return. If you ignore this optional argument, the function will take default value as 0.1 i.e. 10%. If at all IRR gives the #NUM error result, you need to change the value for ages.
Pro Tip: If you look into the second syntax, GUESS is included in parenthesis [], that means that argument is not a mandatory thing for this formula.
Example: Let us say you can get 15% PA interest on your $2,000. So $2,000 earns $300 in one year ($2,000*15% = $300). Your $2,000 is worth $2,300 in one year’s time. In simple words, $2,300 next year is worth only $2,000 now or present value of $2,300 next year is $2,000.
How to Use the IRR Function in Excel?
IRR Function in Excel is very simple and easy to use. Let understand the working of IRR Function in Excel by some IRR Formula example. IRR function can be used as a worksheet function.
IRR in Excel – Example #1
In this IRR Function in Excel example, let us consider the below table for our illustration, Mr. X started his project with an initial investment of Rs. 10,00,000/- and First 5 years cash inflow is 1 lakh, 2.5 lakh, 3 lakh, 3.5 lakh, and 2 lakh respectively. So now, calculate IRR for this investment made by Mr. X.
For 1st year’s IRR is -90%. That means Mr. X has recovered 10% of his investment by the end of the first year.
So the Result would be :
For 2nd year’s IRR is -45%. That means Mr. X has recovered 35% of his investment by the end of the second year. The thing is while applying the formula you need to select Initial Investment + First Year Inflow + Second Year Inflow.
Result is :
For 3rd year’s IRR is -17%. That means Mr. X has recovered 83% of his investment by the end of the third year. The thing is while applying the formula you need to select Initial Investment + First Year Inflow + Second Year Inflow + Third Year Inflow.
Result is :
For the 4th year, while applying the formula, you need to select Initial Investment + First Year Inflow + Second Year Inflow + Third Year Inflow + Fourth Year. That will give you an IRR of 0.00%. So, Mr. X has recovered his entire investment by the end of the 4th year.
So the Result will be :
For a 5th year, while applying the formula, you need to select Initial Investment + First Year Inflow + Second Year Inflow + Third Year Inflow + Fourth Year + Fifth Year Inflow. That will give you an IRR of 6%. So Mr. X has gained 6% ROI from this project.
The Result will be :
Now MR x will decide that to gain an income of 6% from this project for 5 years is it worth an investment? Off course it is not worth, so Mr. X now can look for other portfolios to invest his money make a better IRR.
IRR in Excel – Example #2
In this IRR Function in Excel example, Mr. Karanth has an investment of 1 lakh USD today and he wants to invest in one of the projects and below table is the information on Outflows and Inflows.
If you look at the table, investment is for 7 years, the initial outflow is $1, 00,000, and on the 3rd year, again he introduced an additional capital of $5,000. Now calculate Internal Rate of Return from this investment.
IRR from this investment is 10.75%.
ERROR HANDLING
Below are the certain things you need to look into before applying IRR formula in Excel.
- If the initial outflow value not shown as negative, Excel will recognize it, as there is not an investment for this project.
we will get a #NUM error.
- If all the values are negative, Excel thinks there is no inflow or income to this project.
we will get a #NUM error.
Things to remember about the IRR Function in Excel
- So in order to get accurate result range of numbers must have a minimum of one negative and one positive value.
- For syntax [GUESS] if the value is not provided by the user by default Excel will treat it as 0.1 which is equal to 10%.
- IRR is always dependent on Net Present Value (NPV). A rate of return given by IRR function is the return rate equivalent to a zero NPV.
- It does not always equal the annual compound rate of return on an initial investment.
Recommended Articles
This has been a guide to IRR in Excel. Here we discuss the IRR Formula in Excel and how to use IRR Function in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
