All
Members
Companies
Blog
Forums
Podcast
Webinars
    User Log in  /  Sign up
  • Forums
    Buying & Selling Real Estate Followed Forums Landlord Forums & Rental Property Questions New Member Intros Newest Posts Trending Discussions See All
  • Education

    Read

    Guides Member Blog BiggerPockets Blog

    Watch

    Webinars Video Library Free Courses

    Listen

    BiggerPockets Podcast The Money Show Podcast BiggerPockets Business Podcast

    Topics

    Business Operations Finance Finding Deals Property Management Property Types Strategy
  • Network

    Recommended Vendors

    Companies Real Estate Agents Hard Money Lenders Investment Companies Mortgage Lenders Contractors

    Search

    Members Events Jobs
  • Tools

    Calculators

    Rental Property Fix and Flip BRRRR Rehab Estimator
    Wholesaling Mortgage Payment 70% Rule Airbnb

    Services

    Landlord Resources
  • Marketplace
    Real Estate Listings Find Foreclosures External Link Ads, Jobs, and Other
  • Bookstore

    Real Estate Books

    The Intention Journal Buy, Rehab, Rent, Refinance, Repeat The Book On Rental Property Investing See all books

    Real Estate Forms

    Lease Agreement Package

    Featured Book

    The House Hacking Strategy book cover
    The House Hacking Strategy

    Discover why so many successful investors support their investment careers with house hacking—and learn from a frugality expert who has “hacked” his way toward financial freedom!

    Get the book now
  • Pricing
Log In Sign up
User
Quick search links
Podcast Hard Money Lenders Books New York
BlogArrowMortgages & Creative FinancingArrowManage Your Investment Risk with the Help of This Technique
Mortgages & Creative Financing

Manage Your Investment Risk with the Help of This Technique

Kyle Zaylor
Expertise: Business Management, Real Estate Marketing, Real Estate Investing Basics
25 Articles Written
Sensitivity Tables

Risk is EVERYWHERE in real estate. Given the amount of variables that affect the sale, cash flow, or acquisition of any property or development, you’re bound to have one or multiple variables greatly affect your deal (in a positive or negative way).

Want more articles like this?

Create an account today to get BiggerPocket's best blog articles delivered to your inbox

Sign up for free

Our industry is governed by many “standard” metrics that we use to understand the relative riskiness of a deal. For example:

  • 50 percent of gross rent should be attributed to expenses for any given small cash-flowing property;
  • The cap rate on a large stabilized market rate rental property should be around 7.5 percent;
  • A commercial lender should require a 1.2 percent debt coverage ratio on an acquisition, etc.

While these metrics (and many others) are rough industry standards, they are never truly accurate for any one specific deal because the metrics always deviate plus or minus a few degrees. When you add up enough minor deviations from the standard assumptions, you could have what once appeared to be a homerun quickly turn into a strike out.

So how can we understand the ramifications of the fluctuations around these standard, assumed metrics?

Enter Sensitivity Tables:

Sensitivity tables are a financial and business modeling technique that allows one to understand an output (think NOI, ROI, IRR, acquisition price, etc.) based on the changes in one or two variables.  Typical questions answered by a sensitivity table include:

  • What will my debt service be based on different acquisition prices and interest rates?
  • What is the NOI based on different acquisition prices and rent levels?
  • What is the lowest rent I can charge to still generate at least X dollars in monthly income for this property if I acquire it at Y price?
  • What cap rate and NOI do I have to have to acquire this property for X dollars?
  • At what gross rent and vacancy levels do I start losing money on this property?

Modeling these variables can quickly provide a tremendous amount of information on the relative risk of a project. To build a sensitivity table, model your deal with a spreadsheet as you would any other deal. Once finished, pick several variables you want to “test” the sensitivity on.  To do this, you’ll have to pick three things.

1) Your outcome. What result to do you want to see from this analysis (common outcomes include net operative income, internal rate of return, net present value, and acquisition price)?

2) Input number one. What input do you want to test the sensitivity on (common inputs include the cap rate, lender interest rate, rent levels, and vacancy rates)?

3) Input number two.

Then use your spreadsheet to build the sensitivity table. The inputs will be in a column and row while the outputs will populate the rest of the table. A typical, blank sensitivity table will look like this:

Blank Sensitivity Table
Here’s a very quick example using a hypothetical investment.

Let’s say a broker calls me up with an intriguing multi-family acquisition. It’s in an area I know very well and I’m confident in my assumptions on rent, expenses, vacancies, and tenant turnover. After modeling my cash flows and returns, I want to test out the riskiness of vacancy rates and rent levels. Based on past projects, I’m pretty sure I can achieve a gross monthly rent of $7,000 and have a vacancy and credit loss of about five percent. By keeping my other variables constant (operating expenses, debt service, etc.) I can use a sensitivity table to see how high or low these variables can be before I start incurring a negative cash flow.

Filled in Sensitivity Table

Holding my assumptions like expenses and debt service constant (among others), I can see that I need to be somewhat careful about both vacancy and gross rents because, if they deviate in the wrong direction simultaneously, I could quickly be in a bad situation. Looking at the results of the sensitivity analysis, if I think my general assumptions are sound, I may think this is too risky of a venture for my capital.

Every acquisition or development has a mountain of risks involved. While you’ll never truly know the exact performance of a property or new project, sensitivity tables can help get a sense for how tolerant you can be when the actual numbers deviate from your initial projections.

P.S.

If you’re interested in an available and formatted sensitivity table, check out the BiggerPockets FilePlace (within the Resources section of BiggerPockets). I used a modified version of the sensitivity table found in the “Pro Forma Template – Small Apartments” Excel file for the example above.

Photo: llamnudds

By Kyle Zaylor

Kyle is the creator of realestate-java.com, a blog dedicated to commercial real estate development. Kyle is also a real estate development associate with Blu Homes, Inc. His company focuses on building sustainable homes throughout the country.

5 Replies
    Glenn Espinosa
    Replied almost 7 years ago
    WOAH. Just stumbled upon this and it is AWESOME! Thanks Kyle! Can’t wait to play around with this thing on my future deal analyses.

    Report Abuse

    Why are you reporting this?

    Additional Comments (optional)

    Cancel
    Mike
    Replied almost 7 years ago
    Really solid resource hee Kyle, thanks for sharing with us all!

    Report Abuse

    Why are you reporting this?

    Additional Comments (optional)

    Cancel
    Ankit Duggal
    Replied almost 7 years ago
    Kyle Great job. You are using the Data Table function within Excel for this sensitivity table? As that can do the same thing and utilize conditional formatting to highlight the turning points of where cash flow turns negative. Ankit Reply Report comment

    Report Abuse

    Why are you reporting this?

    Additional Comments (optional)

    Cancel
    Ankit Duggal
    Replied almost 7 years ago
    Kyle Great job. You are using the Data Table function within Excel for this sensitivity table? As that can do the same thing and utilize conditional formatting to highlight the turning points of where cash flow turns negative. Ankit

    Report Abuse

    Why are you reporting this?

    Additional Comments (optional)

    Cancel
    Kyle Zaylor
    Replied almost 7 years ago
    Glenn and Mike, glad you found it useful! Ankit, good call on the data tables. I created the example manually, but definitely true with the effectiveness of the Data Table function in Excel (and all the “what-if analysis” functions).

    Report Abuse

    Why are you reporting this?

    Additional Comments (optional)

    Cancel
Rotate Log in or sign up to comment

Related Blog Posts

Mortgages & Creative Financing Dec 06, 2019

4 Mortgage Options: Evaluating the Pros & Cons of Real Estate Financing

By Larry Alton

The bank will tell you if there are certain loans you don’t qualify for, but it’s up to you to decide whether you’ll pursue a loan you’re “qualified” for.

Read more →

Mortgages & Creative Financing Dec 06, 2019

6 Ways to Invest in Real Estate with Little Money or Experience

By Brandon Turner

Isn’t it time to let go of your if, ands, and “big buts,” and learn how to invest in real estate? Almost anyone who has made the leap would tell you yes! But you’re lacking money or don’t have experience, you say… Sorry, those aren’t good excuses to put off investing! Here’s how to make it happen.

Read more →

Mortgages & Creative Financing Nov 29, 2019

If You’re Well-Prepared, You Can Lower Your Loan Length

By Matt DeBoth

Seasoned investors, this article is for you (sorry newbies). Sometimes you have to get creative with your financing. I break down ways for you to shorten the life of your loan, as well as pitfalls to avoid.

Read more →

Mortgages & Creative Financing Nov 28, 2019

How to Raise Money for Your Next Deal—Without Legal Issues

By Ben Leybovich

Here’s what you need to know when financing your next real estate deal and how to avoid any legal troubles. I break down what is acceptable and what could get you into trouble with the SEC.

Read more →
Log in Sign up

Log in

Forgot password?

If you signed up for BiggerPockets via Facebook, you can log in with just one click!

Log in with Facebook

Or
btn_google_dark_normal_ios Created with Sketch. Continue with Google

Let's get started

We just need a few details to get you set up and ready to go!

Use your real name

Use at least 8 characters. Using a phrase of random words (like: paper Dog team blue) is secure and easy to remember.

By signing up, you indicate that you agree to the BiggerPockets Terms & Conditions.

Or
btn_google_dark_normal_ios Created with Sketch. Continue with Google

Why create an account?

Receive a free digital download of The Ultimate Beginner's Guide to Real Estate Investing.

Connect with 1,000,000+ real estate investors!

Find local real estate meetups and events in your area.

Start analyzing real estate properties, we do the math for you.

It's free!

Explore

  • Membership
  • Community
  • Education
  • Marketplace
  • Tools
  • FilePlace
  • REI Resources
  • Perks
  • iOS App
  • Android App

Company

  • About Us
  • Press
  • Advertising
  • Careers
  • Stats
  • Contact Us

Important

  • Terms of Use
  • Rules
  • Privacy
  • FAQ

Social

  • Facebook
  • Twitter
  • YouTube
  • Instagram
© 2004-2019 BiggerPockets, LLC. All Rights Reserved.