The internal rate of return or most commonly known as the IRR is a metric utilized in capital budgeting to determine the profitability of potential ventures. It is the discount rate that turns the NPV or the net present value of all cash flows equal to zero. Therefore, the calculation of the IRR heavily relies on the same formula as the NPV does.
Here, we will discuss IRR how to calculate in Excel, formula, significance, and uses as well as example templates that you can use as a reference.
The Formula and Calculation
There isn’t really a concrete way of calculating the IRR especially when calculating it by hand, you will have to go through countless trial and error just to arrive at whatever rate which turns the NPV equal to zero. As mentioned above, the calculation of the IRR relies on the calculation of the NPV. Hence, we will then apply the same formula to determine the NPV equals zero.
See below to see the IRR Formula.
To calculate the IRR using the formula shown above, the value of NPV is automatically set to zero. And as you can see, due to the nature of the formula, the IRR can’t be calculated analytically, hence, you will either need to go through trial and error or make use of tools such as MS Excel which has a featured function to calculate the IRR.
How to Calculate IRR using the Formula on Excel
The Excel IRR function is one of the financial functions available in Excel that helps to calculate the IRR easier compared to doing it manually. The IRR function, basically, returns the IRR for a series of cash flows that occur at regular intervals.
Syntax / Function Formula: = IRR ( values, [guess] )
Values – stands for an array or reference to cells that contain values that represent the series of cash flows and include the investment as well as the net income values. It is required that you will input or provide these data for the function to work.
Guess – an estimate for the expected IRR or the number you assumed that is closer to the expected IRR. It is, however, optional to input data here since the function can simply take a default value of 10%.
Using the IRR Function in Excel, there are two scenarios in which you can calculate the IRR.
- If the expected cash flows are constant
- If the project has a compounded growth rate
The internal rate of return is known to be a time value of money metric which represents the annual rate of earnings of a venture, therefore, the IRR is dependent on cash flows of the project. To understand the logic of IRR how to calculate in Excel, refer to the examples below.
Scenario 1: Let us assume Project A has an initial investment of $100 000 and expects constant cash flow of $25 000 for 5 years.
The first step would be to put the cash flows in a standardized format. Once the Table of cash flows is prepared, we will then use the IRR function to determine the IRR with the assumption of a 10% discount rate as shown below.
The initial investment is negative since it is considered as an outgoing payment. The values after that are the cash inflows that are expected for up to 5 years of operation. By applying the formula as shown in the illustration above, calculating the IRR became easier. Though this isn’t actually perfect since it is not designed to calculate compounded growth rate cash flows, you can still calculate the IRR as described in the next scenario example.
Scenario 2: Assuming the initial investment is $50 000 with expected varied cash flows $15 000, $17 000, $18 000, $10 000, and $10 000 for each corresponding year.
Following the format of scenario one, you will first need to format your cash flows into a table in Excel and then apply the IRR formula for a compounded annual growth rate or also known as the CAGR.
CAGR = ( End Value / Start Value ) ^ ( 1 / Periods ) – 1
End Value – Last
Start Value – First positive cash inflow
Periods – Number of Years
Here is how it looks like in Excel:
As you noticed, both scenarios returned with an IRR value of less than 10%. This means that the investment or project is not feasible or attractive. It is important that before deciding on investing, you must determine if the resulting IRR is greater than the expected 10% which is the normal rate for a feasible investment.
Now, see another example if we apply this to a financial model to conduct an IRR Analysis but this time with a project that is feasible and attractive to investors. Refer to the screenshot below which shows an overview of an IRR Analysis.
As shown in the example above, the IRR is equal to 27.3% and as you recall, the IRR is the discount rate used to turn the NPV equal to zero. It is important to take note that we only arrived with a resulting discount rate of 27.3% by going through iterative trial and error until the value of NPV equals zero. This can be also derived by using the goal seek function in Excel or applying the IRR formula and apply it to the range of projected Free Cash Flows to Firm to determine the discount rate.
What is the Significance of Calculating the IRR and its Uses
The IRR is one of the most used profitability measures since it is easy to understand and easy to compare to the required return. Many companies tend to take on various projects to either increase revenues or cut down costs and with the help of calculating the IRR, they will be able to determine which projects they should invest in and which projects’ budgets will be deducted. Thus, the IRR became a metric mostly used for capital budgeting.
It is evident that IRR is a much more useful tool when used to conduct comparative analysis. Basically, the greater the value of IRR of a project, the more desirable it is to undertake it. Due to this, it is easier to determine which projects will be feasible and will gather more earnings in the future. Of course, the IRR is also very useful to calculate when deciding to start a new project or to end a project. Though the IRR result may vary, as long as it is higher than the expected return rate then it is a worthy project to invest in.
But, it is also important to take note that this is just an assumption and not actual. There could be certain assumptions that drive the resulting IRR into a higher or lower percentage. Thus, this doesn’t always mean that the project will be feasible due to the timing and size of cash flows, leveraged used, and other differences in the return assumptions.
Despite the disadvantages of calculating the IRR, conducting an IRR Analysis can be used in many cases such as the following:
- Determining the profitability of a project or investment.
- Maximizing the Net Present Value as an indication of profitability, efficiency, quality, and yield of an investment or project.
- Calculating the Fixed Income ( calculating yield to maturity and yield to call)
- Measuring new debt in terms of yield to maturity (used often when new debts are raised for funding new projects).
- Evaluating share issues and stock buyback programs.
- Measuring the General Partner’s performance (Limited Partner’s perspective).
- Analyzing projects or investments for P&E and Venture Capital with multiple investments.
- Determining the returns of the projects or investments for Comparison Analysis.
- Understanding risk tolerance, investment needs, and avoiding bad/risky investments or projects.
References and Example Financial Model Templates with IRR Calculation
If you want to integrate the calculation of IRR into your business plan, it is always best to present it in a complete financial model with an IRR Analysis section. Though this can be a time-consuming task as well as requires you to be skilled in financial modeling, this doesn’t mean that you can’t do it yourself. Try using available tools that are in Excel instead such as financial model templates with IRR Analysis.
Not only are the templates can be used as a starting base of your own financial model but it can also be used as a reference if you plan to build a financial model in the future that needs an IRR calculation. These templates are also designed by expert financial modelers with the right know-how and experience in building financial models for different kind of users such as business executives, CFOs, entrepreneurs, and many other entities from countries such as the USA, Germany, Australia, Canada, Japan, and many more that are in need of assistance when it comes to financial modeling.
If you are looking for financial model templates with IRR Analysis, simply check out the list below:
This Financial Model Template in Excel provides an easy way to forecast the financial performance for a Mobile App or similar Software. It focuses on revenues from paid downloads, in-app purchases, subscriptions and advertisement income
The Solar Energy Financial Model Excel template forecasts the expected financials for a Solar Park project and calculates the relevant project metrics to determine its financial feasibility. Pro and Premium model versions also include the developers ... read more
The Pharma Biotech Valuation Model Template calculates the risk-adjusted DCF Value of a Pharma or Biotech Startup Company with several products under development. The product forecasts are probability adjusted to take into account the success probabi... read more
The commercial real estate valuation model template assists to run a professional DCF Valuation for a commercial property such as an office building, industrial site, logistics building, storage or a retail building. The model comes as BASIC, PRO and... read more
This is a financial model template for a new private school startup business. The Excel model allows forecasting the cash flows over the next 10 years for a School Startup offering Kindergarten, Middle School, and High School classes and calculates e... read more
Feel free to read the article about the differences between IRR vs NPV.