If you’re looking to learn how to analyze a real estate deal, then you’ve found the right place.
Below we will walk you through how to use The World’s Greatest Real Estate Deal Analysis Spreadsheet™ (which you can download for free) to analyze an investment property. Throughout we will also show you how to analyze your entire investment strategy using the Real Estate Financial Planner™ software.
Before we begin with the detailed deal analysis instructions, let’s get an overview of the different parts of the deal analysis spreadsheet so you know what each area does and why it is there.
- Property Purchase Information – In this section of the spreadsheet, you will enter inputs about the property that you are considering purchasing. This is where we will start first.
- Income – This section addresses the income from the property.
- Expenses – This section deals with the expenses on the property.
- Cash on Cash Return on Investment – This section walks you through the calculations to ultimately get to cash on cash return on investment.
- Overall Analysis – This section analyzes the overall return from buying and then selling the property. It includes detailed returns broken out by the four areas of return from the Return on Investment Quadrant™ and Return on Equity Quadrant™ for each year.
- Return on Investment Quadrant™ and Return on Equity Quadrant™ – This quickly summarize the Return on Investment Quadrant™ and Return on Equity Quadrant™ for the first year visually.
- Partnerships – This section allows you to model buying this property with partners of various types (credit partner who gets the loan, deal syndicator and/or money partners).
- Capital Expenses (Cap Ex) – This section allows you to model all your capital expenses (cap ex) on the property.
- Income Sensitivity – This section allows you to quickly see the impact on cash flow and cash on cash return on investment as the income on the property varies.
- Option Fee Sensitivity – If you are offering this property as a lease-option, this section allows you to see how cash flow and cash on cash return on investment varies based on the option fee you receive from your tenant-buyer for the lease-option.
- Tenant-Buyer Monthly Payment – This section allows you to see a variety of calculations related to what the tenant-buyer might need to be paying in monthly payments if you were going to offer the property on a lease-option. Use it to determine what rent you are going to charge for the property on your lease-option.
- Charts of Return on Investment Quadrants™ and Return on Equity Quadrants™ – These are charts of each of the four areas of return for the Return on Investment Quadrant™ and Return on Equity Quadrant™ for the full 40 year investment horizon so you can see these returns and how they behave over time.
- Purchase Price versus Rent Sensitivity Table for Cash on Cash Return On Investment – This section allows you to see the impact on cash on cash return on investment of offering more or less in purchase price and getting more or less in rent. It is great to look at when you’re out looking at the property and considering making an offer as you firm up your rent estimates.
- Your Custom Calculations – This section allows you to extend the capabilities and calculations of the spreadsheet by adding any additional notes or calculations here.
- Reset Button, Legal and Other Info – This section has the reset button to return the spreadsheet to its default settings, legal disclosures, some very basic instructions.
Helpful Tips on Analyzing Investment Properties Right on Spreadsheet
If you’d like more information on almost any field in the spreadsheet, simply mouse-over the red triangle in the spreadsheet for detail information about that cell.
Once you mouse-over a helpful tip box will appear giving you additional information about that cell (sample shown below).
If You “Mess Up”, Just “Reset All Values”
We’re about to start going through the process of entering values, but before we do, I want to relieve some possible stress and anxiety.
You can’t mess up the spreadsheet.
All the calculations are locked and you can reset the spreadsheet to the default values by clicking on the “Reset All Values” button.
Of course, don’t click this button after you’ve taken the time to analyze a property you want to save. For that, save a copy of the spreadsheet first.
Key to Cell Color
The spreadsheet is color coded to make it easier to determine which cells you should be filling in and which cells are calculated for you.
- Orange Cells – Primary input cells. You should be entering in values in these cells of the spreadsheet.
- Green Cells – Informational cells. You should not try to edit these.
- Blue Cells – Primary calculation cells. This uses your inputs (from the orange cells) and does calculations with them. You should not try to edit these.
- Yellow Cells – Second calculation cells. These tend to be summary calculations. Like blue cells, these use your inputs (from the orange cells) and does calculations with them. You should not try to edit these.
- Light Gray Cells – Tertiary calculation cells. These tend to show what percentage a value is as part of a larger whole. Like blue and yellow cells, they take your inputs and presents information based on those. You should not try to edit these.
- Dark Gray Cells – No input or output. You should not try to edit these.
Of course, the white cells are just labels. Nothing for you to enter or edit in those except in the section for adding your own notes and calculations (section labeled with “14”).
Enter Property Details
Open a copy of the The World’s Greatest Real Estate Deal Analysis Spreadsheet™ in Microsoft Excel.
This is an orange input field that allows you to put in the address and/or a short description of the property that you are intend to analyze.
A couple examples… if you plan to analyze the property at 1234 Main St in Fort Collins, Colorado, you might put: “1234 Main St, Fort Collins, CO 80525” in this field.
Or, if you were buying it from the MLS you might also append the MLS number to the address to make it easy for you to find the property again. That might look like: “1234 Main St, Fort Collins, CO 80525 – MLS #987654”.
Other times, we might put what we expect to do with the property in this field as well to differentiate between different strategies. To continue with our example, if we are debating whether to use the property as a long-term buy and hold but might also consider offering it as a lease-option we might use the following:
- “1234 Main St, Fort Collins, CO 80525 – MLS #987654 – Buy and Hold”
- “1234 Main St, Fort Collins, CO 80525 – MLS #987654 – Lease-Option”
We might also use this field to differentiate different financing strategies. Three more examples:
- “1234 Main St, Fort Collins, CO 80525 – MLS #987654 – Nomad 5% Down”
- “1234 Main St, Fort Collins, CO 80525 – MLS #987654 – Nomad 5% Down, Pre-Pay PMI”
- “1234 Main St, Fort Collins, CO 80525 – MLS #987654 – Nomad 5% Down, Lender-Paid PMI”
- “1234 Main St, Fort Collins, CO 80525 – MLS #987654 – 20% Down Rental”
- “1234 Main St, Fort Collins, CO 80525 – MLS #987654 – 20% Down Owner-Occupy”
What you decide to use here is ultimately up to you, but taking a few moments to describe what you’re analyzing will be helpful if you return to look at it again later.
You can also use section 14 to add more detailed notes or to do some additional calculations.
Property Purchase Information
We will be starting at the very top left of the spreadsheet (section 1) and entering in the property details one at a time. Once we’ve entered in all the property details, I will walk you through the output and how to interpret the output from the spreadsheet.
After Repair Value (ARV)
The After Repair Value (abbreviated ARV) is the value of the property at the time of purchase, if all the work you’ve budgeted to be done to the property was completed.
When you’re buying a property below current fair market value, the ARV will be higher than the Purchase Price.
In some markets where you having to bid above asking price (and in some cases above appraised value) to get your offer accepted and purchase property, your Purchase Price might be higher than the After Repair Value.
A little trick I use in certain situations—especially when buying new construction properties—is to enter in a simple formula for the After Repair Value instead of a fixed number. For example, if I was buying a new construction property, I might use the following formula instead of just entering in a single value for the After Repair Value.
As you can see in the image above, instead of just entering in $398,914, I used the simple formula: “=376839+5000+525+1200+350+6000+8000+1000” (if you’re not used to working in Excel… note that it starts with an equal sign to show that it is a formula).
I did this so that I can “break out” what the component parts of the value were. In this example, this is a new construction property with:
- A base price of $376,839 plus
- $5,000 for air conditioning plus
- $525 for an over the stove microwave oven plus
- $1,200 for cabinets upgrade (added by the builder) plus
- $350 for garage door openers plus
- $6,000 for backyard landscaping plus
- $8,000 for backyard fencing plus
- $1,000 for window blinds
When summed up, it totals $398,914 and that is what appears in the After Repair Value field. But, now I can also look at the formula and see what extras I included and for how much. I could add what the actual items and the cost in the notes section at the bottom of the spreadsheet as well.
The Purchase Price is the price that you’re buying the property for.
If you’re buying a property from the MLS and paying full retail for it, it is extremely common for your After Repair Value to be the same as your Purchase Price.
Do you remember the “trick” we used for breaking out the costs when buying new construction properties with the After Repair Value? You can use the same trick with Purchase Price as well to break out what is included in the purchase.
When a seller is willing to contribute some of the proceeds from the sale as a credit to you, we call those Seller Concessions.
The most common Seller Concessions are when a seller is willing to contribute some money toward your closing costs or your loan costs.
The spreadsheet will also calculate what percentage of the Purchase Price your Seller Concessions are. The amount of Seller Concessions is usually limited by the loan program you are using. You will want to check with your lender before making your offer to make sure that you will be able to use that amount of Seller Concessions with the loan program the lender is planning to use for you.
This is why it is critically important to check with your lender BEFORE you make an offer to verify that you will be able to use the entire Seller Concession based on both your loan program and the amount of closing costs you’ve opted to take.
In some cases you may want or need to voluntarily increase your closing costs by buying down your interest rate, paying private mortgage insurance with an up-front payment and/or choosing a more expensive insurance policy (maybe even temporarily) to make sure you use the entirety of your negotiated Seller Concessions.
Down Payment %
Next, enter in the percentage of the Purchase Price that you are going to invest as your down payment for the loan. Often this percentage will be dictated by the loan type you plan to get.
For example, if you plan to buy this property as a non-owner-occupant investment, it will often be 20% down payment. Although your lender may have a 15% down payment non-owner-occupant investor loan program (the 15% down payment loan program will likely have private mortgage insurance).
Or, you may choose to put down 25% or even 30% to get a slightly better interest rate and to borrow less… both of which will help improve cash flow.
Or, maybe you are planning to buy the property as a house hack. House hacks are properties you are moving into but you can rent part of it to generate income. The most common example is buying a duplex, triplex or four-plex. You could live in one of the units and rent out the others. If you were buying a duplex, triplex or four-plex, the most common loan types used when buying those types of multi-family units when house hacking are 3.5% down payment FHA loans or 0% down VA loans.
Another example of house hacking is getting roommates in a single family home. A common loan type for buying single family homes as an owner-occupant might be the 3% down payment conventional loan for the first property or two and then 5% down payment conventional for subsequent purchases. These are the same loan programs most Nomads™ would be using to serially acquire properties, live in the property for a year (a requirement of the lender) and then convert the property to a rental when you buy your next owner-occupant property.
Then, if you’d still consider buying it after analyzing it as a non-owner-occupant investment, consider changing the loan type and specifically the Down Payment % to the down payment you actually plan to use with the owner-occupant financing you are getting as a house hacker or Nomad™ In some real estate markets, like my market in Northern Colorado, you are very likely to have negative cash flow when you change your financing from 20% down payment to the 0%, 3%, 3.5% or 5% down payments that house hackers or Nomads™ would use.
I typically think of this negative cash flow as “financing the down payment” you opted not to put down. In other words, if you had put 20% down, you might have had positive cash flow, but by putting only 5% down, you are financing the 15% you did not put down in the form of slight negative cash flow.
To put some rough numbers to that example for my local market: if you’re considering a $300,000 property, instead of putting $60,000 down (20% of $300,000) and having break-even cash flow you might opt, as a house hacker or Nomad™, to buy the property with $15,000 down (5% of $300,000) and keep the $45,000 in cash reserves in your bank account. Putting down $45,000 less means you’re likely to have $225 or so in negative cash flow per month (using the $50 per $10,000 rule of thumb). This ignores cash flow from depreciation which will likely offset the overwhelming majority of that negative cash flow.
Continuing, it would take you 200 months or 16.67 years of $225 per month in negative cash flow to have paid $45,000 in negative cash flow. Do you think that rents will have gone up enough in the next 16.67 years to eliminate the negative cash flow by that point? Plus, you had the $45,000 you did not use as a down payment presumably invested and earning you money elsewhere during that 16.67 years.
You could add the calculation for the number of months of negative cash flow it would take to use up the down payment you saved in section 14 if you wanted to see this number as your analyze your own properties.
In some less common situations, you may actually have a negative down payment percentage when you receive money at closing instead of putting money down. As one example, if you were buying a property “subject to” the existing financing in a situation where a seller owed more on the property than the property could support, a seller might be willing to pay you to acquire the property. Again, these are unusual situations for most buyers.
Down Payment Amount
The blue colored cell Down Payment Amount is calculated for you by taking your Down Payment % and multiplying it by the Purchase Price.
It tells you the dollar amount of your down payment.
Closing Costs are the dollar amount you’re paying in closing costs.
These should include:
- your share of the title insurance and the fee the title company or attorney charges to close the transaction
- Any upfront private mortgage insurance premiums (if you’re putting less than 20% down and choosing a loan program/lender that offers this as an option)
- Any points you are paying to get your interest rate. In some cases you will receive a credit for voluntarily taking a slightly higher interest rate; in those cases make sure you subtract out the credit because it will offset your other closing cost expenses.
- The cost of your appraisal.
- The costs of things like your property inspection, surveys, radon test, pest control tests and sewer scope
- If you are paying a wholesaler a wholesale fee or a real estate agent a commission that is not already included in the purchase price be sure to add that as well.
Rent Ready Costs
Rent Ready Costs are the costs associated with getting the property ready to rent or occupy.
If you are buying a distressed property that needs work, this is where you put your costs of all the repairs, other people’s labor and—if you’re assigning a value to your own labor—the value of your labor as well.
If you are buying new construction property and you’re not rolling things like backyard landscaping, window blinds, air conditioners, garage door openers, refrigerators and fencing into the purchase by having the builder include them in the purchase price then be sure to include anything you need to come out of pocket for here.
Most rentals will have $500 to $1,000 of work from the inspection report for you to do before or shortly after you rent the property, so it is highly unusual to have $0 here.
PRO TIP: If you are buying a property that is already rented to a tenant at below-market rents, you can account for this below market rent using the Rent Ready Costs field.
Here’s how: calculate how much rent you’re missing out on by having below-market rent for the remainder of the current tenant’s lease and add that to your Rent Ready Costs.
For example, let’s say the rent is $200 below fair market rent and there are 7 months left on the lease before you opt not to renew the lease and replace them with a full fair-market rent tenant in the property. You take the $200 per month and multiply that amount by the 7 months remaining to find out how much the below-market rent is going to cost you.
If you choose to do this, be sure to use the fair-market rent for the Monthly Rent input field and not the current below-market rent since you are already accounting for the below-market rent as a one-time up-front write off.
Modeling below-market rent as a Rent Ready Costs makes the below-market rent a one-time up-front expense of acquisition instead of impacting how you evaluate your returns if it was a permanent lower rent.
Initial Reserve Account
Initial Reserve Account is the amount of money you set aside in a separate account as your initial reserves for the property.
We would typically only use Initial Reserve Account if we are putting together a partnership and need the partnership to have its own reserve funds. This field allows us to show that the money partner also needs to put up money for reserves and that those reserves will be included in our return on investment calculations.
Otherwise, for properties I’m buying personally for my own portfolio, I do not take into account reserves I have set aside as part of my Total Up Front Investment.
It would be more conservative for you to model having separate reserves for each property you buy, but few investors would analyze deals like that.
Prudent investors—even if they are not analyzing deals with separate reserve accounts—will have at least six months of all expenses for every property available to them in reserves. This would include mortgage payment (principle and interest), taxes, insurance, utilities, HOA and any expenses specific to properties they own. This is a conservative money management and risk reduction practice.
Credit Partner Up Front Fee
If you are not analyzing this property as a possible partnership with partners, then you can ignore the Credit Partner Up Front Fee.
However, if you were going to partner with someone who was going to get the loan for this property as their contribution to the partnership, you’d enter in the Initial Fee that the person signing for the loan would receive in the Partnership section of the spreadsheet (section 7).
Since that upfront fee is a cost you’ll need to have to do the deal, the spreadsheet will pull the value you entered in the Partnership section and show it here as part of the upfront costs of doing the deal. So, if you were going to give your credit partner an upfront fee to get the loan it would show up in the Total Investment (Cost to Close) field as well.
Money Partner (Loan) Points
Similar to the Credit Partner Up Front Fee, you won’t be using Money Partner (Loan) Points unless you are analyzing this deal to purchase it with partners and specifically a partnership where the money partner is going to be receiving points on money they loan to the partnership.
Unless you’re doing that kind of partnership, this cell should be blank (read that as zero).
If you are doing a partnership with a money partner and that money partner is going to be receiving upfront points on the money they loan, you’d enter that in the partnerships section (section
7) in the sub-section labeled Money Partner (Loan-Based – Interest Only) in the field for points as shown below.
If you do pay the partner points and add it to the spreadsheet, the dollar amount of those points will show up in property purchase information section as we will need to know that amount to calculate the Total Investment (Cash to Close).
Lease Option Fee
1st Loan Amount
PMI Monthly Payment Amount
1st Loan Interest Rate
1st Loan Length
2nd Loan Amount
2nd Loan Interest Rate
2nd Loan Length
Total Investment (Cost to Close)
Total Finished Square Footage
Effective Income Tax Rate
Cash Flow Analysis: Income
Monthly Rent OVERRIDE
Monthly Other Income
Annual Other Income
Gross Potential Income
Gross Operating Income
Cash Flow Analysis: Expenses
Annual HOA Dues
Other 1 and 2
Annual Additional Expenses
Management Leasing Fee
Credit Partner Yearly Fee
Cash on Cash Return
Net Operating Income (NOI)
Debt Service (1st Loan)
Principal Paid (1st Loan)
Interest Paid (1st Loan)
Private Mortgage Insurance (PMI)
Debt Service (2nd Loan)
Principal Paid (2nd Loan)
Interest Paid (2nd Loan)
Money Partner (Loan) Payment
Loan To Value (LTV) All Loans
Capital Expenditures (Cap Ex)
Annual Cash Flow
Monthly Cash Flow
Cash on Cash Return on Investment (CoC ROI)
Cash Flow from Depreciation
Annual Cash Flow (incl. Depreciation)
Cash on Cash ROI (incl. Depreciation)
Debt Service Coverage Ratio
Gross Rent Multiplier
Capitalization Rate (Cap Rate)
Overall Analysis: Income
Sale Price OVERRIDE
Cumulative Cash Flow
Ending Reserve Account
Overall Analysis: Expenses
1st Loan Amount
2nd Loan Amount
Option Fee Credit
Return on Investment (ROI) w/out Cash Flow from Depreciation)
Return on Investment (ROI)
Annualized Return on Investment (ROI)
Compound Return on Investment (ROI)
Internal Rate of Return (IRR)
Return on Investment (ROI) for Return on Investment Quadrant™
Cash Flow from Depreciation
Total Return on Investment (ROI)
Return on Equity (ROE) for Return on Equity Quadrant™
Cash Flow from Depreciation
Total Return on Equity (ROE)
Return on Investment Quadrant™
Return on Equity Quadrant™
Partnerships: Credit Partner (Fee Based)
Partnerships: Credit Partner (Deal Based)
Cash Flow %
Partnerships: Deal Syndicator
Cash Flow %
Partnerships: Money Partner 1 and 2
Cash Flow %
Partnerships: Money Partner (Loan Based – Interest Only)
Partnerships: Totals (Math Check)
Cash Flow %
Capital Expenditures (Cap Ex) Calculation Table
Adding Your Own
Option Fee Sensitivity
Estimated Tenant-Buyer Monthly Payment
Purchase Price and Purchase Price Appreciation Rate
Tenant-Buyer Monthly Payment
Year 1 Down Payment
Private Mortgage Insurance (PMI) Rate
Return on Investment Chart
Return on Equity Chart
Purchase Price with Rent Sensitivity Table for Cash on Cash Return on Investment (ROI)
Purchase Price Variance
Auto Formatting High Target
Auto Formatting Low Target
Your Custom Calculations
Notes, Copyright and Legal Notices
Copyright and Trademarks
Video Tutorial of How to Analyze a Real Estate Deal
In mid-September, 2019 I gave a presentation to a standing-room only crowd of real estate investors on how to analyze a lease-option deal using The World’s Greatest Real Estate Deal Analysis Spreadsheet™. For just over 2 hours, I walked them through much of what I presented above but in video form.
There was an interesting discussion of whether or not to include pre-paids like taxes and insurance in your Closing Costs or not. I made the argument that since you’re including them elsewhere in the spreadsheet, I personally leave them out. Others were quick to point out that while they are paid elesewhere, you do have that money “out” and it should be accounted for as part of your initial investment. The challenge with this, of course, is that when you sell, you do get those refunded.
Watch the class and hear this discussion and many others as I teach you how to analyze a real estate deal using the spreadsheet.