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 measuring 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 which 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 on 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])
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.
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:
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 using the resulting values as inputs for the IRR formula.
The formula below shows how to calculate IRR:
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:
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:
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