Rolling period
 

I have a spreadsheet with monthly returns for the last 5 years. I would like
to be able to type in a start date and end date and have excel calculate the
linked return. Is there a way I can do this? Thank you.

"matthew" wrote in message
...
I have a spreadsheet with monthly returns for the last 5 years. I would
like
to be able to type in a start date and end date and have excel calculate
the
linked return. Is there a way I can do this? Thank you.

Can you provide an example?

/Fredrik

Example:

Date Return
1/31/04 2.0%
2/28/04 1.0%
3/31/04 3.0%
4/30/04 -2.5%
5/31/04 4.0%

I want to be able to type in start date of 3/31/04 and end date of 5/31/04
and have excel calculate the return for that time period. Should be 4.44%.


"Fredrik Wahlgren" wrote:


"matthew" wrote in message
...
I have a spreadsheet with monthly returns for the last 5 years. I would
like
to be able to type in a start date and end date and have excel calculate
the
linked return. Is there a way I can do this? Thank you.

Can you provide an example?

/Fredrik

How is 4.44% arrived at?
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"matthew" wrote in message
...
Example:

Date Return
1/31/04 2.0%
2/28/04 1.0%
3/31/04 3.0%
4/30/04 -2.5%
5/31/04 4.0%

I want to be able to type in start date of 3/31/04 and end date of 5/31/04
and have excel calculate the return for that time period. Should be 4.44%.


"Fredrik Wahlgren" wrote:


"matthew" wrote in message
...
I have a spreadsheet with monthly returns for the last 5 years. I would
like
to be able to type in a start date and end date and have excel
calculate
the
linked return. Is there a way I can do this? Thank you.

Can you provide an example?

/Fredrik

The subject was period. Now you're suddenly talking about some return value.
Where do these return values come from? Furthermore, the start date appears
in the middle of your example.

/Fredrik


"matthew" wrote in message
...
Example:

Date Return
1/31/04 2.0%
2/28/04 1.0%
3/31/04 3.0%
4/30/04 -2.5%
5/31/04 4.0%

I want to be able to type in start date of 3/31/04 and end date of 5/31/04
and have excel calculate the return for that time period. Should be 4.44%.


"Fredrik Wahlgren" wrote:


"matthew" wrote in message
...
I have a spreadsheet with monthly returns for the last 5 years. I
would
like
to be able to type in a start date and end date and have excel
calculate
the
linked return. Is there a way I can do this? Thank you.

Can you provide an example?

/Fredrik

I have a list of 5 years worth of monthly returns or 72 cells with dates and
the corresponding return. If I want to take a date range/period, I would
like it to link those returns. So in my example below if I type in 2/28/04 -
5/31/04 I would like excel to do the following
=((1%+1)*(3%+1)*(-2%+1)*(4%+1)-1)*100 which equals 5.486%. Can this be done
with excel? Thank you.




Date Return
1/31/04 2.0%
2/28/04 1.0%
3/31/04 3.0%
4/30/04 -2.5%
5/31/04 4.0%
I want to be able to type in start date of 3/31/04 and end date of 5/31/04
and have excel calculate the return for that time period. Should be 4.44%.

Matthew:
A not very sophisticated approach would be to add a helper column to the
right of your monthly returns which calculates the cumulative return from the
first month.
Step 2 would be to use a vlookup that takes the cumulative value found by
using your later date and divides it by the result of a second vlookup that
returns the cumulative value found by using your earlier date and then
subtracts 1.
Stephen Powell

"matthew" wrote:

I have a list of 5 years worth of monthly returns or 72 cells with dates and
the corresponding return. If I want to take a date range/period, I would
like it to link those returns. So in my example below if I type in 2/28/04 -
5/31/04 I would like excel to do the following
=((1%+1)*(3%+1)*(-2%+1)*(4%+1)-1)*100 which equals 5.486%. Can this be done
with excel? Thank you.




Date Return
1/31/04 2.0%
2/28/04 1.0%
3/31/04 3.0%
4/30/04 -2.5%
5/31/04 4.0%
I want to be able to type in start date of 3/31/04 and end date of 5/31/04
and have excel calculate the return for that time period. Should be 4.44%.

"Stephen POWELL" wrote in message
...
Matthew:
A not very sophisticated approach would be to add a helper column to the
right of your monthly returns which calculates the cumulative return from
the
first month.
Step 2 would be to use a vlookup that takes the cumulative value found by
using your later date and divides it by the result of a second vlookup
that
returns the cumulative value found by using your earlier date and then
subtracts 1.
Stephen Powell


I think this is a reasonable approach.

/Fredrik