How to measure Investment Performance

LLLLWhenR2I · Post by **LLLLWhenR2I** » Fri Jan 26, 2007 7:22 am

Link to Original thread: How to measure Investment Performance

From:

assumedjohndoe (Original Message)Sent: 8/14/2006 5:20 PMTo all Financial Gurus:

(I use Quicken 2006 to track all my investments.)
To measure performance, Quicken provides an Internal Rate of Return for my IRA account.
I noticed that even though the market value of my holdings ($10,000) in the IRA account is lower than the Cost basis ($12,000), the IRR is a positive number (15%).
After some research I found that IRR takes into account series of cash flows over a period of time. I am *guessing* this is causing the rate of return to be higher than what it really is.

Questions:
1. Can you explain why the Rate of Return is a positive number when in reality I have made a loss on my investments?

2. Is there any tool/website/package that will allow me to compare my current IRA value vs the value if I had put the money in a CD with X% interest? I currently do this in a spreadsheet but it is very cumbersome to maintain and also error prone.

Thank you in advance for enlightening.

LLLLWhenR2I · Post by **LLLLWhenR2I** » Fri Jan 26, 2007 7:23 am

From: Bobus175 Sent: 8/15/2006 7:15 AM
I noticed that even though the market value of my holdings ($10,000) in the IRA account is lower than the Cost basis ($12,000), the IRR is a positive number (15%).

Not sure what you mean by "cost basis" above.

IF

(a) Your total contributions to IRA are $12K AND
(b) You have not taken any distributions from IRA AND
(c) Current value of IRA is $10K

THEN

the IRR cannot be positive.

If you can post the schedule of contributions to IRA (dates and corresponding amounts), then someone here can determine IRR quite easily.

LLLLWhenR2I · Post by **LLLLWhenR2I** » Fri Jan 26, 2007 7:24 am

Will be posting more

LLLLWhenR2I · Post by **LLLLWhenR2I** » Sat Jan 27, 2007 12:20 am

From:

LLLLWhenR2ISent: 8/15/2006 10:01 AM
Bobus.
To me your assumptions seems to be correct. I also believe this is what OP wants to convey and also to me IRR cannot be positive. I am just wondering whether OP is looking at correct time-frame or not. Probably he is looking at last years performance.....

OP please check the dates and let us know about it.

LLLLWhenR2I · Post by **LLLLWhenR2I** » Sat Jan 27, 2007 12:21 am

From: Nattusbs1 Sent: 8/15/2006 10:27 AM

I use Compounded Annual Growth Rate in an Excel spreadsheet.

Cheers:Nattusbs

LLLLWhenR2I · Post by **LLLLWhenR2I** » Sat Jan 27, 2007 12:22 am

From: r2i2006 Sent: 8/15/2006 11:15 AM

If you have Quicken or Money, download the fund data into it and it will give you the Annualized Return. It actually adds distributions to the cost basis and thus makes the results more accurate.

LLLLWhenR2I · Post by **LLLLWhenR2I** » Sat Jan 27, 2007 12:22 am

From: assumedjohndoe Sent: 8/15/2006 1:14 PM

I think my understanding of IRR is wrong or I am interpreting it wrong.

Form my reading, You need to have an initial investment and a series of cash flows to calculate IRR.
For example, I Invest $100,000 in Year 1 and receive revenues $50,000, $40,000, $30,000, $20,000 from Year 2 to 5. The Investment Rate of Return is 3.125%. (You can calculate this using IRR function in Excel)

But this is different from a typical IRA account where you start with $1000 in Year 1 and then contribute $1000 from Year 2 to Year 5. At the same time you may get Dividends/Capital Gains Distributions of $50.00 from Year 2 to Year 5.

It would look like this:
Year Contribution Dividends/Capital Gains Dist. Total
1 1000 0 1000
2 1000 50 1050
3 1000 50 1100
4 1000 50 1150
5 1000 50 1200

So how do you calculate IRR in such a scenario? You cannot consider your contributions as income.

In the above example, my "Cost Basis" i.e. the money I have put in or have earned as dividend/Cap Gains is $1200.

I have paid $1200 for shares is a mutual fund, lets say 120 shares.

The market value, on a given day, of these 120 shares could be $1000. That brings me back to my original question, how can I have a positive IRR when in reality I have lost money?

Thank you Guys/Gals for your time in explaining this to me.

LLLLWhenR2I · Post by **LLLLWhenR2I** » Sat Jan 27, 2007 12:23 am

From: Bobus175 Sent: 8/15/2006 5:50 PM

I think my understanding of IRR is wrong or I am interpreting it wrong.

Seems so.

For example, I Invest $100,000 in Year 1 and receive revenues $50,000, $40,000, $30,000, $20,000 from Year 2 to 5. The Investment Rate of Return is 3.125%. (You can calculate this using IRR function in Excel)

Seems incorrect. Are you calculating yearly IRR? Was the $100K investment made at the beginning or end of Year 1? Were the revenues received at the end or beginning of the respective years?

But this is different from a typical IRA account where you start with $1000 in Year 1 and then contribute $1000 from Year 2 to Year 5. At the same time you may get Dividends/Capital Gains Distributions of $50.00 from Year 2 to Year 5.

It would look like this:
Year Contribution Dividends/Capital Gains Dist. Total
1 1000 0 1000
2 1000 50 1050
3 1000 50 1100
4 1000 50 1150
5 1000 50 1200

Did you start with $1K at the end or beginning of year 1? Dividend/cap gain distributions within the IRA from underlying funds dont matter - what matters are withdrawals from IRA, if any. Was the $1K per year contribution made at the beginning or end of the respective years? What is value of the IRA at the end of Year 5?

LLLLWhenR2I · Post by **LLLLWhenR2I** » Sat Jan 27, 2007 12:23 am

From:

assumedjohndoeSent: 8/15/2006 6:34 PMBobus175,

For example, I Invest $100,000 in Year 1 and receive revenues $50,000, $40,000, $30,000, $20,000 from Year 2 to 5. The Investment Rate of Return is 3.125%. (You can calculate this using IRR function in Excel)

Seems incorrect. Are you calculating yearly IRR? Was the $100K investment made at the beginning or end of Year 1? Were the revenues received at the end or beginning of the respective years?

IRR is calculate for the entire time period. It doesn't matter when you made the investment, at lease the Excel IRR function does not have any date parameter. There is seperate Excel function XIRR which for which you need to provide the dates when you go the income.

But this is different from a typical IRA account where you start with $1000 in Year 1 and then contribute $1000 from Year 2 to Year 5. At the same time you may get Dividends/Capital Gains Distributions of $50.00 from Year 2 to Year 5.

It would look like this:
Year Contribution Dividends/Capital Gains Dist. Total
1 1000 0 1000
2 1000 50 1050
3 1000 50 1100
4 1000 50 1150
5 1000 50 1200

Did you start with $1K at the end or beginning of year 1? Dividend/cap gain distributions within the IRA from underlying funds dont matter - what matters are withdrawals from IRA, if any. Was the $1K per year contribution made at the beginning or end of the respective years? What is value of the IRA at the end of Year 5?
There were no withdrawls. Again I think it doesn't matter when you made the contribution in a year. The value of the IRA at the end of Year 5 is $ 1200.

LLLLWhenR2I · Post by **LLLLWhenR2I** » Sat Jan 27, 2007 12:24 am

From:

Bobus175Sent: 8/15/2006 10:25 PM#8:

For an avowed learner, your posts suggest a

(a) lot of confidence and
(b) determination not to delegate the decision about how best your learning can be facilitated to someone who comes along to help.

Perhaps it has not crossed your mind that the above combination can be dysfunctional to the task, and place an unwarranted burden on the person who comes along to help.

Now to some substantive issues that may cast some light on the assertions made in #8.

The excel function IRR assumes equal time intervals between consecutive cash flows. In the event one wants to compute IRR (using the excel IRR function) when the time interval between consecutive cash flows is not equal, one has two options:

(i) circumvent the limitation of the excel IRR function by entering zero(s) appropriately

or

(ii) Use the excel function XIRR.

The excel IRR function does not assume anything about whether the equal time interval between consecutive cash flows is a year, a day, a month or something else. So the number that it returns has to be interpreted based on the problem at hand. The excel IRR function also does not assume anything about whether the cash flows occured at the beginning or end of a period. As long as we have equal time intervals between consecutive cash flows, the excel IRR function will give sensible answers. Else, it will likely be GIGO - garbage in, garbage out.

The question about whether the cash flows occured at the beginning or end of year was asked to find out if the time intervals between consecutive cash flows (in your problem) was equal or not. It helps if the problem at hand is well specified and understood before crunching numbers.

To illustrate, let me take an extract from your #6 and compute IRR, under two alternative scenarios (not exhaustive):

For example, I Invest $100,000 in Year 1 and receive revenues $50,000, $40,000, $30,000, $20,000 from Year 2 to 5. The Investment Rate of Return is 3.125%. (You can calculate this using IRR function in Excel)

Scenario 1

Invest $100K at the beginning of Year 1.
Receive cash flows of $50K, $40K, $30K and $20K at the end of Years 2 through 5, respectively.

The yearly IRR under the above scenario is 11.53%

Scenario 2

Invest $100K at the end of Year 1.
Receive cash flows of $50K, $40K, $30K and $20K at the end of Years 2 through 5, respectively.

Now, for Scenario 2, the yearly IRR is 17.80%

Both 11.53% and 17.80% are a far cry from 3.125%.
For Scenario 2 above, before using excel IRR function, the cash flows may be entered on an excel sheet as follows:
-10000050000400003000020000
whereas for Scenario 1 above, the cash flows may be entered on an excel sheet as follows:
-100000050000400003000020000
Good Night and Good Luck with your learning.