help! cumulative returns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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.
 
blapatrick said:
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?
Click to expand...

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.
 
blapatrick said:
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?
Click to expand...

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.
 
Back
Top