SFH Rental Analysis (Spreadsheet)

97 Replies

As another test of the new BP file vault, and to give back to the BP community, attached is the spreadsheet I use to evaluate single family house rentals.

In general, the red colored fields are the inputs you probably want to change, and everything is based on those parameters.

If you have any questions, feel free to post them in this thread, and I'll try to answer them...

Please consider this copyrighted by me, though you are free to use it in any way you wish as long as you don't try to profit from it...

Looks like a useful tool for measuring the performance of a single family rental property. I see that you have used a 30 year period was that because of the loan amortization period or do you see that as a typical holding period?

Originally posted by Charles Perkins:
I see that you have used a 30 year period was that because of the loan amortization period or do you see that as a typical holding period?


Couldn't possibly be holding period. It's too short! :D

Originally posted by Charles Perkins:
Looks like a useful tool for measuring the performance of a single family rental property. I see that you have used a 30 year period was that because of the loan amortization period or do you see that as a typical holding period?


I only used 30 years because that's the typical amortization period of the loans I generally work with. In most cases, the loan payoff period is less than the amortization period (i.e., 30 year amortization with balloon payment in 5 years), in which case I just ignore the results after year 5.

Of course, the spreadsheet could be modified to account for multiple loans, multiple amortization periods and different payoff periods...I just tried to keep it relatively simple and able to suit my typical needs.

Spreadsheet looks well thought out.

I'll be trying it out with properties I consider in the future.

Thanks J Scott!

Well laid out, informative spreadsheet.

Thanks for sharing!

Thanks for the great spreadsheet.

Thank you very much. Can you pleae tell me what is the vacancy rate percentage means in the spreadsheet. For each month, I see 8% and for each year, I see either 8% or 7%. Sorry if it's a basic question (newbie).

Originally posted by David Duong:
Thank you very much. Can you pleae tell me what is the vacancy rate percentage means in the spreadsheet. For each month, I see 8% and for each year, I see either 8% or 7%. Sorry if it's a basic question (newbie).


Vacancy rate is the percentage time that you are not receiving income from the property because you don't have it rented. 8.3% is equivalent to 1/12, or one month per year that the property is not rented and therefore you are losing income.

You can configure each year as you wish...I assumed one month the first year (8.3%) and then slightly less in subsequent years (7%) because you've gotten the property into rental condition already.

Does that answer your question?

J Scott,

Thank you again - that makes perfect sense. May I please ask you 4 more questions:

1. On your spreadsheet example, expenses were 45% of gross income. Is what people on this blog referred to when they state rental property expenses should be less than 50% or I believe they say something similar.

2. Can you please verify that mortgage and interest is never to be calculated in NOI?

3. If you don't mean, please... can you explain the cash rate of return and the total return of return. I really would like to get a better understanding.

4. Finally, is there a simple ratio or rule of thumb to determine if a rental property is a good investment. For example, if cash ROI is x, then it's a good investment.

Originally posted by David Duong:

1. On your spreadsheet example, expenses were 45% of gross income. Is what people on this blog referred to when they state rental property expenses should be less than 50% or I believe they say something similar.

For a very basic analysis (before you have all the real numbers), most people assume that total expenses (the number I have in the spreadsheet) will be about 50% of the rental income. This is what is referred to as the "50% Rule" around here.

Generally, you should expect expenses to be between 45-55% on a given house, depending on location, condition, market, tax burden, etc.


2. Can you please verify that mortgage and interest is never to be calculated in NOI?

Correct. NOI does not include any mortgage (debt service) in its calculation.


3. If you don't mean, please... can you explain the cash rate of return and the total return of return. I really would like to get a better understanding.

Cash-on-cash return (Cash ROI) is the return on your initial investment (down-payment + closing costs). It only assumes your return based on the cash flow from the property. It doesn't include the profit potential you get from things like paying down the mortgage (equity) and tax advantages you might get from the property.


4. Finally, is there a simple ratio or rule of thumb to determine if a rental property is a good investment. For example, if cash ROI is x, then it's a good investment.


This is a very personal decision for each investor based on his/her own goals, but here are some things that some investors look for:

- At least $100 profit per month after all expenses and mortgage payments;

- At least $200 profit per month after all expenses and mortgage payments;

- At least 20% return on investment (Cash ROI);

- At least 30% total return (Total ROI).

Thank you again. That was very informative. Just in two chats, learned more than a weeks worth of reading. Nothing beats real experience and from learning other investors sharing real experiences.

Thanks so much for sharing! My reformed accountant's analytical brain still loves spreadsheets :-)

J Scott,

Thank you for the article. It was very informative!

Nice spreadsheet.... I was playing around with some number and found that there seems to be a problem with a couple of cells when making a change. In particular when changing a number in either D3, H3, H6, H7, it effects the cells in L9, P9 and all cells in lines 43 thru 45 with an error code.. #NAME? I've tried to figure this out but to no avail. Can you help? You can email me at [EMAIL REMOVED]

Originally posted by Robert Savidge:
In particular when changing a number in either D3, H3, H6, H7, it effects the cells in L9, P9 and all cells in lines 43 thru 45 with an error code.. #NAME?


Robert -

Sounds like you're using Office 2003, in which case you need to install an Excel add-on called the Analysis ToolPak.

See here:

http://office.microsoft.com/en-us/excel-help/cumprinc-HP005209039.aspx

If you click the "How?" icon, it will explain how to download and install for free...

Let me know if that works for you...

Thank you, J.

I've been working up a sheet like this for myself over the las t couple of months, but yours is excellent. i am sure many in the group will benefit from it.

Jeff

Hi J Scott,
Did you count appreciation and depreciation write-off in your spreadsheet? Or did I miss it somewhere?
Thanks,
Angie

Originally posted by Angie Menegay:

Did you count appreciation and depreciation write-off in your spreadsheet? Or did I miss it somewhere?

Hi Angie -

Nope, my spreadsheet doesn't address any of tax related consequences (positive or negative) of the deal.

This was intentional, as every investor is going to face a different tax situation, so including that information would likely result in an incorrect analysis for many investors.

Scott-

I am new to the site and I apologize if it is right in front of me but I can not find the attachment with the spreadsheet. Can you please inform me of where to look?

Thank you!

Forgive me but I don't see how I can download the file.

Thanks!

See the post above yours...

Create Lasting Wealth Through Real Estate

Join the millions of people achieving financial freedom through the power of real estate investing

Start here