# cash flow and 36 other financial metrics:chapter 3

**how the time value of money will influence your real estate investment decision**

While compound interest may initially seem associated with savings accounts and certificates of deposit, its relevance extends far beyond traditional banking realms into the domain of real estate. In essence, compound interest serves as a tool for comprehending the fluctuation in the value of an investment over time, a crucial understanding for any real estate investor.

The familiar concept of compounding unfolds when depositing money in a bank. The invested sum accrues interest at a specified rate, with this interest compounding at regular intervals. For instance, consider depositing R1,000 at an annual interest rate of 12%, compounded monthly. At the end of the first month, the investment grows by 1% (1/12 of the annual rate), resulting in a new total of R1,010. This 1% is termed the periodic interest rate, denoting the rate at which interest accumulates for each compounding period. The process continues, and after ten more months, the account balance reaches R1,126.83. Notably, compounding enables the earning of interest on previously earned interest, showcasing the financial benefits of frequent compounding.

This example underscores the four variables integral to any compound interest calculation: the initial dollar amount (present value or PV), the periodic interest rate (i or %i), the number of compounding periods (NPER or n), and the final amount after compounding (future value or FV).

Real estate, like bank accounts, can experience growth from a present value to a future value. Moreover, all four variables can be calculated if three are known, allowing for diverse applications. For instance, estimating the time required for a property to appreciate from its purchase price to a targeted resale price involves knowing the present value, future value, and periodic rate to determine the number of periods.

The section proceeds to discuss the use of compound interest and the time value of money in real estate, emphasizing appreciation as a key factor contributing to financial gains in property investments. An illustrative example poses a scenario where a property purchased for R100,000 is expected to appreciate to R200,000 at a rate of 3% per year. The question arises: How many years will it take for this appreciation to occur? The mathematical formulation involves the present value (R100,000), future value (R200,000), and periodic rate (3%), with the objective of calculating the number of periods.

As the material progresses, various methods for calculating the number of periods in a compound interest problem are presented. The classic but complex logarithmic method is outlined, followed by a more practical approach utilizing spreadsheet programs like Microsoft Excel, which offer efficient financial functions for real estate calculations.

Using Excel to Make Real Estate Calculations

We’re going to spend some extra time on what would otherwise be a fairly uncomplicated example. As we do so, you’ll see how to use Excel in a basic way to perform some of the calculations that would otherwise be time-consuming to do by hand. If you’re already familiar with Excel, please be patient; we’ll go through this in small steps for the purpose of this first example.

Let’s start. You want to calculate the number of periods it will take your R100,000 property to grow in value to R200,000 if real estate values rise at 3% per year.

- Open a blank Excel worksheet.
- Pull down the “Formulas” menu and choose “Function.” You’ll see a window that looks like this (Figure 3.1).

- In the left-hand pane, you’ll see function categories. Choose “Financial.” Excel has a financial function called NPER for calculating the number of periods in a lot of different investment scenarios. Select that function in the right-hand pane and click “OK.”

- You will next see a window where you can enter amounts for each of the variables that might be used with this function. Not all of the variables are necessary for our example. You have no payments, so that can be ignored. The “Type” refers to whether payments are made at the beginning or end of each period; the function defaults to the typical choice, so you can ignore that also. You need to enter: a. The “Rate” as 0.03 (the decimal equivalent of 3%) b. The “PV” as -100000 (notice that you must express the PV as a negative number because, from the function’s flow-of-funds investment perspective, PV is money going out and FV is money coming back in; note also that you don’t enter commas within the numbers) c. The FV as 200000

- At the bottom of the window (Figure 3.2), you’ll see the formula result, 23.44977225. This means it will take about 23.45 years for the value of this property to appreciate from R100,000 to R200,000 at an annual growth rate of 3%.

- When you click the “OK” button, this window disappears, and the result displays in whatever cell your cursor was occupying when you began this exercise. That cell now also contains a formula, which was created by the form you filled:
`=NPER(0.03,,-100000,200000)`

If someday you become an Excel “power user,” you can skip the form and just type a formula like this directly into the cell. Until then, you can use a form like the one above to calculate many financial functions.

Method #3 You don’t have to be a mathematician or a computer whiz to succeed as a real estate investor. Being comfortable using a spreadsheet can be a real benefit, but if you don’t want to dig too deeply into the functions and formulas, that’s all right too. A third and painless way to perform these calculations is to use the collection of Excel templates that we’ve written to accompany this book. You can download them from http://www.realdata.com/book. These Excel sheets make it easy not only to perform the calculations but also to visualize the examples.

When you open the Excel file for “Compound Interest,” you’ll find a section where you can calculate the number of periods. If you use this spreadsheet, the task of performing this calculation is really easy. Within the spreadsheet, we’ll adopt a basic color code—blue for the known variables and black for the unknown that you want to compute. Enter the present value, periodic rate, and future value. As soon as you do, presto—the number of periods appears:

What is especially helpful about using this Excel model to make your calculation is that you can quickly try other “What if … ?” propositions. In this case, your barber, who has correctly called the last eight Super Bowls, advises you that an appreciation rate of 3.75% would be more realistic. Change the periodic rate, and the worksheet recalculates instantly:

Now you’re looking at a little less than 19 years for your property to double in value.

You may want to consider this problem from a different perspective. You’re not getting any younger, so you decide that you can wait nine years at most to double your money. What growth rate would be necessary to accomplish that goal?

Once again, you could seek your answer using the following formula: `Periodic interest = (FV / PV)(1/N) – 1`

But as before, this is a difficult and time-consuming process.

You could also build an Excel formula to do the job, using the financial functions window shown in Figure 3.1. If you do so, you’ll run through these inputs for RATE—nper, pmt, pv, fv, type, guess—and get a formula like this: `=RATE(9,,-100000,200000,,0.1)`

The easiest way is simply to stay with the same Excel worksheet and use this handy section: You still have a PV of R100,000 and an FV of R200,000. This time, instead of entering the rate and getting back the number of years as your answer, you enter the number of years, and the model tells you what rate is necessary to achieve your goal. You’ll need an appreciation rate of about 8% to double the value of this property in nine years. Now suppose that you want to speculate on what the value of this property might have been three years ago. Assume that property values grew at 4% annually during that period. Do you need a template for “past value”? No, as Figure 3.3 shows, you just have to orient yourself correctly on the economic timeline.

To figure out what this property must have been worth three years ago, you have to imagine yourself back then looking toward today as the future. Once again, you find yourself in a situation where you know three of the four variables and want to calculate the fourth. You know that the periodic rate is 4% per year, the time is three years, and the FV is R100,000 (Figure 3.4).

You want to calculate the PV. Remember, you’re standing back in time three years ago (that’s the “Present”) looking forward to today (the “Future”). By now you know the drill. The formula is ugly—Present Value = FV / [(1 + i) N]—so forget it. You can use Excel’s financial functions menu to fill out a form for the PV function with these entries: rate, nper, pmt, fv, type. You get back a formula that says `=PV(0.04,3,,-100000)`

. That’s all right, but easiest of all is to return to the Excel spreadsheet and use the mini-template that we’ve created for you. This basic template for computing the PV will do the trick. You enter the data, and the sheet calculates as follows: If your assumptions are correct, you can reasonably estimate that, all other factors being equal, the property was worth about R89,000 three years ago. What Every Investor Needs to Know About Cash Flow: Calculating the Present Value You’ll recall that the first item we talked about in the Introduction, “The Four Ways to Make Money in Real Estate,” was cash flow. Invariably, you are pleased when more cash comes in than goes out. However, the cash flow from an income-property investment does not all come suddenly in a single rush. You would like to have a positive cash flow each year that you operate the property, but that cash flow will almost certainly be greater some years than others. When you eventually sell the property, you’ll consider the proceeds to be part of your overall cash flow for the sale year. The time value of money plays a critical role when you consider just how valuable your property’s cash flows really are. Timing is everything. Cash you receive sooner is more valuable than cash you receive later because the sooner you have it, the sooner you can put it to work earning more cash. When you look at the cash flows from a real estate investment, you want to calculate the PV of those cash flows. Essentially, this calculation presents the same situation you saw in the previous compound interest example, except you are viewing it from the other end of the telescope. Instead of watching the smaller value get larger, you watch the larger value get smaller. When you find the PV of a future cash flow, you are finding what it is worth right now in today’s rands. When you find the PV of a series of cash flows, you are finding the present worth of each of them and then totaling those individual PVs. The sum of the PVs of each individual future cash flow is the PV of the whole stream of future income. Let’s start by looking at just one cash flow that occurs in the future. In Figure 3.5, you can see that if you have a single R100,000 cash flow that occurs five years in the future, its value today, discounted at 10% per year, is R62,092.13. Following the figure from right to left, you can track how the R100,000 steps down each year until it reaches the present.

Why is this procedure so important to you as a real estate investor? It may be fine to know that you can take R100,000 out of your venture five years from now, but as an investor, you recognize that money has a time value. A rand to be received in five years has less value than one you can have today. The rand will typically lose buying power each year, so you can’t expect in five years to purchase goods and services with a current value of R100,000. Perhaps more importantly, if you don’t have the cash in hand today, you can’t put it to work for you. For this reason, even individuals who may not think of themselves as investors will certainly put any significant amount of cash received today into a certificate of deposit, money market fund, hot Internet stock, or some other form of investment. Similarly, professional investors look at their return as the subject of reinvestment. The longer they must wait for it, the less it can earn. Discounting is a way of measuring the loss of value caused by the deferral of a return. In the typical real estate venture, you expect some kind of return each year that you operate the property, and you expect an additional benefit from the eventual sale. Each of these returns will have its own timing and therefore will require its own discounting. Let’s look at a specific example. Using a R90,000 down payment, you plan to purchase a property that yields the following cash flows each year. These amounts represent what remains after paying all operating expenses and debt requirements: You expect to sell the property at the end of year 5, realizing cash proceeds of R100,000. Your overall cash flow now looks like the following, where the final year includes cash from both the operation and the resale of the property: To determine the PV of all the benefits of this proposed investment, you calculate the PV of each cash flow, including the sale proceeds, and then sum these amounts. You can consider the investment profitable if the PV of the benefits exceeds their cost (the down payment). Perhaps you’re confident that you could earn 10% if you invested your money in a similar property presenting a similar risk. Why? Because that’s what other investors in your area are earning with those similar properties. For that reason, you feel that cash received in the future from this property must be discounted in value at no less than 10%. Rule of Thumb: When you try to decide whether it makes sense to buy a particular income property at a given price

FIGURE 3.6 Discounting five future cash flows. Your first-year cash flow of R9,000 is discounted back one year at 10%; its PV is R8,182.82. Your second-year cash flow of R11,000 is discounted back in two steps, 10% each year, and its PV equals R9,090.91. This pattern continues until your final cash flow, which represents both the cash from operating the property and the cash from its resale in the fifth year. You must discount the R115,000 for five years at 10% per year, giving that cash flow a worth of R71,405.95 today. If you sum the PVs of each of these cash flows, you get R105,856.47. Assuming your discount rate of 10% is reasonable, you can think of this sum as the value today of this property’s expected future benefits. What does this figure tell you about the merits of this property as an investment? Don’t expect this calculation or any other single measure to give you a simple and definitive “good deal/bad deal” answer. You need to look at each measurement as part of a larger picture and apply your judgment to determine if the deal makes sense to you. Keep in mind that the PV represents the present worth of all future benefits. If that’s so, then you should compare the PV to what it cost you, namely the cash required to purchase the property. You find that the value of what you expect to receive (R105,856.47) is greater than that of what you had to invest (R90,000) to get it. As a successful developer once said to me, you can’t lose money making a profit. The difference between these two is called the net present value (NPV). In this case, the NPV is R15,856.47. Rule of Thumb: Whenever the NPV is greater than zero, it means that the discounted value of the future cash flows is greater than your original cash investment. Translation: Your real rate of return is actually higher than the discount rate you used. How do you perform this kind of discounted cash flow analysis? Once again, there’s the hard way and the easy way. The hard way is to begin with any of the methods discussed previously for calculating PV. Then do the following:

- Use that method to figure out the PV of each of the cash flows.
- Add those PVs up to get the PV of all the cash flows combined.
- Subtract the initial investment to get the NPV. The easy way is to use a spreadsheet like the one called “Net Present Value,” available for download. The model uses a built-in Excel function called @NPV. This function allows you to specify a periodic discount rate and a range of spreadsheet locations for the cash flows. The first location is discounted once at the periodic rate, the second location is discounted twice, and so on. Because your initial investment occurs on day 1 (and not at the end of the first period), you don’t want to discount that number. So you use the NPV function to discount just the cash flows and then subtract the initial investment as you did in the preceding example. After you enter the data as shown, you find that the answer is the same as with your earlier computation. The template’s “What if … ?” capabilities are now at your disposal. For example, suppose you discover that 10% is not a suitable discount rate to use for properties in your area and you need to refigure these cash flows at 12%. By simply changing the discount rate to 12%, you can determine that your income stream has an NPV of only R8,188 if discounted at that rate. Evaluating Leases You can use your discounting skills for yet another type of analysis. Just as you may be interested in the PV of the income stream from an entire property, you may also be interested in the PV of the income from an individual lease. Why would you want to determine the value of a lease? First of all, a lease is an asset, something of value, much like a promissory note. It can be useful for you to know what it’s worth because you may want at some time to sell your rights under a lease. In order to raise immediate cash, for example, you might try to sell the right to collect the payments due under a lease you currently own. Perhaps more common is the situation where you are negotiating terms with a prospective tenant. If you’re dealing with commercial property, there may be many matters to settle. You need to agree not only on the initial rental amount and the length of the lease but also on the rate and timing of rent increases and on the amount and timing of other payments such as the tenant’s contribution to real estate taxes, utilities, or insurance. The lease proposals you make or receive could involve a number of possible combinations of these variables. The value in finding the present worth of a lease really lies in the ability it gives you to compare different lease proposals. Keep in mind the theme of this chapter: the time value of money. As the owner of a property, you may be able to seal the deal with a tenant by agreeing to a lease that provides for a small rent increase each year rather than just one, much larger increase that kicks in at a later time. A quick discounted cash flow analysis of the lease payments may be able to show you that the concession that convinces the tenant to sign on the dotted line really costs you little or nothing in discounted rands. How does this calculation differ from what you’ve already done? First, it’s common practice when evaluating a property to annualize the cash flows. By that, we mean we usually look at income and expenses as though they were received and disbursed in lump sums at the end of each year. You know, however, that lease payments are typically made monthly and in advance. They’re defined by contract; unlike property cash flows, their timing and amount are reasonably predictable. When you evaluate a lease, you can be a bit more precise if you look at a lease’s income stream in terms of monthly amounts; and doing so is not impractical. One decision you may face with a small commercial property concerns terms for a tenant who has just started in business. Do you begin at a low rent to give the tenant a chance to get established and then escalate quickly to make up for lost time? Or do you start off high, assuming that you’d better collect as much as you can while the tenant is still solvent, and not worry about a long term that may never occur? You can use a basic Excel model (see http://www.realdata.com/book for “Value of a Lease”) to analyze such a situation. The model treats each monthly payment as a periodic cash flow. Since lease payments are made at the beginning rather than at the end of each period, it leaves the first payment undiscounted and applies Excel’s NPV function to the remaining payments. It also divides the discount rate by 12 so that it’s applying a monthly rate to the monthly payments. Suppose you have a prospective tenant for a retail space in your strip shopping center. Your advertised price is R2,000 per month on a five-year lease beginning in April. The tenant is responsible for a share of the real estate taxes, payable in July of each year. This year’s share is R1,000, and your experience has been that taxes in this location increase

As illustrated, the PV of the lease proposed by the tenant is R91,868. Adjusting the monthly rental amount to R2,000 for each year, as originally proposed, entails a new consideration for the tenant’s contribution to property taxes. Remember that the tenant is also obligated to pay a share of the property taxes, referred to as an “expense pass-through.” Did you accurately calculate the pass-through for property taxes? Year 1 is R1,000, and each subsequent year increases by 10%: R1,100 for year 2, then R1,210, R1,331, and R1,464. Taxes are payable in July, the fourth month of the lease year (given the lease starts in April). In month 4, you need to modify the amount to include combined rent and taxes.

Comparing the PV of your original proposal with that of the tenant, it’s evident that your original proposal has a considerably higher value. Thus, you must decide whether you are willing to accept a decrease in the expected cash flow from this lease. As a landlord, you need to weigh whether it’s better to accept the offer, make a counteroffer, or wait for a potentially better proposition.

In this scenario, you can quickly evaluate various counterproposals. Consider five different rent options and utilize the model to assess each scenario. Ensure to incorporate the property tax pass-through into month 4 of each proposal. The results show that the PVs of the first and fifth alternatives are nearly identical to your original proposed lease. The second and fourth options have higher values, while the third has a lower value.

This analysis highlights the significant benefit of crunching numbers in a real estate deal. Each of these five options might be more appealing to your start-up tenant compared to your original proposal, as each offers a significant concession in the first year. Four of the five options provide a break in the second year as well. Only option 3 would cost you any real money; options 2 and 4 would actually exceed your initial expectations. A brief exploration of PV calculations has provided you with four alternative ways to make this deal work.

You can continue to adjust these rent amounts and explore other variables, searching for additional ways to make the deal feasible. Perhaps you might consider breaking the tax payment into two installments or foregoing it altogether in the first year. Alternatively, you could experiment with different combinations of rental amounts. Whatever you choose, the ease with which you can explore and comprehend your options will significantly contribute to making well-informed decisions.

**Mortgage Calculations**

One of life’s few certainties, in addition to death and taxes, is that you should buy real estate, whenever possible, with other people’s money. The other people may be banks, insurance companies, private lenders, or the property sellers themselves. Just avoid lenders who disburse funds from the trunks of their cars. The money is loaned most often in the form of a mortgage—an interest-bearing note secured by the property and repaid typically in equal monthly installments.

Because mortgage financing is an integral part of real estate investing, there are probably no calculations performed more often than those that pertain to such financing. It’s a rare transaction that doesn’t cause you to ask, “What will it take to service the debt? How much interest will I pay? How much will I owe when I sell?”

The typical amortized mortgage is structured as something called an ordinary annuity. That’s a series of regular, equal amounts disbursed at the end of each payment period. Four variables are involved in any mortgage calculation: the principal amount (or PV), the periodic interest rate, the number of payment periods, and the payment amount.

You can calculate the monthly payment using a mathematical formula that is about a yard and a half long. Let’s take the liberty of assuming that you’d rather go directly to the simpler methods.

First, turn to the Appendix in the back of the book where you will find the first few pages of a table called “Monthly Mortgage Payment per R1.” The rest of the table can be found at http://www.realdata.com/book. The chart runs from 1% to 14.375% and from 1 to 30 years in 1-year increments. The following is a segment of that table.

Say that you are applying for a mortgage of R200,000 for 30 years at 5 5/8% (that’s 5.625% for the fractionally challenged). Find the column for the rate 5.625 and follow it down to the row for 30 years. There you see 0.00575656. This is the monthly payment amount for R1 at 5.625% for 30 years. Multiply the total rands amount of the mortgage by this factor to calculate the monthly payment: R200,000 × 0.00575656 = R1,151.31.

**Rule of Thumb:** Sometimes you need to make a quick estimate without the benefit of a calculator. Look up the factor and move the decimal point four places to the right so that you are dealing with mortgages in the tens of thousands, or move it five places for hundreds of thousands. In the preceding example, the factor becomes 575.656 per 100,000. For two “hundreds of thousands,” double the 575, and you have a quick estimate of R1,150.

Try another example. You want to borrow R127,650 at 6% for 25 years. What is the monthly payment? This time we’ll let you find the right page in the table by yourself. Come back when you’re ready.

You should have discovered that 6% for 25 years requires a monthly payment of 0.00644301 per R1. R127,650 × 0.00644301 = R822.45.

Once again, Excel has a function for computing each of the variables, so you’ll find that a spreadsheet template is the fastest and easiest way to perform mortgage calculations. Just as you saw in the compound interest calculations earlier, if you know three of the mortgage variables, you can calculate the fourth.

Let’s use an Excel model (see http://www.realdata.com/book for “4 Annuity Functions”) to figure a payment amount.

As you can see, you have the four variables: N, the number of payment periods; %i, the interest rate per period; Pmt, the payment per period; and PV, the present value.

Suppose that you want to find the monthly payment amount needed to amortize a R100,000 loan at 9% per year for 25 years. If you want your answer—the payment—to be the correct monthly amount, then it’s necessary for you to express the number of payment periods and the periodic interest rate as monthly amounts also. Similarly, if you were seeking the quarterly payment, you would have to express N as the number of quarters and %i as the quarterly rate.

To solve the problem, you enter 300 under N. This is the number of payments you would make in 25 years. (You can also type =25*12, and Excel will act as a calculator, placing the product of these two numbers in the active cell.) If your annual interest rate is 9%, you must divide 9% by 12 for the monthly rate. To do so, you can enter =0.09/12, or .0075 under %i. The amount of the loan