Rental Cash Flow Analysis Spreadsheet for Excel

By
Real Estate Broker/Owner with Tellus Real Estate Solutions, LLC Managing Broker: 23635
http://actvra.in/4mBv

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
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

Spam prevention
Show All Comments
Ambassador
987,695
Jason Crouch
Austin Texas Homes, LLC - Austin, TX
Broker - Austin Texas Real Estate (512-796-7653)

Jason - I applaud you for sharing this detailed spreadsheet.  Good stuff!

Aug 16, 2010 11:05 PM #10
Rainmaker
558,767
Chris Olsen
Olsen Ziegler Realty - Cleveland, OH
Broker Owner Cleveland Ohio Real Estate

Hi Jason -- Good stuff here, thanks for sharing.  I used to be a commercial real estate appraiser and we had very similar spreadsheets! :)

Aug 16, 2010 11:19 PM #11
Rainmaker
71,979
Nate Gerard
Keller Williams Premier - Stillwater, MN
CDPE, East Metro Twin Cities Realtor

Here here! I've bookmarked for future reference. I have one that computes Passive Activity Loss - a powerful means of offsetting income for those who make less than $150K taxable and also for offsetting profits from real estate. If you or anyone else is interested, please let me know.

Aug 16, 2010 11:55 PM #12
Rainmaker
423,321
Denise Hamlin
Cardinal Realty - Locally Owned Real Estate Company - Iowa City, IA
Real Estate & Relocation Services 319-400-0268

Hi Jason ~ I too will be bookmarking this for future reference. Thank you.

Aug 16, 2010 11:59 PM #13
Rainer
196,219
Brett Pehrson
Advanced Funding Home Mortgage - Salt Lake City, UT
NMLS# 272088

Jason - What a great tool you've just shared!  Thanks so much for contributing this to the ActiveRain community.  I can see people finding you in a couple years when they're looking for a resource like this.  Great job!

Aug 17, 2010 01:48 AM #14
Rainmaker
1,518,066
John Novak
Keller Williams Realty The Marketplace - Las Vegas, NV
Henderson, Las Vegas and Summerlin Real Estate

Nice work on this, Jason. It's always nice to see someone who really knows their way around Excel.

Aug 17, 2010 02:20 AM #15
Rainmaker
226,719
Bernadine Hunter, SFR, ACRE
Keller Williams Greater Columbus Realty - Columbus, OH
"Finding Solution to Your Real Estate Needs"

Thanks for the freebee. I occassionally run across individuals that was to invest in real estate, but don't have the proper tool to evaluate the return on their investment. This really helps.

Aug 17, 2010 04:01 AM #16
Rainmaker
894,517
Richard Weeks
Waller Group - Dallas, TX
Realtor, Associate Broker - Manager Business Development

Jason,

Great job and thanks for sharing with the AR community.

I was not able to down load as I have an older version of Excel; however I would love to have this file.

Aug 17, 2010 07:14 AM #17
Rainer
138,935
John Zappia
Maine Custom Realty - Portland, ME
Maine Custom Realty

Thank you Jason..I appreciate it immensely...I've sold many multi's to investors and have a few of my own...I always have to do the 'down & dirty' calculations with a pencil & a piece of scrap paper...needless to say, I'll be much more confident showing a client something like this (I've tried to do something similar before but Excel & I aren't always in-sync).

Aug 17, 2010 07:36 AM #18
Rainer
157,973
Jim Smith
The Property Management Company - Round Rock, TX
Broker,CRS,GRI,RMP,CNE,TRLP

Thank you for sharing your simplified spreadsheet.  I deal strictly with real estate management, leasing and consulting for investors; and usually use formulas that are quite a bit more complicated (acquired through CCIM Courses).  This appears to be a bit easier to understand for the novice investor. You willingness to share this is another example of professionalism as it should be!

Aug 17, 2010 07:47 AM #19
Rainmaker
1,092,012
Bryant Tutas
Bryant Tutas-Tutas Towne Realty, Inc - Poinciana, FL
Broker/REALTOR, Tutas Towne Realty, Inc

Well done Jason. And thanks for sharing this with all of us. That's what this site is all about.

Aug 17, 2010 08:10 AM #20
Rainmaker
236,521
Mark Montross
Catamount Realty Group - Burlington, VT
Listing and Buyer Specialist

Jason - this is awesome. Thank you very much for sharing, I really appreciate having this level of detail!

Aug 17, 2010 08:30 AM #21
Rainmaker
895,372
Michael Setunsky
Michael's Commercial LLC - Woodbridge, VA
Your Commercial Real Estate Link to Northern VA

Jason, this a very good Cash Flow analysis program. Great way to determine if an investor should go ahead with a property. Thanks.

Aug 17, 2010 08:58 AM #22
Rainmaker
291,273
Darrell Walters
W. Darrell Walters - Newnan, GA

This is great information for potential investors. Thanks for sharing!

Aug 17, 2010 09:25 AM #23
Anonymous
Anonymous

Jason, thanks for sharing. I have been using different spreadsheet, but like your's better.

Aug 17, 2010 11:12 AM #24
Rainmaker
191,418
Gregory Bain
Mezzina Real Estate & Insurance - Little Egg Harbor, NJ
For Homes on the Jersey Shore

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

Aug 17, 2010 12:36 PM #25
Rainmaker
46,559
Jason Hershey
Tellus Real Estate Solutions, LLC - Duvall, WA
Helping clients make great real estate decisions

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. 

Aug 17, 2010 01:37 PM #26
Ambassador
1,293,925
Erica Ramus
Erica Ramus - Ramus Realty Group - Pottsville, PA - Pottsville, PA
MRE, Schuylkill County PA Real Estate

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

Aug 17, 2010 02:42 PM #27
Rainer
32,788
Tim Cahill
RE/MAX Results - Arlington, MA
MBA, EcoBroker

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

Aug 17, 2010 07:09 PM #28
Anonymous
Adrian Willanger

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

Mar 21, 2011 08:29 PM #30
Show All Comments
Rainmaker
46,559

Jason Hershey

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

Additional Information