What If? A Big Question Tailored for Excel

9

One of the main reasons Excel is such a valuable tool in investing, deals with the question of “What If”.  What if I increase rents or decrease operating expenses, what would happen to my cash flow? What if the market cap rate increases or decrease?  What if vacancies increase or appreciation decreases?  If you are faced with “What Ifs”, Excel could be the tool that can help you out.

Predicting the Future

Let me establish a baseline with Excel’s “What If” capabilities.  Excel cannot predict the future.  If you are looking for a one-answer-solves-all solution to real estate investing, Excel is not the answer.   It is safe to say that there is no one-answer solution to investing.  If you have found that solution, please let me know.

We have established that Excel is not a crystal ball.  However, Excel is a tool that can assist you in formulating decisions about property investments.  By nature, real estate investors love to “run the numbers”.  Running the numbers means, applying known values to future possibilities while accounting for unexpected results.  The opposite is spending money and hoping that you will make money; that is called speculating.  I have learned from others that real estate investors are not a speculating group.

So, Excel is not a crystal ball and investors are not speculators.  What can Excel really do for a real estate investor that needs to know, “What If”?

A Simple “What If”

Time for a simple “What If” analysis using a basic loan calculator as an example.  What If we could change the annual interest rate on a loan?  How would a change in the interest rate affect the monthly payment, total payment and the total interest payment for the loan?

The picture below shows a basic loan calculator. I am sure you have seen similar calculators online or you have created one yourself.

basic calc

The calculator has three input cells for the; loan amount, annual interest rate and term in years.  Below are the calculated results cells.  Using this loan calculator we could easily answer our What If regarding annual interest rate.  Just change the 6.25% to any rate we want, the cells below would show the results.  This is fine, if we only need to get results from one or two interest rate changes.  How about seven or even twelve different rates?

You could print each rate change or just write the results down on some paper.  You know there has to be a better way.  Using the loan calculator and creating a data table will solve this problem.  The data table will show twelve interest rates ranging from 5.00% to 8.00%, in 0.25% increments.  The basic loan calculator with the added “What If” annual interest rate data table is presented below.

whatif

For interest rates between 5% and 8%, the input cell is not needed, just locate the rate in the column and read the results in that row.  You might be wondering how long it took me to enter each annual interest rate and transfer the results into the data table.  Would you believe four simple steps?

Starting with the loan calculator, all the result cells must have the following formulas.

	Monthly payment          =        -PMT()
	Number of payments       =        Term x 12
	Total interest           =        Total Payments – Loan amount
	Total Payments           =        Monthly payment * Number of payments

The foundation of the data table is a left column with percentages from 5.00% to 8.00% in increments of 0.25%.  For example, 5.25% has the formula, =F4 + 0.25% in its cell.  Copy that cell and drag it down to 8.00%.  Next step, the existing result cell values, place them on the first row of the data table.  The rest of the table is just labeling.  The data table should now have a left column of percentages and a top row of values, nothing in the middle.

Related: The Many Ways of Viewing an Excel Worksheet

Creating the “What If” data table in four steps:

  1. Starting from the left blank white cell, the cell just above the 5.00%, of the data table, click and select the entire table.
  2. On the Ribbon;  Data  à  (Data Tools)  What If Analysis  à  Data Table…
  3. In the Data Table dialog, click in Column input cell:   (click the annual interest rate percent on the loan calculator.
  4. The click the OK button, the data table has been populated with the results of each annual interest rate.

There it is, four steps and you now have a “What If” analysis for annual interest rate.  That was a simple What If analysis using just one input cell, the annual interest rate.  What about a “What If” based upon more than one cell?  Can you have multiple cells as input for a “What If” analysis?  Can the multiple input cells affect an interactive worksheet, instead of a static data table?

“What If” Analysis with Multiple Cells

If you have a 10 year Proforma cash flow worksheet, what would happen if you could change or input various values.  Values such as the percentage increase in rents over the 10 years.  Increase rent would likely increase the vacancy rate.  Would it be of value to change these input values and see the results?  How about the operating expenses, how will they be affected?  “What If” analyses could give that insight into the effects of those changes?

The picture below shows a 10 year income and expense sheet for a multifamily property.  At the lower left are input cells for the “What If” analyses.  Changes made in the input cell percentages would immediately affect the results in the 10 year results above.

 proforma

 The 10 year projections of cash flow for a sample multifamily property has seven changeable What If input cells.  The results of the “What Ifs” are calculated over the 10 years.

Related: The Story Beneath the Values Using Excel Charts

Modeling Different “What Ifs”

Using What Ifs on the example above allows you to model or simulate the impact the changes in the input cells will have upon cash flow.  With this type of model, you could assume that rents will increase by 3% over the 10 years.  What if you are wrong in your assumption?  Just change the rent What If to a different amount and observe the results.  While making assumptions about the rent, what about the vacancy and credit loss percent?  Will vacancy rates hold steady as rents are increasing slightly?  These and other questions or models are possible with a well-constructed “What If” analysis.

When faced with multiple “What If” input cells, your understanding of investing must be applied.  In the above example, it would be best to change one or two percentages at a time, versus changing all seven at once.  You might look at rents and vacancies at the same time.  Or just change the appreciation percentage or change only the operating expenses percentages.  Remember that Excel is just a tool and a tool is only as valuable to you as how you use it.

“What If” questions can be answered through changes made in the input cells of a worksheet.  The different changes you make and the results derived are the different “What Ifs” that are produced.  Having varying “What Ifs” gives a glimpse into the future, a future of possibilities and not facts.  However with these possibilities you can decide your investment direction coupled with your experiences or the experiences of other investors.

About Author

Loretta Steele is a computer instructor at Moreno Valley College and a real estate broker. She focuses on investment analysis of multifamily properties using Microsoft Excel. She has instructed others in computer applications for over 10 years. Loretta is also the creator of three real estate investment software products; APOD Extra, InvestorApt Analysis and AgentApt Analysis.

9 Comments

  1. Chad Ballard on

    I use a similar concept in my work as an engineer (sensitivity analysis). You manipulate each variable and see how that variable affects what you are trying to accomplish as a whole. A very useful tool in investing and trying to predict best and worst case scenarios. There are even some more advanced tools (Monte Carlo simulations) to “predict the future” but that would be way over kill for an investor.

    I would go further and not just things like rent increases but you can see how different things affect your investment and you can shift your focus. For example how much does negotiating your property management fees from 10% to 8% matters, or maybe it doesn’t compared to what interest rate you get. This helps you focus on the most important aspects that have the greatest return on your time.

    • Chad, thanks for the additional comments on sensitive analysis. Having a tool that allows you to plug in the variables and make adjustments is very valuable in investing.

      Really appreciate your input and glad you enjoy the articles.

Leave A Reply

Pair a profile with your post!

Create a Free Account

Or,


Log In Here

css.php