ROI Yield Report Glitch?

[elliots]

Mark,

I bought an investment in 2008, sold it in 2010 and then bought it again near the end of 2010. There were 2 dividend and 2 foreign tax transactions. The ROI Yield report is showing N.A. for 3 and 6 mo, and then big negative numbers for 9 mo, 1 and 2 yrs. I suspect the pattern of transactions has blown the calculation algorithm. I can email a print of the report and data if you'd like.

Elliot

[Mark]

Hi Elliot,

This should not cause a problem. You might check out your interpolation range settings for why you are getting N/A for those certain time periods. If you'd like to see the details of the calculation, turn on yield logging, and then take a look at the log files. See "Options / General Preferences... / Yields".

[elliots]

Mark,
I did have a hole in the price data between the sale and repurchase which I have filled in. Now I get numbers for 3 and 6 mo but they are wildly absurd as are the numbers for 9m, 1 and 2 yrs. The investment was sold to 0 on 6/9/10 and there were cash dividends and foreign tax transactions on 6/15/10. I think you should look at the report and data.

By the way, where are the logs filed -- I did turn on logging but can't find the logs.

Elliot

[Mark]

Hi Elliot,

I don't mind looking at the issue, but it is better all around if you try to figure it out first... This can often help you learn what caused it, and help you to be able to figure out problems in the future if you run into something different.

By looking at the logs you can see all the components of the yield equation being solved, and compare those to what you are expecting. For example, if you have a buy with a cost basis of $1000, you would expect to see a term in the equation with this amount. If you have some un-expected terms in the yield equation, it can help steer you to what data you may have incorrectly recorded. The ROI yield equation should have a component for the beginning value, one for the ending value, and then one for every transaction within the yield period. For the generic equation, see:

http://www.fundmanagersoftware.com/help/yields_roi.html

If you press the "Help..." button in this dialog (Options / General Preferences... / Yields) the name and location of the log files are shown.

[elliots]

Mark,

I've looked at the log. Without doing the math (I'd have to go to my calculator manual to remember how to take the 0.032609th root) it's obviously wrong.

For starters, I just looked at the 3 month term: As of date is 12/31/10 and the stocks market value is 11280.50. The position was bought 12/28/10 for 11200. That's a 0.71 period return and is also the IRR (ROI). The report shows 18.97. I didn't check to see if that's annualized (you say you don't for periods less than a year and in any case the annualize box is unchecked on the yields preference menu). Further, since the investment was not held for 3 months why is it calculating at all?

Elliot

[Mark]

Hi Elliot,

I think the confusion may be the meaning of a 3 month yield, at least how FM calculates it. When you calculate a 3 month yield, the yield is for that full 3 months. You can think of the yield as a rate at which you earned. In the case of ROI yields, this rate is time and value weighted. So, if you only owned the position for 3 days of the 3 months, the performance over those 3 days are essentially extrapolated to get the rate for the full 3 months. Periods in this 3 month window where you didn't own any shares have zero weighting, since the value is zero.

For example, let's say you are calculating a yield for 120 days, but you only owned the investment for 12 of those days. If you gained 10% on your investment in those 12 days, the yield for 120 days would be 100% (10% * 120 / 12), not 10%.

There is an interpolation range setting meant to help here. It is "Allow Interpolation Prior to Earliest Recorded Data". By default, this option is turned off, so that if you calculate a 3 month yield from 10/1 - 12/31, but you didn't buy it until 12/28, the yield will report N/A. You would have to have purchased it before 10/1 in order to get a yield when this option is off.

I think what you may be looking for is a yield term from 12/28 - 12/31. If you create a Custom report and set the date range to this, and use "ROI (between)", you will get the number I think you're expecting.

[Mark]

Hi Elliot,

I just re-read your initial post. I think since you initially bought it in 2008, that is allowing FM to report a 3 month yield with that interpolation range setting off, even though you only re-bought it 3 days before the end of the yield term. I'm thinking a good change would be to have an interpolation range option that prevents ROI yields from being reported unless you owned it for the full yield term, not based upon just when you first bought it.

[elliots]

Mark,

I now understand what you're calculating and I think you need to make it more clear in the documentation. You say you're not "annualizing" which is factually correct, but what you are doing is "3 monthizing." Thus, for any time period, you're calculating as though the asset were held for the entire period and you should clarify that.

I personally think that extrapolating returns earned over very short periods to longer periods is misleading and a dangerous practice, especially for the inexperienced. In my opinion the calculation should only be from the date of purchase or maybe the beginning of the next full period -- so I'll stop using this report. Actually, I'd really like to be able to suppress the individual investment returns anyway because I'm trying to keep my client's focus on the total portfolio, not each individual piece. I can get that effect now with the Exec Summary. There are other reports that can examine the individual investments if desired.

In this case, the price interpolation feature didn't flag me because I had previously owned the investment (and sold to 0) and I maintain full daily pricing on everything -- so I had prices at the beginning of the period. I'm doing historical price retrieves for everything a few times a month and then I do a price import from the broker for month end data to ensure that the price I'm using is identical to what's on the broker's month end statement. If I turned off price retrieve on sold investments (file operations menu) will that turn off historical price retrieve too? Is that what I should be doing? It should reduce file sizes and allow the interpolation feature to generate the NA I'm looking for in the ROI report.

Elliot

[Mark]

Hi Elliot,

Thanks for the feedback. This issue does cause a fair amount of confusion. I'm thinking about adding a new interpolation option something like:

"Compress ROI yield term to dates owned"

or something like that. So, if you request a 3 month yield, but only owned it for the last 1 month, it would calculate the ROI yield over only that 1 month period, and report that figure.

Yes, if you turn off the retrieve price option, that affects any internet price retrieve, including historical servers. You can choose for yourself if that is what is best, but not having the prices for periods where you don't own the shares will prevent the ROI from reporting, assuming you leave on the option to limit the number of days you interpolate pricing.

[elliots]

Mark,

I like the "Compress ROI. . ." idea but you would need to flag those with a footnoted symbol "Return is not for full period."

I also think you need to deal with "discontinuous" positions -- like the one I had where I owned if for some months a year ago, sold out, and then re-established. I think those older holding periods should not be computed -- they really don't make sense anyway. Like the report in my case for the discontinuous investment shows 18.97, 41.53, -52.93, -43.22, and -1.43 for 3mo, 6mo, 9mo, 1yr, 2y time periods. It was bought12/26/08, sold 6/9/10, and then bought again12/28/10.

Elliot

[Mark]

Hi Elliot,

Holdings outside the requested yield term do not affect the ROI yields. The yield calculation looks at the starting value, ending value, and all transactions within the yield period, so if you're calculating a 3 month yield, ending 12/31/2010, what you bought/sold in 2008 makes no difference, other than the issue that it allows you to pass the interpolation range check, and reports a yield when maybe you don't want it to. That won't affect the calculated ROI yield numbers though. If you sold on 6/9/2010, and that was after the beginning of any of those yield terms, then this would play into the ROI yields.

I do think this needs some more thought. Another option may be to not compress the yield term, but have an interpolation check something like:

"Enforce shares are owned for entire yield term"

so that if you only own the security for part of the yield term, you'll get an N/A.

[elliots]

Mark,

Your last "Enforce shares are owned for entire yield term" is the way to go but there's more.

Not to prolong this but even worse . . .

In the example I gave, the 3 month yield is an extrapolation of 3 days and so is the 6 month (the same 3 days). Even worse, the big negative numbers for the longer periods are largely due to different sized positions. Your calculation uses the position value -- and the old position was for 400 shares and the more recent position was for 350 shares. That value difference is showing as an investment loss and accounts for the negative result, not the investment performance.

Elliot

[Mark]

Hi Elliot,

The position size difference will not cause poor performance numbers. The ROI yields are calculating the true yield equation, and are time and money weighted. If you have big negative numbers, then somehow the investment performed poorly (went down in value, compared with what you started with, and what you put in or took out). The ROI yield equation is capable of handling different sized positions for different time periods. The yield is time and money weighted, so the performance is more heavily weighted for larger amounts and is also affected by how long the money is invested. In order to get a negative yield though, you had to have a loss at some point in the yield term.

If you'd like to understand it more, please tell me the ending yield date, yield term, starting and ending values, and all the transactions within the yield period. From this, we can get an idea of what the yield should be. You can see the ROI yield equation here:

http://www.fundmanagersoftware.com/help/yields_roi.html

It would also be helpful to post the yield equation for this case in question, as read from the yield log file.

[elliots]

Mark,

End point is 12/31/10 and the calculation is for 3m, 6m, 9m, 1yr, and 2yr.
Transactions are all in SNY stock:

Bought 400 sh 12/26/08 Value $12,509.00
Dividend 6/1/09 $595.91
Foreign Tax Paid 6/1/09 $148.98
Dividend 6/15/10 $586.01
Foreign Tax Paid 6/15/10 $145.98
Sold 400 sh 6/9/10 Value $11,652.80
Bought 350 sh 12/28/10 Value $11,216.80


FROM THE ROI LOG:
ROI Equation for investment "SANOFI-AVENTIS": Yield Result (i) = 18.97

Dates: 10/1/2010 - 12/31/2010
Including Accrued Interest: ON
Including Account Fees: ON
Yield Period in Years: 0.252055
End_val + Dist(s) - Inv(s) - Beg_val = 0

11280.500000 - 11216.800000(1 + i)^0.032609 - 0.000000(1 + i)^1.000000 = 0
***************************************
ROI Equation for investment "SANOFI-AVENTIS": Yield Result (i) = 41.53

Dates: 7/1/2010 - 12/31/2010
Including Accrued Interest: ON
Including Account Fees: ON
Yield Period in Years: 0.504110
End_val + Dist(s) - Inv(s) - Beg_val = 0

11280.500000 - 11216.800000(1 + i)^0.016304 - 0.000000(1 + i)^1.000000 = 0
***************************************
ROI Equation for investment "SANOFI-AVENTIS": Yield Result (i) = -52.43

Dates: 4/1/2010 - 12/31/2010
Including Accrued Interest: ON
Including Account Fees: ON
Yield Period in Years: 0.753425
End_val + Dist(s) - Inv(s) - Beg_val = 0

11280.500000 + 586.010000(1 + i)^0.723636 + -145.090000(1 + i)^0.723636 - 11216.800000(1 + i)^0.010909 - -11652.800000(1 + i)^0.745455 - 14944.000000(1 + i)^1.000000 = 0
***************************************
ROI Equation for investment "SANOFI-AVENTIS": Yield Result (i) = -43.22

Dates: 1/1/2010 - 12/31/2010
Including Accrued Interest: ON
Including Account Fees: ON
Yield Period in Years: 1.000000
End_val + Dist(s) - Inv(s) - Beg_val = 0

11280.500000 + 586.010000(1 + i)^0.545205 + -145.090000(1 + i)^0.545205 - 11216.800000(1 + i)^0.008219 - -11652.800000(1 + i)^0.561644 - 15708.000000(1 + i)^1.000000 = 0
***************************************
ROI Equation for investment "SANOFI-AVENTIS": Yield Result (i) = -1.43

Dates: 1/1/2009 - 12/31/2010
Including Accrued Interest: ON
Including Account Fees: ON
Yield Period in Years: 1.000000
End_val + Dist(s) - Inv(s) - Beg_val = 0

11280.500000 + 586.010000(1 + i)^0.545205 + -145.090000(1 + i)^0.545205 + -148.980000(1 + i)^1.583562 + 595.910000(1 + i)^1.583562 - 11216.800000(1 + i)^0.008219 - -11652.800000(1 + i)^0.561644 - 12864.000000(1 + i)^2.000000 = 0
***************************************

[Mark]

Hi Elliot,

Let's focus on the first yield with a large negative amount, the 9M yield:

ROI Equation for investment "SANOFI-AVENTIS": Yield Result (i) = -52.43

Dates: 4/1/2010 - 12/31/2010
Including Accrued Interest: ON
Including Account Fees: ON
Yield Period in Years: 0.753425
End_val + Dist(s) - Inv(s) - Beg_val = 0

11280.500000 + 586.010000(1 + i)^0.723636 + -145.090000(1 + i)^0.723636 - 11216.800000(1 + i)^0.010909 - -11652.800000(1 + i)^0.745455 - 14944.000000(1 + i)^1.000000 = 0

You ended with an ending value of 11,280 on an invested amount of 11,216. For this section you did have a positive return. However, at the beginning of the period you started on 4/1 with a market value of 14,944, and within the period you sold those shares for only 11,652.80, thus the large negative return. This investment lost 22% in only a 2 month period:

(11652.80 - 14944) / 14944 = -22%
From 4/1 to when you sold it on 6/9.

So, you had a small positive return for 3 days near the end of the 9M yield term, but you had a large negative for 2 months of the yield term, and it is weighted more because of the time and amount invested.

Solving the whole yield equation, factoring in all of these items, plus the distributions, results in the -52% being reported. You can plug the 52% number into that equation and verify it equates.

The key is you have to look at it for the time period of the yield term. The yield term started on 4/1, where your market value was higher than when you sold, so your ROI will correctly reflect this loss.