What is the IRR Function?
The IRR function is categorized under Financial functionsFunctionsThe IRR function is categorized under Financial functions. IRR will return the Internal Rate of Return for a given cash flow, that is, the initial investment value and a series of net income values. In financial modeling, as it helps calculate the return an investment would earn based on series of cash flows. IRR will return the Internal Rate of ReturnInternal Rate of Return (IRR)The Internal Rate of Return (IRR) is the discount rate that sets the net present value of an investment equal to zero. This guide to calculating IRR will give several examples and who why it's used in capital budgeting, private equity and other areas of finance and investing. If IRR is greater than cost of capital, for a given cash flow, that is, the initial investment value and a series of net income values.
The function is very helpful in financial modelingWhat is Financial ModelingFinancial modeling is performed in Excel to forecast a company's financial performance. Overview of what is financial modeling, how & why to build a model. A 3 statement model links income statement, balance sheet, and cash flow statement. More advanced types of financial models are built for valuation, plannnig, and, as it helps calculate the rate of return an investment would earn based on series of cash flowsValuationThe IRR function is categorized under Financial functions. IRR will return the Internal Rate of Return for a given cash flow, that is, the initial investment value and a series of net income values. In financial modeling, as it helps calculate the return an investment would earn based on series of cash flows. It is frequently used by businesses to compare and decide between capital projects. One example is when a company is presented with two opportunities: one is investing in a new factory and the second is expanding its existing factory. Using the IRR function, we can estimate the IRRs for both scenarios and check which one is higher than the weighted average cost of capital (WACCWACCWACC is a firm’s Weighted Average Cost of Capital and represents its blended cost of capital including equity and debt. The WACC formula is = (E/V x Re) + ((D/V x Rd) x (1-T)). This guide will provide an overview of what it is, why its used, how to calculate it, and also provides a downloadable WACC calculator) of the business (also known as a hurdle rate ). If both are greater than the cost of capital, we will choose the one that shows a higher IRR and/or Net Present Value (NPVNPV FormulaA guide to the NPV formula in Excel when performing financial analysis. It's important to understand exactly how the NPV formula works in Excel and the math behind it. NPV = F / [ (1 + r)^n ] where, PV = Present Value, F = Future payment (cash flow), r = Discount rate, n = the number of periods in the future) than the other.
Note: for increased precision, CFI recommends you use the XIRR functionXIRR vs IRRWhy use XIRR vs IRR. XIRR assigns specific dates to each individual cash flow making it more accurate than IRR when building a financial model in Excel. The Internal Rate of Return is the discount rate which sets the Net Present Value of all future cash flow of an investment to zero. Use XIRR over IRR.
IRR Formula
=IRR(values,[guess])
The IRR function uses the following arguments:
- Values (required argument) – It is an array of values that represent the series of cash flows. Cash flows include investment and net income values. Values can be a reference to a range of cells containing values.
- [Guess] (optional argument) – It is a number guessed by the user that is close to the expected internal rate of return (as there can be two solutions for the internal rate of return). If omitted, the function will take a default value of 0.1 (=10%).
Notes
- The argument value should contain at least one positive and one negative value to calculate the internal rate of return.
- The IRR function uses the order of the values to interpret cash flows. Hence, it is necessary to enter the payments and income values sequentially.
- If the array or reference argument contains logical values, empty cells or text, those values are ignored.
To learn more, launch our free Excel crash course now!
How to use IRR Function in Excel?
As a worksheet function, IRR can be entered as part of a formula in a cell of a worksheet. To understand the uses of the function, let us consider a few examples:
Example 1
For an initial investment of 1,000,000, the cash flows are given below:
The initial investment here is a negative value as it is an outgoing payment. The cash inflows are represented by positive values.
The internal rate of return we get is 14%.
Example 2
Let’s calculate the CAGR using IRR. Suppose we are given the following information:
The IRR function is not exactly designed for calculating compound growth rate, so we need to reshape the original data in this way:
The beginning value of the investment should be entered as a negative number. The ending value of the investment is a positive number. All intermediate values are kept as zeros.
Now, we would simply apply the IRR function to calculate the CAGR, as shown below:
The CAGR we get is:
Let’s do reverse engineering to check if the resulting value is correct or not.
The formula generally used to calculate CAGR is =((End Value/Start Value)^(1/Periods) -1.
The formula to use will be:
The result we get is same as the one we got using IRR function:
Things to remember about the IRR Function
- #NUM! error – Occurs when:
- If the given value array does not contain at least one negative and one positive value
- The calculation fails to converge after 20 iterations. If the internal rate of return is unable to find a result that works after 20 tries, then the #NUM! error value is returned.
- IRR is closely related to the NPV (Net Present Value) function. The rate of return calculated by IRR is the discount rate corresponding to a $0 (zero) NPV.
Free Excel Course
If you want to learn more about functions and become an expert on them, check our CFI’s Free Excel Crash Course! Go through our step by step instructions and demonstrations on how to become an Excel power user.
Additional resources
Thanks for reading the CFI guide to important Excel formulas! By taking the time to learn and master these functions, you’ll significantly speed up your financial modeling and analysis. To learn more, check out these additional resources:
- XIRR vs IRRXIRR vs IRRWhy use XIRR vs IRR. XIRR assigns specific dates to each individual cash flow making it more accurate than IRR when building a financial model in Excel. The Internal Rate of Return is the discount rate which sets the Net Present Value of all future cash flow of an investment to zero. Use XIRR over IRR
- Modified Internal Rate of Return (MIRR)MIRR GuideMIRR is a powerful Excel function. This guide explains the MIRR formula, function, and how to use it when making an investment decision. Like IRR, it also
- Finacial Modeling GuideFree Financial Modeling GuideThis financial modeling guide covers Excel tips and best practices on assumptions, drivers, forecasting, linking the three statements, DCF analysis, Excel modeling and much more. Designed to be the best free modeling guide for analysts by using examples and step by step instructions. Investment banking, FP&A, research
- Financial Analyst ProgramFMVA™ CertificationThe Financial Modeling & Valueation Analyst (FMVA)™ accreditation is a global standard for financial analysts that covers finance, accounting, financial modeling, valuation, budgeting, forecasting, presentations, and strategy.