Rather than just give you my analysis spreadsheets, my objective with this article is to show you how to develop your own. It is critical that you learn how to do so if you are to succeed at real estate and I want to encourage you to begin “playing” with Excel as soon as possible. This will be far more beneficial to you than giving a cursory glance to someone else’s work because it is easy enough to understand. Consider this a case of teaching you how to fish and feeding you for a lifetime rather than giving you a fish and feeding you for a day.
You see, chances are my spreadsheets would not even be suitable for you because I am likely using a different investment methodology. Even if we are both using a similar technique there could be small differences based on our respective financing or some other aspect that means it doesn’t quite fit.
But more important than any of that is this:
I have found that the greatest benefit of developing your own analysis spreadsheet is that you become intimately familiar with the numbers. By the time you’ve developed it and used it a few times you understand the numbers inside out and upside down and have a thorough understanding of what numbers are most important and what effect certain adjustments will have.
You should always use your spreadsheet but the funny thing is that once you’ve gone through this process you actually don’t need it as much because you have such a thorough understanding of what numbers will work for you and your market. What could be more valuable to a real estate investor than that sort of innate knowledge?
The PMT Function in Excel
Before we get into it you need to familiarize yourself with the PMT function and it’s variants in Excel. PMT stands for “payment” and is a variable in a set of functions related to loans. The variables in this set of functions are:
PMT – payment
PV – present value
Rate – interest rate
Nper – number of periods
FV – future value
Type – payments due at start or end of periods
Here is a Microsoft page all about it – http://office.microsoft.com/en-us/excel/HP100698251033.aspx
One thing to be aware of is that the interest rate must be for the same time period as the payment. So if the payments are monthly you must remember to divide the annual interest rate by 12 when you put it in this equation.
Inputs and Outputs
In designing your spreadsheet you need to think about what inputs and outputs you will need to evaluate a deal. This needn’t be very difficult. You can just begin listing variables down the left-hand column and you will soon figure out what other information you need as you go. Then you can drag and drop stuff to rearrange it later.
But as a primer here is a list of things that you could include as inputs:
Property Address
Value
Price
Closing Costs (can estimate as % price)
Percent Finance
Down Payment
Amount Borrowed
Term of Loan (years)
Number of Repayments per year (needed for PMT function)
Loan Interest Rate
Management Fee
Repair Costs
Expected Rent
Vacancy Rate
The output we are looking for is, of course, some form of return on investment. I’m generally looking at cash-on-cash return (CCR) which is the return on the cash that I put into the deal (not the price of the property).
CCR = net annual cash flow / cash invested
If you have a private investor you would also include an analysis of their cash-on-cash return since the deal must work for them too.
Ultimately though, you can use this spreadsheet to adjust various inputs so that you CAN meet your desired return. In other words, if your desired CCR is 30% you can adjust various inputs to determine what price you should offer on a property in order for it to be a viable investment.
You would probably also include some other key numbers in a tidy Evaluation Report such as:
down payment
closing costs
total investment
monthly income
monthly expenses
net cashflow
But once again the numbers you display will depend upon your investing methodology. These are more typical of a cashflow investor than a flipper.
What-if Analysis
And finally, you can use your completed spreadsheet to run various what-if analyses. For example, what if:
you get a higher (or lower) LVR (loan-to-value) ratio loan?
interest rates increase?
your investor increases the down payment?
you charge the tenant a greater option fee?
you change the term of the loan?
you reduce your management fees?
you reduce your closing costs?
You can see what I’m getting at. Once you have a working model you are armed with some very powerful information.
The Layout
I’ve included this screen capture of one of my spreadsheets just to give you some layout ideas. Please do not try to model your spreadsheet exactly from this one. This is for a very specific investing methodology and many of the fields will not make sense for your siutation.
However, I just wanted to show how I divide the spreadsheet into:
input area at the top (where yellow fields are inputs and blue fields are calculated)
brief “Deal Evaluation” report at the bottom with some of the key outputs I mentioned earlier (for a cashflow investment)
a “What-If” tool in the top-right corner
Possible Spreadsheet Layout
I hope I’ve given you enough information to just start playing with a spreadsheet and figure this out for your situation. If you have specific questions or difficulties just come back here and use the comment field below. Either I or one of your fellow readers should be able to help you out.
To Your Success,
Scott Roemermann.
Popularity: 9% [?]