≡ Menu

Free Rental Property Investment Analysis Calculator (Excel Spreadsheet)

If you’re considering the purchase of a residential or commercial investment property but need help crunching the numbers to determine if the investment makes sense, my free Rental Property Investment Analysis Calculator (using Microsoft Excel) may be just the investment analysis tool you’re looking for!

As you may or may not know, rental properties generate value for their owners in three distinct ways: Cash Flow, Repaid Mortgage Principle, and Property Value Appreciation.  Here’s a closer look at each of these profit sources:

Cash Flow:

Cash flow is the rent money left over at the end of each month after the property’s mortgage, taxes, maintenance, insurance and property management costs are paid. Obviously, your goal is to have more rent money coming in each month than you’re paying out in expenses; this is known as having positive cash flow. If the rent collected each month does not fully cover the monthly expenses of owning the rental property, you have a negative cash flow. While positive cash flow is best, there are some instances when it may make sense to have a negative cash flow (especially for the first few years) on an investment property, but we’ll get to that a little later.

Mortgage Principle Repayment:

If you’ve mortgaged a rental property and you’re using the rent money to pay the monthly mortgage payments, you are also reaping the benefits of having someone else pay down your mortgage.

Property Value Appreciation:

The third way investors make money with rental properties is through the property’s appreciation in value. In “real terms” the increase in a rental property’s value can be affected by inflation. If inflation is 3% per year and your rental property appreciates in value at only 2% per year, you’re rental property is actually decreasing in value in terms of “today’s dollars”.

Understanding these values and how to calculate them can be very difficult for beginner or aspiring real estate investors. Making this process even more difficult are the many variables that come into play that can affect some if not all of the ways you can make money with rental properties.

Variables that Affect the Profit of Rental Properties:

Purchase Price:

The biggest factor affecting the potential profit of a rental property is the purchase price of the property. All other things being equal, the more you pay for a rental property, the less your profits will be.

Down Payment:

Believe it or not, the less money you put down on a rental property, the higher your return on investment will be. This is because your return on investment is measured by how much money you actually personally invest into the property. If you put $25,000 down on a rental property and pay the mortgage off with rent money paid by the property’s tenant(s), the $25,000 is considered your investment. The total profit earned on the property from cash flow, mortgage principle repayment and the property value appreciation all result from your initial $25,000 investment that helped you secure a mortgage on the property.

Mortgage Interest Rates:

Mortgage interest rates will affect the profitability of rental properties. The higher the interest rate, the higher your monthly mortgage payments will be. Higher interest rates will also affect how much of the mortgage’s principle is repaid each month.

Mortgage Length:

Deciding how long to mortgage rental property can be a very hard decision; however, it is possible to determine the optimum number of years to finance a rental property to maximize the return on your investment (your down payment). The longer you mortgage the rental property, the higher your monthly cash flow will be (lower monthly expenses). On the other hand, the principle is paid down more slowly on the property. Using my rental property analysis tool, you’ll be able to find the “sweet spot” between these two variables to determine how long your mortgage should be on your rental property.

Property Value Appreciation:

Most properties will appreciate in value over the long term. Historically, homes in the United States have appreciated in value by about 4% per year (staying slightly ahead of inflation). The higher the annual appreciate rate (which you can only estimate), the more profit you will make on the rental property since you’ll be able to sell it (or charge more rent) than you could when you first bought the property.

Property Tax Rate:

The higher your annual property tax rate, the lower your monthly cash flow will be.

Maintenance Expense:

Another cash flow killer is maintenance expense. Many real estate investors count on spending 1% of the property’s market value each year in maintenance costs. For a $120,000 rental property, they’ll budgeting $1200 per year ($100/month) on maintenance costs.

Monthly Rent:

The more rent you can earn off a property, the higher your monthly cash flow will be.

Occupancy Rate:

You won’t be able to rent the property out 100% of the time. Once a tenant moves out, it takes time to ready the property and find a new tenant. There are also instances when a tenant is unable (or unwilling) to pay rent. Some real estate investors I’ve talked with say their occupancy rate is around 90-93% on average.

Property Management:

If you’re relatively new to rental properties or you live far away from the rental property that you own, you may considering using a property manager to deal with the tenants and handle any issues that may arise. If this is the case, you can expect to pay a fee of 5% to 10% of the monthly rent for this service. This fee will hurt your monthly cash flow.

Annual Insurance Premiums:

Depending on which part of the country you live in, you may pay anywhere between .3% and 3% of the rental property’s market value in rental insurance each year. Dividing this out over a 12 month period will allow you to determine the affect property insurance has on your monthly cash flow.

Inflation Rate:

Inflation can take a bite out of your cash flow as well as the rate at which your property appreciates in value when looked at in “real terms” or “today’s dollars”. As we discussed above, a home can appreciate by 4% each year, but if inflation is 2% the real value of the home’s appreciation is much less.

As you can see, any one of the above variables could adversely affect the profitability of a rental property. To help me analyze a particular rental property I’ve put together an easy to use Excel Spreadsheet that takes each of these variables into consideration and tracks their effect.

This rental property investment calculator is an experiment and I make no guarantee of its accuracy, but for me it is a valuable investment analysis tool. When I see a prospective rental property for sale, I can just plug the numbers into my investment property program and I will get a pretty good idea if the property is a good investment or not.

Entering the Rental Property Variables into the Program:

click image to enlarge

The first section of the rental property investment program is where you enter in all the variables that affect the profitability of the rental property. Note: The “Amount Financed” value is calculated automatically for you:

Analysis Results Section:

Once you’ve entered and reviewed all the relevant data into the spreadsheet, you can look at the “Analysis Results” section to get a sense of the profitability of the investment property. Below is a screen shot of what the results will be based on the sample data entered in the section above:

click image to enlarge

Let’s take a look at each of these values as I try to explain my methodology on how to calculate each one.

Total cash flow during the full term of mortgage’s repayment (or the length of time you plan to own the property): This value, $20,867.55, is the estimated total of all the monthly cash flow you will have over the term of the mortgage (or time you expect to own the home). It is calculated by adding the balances of each month’s rent-expenses. Even if the first few years are negative, as they are in this example, you still may end up with a positive overall cash flow because you’ll be able to charge more rent (theoretically) as the property increases in value.

Total cash flow during the full term of mortgage’s repayment (or the length of time you plan to own the property) in “Today’s Dollars”: This value, $17,488.58, is the estimated total of all the monthly cash flow you will have over the term of the mortgage (or time you expect to own the home). It is calculated by adding the balances of each month’s rent-expenses. This time, however, the monthly cash flows are “discounted” based on the rate of inflation you entered in the fields above. You can also see the comparison between the monthly cash flows and the “adjusted” monthly cash flows in the “Raw Data” section of the spreadsheet.

Monthly rent cash flow in “today’s dollars” after mortgage is paid off: After the mortgage is paid off (or if you pay cash for the property) your monthly cash flow will be much higher. The value of this monthly payment is displayed here and assumes the rent you can charge for the property increases at the same rate the property’s market value does.

Principle repaid during term of mortgage repayment in “Today’s Dollars” (will be zero if you paid cash for the property): This amount is equal to the amount you plan on mortgaging for the property. This is the amount of principle on the property that will be “paid off” by the renters.

Once the mortgage is paid off, this is the value of receiving your monthly rent payments in “Today’s Dollars” (assuming a conservatively invested perpetuity): One of the great things about owning a rental property is that after the mortgage is paid off you still have the monthly rent income coming in forever (providing you keep up on the property’s maintenance). The financial term for this sort of payment is called a “perpetuity”. You can calculate the present or current value of a perpetuity (the $825 monthly cash flow mentioned above), by dividing the value by a “conservative” interest rate. In other words pick an interest rate that you could be almost guaranteed to earn in the open market (I used 4% as a value in this spreadsheet). This value is “how much money” you would need to invest at 4% to earn $825 per month? Answer: $247,500. This value may not matter to some real estate investors, but it is an interesting value to me.

Increase in rental property’s market value in “Today’s Dollars” during the mortgage term (or during the time you expect to own the property): If the investment property appreciates in value at the same rate of inflation, this value will remain “$0” because there will be no change in the “real” value of the property. However, if you enter a different inflation rate and property value appreciation rate the difference will be calculate here.

Compound Annual Growth Rate (Return on Investment) of your initial investment (down payment) during the time you plan to own or mortgage the home using the “geometric average” formula including the effect of inflation: This value (7.84% in our example) is the average return earned on your initial investment (which is considered to be your down payment). In other words, this is the effective rate that your investment would grow annually in order to achieve the “total profit” calculated in the next cell below.

Total profit during the mortgage repayment period (or during the time you expect to own the property) in “Today’s Dollars”: In the values above, the program has calculated the “profits” of the investment over the three main sources of value rental property investor receive (cash flow, principle repayment, and property appreciation). The value in this box is simply the sum of these three individual profit sources of owning a rental property. In our example, the total estimated profit of purchasing the property using a 15 year mortgage is $155,023.39 in today’s dollars.

Raw Data:

The “raw data” calculated by my experimental rental property analysis program also reveals some interesting data that is of interest to the beginning or aspiring investor. Here is a screen shot of the raw data section of the Excel Sheet:

click image to enlarge

Month by Month Cash Flow Analysis: Perhaps the most useful information found in the “raw data” files is a breakdown of the rental property’s monthly cash flows. As you can see in our example, the first few months start off cash flow negative (the property owner has to pay extra out-of-pocket cash to cover expenses). However, because of the appreciation in property value and rent, the cash flow for our example rental property turns positive by the 38th month (not shown in this screen shot).

Property Value Appreciation: You can also see the estimated monthly increase in the property’s value.

Increase in Rent: Because we’ve fixed the estimated monthly rent you can charge for the property, you can also the average monthly increases you’ll be able to charge for rent. The rent won’t actually increase each month as shown in our example because you’ll probably fix the rent in 12 month lease increments. For the purposes of our calculations, the effect of the increasing the rent monthly does not affect our overall calculations.

Let me know what you think!

This rental program investment program is far from perfect, but it is a good starting point for me as I consider investing in rental properties. As always, please be sure to consult a qualified investment professional before acting on any of the results calculated in this spreadsheet since I cannot guarantee their accuracy or validity.

If you have any ideas for improvements or see any mistakes that need to be corrected please email me or leave a message in the comment section below!

Good Luck!

P.S. I was inspired to write this article and create the Excel spreadsheet after reading posts on real estate investing by J Money, Lazy Man, 2Million, FMF and Flexo.  They really got me thinking about the benefits of investment properties and hopefully this rental property investment program will help them too!

facebooktwitterpinterest

THANKS for reading my personal finance blog! You can learn more about my site here or you can jump right into the juicy stuff by reading my family's own detailed debt free success story (which has formed the foundation of this site). Please consider signing up for my free email updates!

{ 8 comments… add one }

  • Levi January 14, 2013, 10:52 pm

    This is an awesome tool. I was just working on this with pen and paper to explain it to my dad when I found this

  • ming July 9, 2014, 12:36 pm

    Great tool! Thanks!
    But shouldn’t you have included an entry for utilities? Most apartments pay for utilities (water, waste for all tenants, and electricity for common/laundry area). It’s close to 10% of rent income. Also. another entry for additional income, such as laundry facilities.

  • Hector July 20, 2014, 7:49 am

    Do you take into account tax benefits of owning the property? For example: Depreciation, and being able to deduct the losses from earned income?

  • KN August 9, 2014, 5:45 pm

    Very nice tool, but it does not include the tax benefit of depreciating the rental property?

  • Ben August 14, 2014, 1:31 pm

    That is a very good point! I will definitely look into that for the next version!

  • Ben August 14, 2014, 1:34 pm

    Hello Hector, You bring up a very good point and I will consider this recommendation in a new release due out later this year! Thanks for visiting my site!

  • Thomas September 5, 2014, 6:25 pm

    Looks good, I want to know more

  • Ben September 6, 2014, 4:24 pm

    Do you have a particular questions about our rental property investment calculator I can help you with? Thanks for reading!

Leave a Comment