The tutorial shows how to calculate IRR of a project in Excel with formulas and the Goal Seek feature. You will also learn how to create an internal rate of return template to do all IRR calculations automatically.
When you know the internal rate of return of a proposed investment, you may think you have all you need to evaluate it - the bigger the IRR the better. In practice, it's not that simple. Microsoft Excel provides three different functions to find the internal rate of return, and truly understanding what you are actually calculating with IRR will be very helpful.
The internal rate of return (IRR) is a commonly used metric to estimate the profitability of a potential investment. Sometimes, it is also referred to as discounted cash flow rate of return or economic rate of return.
Technically, IRR is the discount rate that makes the net present value of all cash flows (both inflows and outflows) from a certain investment equal to zero.
The term "internal" indicates that IRR takes into account only internal factors; external factors such as inflation, the cost of capital and various financial risks are excluded from calculation.
In capital budgeting, IRR is widely used to evaluate the profitability of a prospective investment and rank multiple projects. The general principle is as simple as this: the higher the internal rate of return, the more attractive the project is.
When estimating a single project, finance analysts typically compare the IRR to a company's weighted average cost of capital or hurdle rate, which is the minimum rate of return on an investment that the company can accept. In a hypothetical situation, when IRR is the only criterion for making a decision, a project is considered a good investment if its IRR is greater than the hurdle rate. If the IRR is lower than the cost of capital, the project should be rejected. In practice, there are a lot of other factors that influence the decision such as the net present value (NPV), payback period, absolute return value, etc.
Though IRR is a very popular method for assessing capital projects, it does have a number of inherent flaws that may lead to suboptimal decisions. The main problems with IRR are:
Despite these deficiencies, IRR continues to be an important measure of capital budgeting and, at the very least, you should cast a skeptical look at it before making an investment decision.
As the internal rate of return is the discount rate at which the net present value of a given series of cash flows is equal to zero, the IRR calculation is based on the traditional NPV formula:
If you are not very familiar with the summation notation, the extended form of the IRR formula may be easier to understand:
Where:
The nature of the formula is such that there is no analytical way to calculate IRR. We have to use the "guess and check" approach to find it. To better understand the concept of the internal rate of return, let's perform an IRR calculation on a very simple example.
Example: You invest $1000 now and get back $500 and $660 in the next 2 years. What discount rate makes the Net Present Value zero?
As our first guess, let's try 8% rate:
Adding those up, we get the NPV equal to $28.81:
Oh, not even close to 0. Maybe a better guess, say 10%, can change things?
That's it! At 10% discount rate, the NPV is exactly 0. So, the IRR for this investment is 10%:
That's how you calculate the internal rate of return manually. Microsoft Excel, other software programs and various online IRR calculators also rely on this trial and error method. But unlike humans, computers can do multiple iterations very quickly.
Microsoft Excel provides 3 functions for finding the internal rate of return:
Below you will find the examples of all these functions. For the sake of consistency, we will be using the same data set in all the formulas.
Suppose you are considering a 5-year investment with the cash flows in B2:B7. To work out the IRR, use this simple formula:
=IRR(B2:B7)
For more information, please see the Excel IRR function.
In case of cash flows with unequal timing, using the IRR function can be risky, as it assumes that all payments occur at the end of a period and all time periods are equal. In this case, XIRR would be a wiser choice.
With the cash flows in B2:B7 and their dates in C2:C7, the formula would go as follows:
=XIRR(B2:B7,C2:C7)
For more details, please see the Excel XIRR function.
To handle a more realistic situation when the project funds are reinvested at a rate closer to a company's cost of capital, you can compute the modified internal rate of return by using a MIRR formula:
=MIRR(B2:B7,E1,E2)
Where B2:B7 are cash flows, E1 is the finance rate (the cost of borrowing the money) and E2 is the reinvest rate (the interest received on the reinvestment of earnings).
I believe no one can give a generic answer to this question because the theoretical basis, advantages and drawbacks of all three methods are still disputed among finance academics. Perhaps, the best approach would be to do all three calculations and compare the results:
Generally, it is considered that:
If you need to do IRR calculation in Excel on a regular basis, setting up an internal rate of return template can make your life a lot easier.
Our calculator will include all three formulas (IRR, XIRR, and MIRR) so that you won't have to worry which result is more valid but could consider them all.
Assuming your worksheet is named Sheet1, the first cash flow (initial investment) is in cell A2, and the date of the first cash flow is in cell B2, make the named ranges based on these formulas:
Cash_flows:
=OFFSET(Sheet1!$A$2,0,0,COUNT(Sheet1!$A:$A),1)
Dates:
=OFFSET(Sheet1!$B$2,0,0,COUNT(Sheet1!$B:$B),1)
The detailed steps can be found in How to create a dynamic named range in Excel.
=IRR(Cash_flows)
=XIRR(Cash_flows, Dates)
=MIRR(Cash_flows, Finance_rate, Reinvest_rate)
Done! You can now input any number of cash flows in column A, and your dynamic internal rate of return formulas will recalculate accordingly:
As a precaution against careless users who may forget to fill all the required input cells, you can wrap your formulas in the IFERROR function to prevent errors:
=IFERROR(IRR(Cash_flows), "")
=IFERROR(XIRR(Cash_flows, Dates), "")
=IFERROR(MIRR(Cash_flows, Finance_rate, Reinvest_rate), "")
Please keep in mind that if the Finance_rate and/or Reinvest_rate cells are blank, the Excel MIRR function assumes they are equal to zero.
The Excel IRR function only performs 20 iterations to arrive at a rate and XIRR performs 100 iterations. If after that many iterations a result accurate within 0.00001% is not found, a #NUM! error is returned.
If you are looking for more accuracy for your IRR calculation, you can force Excel to do over 32,000 iterations by using the Goal Seek feature, which is part of What-If Analysis.
The idea is to get Goal Seek to find a percentage rate that makes the NPV equal to 0. Here's how:
=NPV(B9,B3:B7)+B2
When done, click OK.
Click OK to accept the new value or Cancel to get back the original one.
In a similar manner, you can use the Goal Seek feature to find XIRR. The only difference is that you will need to use the XNPV formula instead of NPV.
That's how to do IRR calculation in Excel. To have a closer look at the formulas discussed in this tutorial, you are welcome to download our sample workbook to Calculate IRR in Excel. I thank you for reading and hope to see you on our blog next week!
2 responses to "How to calculate IRR in Excel with formulas, template and Goal Seek"
Dear Svetlana,
Thank you for such enlightening write-ups. Much appreciated.
I'm trying to find out the real-world realized returns of my ETF investments. It is always represented in Annualized return, which is not the "actual" return that I might be getting due to market volatility. To me, I believe, IRR is the actual realized return. Correct me if I'm wrong.
How do I make an IRR calculation sheet for daily cash flow, because I randomly buy funds at multiple intervals.
Kindly advise.
Thank you!!!