No. of Recommendations: 0

Has anyone else noticed that Quicken 98 calculates the IRR (internal rate of return, also known as average annual total return) incorrectly in its "Investment Performance report"?

You can verify this with the following example:

1/1/96 buy 1,000 shares of something at $1 per share

7/2/96 sell all 1,000 shares for $2 per share

Do the Investment Performance report, and use "customize" to set "subtotal by" to subtotal by security. Use a date range of 1/1/96 to 1/1/97 (or anything 1/1/96 or earlier to 7/2/96 or later).

If you have an annually compounded investement that yields 200%, and you invest $1,000 on 1/1 (as in the example above) and close out the investment half a year later (7/2 in the example), you would receive $2,000 back (again, like in the example above). This is because 200% interest on $1000 is $2000, but since it was only half a year you'd only get half that, or $1000. $1000 interest plus the $1000 original investment is $2000. There's no compounding of interest since I've specified annual compounding and it's been less than a year.

Quicken's help text says they use annual compounding, but the answer in the report is roughly 300%, not the correct answer of 200%. 300% would be the correct answer if the interest was compounded twice a year.

I've called Intuit and they admit the report is wrong, but call it a "program limitation". The reason the results are wrong is that the formula they use is improperly derived. Incidentally, Excel's XIRR function uses the same formula and gives the same incorrect answer. Microsoft admits the answer is wrong when you use periods less than a year. In fact, Quicken and Excel are wrong when you use periods that aren't exact multiples of a year.

I noticed that on Intuit's support web page there's the question "Why is the annual return on my Investment Performance report too high?" but their suggestion is to use a report period of one year (not because the answers are wrong, but because over a year they are easier to understand). In my example above, changing the report period makes no difference as long as it includes both the buy and sell dates.

Comments, anyone?

No. of Recommendations: 0

<steps up to microphone>

TAP TAP TAP, IS THIS THING ON; SHREEEEEEEAAAAAAK!

<covers ears and turns off mike> sorry.

Errr…not real good with the high finance terms, but let me take a crack at the numbers:

I think I see a couple of problems.

>>If you have an annually compounded investement that yields 200%, and you

invest $1,000 on 1/1 (as in the example above) and close out the investment

half a year later (7/2 in the example)<<

it's not really compounded annually if you take it out after only 6 months

it's just plain not compounded at all.

since you got interest at 6 months one has to assume no compounding, or 6 month compounding.

>>This is because 200% interest on $1000

is $2000, but since it was only half a year you'd only get half that, or $1000<<

whoa nelly, fallacy alert.

let's look at this a different way:

I just played around with excel's xirr function.

it returns "annualized gain" (my term).

In your case, the absolute or total (or whatever) return is 200%.

the absolute increase or gain is 100%.

I believe the key word here is ANNUALIZED

annualizing gives you the gain per year

(whether investing less or more than a year)

In this case, you double in 6 months.

If you'd invested for one year, your account would have doubled again to $4,000

you've increased by $3,000 or 300% excel returns 3.somethingsmall

(would that we all had such problems)

I don't think there's anything wrong with the function, just the folks at their help line <g>

try a few examples on excel $1,000 into $4,000 over one year XIRR=3.0

$1,000 into $2,000 over one year XIRR=1.0

<stands on head>

or looking at it another way

suppose your annualized increase is 200%

invest $1,000 over 1 year and your total is $3,000

what will have after only 6 months investing at that rate? (now we have to assume 6 month compounding)

without doing all the math <waves hands around frantically> you should get a total of...

$1,000 * (SquareRoot(3)) = about $1732.

try the numbers in excel. i think the fool school discusses this specifically in a section on compound growth. check out the home page

am i making sense or missing the boat?

yFool

No. of Recommendations: 0

yFool, I don't think you're quite understanding my point; I probably wasn't as clear as I should have been.

I'm saying, assume I find a bank somewhere that pays annually compounded interest on say, a savings account. (I know that's improbable, but Quicken picked annual compounding, not me.) And, their annual interest rate is 200% (I know that's improbable too, but it makes the error so much clearer).

OK, you've got this savings account with 200% interest compounded annually. You put $1000 in on January first. After half a year, you close the account out. I agree that there has been no compounding, because you didn't wait the whole year out. So, does the bank give you back your $1000 and say "have a nice day"? Not any bank I know of. They would pay you the accrued interest, which would be $1000 x 200% x 1/2year, or $1000 (plus your original investment of $1000, for a total of $2000).

So, we know that with a 200% annually compounded savings account, you put in $1000 and half a year later you get out $2000 (if you close the account). You enter these results in Quicken (using "buy" and "sell" an imaginary security), and ask it what the annually compounded interest rate was. Quicken's help text specifically says it computes the interest rate that a bank would pay in order to give the same return as your investment, which we know is 200%. But Quicken's result is 300% (give or take a little).

My assertion is that since the help text says it computes the interest rate you'd have to make from a bank to match the results of your investment, the 300% answer is wrong.

It's a little harder to judge Excel's XIRR function, since they never say exactly what it's supposed to compute anyway. But I think I could demonstrate that XIRR gives conflicting answers, given enough text space (Microsoft believed me, anyway, and it took a heck of a lot of text).

As to your comment that if $1000 went to $2000 in half a year, the $2000 would go to $4000 by the end of the year, no, that's not true. The account is going to be compounded annually (that is, by the end of the year, still no compounding will have taken place). At mid-year, there's $1000 in principal and $1000 in accrued (but not compounded) interest; in the second half of the year additional interest will accrue on the $1000 principal only, not on the principal plus accrued interest.

No. of Recommendations: 0

Radish,

Look at it this way, that gain at 6 months isn't realized, as soon as you say that it is then it calculates that away.

Looking at that same account,

1000, at 200% annual compounding.

It would be my vested interest at the 6 month mark to pull out, take the gain, reinvest in in that account, so for the next 6 months I have the NOW compounded 1000 interest earning me money so at the end of the year I would have a 300% return instead of a 200% return.

The XIRR function and the function in Quicken are not flawed they are annualizing a return. Much the same as alot of Mutual funds do, as more data and info is added the more accurate the calculation becomes.

By chooseing the 6 month period you are in effect forcing the 6 month compounding to occur because at that point in time you have a 1000 dollar gain, annualize that again for another 6 months and you get the 3000 gain, do the Xirr calculation for that period of time it will show 200%

I do disagree that if the period is off from a year that it will be inaccurate though, for periods longer than a year the Xirr function has more data points it gets more accurate over time.

Douglas

No. of Recommendations: 0

Oops on the xirr calculation for a year didn't sound right after I hit send.

Assuming that you didn't do the Foolish thing at 6 months and left the money in there for the year the Xirr calcuation would show the 200%.

When the period was chosen for 6 months it compounded the gain at that point and calculated through.

No. of Recommendations: 0

eldeon,

I agree that in real life if you had money in a savings account that compounded interest annually, you'd want to close the account and open a new one frequently so as to effectively compound the interest. That's why banks don't have savings accounts with annually compounded interest!

That doesn't change the fact that Quicken's answer is incorrect.

I do have examples where Quicken's answer is incorrect but the data is more in line with a real-world investment, but they are very complicated and involve multiple investments over non-periodic intervals. The nice thing about the example I gave is that it is so simple and shows the error so clearly.

No. of Recommendations: 0

Sorry…

as I said in the initial post, I'm not great with the financial terms, just taking a crack at the numbers. Do "banks" really do this? Does there exist a bank that will, figuratively speaking, pay out 100% gain (or half of the annual yield) after 6 months when the annual yield is 200%? This doesn't make much sense to me. As mentioned in a post above, one could then reopen an account, invest the $2,000 total, and again close at 6 months, boosting your yield to 300%. If I were running the bank I would not give a 100% gain for 6 months. As mentioned in the latter half of my reply, I would return $1732 or SquareRoot(1+annual gain) rather than $2,000. That way if some Fool like me tried to open another account, I couldn't take advantage of me. Is there a real life situation where your example applies or is someone just giving a simplified periodic statement? <salivates at opportunity to chew on another example>

Y

(quicken apologist)

No. of Recommendations: 0

yFool,

As I said in an earlier reply, I don't know of any banks that use annual compounding on savings accounts. I just have to use annual compounding because that's what Quicken uses (at least, that's what their help text says they use). Also, banks don't pay 200% interest, the large number just serves to highlight the problem in Quicken.

But in a more general sense, the answer to your question is yes. Typically, banks use continuous, daily, or monthly compounding on savings accounts and similar accounts. When the compounding is monthly, they generally say that "interest is accrued daily and compounded monthly". That means if you opened an account, then closed is half a month later, you would still have earned 15 days (or so) of accrued interest. If the interest rate was, say, 6%, then you'd get about 6%/24 times your investment as interest.

But to look further at eldoen's comments, let's say you do take the money out after six months and then reopen a new account. In Quicken, you could enter:

1/1/96 buy 1 share at $1000

7/2/96 sell 1 share at $2000

7/2/96 buy 1 share at $2000

1/1/97 sell 1 share at $4000

Remember, we're using a bank account with 200% annually compounded interest. So, the equivalent interest that a bank would pay is the same as the interest the bank DID pay, namely 200%. Quicken still gives the 300% answer, though. Yes, 300% is the "effective yield". But it is NOT the interest a bank would pay, which is what the help text says you get.

No. of Recommendations: 0

I've received an email from Intuit which essentially says that the help text is incorrect in stating that the results are the same as for an annually compounded bank account. Actually, the results are from the standard IRR formula, and thus have nothing to do with annually compounded bank accounts.

I'll have to see if I can dig up some info on where this "standard IRR" formula comes from. I checked the local library, but there was little mention of IRR in the accounting and investing books I found (usually just a cursory definition). It's clear to me that it has been derived from the formula for annually compounded interest, but I still think the way people are using it is not correct when short time periods are involved. I think it should really be derived from the formula for continuously compounded interest, or, if one is using days as one's smallest time unit, from daily compounded interest.

Thanks to everyone who responded to my original message!

No. of Recommendations: 0

OK, I'll admit it. I'm not really a quicken apologist. I don't even use quicken (just got msmoney98). wow, do i feel better now that i've got that off my chest. I'm still not sure I understand why you are interested in the discrepancy. It seems as though you get a free boost from the bank, but only if you close the account, and only one time for some small time interval. I can't really apologize for quicken's help file statement. I have used the XIRR function in excel and found that it generally agrees with the Msmoney98 annual return calculation. If you have excel, you can get help on the XIRR function and it will give you the exact formula (daily compounding). I seem to recall that the bank's convention is to use 365 days for the denominator of the exponential term, but the cobwebs surrounding this memory make it suspect.

Let's see if this makes sense (from excel):

0= summation from i=1 to N {Pi/[(1+rate)^((di*d1)/365)]}

Where:

di = the ith payment date.

d1 = the 0th payment date.

Pi = the ith payment.

The message says they use an iterative technique to solve for the rate.

sorry if i'm not getting it ;(

y

No. of Recommendations: 0

i guess i don't understand the definition of accrued. does accrued imply that the interest increases from day one to the final day in a strictly linear fashion? if so, i sort of see your point. i was assuming that accrued simply means that it increases daily but in a geometric fashion, rather than linearly.

y

No. of Recommendations: 0

yFool,

The formula you give from Excel's XIRR function is the one I always see for IRR, and the one (essentially) that Quicken uses. The problem I have with Quicken is they said the result is the annually compounded interest rate that a bank would have to pay in order for a bank account to have the same balance as the value of your investment (assuming you put in / take out the same amounts of money in the investment and bank account at the same times). IRR simply does not do that.

You'd think it ought to, because it is clearly derived from the formula for annually compounded interest: P*(1+r)^n where P is the initial principal, r is the interest rate (e.g. 0.06 for 6%), and n is the number of years (that is, the number of periods of compounding). The problem is that everyone has substituted days/365 for n, and that just doesn't work. The "number of periods" has to be an integer. The correct formula for annually compounded interest when n can have a fractional component is: (P*(1+r)^int(n))*(1+r*(n-int(n))).

When your resolution is days as it is in Quicken (meaning, you can put in a date but you can't put in a time, so the smallest possible time interval is one day), the formula you should use for IRR is:

0= summation from i=1 to N {Pi/[(1+rate/365)^(di*d1)]}

which is based on daily compounding rather than annual, thus avoiding the problem of a fractional number of compounding periods. This formula gives the value one would expect, 140.0463%, for daily compounding to bring $1000 to $2000 in half a year (181 days, actually). XIRR and Quicken give this 300% number, which I consider to be wrong.

No. of Recommendations: 0

yFool,

In standard banking practise, when interest is earned daily and compounded monthly (for example), the amount of interest earned each day is P*r/365 where P is the principal and r is the interest rate. Each day, the interest would be earned, or "accrued" as they put it, but not added to the principal. So, each day of the month you earn the same amount of interest. At the end of the month, the interest is "compounded" by adding it to the principal. So, next month you earn a little more interest daily since the principal is larger now.

So yes, the interest earned before compounding accumulates linearly.

No. of Recommendations: 0

This is what I was going to point out yesterday if my account had not been hosed. Interest compounded will be a geometric series. It will require the use of square roots to calculate for periods less than 1 year. I think that accrued interest implies a linear series. It is money that you have earned but not been credited with. Therefore, it does not add to the earning/interest gaining power of your principal. This will only require the use of division to calculate accrued interest. However, the moment the credit the money to your account it is now compounded so the simple division will not work and will be inaccurate.

chris

No. of Recommendations: 0

>>(P*(1+r)^int(n))*(1+r*(n-int(n)))<<

very nice, geometric series with linear ramps.

>>the formula you should use for IRR is:

0= summation from i=1 to N {Pi/[(1+rate/365)^(di*d1)]}

which is based on daily compounding rather than annual, thus avoiding the

problem of a fractional number of compounding periods. This formula

gives the value one would expect, 140.0463%, for daily compounding to

bring $1000 to $2000 in half a year (181 days, actually). XIRR and

Quicken give this 300% number, which I consider to be wrong.the formula you should use for IRR is:

0= summation from i=1 to N {Pi/[(1+rate/365)^(di*d1)]}

which is based on daily compounding rather than annual, thus avoiding the

problem of a fractional number of compounding periods. This formula

gives the value one would expect, 140.0463%, for daily compounding to

bring $1000 to $2000 in half a year (181 days, actually). XIRR and

Quicken give this 300% number, which I consider to be wrong.<<

hmmm...

let me chew on that <gets out pad and paper>

oops, it's friday night, maybe in the morning <g>

thanks

y

No. of Recommendations: 0

yFool,

Oops! I copied the formula from your message, and you seem to have copied it from Excel's XIRR help, which has a typo. Anyplace you see di*d1 that should be di-d1. (Obviously, multiplying dates makes no sense; they should be subtracted.)

However, before you put much effort into it . . .

I've been doing some more research on IRR, and I think I'm onto a different track. Let me think about it a little more, and I'll post an update.

No. of Recommendations: 0

Yes yes, I messed up the exponential term, should be delta d or di-d1 or something, you know what I mean (waves hands some more).

The formula for daily compounding looks good to me. But that's not what I'd want from xirr. I'd just want a consistent formula. The point (for me) is to "normalize" growth so that you can make apples to apples comparisons. We all know 7% compounded instantaneously, daily, monthly, or annually will give 4 different totals at one year. So we need a convention to compare investments. The convention, as I understand it, is annual return, although I agree with you that it makes sense to use daily compounding for all comparisons. Perhaps for the long term investor annual compounding is more convenient (you can sort of do it in your head). Thus the XIRR function (I think, still haven't crunched the numbers) creates a geometric series based upon the data points you input and "fills in the gaps" with the same exponential function, rather than the linear step function you described earlier. When you select a 6 month period, you are, sort of forcing it to compound at 6 months as eldoen mentioned in a post above.

Or looking at it a different way, here's how I initially solved the simple case you presented:

(1+rate)^n=2 for some time period n (expressed in terms of years). I then ask what will the return be at one year?

Let m=1/n. thus, using the correct units for n, m*n=1

So at one year, your total is (1+rate)^(m*n) {real multiplication this time}.

The beauty is you don't pay any attention to the rate.

Plugging in the numbers for 6 months: n=1/2 (yuky fractional year) , m=2

(1+rate)^(2*n)=1yr value. = [(1+rate)^n]^2 = 2^2 = 4 (300% increase).

No. of Recommendations: 0

If you go to http://members.aol.com/tracypaul and use the Savings Calculator there, it gives the correct result of 200%. (put in .5 for years)