Rental Analysis Spreadsheet
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…
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.
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.
Thanks!!!
This is great, thanks!
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.
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
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
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!!
Hey Bree,
Glad it’s helpful…let me know if you need any help with it…
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.
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
the spreadsheet will not download keeps wanting the registration over and over help
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…
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
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
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.
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
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…
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” ?
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
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.
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…
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
Thank you very much. This is very clear & helpfull
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
Great info and Books J. I appreciate all the forms and advice you give.
The link above for the Analysis Toolpak is dead but this link shows quick steps on how to add it in 2012. I just added it for office 2013 and worked like the steps show.
https://support.office.com/en-us/article/Load-the-Analysis-ToolPak-6a63e598-cd6d-42e3-9317-6b40ba1a66b4
Thanks Michael!!!
Hi,
Do you have a financial analysis version for condo investments and rentals? I would appreciate if you can email me a copy?
Thx!
Hi Scott, great spreadsheet! I notice though that your spreadsheet does not have a row for Income Tax that is payable on the Rental Income. Is there a specific reason why you don’t account for Income Tax in your analysis?
Thanks.
Hey Bruce,
I consider taxes to be an overall cost of doing business, not necessarily a cost towards a particular investment(s). And the tax I pay on an investment may be completed unrelated to the return I’ve made on that investment.
For example, let’s say I earn $10,000 on an investment. If my total income in that year was $500,000, I’ll pay about 50% of that $10,000 in state/federal tax. But, if my total income for that year was a $10,000 loss, I’ll pay nothing on that $10,000 gain. In other words, taxes are rarely a function of the success/failure of a particular investment, so I don’t like to consider them as part of the investment analysis.
Does that make sense?