Who wants to evaluate my spreadsheet/deal analyzer?

Question

I'm sure I made a few mistakes but its becoming a useful tool. Whatcha think?

Tab 1 - Deal Overview

Tab 2 - 10/20 Year Financials

Tab 3 - Loan 1 Amortization

Tab 4 _ Loan 2 Amortization

https://www.biggerpockets.com/files/user/JBradford77/file/deal-analyzer

Derek Carroll · Answer

Brad Saari On the 10 year tab You're calculating cash on cash including the down payment amount which is incorrect since you show that you're actually getting a loan for this amount. Cash on cash should only be calculated based on cash that has actually left your wallet. If you're going to the trouble of estimating future property value along side of loan balances then I would include a Column for equity. I ALWAYS include sensitivity tables, on as many variables as I can (rent growth, appreciation, expense growth, sales price, ect) The fields where you also make assumptions (capex @ 5%, interest rate, ect) I would break each of those out as a separate column to calculate from so that you can easily change the assumptions to test different scenarios. Your ROI calculation is off in a few ways. You're not taking into account the loan balances if any that are being paid off, you're again using total investment which includes borrowed funds. You're not taking into account time value of money which is extremely important over long time horizons like this. I'd create another field for net cash flow in the sale year after repayment of debt, and then run an IRR calculation using all of the annual cash flows. These are what jump out at me. Hard to comment further from my phone. In general though it's well laid out and easy to read.

Julie Marquez · Answer

Very nice spreadsheet, thanks for sharing! As a beginner, this is nice to see where the calculations are pulling from. That way I can really learn and understand the root of what they mean, and it is visually nice to see as I learn.My only critique is not easily knowing what cells to input, versus what ones calculate. Obviously I can figure that out as I go along, but I'll probably highlight the input cells. Thanks again for sharing!

Ross Dillingham · Answer

@Brad Saari great thanks so much!

Brad Saari · Answer

@Ross Dillingham Those are industry standard tests for quickly evaluating a deal. Rule of 2: The 2% rule says that for a rental property investment to be “good”, the monthly rent should be equal to or higher than 2% of the purchase price. I actually had a flub in my calc - change it to monthly rental income / purchase price. I don't really pay attention to this one as its not realistic for my market. I generally shoot for over 1%. 50% Rule: another gauge to quickly analyze a deal. Most investors find that their operating expense historically net out to 50% of revenues or rental income. There is a lot that makes up op expenses and its probably the trickiest part of analyzing a deal. But to quickly apply while analyzing: Total rental income - (total rental income *.5) = NOI. You can then apply your debt servicing to arrive at cash flow. There is no 40% rule, I made it up as I feel 40% is a more accurate gauge for me. Expense as a % on income is just showing how close I am to 40% or 50% i am in my operating expense assumptions.

Ross Dillingham · Answer

Great spread sheet. Can you explain the following calculations: Rule of 2 Expense as % of Income 50% Rule 40% Rule

Brad Saari · Answer

Alright gang, I've uploaded a more accurate copy regarding CoC and ROI. I also followed some of Derek's suggestions on sensitivity variables, tho I'm just cancelling out income and expense increases at 2% each. Changed some formatting and usability stuff. If anyone wants to take a stab at TMV/PV or IRR please feel free but I'm omitting those for now

Derek Carroll · Answer

I wouldn't worry about a discount rate, rather i'd do an IRR calculation. discount rate is often a guess (even with academics and investment professionals) because it requires assumptions that don't always hold true. The basic idea is discount rate = rate you could earn on other investments if your money were invested elsewhere + a risk premium for the selected investment. Both of these can be hard to determine so 8-12% is a good ballpark that people like to use, but that may not be accurate for every situation. IRR is a more useful measurement tool than NPV because it doesn't require you to pick a discount rate. However, if you understand how the two formulas are related then it's helpful to see both at the same time. Here's some more reading on the subject: http://www.propertymetrics.com/blog/2013/06/28/npv...

Brad Saari · Answer

@Derek Carroll Can you explain what you meant by "Your ROI calculation is off in a few ways. You're not taking into account the loan balances if any that are being paid off, you're again using total investment which includes borrowed funds." Isn't Total ROI simply = annual cash flow + equity / Money out of Pocket? Doesn't that hold true year after year? For Time Value of Money/Present Value what would you use as the discount rate? Loan APR?

Brad Saari · Answer

@Derek Carroll I really appreciate the input. I'll post the suggested changes tomorrow.

Derek Carroll · Answer

Brad Saari l'll try to take a look and offer feedback in a bit. Quickly though based on the comment from above on "professional" models input cells are formatted with bold blue text so that it's easy to see both on screen and in print.