Convert average daily yield to APY

[Greg]

I am a beginner in Excel, so bear with me. I have a spreadsheet that tracks
my investments, and calculates the daily return as a percentage, which I then
have averaged to show the average daily yeild. I have come up with another
formula that shows compounding interest, but it needs the APY to get proper
calculation. What I want to be able to do is convert the average daily yield
to an APY, which will change as daily average changes. Everything I have
tried so far has not worked. Any help would be greatly appreciated.

Thanks

 

[joeu2004]

I have a spreadsheet that tracks
my investments, and calculates the daily return as a percentage, which I then
have averaged to show the average daily yeild. I have come up with another
formula that shows compounding interest, but it needs the APY to get proper
calculation. What I want to be able to do is convert the average daily yield
to an APY, which will change as daily average changes.

Ostensibly, for securities, if A1 has the average daily return
(percentage change), it can be annualized by either of the following
equivalent formulas:

=(1+A1)^252 - 1

=fv(A1, 252, 0, -1) - 1

252 is the typical number of trading days per year, which is
appropriate for traded securities. Use 365 instead of 252 if you are
talking about money market investments and savings accounts (you
mention "interest").

However, for money market investments, you might need to look at how
the "interest" is computed. Often, it is a simple daily interest rate
applied to the daily balances or the average daily balance for a
month. In that case, the APY is computed by:

=(1+A1*(D2-D1))^12 - 1

=fv(A1*(D2-D1), 12, 0, -1) -1

where D1 and D2 are the dates of the end of previous and current
statement periods, respectively. On average, D2-D1 is 365/12.

For securities, although the formulas above are mathematically
correct, it is debatable whether your can realistically annualize sub-
annual returns in this manner. For example, HPQ change by 0.39% today
(6/18/2007). But no one would say HPQ changed 166.68% annually, which
is (1+0.39%)^252-1.

It is better to compute year-over-year return rates. For example, HPQ
was 33.04 on 6/16/2006 and 45.89 on 6/18/2006. So the annual return
is 38.89%.

Ostensibly, you can compute the average daily return from that,
namely: (1+38.89%)^(1/252)-1. But again, although that is the
compounded daily rate that yields the annual rate, it does not
realistically reflect the average of the daily rates of change.

HTH. Post back if any of this changes the way you want to do things,
and you want more help. Also, it might be helpful if you provide the
formula that you use to compute "the average daily yield".

 

[Greg]

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.

Thanks again

 

[joeu2004]

[repost (1)]

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!

 

[joeu2004]

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

Wow, talk about overkill! Forecasting in that manner has its purpose;
but on second thought, I suspect it does not meet your needs.

Let me recap and expand a little.


1. For money market accounts that compound daily, the APY can be
estimated by either of the following:

=(1+A1)^365 -1

=fv(A1, 365, 0, -1) - 1

where A1 is the daily interest rate.


2. For money market accounts that compound monthly using a daily
balance or average daily balance method, the APY can be estimated by
either of the following:

=(1+A1*D1)^12 - 1

=fv(A1*D1, 12, 0, -1) - 1

where A1 is the daily interest rate, and D1 is the number of days in
the month.


3. For securities investments, I would not compound the daily rate of
change.

Instead, I would compute an annual trailing rate of change, ideally
using year-over-year prices. For example, if B1:B253 contains a full
year of daily prices (in your case, portfolio value), the following
computes the annual trailing rate of change for the date corresponding
to B253:

=B253/B1 - 1

If I have less than one year of prices, I would annualize the trailing
rate of change for as much data that I have. For example, if B1:B64
contains a quarter of daily prices, either of the following computes
the rate of change for the quarter and annualizes it:

=(B64/B1)^4 - 1

=fv(B64/B1 - 1, 4, 0, -1) - 1

Or more generally:

=(B64/B1)^(252/63) - 1

HTH.