Follow Us on Social Media

email icon rss icon linked.in icon google plus icon twitter icon facebook icon

One Spreadsheet To Rule Them All: Building A Lighting Fast Lead Analyzer

by Glenn Espinosa on February 1, 2013 · 11 comments

  
Lead Manager

An important part of real estate investing is learning methods to increase the efficiency of your everyday business processes. The more efficient your business becomes, the more deals you acquire, the more success you will have.

As I graduated from doing one or two flips a year, I quickly found out the importance of having a system in place to quickly analyze leads and adjust numbers as I found out more information. My old system of handwritten notes and numbers scribbled onto scrap pieces of paper and carried around with me as I followed up on leads simply wasn’t cutting it anymore.

So I created an excel spreadsheet. It’s not the prettiest of things and I’m sure there are investors more experienced than I who have more complicated, more precise, and more efficient spreadsheets. However, this one spreadsheet alone increased my deal analysis from 2-5 leads a day to over 25+. In the past month I’ve been able to analyze over 200 potential leads and ultimately buy more houses.

***To download the actual Spreadsheet, click here and get it in the BiggerPockets FilePlace***

So lets get started on creating an awesome spreadsheet.

Photo One

What You Need:

You don’t need much to get started:

  • You need Excel and basic excel knowledge (adding formulas)
  • Calculator
  • An idea of how much you pay for financing (e.g. HML, private lending, credit card APR, cash?)
  • Historical data of your closing costs (no history? I can give you ballparks but you have to do your own research)

Creating Template Outline

  1. Open Excel and create a new blank workbook.
  2. Using the first row, start naming and resizing the columns into information categories that you want to track. For the basics you will need:
  • Address
  • Floor configuration (bedrooms/baths/garage)
  • Square footage
  • Asking price
  • ARV
  • Max allowable offer
  • Offer amount
  • EMD amount
  • Financing amount
  • Financing costs
  • Closing costs
  • Purchase costs
  • Rehab costs
  • Profit

As you become more sophisticated you can start tracking other things such as ROI, IRR, and even get as complicated as calculating for +/- profit as according to +/- rehab. It’s up to you to decide what calculations are important to you and your business, but for now lets continue with the basics.

Tip: As these are your headers for all your information, go ahead and experiment with the fonts and colors to help these categories stick out more. For mine I added both background and font colors, bold, and increased the font by +1.

Photo 2

Adding The 5 Essential Formulas

1.) Max Allowable Offer

Going from left to right you have a few static categories that are simply plug ins for each property you analyze. The first category that will need a formula is Max Allowable Offer (MAO). For my flips I calculate my MAO using a 15% profit from the ARV. You will have to decide your own profit percentage but the rest of the formula is the same.

MAO = ARV – (ARV * 15%) – Financing costs – Purchase costs – Selling costs – Rehab

To input that formula into Excel you will need to click on the first MAO entry box (F2) and type in =E2-(E2*0.15)-J2-K2-L2-M2

If you added more categories or changed the order of a few, then make sure you are subtracting the correct numbers from your MAO.

Tip: So that you don’t have to type each formula into each row, go ahead and copy your formula for F2, highlight a stretch of the F column, and paste the formula into those cells. Excel is ingenious in that it will update each formula to correspond with its particular row. Also, go ahead and set the number style to accounting so that the dollar signs are automatically placed into each figure.

wJ2CM2b - Imgur

2.) Financing Costs

The next step, calculating your financing costs, is a tricky one. You have to analyze your own situation and find out the best way to come up with this number for yourself. As an example, for me I have a Hard money lender that I have worked with that offers me 12% and 4 points for 6 month loans.

To calculate that I take my HML amount and multiply by 12%. I then take that number and divide by 12 to reveal my monthly interest payment. I then multiply that monthly payment by 10 (4 points + 6 months of interest) to reveal my final cost of financing.

For example, if you have a HML who will fund your projects with a 12 month loan at 15% with 5 points your formula will be as follows:

(HML amount * .15 / 12) * 17 (12 months + 5 points) = financing costs

If I2 is your HML amount, in excel it may look like the following:

=(I2*0.15/12)*17  

Note: This number is your MAXIMUM costs for financing. I practice being conservative with my numbers and as a general rule of thumb, I budget for the worst case scenario (holding a property for the maximum loan length).

3.) Closing Costs

Closing costs is next and I calculate this using the ARV. We know that the two main components of selling costs are agent commissions and fees paid at closing. Typically I pay 6% of the ARV to my agent as well as the buyer’s agent. Also, I’ve found that historically I pay an average of 1.5% of the ARV in fees such as title, survey, and appraisal. In total, I pay an average of 8.5% of the ARV whenever I sell a home. To input that into excel we will use the formula = ARV * .085   Does your local market have sellers cover some of the buyers closing costs? Are you a realtor and list your own homes? Do you list everything FSBO? Whatever you situation, make sure to calculate your closing costs accordingly.

4.) Purchase Costs

Purchase costs is also pretty straightforward. Typically, I have found that I spend around 3.5% – 4% of the purchase price when purchasing a flip. This includes items such as title insurance, inspections, appraisals, etc. Look over your own historical data and figure out what you spend on average when purchasing a home. Remember to always round up. If you use 4% like I do, your formula will look as follows = Offer amount * .04

5.) Potential Profit

The best column in my opinion is the profit column. To calculate this you will simple subtract all of your expenses from the ARV. The formula will be as follows = ARV – offer amount – financing costs – purchase costs – selling costs – rehab

Methods of Improvement

So thats basically it! With those 5 formulas you can start plugging in information and quickly analyzing and keeping track of your leads.
But what is a spreadsheet with out more options and more data points to analyze. Some things to think about that you could add to your own spreadsheet:

  • ROI
  • Color code system (I color code my leads with 4 colors. Red = no deal, blue = offer placed/follow up, green = accepted, yellow = keep an eye on for the future)
  • Money down when compared to your financing amount. This is simple to calculate: (Purchase price + rehab) – financing amount
  • Rehab as compared to profit? Maybe this could help you identify which flips will require the less work yet yield the most profit?
  • Add different financing options? As you advance in your flipping career you will start to find different financing options for your deals. I’m currently adding a drop down menu in my spreadsheet to include private money, different HML fee structures, as well as a no leverage option.
  • +/- percentages. This could be easy to implement. All you would have to do is add columns that +/- your rehab costs by whatever percentage you want. Then you create +/- profit columns and tie those in their respective rehab numbers. Making sense?
  • LTV

The options to improve your spreadsheet are unlimited. Essentially, you as a rehabber have to find what numbers matter to you the most. This spreadsheet will help in keeping your leads organized and knowing what price you can come in at.

Disclaimer: This is a pretty basic analysis model that can help new investors analyze a lead faster. This spreadsheet only works if you are calculating ARV’s and rehab expenses correctly and building in adequate contingencies in your formulas. It is up to you as an investor to find your own costs percentages when calculating your purchasing and selling expenses.

So what do you think? Any pros out there with similar (or totally different) systems of analyzing leads? We’d love to hear from you! If this tutorial helped you out (or didn’t), please let me know!

Photo:Menage a Moi

Email *
  



{ 11 comments… read them below or add one }

Danny Johnson February 1, 2013 at 7:41 am

Hey Glenn.

Great spreadsheet. I’m glad you walked through every step of setting it up.

Reply

Glenn Espinosa February 1, 2013 at 8:34 am

Thanks for the comment, Danny.

You know I actually looked at your system a few months back and I took a couple of the features that I liked and used it to build my own spreadsheet.

Small world, eh!

Thanks and happy investing,

Glenn

Reply

Danny Johnson February 1, 2013 at 10:31 am

That’s why I like it so much!

Reply

Clay Huber February 1, 2013 at 8:16 am

Great post Glenn.

The quicker people learn to love spreadsheets, the more time they will free up in their schedules.

Reply

Glenn Espinosa February 1, 2013 at 8:37 am

Thanks for the comment, Clay.

You are exactly right. The 1-2 hours it took me to put this spreadsheet together has saved me days if not weeks worth of lead analysis. Not only that, it has allowed me to keep better track of my offers so that I can follow up down the road.

Happy investing,

Glenn

Reply

Jason February 1, 2013 at 10:34 am

Great tool, will you post the final product on your website or link to it here please?

Reply

Joshua Dorkin February 1, 2013 at 1:45 pm

Jason – Maybe Glenn can upload it to the BiggerPockets FilePlace so everyone can download it. Glenn – let me know if you can do that and we’ll link the post directly to it.

Reply

Jason February 1, 2013 at 3:10 pm

Thanks, that would be very helpful to use as a template and make changes as necessary.

Reply

Glenn Espinosa February 2, 2013 at 9:41 am

Josh – I uploaded a lite version of the spreadsheet into my fileplace!
Jason – Let me know how else I can help. The template will serve as a great example when putting together you own personal spreadsheet that meets your company’s needs.

Happy investing!

Reply

Will February 3, 2013 at 12:20 pm

Thanks guys

Reply

Shaun February 13, 2013 at 8:26 am

Great article.
Good analysis tools are essential in this business.
I like how you show how simple it is to make your own custom made tool with a basic spreadsheet.
I think a lot of newbies think they need some sleek software to analyze deals and track them.
Then they spend $2k and a year learning a program instead of looking for deals!

One issue I had with the method is using the max length of the loan.
That isn’t bad but you could inadvertently make you finance costs to low by getting short loans.
If I read it right I could get a dangerous 3 month loan with tough terms and it will look better than a cheaper, safer, 12 month loan just because paying on a longer term costs more interest.
To take it to the absurd extreme you’d never get a conventional loan since 4% over 30 years is a lo of money!

Reply

Leave a Comment

Comment Policy:

• Use your real name and only your name in the field designated for your name.
• No keywords allowed as anchor text in the name or comment fields.
• No signature links allowed under your comments
• You may use links in the body of your comment, but it must be relevant to the discussion at hand, and not merely be some promotional link.
• We will have NO reservations about deleting your content if we feel you are posting merely to get a link without adding value to our discussion.
If you add value, but still post keywords, we'll use your comment, but remove your link and keywords.
• For more information about acceptable practice, see our site rules.

Want your photo to appear next to your comments? Set up your Gravatar today.

Previous post:

Next post: