When you're using the program, things go better if you first print out these directions. If you're still shopping, the best evaluation results are obtained by looking at the real estate software demo, while following along as you read these directions. The multiple real estate calculator demo shows off the features better with text boxes, and usage of manual overrides.

The "demos" are the actual spreadsheets, without the formulas, so you won't be able to do anything with the demos but look at them. You'll receive a working program after purchase.

If you try to print the demo directly from your browser, or you just click Print in Excel, it probably won't work well. Tips for working with and printing in Excel are here, here, and here. The pages on the free sample comprehensive financial plan print better than the demo.

If you need to print something on a password protected sheet, and you need to tinker with column and row sizes, and you bought support, and it's in Excel 2007 format, then you can get a version sent to you that does that.

You switch between the sheets (pages) that make up the workbook (AKA spreadsheet) by either clicking on the sheet tab names at the bottom left of the Excel window, or by pressing Control Page Up or Down (pressing the Control key and either the Page Up or Page Down key at the same time).

About the colored sheet tabs: Blue is a presentation sheet. Light green is an input sheet. Green is both an input sheet, and a presentation sheet. Orange is a calculation sheet. Purple is something else.

Before getting started, save an unused copy of all of the files in a separate folder, so you'll always have the original unaltered files. Then if you do something like delete a formula, you can easily fix it by copying it back using these original files. Then save files you've worked on using a different file name (and into a different folder).

If you don't like a file being read-only, go to Save As, then go to Tools, General Options, uncheck the Read Only box, then click Save. More Tips for advisers saving and organizing client files are here.

Programs usually come without sample input data, but be sure to delete all of the sample / client input (everything in green-shaded cells) before inputting your data.

You should make CD (or at least USB flash drive) backups of all of your work at least on a monthly basis, and store the CD in a safe location, but not where your computer is. More generic free PC tips are here.

If you're seeing this: ####, then either change your Zoom magnification number (using the slider at the bottom right), or make the column width wider. If you see this: #REF! after completing your input, please respond to get a new program immediately. This usually means data was lost either via e-mail, unzipping, or while making a CD.

If a red Error! dialog box comes up saying your input must be confined to a range of values, and your input was within that range, then the problem is that the input cell doesn't accept pennies. Try again rounding the value to the nearest dollar. If it still happens, then send e-mail with the spreadsheet name, sheet tab name, and exact cell number, so it can be changed.

If you're an investing consumer (not a professional financial planner working with clients), then when you read "client, prospect, or they," just think "you." You would be both the advisor and the client (or spouse).

About financial planning software support.

About getting custodian brokerage account data to download into spreadsheets.

About financial plan software updates.

About financial planning software integration (sharing data between modules)

Read how to present sample reports to prospects and clients (and it's the place to start if you're a financial adviser wanting to learn what's going on at the highest level).

How to send financial plans to clients via e-mail, without sending the whole program.

Basic input concept: Input your data into the green-shaded cells of the input sheets (usually grouped in the middle of the workbook). This will make data flow to the calculations sheets, which are on the far right group of sheets. This data flows through the calculation sheets (or just areas if there are no separate calculation sheets), which will then populate the presentation sheets (usually grouped in the left of the workbook), where you look at the results.

After inputting, you fix mistakes, repeat, format, and print. If a cell is not colored green, then it's not an input cell, so you won't be able to do anything with it. This prevents users from damaging the spreadsheet. Don't input, or type over, any formulas on the presentation sheets because then they will no longer change or function when you change your input data. Input cells usually turn a light-purplish-gray when a non-zero value is entered into them.

About the colored cells: Medium or light gray is either a title heading, or an input cell after a non-zero value is input into it (it's green when either zero or empty). Peach (or pinkish, depending on your Excel version) is (usually password protected) a field that changes when input changes. Light gray cells are information-providing, non-input, cells that don't change with input. White cells usually don't have anything in them, except on the unprotected presentation sheets (and change when input changes).

You can copy the input sheet to the far left unprotected Scratch Pad sheet, then make notes there, so you'll know why you did what you did in the future.

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

Real Estate Software Directions

Open the file Real estate Calculator.xlsx or Multiple Real Estate Calculator.xlsx, with MS Excel (2007 or later).

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.

On Real Estate Calculator.xlsx there are six sheets in the spreadsheet. On Multiple Real Estate Calculator.xlsx there's a couple dozen.

For Real Estate Calculator.xlsx, start on the Input sheet. On Multiple Real Estate Calculator.xlsx, 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.xlsx. If you're working with Multiple Real Estate Calculator.xlsx, 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 (that you can hide).

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 future 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've 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 will be.

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, over the same time horizon, by investing in something else.

This is confusing, so an example will help. In the single rental demo, the before-tax IRR is 8.97%. If you were to input 8.97% 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 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 8.97%.

That also means if you bought mutual funds that returned 8.97% on 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, even after the tax benefits.

Back to the example: If you input 9.5%, then you'll get around -$5,000 in cell F21. This means that if you would have had the exact same cash flows at the exact same time, and got 8.97% instead of 9.5% in mutual funds, you would have made $5,000 MORE money in mutual funds than the rental.

So if you are being "required" to get a 9.5% rate of return on your money, you would have been disappointed by $5,000 by investing in this rental property. The bottom-line in this example, is that if your goal is to get 9.5% on your money with this property, then you should not have paid $250,000 for it (as inputted into cell A15 of the Input sheet). You should have only paid $245,000.

Experienced real estate investors can use NPV to value properties outright like this, and they can also use it to make apples-to-apples comparisons on like properties. This is 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 an 8% rate of return requirement on your money (instead of 9.5%), then you would have made your 8.97%, then, around $10,000 on top of that (the difference between 8% and 8.97% 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 $270,000. In this case, the property is a great deal if you only expected to get 8% 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 real estate calculator product page, when you borrow money in the Real World, the payments / interest / principal / and end-of-year liability will not be the same as the program calculates. Few programs do this correctly, so the best thing to do is to create a Real World amortization schedule yourself, and then input these numbers 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 - 19 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 items 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.

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 Loan Parameters:

This is a good time to point out that whenever you use a manual override, you can makes notes to explain why in the text column next to it. This is to help remember why 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 principal 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 principal 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'll need to do.

You can also account for making additional principal payments by using column T. These amounts input will be in addition to any principal 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 of the Input sheet. Add any amounts of additional / miscellaneous incomes (laundry room, parking, etc.) you'd 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, then have this amount be the new base amount, then have it inflate at a set amount in the future, do this:

Say in year #5 you increased rent to be $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, then will be inflated at the rate input into cell A32 of the Input sheet.

To make subsequent years rent inflate (at 5%) 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), 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'd account for this estimated loss of 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 automatically 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.

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 CJ of the Calculations sheet, and it will manually override the global default rate input into cell A35 of the Input sheet.

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

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 input 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'll 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. This is the calendar year, not the number of years.

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'd 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, then subtracts this amount to the last year's cash flow in column BY of the Calculations sheet.

You can set this to be whatever you want 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 A39 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 CD of the Calculations sheet. The amount of taxes is the amount of the sale proceeds (explained above) minus the basis in column BW 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 BU of the Calculations sheet, and the amount of taxes is shown in column BV. 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 calculate 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 Scratch Pad sheets).

How to Use the Underwater Calculator

Go to the Underwater Input sheet.

Input all of the data into B5 - 19. There's no protection in these cells, so you can copy and paste the whole input block with one stroke.

Input the month you want to see the bottom-line numbers for into B19. This is the month you expect to sell the property.

That's about it, unless you want to use the manual overrides on the Underwater Calculations sheet. If so, then just input those value where it says to.

The results are displayed on the Underwater Results sheet.

The bottom line three choices on what to do about this conundrum is in a text box on the Underwater Calculations sheet around cell AA1.

How to Account for 1035 Exchanges

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

Just set the rental up as being sold in the current year, 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.

Articles about renting vs. owning a home

Financial Planning Software Modules For Sale
(are listed below)

Financial Planning Software that's Fully-Integrated
(the IFP is the NaviPlan alternative for 1/6th the price)

Goals-Only "Financial Planning Software"
(the MoneyGuidePro alternative for 1% of their price)

Retirement Planning Software Menu: Something for Everyone
(the RWRs, RP, and SRP)

Comprehensive Asset Allocation Software

Model Portfolio Allocations with Historical Returns

Monthly-updated ETF and Mutual Fund Picks

DIY Investment Portfolio Benchmarking Program

Financial Planning Fact Finders for Financial Planners Gathering Data from Clients

Investment Policy Statement Software (IPS)

Life Insurance Calculator (AKA Capital Needs Analysis Software)