Offset() function to Calculate IRR for Dynamic Range

Posted on October 4th, 2011 in Financial Modeling - 31 comments

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!

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!

 A Challenge from Hui Filter values where Fruit=Banana OR Sales>70. In Other Words, How to use Advanced Filters?
 Written by paramdeep@gmail.com Tags: excel 2007, Financial Modeling, Financial Modeling School, OFFSET(), tips Home: Chandoo.org Main Page ? Doubt: Ask an Excel Question

31 Responses to “Offset() function to Calculate IRR for Dynamic Range”

1. Lianna says:

Awesome stuff. Offset / Index are great for moving averages too. Not sure what I'd do with Indirect.

One thing is certain - you will spend half the time saved explaining to folks what your formula does.

2. I use them at work for creating Dashboards. I love them all 🙂

3. John H says:

This post is a beauty and another example of why I love this site!

4. Andrew says:

Indirect() is good for cascading data validation - based on the value in another cell.

5. Mike86 says:

You could make it really dangerous and use:

IRR=offset(E3,0,0,1,counta(3:3)-1)

Dynamic update just waiting for someone to put a text string into another row 3 cell.

6. K Mac says:

Definitely pretty useful stuff especially with rolling periods but as
Lianna & Mike86 allude to can be pretty dangerous in the hands of babes and most likely a feature that requires some explanation where a workbook is distributed.

The reality is that for many users a combination of more than one function in any one formula frightens the hell out of them and they never bother to read even the introductory paragraph in Help (what's that?) to give them a clue as to the nature of the formula

7. Imran says:

Hi Chandoo,
I recently started using your site and I am pretty impressed.
This is the 1st time I am commenting.
Just to know, why not we have the dynamic range of cashflows in a excel table, so that we can refer it in the IRR calculation so that it auto adjusts?
I hope I am not fooling myself 🙂
Thanks.

8. paramdeep@gmail.com says:

@Lianna, @PSG, @John: Thanks
@Andrew: Thanks. This is a great usage. Have you used it in financial models?
@Mike: Yes. If the full row is selected, it would be still more dangerous! 😉
@Mac: I am assuming that this function is not for "babes" 🙂

9. DQKennard says:

I've used Indirect to construct lookup references (e.g. in Index functions). In the article example, FY column headers could be used to construct references to the correct datasheet or table for that fiscal year, to be used in Index, Match, etc.

I use Index all the time, usually with Match to determine row, and/or column. Match for column in a table header row is much more dynamic, since it avoids the hard coded column number, and can be much more explainable and extensible -- e.g. this is row "Exempt" so I'm looking for column "Exempt" and entering another row is much easier.

Sometimes, my row Match function has things like Match(criteria1&criteria2,array1&array2,0) requiring the index and whatever else it's embedded in to be an array formula.

These could, of course, be all mashed together in one big unholy nested function: Indirects to determine where to look, Counts to determine how big the arrays are, Offsets to return the arrays, Match to access the arrays, Index to determine which array member to use, wrapped in If to handle errors and such, and the whole thing's an array formula. I wouldn't leave that out and visible where someone might see it. 🙂

I've used Offset less. I've used it to compare rows above and below for duplicate checking in sorted data, or to conditionally format duplicates, or to change color based on changes in section/group.

Since I've always used offset to, well, *offset*, I've never thought about using the height and width to return dynamic arrays. I can use that. Thanks.

10. Mawdo81 says:

Hi,
Offset is Key for any sort of data validation list.
Keep 1 sheet for your options and arrange these in lists. I always use shOptions as codename for this sheet, allowing me reuse code mudules easily.

Use offset to name ranges and then use the data validation list as =namedrange

Hide & protect shOptions.

This give you a very quick and easy way to add additional items to the data validation (new product for example). Or you can build a userform and allow users to add to these lists without fear of them breaking your workbook.

Hope that helps,

M

11. Alex says:

My problem with Offset function that it keeps recalculating the spreadsheet. As it has become very large with over 5000 rows, this takes a long time even if nothing has changed and I open my spreadsheet. Any solutions for this

12. Mark says:

Alex, use INDEX to create your dynamic ranges in a psuedo-volatile way (see examples @ excelhero). Hope this helps.

13. paramdeep@gmail.com says:

@Imran: Thanks. If you link this IRR to a data table, it should still work. But how do you input the new cash flows in the cells (dynamically)?
@DQKennard: 🙂 As chandoo would say, you would definitely need a laaarge cup of caffe latte to understand that function! 😉
@Alex: As Mark has pointed out, Index can be use there. Else, I guess you can remove the auto calculate in Excel (File --> Excel Options --> Formulas) and make it manual.
@Mark: Thanks.

14. Mawdo81 says:

@paramdeep:
Please don't turn of auto calculate! It has to be one of the worst feature in Excel! I understand why some might need to very occassionally turn it off but it should be avoidable through good workbook design.

There is never enough visual clues to when it is off & if users don't know that it is off it can cause all sorts of errors becuase the result cells aren't always the result that should be given based on the inputs.

15. paramdeep@gmail.com says:

@Mawdo: I agree it is not a good idea to turn off auto calculate. But if your sheet has become slow because of this, you can turn it off, and then remember to turn it on, once the model is built! 🙂

16. Mawdo81 says:

@paramdeep:
I think we'll have to agree to dis-agree on this one. 😉

I stand by my comment that it should be avoidable through good workbook design.

Keep up the goodwork on the rest though, I'm fairly new to the site but its quickly moving up my "go-to" list.
M

17. marninei says:

Hi all,

New to this site, very impressive.

I am a financial consultant\modelist, and where I work people used to write dynamic offsets as: OFFSET(ref,row,column):OFFSET(ref,row,column). This of course made formulas extremely long, but when I started working there I didn't know about the height\width options.
As time went by I learned to use height\width and got people around me to shrink their formulas by using them.
generally I'm not a big fan of offsets because their so hard to audit, BUT, the funniest thing happened last week. I was auditing a model (I use Explode Add-In, by xl-logic.com) and usually the addin doesn't help much with offsets because it only helps you jump to the references but you don't see the actual result. ANYWAY, I found out that when you use the OFFSET:OFFSET way of writing, explode jumps you to the offset's result, i.e. the range refered to.

Now I find myself thinking what do I prefer, shorter, more readable lines or easier use of audit tools....

Any thoughts?

18. Mawdo81 says:

@marninei - Can you edit Explode? Or Can you get the functionality added in, Offest is widely used & I used to be a massive advocate so it would make sense for Explode to be able to cope with that. As far as I would have thought, it is a change from coping with Offset retruning a cell to coping with it returning a range.

@ Mark et all - Excel Hero's dynamic range using Index is very powerful & I've become a convert. Offset is powerful but volatile. I was happy with that due to the frequency of the ranges I tended to use it for. (Named formula to be exact, so not needing as many re-calcs as it might otherwise). However in trying to access some of these ranges the other day, from external workbooks, I realised the limitation of Offset - the workbook needs to be open to return what ever it would have returned. The index solution doesn't, leaving you with names you can access freely with data as current as when the source was last saved.

M

19. marninei says:

@Mawdo81 - Sadly, explode is locked. btw, if any of you haven't experienced its power I advise you all to try it. It's an amazing auditing tool.
About offset, I think there are situtions for using every formula, but as you know sometime people "fall in love" with a single formula and use it for practically everything, even when there are much more useful, simple, elegant tools to use. Dynamic IRR is one of the best things to use offsets for, since sometimes the cashflow's first periods are zero, resulting in the IRR returning either zero or an error.

20. Model_Citizn says:

Thanks, useful tool...how would one do this dynamic change for XIRR which relies on dates as an input..doesnt quiet work as easily.

• @Model_Citizn: I am sorry for the delayed reply. I seem to have missed the comments on the thread! But just like we find the dynamic range of cash flow using offset, you can find the dates using offset. It is just extending the same logic on two rows instead of one!

21. [...] few months back, I had written about the offset function and how it can be used to create flexible models. I had discussed at that point of time, why offset function is one of the most versatile functions [...]

22. Himanshu Arora says:

Thank You..I understood how to use and apply "Offset" function from this article

23. Ed says:

HEy Chandoo - any advice on how to calculate IRR when the cashflow values are not in an array? I have a situation where all the cashflow values are in a column array except for the final one that is in the correct row but in a different column.

• Hui... says:

@Ed

I'd setup a range and transfer all values, using formulas, into a single location so that it can be simply used

24. Nara says:

I have downloaded Flexible IRR Model and followed all the steps. And then I simply put IRR formula to (E3:S3 ) range and whatever I do, I get the same results by using both options.

The only exception is when I don't put any number in-between two but then if I put zero, I get the common result which was different from IRR results from two different options.

So what's the point of OFFSET function in this calculation?

Thanks

• Hui... says:

@Nara

Chandoo's formula =IRR(Offset(E3,0,0,1,E5))
Allows the user to change the value in E5 ie the number of years and the formula adjusts itself

Using Offset in an IRR or other function that requires a range allows you to make it dynamic or variable based on a cell's value eg: E5

That is as the data is added to the range changes automatically

So Chandoo could also use
=IRR(Offset(E5,0,0,1,Counta(E5:Z5)))
and the formula would adjust itself as you added extra years to the project

• Nara says:

@Hui

But if you add IRR function in the same spreadsheet and tIn IRR function select the whole line indicating cashflow amounts, you will get the same results. I have tested by adding cashflows in following years. As I said the only difference came in the case of living blank cell in-between.

25. […] Related: Using IRR() function over a dynamic range with OFFSET. […]

26. Dianne Souder says:

Is the CAGR applicable to the growth rate of spend as well?

 A Challenge from Hui Filter values where Fruit=Banana OR Sales>70. In Other Words, How to use Advanced Filters?