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
what rate

XIRR
The accuracy of XIRR is usually good enough
When it isn't, there's no limit to the accuracy of
handiterated DCF except the size of the null test
(set by the user), and the user's patience in
iterating the guessrate.




Discounted Cash Flow [DCF] is the financial analyst's prime
tool. No other tool is more useful. Or accurate, considering
the limits of analysis.
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 handiterated 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, OUTofpocket, 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 365day timebase. XIRR returns a rate
stated on a 365day 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 50degree temperature: but
what is the temperature's "base"; Fahrenheit,
Celsius, 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 handiterated 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 IRRcalculated pPR (perPeriod Rate) is
normalized on a 365day timebase by time
exponential, IRR returns exactly the same
answer as XIRR.




Rates in the USA are typically stated on 365days 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 366day
timebase. The effect of the 366day timebase is to pay less
interest on the same stated rate. (5% interest on a 365day
timebase pays more interest than 5% on a 366day timebase.)
When billions of dollars are on deposit, banks have a hard
time resisting the temptation to recompute interest on a
366day 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 (« FAQ ») when
I suggested that they standardize their rates on APY, that
is, on a yield on a 365day timebase.
Handiterating DCF in a spreadsheet
If you're using a spreadsheet that doesn't have an XIRR
function, handiterated DCF will do the same thing XIRR
does with as great or greater precision.
One of the several virtues of handiterated 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 leapdays (in leap years) occuring during the
term of a loan), by changing the timeBase in the middle of
the handiterated DCF formula stack. Handiterated 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 09. 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 handiterate 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 guessrate 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 guessrate entered. Almost split the
difference between your last two guesses, it's faster
to err in a known direction.
The virtue of handiterated DCF? it does XIRR transparently. You
can see how it works.

