Dissertation on Quicken's IRR
Fred H Smith
time. I can’t think of a better way to do it than to add to the discussion
on Quicken’s calculation of return on investment (what Quicken calls “Avg
Annual Total Return”, which I will call ROI).
Quicken uses the industry-standard IRR calculation to calculate ROI. There
is only one way to calculate IRR, and Quicken does it correctly.
The problem with IRR is that it is very resource intensive. Those of you
with HP12C calculators (the only worthwhile calculator, in my opinion) have
experienced this because it takes a lot longer to calculate i (the interest
rate) than any other financial variable. It’s only since the advent of
Pentium-class computers that programs have been able to provide the IRR
calculation without taking an inordinate amount of time.
Because IRR takes a lot of computer power, several other methods have been
developed to calculate ROI, most notably modified Dietz. However, any other
method is only an approximation. Unfortunately, these approximations, while
useful, add to the investor confusion about what is true ROI.
Here’s how IRR works. Suppose you buy a stock for $10,000 and two years
later it is worth $12,100. Most people would say “great, I’ve made 21% on my
investment.” In talking to one of your friends, she tells you she invested
$10,000 and one year later it was worth $12,000. Since she only made 12% on
her investment, you figure you got the better deal.
Of course you didn’t because your money was invested for two years, and hers
for only one. In order to properly compare the investments, you must
normalize the time period, by calculating the average annual return.
To calculate IRR, you start with the standard future value formula:
FV=PV*(1+i)^n
PV is the amount you invested, FV is what it is currently worth today, and n
is the number of years it has been invested. Plug these numbers into your
financial calculator, and you’ll find that you made a 10% ROI, while your
friend made 12%. So she found the better investment.
Real life, of course, is more complicated than these simple examples. Most
people make a series of investments over time, and very few investments fit
into neat yearly boundaries. But these complications are easy to overcome.
To calculate the time period, simply take the number of days the money has
been invested and divide by 365. (I know there are an average of 365.25 days
per year, but IRR ignores the leap day.)
Handling more than one deposit is what really gets your computer smoking.
Suppose you invest $10,000 one day, $20,000 six months later and in two
years your investment is worth $40,000. What’s your ROI?
Using the standard financial formula, we know that:
40000=10000*(1+i)^2+20000*(1+i)^1.5
What value of i will solve the equation? The only way to figure it out is
trial and error (or what mathematicians call iteration). First, plug in 10%
for i and see what happens. If you had made 10%, your investment would be
worth $35,174. So try 20%. It would have been worth $40,691. So try
something less. Eventually, you find out your ROI is 18.78%. That’s the
number which makes the above formula true.
The challenge with IRR is getting it to converge to the right answer in a
reasonable length of time. Fortunately, Isaac Newton, with some help from a
guy called Raphson, came to the rescue. They developed the Newton-Raphson
method of iteration. I have no idea how it works but it sure is slick. I
have seen it take a series of over 100 cash flows, with an initial estimate
of 10%, and find the correct answer of –7.62% in only seven iterations.
The place where IRR can be misused, is over short periods of time. If you
buy a stock which goes up 5% in one week, IRR will correctly annualize the
return (it’s 1.05^52.14 or 1173%). That’s not the fault of the formula, it’s
the fault of people who think that when they make 5% one week, they can keep
it up for every single week of the ensuing year.
I think I’m tired enough now to go back to bed. Hopefully, I’ve helped
people understand IRR better without boring too many people in the process.
Kindest regards,
Fred.
bill...@my-dejanews.com
Rate of Return) is what is shown on the Investment Performance Report. What
Intuit uses for ROI (rate of return - shown on teh portfolio view) is a
completely different measure.
Also people can be confused by what exactly is part of the cash flow. That can
vary depending on the "sub-total by ?" and what is included or excluded in the
report. If you use the show cash flow option you will see the what is
included.,
Bill
In article <WASV2.13608$K46....@news.rdc1.ab.wave.home.com>,
-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
John Wright
Fred H Smith wrote:
> snip
>
> The problem with IRR is that it is very resource intensive. Those of you
> with HP12C calculators (the only worthwhile calculator, in my opinion) have
> experienced this because it takes a lot longer to calculate i (the interest
> rate) than any other financial variable.
> Kindest regards,
> Fred.
gmdent
John Wright wrote in message <372B37CC...@relaymail.net>...
NePhiFoFum
mean by it taking considerably longer?
Fred H Smith
to calculate i than any other variable. That's because it must iterate to
get i, whereas PV, FV, n, and PMT can be calculated directly.
By the way, since your only comment was on the length of the iteration
process, does that mean that you now agree that Quicken calculates IRR
correctly?
Also, sorry about my off-handed remark about which calculator is best. I
didn't know that it would cause such a stir.
NePhiFoFum wrote in message
<19990501192620...@ng-fi1.aol.com>...
NePhiFoFum
think the prgram should look for more than one IRR by using different
estimates. I would at least like to be told that more than one IRR exists,
rather than be given an incorrect number.
By the way, we had to write a program that utilized the Newton-Rhapson (sp?)
method for my financial policy class. I don't remember anything being said
about this method being anything other than infallible. Are you sure?
(Actually, I can't remember who it was that said that the Newton-Rhapson
produced errors 7 out of 10 times, but it was in this newsgroup.)
John Wright
came with it has some interesting reading material on sleepless nights. But I
still prefer the newer 17B. A lot easier to use. :-)
NePhiFoFum
Fred H Smith
database is approaching 1 Gb and has over 5 million transactions. I've never
seen a case where there's more than one correct IRR.
I have seen cases where Newton-Raphson doesn't converge. But that's in
highly unusual circumstances where you estimate 10% and the actual number is
something like -99%.
With respect to Newton-Raphson being infallible, I do remember an article in
a Lotus 1-2-3 magazine which demonstrated two different IRRs on the same set
of data. I don't remember the details, but it had something to do with what
rate you assumed you got on reinvesting the cash flows.
But when you have an investment account, you don't have the problem of
reinvesting cash flows. Once money is withdrawn, you assume it's spent, not
reinvested. Therefore there should always be one correct IRR for an
investment account.
And it's easy to verify whether Quicken is calculating the correct return.
Once Quicken gives you the rate, all you have to do is a simple spreadsheet
to calculate the future value of each cash flow, then add them up. If the
sum equals the current market value of the account, then the rate is
correct. And no other rate could be correct because it wouldn't add up to
the current market value.
So given the problem we are solving, yes I'm sure Newton-Raphson (and
Quicken) calculate the correct IRR.
Regards,
Fred.
NePhiFoFum wrote in message
<19990502085112...@ng-ch1.aol.com>...
Fred H Smith
NePhiFoFum wrote in message
<19990502114518...@ng-cg1.aol.com>...
Gina Dent
--
Gina M. Dent
BookSmart
St. Louis, MO
Fred H Smith <freds...@yahoo.com> wrote in article
<jA6X2.2869$NC4....@news.rdc1.ab.wave.home.com>...