fbpx
Search
Close this search box.

Using IRR with Data Tables – Modeling Cash-flow Scenarios in Excel

Share

Facebook
Twitter
LinkedIn

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…

Orchard investment decision - IRR with data tables

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:

expected cash flows

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.

expected cash flows
=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,

percentage changes in purchase and terminal values

… 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.

IRR for all 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.

We need a two variable data table to calculate all the IRR combinations. To set this up follow these instructions:

Pct changes in purchase and terminal values
  1. Modify input data cells (B7 = Purchase Price and B8 = Terminal Value) so that they include the % change values from cells H10, H11 respectively.
  2. Set up the IRR calculation in the top left corner of the scenario grid.
  3. Select entire range, including the top left cell with IRR calculation.
  4. Go to Data ribbon and click on What-if scenarios
Data Table button in Excel
  1. 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.

Visualizing the Results

We can easily create a visual from the scenario results; something like this:

Scenario visualization with conditional formatting.

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.

50 ways to analyze data course.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

Weighted Average in Excel with Percentage Weights

Weighted Average in Excel [Formulas]

Learn how to calculate weighted averages in excel using formulas. In this article we will learn what a weighted average is and how to Excel’s SUMPRODUCT formula to calculate weighted average / weighted mean.

What is weighted average?

Wikipedia defines weighted average as, “The weighted mean is similar to an arithmetic mean …, where instead of each of the data points contributing equally to the final average, some data points contribute more than others.”

Calculating weighted averages in excel is not straight forward as there is no built-in formula. But we can use SUMPRODUCT formula to easily calculate them. Read on to find out how.

3 Responses to “Using IRR with Data Tables – Modeling Cash-flow Scenarios in Excel”

  1. Anil Kumar says:

    Hi Chandoo, I read your blogs all are very knowledgeable. They help me a lot to learn new things. thanks for sharing your knowledge.

  2. 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!

  3. doug marcus says:

    i finally get it. thank you.

Leave a Reply