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

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.

Who wants to evaluate my spreadsheet/deal analyzer?

Most Popular Reply