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 **Rental Property Investment Analysis Calculator** (using Microsoft Excel) may be just the investment analysis tool you’re looking for!

## How the Program Works

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:

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:

## 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:

* 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.

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

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.

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

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

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

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!

Looks good, I want to know more

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

I enter a 15 year mortgage period. Why doesn’t your model reflect the pay down on the mortgage going to “zero” at 180 months?

Hello Brent,

Are you referring to the “raw data” at the bottom of the sheet? For programming purposes, I run all future values out to 360 months (the maximum loan term allowed in my program). For instance, even though you have a 15 year mortgage, we can project that in 30 years you’ll be able to rent the property for X amount of money per month. In the case of your 15 year mortgage, the program will only used those values up to 180 months when calculating your results. I know this is probably confusing. In the end, I could have left the “raw data” file hidden, but I wanted people to see where the numbers were coming from…hope this helps!

Love the tool, do you have an updated version with the suggested add in’s? I downloaded this 1.5 years ago, donated to you. Please let me know. Thanks

perpetuity is 2 types-level and growing perpetuity

1.in perpetuity you have divided 825/4%=825/0.04=20625,but you have given it as 247500

if i take 2 int rates a) inflation and b) growth rate and then use the perpetuity ex:3% growth and inflation 1.5% still i didnt get it

correct me

surendra

the analysis section is the key!

can you write down a seperate excel sheet for each value

it would be helpul please!!

surendra

Hi Therese! Thank you very much for your donation for my rental property investment calculator! I’ll be working on a revision over the next month or so and will email you when it is complete.

Is there a place to enter expenses such as HOA dues?

I want to understand how did you calculate the rent in the raw table because in the initial

Months the rent looks reduced instead of fixed. If the rental is fixed for the first 12 months at least shouldn’t it be shown as fixed in the raw table rent column as well? I will appreciate if you can explain your calculations here. Thanks

Hello,

Has anyone else had trouble editing the cells with white background?

I cannot seem to dive into the cells in order to analyze the formula used. Any insight would be appreciated.

Thank you,

Erik

Hello Tania,

The raw rent table is a function of the initial rent, the appreciation of the property’s value and the occupancy factor.

You’re right that the rent will be fixed the first 12 months. However, for modeling purposes, the occupancy rate (default value of 93%) is applied for all months because it would be impossible to predict exactly which months the property would sit idle or unoccupied.

Hope this was helpful!

Mike, sorry not in the current version. You could figure out what percentage of the home’s value the HOA dues are and add that percentage to your tax column or annual maint expense column.

My brother recommended I might like this website. He was entirely right. This post actually made my day. You can not imagine simply how much time I had spent for this information! Thanks!

Ben, does the spreadsheet now include:

-the tax benefit of depreciation? As you may know the depreciation rate for a residential prop is 27.5 years.

-the tax deductibility of mortgage interest?

Essentially what i am looking for is a rental property investment spreadsheet that includes after tax return. And if the return can be given in a DCF and IRR and ROI that would be great!

Please advise.

Did you add the ability to take into account tax benefits of owning the property?

Depreciation, and the ability to deduct the losses from earned income?

Hi Jeff! I have not added the ability to take into account tax benefits of owning the property as each individual’s tax situation is different. Further complicating the issue is the various of ownership options (LLC, S-Corp, etc.). Thanks for following up and good luck with your rental property investing!