How to Create Investment Property Cash Flow Calculator in Excel

This article will discuss Investment Property Cash Flow, and components of cash flow. It will also give you simple and easy guidelines on how to make investment decisions using an Investment Property Cash Flow Calculator in Excel. There is a Cash Flow Calculator in .xlsx format attached here. You can download and use it for your business purposes.

What Is Cash Flow?

Let’s first get a brief idea of Cash Flow. When you run a business, there are two types of transactions cash or cash equivalents. The money you spend is called Cash Outflow, while the money you earn is known as Cash Inflow. These transactions together are known as Cash Flows. Real estate investors also call it Mailbox Money. Cash Flow is the passive income a rental property generates for its buyer. It’s passive because you don’t have to work a 9-5 duty for it or neither have to do a lot of work.

Now, what is cash flow from the perspective of investment or rental property? Let’s assume that you are going to invest your money in an investment property. You must consider two things. How much income you are going to get from the property and how much expense it will cost?

We can say that,

Cash Flow= Total Income – Total Expense

If you see a positive Cash Flow, you are probably going to invest in it. But you must keep away from an investment with negative cash flow.

What Is Investment Property?

Investment Property is real estate purchased by a single investor or a pair or group of investors to generate revenue through rental income or appreciation. You can invest in such property for the long term (or short if you want to resale the property shortly to make a quick profit.

Investment property has the following traits:

They are purchased for rental income, capital appreciation, or both. Also for resale for a quick profit.
Investment property is not occupied by the owner, and not used in production, supply, or administration.

Create an Investment Property Cash Flow Calculator in Excel: 5 Steps

Before making a forecast on the cash flow of your future investment, you must collect some data, and make some educated guesses. We have already discussed that cash flow calculation has two basic components. Income and Expenses. What you need to do is now analyze your income sources (from the property you are going to buy) and expenditure (self-cost for investment, bank loan, maintenance, etc.) in detail. Don’t miss any major expenditure component to make the cash flow forecast as accurate as possible.

Now, in the following sections, we will see how we can create a calculator for investment property cash flow step-by-step. Then we will see how to use it, and later we will try to decide whether our investment would be going to make a profit or not.

Step 1: Set Data to Calculate Financial Requirements from Bank

In the first step of the calculation, you have to make a calculation set-up to estimate how much money you will need to purchase the property, and how much it would cost overall incl. taxes and fees, real estate commission, etc.
You may need to add a cost for rehabbing the property after purchase. So consider all the possible cost items for investment at the planning stage.
Now, set the names in cells B7:B11.
Insert the following formula in cell C8 and drag the formula into cells C9:C10.

=C8*$D$7

Sum them up with the following formula in cell D12.

=SUM(D7:D11)

Then think of a figure for how much money you are going to invest yourself, and how much to take as a loan from a bank. Add the item to the Excel sheet in cell D13.
Then apply the following formula to get the needed amount of bank loan in cell D14.

=D12-D13

Set the amount column (column D, here) format to accounting. As a result, all the amounts will be shown in USD, and the negative amounts will be shown in brackets.

Step 2: Insert PMT Function for Monthly Debt Service Calculation

Now, you have to make another section in the calculator for monthly debt payment calculation. Set two rows for the Yearly Interest Rate and Terms of Loan (you will fix these by negotiation with the respective bank).
To calculate monthly loan payments, there is PMT function in Excel. Insert the function in the next row with proper arguments.
Now, insert the following formula in cell D21.

=PMT(C19/12,C20*12,D14,,1)

Note:

Divide the interest rate by 12, since your calculator is going to calculate monthly loan repayment, while the interest rate is set for a year.
In the same way, the nper will be 12 times the terms of the loan in years.
PV (Present Value) will be the same as the total loan taken).
Consider FV (Future Value) as 0. After the terms of the year’s end, you will no longer repay anything to the bank, so FV is zero from the bank’s perspective.
Since you are going to make a loan payment at the beginning of a month, set the [type] argument as 1. If you plan to make a payment at the end of a month, set it to 0.

Step 3: Add a Section to Calculate Monthly Income

Now create a section for Monthly Income. Consider two rows for Rental Income and Other Income (like application, laundry, etc.).
Apply the following formula in cell D28.

=SUM(D26:D27)

Step 4: Add Monthly Expenditure

In step 2, we have calculated Monthly Debt Service or mortgage separately, so we will include the other expenditure in this step.

We have considered the following here.

Property Management
Repair and Maintenance
Property Taxes
Insurance
Vacancy (will explain next main section of this article)
Utilities
Others

Analyze your case well and do calculations according to that.
Put the names in cells B33:B39.
Insert the following formula in cell D33.

=$D$26*-C33

Drag the fill handle to copy the formula to cell D35.
The minus sign here is to indicate that these are expenditures.
Insert the following formula in cell D36 to calculate the property taxes.

=-D7/12*C36

Insert the following formula in cell D37 to calculate cost considering the property has not been rented for a certain period of the year.

=-D28*C37

To calculate the total expense, apply the following formula in cell D40.

=SUM(D33:D39)

Step 5: Accumulate Income and Expense Results and Insert Formula for Monthly Cash Flow

Now, create a section for the final Monthly Cash Flow. Accumulate the results of Monthly Debt Service, Monthly Expenses, and Monthly Revenue. Add all of them. Since the expenses are negatively put, so adding will return the proper result here.

Insert the following accordingly.

In cell D45:

=D21

In cell D46:

=D40

In cell D47:

=D28

Insert the following formula in cell D48 to calculate monthly cash flow:

=SUM(D45:D47)

Here, we are done with creating an Investment Property Cash Flow Calculator in Excel.

How to Use Investment Property Cash Flow Calculator

Now, we will see how to use the calculator. See and apply the following steps for that.

📌 Step 1: Input Data to Calculate Financial Requirements from Bank

From the broker, or using your link-up or self-investigation, estimate the market value of the property. Input the value in cell D7.
Learn about Taxes and other Fees, Lawyer Costs, Appraisal, and Real Estate Commission rates in terms of percentage of the actual purchase price. Input them in cells C8:C10.
Estimate a Rehab Cost after discussing the property rehabilitation with a relevant professional. Insert the value in cell D11.

Now, input the amount you are going to invest from your pocket in cell D13.

📌 Step 2: Insert Necessary Data to Calculate Monthly Debt Service

You have already applied for a loan from a suitable bank. Input the negotiated Rate of Interest per Annum in cell C19, and the time they have agreed to give you for repaying the loan in cell C20.

📌 Step 3: Insert Monthly Income

You will have only one dependable income source from a rental property, which is your rental income. Input that in cell D26.
You may have other income sources on this property, but it is better not to add them for your forecast safety.

📌 Final Step: Input Monthly Expenses Data

Set Property Management, Repair and Maintenance, and Utility costs in cells C33: C35 in terms of percentages of rental income.
Input property tax % in terms of your actual purchase cost in cell D36.
There will be a year when your rental property will remain vacant. So you will not get any income from it. Consider it as an expense and input this as a % of total income in cell D37.
Finally, input the insurance cost and other costs in cells D38:D39.

As you see, the monthly cash flow is automatically generated by the calculator.

Is This a Good Deal for Investment? – Decide from Cash Flow Calculation

Now, we can hold a fair question, is this going to be a good deal? You see the cash flow is positive, so it means that if you advance invest here, you will generate positive revenue. But monthly $15.07 revenue is not much. If we calculate the COC Return of this investment, we see that it’s only 0.52%.

Not a very attractive amount! But if we dig deeper, this investment is a lucrative one. Let’s see below.

Profitability Analysis of Investment:

Let’s calculate the ROI of this model. Now, what is ROI? ROI is the Return on Investment. Let’s be clearer. When we calculated Monthly Cash Flow, we considered Monthly Debt Service as Cash Outflow, which means it decreased our revenue. But think a bit differently. The more you repay the loan to the bank, the more you own the property, which means repaying the loan is increasing your “money”! So, we can no doubt consider it a cash inflow.

The Monthly Payment to the bank has two parts, monthly interest payment, and monthly principal payment. The interest payment is negative in Cash Flow. So, we will first separate the parts from the total Monthly payment using Excel Functions.

📌 Steps:

In cell G15, insert the following formula, and press ENTER.

=CUMIPMT(C19/12,C20*12,D14,1,C20*12,1)/(C20*12)

This will return the Cumulative Interest payment in one month.

In cell G16, enter the following formula,

=CUMPRINC(C19/12,C20*12,D14,1,C20*12,1)/(C20*12)

With this, you will get a Cumulative Principal payment in one month.

Since we are considering the Monthly Principal Payment as Cash Inflow now, put it in cell G18 with a negative sign before it.
Input the Monthly Cash Flow Calculated earlier in cell G17.
So the Monthly Cash Flow without Principal paydown can be calculated by applying the following formula in cell G19.

=SUM(G17:G18)

Multiply the result by 12 to get the Yearly Cash Flow without Principal Paydown in cell G20.

=G19*12

Now, if you calculate the ROI, it will be 18.97%. Which is a good amount of profit.

The only thing is, you are not getting the whole profit as hard cash by now. You have to wait to get the money physically until you pay the whole bank loan. So, maybe it is going to be a good deal! Again, investment decisions are not so simple. There are many factors to consider that we haven’t included in our article because our target is to provide you with a calculator for Investment Property Cash Flow only.

Download Investment Property Cash Flow Calculator

There are two worksheets in this workbook. In one, we have shown a sample calculation, the other worksheet is there as a calculator. Download it from the following button.

Investment Property Cash Flow Calculator.xlsx

So, we have discussed how to make an Investment Property Cash Flow Calculator in Excel and demonstrated how to use it. If you like this article, please let us know in the comment box. Ask us if you face any difficulties in using the calculator.

<< Go Back to Cash Flow Template | Finance Template | Excel Templates

Get FREE Advanced Excel Exercises with Solutions!