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