Offset() function to Calculate IRR for Dynamic Range

From Chandoo.org

Offset() function to Calculate IRR for Dynamic Range:
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