GovtWork: Discounted Cash Flow

Discounted Cash Flow . . . Compound Interest In Reverse

Whether your cash flows are real or imaginary, as when projecting flows for a new business, discounted cash flow [DCF] will tell you if you're making money or losing it, and at what rate.

Discounted Cash Flow [DCF] is compound interest with time's arrow running backwards.

Each flow is discounted by time, from the flow's date back to the initialization date (today). This is done by iterating a guess-rate into a discount formula stack, one formula for each flow, until the null value is zero [0].

DCF can be done in any spreadsheet for any timebase - and can be more accurate than XIRR.

XIRR is easier to use but less intuitive.

DCF Null test:


          [  Flow   ]
      Sum [---------] = 0
          [ (1+r)^t ]

Typical spreadsheet cell:

flow*(1+rGuess)^-(tFlow/tBase)

Observe sign (+/-) of each flow.

Discounted Cash Flow spreadsheets

Choose the filetype for your spreadsheet:

Excel 5 with XIRR and DCF formula(s)
Lotus 97 with XIRR and DCF formula(s)
Lotus2.2(DOS), DCF formulas are a must w/o XIRR

Files should open okay in any industrial-strenght spreadsheet.

M$ Works 4.5a doesn't support @datevalue or XIRR functions. A later version might. Try it. WK1 and XLS files can be viewed in M$ Works 4.5a by striking 'Okay' (many times) to pass the error prompt when it appears. Offending formulas will load as numbers.

Discounted Cash Flow is the financial analyst's prime tool.

No other tool is more useful, or accurate, considering the limits of analysis.

XIRR

XIRR is usually accurate enough.

When it isn't, there's no limit to the accuracy of hand-iterated DCF except the size of the null test (set by the user), and the user's patience in iterating the guess-rate.

Blood from a stone?

Negative first flow.

The first flow in a DCF calculation is always NEGATIVE, OUT-of-pocket, an INVESTMENT that (hopefully) will return positive future flows. The first flow occurs at time zero. Each following flow (positive or negative) is discounted back to time zero.

Who uses DCF? The Federal Reserve - anyone who needs an accurate method for determining the yield of an investment.

DCF spreadsheet formulas are simple to write and easy to use. The DCF formula is transcendental, it can only be solved by iteration. The XIRR spreadsheet function iterates 100 times (by default). With hand-iterated DCF, the user determines the number of iterations.

What is a ''yield''?

A yield is a perPeriodRate stated on a timebase. In the USA this usually means that the period is the "day" and the yield is stated on a 365-day timebase. XIRR returns a rate stated on a 365-day timebase.

Rates are not stated in a vacuum. A yield's accuracy is only as good as its timebase is stable. It is important to understand that a rate can be quoted on any timebase. If you don't know what the timebase is, then you haven't a clue what the rate means. E. g: a 50-degree temperature: but what is the temperature's "base," Fahrenheit, Celsius, or Kelvin?

Legal rate gimmicks. Really!

Why not IRR?

A rate in the absence of a timebase has no meaning.

IRR, Internal Rate of Return, returns the perPeriodRate (pPR). Unlike XIRR, IRR does not return the periodic rate on a timebase. IRR assumes that each cell represents one period in a series of equal time periods. IRR knows nothing about the timebase. A cell's time interval could be seconds, hours, minutes, days, years, decades or centuries. Converting IRR's periodic rate to a rate on a timebase is something the user must do.

As a practical matter, the use of IRR (instead of XIRR or its equivalent, a hand-iterated DCF calculation) is undesirable when there are many empty cells. When flows are spaced by empty cells, it's easy to get lost in the sheet.

E. g: If flows are more or less 10 months apart and you want IRR to return the daily pPR, an IRR spreadsheet will have data entered about every 300 cells (every 300+/- days). Navigating the sheet becomes a mindboggling chore.

If you can stand the pain of navigating a spreadsheet filled with mostly empty cells, then, when the IRR-calculated pPR (perPeriod Rate) is normalized on a 365-day timebase by time exponential, IRR returns exactly the same answer as XIRR.

Rates in the USA are typically stated on 365-days in a normal year, and sometimes 366 in a leap year though they should not be. Banks have been known to (legally) adjust their rates on savings accounts in a leap year to a 366-day timebase. The effect of the 366-day timebase is to pay less interest on the same stated rate. (5% interest on a 365-day timebase pays more interest than 5% on a 366-day timebase.) When billions of dollars are on deposit, banks have a hard time resisting the temptation to recompute interest on a 366-day timebase during a leap year. Some do, some don't.

One of the more eccentric borrowers is the US Treasury

Treasury computes its borrowing (banks borrow savings) in APR, not APY which the Federal Reserve requires banks to use. Since APR is a nominal rate, that is, a rate in name only, Treasury's use of nominal rates spawned hundreds of broker's calculators to figure out what Treasury's debt offerings really pay - all of which would be unnecessary if Treasury adhered to the same rules as banks. Treasury was mildly amused (see, FAQ) when I suggested that they standardize their rates on APY, that is, on a yield on a 365-day timebase.

Hand-iterating DCF in a spreadsheet

If you're using a spreadsheet that doesn't have an XIRR function, hand-iterated DCF will do the same thing XIRR does with as great or greater precision.

One of the several virtues of hand-iterated DCF is that rates can be computed accurately on any timebase: you control the timebase. You can accurately replicate a rate quoted by a lender by replicating their assumptions (such as one or more leap-days (in leap years) occuring during the term of a loan), by changing the timeBase in the middle of the hand-iterated DCF formula stack. Hand-iterated DCF can extend the precision of XIRR. It can check any rate compounded by time exponential on any timebase. It can restate a rate on any timebase to a rate on any other timebase.

DIY DCF

Do it yourself.

Spreadsheet functions are a convenient replacement for doing the math, not a substitute for knowing what you're doing.

Hint: Unless you've written a computer program or two you may not be aware of zero's place in the scheme of numbers 0-9. Zero comes first.

When was the passing of the millennium? the convention was Y2K - in the year 2000. But the calendar started with year 1. Because there was no year zero, the passing of 2000 years didn't occur until 2001. 2001 started the new millennium.

Computer programs, of which DCF is one, do not run on conventions, they run on precise definitions. "Now," is time "0." A minute from now is time "1." You control the period and timebase: second, minute, hour, day, year, century. Whatever works for you.

It is tedious to hand-iterate to a rate that will satisfy the null equation when large null values are selected. The null test is a test for zero. Beware an excess of precision, the odds that you'll itereate a guess-rate that actually satisfies the null test is zero. Approaching a value near zero is all that's necessary: 10^-6 to 10^-12. The number of iterations required varies with the size of the null test (number of zeros) and the ability of the user to quickly guess the correct rate. With a little practice, and good guesses, the null test can be satisfied in fewer iterations. Watch how the sign and numerical size of the null value changes with each subsequent guess-rate entered. Almost split the difference between your last two guesses, it's faster to err in a known direction.

The virtue of hand-iterated DCF? it does XIRR transparently. You can see how it works.

Analysis:

Checkbook:

Comments:

Misc: