Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Worksheet Functions / June 2006

Tip: Looking for answers? Try searching our database.

help! cumulative returns

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
blapatrick - 27 Jun 2006 21:12 GMT
i'm trying to build a model that's based on cumulative quarterly S&P 500
returns. however, rather than using the actual returns (that only come out at
each quarter's end), i want to create a quarterly return for each week based
on the weekly returns for the previous 13 weeks. i have yet to find a
function in excel that will take a set of interest rates over equal periods
and return the effective (cumulative) rate for that period. can anyone help?
Fred Smith - 28 Jun 2006 01:26 GMT
Yes, we can.

My first suggestion is to change from storing returns to storing the index
value. If the index goes from 1000 to 1200 in your 13-week period, you can
easily calculate that it's gone up 20%.

If you insist on storing weekly returns, you can calculate a quarterly return
with the formula:

=(1+r1)*(1+r2)*(1+r3)*...*(1+r13)-1

where r1..r13 are the previous 13 weeks' returns.

Signature

Regards,
Fred

> i'm trying to build a model that's based on cumulative quarterly S&P 500
> returns. however, rather than using the actual returns (that only come out at
> each quarter's end), i want to create a quarterly return for each week based
> on the weekly returns for the previous 13 weeks. i have yet to find a
> function in excel that will take a set of interest rates over equal periods
> and return the effective (cumulative) rate for that period. can anyone help?
joeu2004@hotmail.com - 28 Jun 2006 01:46 GMT
> i'm trying to build a model that's based on cumulative quarterly S&P 500
> returns. however, rather than using the actual returns (that only come out at
> each quarter's end), i want to create a quarterly return for each week based
> on the weekly returns for the previous 13 weeks. i have yet to find a
> function in excel that will take a set of interest rates over equal periods
> and return the effective (cumulative) rate for that period. can anyone help?

Let's be sure we agree on some terminology first.  It is not clear to me
what you mean by "actual returns", in part because the industry uses the term
"return" ambiguously.  The actual value (or price), denoted y0,...,y13, is
the S&P500 index at any given time; y1,...,y13 are the index values for the
13 weeks of interest, and y0 is the index value of the week preceding that
period.  The return rate, denoted r1,...,r13, is the ratio of any two index
values, which is always positive; for example, r1 = y1/y0.  The growth rate,
denoted g1,...,g13, is the return rate minus one, which can be negative or
positive; for example, g1 = r1 - 1 = y1/y0 - 1.  It is unclear to me which
rate you are using.  It is easier to use the return rate.

If the 13 weekly return rates are in cells r1:r13, the cumulative return
rate can be computed with PRODUCT(r1:r13).  Alternatively, you can write
PRODUCT(r1,r2,...,r13), filling in the cell references where I wrote "...".

But I suspect you also have (or certainly could have) the actual index
values, y0,...,y13.  In that case, the cumulative return rate can be computed
simply with y13/y0.
joeu2004@hotmail.com - 28 Jun 2006 01:48 GMT
> i'm trying to build a model that's based on cumulative quarterly S&P 500
> returns. however, rather than using the actual returns (that only come out at
> each quarter's end), i want to create a quarterly return for each week based
> on the weekly returns for the previous 13 weeks. i have yet to find a
> function in excel that will take a set of interest rates over equal periods
> and return the effective (cumulative) rate for that period. can anyone help?

Let's be sure we agree on some terminology first.  It is not clear to me
what you mean by "actual returns", in part because the industry uses the term
"return" ambiguously.  The actual value (or price), denoted y0,...,y13, is
the S&P500 index at any given time; y1,...,y13 are the index values for the
13 weeks of interest, and y0 is the index value of the week preceding that
period.  The return rate, denoted r1,...,r13, is the ratio of any two index
values, which is always positive; for example, r1 = y1/y0.  The growth rate,
denoted g1,...,g13, is the return rate minus one, which can be negative or
positive; for example, g1 = r1 - 1 = y1/y0 - 1.  It is unclear to me which
rate you are using.  It is easier to use the return rate.

If the 13 weekly return rates are in cells r1:r13, the cumulative return
rate can be computed with PRODUCT(r1:r13).

But I suspect you also have (or certainly could have) the actual index
values, y0,...,y13.  In that case, the cumulative return rate can be computed
simply with y13/y0.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.