Rolling period

[Guest]

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.

 

[Fredrik Wahlgren]

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

 

[Guest]

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%.

 

[Bernard Liengme]

How is 4.44% arrived at?

 

[Fredrik Wahlgren]

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

 

[Guest]

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.

 

[Guest]

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

 

[Fredrik Wahlgren]

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