Rental Real Estate Software Directions

Go to the main Real Estate Investing Software page

Glossary of real estate investing terms: http://www.realestateabc.com/glossary/

Site Information

Read About the Current Sale!

Confused? It Makes Sense If You Start at the Home Page

Generic directions for all of the financial software on this site

Feel free to use Excel's freeze panes function to make it easier to look at.

Real Estate Investing Software Directions

Open the file (Real estate Calculator.xls or Multiple Real Estate Calculator.xls) with MS Excel.

Now go to File, Save As... to save the file you'll be working with using a different file name. This will preserve the original file so that you'll have a fresh unaltered copy in the future. If you're a financial planner working with clients, there are tips on this downloadable Word doc on how to organize and save these types of files on your computer.

On Real Estate Calculator.xls there are six sheets in the spreadsheet. On 5RE.xls (the multiple rental calculator), there's a couple dozen.

For Real Estate Calculator.xls, start on the Input sheet. On 5RE.xls, start input on the Rental #1 Input sheet.

To avoid confusion, the rest of the directions will refer to cells and sheets using the Real Estate Calculator.xls. If you're working with 5RE.xls, just repeat the process using the Rental #2-5 sheets after you've input data into Rental #1.

Step 1: Input basic property and people parameters into cells A5 - 10.

If you're not a financial planner working with clients, then you can delete the contents of A7 & 8, and there will just be some extra white space on the IRR sheet.

Cell A10 is the only cell needed to calculate the property's NPV (net present value). NPV is the financial time value of money term used to describe how much a series of cash flows are worth today.

If you're up on time value of money concepts, then the best way to explain it is by saying that it's just the reverse of IRR (internal rate of return). In English, once you input data, the current market value of the rental property will be displayed in cells C21 and F21 of the IRR sheet. This value will dramatically change depending on the NPV discount rate input into this cell. The lower the discount rate, the higher the net present value.

The generic definition of NPV is: The present value of an investment's future net cash flows, minus the initial investment. If it's positive, then the investment should be made. If it's negative, then it should not be made, because you can do better elsewhere assuming you can get an average rate of return equal to the discount rate you used to calculate NPV. This is why the discount rate input into cell A10 should be a rate of return that you can get with confidence by investing in something else.

This is confusing, so an example will help. In the single rental demo, the before-tax IRR is 11.6%. If you were to input 11.6% into the NPV input cell, you'll get a tiny number as a result. It should be zero, and would be if one were to input the rate using a few more decimal places. This means that if you did the math using all of the inputted cash flows of the property (which was done and is what the program is all about), the overall rate of return of the property since you bought it, until the end of the year in which it was sold, was 11.6%. That also means if you bought mutual funds that returns 11.6% all of the monies invested at the same time, you would have made just as much money in the end as you did with the rental, after the tax benefits (and would not have had to spend all of time, work, and take all of the leveraged risk, by the way).

Back to the example: If you input 12%, then you'll get -$4,826. This means that if you would have had the exact same cash flows at the exact same time, and got 12% instead of 11.6% in mutual funds, you would have made $4,826 MORE money in mutual funds than the rental. So if you are being required to get a 12% rate of return on your money, you would have been disappointed by $4,826 by investing in this rental property. The bottom line in this example, is that if your goal is to get 12% on you money with this property, then you should not have paid $250,000 for it (as inputted into cell A14 of the Input sheet). You should have only paid $245,174 for it.

Experienced real estate investors can use NPV to value properties outright like this, and they can also use it make apples-to-apples comparisons on like properties. This is extremely valuable and can end up either making lots of money, and/or can be used to avoid losing lots of money.

On the other hand, if you would have had only a 10% rate of return requirement on your money, you would have made your 10%, and then, $24,687 on top of that (the difference between 10% and 11.6% over the investment horizon).

In this case the fair market value of the property (assuming all of your future input data happens, which we can guarantee that it definitely will not), would be around $274,687. In this case, the property is a great deal if you only expected to get 10% on it in the first place, or it only cost $250,000.

The reason NPV is one of the first input cells is to surprise you. Input the rate of return you think the property will achieve. The difference between that and the true rate will be the surprise.

Whole books are written on the subject, so this is all that's going to be discussed here. You can find tons of information on IRR and NPV by searching the web.

Step 2: Input the year you first acquired the property into cell A13.

Input the amount of the mortgage loan(s), BEFORE any points or other financed add-ons are applied, into cell A14. These add-ons are input later.

As it says on the product page, when you borrow money in the Real World, the payments/interest/principle/and end of year liability will not be the same as the program calculates. No program does this correctly, so the best thing to do is to create a Real World amortization schedule yourself, and then input this into the manual overrides, as described later. However, if you're just doing a hypothetical "what-if" this is as close as a computer program can get using a fixed-rate mortgage.

If there are no loans at all, input zero into both cells A14 and A17 of the Input sheet. Then input the total amount of cash paid to buy the property into the down payment cell, A16.

Input your estimate of the property's current fair market value into cell A15. It's probably going to be more accurate if you input the purchase price, but it doesn't have to be.

Input the amount of the cash down payment into cell A16. This is how much cash you initially spent to acquire the property. This will show up as a large negative cash flow in the first year's numbers.

Input the number of years financed into cell A17. You can use decimals to account for an odd number of months.

Input the number of points paid in cash into cell A18 and the number of points financed into cell A19. You can fiddle with the exact number you input here so it's the same as the actual amount spent. 1 point at $200,000 is $2,000, so just play with it until it's close enough.

Input the amount paid in cash for closing costs, escrow, loan origination fees, and all that into cell A20. These would only be relevant expenses that are expensed and deductible.

Input the amount financed into cell A21. These are added to the loan value input into cell A14, and are amortized over the life of the loan.

Input the fixed interest rate into cell A22. The program does not do variable rate loans, because it's too complex, and in the end won't be right anyway. Also, in the Multiple Rental Calculator, it makes the spreadsheet too big, and will cause out of memory problems. To input variable rate loans, see how to use the manual overrides below.

Input any cash paid to fix up the property initially into cell A23. After the first year, use the expense input fields to account for money spent on fix up.

Input fix up costs financed into cell A24. This amount is added to the mortgage amount in cell A14. The reason you want to keep all of the cash flows in the correct year, is because it's the most accurate way to calculate the correct IRR. To see what difference it makes, just play with the input, as it won't hurt anything.

Input the three initial deprecation basis amounts into cells A25 - 28. If it's a residential property, use cell A25 for the building. If it's a commercial building, use cell A26. Do not input numbers into both cells!

Now how to use the mortgage manual overrides to accurately account for the loan parameters:

This is a good time to point out that whenever you use a manual override, you can makes notes to explain why you used them in the column next to it. This is to help you remember why did you did something. It doesn't affect any of the calculations, so feel free to use these areas to make all of the notes you want to.

With this feature, you can accurately simulate ANY type of financing.

First create an amortization schedule, or use the one given to you by the lender. This is a table showing the amount of interest and principle that are in every monthly payment for every year in the loan. It will also have the remaining amount of the liability remaining at the end of every year.

Switch to the Calculations sheet.

Go to cell X11 and input the amount of every year's annual principle paid. Repeat the process for the end of year liability into column AC. Then do the same thing with the interest into column AK. That's all you need to do.

You can also account for making additional principle payments by using column T. These amounts input will be in addition to any principle payments already calculated if you are using the automatically generated fixed-rate mortgage numbers. But they will not be if you used the manual overrides in column X in that year. Anything you input into column X will override anything to the left of it.

Now you have accounted for the mortgage parameters and depreciation.

Step 3: Input the (estimated) amount of annual rental income into cell A31. Add any amounts of additional/miscellaneous incomes (laundry room, parking, etc.) you expect to get here too by just adding to it.

Input the default amount of rental income inflation into cell A32. This is how much the gross rental income will increase every year. Again, in the Real World, this will not be correct. So to be exact, use the manual overrides in column K of the Calculations sheet.

If you want to change a certain year's rental income, and then have this amount be the new base amount, and then have it inflate at a set amount in the future, do this:

Say in year #5 you increased rent to $50,000 and you want to inflate that at 5% in every subsequent year. The amount went from $40,000 in year #4 to $50,000 in year #5. Input $50,000 into cell K15.

The issue is that in all of the years in the future, the base amount of rent will revert back to year's #4, and then will be inflated at the rate input into cell A32 of the Input sheet. To make subsequent years rent inflate using the new $50,000 amount, do this in cell K16:  =K15*1.05   Then drag this formula down into all of the later year's cells by making cell K16 the active cell, then clicking on the drag handle (the little black square box in the bottom right corner), and then drag it down to populate the remaining cells in column K.

The vacancy rate input into cell A33 will just be a guess. If you're inputting data from a property that has been sold, you can ignore this. But in the Real World, hardly anyone has 100% occupancy at all times. Also, rents are sometimes discounted to entice tenants to move in. So this is where you account for this estimated loss future income. Anything you input here will be a guess, but you can ask the previous owner about their past experience.

Amounts input into the Rental Income Manual Override column K are also decreased by this vacancy percentage amount. So in order to input exact values here, you'll need to input a larger number with this percentage taking into account. For example, to input an exact amount of $22,000, and the vacancy rate is 5%, then input $23,158 (22,000 / 0.95).

Step 4: Accounting for expenses. There are 26 expense items listed starting cell A65 on the Input sheet. Cells A80 and 81 are Miscellaneous expenses. You can type over the word Miscellaneous to name these two items anything you want to. This gives a total of 28 expense items.

Input the (estimated) amount of each expense into the appropriate cells in column A. Then in column D, input the annual inflation estimates for each one. The program is set to detect zeros, so if you want to input a 0% annual inflation rate in this are, input a tiny number, like 0.001.

You can also ignore this column of individual inflation input altogether, and all 28 expense items will default to the global rate input into cell A34 of the Input sheet. Column C shows the percentage of each expense item in relation to total expenses for the first year.

You can also manually set each expense item to be whatever you want it to be, in any year, by using the manual override columns next to each expense on the Calculations sheet. You can even input negative numbers.

Step 5: Accounting for taxes.

Another unique feature is the ability to change the income tax rate to whatever you want it to be in any year. Just input the rate into the appropriate year in column CG of the Calculations sheet, and it will manually override the global default rate input into cell A35 of the Input sheet. Inputting a negative tax rate may cause errors.

Step 6: Accounting for annual changes in the property's market value.

Again, you can set a global default growth rate into cell A36 of the Input sheet, and then you can manually override this by inputting year-by-year growth rates into the appropriate cells in column F of the Calculations sheet. Rates here can be negative.

You can also manually override all of this, and set the property's end of year market value to be whatever you want it to be, by using column G of the Calculations sheet.

If you make improvements in any of the three deprecation categories (building, appliances, or land), you then want to go to column F or G and increase the value of the property by the amount you think the value increased. For example, if you spend $10,000 on a pool, and think that increased the value of the property by $15,000, account for that in either column F or G.

Step 7: Accounting for the sale of the property.

Input the year of the property's sale into cell A42 of the Input sheet. The property is assumed to be sold on the last day of that year.

The amount of the gross sale proceeds is the last year's end of year market value. So if you want to show selling it in year 20, then the number displayed in cell I29 of the Calculations sheet will be that amount used in the calculations. So if you want to set this to be a certain value to force the sale amount to be a set value, input that amount in the manual override in cell G29.

Cell A39 is where you input the real estate broker's commission percentage. If there is no commission, input 0. All it does it multiply this percentage by the previous year's end of year market value, and then subtracts this amount to the last year's cash flow in columns BZ and CA of the Calculations sheet.

You can set this to be whatever you want it to be by fiddling with the input. For example, if it's agreed that the commission will be a set amount, and not a percentage, then just divide this amount by the selling price, and input that percentage amount. If the sale price is $255,555 and the commission is $5,555, then input 2.174% into cell 39 of the Input sheet (5555 / 255555 = 0.02174).

The capital gains tax rate in cell A40 is the rate that will be subtracted from the sale proceeds in column CA of the Calculations sheet. The amount of taxes is the amount of the sale proceeds (explained above) minus the basis in column BT for the sale year, times the tax rate.

Then if there is a profit on the sale, the depreciation recapture tax rate input into cell A41 of the Input sheet will be used to calculate the amount of depreciation recapture taxes that is also subtracted from the sales proceeds.

The amount of depreciation recapture is displayed in column BR of the Calculations sheet, and the amount of taxes is shown in column BS. If you know the amount of taxes to be paid here, you can fiddle with the tax rate input into cell A41 of the Input sheet until the amount of taxes is equal to that.

Input cell A43 is the amount of pre-sale fix-up costs spent on the property. It is subtracted from the sales proceeds in the year of sale.

Step 8: Accounting for refinancing.

The program will accommodate one refinancing in any year except year #1. Again, this should only be used when you want to compute an IRR or NPV for a hypothetical property. When you do it in reality, you should use a Real World amortization schedule, and input the numbers into the manual overrides as explain above.

The steps are the same as explained above in the original mortgage steps. The only things that happen is that the all of the data in the original loan columns of the Calculations sheet will be deleted in the year of the refinancing and all future years. Also, the amount of the loan will be displayed in cell A57 of the Input sheet.

The amounts input into cell A56 - Cash withdrawn and not spent on the property. These amounts are added to the rental income in that year.

Step 9: Inputting data into cells A60-62 of the Input sheet will create data for several charts on the Graphs sheet.

About the Graphs sheet: Some of the charts get their ratio data from the results of formulas in cells AR2 to BL52 (of the Graphs sheet). It doesn't look like there is anything there because the font color was changed to be white.

The point to remember is to not delete any of this data or the charts won't work right. This sheet is not protected, so you can alter any of this chart data, any of the charts, and you can also make any new chart, graph, or table you want to on this sheet (or the IRR or the Presentation sheets).

How to account for 1035 Exchanges

You can simulate 1035 exchanges by changing the capital gains tax rate.

Just set the rental up as being sold in the current year, and then change the capital gains tax rate until the net amount of sale in column CD of the calculation sheet shows the amount of the value of the transfer.

Miscellaneous: The graphs can easily be modified to display pertinent data. When the sale year is included in the chart data, the last cash flow, the sale proceeds, distorts the previous year's numbers.

The Graphs sheet in Rental #1 of the Multiple Rental Real Estate demo shows what this chart looks like when this last cash flow is deleted. The following charts in the middle of the graphs sheets have had the data ranges altered in this manner: Net Realized Cash Flows, NOI per SqFt, Capitalization Rates, and Debt Service Coverage Ratio.

That's about it. Please let us know if any of this doesn't make sense, and not only will it be edited, but you may earn a freebie for pointing out errors and/or doing things to help make things better.

Menu of Retirement Planning Software

Asset Allocation Overview with Our Historical Returns

Asset Allocation Software

Asset Allocation Models with Our Historical Returns

Our Monthly Updated Mutual Fund Picks


Financial Plan Tools

Family Budgeting Calculator

Financial Planning Fact Finders

Investment Policy Statement Software

Bond Calculators

Investment Software for Comparing 22 Methods of Investing

Buy Term Life Insurance and Invest the Difference in Mutual Funds vs. Whole Life Insurance Calculator

Life Insurance Calculator

Contents of a New Fee-Based Prospect Binder


Services

Fee-only Financial Planning and Investment Consulting for Consumers, Investors, and Financial Planners
 

Building Custom Investment Benchmark Portfolios

Financial Planner Directory

 

How to Become Your Own RIA

 

Coaching for Financial Planners

 

Asset Allocation for 401(k) / 403(b) and Similar Retirement Plans