Internal Rate of Return Analysis

3591 1 0 October 15, 2019

Table of Content

What is IRR Analysis for?
IRR Calculation Form – Internal Rate of Return Example Calculation
Financial Model Templates with IRR Calculation

Once the budgeting process has been completed, the IRR can be calculated. Internal rate of return (IRR), also called time adjusted rate of return, is defined as the rate of interest that equates ( I ) with the ( PV ) of future cash inflows. Basically, the IRR can be defined as the interest rate that equates the present worth of future returns to the investment outlay. It is that interest rate where the present net worth of benefits is equal to the present net worth of investment.

In other words, IRR I = PV or NPV = 0

What is IRR Analysis for?

The primary objective of IRR analysis is to measure the potential return to capital investment, regardless of how the enterprise is financed and how the earnings are distributed. The accuracy of such an analysis is dependent on the quality of data used in the analysis, as well as the precision with which these data are analyzed and evaluated.

The IRR is, used to measure the rate of return on a project that generates internal revenue, in other words, revenue that accrues directly to the project in question. It does not measure any benefits which accrue to other beneficiaries. For example, a rice mill project that generates revenues through sales would be analyzed by the IDR. The benefits measured, in such a case, would be projected net revenue from sales of rice stored and processed by the mill, as well as sales of by-products.

The economic potential for any enterprise under study is determined by comparing the calculated IRR to the relevant opportunity cost of capital. If the calculated IRR is higher than the relevant opportunity cost of capital, this indicates that the venture is feasible. If the calculated IRR is lower than the opportunity cost of capital, this indicates that the venture is infeasible. The greater the difference, the more conclusive the results. Therefore, the greatest advantage of using the IRR method is that it does consider the time value of money and, therefore, is more exact and realistic.

Decision rule: Accept the project if the IRR exceeds the cost of capital. Otherwise, reject it.

IRR Calculation Form – Internal Rate of Return Example Calculation

The internal rate of return (IRR) method approaches the capital investment problem in a different way. Instead of discounting the cash flows at the determined required rate of return to arrive at the net present value, the irr method determines the discount rate that causes the NPV to be equal to zero. This discount rate then becomes the firm’s IRR or the implicit rate of return earned by the project under consideration.

The formula for the internal rate of return shows that the irr occurs when the project’s NPV equals zero. Calculating irr enables an analyst to rank dissimilar projects on the basis of their percentage rate of return. Because the rate of return is a clearly understood measure, many companies have adopted irr as their preferred investment evaluation technique.

Even though one can simply refer to a formula to calculate the IRR, there isn’t really a concrete way how to calculate it. However, the convenience of existing tools today makes the task of calculating the IRR easier compared to the conventional and manual way.

Tools such as spreadsheet programs can be used in making IRR calculations. For example, Excel has a function IRR (values, guess). Excel considers negative numbers as cash outflows such as the initial investment, and positive numbers as cash inflows. Many financial calculators have similar features.

Syntax / Function Formula: =IRR (values, [guess])

Where:

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 internal rate of return calculation example using the Excel function IRR (values, guess).

Let’s assume Company A started with an initial investment worth $100,000 and is expected to generate income of $24,000 constantly.

Excel table showing IRR calculation with income streams over six years.

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:

Excel sheet showing IRR calculation from projected income data

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 (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:

Excel spreadsheet illustrating IRR calculation using CAGR formula.

Isn’t it very convenient to simply use the Excel function as you build a model for your business? However, there is another way to calculate irr and that is to break out all the component cash flows as you calculate each step individually and then use the resulting values as inputs for the IRR formula.

The formula below shows how to calculate IRR:

Formulas for IRR and NPV calculations in financial analysis.

Let’s have another internal rate of return calculation example using the formula above.

Assuming Company B is deciding to invest in a project for $400,000. The project is expected to operate for only 3 years as well as forecasted to generate an annual profit of $200,000. After reaching its end, Company B plans to salvage the assets left which are expected to leave about $20,000 worth.

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.

Financial Model Templates with IRR Calculation

The IRR calculation is usually a part of a complete model such as when building a financial model for an investment or project. You can either build it from scratch or simply take advantage of templates available for download, especially ones that are industry-specific which gives you an option to choose which business model fits your business case the most.

As an example, you can refer to a general use financial model that shows an IRR calculation form:

IRR Calculation Form with financial data for project costs, financing sources, and key rates.

Financial overview chart showing revenues, COGS, OPEX, EBITDA, margin, taxes, change in NWC, CAPEX, and FCFF for 1, 5, and 30 years.

By simply changing the assumptions (cells marked with Blue font), the results are automatically updated and calculated. No more conducting calculations manually and spending too much time on finishing your financial modeling task. By utilizing an IRR Calculation Form template, you will be able to determine the IRR of a project or investment.

To download the example IRR template shown above, you can get it here: IRR Project Finance Analysis Template. You can also check out our industry-specific financial model templates that include the IRR calculation here:

Lovenna Tibas Store Manager

Lovenna Tibas is the Store Manager at eFinancialModels, specializing in website management, platform optimization, and customer support. She also writes about financial modeling topics including business valuation and business models, making complex concepts accessible to readers. With expertise in WordPress, Easy Digital Downloads, and e-commerce troubleshooting, Lovenna combines technical skills with a deep understanding of financial principles. Her work focuses on helping businesses leverage digital tools and financial insights to drive growth and make data-driven decisions.

See Full Bio