Menu
Offset() function to Calculate IRR for Dynamic Range
Financial Modeling - 37 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!
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
Hello Awesome...
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.
Thank you and see you around.
Related articles:
|
Leave a Reply
| « A Challenge from Hui | Filter values where Fruit=Banana OR Sales>70. In Other Words, How to use Advanced Filters? » |
At Chandoo.org, I have one goal, "to make you awesome in Excel & Power BI". I started this website in 2007 and today it has 1,000+ articles and tutorials on data analysis, visualization, reporting and automation using Excel and Power BI. 
37 Responses to “Offset() function to Calculate IRR for Dynamic Range”
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.
I use them at work for creating Dashboards. I love them all 🙂
This post is a beauty and another example of why I love this site!
Indirect() is good for cascading data validation - based on the value in another cell.
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.
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
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.
@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" 🙂
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.
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
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
Alex, use INDEX to create your dynamic ranges in a psuedo-volatile way (see examples @ excelhero). Hope this helps.
@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.
@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.
@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! 🙂
@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
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?
@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
@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.
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!
[...] 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 [...]
Thank You..I understood how to use and apply "Offset" function from this article
@Himanshu: Thanks
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.
@Ed
I'd setup a range and transfer all values, using formulas, into a single location so that it can be simply used
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
@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
@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.
[…] Related: Using IRR() function over a dynamic range with OFFSET. […]
Is the CAGR applicable to the growth rate of spend as well?
Does anyone know if you can skip cells with values in XIRR functions? There is one line of data i want to skip for the XIRR calc without having to delete anything or copy the data anywhere else.
@Bryan
No and Yes
If you use a straight range, No
But you maybe able to use a formula to extract the Cashflows & Dates for use in the XIRR Function
For transparency you are better to extract the data to a separate range and then do the Xirr calcs on that range
If you want specific help can you please ask the question in the Chandoo.org Forums http://forum.chandoo.org/
and please attach a sample file
Absolutely beautiful! Thank you for the elegant solution and the clear explanation of this function's purpose and usage.
I am trying to create a dynamic range based on the number of months eclipsed to an IRR function. I would like to get your input on how to do that.
Example:
[Cell A1] Beg Balance = -20000
[Cell A2] Monthly Contribution = -200
[Cell A3] End Balance = 25000
[Cell A4] IRR % after 5 months of contributions =IRR((A1,A2,A2,A2,A2,A2,A3),0.01)
I am wanting to avoid adding another A2 after each new month.
Hi All,
Any idea on how to use offset to calculate IRR dynamically between two date ranges. For example, I want to calculate IRR between two dates of my choosing. e.g. between Jan and Apr or between 2023 and 2027. The formula and IRR calculation needs to be set up to be dynamic though so that I can switch dates at my discretion.
Thanks
David
If you are using Excel 365, you can use FILTER to first filter down the data to the relevant dates and the send that to IRR.
Something like this:
=IRR(FILTER(values, (datecolumn>=start_date)*(datecolumn<=end_date)))