Tag: IRRInternal rate of return (IRR) analysis allows to compare and investment projects vs. their cost of capital. Here, we listed all financial model templates with the calculation of IRR included or other use cases where the analysis and calculation of IRR are projected. Below the list, we added information about what is IRR, what are its uses and significance, as well as explaining how to calculate IRR.
Starting at: $0.00
This is an Excel financial model template for a single blockbuster product where the founders need to raise funding from investors to setup production capacity for market launch.
What is IRR?
The internal rate of return or also known as the IRR is the interest rate at which the net present value (NPV) of all the cash flows from a project equal zero. The term internal was derived from the fact that it excludes external factors such as inflation, cost of capital, or other various financial risks. In other words, the internal rate of return is the expected rate of return that will be earned on a project. It is usually used to evaluate if the project or investment is attractive. Basically, if the IRR of a new project exceeds a company’s required rate of return then that project is desirable, otherwise, the project is not worth it.
Another way of calling the internal rate of return of a project or investment is the “annualized effective compounded return rate” which sets the NPV of all cash flows, both positive and negative, from the project equal to zero. Therefore, it is the interest rate at which the NPV of the forecasted cash flows is equal to the initial investment and also it can be the discount rate at which the total present value of expenses (costs) equals to the total present value of the positive cash flows. Basically, the IRR is designed to account for the time preference of money and investments since a given ROI (return on investment) is worth more at a given time compared to the same amount of return received at a much later time.
In conclusion to what is IRR is that it is the discount rate which enables the present value of the expected cash inflows (after tax) equal to the present value of the estimated cash outflows of the project. If the IRR is greater than the required rate of return, then accept the project. On the other hand, if the IRR is less than the required rate of return, reject the project.
Types of IRR for Different Types of Cash Flows
The internal rate of return is a time value of money metric which represents the true annual rate of earnings on an investment. Depending on the type of cash flow of a project, there are different types of IRR that can be calculated, such as:
- Unlevered IRR – also known as the unleveraged IRR or Project IRR, is the internal rate of return of a string of cash flows without financing or the Free Cash Flow to Firm (FCFF). It takes its inflows that are needed to fund the project without accounting any debt or funding used for the project. Before accounting any funding from the outside sources, it is better to evaluate the project’s own feasibility by calculating the unlevered IRR. Therefore, it is usually recommended to determine the Unlevered IRR before proceeding with Levered IRR to check if the project is financially healthy or not.
- Levered IRR – also known as the leveraged IRR or Equity IRR, is the internal rate of return of a string of cash flows with financing included or the Free Cash Flow to Equity (FCFE) which means cash flows available for equity holders. Instead of using its inflows as funding, it takes account the debt or the funding to use for the project. The outflows are considered as cash flows from the project minus any interest and debt repayments. Basically, it is a case where the project is funded by a mix of debt and equity.
- Investor IRR – also known as the Cash in / Cash out to investor where the cash in represents the investment provided and the cash out represents the dividends or the sale of the investment. The investor IRR is basically exclusive for investors to calculate the share they’ll get according to the amount of investment they poured into the project.
To determine if the project is attractive, one must first calculate the unlevered IRR to check if the project is able to sustain itself without the help of outside funding and after step financing, one will then proceed with calculating the levered IRR to determine if the project can do better and higher IRR with funding provided.
Uses of Internal Rate of Return
The internal rate of return is a popular profitability measure because, as a percentage, it is easy to understand and easy to compare to the required return. It is a metric used in capital budgeting to help determine the profitability of potential investments. There are many uses of the internal rate of return, such as the following:
- Determining the Profitability of a Project or an Investment. Usually, IRR is used in capital budgeting for corporations to compare the profitability of capital projects. To evaluate if a certain project will yield more than the costs of running the project, or to determine which project will yield more based on the IRR. To ensure maximizing the returns, the higher a project’s IRR is, the more attractive it is to proceed with the project. In cases where there are multiple projects, the ones with the highest IRR will be considered first.
- To Maximize the Net Present Value as an indication of profitability, efficiency, quality, or yield of an investment. Though there are similarities between the IRR and NPV, the latter is more of an indicator of the net value added by making an investment. By applying the IRR method, any investment would be accepted because if the IRR exceeds the cost of capital represents that the project or investment has a positive net present value.
- To Calculate the Fixed Income (calculating yield to maturity and yield to call).
- Measuring new debt in terms of yield to maturity which often is used when a company raises a new debt for funding new projects. Since both IRR and NPV are applicable to liabilities as well as investments, in this case, the lower the IRR is, the more preferable it is for the company.
- Evaluating share issues and stock buyback programs for corporations. This is in case the company wants to proceed with repurchasing their stocks and if the returning capital to the shareholders has a higher IRR that the candidate capital investment projects.
- Used for Private Equity from the limited partner’s perspective, to measure the general partner’s performance as an investment manager. The IRR is used since it’s the general partner that controls the cash flows including the draw-downs of committed capital.
- Analyzing projects or investments for private equity and venture capital which involves multiple investments over the life of a business and cash flow at the end through the sale of the business.
- Determining the returns of the projects or investments and to compare or rank multiple projects based on their expected yield to prefer the ones with a resulting higher IRR.
- To better understand and know of any user’s risk tolerance, investment needs, avoiding risky investments/projects, etc.
Despite the several advantages of using the IRR, it also has its own disadvantages. Unlike the net present value (NPV), the IRR doesn’t give you the return on initial investment in terms of real dollars. Basically, it is best to choose the calculation of the NPV instead of the IRR because usually, financial performance is measured in dollars which is how NPV is expressed while on the other hand, the IRR is expressed in percentage. Another issue with the IRR analysis is that it is under the assumption that the business can continue to reinvest any incremental cash flow at the same IRR, which in reality might not be the case. Hence, choosing the highest NPV is a much preferable option rather than to calculate the internal rate of return.
In conclusion, the calculation of IRR and its analysis can help determine which potential projects are worth investing for. But, it is only as accurate as the assumptions that drive it and that a higher percentage doesn’t always mean that it is the most feasible due to the timing and size of cash flows, leverage used, and differences in the return assumptions.
How to Calculate the Internal Rate of Return
There isn’t really a concrete way on how to calculate irr but there are two ways which could help you by simply using a tool such as Excel. The first way to calculate the IRR in Excel is making use of its built-in functions. The Excel IRR function is one of the financial function available in Excel that helps calculate the IRR. It returns the irr for a series of cash flows that occur at regular intervals.
Syntax / Function Formula: =IRR (values, [guess])
Values - represents an array or reference to cells that contain values and represent the series of cash flows which include investment and net income values. It is required to input such data for the function to work.
Guess - an estimate for expected IRR or the number assumed by the user that is close to the expected irr. It is optional to input since the function can simply take a default value of 10%.
Below is a simple example which shows how IRR function is used.
As shown above, the initial investment is negative since it is considered as an outgoing payment. The values after that represent the cash inflows which comes in the form of positive values. By simply using the IRR function in Excel, the calculation of the IRR became easier. The only downfall about this function is that it is not designed to calculate compound growth rate so when calculating the data with different cash inflows, you will have to change it such as below:
To understand how the IRR with a compound growth is calculated using the Excel IRR Function, let us do a reverse check to see if the resulting value is right. This can be simply done by using the following formula which is usually used to calculate compounded annual growth rate or CAGR.
CAGR = (End Value / Start Value) ^ (1 / Periods) – 1
We will then apply the same formula to manually calculate the IRR which is shown below:
Now that you know the first way on how to calculate irr, the second way is to break out all the component cash flows as you calculate each step individually, then using the resulting values as inputs for the IRR formula. Basically, to calculate the internal rate of return, the expected cash flows of a project must be provided and the NPV must equal to zero. The formula below shows how to calculate IRR:
A company is deciding whether to purchase an equipment for $400,000. The equipment would only last for 3 years but it is expected to generate annual profit for $200,000. After the equipment reaches its end, the company plan to salvage it and expects to receive about $20,000. By using IRR, the company can determine whether to make use of its own cash rather than go for investment options, which should return about 10%.
Here is how the IRR calculation looks like according to the drawn scenario above:
0 = -$400,000 + ($200,000) / (1 + r) + ($200000) / (1 + r)^ 2 + ($200,000) / (1 + r)^3 + $50000 / (1 + r)^4
The investment’s IRR will serve as the rate that makes the present value of an investment’s cash flows equal to zero. If the resulting rate is greater than the expected 10% then it can be said that the investment is feasible.
Calculating the IRR can be a time-consuming task, hence, instead of manually doing it, you can simply take advantage of today’s tools such as a financial model template that includes the calculation of the IRR. This is a much preferable option since a financial model is flexible, detailed, easy to audit and edit, as well as transparent.
If you are looking for a financial model template with IRR calculation, simply check out our list up top. There, you will find industry-specific financial model templates as well as for different use cases. The templates are ready-made by financial modeling professionals with substantial experience and a wide range of know-how which most users are badly in need of.
You can also get different kinds of financial model templates in Excel here at eFinancialModels. These templates are available for download by any kind of users from different countries such as in the USA, Germany, Switzerland, Japan, Saudi Arabia, Egypt, and many more who are in need of help when it comes to their financial modeling tasks.