Financing

Midpoint vs. End-Point Discounting

Understanding Both Techniques Will Strengthen Your Financial Analyses.

Sophisticated spreadsheets and popular financial analysis software programs often use a midpoint discounting technique to compute internal rate of return and net present value. Investors, brokers, and financial analysts comparing alternative investments need to understand this discounting technique to better comprehend the measures of return on investment and to make sense of analyses.

Imagine that you are analyzing an investment opportunity for your most important client, and the preliminary financial analysis is provided in either a planEASe or Argus software format. Is the analysis valid simply because the information is neat and orderly and the columns are all precisely arranged? Certainly not. To best advise their clients, commercial real estate professionals always should look into the critical assumptions that drive an analysis, checking for inconsistencies and, of course, mathematical errors.

Finding the IRR is an iterative algorithmic process; the analyst, the calculator, or the computer program must guess and interpolate until the equation is satisfied. Financial calculators such as the HP 10b and HP 12c will iterate for the correct answer. On the other hand, software such as planEASe, Argus, and Microsoft Excel contain sophisticated financial functions that can make computation even faster with high-speed personal computers. Every real estate investment, no matter how intricate, boils down to a series of cash flows, which can be illustrated by the basic cash flow T-bar table. After all, it is the amount and timing of the anticipated cash flows that matter. However, sophisticated spreadsheet models, as well as popular application software, may not assume traditional beginning-of-period or end-of-period cash flows like calculators do. Commercial real estate professionals need to be aware of the differing results when computing IRR and NPV with a financial calculator and the more-sophisticated software packages available today.

Measuring Cash Flow Timing Traditional financial analysis using financial calculators makes the assumption that all cash flows come in at regular intervals at the beginning or end of each period. To facilitate computation, end-of-year cash flows most often are used for analyzing long-term investments.

In fact, cash flows typically are monthly occurrences with a reversion based on sale proceeds at the end of the term. Financial calculators provide useful estimates of IRR and NPV for comparing alternative investments, but entering monthly cash flows for 120 periods in a 10-year analysis is unruly. Spreadsheet applications handle actual period cash flows much more easily, but the increased precision of using the actual duration will affect the calculated IRR. It is important always to use the same method of calculating IRR when comparing two or more different cash flow projections. Using different methods results in inconsistent comparisons that could lead to costly mistakes.

The following example demonstrates how using different methods of measuring the timing of cash flows can affect IRR.

First, use an HP 10b or HP 12c financial calculator set to annual periods. Set P/YR equal to 1 and load the cash flows shown in Table 1.

While it is possible to change P/YR to 12 to enter monthly cash flows, this process quickly becomes tedious. It is not practical to use a calculator to fine-tune a projected IRR estimate.

By changing the calculator to "begin" mode, thus assuming cash flows at the beginning of the period, the IRR logically should increase. However, the IRR calculation still is 11.44 percent because the calculator forces consistent spacing between cash flows, with cash flow zero exactly one period earlier. Changing P/YR to 12 to simulate monthly cash flows results in 137.258 percent, which divided by 12 is 11.44 percent. The bottom line is that financial calculators do not handle monthly discounting or uneven periods correctly when calculating the IRR. Many programmable calculators are not subject to this constraint.

In comparison to financial calculators, software programs such as Argus or planEASe provide a more precise investment analysis. These applications are not limited to end-of-period cash flows and can calculate uneven spacing between cash flows. PlanEASe defaults to a discounting method in which intermediate cash flows are scheduled to come in at the middle of the period and the reversion is scheduled at the end of the holding period. Argus allows the analyst to schedule cash flows either at the end or middle and also allows the option to place reversion at the end of the holding period.

Cash flows in commercial real estate investments typically come in monthly intervals; the midpoint discounting technique simulates monthly flows by scheduling the intermediate flows at the middle of the period. By scheduling annual cash flows to occur at the midpoint of the year, exactly one-half of the cash flow each year is too early and one-half is too late, approximating a true monthly schedule. These cash flows would look something like Table 2.

The resulting IRR as calculated by planEASe or Argus using the midpoint convention will be 11.9 percent. This discounting technique results in a rate of return notably higher than traditional end-point discounting. This sounds reasonable, but can it be proven?

Determining Cash Flows The IRR is the rate of return on every dollar invested for the duration of each dollar`s stay in the investment. In other words, when cash flows are discounted at the IRR, the present value of all future cash flows will equal CF0 and the NPV will equal zero.

Calculating the present value of future cash flows proves this. First consider the end-point convention, in which the cash flows are assumed at the end of the period and the IRR is 11.44 percent. The sum of all discounted cash flows at the IRR should equal cash flow zero. As shown in Table 3, present value of all future cash flows equals the cost of the cash flows, or CF0, and NPV is zero.

Now consider the midpoint convention. The key to understanding the midpoint IRR rests in acknowledging the duration of each dollar`s stay in the investment. Table 4 uses the 11.9 percent midpoint IRR as the discount rate.

The midpoint IRR is a more accurate reflection of the actual cash flows. Discounting at midpoint will take into consideration that cash flows do not come at the end of each period but that the reversion does.

Calculating Midpoint IRR Microsoft Excel contains many powerful calculation tools similar to those embedded in planEASe and Argus that perform the IRR, NPV, and modified IRR functions, as well as the XIRR and XNPV functions. The XIRR and XNPV functions allow the user to specify exactly when cash inflows and outflows occur by relating each cash flow to a specific date. The XIRR and XNPV functions are available only when the analysis tool pack is added. (See the Excel help menu.)

The syntax for the XIRR and XNPV functions is:
XIRR(values, dates, guess) XNPV(values, dates, guess)

If the guess is omitted, it is assumed to be 10 percent. Table 5 illustrates an example of midpoint and end-point discounting using the XIRR and XNPV functions, planEASe, and Argus. Notice the very slight error in NPV given by XNPV of $0.01. This is a result of the 365-day year assumption embedded in Excel.

Notice the perfect agreement between NPV and XNPV in Table 6; however, the somewhat illogical format of the dates compensates for the 365-day year assumption.

Making Better Decisions When an unwitting analyst compares two competing sets of cash flows, one calculated end-point and the other midpoint, with seemingly the same IRR, a poor decision almost always results.

For example, consider the two cash flow examples in Table 7 for a client with $1 million to invest. Would the client prefer a 15 percent IRR or a 15.02 percent IRR? Does the comparison really split hairs that closely? The NPV of example A is $72,055, while the NPV of example B is only $48,037 if a 12 percent discount rate and the end-point convention is used for both sets of cash flows.

However, since example A uses end-point and example B uses midpoint convention, an "apples to oranges" compar- ison can lead to a costly mistake.

Some popular financial analysis software defaults to the midpoint discounting technique, which always results in a greater IRR and NPV when compared to traditional end-point discounting. PlanEASe defaults to the midpoint discounting method for cash flows and end-point on sale. Argus defaults to end-point cash flows on sale but easily can be changed to midpoint.

Understanding the midpoint technique allows analysts to make more precise comparisons across financial analysis platforms, which results in better investment decisions. Today`s commercial real estate professionals need to understand the subtleties in the timing of the cash flows now more than ever to provide the expert advice clients deserve. 

Jeff L. Engelstad, CCIM, and Robert Stone, CCIM

Jeff L. Engelstad, CCIM, is an assistant professor of real estate and construction management at the University of Denver Burns School of Real Estate and Construction Management, which offers an executive master`s program in real estate and construction management. Contact him at (303) 871-4741 or jengelst@du.edu. Robert Stone, CCIM, is senior vice president and division head for the Senior Housing and Multifamily Division of Henry S. Miller Commercial in Dallas. Contact him at (972) 419-4000 or rstone@ccim.net.

Recommended

Financing Green Initiatives

Summer 2021

Randy Eckers, chair of the real estate finance practice for Akerman LLC, talks about the Commercial Property Assessed Clean Energy (C-PACE) program, which allows building owners to borrow money to complete projects with repayment made through an assessment on their property tax bill.

Read More

2019: The Year of FASB

July.August.18

 

Read More

Mixed Signals

May.June.17

Commercial real estate financing is still relatively cheap and fairly accessible, but there is room for volatility. With a Trump administration, the Brexit vote, and an increasing 10-year Treasury Note rate, a lot can change in the commercial real estate lending in the coming months.

Read More

Breaking Down Defeasance

Jul.Aug.16

CMBS borrowers should understand the prepayment process. During the last cycle, commercial mortgage backed securities lending gained in popularity, culminating in a frantic pace of origination between 2005 and 2008. During that period, many borrowers went through loan defeasance. But for a fairly long stretc

Read More