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
Frequently Asked Questions about Using the OFFSET Function in Real Estate Financial Modeling
What is the OFFSET function and how is it used in real estate models?
The OFFSET() function returns a reference to a range that is a specified number of rows and columns from a starting cell. In real estate models, it’s used to create dynamic lists and calculate values over a defined period, such as a rolling 12-month NOI.
Why should I be cautious when using the OFFSET function?
OFFSET() is a volatile function, meaning it recalculates every time any change is made in the workbook. Excessive use can slow down performance, especially in large models. The post advises: “if there is a non-volatile function that will do the trick, use it.”
How can OFFSET be used to calculate residual value from NOI?
You can use OFFSET() to dynamically select a 12-month period of NOI for residual value calculations. This allows flexibility whether using trailing twelve months (TTM) or forward-looking NOI based on the analysis period. The Excel tutorial walks through this setup.
What does it mean to transpose cash flow strings using OFFSET?
Transposing with OFFSET() involves converting vertical cash flow data into a horizontal layout (or vice versa). This is useful when the model requires a different data orientation than originally entered. OFFSET automates the referencing of those values in a new layout.
Where can I access the Excel files used in these OFFSET examples?
The post provides download links for both examples:
Calculating Residual NOI
Transposing Cash Flows
Each file demonstrates how OFFSET() is implemented for real estate modeling use cases.
What is one key takeaway about when to use OFFSET?
Use OFFSET() when a dynamic reference is necessary and no non-volatile alternative exists. As stated, “this tutorial is meant for those situations” where a static or semi-dynamic formula can’t accomplish the modeling goal.
