Whether your cash flows are real or imaginary, as when
projecting flows for a new business, discounted cash flow
will tell you if you're making money or losing it, and at
Discounted Cash Flow [DCF] is the financial analyst's prime
tool. No other tool is more useful. Or accurate, considering
the limits of analysis.
The accuracy of XIRR is usually good 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.
Who uses DCF? The Federal Reserve - anyone who needs an
accurate method for determining the yield of an
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.
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.
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,
Legal rate gimmicks. Really!
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.
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
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.
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 (« 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
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.
Do it yourself
Spreadsheet functions are a convenient
replacement for doing the math, not a
substitute for knowing what you're
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
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.