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

Comments

Thank you.  I was going to try to explain this to one of my buyer agents and you produced it here. 

Posted by Tim Lorenz, 949 874-2247 (TIM LORENZ - Elite Home Sales Team) over 8 years ago

Glad to help!  Have 'em give me a yell if they have questions.

Posted by Jason Hershey, Helping clients make great real estate decisions (Tellus Real Estate Solutions, LLC) over 8 years ago

WOW Jason! That's Dynamite!  Thank You so much for sharing your hard work with us!

I am suggesting this post for a feature to help spread the word of your generosity!

Thank You Again! I really do appreciate this!

Posted by Harry Logan (RE/MAX executives realty) over 8 years ago

Thanks Harry, glad you like it.  I may get really motivated and put together an IRR spreadsheet too.

Posted by Jason Hershey, Helping clients make great real estate decisions (Tellus Real Estate Solutions, LLC) over 8 years ago

Excellent tool and that you created it yourself will make your clients feel special and they will know they are dealing with a PRO!!!

Posted by Wallace S. Gibson, CPM, LandlordWhisperer (Gibson Management Group, Ltd.) over 8 years ago

Jason,

Thanks for coming from a place of contribution and adding value to the community!

Posted by Scott Lewis, REALTOR, e-Pro,CIA,IMSD,GRI & CNE (The Bald Man Group) over 8 years ago

What a great piece of work here Jason. This will save lots of time when you use it over and over. Agreat tool for any REALTOR®

Posted by Roland Woodworth, Q Realty - Power In Real Estate (Q Realty) over 8 years ago

Thanks Jason, greatly appreciated.

Posted by Rick Beal (Coldwell Banker Residential Brokerage) over 8 years ago

Hi Jason,

Great Post!  This is excellent information, thank you for sharing your hard work. 

Posted by Dinah Stallworth-Lewis, NATCHITOCHES, LA HOMES FOR SALE (Priority Real Estate LLC - 800.978.4847) over 8 years ago

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

Posted by Jason Crouch, Broker - Austin Texas Real Estate (512-796-7653) (Austin Texas Homes, LLC) over 8 years ago

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

Posted by Chris Olsen, Broker Owner Cleveland Ohio Real Estate (Olsen Ziegler Realty) over 8 years ago

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.

Posted by Nate Gerard, CDPE, East Metro Twin Cities Realtor (Keller Williams Premier) over 8 years ago

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

Posted by Denise Hamlin, Broker/Owner, Helping Happy Clients Make Smart Choices (Cardinal Realty ~ 319-400-0268) over 8 years ago

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!

Posted by Brett Pehrson (Advanced Funding Home Mortgage, NMLS#13287) over 8 years ago

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

Posted by John Novak, Henderson, Las Vegas and Summerlin Real Estate (Keller Williams Realty The Marketplace) over 8 years ago

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.

Posted by Bernadine Hunter, SFR, ACRE, "Finding Solution to Your Real Estate Needs" (Keller Williams Greater Columbus Realty) over 8 years ago

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.

Posted by Richard Weeks, REALTOR®, Broker over 8 years ago

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

Posted by John Zappia, Maine Custom Realty (Maine Custom Realty) over 8 years ago

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!

Posted by Jim Smith, Broker,CRS,GRI,RMP,CNE,TRLP (The Property Management Company) over 8 years ago

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

Posted by Bryant Tutas, Selling Florida one home at a time (Tutas Towne Realty, Inc and Garden Views Realty, LLC) over 8 years ago

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

Posted by Mark Montross, Listing and Buyer Specialist (Catamount Realty Group) over 8 years ago

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

Posted by Michael Setunsky, Your Commercial Real Estate Link to Northern VA over 8 years ago

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

Posted by Darrell Walters (W. Darrell Walters) over 8 years ago

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

Posted by Gary Burleson, Myrtle Beach Homes, Condos, Foreclosures, Investment Propery (Beach Water Realty - www.beachwaterrealty.com) over 8 years ago

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

Posted by Gregory Bain, For Homes on the Jersey Shore (Mezzina Real Estate & Insurance) over 8 years ago

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. 

Posted by Jason Hershey, Helping clients make great real estate decisions (Tellus Real Estate Solutions, LLC) over 8 years ago

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

Posted by Erica Ramus, MRE, Schuylkill County PA Real Estate (Erica Ramus - Ramus Realty Group - Pottsville, PA ) over 8 years ago

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

Posted by Tim Cahill, MBA, EcoBroker (RE/MAX Results) over 8 years ago

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

Posted by Adrian Willanger over 7 years ago

Participate