Calculate Holding Period Returns Between Two Customizable Dates

[Josh M]

I have time series data of mutual fund prices from Yahoo Finance that I
update frequently. Column A has the dates (Daily), Column B has the Adjusted
Close Value, and in Column C I have formulas to calculate the daily returns
(B8/B7-1).

VTSMX Adj. Close
1/3/2005 26.25
1/4/2005 25.91 -1.30%
1/5/2005 25.76 -0.58%
1/6/2005 25.86 0.39%
1/7/2005 25.8 -0.23%..... to present.

I have a Start Date in cell A1 and End Date in cell A2, and I have used
dynamic ranges to create a price chart that expands automatically as more
data is added to the time series, and it is flexible to show different
periods by adjustng the two dates. However, instead of charting the data, I
would just like to show the heriod return in another cell (like cell A5)
between the two dates I select, for example between 10/21/2008 and 7/31/2009.

I can do this manually by using the following formula between the selected
dates:
{=PRODUCT(C135:C197+1)-1}

Is there a way to calculate different time periods, simply by changing the
Start Date and End Dates?

Thank you,

Josh

 

[Joel]

You can do it with a UDF

call with
=productDates(B13:B19,A13:C19,A1,B1)

Function ProductDates(DailyReturn As Range, DailyDates As Range, _
StartDate As Date, EndDate As Date)

ProductDates = 1
For Count = 1 To DailyReturn.Count
MyDate = DailyDates.Cells(Count, 1)
Amount = DailyReturn.Cells(Count, 1)
If MyDate >= StartDate And MyDate <= EndDate Then
ProductDates = ProductDates * Amount
End If
Next Count
End Function