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.
31 Responses to “Beautiful Budget vs. Actual chart to make your boss love you”
Would be considerably easier just to have a table with the variance shown.
On Step 3, how do you "Add budget and actual values to the chart again"?
There are a few ways to do it.
Easy:
1) Copy just the numbers from both columns (Select, CTRL+C)
2) Select the chart and hit CTRL+V to paste. This adds them to chart.
Traditional:
1) Right click on chart and go to "select data..."
2) From the dialog, click on "Add" button and add one series at a time.
One more way to accomplish it is just select the columns into chart. Press Ctrl+C and then press Ctrl+V
Regards
Neeraj Kumar Agarwal
Unfortunately, this doesn't seem to work for me in Excel 2010. The "Var 1" and "Var 2" columns cannot combine two fonts to display the symbol and the figure side-by-side.
Secondly, there is no option to Click on “Value from cells” option when formatting the label options. The only options provided are Series Name, Category Name or Value.
@TheQ47... the emoji font also has normal English letters, so if you use that font, then you should be ok. I am assuming your computer doesn't have that font or hasn't been upgraded for emoji support.
Reg. Excel 2010, you can manually link each label to a cell value. Just select one label at a time (click on labels, wait a second, click on an individual label) and press = and link it to the label var 1 or var 2.
I am using excel 2010, please explain how to apply Step 12
Regards
Neeraj Kumar Agarwal
Hi Neeraj,
"Value from cells" option is only available in Excel 2013 or above. In older versions, you have to manually adjust the label value by linking each label seperately.
Read this please: https://chandoo.org/wp/change-data-labels-in-charts/
Sir, you are just awesome.
Your creativity has no limit.
Regards
Neeraj Kumar Agarwal
Hi Chandoo,
I just found your website, and really love it. It helps me a lot to be an Excel expert 😉
Currently I am facing with a problem at step 11:
Var1 Var2
D30%
A5%
B0%
B4%
B7%
C10%
C13%
D27%
I42%
Though at mapping table, I used windings, here formula uses calibra. How I can change it? I am able to change only the whole cell. In this case numbers will be Windings too.
Thanks for your help!
Hi Mariann... Welcome to Chandoo.org and thanks for your comment.
If you wanted to use symbols from wingdings and combine them with % numbers, then you need to setup two labels. One with symbol, in wingdings font and another with value in normal font. Just add the same series again to the chart, make it invisible, add labels. You may need to adjust the alignment / position of label so everything is visible.
[…] firs article explains how you can enhance your charts with symbols. You can simply insert any supported symbol into your data and charts. To some extend you can […]
You're a good person, thank you to share your knowledge with us, I will try to do in my work
Great visualization of variance. My question is that is this possible in powerbi?
How would you go about it?
HELLO, WHY CANT I FIND VALUES FOR LABELS IN EXCEL 2013
Dear chanddo sir,
What to do if we have dynamic range for Chart. How this will work. can you able to make the same thing works on dynamic range.
Sir Chandoo,
Good Day!
First, I'd like to say that I am very grateful for your work and for sharing all these things with us.
I tried to do this chart but it seems that the symbols don't work with text (abs(var%),"0%") unless we keep the Windings font style.
The problem is, it converts the text into symbol as well and you wont see the 0% anymore. I'm using Windows 7.
WOW - Segoe UI Emoji
This is the greatest discovery for me this month 🙂 Thanks for sharing.
Here's my two-cents:
https://wmfexcel.com/2019/02/17/a-compelling-chart-in-three-minutes/
Sir This is awesome chart, and very easy to made because of your way to explain is very simple , everyone can do. Thank you
one problem i am facing, I hv made this chart , but when i am inserting data table to chart it is showing two times , how can i resolve this
in this chart when i am adding new month data for example first i made this chart jan to mar but when i add data for the apr month graphs updated automatically but labels are missing for that new month
Hi Renuka,
Please make sure the formulas for labels are also calculated for extra months. Just drag down the series and set label range to appropriate address.
So I am playing with the Actual chart here - but amounts are bigger than your - you have 600 as Budget - my budget is 104,000 - is there a way to shorten that I am unaware of
thank you - I LOVE YOUR SITE
Thanks for the tips and tricks on Excel. In the Planned versus Actual chart examples, you use multiple values (ex. multiple Categories in above). How can this be done when we have only 1 set of values? For example if I have only this:
Planned Actual
SOW Budget 417480 367551
How can I create a single bar chart like the one above?
Thank you Chandoo.
This one is just perfect for my Quarterly Review presentation on Operational Budget against Actual Performance for the Hospital I'm currently working with.
Just Subscribed today (10 minutes ago)
Is there a way to make the table of data into a pivot table to be able to add a slicer for the graph due to many different categories and months?
Hi, I tried to modify you template with something appropriate for me, and I found a problem. this template was modified by me started with excel 2010, then 2016 and finally 2019. Same thing - somehow appear an error - or didn't show the emoticons for positive percentage or doubled the emoticons for some rows. I suspect to be from excel. if is need it I can sand you my xlsx for study. Please help if you can.
Hi Chandoo,
Could you please check the Var Formula in Step1. You have mentioned budget-actual and when i did this i got different values but when reversed like actual-budget i got the actual value what you have demonstrated in step1.
Please share your view.
This is a great chart (budget vs. actual). However, in trying recreate it, I cannot color in the UP Down bars individually, and they all become formatted with the same color. I'm using Office 365. Look forward to the feedback.
Thanks.
Dan
pls explain in detail step 7
While in the Excel sheet you have used following formula for Var
Var = Actual - Budget
But
in the note, you have written
Var = Budget - Actual
Good Presentation and Data information.thank you so much chandoo.