Using the OFFSET Function in Real Estate Financial Modeling
In a previous post, I showed you how to use the OFFSET function to create dynamic lists in Excel. As you become more comfortable using this function in real estate financial modeling, you’ll find that it has almost infinite applications when modeling real estate investments. In this tutorial, I share two more ways you might use the OFFSET function in your real estate Excel models. Find the two video tutorials as well as Excel files below.
A Note About Volatile Formulas
I should first mention that the OFFSET function is a volatile function. A Volatile Function in Excel is one that recalculates every time Excel recalculates, regardless of whether information in the function changed. Thus, the more volatile formulas you write, the slower your Workbook will be. So you should use these functions sparingly.
With that said, Excel has made volatile functions more efficient over the years such that their impact on speed isn’t as dramatic, nonetheless it’s important to consider this fact anytime you write a volatile formula.
So a good rule of thumb is, if there is a non-volatile function that will do the trick, use it. But oftentimes there isn’t, and so this tutorial is meant for those situations.
Using OFFSET() to Calculate Residual NOI
One reason to use the OFFSET function is to find 12 months of net operating income for calculating the residual value. By using the OFFSET function, you’re able to make the residual value calculation fully dynamic to changes in the analysis period and/or dynamic to whether to use the trailing twelve or forward-looking 12 months of NOI.
In this tutorial, I show you how to use the OFFSET function to grab 12 months from a string of monthly cash flows, based on analysis period and residual NOI assumptions.
Click here to download the Excel file used in this exercise
Transpose a String of Vertical or Horizontal Values
Another reason you might use the OFFSET function is to transpose a string of vertical or horizontal values. Or in other words, display horizontally a string of vertical values or vice versa. This often becomes necessary when you’ve modeled a string of cash flows in one direction, but then need to display those cash flows differently and need to speed up the process of linking one to the other.
Click here to download the Excel file used in this exercise
