In this article, we’ll learn how to calculate IRR in Excel. Internal Rate of Return (IRR) is a popular budgeting metric of capital used to estimate the return of potential investment.
Also read: ROI in Excel – How to Calculate ROI in Excel
Formula to calculate IRR
IRR or Internal rate of return is a method of calculating an investment’s rate of return. As by the name, it only takes into consideration internal factors and parameters of an organization/firm/investment in calculation and excludes external factors, such as market demand, market sentiments, inflation, the cost of capital.
IRR can be calculated by equating the Net Present Value (NPV) equation equal to zero (0) and solving for the rate of return (IRR).
N: total number of periods
NPV: Net Present Value
n: a non-negative integer
: cash flow
: internal rate of return
Formula to Calculate IRR in Excel
The formula to calculate IRR in Excel returns the internal rate of return for a series of cash flows.
=IRR (values, [guess])
The excel function uses two arguments:
- Values (required argument) – An array of values, representing 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) – This 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 skipped, the function will take a default value of 0.1 (=10%).
Steps to calculate IRR in Excel
Let’s get into calculating the IRR in Excel with the help of an existing dataset.
Step 1: Input the Data
- Enter the relevant data in correct formatting, for which you want to calculate IRR.
Here, in our example, we are calculating the IRR of a company, started five years ago. We have Cash Outflows and Cash Inflows data with us. You may notice that the ‘Initial Investment’ adjacent cell of Cash Inflows is empty, the reason being, that our example company was set up (started) at that time with an initial investment of $80,000, hence No Cash Inflows.
Step 2: Calculate the Net Flow
- Now, we need to calculate the Net Cash Flow.
- Net Cash Flow = Cash Inflows – Cash Outflows
Here, in our example, it would be found using (B2-C2)
- Apply the same formula to all the cells of the ‘Net Cash Flow’ column. (You may encounter both positive and negative values, as Net Cash Flow of a company can be either)
Step 3: Apply the IRR formula
- To apply the IRR formula, select a cell for output
- Then type in:
- Select, all the data values, for which you want to find the IRR from the Net Cash Flow column (include all positive and negative values), in our example all values from E2:E7
- Close the formula bracket and hit Enter to apply the formula.
- You must have got the final output for IRR (Internal Rate of Return) in the output cell
- But, the IRR that we have calculated is not precise and may lead to erroneous assumptions and calculations when dealing financial data.
- To make Internal Rate of Return (IRR) precise we, need to increase the Decimal Value precision in our output cell.
- To do so, select Home in your Home tab and click on Increase Decimal (2 or 3 times, as per decimal places required)
Hence, we get our final and precise result for Internal Rate of Return (IRR):
That’s It! Hope you have learned well – What is Internal Rate of Return (IRR), and how to calculate IRR in Excel.