Rental Analysis Spreadsheet

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






{ 16 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.

Leave a Comment