|
Weekly Returns
i'm doing a project in my finance class and i need to do the weekly return of
the closing price of the companies stock for 15 weeks. Is there a function for this, and if not does anybody know how i can calculate the weekly return |
Answer: Weekly Returns
Yes, there is a function in Excel that can help you calculate the weekly return of a stock. The function is called "WEEKNUM" and it can be used to determine the week number of a specific date. Here are the steps to calculate the weekly return:
By following these steps, you should be able to calculate the weekly return of the closing price of the stock for 15 weeks. Good luck with your project! |
Weekly Returns
If you bought something for $100, and one week later sold it for $110,
what's your return on investment? Hopefully, you were able to answer instantly that it's 10%. If not, maybe finance is not for you. It's the same with returns on a stock price. The return on investment is always: =(EndingValue - BeginningValue) / BeginningValue Hopefully you can take it from here. But if you need more help, post back. Regards, Fred. "Robbins" wrote in message ... i'm doing a project in my finance class and i need to do the weekly return of the closing price of the companies stock for 15 weeks. Is there a function for this, and if not does anybody know how i can calculate the weekly return |
Weekly Returns
Oh ok i thought that's what it was but our book and notes don't really
explain it as easily. "Fred Smith" wrote: If you bought something for $100, and one week later sold it for $110, what's your return on investment? Hopefully, you were able to answer instantly that it's 10%. If not, maybe finance is not for you. It's the same with returns on a stock price. The return on investment is always: =(EndingValue - BeginningValue) / BeginningValue Hopefully you can take it from here. But if you need more help, post back. Regards, Fred. "Robbins" wrote in message ... i'm doing a project in my finance class and i need to do the weekly return of the closing price of the companies stock for 15 weeks. Is there a function for this, and if not does anybody know how i can calculate the weekly return . |
Weekly Returns
"Robbins" wrote:
Oh ok i thought that's what it was but our book and notes don't really explain it as easily. Perhaps you should tell us how your book and notes explain it. There are several ways to express periodic returns. Perhaps your instructor is expecting you to follow the method described in your book and notes. In particular, should weekly returns be expressed as percentage change per week (Fred's formula), or an annualized percentage change? Also, it is unclear what you mean by "do the weekly return ... for 15 weeks". If you will be doing some statistical analysis, especially computing volatility, you might be interested in the log return, not the arithmetic return (Fred's formula). ----- original message ----- "Robbins" wrote in message ... Oh ok i thought that's what it was but our book and notes don't really explain it as easily. "Fred Smith" wrote: If you bought something for $100, and one week later sold it for $110, what's your return on investment? Hopefully, you were able to answer instantly that it's 10%. If not, maybe finance is not for you. It's the same with returns on a stock price. The return on investment is always: =(EndingValue - BeginningValue) / BeginningValue Hopefully you can take it from here. But if you need more help, post back. Regards, Fred. "Robbins" wrote in message ... i'm doing a project in my finance class and i need to do the weekly return of the closing price of the companies stock for 15 weeks. Is there a function for this, and if not does anybody know how i can calculate the weekly return . |
Weekly Returns
Here's what i needed to do. We have to follow a company for 15 weeks. Every
friday starting on Aug 21, 2009 we had to write down the closing price for the stock that week. Based on the closing prices he wants us to calculate the weekly return for each stock and the whole porfolio. We are also supposed to calculate the total return for each stock and portfolio. And lastly calculate the risk of each stock and the whole portfolio. "Joe User" wrote: "Robbins" wrote: Oh ok i thought that's what it was but our book and notes don't really explain it as easily. Perhaps you should tell us how your book and notes explain it. There are several ways to express periodic returns. Perhaps your instructor is expecting you to follow the method described in your book and notes. In particular, should weekly returns be expressed as percentage change per week (Fred's formula), or an annualized percentage change? Also, it is unclear what you mean by "do the weekly return ... for 15 weeks". If you will be doing some statistical analysis, especially computing volatility, you might be interested in the log return, not the arithmetic return (Fred's formula). ----- original message ----- "Robbins" wrote in message ... Oh ok i thought that's what it was but our book and notes don't really explain it as easily. "Fred Smith" wrote: If you bought something for $100, and one week later sold it for $110, what's your return on investment? Hopefully, you were able to answer instantly that it's 10%. If not, maybe finance is not for you. It's the same with returns on a stock price. The return on investment is always: =(EndingValue - BeginningValue) / BeginningValue Hopefully you can take it from here. But if you need more help, post back. Regards, Fred. "Robbins" wrote in message ... i'm doing a project in my finance class and i need to do the weekly return of the closing price of the companies stock for 15 weeks. Is there a function for this, and if not does anybody know how i can calculate the weekly return . . |
Weekly Returns
"Robbins" wrote:
We have to follow a company for 15 weeks. [....] he wants us to calculate the weekly return for each stock and the whole porfolio. I'm confused. On the one hand, you say you are tracking "a company". On the other hand, you imply that you have a "portfolio", which is presumably more than one stock. For each stock, Fred's formula can be used to compute the simple weekly (rate of) return. For a portfolio of stocks, you would sum the return (simple or total) of each stock times the "weight" of each stock in the portfolio. The "weight" is usually the stock value as a percentage of the portfolio value (simple or total). We are also supposed to calculate the total rate of return for each stock and portfolio. As you may know, the difference between simple return and total return is usually the inclusion of distributions (e.g. dividends) in the latter, presumed to be reinvested. But you might have a different meaning in mind when you say "total return". If you do, it would behoove you to choose a different term to avoid confusion. And lastly calculate the risk of each stock and the whole portfolio. This is where things get very complicated. First, there are many definitions of "risk", even if you substitute the word "volatility", which is only one possible definition of "risk". Based on the context, I suspect you are studying "modern portfolio theory", or at least a portion of it. In that case, I presume you mean the standard of deviation (sd). But even then, the question is: the sd of what? For individual stocks, "volatility" is usually defined as the sd of the log returns (simple or total), although I have seen some simplified explanations that use the sd of the arithmetic returns (simple or total), which I call the "arithmetic sd". The log return is log(endValue/begValue) or log(1+simpleReturn). The two forms are equivalent mathematically. If the simple returns for 15 weeks are in B2:B15 (yup: that's only 14 returns!), the sd of the log returns is computed by the following array formula: =stdev(log(1+B2:B15)) An array formula is committed using ctrl+shift+Enter instead of Enter. You should see curly braces around the entire formula, i.e. {=formula}. If you make a mistake, "edit" the formula by pressing F2, then press ctrl+shift+Enter. Note that in Excel 2003, I use STDEV instead of STDEVP because you have a sampling of stock prices. (I believe the function names changed in Excel 2007.) For MPT, it is unclear to use the sd of the log returns directly (which I call the "log sd") or the antilog of that (which I call the "geometric sd"). I've seen both used; but I believe the original theory uses the "log sd". The antilog is computed by the following array formula: =10^stdev(log(1+B2:B15)) Note that I use "10^". You might see EXP(STDEV(...)). EXP is appropriate if we used LN(1+B2:B16) instead of LOG(1+B2:B16) -- yet-another dubious factor in how "volatility" (i.e. "log sd") should be defined. It only makes a difference if you use the "log sd" instead of the antilog. That defines the __periodic__ "volatility". For MPT, I believe they usually use the annualized "volatility". The weekly volatility is usually annualized by multiplying by SQRT(52). I believe that applies equally well whether "volatility" is the log sd, geometric or arithmetic sd. (To understand why, you really need to look at probability theory. I could explain it once; but I've long-since forgotten.) That is called the "square root of time" rule. But sometimes, other methods of annualizing volatility are used. For a portfolio, the definition of "volatility" is much more complicated. I won't even try to summarize. See http://en.wikipedia.org/wiki/Modern_portfolio_theory . However, if your class uses a different definition, by all means use it. Nothwithstanding all of this complex "financial engineering", there are many presentations of MPT that simplify various steps in order to make the whole thing tractable. If your class has done so, by all means use the methods defined by your class. I hope that helps. If nothing else, it might offer insight into why your "book and notes don't really explain it as easily" ;-). ----- original message ----- "Robbins" wrote in message ... Here's what i needed to do. We have to follow a company for 15 weeks. Every friday starting on Aug 21, 2009 we had to write down the closing price for the stock that week. Based on the closing prices he wants us to calculate the weekly return for each stock and the whole porfolio. We are also supposed to calculate the total return for each stock and portfolio. And lastly calculate the risk of each stock and the whole portfolio. "Joe User" wrote: "Robbins" wrote: Oh ok i thought that's what it was but our book and notes don't really explain it as easily. Perhaps you should tell us how your book and notes explain it. There are several ways to express periodic returns. Perhaps your instructor is expecting you to follow the method described in your book and notes. In particular, should weekly returns be expressed as percentage change per week (Fred's formula), or an annualized percentage change? Also, it is unclear what you mean by "do the weekly return ... for 15 weeks". If you will be doing some statistical analysis, especially computing volatility, you might be interested in the log return, not the arithmetic return (Fred's formula). ----- original message ----- "Robbins" wrote in message ... Oh ok i thought that's what it was but our book and notes don't really explain it as easily. "Fred Smith" wrote: If you bought something for $100, and one week later sold it for $110, what's your return on investment? Hopefully, you were able to answer instantly that it's 10%. If not, maybe finance is not for you. It's the same with returns on a stock price. The return on investment is always: =(EndingValue - BeginningValue) / BeginningValue Hopefully you can take it from here. But if you need more help, post back. Regards, Fred. "Robbins" wrote in message ... i'm doing a project in my finance class and i need to do the weekly return of the closing price of the companies stock for 15 weeks. Is there a function for this, and if not does anybody know how i can calculate the weekly return |
Weekly Returns
Yea well we did risk in class with variance and standard deviations. And i'd
assume total return for the portfolio is just the average of all the total returns for each closing week of the stock "Joe User" wrote: "Robbins" wrote: We have to follow a company for 15 weeks. [....] he wants us to calculate the weekly return for each stock and the whole porfolio. I'm confused. On the one hand, you say you are tracking "a company". On the other hand, you imply that you have a "portfolio", which is presumably more than one stock. For each stock, Fred's formula can be used to compute the simple weekly (rate of) return. For a portfolio of stocks, you would sum the return (simple or total) of each stock times the "weight" of each stock in the portfolio. The "weight" is usually the stock value as a percentage of the portfolio value (simple or total). We are also supposed to calculate the total rate of return for each stock and portfolio. As you may know, the difference between simple return and total return is usually the inclusion of distributions (e.g. dividends) in the latter, presumed to be reinvested. But you might have a different meaning in mind when you say "total return". If you do, it would behoove you to choose a different term to avoid confusion. And lastly calculate the risk of each stock and the whole portfolio. This is where things get very complicated. First, there are many definitions of "risk", even if you substitute the word "volatility", which is only one possible definition of "risk". Based on the context, I suspect you are studying "modern portfolio theory", or at least a portion of it. In that case, I presume you mean the standard of deviation (sd). But even then, the question is: the sd of what? For individual stocks, "volatility" is usually defined as the sd of the log returns (simple or total), although I have seen some simplified explanations that use the sd of the arithmetic returns (simple or total), which I call the "arithmetic sd". The log return is log(endValue/begValue) or log(1+simpleReturn). The two forms are equivalent mathematically. If the simple returns for 15 weeks are in B2:B15 (yup: that's only 14 returns!), the sd of the log returns is computed by the following array formula: =stdev(log(1+B2:B15)) An array formula is committed using ctrl+shift+Enter instead of Enter. You should see curly braces around the entire formula, i.e. {=formula}. If you make a mistake, "edit" the formula by pressing F2, then press ctrl+shift+Enter. Note that in Excel 2003, I use STDEV instead of STDEVP because you have a sampling of stock prices. (I believe the function names changed in Excel 2007.) For MPT, it is unclear to use the sd of the log returns directly (which I call the "log sd") or the antilog of that (which I call the "geometric sd"). I've seen both used; but I believe the original theory uses the "log sd". The antilog is computed by the following array formula: =10^stdev(log(1+B2:B15)) Note that I use "10^". You might see EXP(STDEV(...)). EXP is appropriate if we used LN(1+B2:B16) instead of LOG(1+B2:B16) -- yet-another dubious factor in how "volatility" (i.e. "log sd") should be defined. It only makes a difference if you use the "log sd" instead of the antilog. That defines the __periodic__ "volatility". For MPT, I believe they usually use the annualized "volatility". The weekly volatility is usually annualized by multiplying by SQRT(52). I believe that applies equally well whether "volatility" is the log sd, geometric or arithmetic sd. (To understand why, you really need to look at probability theory. I could explain it once; but I've long-since forgotten.) That is called the "square root of time" rule. But sometimes, other methods of annualizing volatility are used. For a portfolio, the definition of "volatility" is much more complicated. I won't even try to summarize. See http://en.wikipedia.org/wiki/Modern_portfolio_theory . However, if your class uses a different definition, by all means use it. Nothwithstanding all of this complex "financial engineering", there are many presentations of MPT that simplify various steps in order to make the whole thing tractable. If your class has done so, by all means use the methods defined by your class. I hope that helps. If nothing else, it might offer insight into why your "book and notes don't really explain it as easily" ;-). ----- original message ----- "Robbins" wrote in message ... Here's what i needed to do. We have to follow a company for 15 weeks. Every friday starting on Aug 21, 2009 we had to write down the closing price for the stock that week. Based on the closing prices he wants us to calculate the weekly return for each stock and the whole porfolio. We are also supposed to calculate the total return for each stock and portfolio. And lastly calculate the risk of each stock and the whole portfolio. "Joe User" wrote: "Robbins" wrote: Oh ok i thought that's what it was but our book and notes don't really explain it as easily. Perhaps you should tell us how your book and notes explain it. There are several ways to express periodic returns. Perhaps your instructor is expecting you to follow the method described in your book and notes. In particular, should weekly returns be expressed as percentage change per week (Fred's formula), or an annualized percentage change? Also, it is unclear what you mean by "do the weekly return ... for 15 weeks". If you will be doing some statistical analysis, especially computing volatility, you might be interested in the log return, not the arithmetic return (Fred's formula). ----- original message ----- "Robbins" wrote in message ... Oh ok i thought that's what it was but our book and notes don't really explain it as easily. "Fred Smith" wrote: If you bought something for $100, and one week later sold it for $110, what's your return on investment? Hopefully, you were able to answer instantly that it's 10%. If not, maybe finance is not for you. It's the same with returns on a stock price. The return on investment is always: =(EndingValue - BeginningValue) / BeginningValue Hopefully you can take it from here. But if you need more help, post back. Regards, Fred. "Robbins" wrote in message ... i'm doing a project in my finance class and i need to do the weekly return of the closing price of the companies stock for 15 weeks. Is there a function for this, and if not does anybody know how i can calculate the weekly return . |
Weekly Returns
just the average of all the total returns for each closing week of the
stock No, it's not. If you invest $100 and it's worth $110 at the end of 15 weeks, your return is 10%. It matters not what happened in the intervening time frame. To calculate the total return, it's the same formula. In this case, EndingValue is the stock price at week 15, and beginning value is the stock price at week 1. Regards, Fred "Robbins" wrote in message ... Yea well we did risk in class with variance and standard deviations. And i'd assume total return for the portfolio is just the average of all the total returns for each closing week of the stock "Joe User" wrote: "Robbins" wrote: We have to follow a company for 15 weeks. [....] he wants us to calculate the weekly return for each stock and the whole porfolio. I'm confused. On the one hand, you say you are tracking "a company". On the other hand, you imply that you have a "portfolio", which is presumably more than one stock. For each stock, Fred's formula can be used to compute the simple weekly (rate of) return. For a portfolio of stocks, you would sum the return (simple or total) of each stock times the "weight" of each stock in the portfolio. The "weight" is usually the stock value as a percentage of the portfolio value (simple or total). We are also supposed to calculate the total rate of return for each stock and portfolio. As you may know, the difference between simple return and total return is usually the inclusion of distributions (e.g. dividends) in the latter, presumed to be reinvested. But you might have a different meaning in mind when you say "total return". If you do, it would behoove you to choose a different term to avoid confusion. And lastly calculate the risk of each stock and the whole portfolio. This is where things get very complicated. First, there are many definitions of "risk", even if you substitute the word "volatility", which is only one possible definition of "risk". Based on the context, I suspect you are studying "modern portfolio theory", or at least a portion of it. In that case, I presume you mean the standard of deviation (sd). But even then, the question is: the sd of what? For individual stocks, "volatility" is usually defined as the sd of the log returns (simple or total), although I have seen some simplified explanations that use the sd of the arithmetic returns (simple or total), which I call the "arithmetic sd". The log return is log(endValue/begValue) or log(1+simpleReturn). The two forms are equivalent mathematically. If the simple returns for 15 weeks are in B2:B15 (yup: that's only 14 returns!), the sd of the log returns is computed by the following array formula: =stdev(log(1+B2:B15)) An array formula is committed using ctrl+shift+Enter instead of Enter. You should see curly braces around the entire formula, i.e. {=formula}. If you make a mistake, "edit" the formula by pressing F2, then press ctrl+shift+Enter. Note that in Excel 2003, I use STDEV instead of STDEVP because you have a sampling of stock prices. (I believe the function names changed in Excel 2007.) For MPT, it is unclear to use the sd of the log returns directly (which I call the "log sd") or the antilog of that (which I call the "geometric sd"). I've seen both used; but I believe the original theory uses the "log sd". The antilog is computed by the following array formula: =10^stdev(log(1+B2:B15)) Note that I use "10^". You might see EXP(STDEV(...)). EXP is appropriate if we used LN(1+B2:B16) instead of LOG(1+B2:B16) -- yet-another dubious factor in how "volatility" (i.e. "log sd") should be defined. It only makes a difference if you use the "log sd" instead of the antilog. That defines the __periodic__ "volatility". For MPT, I believe they usually use the annualized "volatility". The weekly volatility is usually annualized by multiplying by SQRT(52). I believe that applies equally well whether "volatility" is the log sd, geometric or arithmetic sd. (To understand why, you really need to look at probability theory. I could explain it once; but I've long-since forgotten.) That is called the "square root of time" rule. But sometimes, other methods of annualizing volatility are used. For a portfolio, the definition of "volatility" is much more complicated. I won't even try to summarize. See http://en.wikipedia.org/wiki/Modern_portfolio_theory . However, if your class uses a different definition, by all means use it. Nothwithstanding all of this complex "financial engineering", there are many presentations of MPT that simplify various steps in order to make the whole thing tractable. If your class has done so, by all means use the methods defined by your class. I hope that helps. If nothing else, it might offer insight into why your "book and notes don't really explain it as easily" ;-). ----- original message ----- "Robbins" wrote in message ... Here's what i needed to do. We have to follow a company for 15 weeks. Every friday starting on Aug 21, 2009 we had to write down the closing price for the stock that week. Based on the closing prices he wants us to calculate the weekly return for each stock and the whole porfolio. We are also supposed to calculate the total return for each stock and portfolio. And lastly calculate the risk of each stock and the whole portfolio. "Joe User" wrote: "Robbins" wrote: Oh ok i thought that's what it was but our book and notes don't really explain it as easily. Perhaps you should tell us how your book and notes explain it. There are several ways to express periodic returns. Perhaps your instructor is expecting you to follow the method described in your book and notes. In particular, should weekly returns be expressed as percentage change per week (Fred's formula), or an annualized percentage change? Also, it is unclear what you mean by "do the weekly return ... for 15 weeks". If you will be doing some statistical analysis, especially computing volatility, you might be interested in the log return, not the arithmetic return (Fred's formula). ----- original message ----- "Robbins" wrote in message ... Oh ok i thought that's what it was but our book and notes don't really explain it as easily. "Fred Smith" wrote: If you bought something for $100, and one week later sold it for $110, what's your return on investment? Hopefully, you were able to answer instantly that it's 10%. If not, maybe finance is not for you. It's the same with returns on a stock price. The return on investment is always: =(EndingValue - BeginningValue) / BeginningValue Hopefully you can take it from here. But if you need more help, post back. Regards, Fred. "Robbins" wrote in message ... i'm doing a project in my finance class and i need to do the weekly return of the closing price of the companies stock for 15 weeks. Is there a function for this, and if not does anybody know how i can calculate the weekly return . |
Weekly Returns
"Robbins" wrote:
i'd assume total return for the portfolio is just the average of all the total returns for each closing week of the stock I am not convinced that you are using the terms "portfolio" and "total return" correctly.. The portfolio return is the __weighted__ average of the returns of each stock, as I said, not the simple average. Consider the following example portfolio. 100 shares of X invested at $10/share ($1000 total), now valued at $11/share ($1100 total). Return: 10% = 1100/1000 - 1. 50 shares of Y invested at $5/share ($250 total), now valued at $10/share ($500 total). Return: 100% = 500/250 - 1. The total investment was $1250. Total portfolio value now is $1600. The simple average of the returns is 55% = (100% + 10%)/2. That is not the portfolio return. But the weighted average is 28% = 10%*1000/1250 + 100%*500/1250. That is the portfolio return. To verify, note that the portfolio return can also be computed by 1600/1250 -1 = 28%. However, I wonder if the disconnect is a terminology problem. Note that a portfolio is a collection of assets (stocks). But you refer to the "total returns ... of the stock" (singular). A typo? Also, the "total return" is based on current stock value plus distributions. If company X distributed dividends of $1/share in the same period, the total return is (1100 + 100)/1000 -1 = 20%. Actually, we usually assume that dividends are reinvested. If the stock value was $10.50 when dividends were reinvested, we would have purchased an additional 100/10.50 = 9.5238 shares. So the actual "total return" is 11*109.5238/1000 - 1 = 20.48%. Alternatively, we could compute the IRR, taking into account the timing of the dividend reinvestment. (But I would not bother if you are tracking weekly returns.) Those are all ways that people use to compute "total return". It's a vague term. But I wonder if you are using the term "total return" to mean "sum of returns" or something like that for a single stock. PS: IMHO, portfolio "risk" (i.e. standard deviation) could be computed based on the weekly portfolio returns as they are computed above, in the same that we determine "risk" (sd) for an individual stock, not the complex formula that financial engineers use. I 'spose the latter is useful if you do not have all the details. But I don't believe the two approaches are mathematically equivalent. ----- original message ----- "Robbins" wrote in message ... Yea well we did risk in class with variance and standard deviations. And i'd assume total return for the portfolio is just the average of all the total returns for each closing week of the stock "Joe User" wrote: "Robbins" wrote: We have to follow a company for 15 weeks. [....] he wants us to calculate the weekly return for each stock and the whole porfolio. I'm confused. On the one hand, you say you are tracking "a company". On the other hand, you imply that you have a "portfolio", which is presumably more than one stock. For each stock, Fred's formula can be used to compute the simple weekly (rate of) return. For a portfolio of stocks, you would sum the return (simple or total) of each stock times the "weight" of each stock in the portfolio. The "weight" is usually the stock value as a percentage of the portfolio value (simple or total). We are also supposed to calculate the total rate of return for each stock and portfolio. As you may know, the difference between simple return and total return is usually the inclusion of distributions (e.g. dividends) in the latter, presumed to be reinvested. But you might have a different meaning in mind when you say "total return". If you do, it would behoove you to choose a different term to avoid confusion. And lastly calculate the risk of each stock and the whole portfolio. This is where things get very complicated. First, there are many definitions of "risk", even if you substitute the word "volatility", which is only one possible definition of "risk". Based on the context, I suspect you are studying "modern portfolio theory", or at least a portion of it. In that case, I presume you mean the standard of deviation (sd). But even then, the question is: the sd of what? For individual stocks, "volatility" is usually defined as the sd of the log returns (simple or total), although I have seen some simplified explanations that use the sd of the arithmetic returns (simple or total), which I call the "arithmetic sd". The log return is log(endValue/begValue) or log(1+simpleReturn). The two forms are equivalent mathematically. If the simple returns for 15 weeks are in B2:B15 (yup: that's only 14 returns!), the sd of the log returns is computed by the following array formula: =stdev(log(1+B2:B15)) An array formula is committed using ctrl+shift+Enter instead of Enter. You should see curly braces around the entire formula, i.e. {=formula}. If you make a mistake, "edit" the formula by pressing F2, then press ctrl+shift+Enter. Note that in Excel 2003, I use STDEV instead of STDEVP because you have a sampling of stock prices. (I believe the function names changed in Excel 2007.) For MPT, it is unclear to use the sd of the log returns directly (which I call the "log sd") or the antilog of that (which I call the "geometric sd"). I've seen both used; but I believe the original theory uses the "log sd". The antilog is computed by the following array formula: =10^stdev(log(1+B2:B15)) Note that I use "10^". You might see EXP(STDEV(...)). EXP is appropriate if we used LN(1+B2:B16) instead of LOG(1+B2:B16) -- yet-another dubious factor in how "volatility" (i.e. "log sd") should be defined. It only makes a difference if you use the "log sd" instead of the antilog. That defines the __periodic__ "volatility". For MPT, I believe they usually use the annualized "volatility". The weekly volatility is usually annualized by multiplying by SQRT(52). I believe that applies equally well whether "volatility" is the log sd, geometric or arithmetic sd. (To understand why, you really need to look at probability theory. I could explain it once; but I've long-since forgotten.) That is called the "square root of time" rule. But sometimes, other methods of annualizing volatility are used. For a portfolio, the definition of "volatility" is much more complicated. I won't even try to summarize. See http://en.wikipedia.org/wiki/Modern_portfolio_theory . However, if your class uses a different definition, by all means use it. Nothwithstanding all of this complex "financial engineering", there are many presentations of MPT that simplify various steps in order to make the whole thing tractable. If your class has done so, by all means use the methods defined by your class. I hope that helps. If nothing else, it might offer insight into why your "book and notes don't really explain it as easily" ;-). ----- original message ----- "Robbins" wrote in message ... Here's what i needed to do. We have to follow a company for 15 weeks. Every friday starting on Aug 21, 2009 we had to write down the closing price for the stock that week. Based on the closing prices he wants us to calculate the weekly return for each stock and the whole porfolio. We are also supposed to calculate the total return for each stock and portfolio. And lastly calculate the risk of each stock and the whole portfolio. "Joe User" wrote: "Robbins" wrote: Oh ok i thought that's what it was but our book and notes don't really explain it as easily. Perhaps you should tell us how your book and notes explain it. There are several ways to express periodic returns. Perhaps your instructor is expecting you to follow the method described in your book and notes. In particular, should weekly returns be expressed as percentage change per week (Fred's formula), or an annualized percentage change? Also, it is unclear what you mean by "do the weekly return ... for 15 weeks". If you will be doing some statistical analysis, especially computing volatility, you might be interested in the log return, not the arithmetic return (Fred's formula). ----- original message ----- "Robbins" wrote in message ... Oh ok i thought that's what it was but our book and notes don't really explain it as easily. "Fred Smith" wrote: If you bought something for $100, and one week later sold it for $110, what's your return on investment? Hopefully, you were able to answer instantly that it's 10%. If not, maybe finance is not for you. It's the same with returns on a stock price. The return on investment is always: =(EndingValue - BeginningValue) / BeginningValue Hopefully you can take it from here. But if you need more help, post back. Regards, Fred. "Robbins" wrote in message ... i'm doing a project in my finance class and i need to do the weekly return of the closing price of the companies stock for 15 weeks. Is there a function for this, and if not does anybody know how i can calculate the weekly return . |
Weekly Returns
Now i understand what's going on. I got clarification from my professor and
he said we had to get the closing price of the 5 stocks we picked in the beginning of the semester. It makes sense now since the project information doesn't say for 5 stocks. "Joe User" wrote: "Robbins" wrote: i'd assume total return for the portfolio is just the average of all the total returns for each closing week of the stock I am not convinced that you are using the terms "portfolio" and "total return" correctly.. The portfolio return is the __weighted__ average of the returns of each stock, as I said, not the simple average. Consider the following example portfolio. 100 shares of X invested at $10/share ($1000 total), now valued at $11/share ($1100 total). Return: 10% = 1100/1000 - 1. 50 shares of Y invested at $5/share ($250 total), now valued at $10/share ($500 total). Return: 100% = 500/250 - 1. The total investment was $1250. Total portfolio value now is $1600. The simple average of the returns is 55% = (100% + 10%)/2. That is not the portfolio return. But the weighted average is 28% = 10%*1000/1250 + 100%*500/1250. That is the portfolio return. To verify, note that the portfolio return can also be computed by 1600/1250 -1 = 28%. However, I wonder if the disconnect is a terminology problem. Note that a portfolio is a collection of assets (stocks). But you refer to the "total returns ... of the stock" (singular). A typo? Also, the "total return" is based on current stock value plus distributions. If company X distributed dividends of $1/share in the same period, the total return is (1100 + 100)/1000 -1 = 20%. Actually, we usually assume that dividends are reinvested. If the stock value was $10.50 when dividends were reinvested, we would have purchased an additional 100/10.50 = 9.5238 shares. So the actual "total return" is 11*109.5238/1000 - 1 = 20.48%. Alternatively, we could compute the IRR, taking into account the timing of the dividend reinvestment. (But I would not bother if you are tracking weekly returns.) Those are all ways that people use to compute "total return". It's a vague term. But I wonder if you are using the term "total return" to mean "sum of returns" or something like that for a single stock. PS: IMHO, portfolio "risk" (i.e. standard deviation) could be computed based on the weekly portfolio returns as they are computed above, in the same that we determine "risk" (sd) for an individual stock, not the complex formula that financial engineers use. I 'spose the latter is useful if you do not have all the details. But I don't believe the two approaches are mathematically equivalent. ----- original message ----- "Robbins" wrote in message ... Yea well we did risk in class with variance and standard deviations. And i'd assume total return for the portfolio is just the average of all the total returns for each closing week of the stock "Joe User" wrote: "Robbins" wrote: We have to follow a company for 15 weeks. [....] he wants us to calculate the weekly return for each stock and the whole porfolio. I'm confused. On the one hand, you say you are tracking "a company". On the other hand, you imply that you have a "portfolio", which is presumably more than one stock. For each stock, Fred's formula can be used to compute the simple weekly (rate of) return. For a portfolio of stocks, you would sum the return (simple or total) of each stock times the "weight" of each stock in the portfolio. The "weight" is usually the stock value as a percentage of the portfolio value (simple or total). We are also supposed to calculate the total rate of return for each stock and portfolio. As you may know, the difference between simple return and total return is usually the inclusion of distributions (e.g. dividends) in the latter, presumed to be reinvested. But you might have a different meaning in mind when you say "total return". If you do, it would behoove you to choose a different term to avoid confusion. And lastly calculate the risk of each stock and the whole portfolio. This is where things get very complicated. First, there are many definitions of "risk", even if you substitute the word "volatility", which is only one possible definition of "risk". Based on the context, I suspect you are studying "modern portfolio theory", or at least a portion of it. In that case, I presume you mean the standard of deviation (sd). But even then, the question is: the sd of what? For individual stocks, "volatility" is usually defined as the sd of the log returns (simple or total), although I have seen some simplified explanations that use the sd of the arithmetic returns (simple or total), which I call the "arithmetic sd". The log return is log(endValue/begValue) or log(1+simpleReturn). The two forms are equivalent mathematically. If the simple returns for 15 weeks are in B2:B15 (yup: that's only 14 returns!), the sd of the log returns is computed by the following array formula: =stdev(log(1+B2:B15)) An array formula is committed using ctrl+shift+Enter instead of Enter. You should see curly braces around the entire formula, i.e. {=formula}. If you make a mistake, "edit" the formula by pressing F2, then press ctrl+shift+Enter. Note that in Excel 2003, I use STDEV instead of STDEVP because you have a sampling of stock prices. (I believe the function names changed in Excel 2007.) For MPT, it is unclear to use the sd of the log returns directly (which I call the "log sd") or the antilog of that (which I call the "geometric sd"). I've seen both used; but I believe the original theory uses the "log sd". The antilog is computed by the following array formula: =10^stdev(log(1+B2:B15)) Note that I use "10^". You might see EXP(STDEV(...)). EXP is appropriate if we used LN(1+B2:B16) instead of LOG(1+B2:B16) -- yet-another dubious factor in how "volatility" (i.e. "log sd") should be defined. It only makes a difference if you use the "log sd" instead of the antilog. That defines the __periodic__ "volatility". For MPT, I believe they usually use the annualized "volatility". The weekly volatility is usually annualized by multiplying by SQRT(52). I believe that applies equally well whether "volatility" is the log sd, geometric or arithmetic sd. (To understand why, you really need to look at probability theory. I could explain it once; but I've long-since forgotten.) That is called the "square root of time" rule. But sometimes, other methods of annualizing volatility are used. For a portfolio, the definition of "volatility" is much more complicated. I won't even try to summarize. See http://en.wikipedia.org/wiki/Modern_portfolio_theory . However, if your class uses a different definition, by all means use it. Nothwithstanding all of this complex "financial engineering", there are many presentations of MPT that simplify various steps in order to make the whole thing tractable. If your class has done so, by all means use the methods defined by your class. I hope that helps. If nothing else, it might offer insight into why your "book and notes don't really explain it as easily" ;-). ----- original message ----- "Robbins" wrote in message ... Here's what i needed to do. We have to follow a company for 15 weeks. Every friday starting on Aug 21, 2009 we had to write down the closing price for the stock that week. Based on the closing prices he wants us to calculate the weekly return for each stock and the whole porfolio. We are also supposed to calculate the total return for each stock and portfolio. And lastly calculate the risk of each stock and the whole portfolio. "Joe User" wrote: "Robbins" wrote: Oh ok i thought that's what it was but our book and notes don't really explain it as easily. Perhaps you should tell us how your book and notes explain it. There are several ways to express periodic returns. Perhaps your instructor is expecting you to follow the method described in your book and notes. In particular, should weekly returns be expressed as percentage change per week (Fred's formula), or an annualized percentage change? Also, it is unclear what you mean by "do the weekly return ... for 15 weeks". If you will be doing some statistical analysis, especially computing volatility, you might be interested in the log return, not the arithmetic return (Fred's formula). ----- original message ----- "Robbins" wrote in message ... Oh ok i thought that's what it was but our book and notes don't really explain it as easily. "Fred Smith" wrote: If you bought something for $100, and one week later sold it for $110, what's your return on investment? Hopefully, you were able to answer instantly that it's 10%. If not, maybe finance is not for you. It's the same with returns on a stock price. The return on investment is always: =(EndingValue - BeginningValue) / BeginningValue Hopefully you can take it from here. But if you need more help, post back. Regards, Fred. "Robbins" wrote in message ... i'm doing a project in my finance class and i need to do the weekly return of the closing price of the companies stock for 15 weeks. Is there a function for this, and if not does anybody know how i can calculate the weekly return . . |
Weekly Returns
"Robbins" wrote:
Now i understand what's going on. I got clarification from my professor Good. I am glad to hear my comments stimulated you to get clarification on your assignment. Are all your questions answered now? If you have further related questions, I suggest that you post a follow-up to this thread instead of starting a new thread. One last thought, which I offer with some trepidation because it might muddy the water. If you do not understand the following, you might ignore it or seek clarification from your instructor. It occurred to me that "totat return" might have another (uncommon) interpretation in this context, as might "average of all the total returns". Consider the following example. (Note: I purposely use "total" in an uncommon way to demonstrate the potential for confusing terminology.) Suppose the closing price of stock for company X in each of 5 weeks is 10.00, 10.50, 9.50, 10.25, and 9.75. The weekly rates of return are about 5.00%, -9.52%, 7.89% and -4.88% (e.g. 9.75/10.25 - 1). But the "total return" for the period -- that is, the simple return for the 5-week period -- is about -2.50%; that is, 9.75/10 - 1. If you have only the weekly rates of return in B2:B5, say, this can also be computed by the array expression (ctrl+shift+Enter) PRODUCT(1+B2:B5)-1. Moreover, the "average total return" for the period -- i.e. the compounded average weekly return, aka CAGR, for the 5-week period -- is about -0.63% -- (9.75/10)^(1/4) - 1. This can also be computed by the array expression GEOMEAN(1+B2:B5)-1. Note that the CAGR is different from the simple arithmetic average of about -0.38%, computed by AVERAGE(B2:B5). However, arguably, the arithmetic average can be the right "average total return" to use for some purposes, e.g. Monte Carlo simulation. Finally, each of those rates might be annualized in any of several ways, all in common usage. One typical method: (1+r)^52 - 1, where "r" is a weekly rate. ----- original message ----- "Robbins" wrote in message ... Now i understand what's going on. I got clarification from my professor and he said we had to get the closing price of the 5 stocks we picked in the beginning of the semester. It makes sense now since the project information doesn't say for 5 stocks. "Joe User" wrote: "Robbins" wrote: i'd assume total return for the portfolio is just the average of all the total returns for each closing week of the stock I am not convinced that you are using the terms "portfolio" and "total return" correctly.. The portfolio return is the __weighted__ average of the returns of each stock, as I said, not the simple average. Consider the following example portfolio. 100 shares of X invested at $10/share ($1000 total), now valued at $11/share ($1100 total). Return: 10% = 1100/1000 - 1. 50 shares of Y invested at $5/share ($250 total), now valued at $10/share ($500 total). Return: 100% = 500/250 - 1. The total investment was $1250. Total portfolio value now is $1600. The simple average of the returns is 55% = (100% + 10%)/2. That is not the portfolio return. But the weighted average is 28% = 10%*1000/1250 + 100%*500/1250. That is the portfolio return. To verify, note that the portfolio return can also be computed by 1600/1250 -1 = 28%. However, I wonder if the disconnect is a terminology problem. Note that a portfolio is a collection of assets (stocks). But you refer to the "total returns ... of the stock" (singular). A typo? Also, the "total return" is based on current stock value plus distributions. If company X distributed dividends of $1/share in the same period, the total return is (1100 + 100)/1000 -1 = 20%. Actually, we usually assume that dividends are reinvested. If the stock value was $10.50 when dividends were reinvested, we would have purchased an additional 100/10.50 = 9.5238 shares. So the actual "total return" is 11*109.5238/1000 - 1 = 20.48%. Alternatively, we could compute the IRR, taking into account the timing of the dividend reinvestment. (But I would not bother if you are tracking weekly returns.) Those are all ways that people use to compute "total return". It's a vague term. But I wonder if you are using the term "total return" to mean "sum of returns" or something like that for a single stock. PS: IMHO, portfolio "risk" (i.e. standard deviation) could be computed based on the weekly portfolio returns as they are computed above, in the same that we determine "risk" (sd) for an individual stock, not the complex formula that financial engineers use. I 'spose the latter is useful if you do not have all the details. But I don't believe the two approaches are mathematically equivalent. ----- original message ----- "Robbins" wrote in message ... Yea well we did risk in class with variance and standard deviations. And i'd assume total return for the portfolio is just the average of all the total returns for each closing week of the stock "Joe User" wrote: "Robbins" wrote: We have to follow a company for 15 weeks. [....] he wants us to calculate the weekly return for each stock and the whole porfolio. I'm confused. On the one hand, you say you are tracking "a company". On the other hand, you imply that you have a "portfolio", which is presumably more than one stock. For each stock, Fred's formula can be used to compute the simple weekly (rate of) return. For a portfolio of stocks, you would sum the return (simple or total) of each stock times the "weight" of each stock in the portfolio. The "weight" is usually the stock value as a percentage of the portfolio value (simple or total). We are also supposed to calculate the total rate of return for each stock and portfolio. As you may know, the difference between simple return and total return is usually the inclusion of distributions (e.g. dividends) in the latter, presumed to be reinvested. But you might have a different meaning in mind when you say "total return". If you do, it would behoove you to choose a different term to avoid confusion. And lastly calculate the risk of each stock and the whole portfolio. This is where things get very complicated. First, there are many definitions of "risk", even if you substitute the word "volatility", which is only one possible definition of "risk". Based on the context, I suspect you are studying "modern portfolio theory", or at least a portion of it. In that case, I presume you mean the standard of deviation (sd). But even then, the question is: the sd of what? For individual stocks, "volatility" is usually defined as the sd of the log returns (simple or total), although I have seen some simplified explanations that use the sd of the arithmetic returns (simple or total), which I call the "arithmetic sd". The log return is log(endValue/begValue) or log(1+simpleReturn). The two forms are equivalent mathematically. If the simple returns for 15 weeks are in B2:B15 (yup: that's only 14 returns!), the sd of the log returns is computed by the following array formula: =stdev(log(1+B2:B15)) An array formula is committed using ctrl+shift+Enter instead of Enter. You should see curly braces around the entire formula, i.e. {=formula}. If you make a mistake, "edit" the formula by pressing F2, then press ctrl+shift+Enter. Note that in Excel 2003, I use STDEV instead of STDEVP because you have a sampling of stock prices. (I believe the function names changed in Excel 2007.) For MPT, it is unclear to use the sd of the log returns directly (which I call the "log sd") or the antilog of that (which I call the "geometric sd"). I've seen both used; but I believe the original theory uses the "log sd". The antilog is computed by the following array formula: =10^stdev(log(1+B2:B15)) Note that I use "10^". You might see EXP(STDEV(...)). EXP is appropriate if we used LN(1+B2:B16) instead of LOG(1+B2:B16) -- yet-another dubious factor in how "volatility" (i.e. "log sd") should be defined. It only makes a difference if you use the "log sd" instead of the antilog. That defines the __periodic__ "volatility". For MPT, I believe they usually use the annualized "volatility". The weekly volatility is usually annualized by multiplying by SQRT(52). I believe that applies equally well whether "volatility" is the log sd, geometric or arithmetic sd. (To understand why, you really need to look at probability theory. I could explain it once; but I've long-since forgotten.) That is called the "square root of time" rule. But sometimes, other methods of annualizing volatility are used. For a portfolio, the definition of "volatility" is much more complicated. I won't even try to summarize. See http://en.wikipedia.org/wiki/Modern_portfolio_theory . However, if your class uses a different definition, by all means use it. Nothwithstanding all of this complex "financial engineering", there are many presentations of MPT that simplify various steps in order to make the whole thing tractable. If your class has done so, by all means use the methods defined by your class. I hope that helps. If nothing else, it might offer insight into why your "book and notes don't really explain it as easily" ;-). ----- original message ----- "Robbins" wrote in message ... Here's what i needed to do. We have to follow a company for 15 weeks. Every friday starting on Aug 21, 2009 we had to write down the closing price for the stock that week. Based on the closing prices he wants us to calculate the weekly return for each stock and the whole porfolio. We are also supposed to calculate the total return for each stock and portfolio. And lastly calculate the risk of each stock and the whole portfolio. "Joe User" wrote: "Robbins" wrote: Oh ok i thought that's what it was but our book and notes don't really explain it as easily. Perhaps you should tell us how your book and notes explain it. There are several ways to express periodic returns. Perhaps your instructor is expecting you to follow the method described in your book and notes. In particular, should weekly returns be expressed as percentage change per week (Fred's formula), or an annualized percentage change? Also, it is unclear what you mean by "do the weekly return ... for 15 weeks". If you will be doing some statistical analysis, especially computing volatility, you might be interested in the log return, not the arithmetic return (Fred's formula). ----- original message ----- "Robbins" wrote in message ... Oh ok i thought that's what it was but our book and notes don't really explain it as easily. "Fred Smith" wrote: If you bought something for $100, and one week later sold it for $110, what's your return on investment? Hopefully, you were able to answer instantly that it's 10%. If not, maybe finance is not for you. It's the same with returns on a stock price. The return on investment is always: =(EndingValue - BeginningValue) / BeginningValue Hopefully you can take it from here. But if you need more help, post back. Regards, Fred. "Robbins" wrote in message ... i'm doing a project in my finance class and i need to do the weekly return of the closing price of the companies stock for 15 weeks. Is there a function for this, and if not does anybody know how i can calculate the weekly return . . |
Weekly Returns
Sorry i'm not sure how to make a follow up thread. So just to make sure i'm
doing this right, the total weekly return for each stock would be the last stock price for dec 4 minus the very beginning price of aug 21 divided by the aug 21 closing price. And i'd assume that would be the same for total return. "Joe User" wrote: "Robbins" wrote: Now i understand what's going on. I got clarification from my professor Good. I am glad to hear my comments stimulated you to get clarification on your assignment. Are all your questions answered now? If you have further related questions, I suggest that you post a follow-up to this thread instead of starting a new thread. One last thought, which I offer with some trepidation because it might muddy the water. If you do not understand the following, you might ignore it or seek clarification from your instructor. It occurred to me that "totat return" might have another (uncommon) interpretation in this context, as might "average of all the total returns". Consider the following example. (Note: I purposely use "total" in an uncommon way to demonstrate the potential for confusing terminology.) Suppose the closing price of stock for company X in each of 5 weeks is 10.00, 10.50, 9.50, 10.25, and 9.75. The weekly rates of return are about 5.00%, -9.52%, 7.89% and -4.88% (e.g. 9.75/10.25 - 1). But the "total return" for the period -- that is, the simple return for the 5-week period -- is about -2.50%; that is, 9.75/10 - 1. If you have only the weekly rates of return in B2:B5, say, this can also be computed by the array expression (ctrl+shift+Enter) PRODUCT(1+B2:B5)-1. Moreover, the "average total return" for the period -- i.e. the compounded average weekly return, aka CAGR, for the 5-week period -- is about -0.63% -- (9.75/10)^(1/4) - 1. This can also be computed by the array expression GEOMEAN(1+B2:B5)-1. Note that the CAGR is different from the simple arithmetic average of about -0.38%, computed by AVERAGE(B2:B5). However, arguably, the arithmetic average can be the right "average total return" to use for some purposes, e.g. Monte Carlo simulation. Finally, each of those rates might be annualized in any of several ways, all in common usage. One typical method: (1+r)^52 - 1, where "r" is a weekly rate. ----- original message ----- "Robbins" wrote in message ... Now i understand what's going on. I got clarification from my professor and he said we had to get the closing price of the 5 stocks we picked in the beginning of the semester. It makes sense now since the project information doesn't say for 5 stocks. "Joe User" wrote: "Robbins" wrote: i'd assume total return for the portfolio is just the average of all the total returns for each closing week of the stock I am not convinced that you are using the terms "portfolio" and "total return" correctly.. The portfolio return is the __weighted__ average of the returns of each stock, as I said, not the simple average. Consider the following example portfolio. 100 shares of X invested at $10/share ($1000 total), now valued at $11/share ($1100 total). Return: 10% = 1100/1000 - 1. 50 shares of Y invested at $5/share ($250 total), now valued at $10/share ($500 total). Return: 100% = 500/250 - 1. The total investment was $1250. Total portfolio value now is $1600. The simple average of the returns is 55% = (100% + 10%)/2. That is not the portfolio return. But the weighted average is 28% = 10%*1000/1250 + 100%*500/1250. That is the portfolio return. To verify, note that the portfolio return can also be computed by 1600/1250 -1 = 28%. However, I wonder if the disconnect is a terminology problem. Note that a portfolio is a collection of assets (stocks). But you refer to the "total returns ... of the stock" (singular). A typo? Also, the "total return" is based on current stock value plus distributions. If company X distributed dividends of $1/share in the same period, the total return is (1100 + 100)/1000 -1 = 20%. Actually, we usually assume that dividends are reinvested. If the stock value was $10.50 when dividends were reinvested, we would have purchased an additional 100/10.50 = 9.5238 shares. So the actual "total return" is 11*109.5238/1000 - 1 = 20.48%. Alternatively, we could compute the IRR, taking into account the timing of the dividend reinvestment. (But I would not bother if you are tracking weekly returns.) Those are all ways that people use to compute "total return". It's a vague term. But I wonder if you are using the term "total return" to mean "sum of returns" or something like that for a single stock. PS: IMHO, portfolio "risk" (i.e. standard deviation) could be computed based on the weekly portfolio returns as they are computed above, in the same that we determine "risk" (sd) for an individual stock, not the complex formula that financial engineers use. I 'spose the latter is useful if you do not have all the details. But I don't believe the two approaches are mathematically equivalent. ----- original message ----- "Robbins" wrote in message ... Yea well we did risk in class with variance and standard deviations. And i'd assume total return for the portfolio is just the average of all the total returns for each closing week of the stock "Joe User" wrote: "Robbins" wrote: We have to follow a company for 15 weeks. [....] he wants us to calculate the weekly return for each stock and the whole porfolio. I'm confused. On the one hand, you say you are tracking "a company". On the other hand, you imply that you have a "portfolio", which is presumably more than one stock. For each stock, Fred's formula can be used to compute the simple weekly (rate of) return. For a portfolio of stocks, you would sum the return (simple or total) of each stock times the "weight" of each stock in the portfolio. The "weight" is usually the stock value as a percentage of the portfolio value (simple or total). We are also supposed to calculate the total rate of return for each stock and portfolio. As you may know, the difference between simple return and total return is usually the inclusion of distributions (e.g. dividends) in the latter, presumed to be reinvested. But you might have a different meaning in mind when you say "total return". If you do, it would behoove you to choose a different term to avoid confusion. And lastly calculate the risk of each stock and the whole portfolio. This is where things get very complicated. First, there are many definitions of "risk", even if you substitute the word "volatility", which is only one possible definition of "risk". Based on the context, I suspect you are studying "modern portfolio theory", or at least a portion of it. In that case, I presume you mean the standard of deviation (sd). But even then, the question is: the sd of what? For individual stocks, "volatility" is usually defined as the sd of the log returns (simple or total), although I have seen some simplified explanations that use the sd of the arithmetic returns (simple or total), which I call the "arithmetic sd". The log return is log(endValue/begValue) or log(1+simpleReturn). The two forms are equivalent mathematically. If the simple returns for 15 weeks are in B2:B15 (yup: that's only 14 returns!), the sd of the log returns is computed by the following array formula: =stdev(log(1+B2:B15)) An array formula is committed using ctrl+shift+Enter instead of Enter. You should see curly braces around the entire formula, i.e. {=formula}. If you make a mistake, "edit" the formula by pressing F2, then press ctrl+shift+Enter. Note that in Excel 2003, I use STDEV instead of STDEVP because you have a sampling of stock prices. (I believe the function names changed in Excel 2007.) For MPT, it is unclear to use the sd of the log returns directly (which I call the "log sd") or the antilog of that (which I call the "geometric sd"). I've seen both used; but I believe the original theory uses the "log sd". The antilog is computed by the following array formula: =10^stdev(log(1+B2:B15)) Note that I use "10^". You might see EXP(STDEV(...)). EXP is appropriate if we used LN(1+B2:B16) instead of LOG(1+B2:B16) -- yet-another dubious factor in how "volatility" (i.e. "log sd") should be defined. It only makes a difference if you use the "log sd" instead of the antilog. That defines the __periodic__ "volatility". For MPT, I believe they usually use the annualized "volatility". The weekly volatility is usually annualized by multiplying by SQRT(52). I believe that applies equally well whether "volatility" is the log sd, geometric or arithmetic sd. (To understand why, you really need to look at probability theory. I could explain it once; but I've long-since forgotten.) That is called the "square root of time" rule. But sometimes, other methods of annualizing volatility are used. For a portfolio, the definition of "volatility" is much more complicated. I won't even try to summarize. See http://en.wikipedia.org/wiki/Modern_portfolio_theory . However, if your class uses a different definition, by all means use it. Nothwithstanding all of this complex "financial engineering", there are many presentations of MPT that simplify various steps in order to make the whole thing tractable. If your class has done so, by all means use the methods defined by your class. I hope that helps. If nothing else, it might offer insight into why your "book and notes don't really explain it as easily" ;-). ----- original message ----- "Robbins" wrote in message ... Here's what i needed to do. We have to follow a company for 15 weeks. Every friday starting on Aug 21, 2009 we had to write down the closing price for the stock that week. Based on the closing prices he wants us to calculate the weekly return for each stock and the whole porfolio. We are also supposed |
Weekly Returns
"Robbins" wrote:
Sorry i'm not sure how to make a follow up thread. You're doing it just fine. "Follow up" and "thread" are techie terms for "response" and "discussion". the total weekly return for each stock would be the last stock price for dec 4 minus the very beginning price of aug 21 divided by the aug 21 closing price. Yes, that is how to compute the "simple return" for the period from Aug 21 through Dec 4. And frankly, that is the terminology I would use -- or more simply, the "15-week simple return". Generally, the term "total return" reflects that change in value plus any distributions (e.g. dividends). Of course, we can assume there are no distributions. But I would be careful your use of the term. You are likely to confuse people, especially since I believe you are confused. That is certainly not the total "weekly" return. That term is meaningless in this context; but if it had meaning, it would be the change in value of the stock for one week plus any distributions that week. Since we are assuming there are no distributions, the term "total weekly return" is synonymous with "weekly return" -- which is not how you seem to be using those terms, incorrectly. And i'd assume that would be the same for total return. I think you believe those are two different values. My guess is you believe "total return" refers to the portfolio of stocks, whereas "total weekly return" refers to a single stock. That dichotomy is incorrect. The correct terms are "total return for a stock" (or "a stock's total return") v. "total return for a portfolio" (or "a portfolio's total return"). And yes, as I explained by example previously, assuming no distributions, the portfolio's 15-week total return can be computed by the sum of the stock values (stock price times number of shares held) on Dec 4 divided by the sum of stock values on Aug 21, then minus one. By the way, A/B - 1 is mathematically equal to (A-B)/B. ----- original message ----- "Robbins" wrote in message ... Sorry i'm not sure how to make a follow up thread. So just to make sure i'm doing this right, the total weekly return for each stock would be the last stock price for dec 4 minus the very beginning price of aug 21 divided by the aug 21 closing price. And i'd assume that would be the same for total return. "Joe User" wrote: "Robbins" wrote: Now i understand what's going on. I got clarification from my professor Good. I am glad to hear my comments stimulated you to get clarification on your assignment. Are all your questions answered now? If you have further related questions, I suggest that you post a follow-up to this thread instead of starting a new thread. One last thought, which I offer with some trepidation because it might muddy the water. If you do not understand the following, you might ignore it or seek clarification from your instructor. It occurred to me that "totat return" might have another (uncommon) interpretation in this context, as might "average of all the total returns". Consider the following example. (Note: I purposely use "total" in an uncommon way to demonstrate the potential for confusing terminology.) Suppose the closing price of stock for company X in each of 5 weeks is 10.00, 10.50, 9.50, 10.25, and 9.75. The weekly rates of return are about 5.00%, -9.52%, 7.89% and -4.88% (e.g. 9.75/10.25 - 1). But the "total return" for the period -- that is, the simple return for the 5-week period -- is about -2.50%; that is, 9.75/10 - 1. If you have only the weekly rates of return in B2:B5, say, this can also be computed by the array expression (ctrl+shift+Enter) PRODUCT(1+B2:B5)-1. Moreover, the "average total return" for the period -- i.e. the compounded average weekly return, aka CAGR, for the 5-week period -- is about -0.63% -- (9.75/10)^(1/4) - 1. This can also be computed by the array expression GEOMEAN(1+B2:B5)-1. Note that the CAGR is different from the simple arithmetic average of about -0.38%, computed by AVERAGE(B2:B5). However, arguably, the arithmetic average can be the right "average total return" to use for some purposes, e.g. Monte Carlo simulation. Finally, each of those rates might be annualized in any of several ways, all in common usage. One typical method: (1+r)^52 - 1, where "r" is a weekly rate. ----- original message ----- "Robbins" wrote in message ... Now i understand what's going on. I got clarification from my professor and he said we had to get the closing price of the 5 stocks we picked in the beginning of the semester. It makes sense now since the project information doesn't say for 5 stocks. "Joe User" wrote: "Robbins" wrote: i'd assume total return for the portfolio is just the average of all the total returns for each closing week of the stock I am not convinced that you are using the terms "portfolio" and "total return" correctly.. The portfolio return is the __weighted__ average of the returns of each stock, as I said, not the simple average. Consider the following example portfolio. 100 shares of X invested at $10/share ($1000 total), now valued at $11/share ($1100 total). Return: 10% = 1100/1000 - 1. 50 shares of Y invested at $5/share ($250 total), now valued at $10/share ($500 total). Return: 100% = 500/250 - 1. The total investment was $1250. Total portfolio value now is $1600. The simple average of the returns is 55% = (100% + 10%)/2. That is not the portfolio return. But the weighted average is 28% = 10%*1000/1250 + 100%*500/1250. That is the portfolio return. To verify, note that the portfolio return can also be computed by 1600/1250 -1 = 28%. However, I wonder if the disconnect is a terminology problem. Note that a portfolio is a collection of assets (stocks). But you refer to the "total returns ... of the stock" (singular). A typo? Also, the "total return" is based on current stock value plus distributions. If company X distributed dividends of $1/share in the same period, the total return is (1100 + 100)/1000 -1 = 20%. Actually, we usually assume that dividends are reinvested. If the stock value was $10.50 when dividends were reinvested, we would have purchased an additional 100/10.50 = 9.5238 shares. So the actual "total return" is 11*109.5238/1000 - 1 = 20.48%. Alternatively, we could compute the IRR, taking into account the timing of the dividend reinvestment. (But I would not bother if you are tracking weekly returns.) Those are all ways that people use to compute "total return". It's a vague term. But I wonder if you are using the term "total return" to mean "sum of returns" or something like that for a single stock. PS: IMHO, portfolio "risk" (i.e. standard deviation) could be computed based on the weekly portfolio returns as they are computed above, in the same that we determine "risk" (sd) for an individual stock, not the complex formula that financial engineers use. I 'spose the latter is useful if you do not have all the details. But I don't believe the two approaches are mathematically equivalent. ----- original message ----- "Robbins" wrote in message ... Yea well we did risk in class with variance and standard deviations. And i'd assume total return for the portfolio is just the average of all the total returns for each closing week of the stock "Joe User" wrote: "Robbins" wrote: We have to follow a company for 15 weeks. [....] he wants us to calculate the weekly return for each stock and the whole porfolio. I'm confused. On the one hand, you say you are tracking "a company". On the other hand, you imply that you have a "portfolio", which is presumably more than one stock. For each stock, Fred's formula can be used to compute the simple weekly (rate of) return. For a portfolio of stocks, you would sum the return (simple or total) of each stock times the "weight" of each stock in the portfolio. The "weight" is usually the stock value as a percentage of the portfolio value (simple or total). We are also supposed to calculate the total rate of return for each stock and portfolio. As you may know, the difference between simple return and total return is usually the inclusion of distributions (e.g. dividends) in the latter, presumed to be reinvested. But you might have a different meaning in mind when you say "total return". If you do, it would behoove you to choose a different term to avoid confusion. And lastly calculate the risk of each stock and the whole portfolio. This is where things get very complicated. First, there are many definitions of "risk", even if you substitute the word "volatility", which is only one possible definition of "risk". Based on the context, I suspect you are studying "modern portfolio theory", or at least a portion of it. In that case, I presume you mean the standard of deviation (sd). But even then, the question is: the sd of what? For individual stocks, "volatility" is usually defined as the sd of the log returns (simple or total), although I have seen some simplified explanations that use the sd of the arithmetic returns (simple or total), which I call the "arithmetic sd". The log return is log(endValue/begValue) or log(1+simpleReturn). The two forms are equivalent mathematically. If the simple returns for 15 weeks are in B2:B15 (yup: that's only 14 returns!), the sd of the log returns is computed by the following array formula: =stdev(log(1+B2:B15)) An array formula is committed using ctrl+shift+Enter instead of Enter. You should see curly braces around the entire formula, i.e. {=formula}. If you make a mistake, "edit" the formula by pressing F2, then press ctrl+shift+Enter. Note that in Excel 2003, I use STDEV instead of STDEVP because you have a sampling of stock prices. (I believe the function names changed in Excel 2007.) For MPT, it is unclear to use the sd of the log returns directly (which I call the "log sd") or the antilog of that (which I call the "geometric sd"). I've seen both used; but I believe the original theory uses the "log sd". The antilog is computed by the following array formula: =10^stdev(log(1+B2:B15)) Note that I use "10^". You might see EXP(STDEV(...)). EXP is appropriate if we used LN(1+B2:B16) instead of LOG(1+B2:B16) -- yet-another dubious factor in how "volatility" (i.e. "log sd") should be defined. It only makes a difference if you use the "log sd" instead of the antilog. That defines the __periodic__ "volatility". For MPT, I believe they usually use the annualized "volatility". The weekly volatility is usually annualized by multiplying by SQRT(52). I believe that applies equally well whether "volatility" is the log sd, geometric or arithmetic sd. (To understand why, you really need to look at probability theory. I could explain it once; but I've long-since forgotten.) That is called the "square root of time" rule. But sometimes, other methods of annualizing volatility are used. For a portfolio, the definition of "volatility" is much more complicated. I won't even try to summarize. See http://en.wikipedia.org/wiki/Modern_portfolio_theory . However, if your class uses a different definition, by all means use it. Nothwithstanding all of this complex "financial engineering", there are many presentations of MPT that simplify various steps in order to make the whole thing tractable. If your class has done so, by all means use the methods defined by your class. I hope that helps. If nothing else, it might offer insight into why your "book and notes don't really explain it as easily" ;-). ----- original message ----- "Robbins" wrote in message ... Here's what i needed to do. We have to follow a company for 15 weeks. Every friday starting on Aug 21, 2009 we had to write down the closing price for the stock that week. Based on the closing prices he wants us to calculate the weekly return for each stock and the whole porfolio. We are also supposed |
Weekly Returns
Ok i've got the hang of it now. Sorry if i was confusing you with the
terminology. Thanks for your help "Joe User" wrote: "Robbins" wrote: Sorry i'm not sure how to make a follow up thread. You're doing it just fine. "Follow up" and "thread" are techie terms for "response" and "discussion". the total weekly return for each stock would be the last stock price for dec 4 minus the very beginning price of aug 21 divided by the aug 21 closing price. Yes, that is how to compute the "simple return" for the period from Aug 21 through Dec 4. And frankly, that is the terminology I would use -- or more simply, the "15-week simple return". Generally, the term "total return" reflects that change in value plus any distributions (e.g. dividends). Of course, we can assume there are no distributions. But I would be careful your use of the term. You are likely to confuse people, especially since I believe you are confused. That is certainly not the total "weekly" return. That term is meaningless in this context; but if it had meaning, it would be the change in value of the stock for one week plus any distributions that week. Since we are assuming there are no distributions, the term "total weekly return" is synonymous with "weekly return" -- which is not how you seem to be using those terms, incorrectly. And i'd assume that would be the same for total return. I think you believe those are two different values. My guess is you believe "total return" refers to the portfolio of stocks, whereas "total weekly return" refers to a single stock. That dichotomy is incorrect. The correct terms are "total return for a stock" (or "a stock's total return") v. "total return for a portfolio" (or "a portfolio's total return"). And yes, as I explained by example previously, assuming no distributions, the portfolio's 15-week total return can be computed by the sum of the stock values (stock price times number of shares held) on Dec 4 divided by the sum of stock values on Aug 21, then minus one. By the way, A/B - 1 is mathematically equal to (A-B)/B. ----- original message ----- "Robbins" wrote in message ... Sorry i'm not sure how to make a follow up thread. So just to make sure i'm doing this right, the total weekly return for each stock would be the last stock price for dec 4 minus the very beginning price of aug 21 divided by the aug 21 closing price. And i'd assume that would be the same for total return. "Joe User" wrote: "Robbins" wrote: Now i understand what's going on. I got clarification from my professor Good. I am glad to hear my comments stimulated you to get clarification on your assignment. Are all your questions answered now? If you have further related questions, I suggest that you post a follow-up to this thread instead of starting a new thread. One last thought, which I offer with some trepidation because it might muddy the water. If you do not understand the following, you might ignore it or seek clarification from your instructor. It occurred to me that "totat return" might have another (uncommon) interpretation in this context, as might "average of all the total returns". Consider the following example. (Note: I purposely use "total" in an uncommon way to demonstrate the potential for confusing terminology.) Suppose the closing price of stock for company X in each of 5 weeks is 10.00, 10.50, 9.50, 10.25, and 9.75. The weekly rates of return are about 5.00%, -9.52%, 7.89% and -4.88% (e.g. 9.75/10.25 - 1). But the "total return" for the period -- that is, the simple return for the 5-week period -- is about -2.50%; that is, 9.75/10 - 1. If you have only the weekly rates of return in B2:B5, say, this can also be computed by the array expression (ctrl+shift+Enter) PRODUCT(1+B2:B5)-1. Moreover, the "average total return" for the period -- i.e. the compounded average weekly return, aka CAGR, for the 5-week period -- is about -0.63% -- (9.75/10)^(1/4) - 1. This can also be computed by the array expression GEOMEAN(1+B2:B5)-1. Note that the CAGR is different from the simple arithmetic average of about -0.38%, computed by AVERAGE(B2:B5). However, arguably, the arithmetic average can be the right "average total return" to use for some purposes, e.g. Monte Carlo simulation. Finally, each of those rates might be annualized in any of several ways, all in common usage. One typical method: (1+r)^52 - 1, where "r" is a weekly rate. ----- original message ----- "Robbins" wrote in message ... Now i understand what's going on. I got clarification from my professor and he said we had to get the closing price of the 5 stocks we picked in the beginning of the semester. It makes sense now since the project information doesn't say for 5 stocks. "Joe User" wrote: "Robbins" wrote: i'd assume total return for the portfolio is just the average of all the total returns for each closing week of the stock I am not convinced that you are using the terms "portfolio" and "total return" correctly.. The portfolio return is the __weighted__ average of the returns of each stock, as I said, not the simple average. Consider the following example portfolio. 100 shares of X invested at $10/share ($1000 total), now valued at $11/share ($1100 total). Return: 10% = 1100/1000 - 1. 50 shares of Y invested at $5/share ($250 total), now valued at $10/share ($500 total). Return: 100% = 500/250 - 1. The total investment was $1250. Total portfolio value now is $1600. The simple average of the returns is 55% = (100% + 10%)/2. That is not the portfolio return. But the weighted average is 28% = 10%*1000/1250 + 100%*500/1250. That is the portfolio return. To verify, note that the portfolio return can also be computed by 1600/1250 -1 = 28%. However, I wonder if the disconnect is a terminology problem. Note that a portfolio is a collection of assets (stocks). But you refer to the "total returns ... of the stock" (singular). A typo? Also, the "total return" is based on current stock value plus distributions. If company X distributed dividends of $1/share in the same period, the total return is (1100 + 100)/1000 -1 = 20%. Actually, we usually assume that dividends are reinvested. If the stock value was $10.50 when dividends were reinvested, we would have purchased an additional 100/10.50 = 9.5238 shares. So the actual "total return" is 11*109.5238/1000 - 1 = 20.48%. Alternatively, we could compute the IRR, taking into account the timing of the dividend reinvestment. (But I would not bother if you are tracking weekly returns.) Those are all ways that people use to compute "total return". It's a vague term. But I wonder if you are using the term "total return" to mean "sum of returns" or something like that for a single stock. PS: IMHO, portfolio "risk" (i.e. standard deviation) could be computed based on the weekly portfolio returns as they are computed above, in the same that we determine "risk" (sd) for an individual stock, not the complex formula that financial engineers use. I 'spose the latter is useful if you do not have all the details. But I don't believe the two approaches are mathematically equivalent. ----- original message ----- "Robbins" wrote in message ... Yea well we did risk in class with variance and standard deviations. And i'd assume total return for the portfolio is just the average of all the total returns for each closing week of the stock "Joe User" wrote: "Robbins" wrote: We have to follow a company for 15 weeks. [....] he wants us to calculate the weekly return for each stock and the whole porfolio. I'm confused. On the one hand, you say you are tracking "a company". On the other hand, you imply that you have a "portfolio", which is presumably more than one stock. For each stock, Fred's formula can be used to compute the simple weekly (rate of) return. For a portfolio of stocks, you would sum the return (simple or total) of each stock times the "weight" of each stock in the portfolio. The "weight" is usually the stock value as a percentage of the portfolio value (simple or total). We are also supposed to calculate the total rate of return for each stock and portfolio. As you may know, the difference between simple return and total return is usually the inclusion of distributions (e.g. dividends) in the latter, presumed to be reinvested. But you might have a different meaning in mind when you say "total return". If you do, it would behoove you to choose a different term to avoid confusion. And lastly calculate the risk of each stock and the whole portfolio. This is where things get very complicated. First, there are many definitions of "risk", even if you substitute the word "volatility", which is only one possible definition of "risk". Based on the context, I suspect you are studying "modern portfolio theory", or at least a portion of it. In that case, I presume you mean the standard of deviation (sd). But even then, the question is: the sd of what? For individual stocks, "volatility" is usually defined as the sd of the log returns (simple or total), although I have seen some simplified explanations that use the sd of the arithmetic returns (simple or total), which I call the "arithmetic sd". The log return is log(endValue/begValue) or log(1+simpleReturn). The |
| All times are GMT +1. The time now is 03:32 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com