Rental Analysis Spreadsheet


25 comments

For investors (and up-and-coming investors) looking for a good spreadsheet to analyze the potential returns on a rental property, here you go.

Like the Rehab Analysis Spreadsheet, this is one of the spreadsheets I use everyday in my business (also modified to remove a lot of the crap that’s specific to my projects).

For this spreadsheet, all the numbers in RED along the top and the left side (Column E) are the configurable inputs. Enter your actual parameters there, and the rest of the spreadsheet updates accordingly.

The important return values (Cash Flow, Cash-on-Cash Return and Total Return) are shown in YELLOW at the top in the “Cash Flow & ROI” box. If any of this spreadsheet doesn’t make sense, check out our tutorial on Introduction to Financial Analysis. It should give you all the background you need to understand the spreadsheet (assuming you have a basic understanding of Excel to begin with).

Btw, if you’re seeing garbage along Row 43, it’s because the spreadsheet uses the Excel function “CUMPRINC”. This requires a free Microsoft add-on called the Analysis Tookpak. If you don’t have that installed, you can download it for free here






{ 25 comments… read them below or add one }

1 Harry November 27, 2010 at 2:30 pm

Sweet, thanks, J! This really rocks. There is one suggestion I have – it would be cool to add rows for total equity (incl. assumed appreciation) and for ROE. That could be a good guide for when it may make sense to pull money out further down the road.

2 J Scott November 28, 2010 at 10:35 pm

Harry -

I actually have that — and a lot more — analysis fields in the spreadsheet I use personally. I don’t distribute the full spreadsheet because it has a lot of custom areas that others probably wouldn’t find useful or may find confusing, but I completely agree that these are things that people should add once they are comfortable with the basic analysis.

3 Ban November 29, 2010 at 11:08 pm

Thanks!!!

4 CW May 9, 2011 at 9:06 am

This is great, thanks!

5 george May 27, 2011 at 12:59 pm

hi, just posted a comment under the “Introduction to Real Estate Investment Deal Analysis” post, but gave me a “No Data Transmitted after i hit submit. I dont know if it took, so here’s my comment again.

“cell F43 (equity accrued) is giving me error. the formula is “=-CUMPRINC(H6/12,12*H7, H4, 1, 12, 0)” and the error is #NAME?

please let me know if you know how i can fix that. thanks a lot.

6 J Scott May 27, 2011 at 2:01 pm

Hi George -

To use the CUMPRINC function in Excel, you need a special add-in from Microsoft (it’s free).

Go here for information on how to install it: http://office.microsoft.com/en-us/excel-help/cumprinc-HP005209039.aspx

7 george May 27, 2011 at 3:31 pm

jscott,

thanks so much. excel did not say anything like that, yet i spent time combing through the formula. I just used OpenOffice to open the file and without doing anything, the cell shows the answer perfectly.

Thanks so much, once again. i am addicted to this site.

george

8 Bree August 16, 2011 at 9:39 pm

Can I just tell you that this is an accountant’s dream!!! (coming from a girl with an accounting degree) You just saved me at least 2 hours worth of work!! GENIUS!!

Thanks so much!!

9 J Scott August 16, 2011 at 9:44 pm

Hey Bree,

Glad it’s helpful…let me know if you need any help with it…

10 Bart G January 17, 2012 at 12:33 pm

Great Tool. I have a follow up question to this analysis. Do you have an analysis that takes into consideration the added complexity of taking a cash out after 1 year? For example, I bought a property using all cash for purchase and repairs. Once it has been rented for 1 yr I plan to get cash out from a local bank. I would like to factor this into a ROI. Do you know of any calculators?

Thanks.

11 J Scott January 17, 2012 at 6:08 pm

Hi Bart -

Actually, the best metric for this type of situation is the Internal Rate of Return (IRR). I discuss it — and give an example similar to what you want to do — here:

http://www.123flip.com/introduction-to-irr

12 rob smith January 18, 2012 at 9:47 am

the spreadsheet will not download keeps wanting the registration over and over help

13 J Scott January 18, 2012 at 10:40 am

Hi Rob,

Seems like it’s working for me…not sure what the problem is. Feel free to send me an email (feedback@ 123flip.com) and I’ll send it over to you…

14 Bart G January 19, 2012 at 4:03 pm

Thanks J Scott.
What about if I don’t plan on selling the property? Basically, I’m trying to find a calculator that assumes I take out an 80/20 loan for the original purchase. Then Spend x $ on rehab. Then once the property is rehabbed and rented, do a refi on the property to get my down payment and rehab costs back. I will then continue to rent the property. I’d like to see a calculator to help determine what the cash flow would look like once the property is rented but also takes into account the new refi loan.
Great site by the way. I’ve spent a lot of time on it the last week.
Bart

15 Ed S February 5, 2012 at 4:31 pm

Thanks J, Great Tool!

We are new to real estate investing and had a question about rows 29 and 30 of the spreadsheet. What do the Variable Cost PM and the Fixed Cost PM values represent in your example?

Thanks,
Ed

16 J Scott February 8, 2012 at 6:48 pm

Hey Ed,

Those are property management costs. Some PMs charge fixed fees for management and some charge variable fees based on the rental amount. Depending on which kind of PM you have (if at all) will determine which of those rows you might use.

17 TM June 13, 2012 at 6:13 pm

J, thanks for all the information. So far I’m finding your blog as addictive as any known drug. I really enjoy your methodical approach to everything. Keep up the great work!

Question: What are the differences between the “Rehab Analysis Spreadsheet” posted here and the “SFH Rental Analysis” file posted on BiggerPockets?

Regards,
TM

18 J Scott June 13, 2012 at 7:34 pm

Hey TM -

Not sure which Rehab Analysis Spreadsheet you’re referring to (this page is the Rental Analysis Spreadsheet), but basically I have one spreadsheet that’s for doing an analysis of rentals (it gives info like cash flow, cash-on-cash return, etc…it’s the one on this page) and I have a spreadsheet for analyzing rehabs/flips (that one basically uses my “Flip Formula” approach to the analysis).

If you need help with either, let me know…

19 TM June 13, 2012 at 7:58 pm

Sorry about that, I copy/pasted the wrong item. Let me restate the question to be clearer.

What are the differences between the file posted here, entitled “Basic_Rental_Analysis_Worksheet.xlsx” and the file posted on BiggerPockets (http://www.biggerpockets.com/files/user/JasonScott/file/20-sfh-rental-analysis) entitled “SFH_Rental_Analysis.xlsx” ?

20 TM June 13, 2012 at 8:07 pm

As a followup, I ask because the file on BP is 28kb and was posted “almost 2 years ago”. This spreadsheet appears to have more data in it. Including “CF & ROI” information in rows 6-9 cols J-P

The file posted here is 31kb, unknown posting date, and does not appear to include this data yet has a larger file size.

I really do appreciate all the effort you’ve put into everything. I would like to make sure I have the most current/complete spreadsheet.

TIA,
TM

21 J Scott June 13, 2012 at 8:16 pm

Hey TM -

They are basically the same thing. They may have been uploaded a few months apart, so there may be minor differences, but to be honest, I don’t remember which one was newer. For the most part though, they are identical.

22 J Scott June 13, 2012 at 8:19 pm

Hey TM -

Those extra cells in the BP version are basically the way to see the difference in cash-flow and COC if I’m not paying a Property Manager to manage the property. The reason for this is PM costs are the one area where I can make a decision to spend or save a good bit of money on an on-going basis, so I like to see my best- and worst-case numbers based on how I decide to do PM for my units.

I believe everything else is the same, though I’m not sure why the file sizes seem weird…

23 TM June 13, 2012 at 9:17 pm

J,
Thanks for the commentary. Difference in file sizes is small enough that it could be as simple as more formatting (Bold, Color, Borders) in the 123flip version.

Maybe time to upload a new version to 123flip & BP based on lessons learned since they were created? If you do update, maybe add an instructions tab or box at the top with the wording from this 123flip page.

“For this spreadsheet, all the numbers in RED along the top and the left side (Column E) are the configurable inputs. Enter your actual parameters there, and the rest of the spreadsheet updates accordingly.

The important return values (Cash Flow, Cash-on-Cash Return and Total Return) are shown in YELLOW at the top in the “Cash Flow & ROI” box. If any of this spreadsheet doesn’t make sense, check out our tutorial on Introduction to Financial Analysis. It should give you all the background you need to understand the spreadsheet (assuming you have a basic understanding of Excel to begin with).”

Thanks again for all the effort you put into this site!!

TM

24 Nelson Keboutlule October 4, 2012 at 10:48 am

Thank you very much. This is very clear & helpfull

25 D Falcon February 18, 2013 at 7:56 am

Hi J Scott,

First off, thank you for your website, blog and posts on BP. Truly a wealth of information.

I was looking for a spreadsheet for Multi Family Rental Analysis. You had something on BP, but I cannot seem to find it again!

Much appreciated,
Dan Falcon

Leave a Comment