Do you want to simulate multiple cash-flow scenarios and calculate the rate of return? Then this article is for you. You can get multiple scenarios of IRR with data tables. In this page learn:
- Introduction to IRR & XIRR functions
- Calculate rate of return from a set of cash-flows with XIRR
- Simulating purchase or terminal value changes with data tables
- Apply conditional formatting to visualize the outputs
- Common issues and challenges faced when using XIRR
Mr. John wants to purchase an orchard…
Let’s set a context for this. Say, Mr. John wants to purchase an orchard. But he has a problem. He wants to know if the orchard is worth his investment. So he prepared all the cash-flows (purchase, operational expenditure every year, fruit sales and terminal value), like this:
He wants to calculate the potential return on investment.
Introduction to IRR, XIRR functions
We can use IRR (Internal Rate of Return) function in Excel to calculate return from a series of even spaced cash flows.
IRR Example:
For example, say you invest $10,000 in a savings bond and you get $500 per year for next 5 years and then you will get $11,000 back. What is the return on this investment?
Because the cashflows are evenly spaced (ie every year you get some money, with upfront investment of 10,000), we can use IRR formula to calculate the return, like this:
=IRR({-10000,500,500,500,500,500,11000})
The result will be 5.72%.
How to interpret IRR Results?
An IRR of 5.72% in simple terms means: your investment of $10,000 is giving you 5.72% return over the period of investment.
All else being equal, if you have multiple options to make an investment, you pick the option with highest IRR.
.. For example, an energy company may use IRR in deciding whether to open a new power plant or to renovate and expand a previously existing one. While both projects are likely to add value to the company, it is likely that one will be the more logical decision as prescribed by IRR.
Investopedia, IRR page
How to calculate IRR when cash-flows are irregular?
Often, your cash-flows are not regular. In such cases, you cannot use the IRR function. But you can use XIRR function to calculate the rate of return, given a set of dates & cash-flows.
XIRR Syntax:
=XIRR(amounts, dates)
For example, =XIRR(C7:C35, B7:B35) will return the rate of return based on values in column C and dates in column B.
Notes on XIRR usage:
- Obviously, both values and dates must be equally sized.
- There must be at least one negative and positive cash-flow values. Otherwise, XIRR will return #NUM! error.
More on XIRR function sytanx, usage and errors [Microsoft Help]
Calculating Mr. John’s Orchard Investment IRR
Since, we have a series of estimated cash-flows (shown below), we can easily calculate the rate of return using XIRR function.
=XIRR(C7:C35, B7:B35)
You will get the rate of return as answer.
What-if the purchase price changes?
Now, let’s say John thinks both purchase price and terminal value can change. Assuming we have the percentage changes in cells like below,
… we can calculate IRR by making necessary changes to the input cells (B7 & B8)
But, what-if there are a whole heap of possible changes?
We want to calculate IRR for each of these scenarios.
We can write a heap of XIRR formulas. But that is a hassle. There must be a better way.
What-if scenarios with Data Tables
The answer is Data Tables. This powerful and elegant feature of Excel can take a formula and calculate it for any set of scenarios.
This page is a simple introduction to Data Tables. For a more comprehensive overview, examples and advanced use-cases, visit Excel Data Tables page.
We need a two variable data table to calculate all the IRR combinations. To set this up follow these instructions:
- Modify input data cells (B7 = Purchase Price and B8 = Terminal Value) so that they include the % change values from cells H10, H11 respectively.
- Set up the IRR calculation in the top left corner of the scenario grid.
- Select entire range, including the top left cell with IRR calculation.
- Go to Data ribbon and click on What-if scenarios
- In the data table inputs screen, select H11 (Terminal value %) as Row input and H12 (Purchase value %) as Column input.
That is all. Excel will calculate all possible scenario values for IRR.
This page is a simple introduction to Data Tables. For a more comprehensive overview, examples and advanced use-cases, visit Excel Data Tables page.
Visualizing the Results
We can easily create a visual from the scenario results; something like this:
All you need is conditional formatting. Select the scenario outputs, go to Home ribbon > Conditional Formatting > Color Scales > and apply any scale that suits your needs.
You can also make charts from this.
Things to keep in mind when modeling IRR scenarios
Keep these things in mind whenever you are modeling scenarios, with IRR or anything else.
- Select right what-if variables: You need to select variables that can be controlled. In our example, Purchase Price change is a variable that is somewhat under control. Mr. John can always negotiate the price down / up. But Terminal Value Change is not that much in our control. It is well into future and cannot be determined. Instead, a better variable to model is Opex changes, as these are in Mr. John’s control.
- Do not use Excel Tables with this model: First two rows of our cash-flow are dependent on formulas and rest of them are simple inputs. This means, you should not be using Excel Tables (CTRL+T) for this data.
- Modeling scenarios on investment horizon: What-if John wants to calculate the returns for 5, 10, 15 years of operations? In such case, you need to use OFFSET or INDEX formulas to dynamically fetch the range for IRR calculation. Here is an example of IRR with Offset.
- Do not average the IRRs from scenarios: You cannot average all the scenario IRRs to arrive at composite rate of return. This can mislead. Instead either go with weighted averages or use the scenario model to understand the variations.
- Modeling more than two variables: Excel data tables allow only two variables. But you can cheat Excel and calculate multi-variable data tables. Start by setting up a scenario table that all all possible variables values and scenario IDs. Then use Scenario ID as one-way variable to calculate the outputs. See this page for an example.
Watch the video tutorial – Data Tables & IRR
I made a mini Masterclass on financial modeling, analysis and data tables with IRR explaining this. Please watch it below to learn more about this concept. You can also watch this video on my YouTube channel.
Download complete model and learn yourself
Click here to download the complete IRR + Data Tables model file. Play with it to learn how to use Data Tables, XIRR and IRR functions.
Learn advanced analytics and modeling concepts in Excel
Data Tables & Scenario modeling is one of the 50 ways to analyze data in Excel. I run a comprehensive online class on data analytics, modeling and data science with Excel. In this program you can learn all about statistical, marketing, financial and operational data analysis with Excel. Check out the course outline, sample lessons and more information from below link.
3 Responses to “Using IRR with Data Tables – Modeling Cash-flow Scenarios in Excel”
Hi Chandoo, I read your blogs all are very knowledgeable. They help me a lot to learn new things. thanks for sharing your knowledge.
Just a couple of thoughts.
There are two key features that students should appreciate, apart from the rest of this useful example:
using XIRR allows us to put the terminal value out of place in the data range and still get the right answer!. IRR would not allow for that.
this is a typical cash flow for a forestry type investment as you should notice that they payback only happens on the final day of the project.
In fact, on a year by year basis, this is dreadful idea but over the lifetime of the project, capital appreciation bales it out!
i finally get it. thank you.