Deal Analysis Spreasheet

[Courtney Araujo]

Hi BP! 

I've created a deal analysis spreadsheet so that I can easily do some quick and dirty number crunching when looking over deals. This spreadsheet analyzes rentals or flips. I was hoping to get some opinions on the spreadsheet to make sure I haven't missed anything. Others are welcome to use it! (make sure you audit it and give it a peer review). I hope the link/upload worked!

https://www.biggerpockets.com/files/user/CourtneyA2/file/rei-deal-analysis-spreadsheet

A brief summary of the spreadsheet:

Input Tab:
- Ability to input up to 5 scenarios.
- Red Font is formula based, all others are inputs. Scenario 1 is linked up to Rehab Analysis Tab.
- When "Holding Period" is set to 12 the scenario treats the deal as Buy&Hold, when it is less than 12 it treats it as a Flip.
- For Amort Schedule: This is for forecasting, calculates the IRR for holding on to the investment. Select which scenario you'd like to see. Projects up to 15 years.
- Property Features, not necessary, but informative and flows through to output tab.

Rehab Analysis Tab: This comes from J Scott's Rehab Analysis Spreadsheet that is on BP. This allows rehab items to be easily broken down when walking through a home and tallied up.

Output Tab:
- Individual Components for 5 scenarios are summarized. PITI; Vacancy/Maintence/CapEx/PM; Purchase Costs; Selling Costs; Gross Monthly Income.
- Calculated Initial Return table summarizes incomes and expenses, formulas switch depending on buy/hold versus flip. I won't go into the details, but feel free to PM me if you have questions. This section includes annual Cash flow, COC ROI and Debt Service Coverage Ratio among other items. They are conditionally formatted Green/Yellow/Red to highlight income and expenses and negative and positive returns.
- Maximum Purchase Price/Rules of Thumb give quick ideas of what a property could be purchased at to be a good investment. Scenario 1 through 5 as you move from column K to O.
- Amortization and Projections calculates the IRR on the property if you were to hold it for whatever you specified on the inputs tab (up to 15 years). Start up contains all purchase and rehab information, including ARV, so it will include your new equity position. Each year going forward will have loan paydown and factor in the increased expenses/income assumptions chosen on inputs tab. Appreciation is not factored into the spreadsheet yet

I'd love to hear others' feedback! Let me know if you have any questions, I'd be more than happy to answer them.

Thanks,

Courtney

[Arpan Patel]

Okay there is a lot to cover so I will break it down sheet by sheet.

Inputs

1) I don't know why you have a vacancy rate for a flip

2) Insurance will be much higher for a flip because you are getting a builders risk insurance, not a normal home owners

3) Why are there 0 holding costs? Wouldn't you have at least the mortgage and insurance even if the tenant is paying for utilities?

4) What is the difference between scenario 1 and 2? Just the rent number?

5) Why would the value be the same for the ARV between scenario 1 and 2 even though in scenario 1 you are dong about half in repairs?

6) Closing costs are going to be more than just the realtor fees. Remember there are prorated taxes and title and many even a lawyer or a concession

7) Why is there is a difference between 3 and 4 when both have the same repair number? Why is one scenario worth 20k more than the other when you are doing the same thing in both scenarios?

8) Does adding 25k in rehab really yield you another 45k in equity between 4 and 5?

9) Why is the interest rate on 3 different from 1 and 2? different lender?

10) Why is it the same rate for 4 and 5 and 1 and 2? 4 and 5 are flips so are you saying the lender you picked will do both models at the same rate? That doesn't sound right to me

11) How are you generating monthly income from a flip in scenarios 4 and 5? How can you lease a place that you are remodeling to flip?

12) Why do you have 30 year loans for you analysis for cash flow and have 10 year amortization for your actual amortization schedule?

13) Why do you have realtor fees at 7% and in your analysis all closing costs are 6%

Rehab Analysis:

1) You only factored one month for your all your holding costs. That should be 6 times that number for a flip.

2) By your math you are showing a 41k loss on the house

3) Hard to evaluate scope of work with no pictures but if you aren't doing any framing then why are you doing so much duct work? You typically need to frame the vents in.

4) Hardwood costs seems low but maybe you are only doing a small rooms worth

5) Counter top installation is a bit off in my guess but maybe you have a small easy kitchen

6) Appliance installation is usually carried out by the vendor you bought it from and it is not usually 1000

7) Door knobs seems a bit low unless you are only doing a few

8) Backsplach material seems low as well

Output is visually fine but I have many questions about inputs and if they are off then your outputs will be off as wel

1) IRR looks suspect as well. If you are getting a a great debt coverage ratio then why is your IRR less than your cap rate? Something if very off

Why are you running your amort schedule on 28.3 years? That was not indicated anywhere else on your spreadsheet. Also I think your Amort formulas need to be checked. Not sure I like the function to have a floor function in it. Plus you are going negative at the end of the 30 years and that wouldn't make sense for a 30 year loan

No idea what RCD is for

I don't understand the purpose of the flipping chart.

This looks like it took a lot of time so don't get too happy making the tool and not as happy about executing the plan. Good luck and let me know what you think of the questions about your spreadsheet.