Plum Crazy Real Estate and Business

head_left_image

Rental Cash Flow Analysis Spreadsheet for Excel

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

 

This is an excerpt from an article on our company website. You can find more posts at http://tellusre.com/feed/

Are you a self-motivated broker, committed to taking great care of your clients?

Join the Tellus Real Estate team!  We offer easy-to-understand, and even easier to calculate, commission plans. Check out our career page at http://tellusre.com/real-estate-careers/

Jason Hershey, Designated Broker
Tellus Real Estate Solutions, LLC
PO Box 1113
Duvall, WA 98019
Office: (877) 413-7325
Cell: 425-417-5389Fax: 425-223-3148

www.tellusre.com

www.twitter.com/tellusre

http://www.linkedin.com/in/jasonhershey

http://www.facebook.com/pages/Tellus-Real-Estate-Solutions-LLC/67149969257

 

Comment balloon 29 commentsJason Hershey • August 16 2010 01:33AM
Rental Cash Flow Analysis Spreadsheet for Excel
share
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. &… more
Puget Sound Landlord and Investor Orgs
share
Here are some quick links to my favorite local landlord organizations: Rental Housing Association of Puget Sound - As a member, I use them for my lease forms, tenant screenings, etc. The group is well organized, well funded, and provides great… more
Snohomish and Eastside King County Commercial Property Sale Summary…
share
Here is a summary of the commercial sales in Snohomish County and King County's Eastside The number transactions is similar to previous months. However, the transactions are generally larger than in previous months. The data comes from… more