Deal Evaluation Spreadsheet - Am I missing anything?

Question

I spent some time combining 4 or 5 other spreadsheets that do things like calculate your mortgage payments with various financing options, calculate your cap ex, and calculate your cash flow, GSI, GOI, NOI, CoC, etc.

I think it's pretty cool but there are probably more sophisticated sheets out there. I tried to keep this simple, focusing on just the numbers you want to see.

Hopefully it will not only be useful to myself, but others too, so I've uploaded it to the File Place.

I'd be thrilled to get some feedback on it as well. Are there things I can improve or that would be useful to see? Errors, omissions, etc?

Here's the link: Deal Evaluation - Mortgage Varriations, Cap Ex, Closing Costs

James Stevens · Answer

It's a pretty good start, but I didn't see anyplace to put in repair costs.  Have you used the calculators here on BP?  They are very well designed and not only cover all the different numbers, but let you put photos of the property in with it and gives all the details about the property.  It also breaks out the estimated performance of the property over the life of the loan.  Once printed out, they look very professional and work well as something to show to lenders to show them how you ran your numbers.

I'm not trying to be negative about what you took your time putting together.  I'm just saying, that when there are already better tools available right here, why try to "re-invent the wheel?"

Immanuel Sibero · Answer

Hi there,

Excel is a great tool for this kind of analysis and I use it often for various things, not just real estate analysis (I'm a real estate newbie). Just a couple of comments:

-  I usually see Capital Expenditures set up as a component (i.e. percentage) of operating items, much like repairs, vacancies, PM, etc. The worksheet sets up Capex as a component of "All In" (i.e. acquisition related) which will skew the metrics normally calculated based on "All In" (i.e. CoC).

- The closing costs tab and capex tabs are very detail. Given that this is a tool to evaluate deals, are the detail breakdowns necessary? When I read the subject of the initial post, I envisioned a short, compressed, quick worksheet to determine if a deal merits further investigation. When I saw the detail breakdown of closing costs and capital expenditures, the worksheet looked more like a tool to "account" for a deal, not necessarily to "evaluate" a deal.

Again I'm a newbie, I'm learning ... :-)

Thanks,

Immanuel

Joe Henry · Answer

To follow up with the suggestions made here I've uploaded a new version: https://www.biggerpockets.com/files/user/JoeH29/fi...

• Added instructions page
• Added monthly cap ex row
• Added explanations to some of the rows in Deal Evaluation  
• Added Permits and Inspection costs to the up-front cap ex calculations

Immanuel Sibero · Answer

@Joe Henry

That's great Joe.... Time to put it to good use :-)

All the best!

Joe Henry · Answer

@Immanuel Sibero,

- First point. Thank you for bringing this up. Yes, I am missing a place in monthly expenses for cap ex. There are two types of cap ex expenses - the ones going into the project up front (e.g. the house needs a new roof immediately) and the ones you need to save for monthly, (e.g. in 5 years it will need a new water heater). So the cap ex I have listed is for the up front cost, costs you know you'll have just by purchasing the property. Thus, they are added to the all-in figure and CoC. You can either factor monthly cap ex into the monthly repair row, or add a row for saving monthly income for future cap ex. I think I will update the sheet to have it's own monthly cap ex row.

- Second point. Yes it's very detailed haha. I am new to RE as well, so I wanted to make a sheet that would help prevent me from overlooking things, thus the amount of detail. You can just add a number in the column next to each cap ex section heading (e.g. B13 for demo), instead of filling out all the items in each section. That way you can do ball-park estimates for each cap ex category.

Saeed Bafaqih · Answer

Great,, Thank you

Joe Henry · Answer

@Christian Costa, thanks! I think I need to add some instructions maybe about how the three pages in the workbook interact, and do please let me know if there's anything else you notice.

One thing I remembered is not included is cost of construction inspections and permits in the Cap Ex sheet. Don't forget those if you need them ;)

Christian Costa · Answer

@Joe Henry Oh I see okay thank you for clearing that up. Overall this is a great spreadsheet and I agree with you I rather use Excel than the BP calculator just for the flexibility part of Excel. Thank you for offering your spreadsheet!

Joe Henry · Answer

@Christian Costa, the number from B41 should be a negative value. If you enter a percentage for management you'll see the calculated management cost is a negative number because it's an expense. All the other expenses you enter should be negative numbers, not positive, which will make B41 a negative number.

If you use the form this way the NOI formula is correct. If you like using positive numbers for the expenses though that's fine (it is easier to enter positive numbers there), and then you could change your formula for NOI to what you posted above.

Thanks for asking though, hope you get some use from the spreadsheet.

Christian Costa · Answer

In the NOI section you're adding and income and expenses.

the formula should be =B21-B41

Deal Evaluation Spreadsheet - Am I missing anything?

Most Popular Reply