One Spreadsheet To Rule Them All: Building A Lighting Fast Lead Analyzer
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.
Want more articles like this?
Create an account today to get BiggerPocket's best blog articles delivered to your inboxSign up for free
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.
What You Need:
You don’t need much to get started:
- You need Excel and basic excel knowledge (adding formulas)
- 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
- Open Excel and create a new blank workbook.
- Using the first row, start naming and resizing the columns into information categories that you want to track. For the basics you will need:
- Floor configuration (bedrooms/baths/garage)
- Square footage
- Asking price
- Max allowable offer
- Offer amount
- EMD amount
- Financing amount
- Financing costs
- Closing costs
- Purchase costs
- Rehab costs
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.
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.
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:
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:
- 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?
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