Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert average daily yield to APY
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert average daily yield to APY
On Jun 18, 10:05 pm, Greg wrote:
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". |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert average daily yield to APY
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" wrote: On Jun 18, 10:05 pm, Greg wrote: 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". |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert average daily yield to APY
[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! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Convert average daily yield to APY
On Jun 19, 2:31 am, I wrote:
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average Daily Balance | Excel Worksheet Functions | |||
Figuring daily average...function ??? | Excel Worksheet Functions | |||
How do I set up a daily average of unit sales formula | Excel Discussion (Misc queries) | |||
Running a Daily MTD average. | Excel Worksheet Functions | |||
daily average | Excel Worksheet Functions |