Robbins said:Oh ok i thought that's what it was but our book and notes
don't really explain it as easily.
Robbins said:We have to follow a company for 15 weeks.
[....]
he wants us to calculate the weekly return for each stock
and the whole porfolio.
We are also supposed to calculate the total rate of return for each stock
and portfolio.
And lastly calculate the risk of each stock and the whole portfolio.
Joe User said:Robbins said: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 said: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.
.
stock
Robbins said: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 said:Robbins said: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 said: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.
:
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 -----
Oh ok i thought that's what it was but our book and notes don't
really
explain it as easily.
:
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.
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
.
Robbins said:i'd assume total return for the portfolio is just the average of all
the total returns for each closing week of the stock
Robbins said: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 said:Robbins said: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 said: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.
:
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 -----
Oh ok i thought that's what it was but our book and notes don't
really
explain it as easily.
:
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.
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
.
Joe User said:Robbins said: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 said: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 said: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 -----
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.
:
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 -----
Oh ok i thought that's what it was but our book and notes don't
really
explain it as easily.
:
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.
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
.
.
Robbins said:Now i understand what's going on. I got clarification from my professor
Robbins said: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 said:Robbins said: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 said: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
:
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 -----
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.
:
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 -----
Oh ok i thought that's what it was but our book and notes don't
really
explain it as easily.
:
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.
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
.
.
Joe User said:Robbins said: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 said: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 said: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 -----
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
:
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 -----
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
Robbins said:Sorry i'm not sure how to make a follow up thread.
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.
Robbins said: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 said:Robbins said: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 said: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.
:
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 -----
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
:
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 -----
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
Joe User said:Robbins said: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 said: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 said: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 -----
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.
:
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 -----
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
:
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