Offset() function to Calculate IRR for Dynamic Range
When you start the project can you be sure, for how long will you operate it? A VC gives you funds to buy a commercial project. You are to operate the project for some time and then sell it off! Can you tell me today, when you will sell?
Real world is dynamic and business situations keep changing! Your excel is not that dynamic, when you use the IRR function and tell it to calculate the IRR, you show fixed cash flows! These cash flows are dynamic.
Not to worry! We have Offset function to our rescue!
What is the Offset() function
In my opinion it is one of the most versatile (And dangerous) functions to use. On the face of it, it is a simple function – As the name suggests, it just offsets your reference.
Offset( range, rows, columns, height, width )
So in the illustrated example, it starts from the C8 cell, moves 0 rows and 0 columns and then gives an array of size 5 x 5 to the sum function!
The difference – Now Offset is NOT returning a value. It is returning references to arrays!
So what can you do with this function?
Ah… you can do a lot! It can change diapers of your kids as well :-). Right now we will see, how it can introduce unparalleled flexibility in your models.
So the VC we were speaking about – gives you USD 1000. You propose to operate the commercial complex for 4 years and post that sell it off for USD 1200.
But then you are not certain if the economic & business conditions would be such that you need to operate it for 4 or 5 or even 6 years. You want a flexibility in the model to “Dynamically Update” itself.
How do we implement this?
The first step is to find the last cash flow in the assumption. I do that simply by counting the number of cash flows. It indicates the number of years that you run the business.
Once I have the number of cash flows with my, I dynamically return the size of the cash flow range using the offset function and input that to IRR function.
Since the offset returns reference to the array of cash flows, I can give that as an argument to the IRR function and it gets me the IRR of the project!
If a new cash flow is entered, the count function would calculate it, pass it to the offset function, which would return the new range to IRR. Phew!!
Few more places, where you can use Offset function
Anywhere you want references to be returned, Offset function does come handy. Similarly I have found offset to be a very useful function if you are looking at creating scenarios, especially in Merger Modeling, Growth Assumptions, Economic Assumptions, etc. With the click of the mouse, it can completely update your model!
Beware
In my modeling experience I have found the following three functions to be quite versatile with the ability to surprise you with their power!
· Offset
· Indirect
· Index
They can return references. This is not usually what you expect Excel to do. So when you are not expecting they can return references to unknown ranges and surprise you. They can make understanding of model (for the reader of your model) fairly difficult and auditing your model difficult. So use them with care!
Do you use these functions?
These functions can make your financial model quite flexible. Do you use such functions in your models? Share your views and experience on making your model more flexible!
Templates to download
I have created a template for you, where the subheadings are given and you have to link the model to get the cash numbers! You can download the same from here. You can go through the case and fill in the yellow boxes. I also recommend that you try to create this structure on your own (so that you get a hang of what information is to be recorded).
Also you can download this filled template and check, if the information you recorded, matches mine or not!
Join our Financial Modeling Classes
We are glad to inform that our new financial modeling & project finance modeling online class is ready for your consideration. Please click here to learn more about the program & sign-up. Chandoo.org has partnered with Pristine to launch a Financial Modeling Course. For details click here. 
For any queries regarding the cash impact or financial modeling, feel free to put the comments in the blog or write an email to paramdeep@edupristine.com
11 Responses
Ciao Hui,
Collecting Excel tricks under the title “Notable Excel Websites (Non-MVP) Edition” is a brilliant idea…
Thank you in the name of all The FrankensTeam.
On our site there is a box with a picture and text highlighting:
This is a no-MVP site
we think ourselves “bad boys” a bit 🙂
For those who would like to know why our site is a no-MVP site, enough to click on the link:
http://goo.gl/lxDszY
Thank you again!
Thanks a lot
I really enjoyed this (newsletter). I must admit that I rarely read an Excel newsletter (and I subscribe to quite a few) all the way though, but this grabbed my attention and before I realized it, I was engrossed in it. I must also admit that most of this I don’t understand, yet. But, it excites me when I do learn something new in Excel. I can’t wait to see how much of this I can implement into my (constantly-evolving) ‘House Budget’ & ‘Family Medical’ worksheets that I have developed over the past few years! I sure hope to see more of these type of newsletters in the future! Thanks!
Thanks for doing this Hui! I appreciate being included.
I like Tom’s tip a lot. I posted about a tool I wrote to automate this at http://yoursumbuddy.com/tables-edit-query-dialog/
EXCELLENT !
Hui, This post is Superb! More over I have always been a fan of Roberto’s work and have learnt a lot from him.
Here are some of my recent contributions
1. Customising markers in a chart – http://www.goodly.co.in/customize-markers-in-a-chart/
2. Charting Hacks to work faster – http://www.goodly.co.in/5-charting-hacks-to-help-you-work-faster/
3. 7 Date formulas to make life easy – http://www.goodly.co.in/date-formulas-in-excel/
4. Customised scrollbar using VBA – http://www.goodly.co.in/customized-scroll-bar-in-excel/
5. Adding Direct Legends – http://www.goodly.co.in/customized-scroll-bar-in-excel/
Hope everyone enjoys!
I like the Excel Ninja Menus.
1. Select a cell or range then move till the 4-way cross appears. Right-Click and drag the selection to another place in the worksheet then, like a ninja, a menu full of skills and throwing stars pops up allowing me to do all kinds of awesomeness.
2. When you click the fill box on a Date and right click and drag it down, a lot of amazing Date options pop up.
I also brand my Excel to remind myself that I’m awesome. In my personal macro workbook I place the following code.
Private Sub Workbook_Open()
Application.Caption = “SuperKrishna’s Awesomeness”
End Sub
My favorite tip goes along with #17. If you try to copy subtotaled data (and in earlier Excel versions filtered data),when you paste it all the data displays instead of just the summarized data.
To get around this, select your summarized data, click on Find and Select tab and then select Go to Special. Click Visible cells Only and click OK. Now paste and you will see that only the summarized data has been copied.
You can also go CTRL+G and then click the Special icon at the bottom of the dialog box.
What a great idea, Chandoo! I’d love to be included in your next edition:) Perhaps a VBA exclusive version?
@Ryan
I will review this concept about 6 months out from the original post and be sure to keep your site in mind
Hui…
That sounds great, Hui:) I just realized I gave credit to Chandoo for the idea and I should have attributed it to you.
Sorry about that!