Internal Rate of Return (IRR) Calculations with Excel

Understanding the Internal Rate of Return (IRR) is paramount in financial analysis, as it provides insight into the potential profitability of an investment. Excel offers a user-friendly platform to compute IRR, allowing analysts to evaluate investment opportunities with precision and efficiency. In this article, we will delve into the fundamentals of IRR calculations with Excel, exploring its methodology, practical applications, and tips for effective utilization.

 

What it Means and How to Calculate it in Excel

There are a couple of methods one can use to calculate ROI (Return on Investment); net present value, payback, breakeven,  and internal rate of return.

In textbooks, the Internal Rate of Return (IRR) is defined as the discount rate that makes the net present value (NPV) of all future cash flows of an investment zero.  In other words, IRR is the expected compound annual rate of return that will be earned on a project or investment. Using IRR to obtain net present value is known as the discounted cash flow method of financial analysis.  This rate is internal since it doesn’t take external factors like risk or inflation into account.

Business owners and executives use IRR as a guideline for deciding whether to proceed with a project or investment. A good IRR is higher than the minimum acceptable rate of return, also known as discount rate, hurdle rate, or cost of capital. In other words, if your minimum acceptable rate of return is 12% and the IRR for a project is 20%, then this is a good IRR relative to your minimum acceptable rate of return. But if the IRR for a project is only 10%, then this is not a good IRR, and the best course of action would be to forego the project or investment.

To make IRR more intuitive, let’s go on with a real-world example.

 

Expansion Project or New Machines

SinSin, a manufacturer of textiles is considering either purchasing a new machine or embarking on an expansion project. Both projects would initially be costly but later boost the company’s profits. The projected cash inflows and outflows for each proposed project are listed in an Excel file. The management like to know the estimated return on each investment. 

 

In the upcoming sections of this article, we will delineate the foundation for calculating the IRR for these two scenarios in Excel and help the company make the best use of its cash.

This article covers the meaning of the Internal Rate of Return, the IRR formula, how to calculate IRR in Excel, when it’s used, and FAQs.

The reader of this article is supposed to have a working knowledge of Excel and be familiar with Net Present Value and the basic concepts in finance. 

 

Internal Rate of Return Formula

To repeat the definition, IRR is the discount rate at which the net present value (NPV) breaks even. This means that the formula of IRR solves for the interest rate that sets the Net Present Value equal to zero.

In most cases, IRR is calculated on a trial-and-error basis. This is accomplished by iteratively using an estimate of IRR in the NPV formula. This process repeats until a discount rate is found that sets the net present value equation equal to zero. It’s indeed not a straightforward calculation.

A more efficient alternative is to use a financial calculator or three Excel functions for internal rate of return, including IRR, XIRR, and MIRR instead.

 

Calculating IRR in Excel

Excel IRR Function

For the IRR function, the interest rate is calculated assuming a stream of equally spaced cash flows.

Syntax and Arguments

IRR(values, [guess])

• values: Required. An array or a reference to cells that contain the cash flows for which we want to calculate the IRR.
  • The array must contain at least one positive and one negative value. Otherwise, IRR returns the #DIV/0! error value.
  • The cash flows must be set evenly and in chronological order.
  • If the array or reference of values contains text, logical, or empty cells, those values are ignored. However, cells with the value zero are included.
• guess: Optional. A number that you guess is close to the result of IRR.
  • Excel calculates IRR iteratively. Starting with guess, IRR loops through the calculation until the result is accurate within 0.00001 percent. After 20 tries, if IRR can’t find a result, the #NUM! error is returned.
  • In most cases, you do not need to provide a value for guess. If omitted, it is assumed to be 0.1 (10 percent).
  • If IRR gives the #NUM! error value, or if the result is not close to your expected value, try again with a reasonable value for guess. For example, if you have monthly or weekly cash flows, then you may need to use a guess that is much smaller than the default 10%.

 

 

As you can see in the screenshot above, the IRR for the Expansion Project is significantly more than the IRR for the New Machines project. This means that the former project would be more profitable.

 

Calculating XIRR in Excel

XIRR function is preferable over the IRR function as it has more flexibility by not being restricted to equally spaced cash flows.

Syntax and Arguments

XIRR(values, dates, [guess])

• values: Required. An array of cash flows or a reference to a range of cells containing cash flows.
  • The array must contain at least one positive and one negative value. Otherwise, XIRR returns the #DIV/0! error value.
  • If the array or reference of values contains text, logical, or empty cells, those values are ignored. However, cells with the value zero are included
• dates: Required. A series of dates that corresponds to the cash flow payments. Dates may occur in any order. Dates should be entered by using the DATE function, formatted as date, or as a result of other formulas or functions. If dates are entered as text, problems may arise.
• Guess Optional. A number that you guess is close to the result of XIRR.

 

Calculating MIRR in Excel

MIRR is a modified internal rate of return. Like the IRR function, MIRR uses periodic cash flows and also assumes the reinvestment of cash in the calculation.

Syntax and Arguments

MIRR(values, finance_rate, reinvest_rate)

• values Required. An array of cash flows or a reference to a range of cells containing cash flows occurring at regular periods.
  • The array must contain at least one positive and one negative value.  Otherwise, MIRR returns the #DIV/0! error value.
  • If the array or reference of values contains text, logical, or empty cells, those values are ignored. However, cells with the value zero are included.
• Finance_rate: Required. The interest rate paid on amounts borrowed to finance cash flows.
• Reinvest_rate: Required. The interest rate you receive on the cash flows as you reinvest them.

 

How Internal Rate of Return is Used

The general rule of thumb says that an investment is worth pursuing if it has a higher IRR than the company’s hurdle rate. When comparing two options, investors usually choose the investment with the higher IRR. However, to be more specific, whether an IRR is good or not depends entirely on the company itself. Each company has a hurdle rate, or the minimum rate of return they need from an investment for it to be worthwhile. Some companies rely on the rate of return expected by shareholders, while for others their hurdle rate is the same as their weighted average cost of capital (WACC). 

On the other hand, IRR alone is usually not enough to comprehensively assess an investment option. That’s why financial analysts use it in combination with other factors like economic conditions, the company’s risk tolerance, and the total duration of the potential investment. 

It is recommended that IRR be used in conjunction with net present value (NPV) to make better investment decisions. 

 

Is the IRR Rule Always Enforced?

In general, the higher the IRR, the better. However, the IRR rule may not always be rigidly followed. A company may prefer an investment with a lower IRR as while as it still exceeds the cost of capital, because the investment has other intangible benefits, such as contributing to a bigger strategic plan, impeding competition, or simply lowering risks. Companies ultimately consider several factors when deciding whether to proceed with a project. There may be factors that outweigh the IRR rule. It’s all about trade-offs.

 

Benefits and Limitations of IRR

Benefits

• Is easy to calculate and understand
• Takes time value of money into account
• Allows for comparison between other investments
•  

Limitations

• Doesn’t return the dollar value
• Doesn’t account for anomalies in cash flows

 

 

Conclusion

By understanding the methodology, practical applications, and nuances of Excel-based IRR computations, analysts can assess investment opportunities, evaluate project profitability, and optimize resource allocation effectively. Excel serves as a versatile tool, providing a user-friendly interface for performing complex financial analyses, including IRR calculations. As businesses strive for sustainable growth and profitability, leveraging Excel for IRR computations becomes indispensable in navigating the dynamic landscape of finance.

 

FAQ