Below is the Excel so you can practice (you will need to download it by clicking on the Excel icon in the bottom border).

Don’t forget to see our other post on what increments you should be using.



In Excel you can use two-way Data Tables (keyboard shortcut: Alt+D+T) to construct a sensitivity analysis output matrix. These matrices can be very insightful as to how a key performance indicator output will change given the simultaneous changing of two input variables.

A two-way data table showing sensitivity of the property's capitalized value to changes in Rent PSF and Sale Cap Rate.

For instance, using the table above as a reference, let’s say we want to see how the capitalized value (output shown in the table interior in black) reacts when changes are made simultaneously to the property Rent PSF (shown across the top-most row in blue) and the Sale Cap Rate (shown down the left-most column in blue).

The Data Table functionality provides us with the spectrum of outputs that result from the intersection of these input variables. For instance, a $64.00 rent and a 7.00% sale cap rate would yield a property value of $4,971,429.

The question becomes, though, what should the range of input values be? What increment should be used for rent and what for sale cap rate?

In other words, should it be this?

Rent increments of $1 PSF and cap rate increments of 25 basis points.

Or should it be this?

Rent increments of $2 PSF and cap rate increments of 100 basis points.

Before we answer this, know that the increment does not need to be constant across the range of the data point (see the very top table rent values as an example). Oftentimes, however, we will use a constant increment for the sake of making the table easier to process mentally.

The answer to the question is really an answer to this: which range of values would be the most realistic and relevant to this transaction? These inputs will produce the most meaningful spectrum of outputs to the intended audience.

So it comes down to your market knowledge. If a lease has never been signed above $67.00 PSF, then maybe the middle table is spot on. If similar properties have traded only above an 8 cap, however, the cap rate inputs are likely too optimistic/aggressive. In this case all three table sale cap rate ranges are not as relevant as they could be.

All of this to say that we should always remember that Excel is a tool – it is not the Gospel… We should use Excel to simulate outcomes that are most meaningful to the specific circumstances at hand, which means using our market knowledge and the basic business judgment with which we have been endowed.


Learn more about REFM Certification In Excel For Real Estate here; a great way to learn/sharpen your skills and add to your resume. Join the hundreds who have already done so!

Excel for Real Estate

Omid Bensal – 10/16/2014
Michael Vielma – 10/18/2014
Clarence E. Wong – 10/19/2014
Tim Kamas – 10/23/2014
Campbell Estes – 10/25/2014
Michael Nieliwodski – 10/27/2014
Duc Minh Do, With Distinction – 10/31/2014
Ziyin Xie – 11/1/2014
Christopher A. Aiello, With Distinction – 11/2/2014
Caleb Sheldon, With Distinction – 11/3/2014
Jason Eng Ken Yap – 11/9/2014
Matthew Thomson, With Distinction – 11/11/2014


Real Estate Finance

Omid Bensal – 10/21/2014
Justin Sheu, With Distinction – 10/22/2014
Michael Nieliwodski – 10/27/2014
Matthew Douglas Hardy, With Distinction – 10/30/2014
Caleb Sheldon, With Distinction – 11/10/2014
Matthew Thomson, With Distinction – 11/11/2014
Jason Eng Ken Yap, With Distinction – 11/12/2014
Jacob Cohen, With Distinction – 11/13/2014


Joint Venture Waterfall Modeling

Michael Loc Tran, With Distinction – 10/19/2014
Omid Bensal – 11/15/2014

{ Comments on this entry are closed }

Posted in: Certification