In a previous post, I discussed using the OFFSET function to create dynamic lists in Excel. As you become more comfortable using this function, you’ll find that it has infinite applications when modeling real estate investments. In this tutorial, I show two more ways I use the OFFSET function in my real estate models:

1) Calculate the trailing twelve months (TTM) net operating income to determine the residual value. When done this way, the TTM calculation is dynamic to changes in the analysis period.

2) Quickly and accurately copy a vertical string of values horizontally. I also show you how, using the OFFSET function, you can copy a string of non-adjacent values such as when you need to copy the payoff of a loan to an annual statement from a monthly amortization table.

I’ve uploaded the spreadsheet I used during the tutorial, with completed formulas. You can download the spreadsheet by clicking the button below. As always, let me know if you have any questions or comments.

Offset in RE Modeling Tutorial Excel File
  • Sample file from tutorial on using OFFSET function in real estate Excel models

You can reach at my contact page.