If you would prefer to skip directly to download the investment property calculator excel spreadsheet, you can by following the above link where you can download the Excel sheet for free, or, you can click the red toolbar below.
I’ve written extensively on not only why you should invest in commercial and multi-family real estate, but, how to create wealth by investing in real estate.
In the article: Here’s How To Buy An Apartment Building And Make A Whopping 110% In Three Years, I detailed my purchasing analysis in regard to investing in an investment real estate property.
The Real Estate Investment Spreadsheet – An Explanation
One of the main criteria, when you invest in real estate, is to analyze the building’s CAP rate, and in addition, to consider how profitable the building is (NOI), and whether it makes for a suitable investment.
There are many factors that go into determining the profitability of investment property such as location, condition, investment purpose, projected cash flows, profit opportunities, etc.
“When estimating how profitable the building Net Operating Income is, there are two methods of calculation which are commonly used: Discounted Cash Flow Analysis (DCF) and Internal Rate of Return (IRR)”, explains Jasper Juhl, a real estate expert and CEO of Better Estimate.
To find out how profitable the building NOI will be with each method, we’ll need to have an understanding of each method and its calculations.
In the article I referenced above, the building I was considering buying had a fairly low CAP rate. Under most circumstances, an investor might find it difficult to invest in a building with a low CAP rate, however, in this circumstance, I demonstrated why the lower the CAP rate, the better the short-term building increase would be. In this particular case, my return was going to be 110% inside 3 years.
Before moving into the free real estate investment spreadsheet explanation, here’s a quick recap
** NOTE, if you want a more detailed explanation of CAP Rate and Pro Forma cap rate, you can get that from this article: What is CAP Rate and Pro Forma CAP Rate in Commercial Real Estate and How Can You Create Wealth With These Numbers?
CAP Rate Formula
CAP rate calculation = Building’s Profit (BI) / Building’s Purchase Price
For example, let’s say the building has a sale price of $100,000, and after all expenses including insurance, hydro, and utilities, the profit is $100,00. The CAP rate will be calculated as:
CAP rate = $10,000 / $100,000 = 10%
Now, there’s one more important real estate formula you need to understand, pro forma CAP rate.
If you’re going to buy that $100,000 building, and you know the building needs $20,000 in repair costs immediately after taking possession, then technically you should add that number into your calculations.
The pro forma CAP rate will consider this $20,000 in additional building repair costs as part of the CAP rate.
The pro forma CAP rate formula is similar to the CAP rate formula:
Pro Forma CAP Rate Formula:
Pro forma CAP rate formula: Building’s Profit after repairs (BI) / Building’s Purchase Price
In this case, the numbers will look as follows: $10,000 / $120,000 = 8.33%
A large component of the real estate investment spreadsheet is in helping you calculate the building’s CAP rate. There’s also a multi-family mortgage calculator in the spreadsheet.
Real Estate Investment Spreadsheet – The Details
The fields in the free real estate investment spreadsheet are as follows
** There’s a GREEN Tab called “Start Here”
** Only enter values into the green fields. Do not touch the yellow fields
- In cell B3 enter the purchase price of the property
- In cell B4 enter the building’s downpayment
- In cell B7 enter the amortization period in years
- In cell B8 enter the interest rate
- In cell B9 enter the mortgage’s first payment date
Now we move into the monthly rents for the multi family units, so, enter those values into the real estate investment spreadsheet:
- In cell B12 to B33, you can enter the monthly rents for each unit
- In cell B37 and B38, enter the parking and laundry revenue
The free Excel sheet will automatically calculate the gross monthly income
In the Excel sheet, you will notice cells C42 to C50 lists many of a building’s typical expenses, and that includes: taxes, insurance, electricity, hydro, gardener, water, management, maintenance, heat, and snow clearing.
Once done, the system will calculate the building’s profit, and, then automatically calculate the building’s CAP rate.
In the sheet that you download, you will notice in cell B56 the CAP rate is 6%.
The system will calculate the yearly profit, and in this case, it is $72,000, the monthly profit is $6,000, and the dollars in your pocket after paying the mortgage is $1,777, which you will see in cell C62. The yearly profit is $21,327 as per the example in the Excel sheet.
Real Estate Risk Arbitrage
In the example I provided above regarding the property where I proposed to make 110% inside 3-years, the property had an approximate 25% IRR (internal rate of return), was an example of something called real estate risk arbitrage.
If you know that you will be able to quickly upgrade and flip a property, and you have calculated the proposed imputed value to be significantly higher, and you can buy something for $X, and sell for $X plus $Y, then you have real estate risk arbitrage.
What is Real Estate Recapture
Real estate recapture is the building value that is recovered with every mortgage payment. Each mortgage payment attributes a certain amount toward the principal and the balance toward the interest.
Let’s say, for example, that you have a $100,000 mortgage, and your next mortgage payment is $700. Of that $700, $200 goes toward interest and $500 toward the principal repayment. Your recapture, in this case, is $500, and you now have a mortgage of $99,500.
You will notice that in cell C62 I called the field “total profit per month”. In actual fact, this isn’t really profit, because some of what the Excel sheet considers “profit” is really a contribution toward the building’s principal repayment, and in turn, is attributed back as equity.
In the real estate investment spreadsheet, you will notice another tab called “monthly amortization”. This is a mortgage table.
Click here to download the free rental property investment analysis calculator excel spreadsheet.
Other Real Estate Articles I’ve written: