# Tag: RealData

## 5 Mistakes Every Real Estate Investor Should Avoid

In my nearly 30 years of providing analysis software to real estate investors, and almost a decade of writing books and teaching real estate finance at Columbia University, I’ve had the opportunity to talk with thousands of people who were analyzing potential real estate investments. Some of these people were seasoned professionals, many were beginners or students, but just about all were highly motivated to analyze their deals to gain the maximum advantage.

I’ve seen some tremendous creativity in their analyses, but I’ve also seen some huge missteps. Here are some of the pitfalls you will want to be sure to avoid.

1. The Formula That Doesn’t Compute

If you are attempting any kind of financial analysis, then a full-featured spreadsheet program like Excel is almost certainly your tool of choice. You might opt for professionally built models, like my company’s RealData software, or you could attempt to construct your own.

• One of the most common problems I see in do-it-yourself models is the basic formula error. A robust financial analysis involves the interaction of many elements, and it is really easy to make any of several errors that are hard to detect. The simplest of these is an incorrect reference.  You entered your purchase price in cell C12 and meant to refer to it in a formula, but you typed C11 in that formula by mistake. You may (or perhaps may not) notice that your evaluation of the property doesn’t look right, but it can be difficult for you to find the source of the problem.
• You used to have a formula in a particular cell, but you accidentally overwrote that formula by typing a number in its place. The calculation is gone from the current analysis, and if you re-use the model, you’ll always be using that number you typed in, not the calculated value you expect.
• Cutting and pasting numbers seems innocent enough, but it can scramble your model’s logic by displacing references. Simple rule: Never cut and paste in a spreadsheet.
• Perhaps the most insidious is the formula that doesn’t do what you thought it did. Let’s say you have three values that you enter in cells A1, B1, and C1. You want to write a formula that adds the first two numbers and divides the result by the third. It’s easy to say this in plain English: “I want A1 plus B1, divided by C1.” So you write the formula as =A1+B1/C1. Wrong. Division and multiplication take precedence, so the division happens first and that result gets added to A1. Not what you expected. The formula that does what you intended would be =(A1+B1)/C1, where the sum of A1 and B1 is treated as a single value, divided by C1.

2. The Modern Art Syndrome

Even if you get all of your formulas correct, your job is only half done. I harangue my grad students constantly with this pearl of wisdom: Sometimes you create a pro forma analysis of a property strictly for your own interest. You will never show it to anyone else. Most of the time, however, successful completion of a real estate investment deal means you have to “sell” your point of view to one or more third parties:

• You may be the buyer, trying to convince the seller that your offer is reasonable;
• You may need to convince the lender that the deal should be financed; or
• You may need to show an equity partner that his or her participation would be profitable.

Most of the homebrew presentations that I see look to me like a Jackson Pollock painting with numbers superimposed. The layout usually has a logic that I can’t discern, and I find myself hunting for the key pieces of information that the presenter should have designed to jump off the page.

The layout needs to be orderly and logical: revenue before expenses and both before debt service.

Labels need to be unambiguous:

• If you mention capital expenditures, are they actual costs or reserves for replacement?
• Is the debt service amortized or interest only?
• When you label a number as “Price,” are you talking about the stated asking price, or your presumed offer? Be clear.

Lenders and experienced equity investors will be looking for several key pieces of information before they scrutinize the entire pro forma, items like Net Operating Income, Debt Coverage Ratio, Cash Flow and Internal Rate of Return.  If these items don’t stand out, or if the presentation is disorganized, you might as well add a cover page that says, “ I’m Just an Amateur Who Probably Can’t Pull This Deal Off.”

3. Errors, We Get Errors, Stack and Stacks of Errors

You may be too young to know Perry Como’s theme song (by the way, it was “letters,” not “errors”), but the tune goes through my head when I look at some investors’ spreadsheets.

• The #NUM error can appear when you try to perform a mathematically impossible calculation, like division by zero, or also when attempting an IRR calculation that can’t resolve.
• #VALUE usually occurs when you type something non-numeric (and that can include a blank space, letters, punctuation, etc.) into a numeric data-entry cell. If there are formulas in your model that are trying to perform some kind of math using the contents of that cell, those formulas will fail. In other words, if you try to multiply a number times a plain-text word, you’re violating a law of nature and Excel is going to call down a serious punishment on your head, a sort of high-tech scarlet letter.

It can get really ugly really fast because every calculation that refers to the cell with the first #NUM or #VALUE will also display the error message, so the problem tends to cascade throughout the entire model. Unfortunately, I often see investors who then go right ahead and print out their reports with these errors displayed and deliver the reports to clients or lenders.

Your objective in giving a report to a third party is typically to try to convince the recipient to accept your point of view. You will not accomplish that if your report has uncorrected errors.

4. What’s Wrong with This Picture?

It’s the errors you overlook – the ones that don’t have nice, big, upper-case alerts like #VALUE – that can cause the greatest mischief of all; and these can be troublesome even if the analysis is for your eyes only.

It may be an unwanted and unintended side effect of the computer age that we tend to accept calculated reports at face value. Be honest: How often do you sit at a restaurant with a calculator and verify the addition on your dinner check?

This presumption of accuracy can be dangerous when you are evaluating a big-ticket item like a potential real estate investment. As I discussed earlier, you could have bogus formulas that give you inaccurate results. But even if you use a professionally created tool like RealData’s Real Estate Investment Analysis software, you are still not immune to the classic “garbage in, garbage out” syndrome.

The mistake that I see far too often is a failure to apply common sense. For example:

• “Gee, this investment looks like it will have a 175% Internal Rate of Return. Looks good to me.”  (Reality: You entered the purchase price as \$1,000,000 instead of \$10,000,000. You should have been saying to yourself, 175% can’t be right; what did I do wrong?)
• “Wow, this property shows a terrific cash flow.” (Reality: You entered the mortgage interest rate as 0.07% instead of 7%.) Again, results outside the norm, either much better or much worse than you would reasonably expect, are your tip-off that a mistake is lurking somewhere. It is essential that you develop the habit of examining every financial work-up – those you create, and also those that are presented to you – very closely to see if the calculations appear reasonable.

5. What You Don’t Know CAN Hurt You

The final item in our list of big-time mistakes goes beyond the mechanics of spreadsheets and formulas and into the realm of fundamentals. You can be the most proficient creator of spreadsheet models on the planet, but if you don’t really understand the essential financial concepts that underlie real estate investment analysis, then you will neither be able to create nor interpret an analysis of such property.

The examples that I’ve seen are numerous – I can’t possibly list more than a few here – but they all revolve around the same issue:  A lack of understanding of basic financial concepts as they apply to real estate.  Some of the most important:

• Net Operating Income – This is a key real estate metric, and calculating it incorrectly can play havoc with your estimation of a property’s value. Basically, NOI is Gross Operating Income less the sum of all operating expenses, but I have frequently seen all kinds of things subtracted when they should not be. These have included mortgage interest or the entire annual debt service, depreciation, loan points, closing costs, capital improvements, reserves for replacement, and leasing commissions. None of these items belongs in the NOI calculation.
• Cash flow – I have seen NOI incorrectly labeled as “cash flow,” and have seen cash flow miscalculated with depreciation, a non-cash item, subtracted.
• Capitalization rate – Cap rate is another key real estate metric and is the ratio of NOI to value. Unfortunately, I’ve encountered some folks who have used cash flow instead of NOI when attempting to figure the cap rate and have ended up with a completely erroneous result – not only for the cap rate itself, but then also for the value of the property.

Clearly, there are two vital problems with these kinds of basic errors. First, is that they completely derail any meaningful analysis. If your NOI is not really the correct NOI and your cap rate is not really the correct cap rate, then nothing else about your evaluation of the property can possibly be correct. And second, if you give this misinformation to a well-informed investor or lender, your credibility will evaporate.

The Bottom Line

What is our take-away from these five disasters waiting to happen? You could avoid many of these errors by using the best, professionally developed analysis models – but then, of course, you would expect me to say that because that’s what we do for a living.

Let me suggest three other important steps you can take:

• Understand that there is no substitute for careful scrutiny of any financial presentation, whether it is someone else’s or your own. Be diligent always and  apply the test of reasonableness.
• Recognize that any real estate analysis you create is likely to be a representation to a third party of the quality of your thinking and professional competence. You wouldn’t be careless or casual with a resume; you should give the same care to your real estate presentations.
• Finally, recognize that you need to make a commitment to mastering the fundamental concepts and vocabulary of real estate investing. There is no substitute for knowledge.

####

## For Real Estate Investors: A Lesson in Clarity

Recently, I was conducting the last class in my course on real estate investment analysis that I teach in Columbia’s MSRED program.  I had assigned my 55 students a series of case studies (much like those in my book, Mastering Real Estate Investment) and told them to build financial pro forms and discuss the reasoning behind their analyses. After reading and commenting on all those analyses, I felt there was one overarching theme on which I wanted to focus my final remarks to the troops: The theme was “clarity.”

Trying to reduce a course to a single word might seem unrealistic (because it is), but I really had more than one angle on the notion of clarity in mind. Even combined, those notions would not replace the real content of a course in investment analysis, but they might express some essential principles that are sine qua non — “without which, nothing” — for investors.

Before you fire up your spreadsheet program or sharpen your pencil, you need to be very clear about your objective (or objectives) in analyzing the property. For example:

• Are you a potential buyer, trying to establish a reasonable offer on a particular property?
• Are you seller or broker trying to justify your asking price?
• Are you a buyer or broker, trying to demonstrate to a seller that his or her price and terms would not be acceptable to a reasonable and prudent investor?
• Are you seeking financing, or refinancing and need to demonstrate to a lender that this loan will meet their underwriting expectations?
• Are you assembling a partnership and trying to show potential equity investors that this deal will make economic sense to them?

You are not trying to create alternate realities, but you might be harboring more than one objective in a given situation. For example, for your private use you might want to look at a range of possible offers by creating best-case, worst-case and in-between scenarios; but in making a presentation to the seller, you would surely not begin by volunteering what you believe to be the highest price at which the investment might have a chance of success.

In making a presentation to a lender, your focus must be to ensure that your presentation includes items like debt coverage ratio, allowance for possible vacancy, and projected cash flows — items that will have an immediate impact on an underwriting decision. For equity partners, you want to be sure that you can demonstrate not only that the property itself makes sense, but that the particular investor, considering allocations and preferred return, can expect an acceptable rate of return on cash invested.

You are typically trying either to make a personal decision about a property or to “sell” your point of view to a third party. Being clear in your own mind about the purpose of your pro forma allows you to focus on how you analyze the property and what information is of greatest importance to your intended audience.

Real estate, like most businesses and professions, has its own language – terms that carry very specific meaning. The misuse of real estate investment terminology can have several possible consequences, all of them bad.

• You can substantially skew the results of an analysis by not being clear in your understanding of important terms. Some of the more egregious examples I have seen include:
• Not understanding the real-estate-specific definitions of terms like “operating expense” and “Net Operating Income.”  I have often seen investors try to include mortgage payments, capital improvements, or reserves for replacement as operating expenses. This mistake can drastically affect your estimate of a property’s worth.
•  Not understanding an important term like “capitalization rate.” I have seen investors try to estimate value by applying a cap rate to the property’s cash flow instead of its Net Operating Income. Big mistake.
• You can bring a dialog or negotiation to a grinding halt by being unclear and offhand in your use of what should be unambiguous terms.  Yes, “price” is a legitimate English word. But if you use it as part of an analysis or presentation, you will leave your reader stumped.  Do you mean the seller’s asking price, the buyer’s offered price, the actual closed selling price?  You can tell me that a building has 20,000 square feet, but do you mean usable square feet or rentable square feet?  It makes a difference.
• You can establish your identity as a rank amateur. Nothing will earn you a sandwich board with the word “newbie” on it quicker than misusing terms or lapsing into incomprehensibly vague language. Credibility matters — just ask your lender or your equity partners.  Be clear. Be precise.

Be Clear When You Build Your Pro Forma or Presentation

If you insist on being a do-it-yourselfer, and you plan to give your pro forma or presentation to a third party, keep in mind that nothing will unsell your argument faster than a jumbled pile of numbers.  Your information should flow and be segmented in a logical order (e.g., don’t show someone the income after the expenses, or the debt service after the cash flows). The reader should be able to apprehend the key metrics with a quick scan of the page, then go back and fill in the details. If your report turns  into a scavenger hunt for vital information, then you will fail to deliver your message. No loan, no partner, no deal.

Your success as a real estate investor requires serious number crunching, but it doesn’t stop there. You must be able to convey your analysis of a property in terms that are unambiguous, accurate, and relevant to your audience. Clarity is what you need.

–Frank Gallinelli

Get some clarity, as well as accurate calculations and industry-standard reports. Use RealData’s Real Estate Investment Analysis, a market leader for almost 30 years, to run your numbers and create your presentations.

## The Flavor of the Month: Apartment Investing

It comes as no surprise to those of us who are a bit long in the tooth: The recent economic environment has been bad for almost everything, but it’s good for multi-family investment property.

When credit flows freely, almost anyone who can buy a house will buy a house. (Whether they can pay for it after the closing is of course another matter.) On the other hand, when credit tightens or dries up almost completely, then the subprime prospects are frozen out of the housing market, along with a sizeable group of perfectly responsible borrowers who now find they can’t clear the considerably elevated qualification standards. It doesn’t take tremendous insight to realize that most of these people are now candidates for apartment space. Remember Econ 101?  Supply, demand, etc.

If you read the financial press (or follow our tweets) then you’ve seen ample evidence lately that apartment properties are hot. The Wall Street Journal  cites a Marcus and Millichap report stating the the values of apartment buildings rose 16% in 2010 after falling 27% between 2006 and 2009. In that same article, WSJ says that the supply of new apartment buildings is at a two-decade low. There’s that supply and demand thing again.

Reuters  recently reported that apartment vacancies showed a steep drop in the first quarter of 2011. At the same time, Investor’s Business Daily noted that even the smallest buildings — those with four units or less — were in high demand. An advantage here for the small investor is that this kind of property can usually qualify for Fannie- or Freddie-backed financing, and perhaps on even more favorable terms if the investors lives in one of the units.

After a long period when it seemed like investors were in duck-and-cover mode, it’s good to see this resurgance of activity.

(self-serving footnote: If you’re doing an apartment deal, be sure to run the numbers first, Either the Express or Professional Edition of Real Estate Investment Analysis will do a great job with apartment buildings. If you’re raising capital from equity partners, then use the Pro Ediiton — it will give you presentations for individual partners.)

## New Mac-Compatible Releases of RealData Software

We’ve supported the Macintosh with Excel-based products since the Mac first came out in 1984.  (In fact, way back then we received an award as one of “100 Most Important Companies on the Macintosh.”)

But a few years ago, Microsoft threw us a real curve when they dropped VBA macro functionality from Excel 2008 for the Mac.  As you may know, it’s those complex and sophisticated macros (which you can’t see) that make our software really powerful and easy to use. So our Mac users had to settle for Excel 2004 to run our software

Thankfully, Microsoft has seen the light and restored VBA in their new Excel 2011; and we wasted not a moment getting to work re-writing our products to make good use of the new Excel. It was no small task, but  now all of our products are truly Mac-compatible.

Not only will you have the advantage of Excel’s new interface and features, but you’ll now be able to use the latest versions of RealData software on your Mac — even REIA Express, Version 2 and the RealData Real Estate Calculator — and you’ll see all of our programs run at speeds that are orders of magnitude faster than they ran with Excel 2004.

(A sidebar note: Because these programs are not simply spreadsheets, Windows versions won’t work properly on the Mac and vice-versa. That’s why we created these new releases specifically for the Mac.)

We’re glad to be back offering the latest and best of our software for our loyal Mac customers.

## Making the Case for Your Commercial Refinance, Part 2

In Part 1 of this article, you learned what information you need to assemble to get started with the process of refinancing your commercial property — information about your property’s income, expenses and loan balance, and about the prevailing cap rate in your market. You also learned to use some of that information to estimate the current value of the property, then learned to take that result to determine if the property will likely satisfy the lender’s Loan-to-Value requirement.

You got acquainted with Debt Coverage Ratio and mortgage constants and saw how to combine those to test your property’s income stream to find out if it’s strong enough to support your loan request.

Now you have some idea of what your property is worth and how likely it is to appeal to an equity partner or to satisfy a lender’s underwriting requirements. Your next task is to convey your evaluation to that potential partner or lender. You need to make your case with a professional presentation that is easy to grasp but also provides enough detail to support your evaluation of the property and your request for financing.

Unless you’re a “flipper,” you can expect to be involved with this property for the long haul, more or less. It should come as no surprise, therefore, that a lender or investor will appreciate getting a sense of how you believe this property will perform over time. You’re not going to predict the future with precision, but in most situations you should be able to make some reasonable and realistic “pro forma” projections of future performance. There is no hard-and-fast rule, but I believe your projections should go out five to ten years. With commercial properties that have long-term leases in places, 20 years would not be unthinkable.

As we develop the pro-forma presentation through the rest of this article, we’ll be using the Standard Edition of RealData’s Real Estate Investment Analysis (REIA) software. Those readers who are familiar with the software will also note that I’ve taken a few liberties with the material I display, editing some of the images (for example, removing multiple mortgages) to allow you to keep your focus on just the key elements of this example.

Where to start? You’re dealing with rental property, so a rent roll would be a good place to begin. And let’s assume that “today” is January 1, 2009. List your rental units (or groups of units, if you have a large number) with the current rent amount and your estimate of how those rents will change over time. You’ll recall from the APOD you constructed earlier that you expect the total gross scheduled rent for this property to be \$219,600 in the first year. For the sake of making this example worthwhile, assume that the property contains both residential and non-residential units, and therefore the total amount of revenue is divided between the two types.

With residential units — apartments, for example — the process of building your rent roll will be fairly straightforward. The rent for each unit of this type is usually a fixed monthly amount. Residential tenancy agreements are seldom long term, most often a one-year lease or even month-to-month occupancy. It’s reasonable to assume that you will try to increase your overall rents on an annual basis. For the first year, you have the following:

Demand for your apartments has always been strong, but you decide you want to be conservative in your estimate of how much more you can charge each year so you decide to project that these rents will rise at an annual rate of 3%.

This is a mixed-use property, which means it contains commercial as well as residential rentals. At street level, below the apartments, you have two retail spaces. The first of these is a hardware store, Nuts & Bolts. This store occupies 1,000 rentable square feet and currently pays \$21.60 per square foot per year. Its lease calls for a rent increase to \$23.50 in July of 2011 The second tenant is Last National Bank, which occupies 2,800 square feet at \$25.00 per foot. This tenant’s rent is scheduled to rise to \$28.00 per square foot in September of 2012.

Note how your handling of commercial rentals differs from residential. One difference is that you typically charge rent by the square foot rather than by the unit. In most U.S. markets, the rent is expressed in terms of dollars per square foot per year, although in some it is per square foot per month. A second difference is in the length of the lease. As noted earlier, a residential tenant’s commitment may be as little as month-to-month, and generally is not more than one or two years. Commercial tenants, in order to maintain and operate a business from their space, need the certainty that they can continue to occupy for a reasonable length of time. They also need to be able to plan their future cash flow. Hence a commercial lease will usually run for at least a few years, up to as many as 20 or 30.

With the information you have in hand about these commercial leases, you should be able to project the rent from the two commercial units for next several years.

The image above is a screen shot from a data-entry portion of the REIA software. This is one image where I haven’t done any editing, i.e., I haven’t removed line items unrelated to our example. I’ve left it complete so you could see that there are other considerations you might need to take into account when you deal with a commercial lease, such as expenses passed through to tenants, leasing commissions, and improvements to the space made by the landlord on behalf of the tenant. We don’t want this article to morph into a full-scale textbook, so we’ll continue to keep our example relatively simple. However, for more information on these and similar topics, you can view our educational articles at realdata.com or refer to the software user’s guide forReal Estate Investment Analysis.

You now have a forecast of the revenue from both the residential and commercial units, and can consolidate this data to include as part of your presentation to your lender or potential partner.

Recall that when you were estimating the value of the property you used something called an Annual Property Operating Data (APOD) form. That form displayed the total rental revenue, an allowance for vacancy and credit loss, and the likely operating expenses for the current year. To fit the needs of your extended presentation you can expand this form to as many years as you want.

For the purpose of this discussion you’ve been projecting out five years, so you’ll do the same with the APOD. You may want to refine your estimates on an almost item-by-items basis. For example, if property taxes, maintenance and insurance are among your greatest expenses, it makes sense to estimate their rates of growth individually. You probably have some history with these items that you can use for guidance. For some other expenses, such as accounting or trash removal, you may want to apply a general, inflation-based estimate. In this example, property management is one of your biggest costs. You know that it will be billed at \$15,740 for the first year, but then as a percentage of collected rent — 7% in this case — for future years, so your estimate will just require that you apply the same rate. If you estimate the future rent reasonably well, then the property management fee will follow.

Let’s say you believe that property taxes will increase at 5% per year, and insurance and maintenance at 4%. For all other expenses, you project a 3% annual increase. Your extended APOD should look something like this:

If you owned this property debt-free, your analysis would be nearly complete. But in fact, your objective here is to build an effective case for refinancing your existing loan, so you really need to demonstrate what kind of cash flow this property will throw off with a new loan in place. You need to take this at least one step further.

Recall from the first section of this article that you estimated the value of the property at \$1.45 million, and that you need to refinance your \$975,000 loan at 7.75% for 15 years. With that information in hand you can complete the taxable income and cash flow sections of your pro forma.

These projections should help you make a strong case for approval of your new loan. With that new loan in place, your debt coverage ratio is more than ample in the first year, and improves each year thereafter. Your cash flow is strong, and it too grows each year. It’s strong enough, in fact, that you could even survive the loss of one of your commercial tenants without plunging into a negative cash flow.

Your Net Operating Income is also going up smartly. Perhaps your lender is concerned that the current prevailing cap rate of 11% will rise to 14% by 2013, possibly reducing the value of the property dangerously close to the amount of the mortgage. Does that look like a genuine cause for anxiety?

 Value = Net Operating Income / Capitalization Rate Value in 2013 = 177,839 / 0.14 Value = 1,270,279

So, if cap rates rise to 14% and your NOI is indeed 177,839, then the property should still have a value of about 1.27 million. This is not good news for you, but does the lender have reason to lose sleep?

What will your loan balance be at the end of 2013? You will have been dutifully paying it down from now until five years hence, so surely you will have made a dent. If you return to the REIA software, you’ll find that it includes amortization schedules for all of your property loans. It also tracks the end-of-year balance for each loan as part of its resale analysis, so let’s look at that:

You will owe \$764,719 at the end of 2013. Your property, if cap rates do rise to 14%, will be worth about \$1,270,000. Recall that your lender required a Loan-to-Value Ratio of 75% when you applied for the loan. Will it be time to reach for the antacids?

 Loan-to-Value Ratio = Loan Amount / Property’s Appraised Amount Loan-to-Value Ratio at EOY 2013 = 764,719 / 1,270,000 Loan-to-Value Ratio at EOY 2013 = 60.2%

Your LTV looks even better at EOY 2013 than it did when you originally applied for the loan. It’s time to find a polite way to tell the lender to stop looking for excuses. Your loan request is solid and needs to be approved.

You’ve assembled a good deal of data to support your loan request, but don’t forget that a major part of your objective here is to present it in the most effective way. Start by trying to boil it all down. Simplify and summarize. Think of this part of the process as the real estate equivalent of the “elevator pitch.” Ultimately you’re going to need to provide the loan officer with every detail, but you may not get a chance to tell the whole story unless you can convey the essentials in the time it takes to ride the elevator. You need an executive summary.

This report gives a very direct one-page summary of basic information about the property and its financial metrics. Your lender can see immediately the amount of the loan you’re looking for, the LTV and Debt Coverage Ratio, the Net Operating Income and the cash flow. This report doesn’t supply the underlying supporting data to justify these numbers — that’s why it’s a summary — but taken at face value it tells the loan officer whether there is any reason to give your request a serious look.

An alternative is a report we call the “Real Estate Business Plan,” and it too looks very different from the rows and columns of numbers usually associated with a pro forma. You might assemble information into a report like this in a situation where you still want to make your initial approach with what is essentially still an overview of the property, but one that provides a bit more detail than the one-page summary. Just as with the Executive Summary, you want to provide enough information to be effective, but not so much that you discourage the recipient from actually reading the document.

We designed this report to focus on property description, sources and uses of funds, financing, cash flows, and rates of return, and to simplify its presentation by displaying only the data that is pertinent to the holding period you specify. So, even though the software can deliver projections of up to 20 years, if you want a report based on a five-year holding period, you get a nice, clean presentation with no extraneous labels or data, as you see in this excerpt:

At the beginning of our discussion of pro formas and presentations, I said that you needed to deliver a package that is easy to grasp but also provides enough detail to support your evaluation of the property and your request for financing. You may have already inferred from the progress of this article that the process of building the presentation runs in a direction that’s essentially opposite from the process of delivery. You need to begin, as we did here, at the most granular level of detail: defining individual unit rents and item-by-item operating expenses, first as they currently exist, then as you project them to grow.

That is why you built your rent roll first, then your extended APOD, then your cash flow projections. Next, you distilled this information into summary formats — the Executive Summary and the Real Estate Business Plan.

You built your case by going from the specific to the general. You’ll typically present your case for financing, however, by going the other way. You start with the Summary or Business Plan type of report, which provides enough information to introduce your request without burying the loan officer in a mountain of tiny numbers. When that loan officer says, “Where did you get these revenue projections?” you’ve got your rent roll. When she says, “How did you come up with this NOI?” you’ve got your APOD. And you can do the same for your cash flow and debt coverage, and resale value and rates of return, and more.

You’ve got it all covered.

Before we conclude this discussion, a brief reality check is in order. The example we just worked through was a happy case study because the property’s income stream justified the financing you sought. All the number crunching in the world, however, won’t transform a troubled investment into a good one. A detailed analysis can, however, still be helpful because it can show you what level of revenue you need to reach, or what level of cost-cutting you have to achieve to bring the property into positive cash flow territory and get it back on its feet. But whatever you do, don’t try to “enhance” the numbers to make the property look good. You’re not going to fool the lender and there’s not much point in fooling yourself.

So, what did you learn in Part 2 of this article? You learned to build a rent roll, one style for residential units and another for commercial. You learned to develop pro forma projections by extending your current-year estimates of revenue, operating expenses, and cash flows into the future. Perhaps most important, you learned about creating presentations out of those pro forma projections — presentations that are readable and effective, and that can help you make you case for financing your investment property.

## Making the Case for Your Commercial Refinance, Part 1

Since we released the original version of our Real Estate Investment Analysis software in 1982, our focus has been on pro forma financial analysis of real estate investments and of development properties – projecting the numbers out over time to help users gain a sense of what kind of investment performance they might expect from a particular property or project.

And for lo, these many years, our customers (and from time to time, we ourselves) have used the software to help make decisions as to whether or not to buy a property, and at what price and on what terms. Customers have used it to model how things might play out in the worst case, or in the best case, or somewhere in between. They have used it also to compare alternative investment opportunities.

This type of decision-making process has by far been the most common use of our software. More and more, however, we’ve seen an increased use of these pro formas for the purpose of making presentations to potential equity partners and to lenders.

Which brings us at last to the point of this article. When the economy is blazing away at warp speed, everything is – or at least seems – a bit easier. Forecasts are easier to meet, and partners and lenders are easier to find. But sometimes the economy is not so good, and presents us with new challenges. At this writing, we find ourselves in the middle of a bad case of credit lockjaw. Nothing lasts forever (which in this instance is a good thing), so eventually our credit markets and overall economy will rediscover their equilibrium.

This is all fine, unless you’re holding a property today with a mortgage that will balloon in the near future. In that case, you need to find a new loan, and you’re probably going to have to work for it. That means doing some homework, understanding the process, and building the most compelling case you can for approval of that new loan.

If you were trying to refinance your home, you would be dealing with recent sales of comparable houses, your personal income and debt, and your credit score. With the possible exception of working to get your credit report in order, there’s not a great deal you would do personally to build a case for your re-fi. With an income property, however, a carefully prepared presentation can go a long way in helping you convince a lender – or even a new equity partner – that you have a viable investment.

Re-enter your friend, the pro forma analysis. You may have thought he was on vacation until sales of real estate revived, but in fact he’s as busy as ever with financing and partnerships. If the numbers do indeed work for a property whose balloon is coming due – and sorry, don’t expect to transform a bad investment into a good one with just a pile of color charts – then a detailed pro forma may be that property’s best friend.

Don’t even think about starting that pro forma until you’ve done a bit of legwork and preparation. First you’re going to need some information that is external to the property itself. You need to know the prevailing market capitalization rate for properties of the same type as yours (i.e., office, retail, apartment, industrial, etc.) and in the same market. This information will be critical to estimating the current value of the property. Perhaps the best place to seek this information is from a local commercial appraiser. The bank will certainly use an appraiser, and the appraiser will certainly use a cap rate, so don’t get left out of the party. For the sake of the example we’re going to construct here, say that the commercial appraiser tells you the prevailing cap rate for properties like yours in your market is 11%.

Next you need to learn about underwriting criteria from your potential lenders. Specifically, you need to know the probable interest rate and term of the new loan; the lender’s maximum Loan-to-Value Ratio; and the lender’s minimum required Debt Coverage Ratio. Don’t assume that these criteria will be identical across all lenders or across all property types. In fact, they probably will not. It should not surprise you that different lenders quote different interest rates, but you must also recognize that the same lender may be willing to lend 80% of the value of an apartment complex, but only 65% of the value of a shopping center. Know the lender’s terms before you ask for the loan.

For the purpose of this example, let’s say you’ve called your current lender and found that their maximum Loan-to-Value Ratio for a property like yours is 75%. They require a Debt Coverage Ratio of at least 1.20, and if all looks good, they will loan at 7.75% for 15 years.

We’ll discuss these criteria in detail in a moment, but for now let’s stay focused on collecting information, this time about the property itself. You need to assemble the amount of actual current rent income from each unit and identify the market rent of currently vacant units. You need to make realistic estimates of rental income for the next several years, taking into account the terms of leases now in place. You must figure your current year’s operating expenses, keeping in mind that certain expenditures such as debt payments, capital improvements and commissions should not be included. Nor should you include depreciation or amortization of loan points, which are deductions but not operating expenses. Once again, you have to make some realistic estimates as to how these expenses may change over the next several years. Finally, of course, you have to learn the balance of your current mortgage, so you’ll know how much of a re-fi you require.

Let’s return now to the underwriting criteria you identified, and start with the Loan-to-Value Ratio (LTV):

 Loan-to-Value Ratio = Loan Amount / Lesser of Property’s Appraised Amount or Actual Selling Price

If this is a re-fi, then there is no “selling price,” so the value here will be the amount for which the property is appraised. If your financial institution has not taken leave of its senses (in general, if it has not appeared in the headlines or before a Congressional subcommittee in the last six months), then it should be reluctant to loan you or anyone else 100% of the value of a property. They expect you to have some skin in the game, and the question is merely how much.

The lender will quote you their maximum LTV, and before you get anywhere near an application form, you are going to perform your own calculation with your particular property. How much of a loan do you need to replace the existing financing, and how does that relate to the current value of the property?

It should be clear enough that the lower your actual LTV, the more likely you are to secure the loan. The lower the LTV, the more you, the borrower have to lose and the less likely you are to walk away. A low LTV may even earn you more favorable terms. You know how much of a loan you need, so to determine the LTV of your proposed loan, you must estimate the value of the property. Find that value with the same method the lender’s appraiser is likely to use: by applying a capitalization rate to the Net Operating Income (NOI). You already called around to find the prevailing market cap rate, so now you need to calculate the NOI. The most direct way to do this is with the venerable APOD form, where you list your annual income and expenses:

The total of your scheduled rent income for this year should be \$219,600, but because of vacancy and credit losses you will actually collect \$210,816. Your various operating expenses total \$51,050, leaving you a Net Operating Income of \$159,766.

Remember that an appraiser told you the prevailing cap rate for this type of property in your market area is 11%. You have what you need to estimate the value of the property:

 Value = Net Operating Income / Capitalization Rate Value = 159,766 / 0.11 Value = 1,452,418

Round that off to \$1.45 million.

You’re ready now to perform your first underwriting calculation. Recall that your lender’s maximum Loan-to-Value Ratio is 75%. Your current loan – the one that is about to balloon – has a balance of \$975,000.

 Loan-to-Value Ratio = Loan Amount / Lesser of Property’s Appraised Amount or Actual Selling Price Loan-to-Value Ratio = 975,000 / 1,450,000 Loan-to-Value Ratio = 67.2%

Assuming the lender’s appraiser agrees with your estimate of value, you’ve cleared your first hurdle. Being a cautious individual, however, you want to know your worst-case scenario. What is the lowest appraisal that would still allow your \$975,000 re-fi to meet the lender’s LTV requirement? Simply transpose the formula to solve for a different variable:

 Property’s Appraised Amount = Loan Amount / Loan-to-Value Ratio Property’s Appraised Amount = 975,000 / 0.75 Property’s Appraised Amount = 1,300.000

Any appraisal over \$1.3 million will be good enough to satisfy the 75% Loan-to-Value requirement.

You will want to build a pro forma that goes out a least five years, so you can demonstrate to the lender that your anticipated cash flow and debt coverage are solid and likely to stay that way. Before you do so, however, there is a formula you can use that will give you a quick estimate of the maximum loan amount that the property’s current income can support. Remember that the strength of an income property lies in the strength of its income stream. This is how the lender will look at your proposal, so it’s what you need to do as well. Here is the formula:

 Maximum Loan Amount = Net Operating Income / Minimum Debt Coverage Ratio / (Monthly Mortgage Constant x 12)

You know that your Net Operating Income is \$159,766, and the lender has told you the Minimum Debt Coverage Ratio is 1.20. But what’s this Monthly Mortgage Constant?

A mortgage constant is the periodic payment amount on a loan of \$1 at a particular interest rate and term. If you know the constant for a loan of \$1, you can multiply it by the actual number of dollars of the loan to find the payment amount.

Readers of my books have access to a web site with a variety of tools, including a table of mortgage constants. You can also calculate the Mortgage Constant using this formula in Microsoft Excel:

 =PMT(Periodic Rate, Number of Periods, -1)

In the Excel formula, the amount of the loan must be entered as a negative number. In the case of a mortgage constant, we want to use a loan of \$1, hence the -1. In the case of a loan at 7.75% for 15 years, the formula would look like this:

 =PMT(0.0775/12, 180, -1) = 0.00941276

Since this loan is going to be paid monthly, you express both the rate and the number of periods as monthly amount. Format your answer to display at least eight decimal places.

Now you have all the elements to plug into the formula for maximum loan amount: the Net Operating Income, the minimum Debt Coverage Ratio, and the Mortgage Constant.

 Maximum Loan Amount = Net Operating Income / Minimum Debt Coverage Ratio / (Monthly Mortgage Constant x 12) Maximum Loan Amount = 159,766 / 1.20/ (0.00941276 x 12) Maximum Loan Amount = 133,138.33 / (0.00941276 x 12) Maximum Loan Amount = 133,138.33 / 0.11295312 Maximum Loan Amount = 1,178,704

Keep in mind that rounding could alter your answer by a few dollars.

(An aside: If you’re the sort of person who does not like to play with long formulas, or who tends to tap calculator keys with a closed fist, we have a solution for you. The RealData Real Estate Calculator – Deluxe Edition, will do all of these underwriting calculations for you, as well as perform a host of other useful real estate functions, including amortization schedules for loans with a variety of terms. There are sixteen modules in the Calculator. Find more info at http://realdata.com/p/calculator.)

Your lender will surely round this result, probably down to something like \$1.175 million. But you’re looking for just \$975,000, so it appears that your income stream will support this loan request. You will want to verify this by calculating the property’s Debt Coverage Ratio going out five or more years. You’ll do that as part of your property pro forma, in the next installment of this article.

Up to this point, however, you’ve accomplished quite a bit: You learned what information you need to assemble about your lender’s underwriting process, about your property’s income, expenses and loan balance, and about the prevailing cap rate in your market. You’ve learned to use some of that information to estimate the current value of the property, then taken that result to determine if the property will likely satisfy the lender’s Loan-to-Value requirement.

You’ve learned about another underwriting metric, Debt Coverage Ratio, and about mortgage constants. You’ve seen how to combine those to test your property’s income stream to see if it’s strong enough to support your loan request.

Not bad for an hour or two of work.

Next time you’ll see how to assemble this information and more into the kind of professional presentation you can give to a potential lender or equity partner.

## Cash Flow Analysis — Annual or Monthly?

A reader of one of my books wrote to me recently with a very worthwhile question.  When we build a pro-forma analysis of future cash flows from a real estate investment, why do we annualize those cash flows instead dealing with them on a monthly basis?  After all, rent is typically collected and bills paid monthly.

The quick and facile answer, of course, is because we’ve always done it that way. Back in the day, we didn’t have powerful personal computers and sophisticated real estate software, so one might argue that this annual approach is just a holdover from a golden age that has passed us by.

Then again, there may be some wisdom inherent in this approach. To make monthly estimates of future cash flows requires monthly, rather than annual, estimates of income, expenses and debt service. The task is not impossible, but collecting, organizing, and deploying this amount of data will surely take much greater time and effort, presumably up to twelve times as much. And we all know that time is money.

Is it practical to do this, and if so, is it worth the effort?  It’s important to keep in mind that you’re not performing an accounting function but rather making projections about what’s going to happen in the future. It’s often difficult enough to estimate your annual cost for heating fuel or electricity five years hence. Trying to estimate such costs by the month can be even more problematic and time-consuming.

Assuming that someone else hasn’t already closed on this property while you were playing Hamlet, did you in fact gain any additional insight or advantage as a reward for your extra effort?

A monthly projection of future cash flows substantially increases your “degrees of freedom” in making estimates, so the monthly estimates are not only more difficult to make, but they also provide you with many more opportunities to be wrong. To put it another way, you are just as likely to introduce errors in timing as you are to add precision, thus offsetting at least some if not all of the benefit of your considerable extra effort.

Having said all this, it is also true that a dramatic skewing of cash flow toward the beginning of a year could make a noticeable difference in a particular discounted cash flow calculation. One might feel that is justified to handle such an atypical income stream differently.

For what it’s worth, my opinion is that the conventional wisdom here actually makes sense. Forecasting the future is an imperfect art; in most situations, annualizing the net cash flow is a reasonable compromise with reality and a task of more manageable proportions.

Frank Gallinelli

RealData

## Excel 2007 – New File Types, Macro Security and Other Mysteries

When Microsoft released Excel 2007 it deployed some of the most extensive changes to the product in many years.  It introduced an entirely new user interface, a new menu format called the “Ribbon,” enhanced security, and an entirely new data structure for its files.  With this new data structure came an array of new file types.

Many of these changes have implications that are not always obvious to the user and, from our point of view at least, are not always entirely welcome.

This will be the first of what may be several posts on the RealData blog where we try to address what we feel are some of critical changes you need to know about in Excel 2007 so that you can use it successfully.  We’re writing these posts with users of RealData software in mind – particularly users of our more sophisticated Excel-based products like “Real Estate Investment Analysis,” “On Schedule” and “Commercial/Industrial Development” – but we feel strongly that it’s information all Excel users should have.

So – let’s begin with by comparing the file types in the old Excel vs. the new Excel.

How Things Were:  Two Key Facts to Remember

1. If you have used earlier versions of Excel, you probably know that you used workbooks, called .xls files – and templates, called .xlt files.  Workbooks are collections of worksheets, sometimes accompanied by built-in programming code called “macros.”  Templates are a special kind of workbook.  When you launch a template, it leaves the original unchanged and presents you with a new workbook file based on that template.  That way you don’t accidentally overwrite the original.
2. If you used RealData software, or perhaps certain other commercial Excel-based applications, the Excel file included hidden macro code.  You never saw it, but you know it was there because Excel would ask you if you wanted to enable the macros. Such code is typically essential for the functioning of a complex Excel-based program.  For example, our “Real Estate Investment Analysis” uses more than 12,000 lines of such code to provide menus, format reports, add/delete data records, and so on – functions that can’t be accomplished with simple spreadsheet formulas.  If you lose the code, the program will no longer function properly.

How Things Are: The New File Formats and How They’re Different

New to Excel 2007 are the .xlsx, .xlsb and .xlsm formats which Excel refers to as “Excel Workbook,” “Excel Binary Workbook” and “Excel Macro-Enabled Workbook,” respectively.  It’s important to understand the differences among these file types, and which can be used with RealData software products.

Each of these new formats is based on what Microsoft calls an “Open XML File Format.”  Without getting more technical than we need to here, the short version is that one can openly view and read the data from these files and convert them to other formats if necessary.  This ability is part of a trend toward an open and universal standard for documents so that data is not tied to a particular proprietary software product or company.

If you used earlier versions of Excel, then you know you customarily saved your workbook as a .xls file.  Let’s look at each of the new file formats and compare them to that original .xls Excel file format:

1. The .xlsx format is a lot like the old .xls format, but with one key difference:  It intentionally does not support macro code and will remove any macro code from a file that contains it. Remember what we said about RealData software and other products that rely on macros to provide their advanced functionality?  Right.  Without the macros these programs won’t work.  If you open a macro-driven .xls file and save it in the new Excel .xlsx format, the macros will be deleted and the program will no longer work as expected.

Fortunately, if you do try to save a file in this format, you will receive a warning message before the macro code is deleted:

Keep in mind that macros can be used by malefactors to deliver viruses. If you download an Excel file from some source other than a trusted commercial vendor, you run a risk as you would downloading a file from any unfamiliar source.  You’ll want to keep the macro code in a program from a trusted source like RealData, but you should be wary of any file containing macros if it comes from a source with which you’re not familiar and confident.

If you want to be certain to keep the macros intact in your RealData program, the simplest and most certain solution is this: Click “No” and return to the “Save” dialog.  Where you see a pulldown that says “Save as type,” choose “Excel 97-2003 Workbook (.xls).”

2. In contrast, the .xlsb and .xlsm formats do support macro code.  The .xlsb file is a streamlined format intended to speed up the process of opening and saving the file.  Like the .xlsm format, it can save an equivalent Excel workbook in a significantly smaller file size than the traditional .xls format.  The information in each of the cells is saved as plain text (you could view the information in Notepad if you wanted to) but the  macro code is encrypted.

Is it all right for you to save your RealData program as a .xlsb or .xlsm file and save some disk space?  The answer is a resounding maybe.

To re-open one of these files once you’ve saved it as .xlsb or .xlsm, Microsoft requires that you have installed on your computer an antivirus product capable of scanning and reviewing encrypted macro code before the file opens.  If this scan process does not happen, then the macros will be disabled in your software.  If you’re alert, you’ll  know this is so because of an inconspicuous warning message that appears in a horizontal bar above the Excel work area:

Click on “Options” and you should see something like this:

The default choice is “Help protect me….”  If you are confident that this file came from a trusted source, you can choose “Enable this content.”  If the file has a security certificate attached, the details of that certificate will be displayed and you can choose “Trust all documents from this publisher.”  RealData does have a security certificate attached to its program files.

Unfortunately, not all antivirus software is able to scan encrypted macro code. From our own experience we can confirm that Version 8.0 of the AVG  product appears to work without problems ( http://www.avg.com).

If you were getting ready to breathe a sigh of relief, hold it.  What we describe above is how .xlsb and .xlsm are supposed to behave.  Some users, however, have reported to us that, if they see the “macros disabled” message and click the options button, they get only one choice, and it’s not the one they want:

Why does this happen sometimes, but not always?  If we could answer questions like that, we’d be so famous you wouldn’t even be able to talk to us.  Seriously.

Which brings us back to bullet-point #1, worth repeating;

If you want to be certain to keep the macros intact in your RealData program, the simplest and most certain solution is this: Click “No” and return to the “Save” dialog.  Where you see a pulldown that says “Save as type,” choose “Excel 97-2003 Workbook (.xls).”

This should be your choice if you want your analysis to be compatible with both Excel 2007 and other computers which may have an earlier version of Excel installed on them.  RealData software products automatically detect if you are using Excel 2007 and set the default file type to be .xls, although you can override this if you so choose.

The Short Version

1. RealData delivers its macro-powered programs using the Excel 97-2003 file format, i.e., .xls. If you always save your work in that format, you should have no problems with Excel 2007 opening or running the macros in RealData programs.
2. If you save a file in .xlsx format, Excel will strip out all of the macro code and that particular RealData file will no longer function properly.
3. If you save in .xlsb or .xlsm format, then when you re-open the file, Excel will be looking for antivirus software to scan the encrypted macros.  If it doesn’t find it, you will have to instruct Excel to open this content; some users have reported being unable to do so.

Excel Template Formats

As mentioned above, RealData delivers it programs as template (.xlt) files. When you launch a template, it leaves the original unchanged and presents you with a new workbook file based on that template.  That way you don’t accidentally overwrite the original.

Excel 2007 contains two new template file formats, so now there are three flavors:

1. .xltx called “Excel Template”
2. .xltm called “Excel Macro-Enabled Template”
3. .xlt called “Excel 97-2003 Template”

RealData software is currently distributed in .xlt format.  You could convert this file to .xltm and it should work fine on your computer, assuming that you have anti-virus software capable of scanning encrypted macros.

But why tempt fate for no reason? We recommend that you stick with the .xlt format.

Excel 2007 File Icons

Each of the file formats has its own icon.  As you can see from the image below, these icons are very similar in appearance.  The template file types share a common horizontal yellow bar across the top edge.  You may find these images helpful when you try to recognize different Excel file types by their icons.

Stayed tuned to our blog for more about Excel 2007.

realdata.com

## New investment analysis service — and data form

If you subscribe to our e-newsletter, the RealData Dispatch, then you know that we just launched a new service where we will run a property analysis for you, using your data and our software.

All you do is download a questionnaire, fill it in with the particulars about the property you want to evaluate, and email it or fax it back to us. We’ll run your information through the Standard Edition of “Real Estate Investment Analysis” and send the reports back via email.

You can get more details here.

Even if you don’t plan to use the service right now, I’d like to suggest that you download the form. I think you’ll find it to be a helpful guide for collecting data whenever you need to do an investment analysis, whether you’re working with our software or scribbling on the back of an envelope.

And speaking of our newsletter, just one more comment. If you subscribed but haven’t been receiving it, then it’s probably getting stuck in your spam box. It seems to me that spam filters have been getting more aggressive. I know it’s necessary to fight the growing tide, but I’ve been finding more “real” mail getting swept away with the junk.

I would urge that you go to your email program or service and “whitelist” realdata.com. For example, if you’re using Yahoo mail, go to “Options” and add a filter that tells Yahoo to direct anything from realdata.com to your inbox. With Gmail, you go to “Settings” and do the same thing.

That way, our newsletter will reach you. In addition to providing announcements about our products, upgrades, etc., we use the newsletter to tell you when we have new educational content and when we’ve found an online resource that might be valuable to you as as an investor or developer.

If you aren’t already subscribed, you can do so here.

Frank Gallinelli
realdata.com

## Download sample chapters from my new book, “Mastering Real Estate Investment”

Hello All —

I thought you might like an opportunity to see some sample chapters from my new book, so I’m making two downloads available.

The first section of the book is devoted to 37 key formulas that every real estate investor should understand and know how to use.  One of the most important of these is capitalization rate.
Chapter 10 discusses cap rate and gives you several examples that you can work through.

The second part of the book provides case studies where I take you step by step through different kinds of property investments.  In Chapter 38 I discuss using a single-family house as a rental property.  This download is a seven-page excerpt from the chapter, which continues with a thorough discussion of the single-family rental.