Rental Cash Flow Analysis Spreadsheet for Excel

By
Real Estate Brokerage with Tellus Real Estate Solutions, LLC Managing Broker: 23635

Updated spreadsheet available:

I've updated the spreadsheet.  You can find a link and description on my website here:

http://tellusre.com/rental-cashflow-analysis-worksheet/

That page has directions for the updated spreadsheet.

Cheers!

 

I'm always helping clients do cash flow analysis for their properties. Usually I just work up a spreadsheet at the time of the analysis.  I finally decided to spend an afternoon and put together a spreadsheet that I could reuse over and over.

This spreadsheet is done as a workbook with 4 pages.  Here is a description of the various worksheets and how to fill them out.

The Property Cash Flow Analysis Worksheet 

cash flow analysis worksheet screenshot

It shows the results of the analysis. The one editable area of the worksheet is the Property Address section, where the user can input the address of the property they are analyzing.

Along with the Property Address the Income section, which shows the gross yearly income, the vacancy allowance, and the resulting effective gross income. The gross income and vacancy allowance are pulled from the "Income" worksheet.

The next section shows the Expenses, pulled from the "Expenses" worksheet.

These numbers are used to calculate the Net Operating Income.  This number is used to calculate a range of property values based on various Cap rates.

The next section of this sheet is the Debt Service section which pulls the total annual debt service from the "Loans and Debt" worksheet.

The last section is the Property Value section. As mentioned earlier, this section provides a range of estimated property values based on the Annual Net Income and various Cap Rates.

The Income Worksheet

Income Worksheet screenshot

The Income Analysis worksheet provides sections for entering monthly income for up to ten units (if there is only 1 unit, like a single family home, only enter a value in one of the cells). You can also enter in estimated monthly incomes from extra sources like Laundry, storage units, etc.

The last value the user enters is the estimated annual vacancy rate.

From this information, total monthly income, total annual income, and effective annual income are calculated. The total annual income, annual vacancy allowance dollar amount, and effective gross income are automatically transferred to the Property Cash Flow Analysis worksheet.

The Expenses Worksheet

Expenses worksheet screenshot

The expenses worksheet has an area for entering Annual Expenses, like taxes and insurance so you don't have to figure out the monthly amounts ahead of time.

The next area is for monthly expenses.  There are categories for most expenses you will need, plus a few that are less common.  You should be able to put in actual numbers, based on history, for things like utilities, and estimated/budget numbers for things like advertising. The main thing to remember is to include all your expenses.

The worksheet will calculate the monthly amounts for your annual expenses and the total monthly and annual expenses. The Total Annual Expenses is transferred to the Property Cash Flow Analysis worksheet automatically.

The Loans and Debt Worksheet

Debt Service Worksheet screenshot

The Loans and Debt worksheet provides debt service info. It allows for up to 3 fully amortized mortgages and 3 interest only loans, or more importantly.. combinations of them.  It does not cover partially amortized loans with balloon payments.

What to use the spreadsheet for

The main use for the spreadsheet is to figure out if a prospective investment property will 'cash flow' (Income Less Expenses is greater than Debt Service). In most cases, investors want positive cash flow. For some properties, they may be willing to put up with a small negative cash flow before taxes.

The spreadsheet can also be used for comparing property values.  All other things being equal (which admittedly they seldom are) the property with the best cap rate for a given price is the better investment.

Download the spreadsheet from here.

If you don't have Excel 2007 or Excel 2010, try out Office Web Apps for free 

Jason Hershey
CENTURY 21 North Homes Realty, Inc.
jhershey@iinet.com
13322 Hwy. 99 South, #201
Everett,  WA   98204
Phone 425.743.3775
Cell 425.417.5389
Toll Free 800.262.1096
Fax 425.742.9140
http://www.nwcommercialre.com

 

close

This entry hasn't been re-blogged:

Re-Blogged By Re-Blogged At
Spam prevention

Accessibility option: listen to a question and answer it!

To submit the form,
drag the lamp to the circle on the side.

Type below the answer to what you hear. Numbers or words, lowercase:

Topic:
ActiveRain Community
Location:
Washington
Groups:
Century 21 Active Rainers
Washington State Investors and Investment Opportunities
Tags:
office tools
excel
spreadsheet
cap rate
property cash flow analysis

Comments 29 New Comment

Rainmaker
190,121
Gregory Bain
Mezzina Real Estate & Insurance

Thanks for the free download - nice of you to give.

August 17, 2010 12:36 PM
Rainmaker
43,865
Jason Hershey
Helping clients make great real estate decisions
Tellus Real Estate Solutions, LLC

Thanks all for your great comments. If you have problems with the spreadsheet or need one for an older version of Excel (remember, you can always use Office Live instead, send me an email and I'll save as in the older format and send to you. 

August 17, 2010 01:37 PM
Ambassador
1,200,689
Erica Ramus
MRE, Schuylkill County PA Real Estate
Erica Ramus - Ramus Realty Group - Pottsville, PA

This is a great spreadsheet. Thank you so much for sharing.

August 17, 2010 02:42 PM
Rainer
32,788
Tim Cahill
MBA, EcoBroker
RE/MAX Results

Thanks for creating this AND for sharing it with us!  Awesome work!  I hope I can return the favor some day.

-TMC

Tim Cahill
MBA, Certified EcoBroker, Realtor
Web site: RE/MAX Results | Blog: Home Green Home
Green Real Estate site: Cyber Green Realty
T: 617.599.2775
E: Tim@FlynnTeam.com or CyberGreenRealty@gmail.com

August 17, 2010 07:09 PM
Anonymous #30
Anonymous
Adrian Willanger

Jason, Thanks for the spreadsheet, I was look everywhere for one, this one is great!

March 21, 2011 08:29 PM
Rainmaker
43,865

Jason Hershey

Helping clients make great real estate decisions
Let our team help
*
*
*
Spam prevention

Accessibility option: listen to a question and answer it!

To submit the form,
drag the airplane to the circle on the side.

Type below the answer to what you hear. Numbers or words, lowercase:

Additional Information

Tellus Real Estate Solutions, LLC is committed to helping you make informed and rewarding decisions when you are looking to buy, sell, lease or invest in residential or commercial real estate. We can assist you with traditional seller and buyer representation, commercial landlord and tenant representation, as well as commercial real estate property management services. Tellus is also a great choice when you need less traditional help. We are an “indie” or boutique real estate firm. We are not affiliated with a big-box or franchised brokerage firm where agents and clients are viewed as a statistic or number. We offer a more personal, one on one experience with a focus on quality. Our brokers are free to tailor their services to better meet your unique real estate needs, offering a more specialized choice of expertise, knowledge, information and customization.