[repost (1)]

On Jun 19, 12:27 am, Greg wrote:
Thanks for the detailed response. I understand that this will not result in a
realistic APY for the short term. Since my investments are in multiple
places, I track them all together in MSN portfolio, which gives me the daily
total gain/loss. I am compiling this info on my sheet, and using an "average"
function to give me the average daily gain. I am hoping that over time this
gives me a more realistic picture.

Of the average __daily__ rate of change, yes. But not of an annual rate of
change, when the daily rate is annualized as I explained.

You had asked for the APY, which is the compounded growth rate. GIGO!

IMHO, a more reasonable (but still unrealistic) estimate of the annual rate
of change can be derived as follows.

Graph the total daily value ("total return") of your portfolio, not the rate
of change, and find a curve that best fits. Usually, it is a linear curve.
Extrapolate the curve to a year (252 units) from some point in time,
depending on what you want. Then compute the year-over-year rate of change.

For example, if A2:A91 contains the total daily value of your portfolio over
the last 90 trading days, B3 guestimates the annual rate of change over the
252 trading days following A2.

B1: =forecast(2+252, $A$2:$A$91, row($A$2:$A$91))
B2: =forecast(2, $A$2:$A$91, row($A$2:$A$91))
B3: =B1/B2 - 1

B1 is the best-fit data point 252 trading days after A2. B2 is the best-fit
data point corresponding to A2.

Of course, the guestimate is unrealistic because the trend is not likely to
remain constant for 252 trading days. But I think it does represent a
reasonable annualized instanteous rate of change.


Endnotes:
(1) Sorry for the repost. Google Groups is interminably slow again;
sometimes it loses postings. I'm impatient to see this posted. It's late!