"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