# Stock market rolling period returns

#### mdelzi23

##### New Member
Hello,
I am trying to calculate stock market returns over different periods. I have downloaded the daily index values from yahoo finance for the S&P 500 and the Russell 2000 index. I would like to start with the 1 year, 3 year and 6 year rolling periods. This is difficult because the data excludes the weekends and holidays which is difficult to calculate 365 days. When I enter a starting period the ending date some times falls on a weekend or holiday. I have used the vlookup function to randomly pick starting dates and find its corresponding index value, but the rolling period often ends on a weekend or holiday where there is no corresponding date or value. I would like to create a lookup that allows the ending date to move out to the next trading day in the future and find the corresponding index value.

This is the formula I have been using =WORKDAY(EDATE(C9,12)-1,0,'S&P Daily '!J2:J7288)
12 is the 12 month rolling period from a specific date. I get a "#N/A" when the end date lands on a weekend, holiday or the data is missing.

Any suggestion would be very helpful.

Thanks

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Welcome to the Forum!

Isn't it usual, given that each day's index values are as at the close of the day, for the index value to be taken for the specified date, or the most recent previous trading date?

So here, for example, won't your 30 June 2012 index be:

=VLOOKUP(DATE(2012,6,30),A1:B3,2,TRUE)
=1,001.

Excel 2010
AB
1Available index values
2Thu 28 Jun 20121,000
3Fri 29 Jun 20121,001
4Mon 2 Jul 2012998

</tbody>

Welcome to the Forum!

Isn't it usual, given that each day's index values are as at the close of the day, for the index value to be taken for the specified date, or the most recent previous trading date?

So here, for example, won't your 30 June 2012 index be:

=VLOOKUP(DATE(2012,6,30),A1:B3,2,TRUE)
=1,001.

Excel 2010
AB
1Available index values
2Thu 28 Jun 20121,000
3Fri 29 Jun 20121,001
4Mon 2 Jul 2012998

<tbody>
</tbody>

Stephen,

Thank you for your prompt response. I completely understand the logic behind your response but the math behind your vlookup is confusing. How is your vlookup finding the date of 6/30/2012 if there is no date in the data? I have pasted the last 12 months of daily closes below to demonstrate what I mean. As you can see the days that fall on a holiday or weekend show #N/A to calculate the 12 month rolling return. Here are the formulas I am using in column H and I.
I might be making this harder than it has to be...

Column H =DATE(YEAR(E2)-1,MONTH(E2),DAY(E2)+1)
Column I=(F2-VLOOKUP(H2,A2:F16608,2,FALSE))/VLOOKUP(H2,A2:F16608,2,FALSE) (this calculates the return over the last 12 months)

A B C D E F G H I
 Date Open High Low Date Close One year 1 year 12/31/2015 2060.59 2062.54 2043.62 12/31/2015 2043.94 1/1/2015 #N/A 12/30/2015 2077.34 2077.34 2061.97 12/30/2015 2063.36 12/31/2014 -0.90% 12/29/2015 2060.54 2081.56 2060.54 12/29/2015 2078.36 12/30/2014 -0.49% 12/28/2015 2057.77 2057.77 2044.2 12/28/2015 2056.5 12/29/2014 -1.49% 12/24/2015 2063.52 2067.36 2058.73 12/24/2015 2060.99 12/25/2014 #N/A 12/23/2015 2042.2 2064.73 2042.2 12/23/2015 2064.29 12/24/2014 -0.91% 12/22/2015 2023.15 2042.74 2020.49 12/22/2015 2038.97 12/23/2014 -2.04% 12/21/2015 2010.27 2022.9 2005.93 12/21/2015 2021.15 12/22/2014 -2.33% 12/18/2015 2040.81 2040.81 2005.33 12/18/2015 2005.55 12/19/2014 -2.69% 12/17/2015 2073.76 2076.37 2041.66 12/17/2015 2041.89 12/18/2014 1.13% 12/16/2015 2046.5 2076.72 2042.43 12/16/2015 2073.07 12/17/2014 5.03% 12/15/2015 2025.55 2053.87 2025.55 12/15/2015 2043.41 12/16/2014 2.85% 12/14/2015 2013.37 2022.92 1993.26 12/14/2015 2021.94 12/15/2014 0.84% 12/11/2015 2047.27 2047.27 2008.8 12/11/2015 2012.37 12/12/2014 -0.89% 12/10/2015 2047.93 2067.65 2045.67 12/10/2015 2052.23 12/11/2014 1.20% 12/9/2015 2061.17 2080.33 2036.53 12/9/2015 2047.62 12/10/2014 -0.55% 12/8/2015 2073.39 2073.85 2052.32 12/8/2015 2063.59 12/9/2014 0.34% 12/7/2015 2090.42 2090.42 2066.78 12/7/2015 2077.07 12/8/2014 0.11% 12/4/2015 2051.24 2093.84 2051.24 12/4/2015 2091.69 12/5/2014 0.91% 12/3/2015 2080.71 2085 2042.35 12/3/2015 2049.62 12/4/2014 -1.16% 12/2/2015 2101.71 2104.27 2077.11 12/2/2015 2079.51 12/3/2014 0.58% 12/1/2015 2082.93 2103.37 2082.93 12/1/2015 2102.63 12/2/2014 2.38% 11/30/2015 2090.95 2093.81 2080.41 11/30/2015 2080.41 12/1/2014 0.71% 11/27/2015 2088.82 2093.29 2084.13 11/27/2015 2090.11 11/28/2014 0.74% 11/25/2015 2089.3 2093 2086.3 11/25/2015 2088.87 11/26/2014 1.04% 11/24/2015 2084.42 2094.12 2070.29 11/24/2015 2089.14 11/25/2014 0.92% 11/23/2015 2089.41 2095.61 2081.39 11/23/2015 2086.59 11/24/2014 1.04% 11/20/2015 2082.82 2097.06 2082.82 11/20/2015 2089.17 11/21/2014 1.54% 11/19/2015 2083.7 2086.74 2078.76 11/19/2015 2081.24 11/20/2014 1.73% 11/18/2015 2051.99 2085.31 2051.99 11/18/2015 2083.58 11/19/2014 1.58% 11/17/2015 2053.67 2066.69 2045.9 11/17/2015 2050.44 11/18/2014 0.44% 11/16/2015 2022.08 2053.22 2019.39 11/16/2015 2053.19 11/17/2014 0.73% 11/13/2015 2044.64 2044.64 2022.02 11/13/2015 2023.04 11/14/2014 -0.82% 11/12/2015 2072.29 2072.29 2045.66 11/12/2015 2045.97 11/13/2014 0.33% 11/11/2015 2083.41 2086.94 2074.85 11/11/2015 2075 11/12/2014 1.83% 11/10/2015 2077.19 2083.67 2069.91 11/10/2015 2081.72 11/11/2014 2.14% 11/9/2015 2096.56 2096.56 2068.24 11/9/2015 2078.58 11/10/2014 2.29% 11/6/2015 2098.6 2101.91 2083.74 11/6/2015 2099.2 11/7/2014 3.29% 11/5/2015 2101.68 2108.78 2090.41 11/5/2015 2099.93 11/6/2014 3.79% 11/4/2015 2110.6 2114.59 2096.98 11/4/2015 2102.31 11/5/2014 4.32% 11/3/2015 2102.63 2116.48 2097.51 11/3/2015 2109.79 11/4/2014 4.66% 11/2/2015 2080.76 2106.2 2080.76 11/2/2015 2104.05 11/3/2014 4.25% 10/30/2015 2090 2094.32 2079.34 10/30/2015 2079.36 10/31/2014 3.91% 10/29/2015 2088.35 2092.52 2082.63 10/29/2015 2089.41 10/30/2014 5.55% 10/28/2015 2066.48 2090.35 2063.11 10/28/2015 2090.35 10/29/2014 5.40% 10/27/2015 2068.75 2070.37 2058.84 10/27/2015 2065.89 10/28/2014 5.18% 10/26/2015 2075.08 2075.14 2066.53 10/26/2015 2071.18 10/27/2014 5.51% 10/23/2015 2058.19 2079.74 2058.19 10/23/2015 2075.15 10/24/2014 6.33% 10/22/2015 2021.88 2055.2 2021.88 10/22/2015 2052.51 10/23/2014 6.29% 10/21/2015 2033.47 2037.97 2017.22 10/21/2015 2018.94 10/22/2014 4.00% 10/20/2015 2033.13 2039.12 2026.61 10/20/2015 2030.77 10/21/2014 6.36% 10/19/2015 2031.73 2034.45 2022.31 10/19/2015 2033.66 10/20/2014 7.85% 10/16/2015 2024.37 2033.54 2020.46 10/16/2015 2033.11 10/17/2014 9.02% 10/15/2015 1996.47 2024.15 1996.47 10/15/2015 2023.86 10/16/2014 9.05% 10/14/2015 2003.66 2009.56 1990.73 10/14/2015 1994.24 10/15/2014 6.41% 10/13/2015 2015 2022.34 2001.78 10/13/2015 2003.69 10/14/2014 6.74% 10/12/2015 2015.65 2018.66 2010.55 10/12/2015 2017.46 10/13/2014 5.87% 10/9/2015 2013.73 2020.13 2007.61 10/9/2015 2014.89 10/10/2014 4.64% 10/8/2015 1994.01 2016.5 1987.53 10/8/2015 2013.43 10/9/2014 2.33% 10/7/2015 1982.34 1999.31 1976.44 10/7/2015 1995.83 10/8/2014 3.11% 10/6/2015 1986.63 1991.62 1971.99 10/6/2015 1979.92 10/7/2014 0.89% 10/5/2015 1954.33 1989.17 1954.33 10/5/2015 1987.05 10/6/2014 0.86% 10/2/2015 1921.77 1951.36 1893.7 10/2/2015 1951.36 10/3/2014 0.17% 10/1/2015 1919.65 1927.21 1900.7 10/1/2015 1923.82 10/2/2014 -1.13% 9/30/2015 1887.14 1920.53 1887.14 9/30/2015 1920.03 10/1/2014 -2.61% 9/29/2015 1881.9 1899.48 1871.91 9/29/2015 1884.09 9/30/2014 -4.76% 9/28/2015 1929.18 1929.18 1879.21 9/28/2015 1881.77 9/29/2014 -4.91% 9/25/2015 1935.93 1952.89 1921.5 9/25/2015 1931.34 9/26/2014 -1.77% 9/24/2015 1934.81 1937.17 1908.92 9/24/2015 1932.24 9/25/2014 -3.26% 9/23/2015 1943.24 1949.52 1932.57 9/23/2015 1938.76 9/24/2014 -2.25% 9/22/2015 1961.39 1961.39 1929.22 9/22/2015 1942.74 9/23/2014 -2.51% 9/21/2015 1960.84 1979.64 1955.8 9/21/2015 1966.97 9/22/2014 -2.10% 9/18/2015 1989.66 1989.66 1953.45 9/18/2015 1958.03 9/19/2014 -2.72% 9/17/2015 1995.33 2020.86 1986.73 9/17/2015 1990.2 9/18/2014 -0.64% 9/16/2015 1978.02 1997.26 1977.93 9/16/2015 1995.31 9/17/2014 -0.20% 9/15/2015 1955.1 1983.19 1954.3 9/15/2015 1978.09 9/16/2014 -0.19% 9/14/2015 1963.06 1963.06 1948.27 9/14/2015 1953.03 9/15/2014 -1.66% 9/11/2015 1951.45 1961.05 1939.19 9/11/2015 1961.05 9/12/2014 -1.79% 9/10/2015 1941.59 1965.29 1937.19 9/10/2015 1952.29 9/11/2014 -2.04% 9/9/2015 1971.45 1988.63 1937.88 9/9/2015 1942.04 9/10/2014 -2.33% 9/8/2015 1927.3 1970.42 1927.3 9/8/2015 1969.41 9/9/2014 -1.57% 9/4/2015 1947.76 1947.76 1911.21 9/4/2015 1921.22 9/5/2014 -3.84% 9/3/2015 1950.79 1975.01 1944.72 9/3/2015 1951.13 9/4/2014 -2.52% 9/2/2015 1916.52 1948.91 1916.52 9/2/2015 1948.86 9/3/2014 -2.73% 9/1/2015 1970.09 1970.09 1903.07 9/1/2015 1913.85 9/2/2014 -4.50% 8/31/2015 1986.73 1986.73 1965.98 8/31/2015 1972.18 9/1/2014 #N/A 8/28/2015 1986.06 1993.48 1975.19 8/28/2015 1988.87 8/29/2014 -0.48% 8/27/2015 1942.77 1989.6 1942.77 8/27/2015 1987.66 8/28/2014 -0.49% 8/26/2015 1872.75 1943.09 1872.75 8/26/2015 1940.51 8/27/2014 -3.00% 8/25/2015 1898.08 1948.04 1867.08 8/25/2015 1867.61 8/26/2014 -6.55% 8/24/2015 1965.15 1965.15 1867.01 8/24/2015 1893.21 8/25/2014 -4.95% 8/21/2015 2034.08 2034.08 1970.89 8/21/2015 1970.89 8/22/2014 -1.09% 8/20/2015 2076.61 2076.61 2035.73 8/20/2015 2035.73 8/21/2014 2.46% 8/19/2015 2095.69 2096.17 2070.53 8/19/2015 2079.61 8/20/2014 5.01% 8/18/2015 2101.99 2103.47 2094.14 8/18/2015 2096.92 8/19/2014 6.30% 8/17/2015 2089.7 2102.87 2079.3 8/17/2015 2102.44 8/18/2014 7.36% 8/14/2015 2083.15 2092.45 2080.61 8/14/2015 2091.54 8/15/2014 6.77% 8/13/2015 2086.19 2092.93 2078.26 8/13/2015 2083.39 8/14/2014 6.98% 8/12/2015 2081.1 2089.06 2052.09 8/12/2015 2086.05 8/13/2014 7.77% 8/11/2015 2102.66 2102.66 2076.49 8/11/2015 2084.07 8/12/2014 7.66% 8/10/2015 2080.98 2105.35 2080.98 8/10/2015 2104.18 8/11/2014 8.83% 8/7/2015 2082.61 2082.61 2067.91 8/7/2015 2077.57 8/8/2014 8.75% 8/6/2015 2100.75 2103.32 2075.53 8/6/2015 2083.56 8/7/2014 8.35% 8/5/2015 2095.27 2112.66 2095.27 8/5/2015 2099.84 8/6/2014 9.52% 8/4/2015 2097.68 2102.51 2088.6 8/4/2015 2093.32 8/5/2014 8.11% 8/3/2015 2104.49 2105.7 2087.31 8/3/2015 2098.04 8/4/2014 8.90% 7/31/2015 2111.6 2114.24 2102.07 7/31/2015 2103.84 8/1/2014 9.02% 7/30/2015 2106.78 2110.48 2094.97 7/30/2015 2108.63 7/31/2014 7.30% 7/29/2015 2094.7 2110.6 2094.08 7/29/2015 2108.57 7/30/2014 6.86% 7/28/2015 2070.75 2095.6 2069.09 7/28/2015 2093.25 7/29/2014 5.72% 7/27/2015 2078.19 2078.19 2063.52 7/27/2015 2067.64 7/28/2014 4.52% 7/24/2015 2102.24 2106.01 2077.09 7/24/2015 2079.65 7/25/2014 4.79% 7/23/2015 2114.16 2116.87 2098.63 7/23/2015 2102.15 7/24/2014 5.74% 7/22/2015 2118.21 2118.51 2110 7/22/2015 2114.15 7/23/2014 6.49% 7/21/2015 2127.55 2128.49 2115.4 7/21/2015 2119.21 7/22/2014 7.27% 7/20/2015 2126.85 2132.82 2123.66 7/20/2015 2128.28 7/21/2014 7.66% 7/17/2015 2126.8 2128.91 2119.88 7/17/2015 2126.64 7/18/2014 8.42% 7/16/2015 2110.55 2124.42 2110.55 7/16/2015 2124.29 7/17/2014 7.30% 7/15/2015 2109.01 2114.14 2102.49 7/15/2015 2107.4 7/16/2014 6.63% 7/14/2015 2099.72 2111.98 2098.18 7/14/2015 2108.95 7/15/2014 6.65% 7/13/2015 2080.03 2100.67 2080.03 7/13/2015 2099.6 7/14/2014 6.59% 7/10/2015 2052.74 2081.31 2052.74 7/10/2015 2076.62 7/11/2014 5.64% 7/9/2015 2049.73 2074.28 2049.73 7/9/2015 2051.31 7/10/2014 4.30% 7/8/2015 2077.66 2077.66 2044.66 7/8/2015 2046.68 7/9/2014 4.15% 7/7/2015 2069.52 2083.74 2044.02 7/7/2015 2081.34 7/8/2014 5.31% 7/6/2015 2073.95 2078.61 2058.4 7/6/2015 2068.76 7/7/2014 4.26% 7/2/2015 2078.03 2085.06 2071.02 7/2/2015 2076.78 7/3/2014 5.11% 7/1/2015 2067 2082.78 2067 7/1/2015 2077.42 7/2/2014 5.29% 6/30/2015 2061.19 2074.28 2056.32 6/30/2015 2063.11 7/1/2014 5.14% 6/29/2015 2098.63 2098.63 2056.64 6/29/2015 2057.64 6/30/2014 4.94% 6/26/2015 2102.62 2108.92 2095.38 6/26/2015 2101.49 6/27/2014 7.41% 6/25/2015 2109.96 2116.04 2101.78 6/25/2015 2102.31 6/26/2014 7.27% 6/24/2015 2123.65 2125.1 2108.58 6/24/2015 2108.58 6/25/2014 8.17% 6/23/2015 2123.16 2128.03 2119.89 6/23/2015 2124.2 6/24/2014 8.27% 6/22/2015 2112.5 2129.87 2112.5 6/22/2015 2122.85 6/23/2014 8.15% 6/19/2015 2121.06 2121.64 2109.45 6/19/2015 2109.99 6/20/2014 7.63% 6/18/2015 2101.58 2126.65 2101.58 6/18/2015 2121.24 6/19/2014 8.36% 6/17/2015 2097.4 2106.79 2088.86 6/17/2015 2100.44 6/18/2014 8.12% 6/16/2015 2084.26 2097.4 2082.1 6/16/2015 2096.29 6/17/2014 8.22% 6/15/2015 2091.34 2091.34 2072.49 6/15/2015 2084.43 6/16/2014 7.73% 6/12/2015 2107.43 2107.43 2091.33 6/12/2015 2094.11 6/13/2014 8.46% 6/11/2015 2106.24 2115.02 2106.24 6/11/2015 2108.86 6/12/2014 8.52% 6/10/2015 2081.12 2108.5 2081.12 6/10/2015 2105.2 6/11/2014 7.99% 6/9/2015 2079.07 2085.62 2072.14 6/9/2015 2080.15 6/10/2014 6.66% 6/8/2015 2092.34 2093.01 2079.11 6/8/2015 2079.28 6/9/2014 6.69% 6/5/2015 2095.09 2100.99 2085.67 6/5/2015 2092.83 6/6/2014 7.74% 6/4/2015 2112.35 2112.89 2093.23 6/4/2015 2095.84 6/5/2014 8.68% 6/3/2015 2110.64 2121.92 2109.61 6/3/2015 2114.07 6/4/2014 9.93% 6/2/2015 2110.41 2117.59 2099.14 6/2/2015 2109.6 6/3/2014 9.70% 6/1/2015 2108.64 2119.15 2102.54 6/1/2015 2111.73 6/2/2014 9.76% 5/29/2015 2120.66 2120.66 2104.89 5/29/2015 2107.39 5/30/2014 9.74% 5/28/2015 2122.27 2122.27 2112.86 5/28/2015 2120.79 5/29/2014 11.00% 5/27/2015 2105.13 2126.22 2105.13 5/27/2015 2123.48 5/28/2014 11.07% 5/26/2015 2125.34 2125.34 2099.18 5/26/2015 2104.2 5/27/2014 10.63% 5/22/2015 2130.36 2132.15 2126.06 5/22/2015 2126.06 5/23/2014 12.29% 5/21/2015 2125.55 2134.28 2122.95 5/21/2015 2130.82 5/22/2014 12.85% 5/20/2015 2127.79 2134.72 2122.59 5/20/2015 2125.85 5/21/2014 13.48% 5/19/2015 2129.45 2133.02 2124.5 5/19/2015 2127.83 5/20/2014 12.89% 5/18/2015 2121.3 2131.78 2120.01 5/18/2015 2129.2 5/19/2014 13.46% 5/15/2015 2122.07 2123.89 2116.81 5/15/2015 2122.73 5/16/2014 13.44% 5/14/2015 2100.43 2121.45 2100.43 5/14/2015 2121.1 5/15/2014 12.34% 5/13/2015 2099.62 2110.19 2096.04 5/13/2015 2098.48 5/14/2014 10.61% 5/12/2015 2102.87 2105.06 2085.57 5/12/2015 2099.12 5/13/2014 10.67% 5/11/2015 2115.56 2117.69 2104.58 5/11/2015 2105.33 5/12/2014 11.98% 5/8/2015 2092.13 2117.66 2092.13 5/8/2015 2116.1 5/9/2014 12.84% 5/7/2015 2079.96 2092.9 2074.99 5/7/2015 2088 5/8/2014 11.22% 5/6/2015 2091.26 2098.42 2067.93 5/6/2015 2080.15 5/7/2014 11.33% 5/5/2015 2112.63 2115.24 2088.46 5/5/2015 2089.46 5/6/2014 10.92% 5/4/2015 2110.23 2120.95 2110.23 5/4/2015 2114.49 5/5/2014 12.51% 5/1/2015 2087.38 2108.41 2087.38 5/1/2015 2108.29 5/2/2014 11.83% 4/30/2015 2105.52 2105.52 2077.59 4/30/2015 2085.51 5/1/2014 10.67% 4/29/2015 2112.49 2113.65 2097.41 4/29/2015 2106.85 4/30/2014 12.24% 4/28/2015 2108.35 2116.04 2094.89 4/28/2015 2114.76 4/29/2014 13.04% 4/27/2015 2119.29 2125.92 2107.04 4/27/2015 2108.92 4/28/2014 13.08% 4/24/2015 2112.8 2120.92 2112.8 4/24/2015 2117.69 4/25/2014 12.78% 4/23/2015 2107.21 2120.49 2103.19 4/23/2015 2112.93 4/24/2014 12.27% 4/22/2015 2098.27 2109.98 2091.05 4/22/2015 2107.96 4/23/2014 12.17% 4/21/2015 2102.82 2109.64 2094.38 4/21/2015 2097.29 4/22/2014 12.00% 4/20/2015 2084.11 2103.94 2084.11 4/20/2015 2100.4 4/21/2014 12.57% 4/17/2015 2102.58 2102.58 2072.37 4/17/2015 2081.18 4/18/2014 #N/A 4/16/2015 2105.96 2111.3 2100.02 4/16/2015 2104.99 4/17/2014 13.07% 4/15/2015 2097.82 2111.91 2097.82 4/15/2015 2106.63 4/16/2014 14.12% 4/14/2015 2092.28 2098.62 2083.24 4/14/2015 2095.84 4/15/2014 14.44% 4/13/2015 2102.03 2107.65 2092.33 4/13/2015 2092.43 4/14/2014 15.08% 4/10/2015 2091.51 2102.61 2091.51 4/10/2015 2102.06 4/11/2014 14.83% 4/9/2015 2081.29 2093.31 2074.29 4/9/2015 2091.18 4/10/2014 11.69% 4/8/2015 2076.94 2086.69 2073.3 4/8/2015 2081.9 4/9/2014 12.37% 4/7/2015 2080.79 2089.81 2076.1 4/7/2015 2076.33 4/8/2014 12.51% 4/6/2015 2064.87 2086.99 2056.52 4/6/2015 2080.62 4/7/2014 11.63% 4/2/2015 2060.03 2072.17 2057.32 4/2/2015 2066.96 4/3/2014 9.28% 4/1/2015 2067.63 2067.63 2048.38 4/1/2015 2059.69 4/2/2014 9.17% 3/31/2015 2084.05 2084.05 2067.04 3/31/2015 2067.89 4/1/2014 10.35% 3/30/2015 2064.11 2088.97 2064.11 3/30/2015 2086.24 3/31/2014 12.21% 3/27/2015 2055.78 2062.83 2052.96 3/27/2015 2061.02 3/28/2014 11.40% 3/26/2015 2059.94 2067.15 2045.5 3/26/2015 2056.15 3/27/2014 11.02% 3/25/2015 2093.1 2097.43 2061.05 3/25/2015 2061.05 3/26/2014 10.39% 3/24/2015 2103.94 2107.63 2091.5 3/24/2015 2091.5 3/25/2014 12.48% 3/23/2015 2107.99 2114.86 2104.42 3/23/2015 2104.42 3/24/2014 12.68% 3/20/2015 2090.32 2113.92 2090.32 3/20/2015 2108.1 3/21/2014 12.46% 3/19/2015 2098.69 2098.69 2085.56 3/19/2015 2089.27 3/20/2014 12.32% 3/18/2015 2072.84 2106.85 2061.23 3/18/2015 2099.5 3/19/2014 12.14% 3/17/2015 2080.59 2080.59 2065.08 3/17/2015 2074.28 3/18/2014 11.59% 3/16/2015 2055.35 2081.41 2055.35 3/16/2015 2081.19 3/17/2014 12.94% 3/13/2015 2064.56 2064.56 2041.17 3/13/2015 2053.4 3/14/2014 11.29% 3/12/2015 2041.1 2066.41 2041.1 3/12/2015 2065.95 3/13/2014 10.53% 3/11/2015 2044.69 2050.08 2039.69 3/11/2015 2040.24 3/12/2014 9.33% 3/10/2015 2076.14 2076.14 2044.16 3/10/2015 2044.16 3/11/2014 8.83% 3/9/2015 2072.25 2083.49 2072.21 3/9/2015 2079.43 3/10/2014 10.73% 3/6/2015 2100.91 2100.91 2067.27 3/6/2015 2071.26 3/7/2014 10.26% 3/5/2015 2098.54 2104.25 2095.22 3/5/2015 2101.04 3/6/2014 12.10% 3/4/2015 2107.72 2107.72 2094.49 3/4/2015 2098.53 3/5/2014 11.98% 3/3/2015 2115.76 2115.76 2098.26 3/3/2015 2107.78 3/4/2014 13.98% 3/2/2015 2105.23 2117.52 2104.5 3/2/2015 2117.39 3/3/2014 13.98% 2/27/2015 2110.88 2112.74 2103.75 2/27/2015 2104.5 2/28/2014 13.44% 2/26/2015 2113.91 2113.91 2103.76 2/26/2015 2110.74 2/27/2014 14.41% 2/25/2015 2115.3 2119.59 2109.89 2/25/2015 2113.86 2/26/2014 14.52% 2/24/2015 2109.1 2117.94 2105.87 2/24/2015 2115.48 2/25/2014 14.50% 2/23/2015 2109.83 2110.05 2103 2/23/2015 2109.66 2/24/2014 14.86% 2/20/2015 2097.65 2110.61 2085.44 2/20/2015 2110.3 2/21/2014 14.62% 2/19/2015 2099.25 2102.13 2090.79 2/19/2015 2097.45 2/20/2014 14.66% 2/18/2015 2099.16 2100.23 2092.15 2/18/2015 2099.68 2/19/2014 14.18% 2/17/2015 2096.47 2101.3 2089.8 2/17/2015 2100.34 2/18/2014 14.21% 2/13/2015 2088.78 2097.03 2086.7 2/13/2015 2096.99 2/14/2014 14.69% 2/12/2015 2069.98 2088.53 2069.98 2/12/2015 2088.48 2/13/2014 15.08% 2/11/2015 2068.55 2073.48 2057.99 2/11/2015 2068.53 2/12/2014 13.65% 2/10/2015 2049.38 2070.86 2048.62 2/10/2015 2068.59 2/11/2014 14.89% 2/9/2015 2053.47 2056.16 2041.88 2/9/2015 2046.74 2/10/2014 13.95% 2/6/2015 2062.28 2072.4 2049.97 2/6/2015 2055.47 2/7/2014 15.74% 2/5/2015 2043.45 2063.55 2043.45 2/5/2015 2062.52 2/6/2014 17.66% 2/4/2015 2048.86 2054.74 2036.72 2/4/2015 2041.51 2/5/2014 16.43% 2/3/2015 2022.71 2050.3 2022.71 2/3/2015 2050.03 2/4/2014 17.56% 2/2/2015 1996.67 2021.66 1980.9 2/2/2015 2020.85 2/3/2014 13.36% 1/30/2015 2019.35 2023.32 1993.38 1/30/2015 1994.99 1/31/2014 11.40% 1/29/2015 2002.45 2024.64 1989.18 1/29/2015 2021.25 1/30/2014 13.73% 1/28/2015 2032.34 2042.49 2001.49 1/28/2015 2002.16 1/29/2014 11.84% 1/27/2015 2047.86 2047.86 2019.91 1/27/2015 2029.55 1/28/2014 13.83% 1/26/2015 2050.42 2057.62 2040.97 1/26/2015 2057.09 1/27/2014 14.86% 1/23/2015 2062.98 2062.98 2050.54 1/23/2015 2051.82 1/24/2014 12.31% 1/22/2015 2034.3 2064.62 2026.38 1/22/2015 2063.15 1/23/2014 11.99% 1/21/2015 2020.19 2038.29 2012.04 1/21/2015 2032.12 1/22/2014 10.16% 1/20/2015 2020.76 2028.94 2004.49 1/20/2015 2022.55 1/21/2014 9.86% 1/16/2015 1992.25 2020.46 1988.12 1/16/2015 2019.42 1/17/2014 9.50% 1/15/2015 2013.75 2021.35 1991.47 1/15/2015 1992.67 1/16/2014 7.83% 1/14/2015 2018.4 2018.4 1988.44 1/14/2015 2011.27 1/15/2014 9.28% 1/13/2015 2031.58 2056.93 2008.25 1/13/2015 2023.03 1/14/2014 11.07% 1/12/2015 2046.13 2049.3 2022.58 1/12/2015 2028.26 1/13/2014 10.16% 1/9/2015 2063.45 2064.43 2038.33 1/9/2015 2044.81 1/10/2014 11.13% 1/8/2015 2030.61 2064.08 2030.61 1/8/2015 2062.14 1/9/2014 12.13% 1/7/2015 2005.55 2029.61 2005.55 1/7/2015 2025.9 1/8/2014 10.23% 1/6/2015 2022.15 2030.25 1992.44 1/6/2015 2002.61 1/7/2014 9.51% 1/5/2015 2054.44 2054.44 2017.34 1/5/2015 2020.58 1/6/2014 10.28% 1/2/2015 2058.9 2072.36 2046.04 1/2/2015 2058.2 1/3/2014 12.27% 12/31/2014 2082.11 2085.58 2057.94 12/31/2014 2058.9 1/1/2014 #N/A 12/30/2014 2088.49 2088.49 2079.53 12/30/2014 2080.35 12/31/2013 12.90% 12/29/2014 2087.63 2093.55 2085.75 12/29/2014 2090.57 12/30/2013 13.53% 12/26/2014 2084.3 2092.7 2084.3 12/26/2014 2088.77 12/27/2013 13.34%

<colgroup><col><col span="3"><col><col span="2"><col><col></colgroup><tbody>
</tbody>

I'd be calculating returns based on the ratios of index closing values. Given that your dates are in descending order, VLOOKUP is out, and we need a variation on INDEX/MATCH. Here's my take on an abridged version of your data.

C2: =INDEX(B\$2:B\$25,MATCH(EDATE(A2,-12),A\$2:A\$25,-1)+ISERROR(MATCH(EDATE(A2,-12),A\$2:A\$25,)))
D2: =B2/C2-1

For example, the 28 Dec 15 1-yr rolling return is based on the 26 Dec 14 closing value of 2,088.77.

I have used the same methodology to calculate some quarterly returns, and matched the numbers quoted by Morningstar here:
S&P 500 PR (SPX) Fund Performance and Returns

G10: =INDEX(\$B\$2:\$B\$25,MATCH(EDATE(G9+1,-3)-1,\$A\$2:\$A\$25,-1)+ISERROR(MATCH(EDATE(G9+1,-3)-1,\$A\$2:\$A\$25,)))
G11: =INDEX(\$B\$2:\$B\$25,MATCH(G9,\$A\$2:\$A\$25,-1)+ISERROR(MATCH(G9,\$A\$2:\$A\$25,)))
(Copy down and across)

Note the values highlighted which are based on the previous available day (i.e. 28th in both cases here).

Excel 2010
ABCDEFGHIJ
1DateCloseStartRolling 1-yr
231 Dec 152,043.942,058.90-0.73%
330 Dec 152,063.362,080.35-0.82%Morningstar
429 Dec 152,078.362,090.57-0.58%
528 Dec 152,056.502,088.77-1.54%20150.44-0.23-6.946.45
624 Dec 152,060.992,081.88-1.00%20141.34.690.614.39
723 Dec 152,064.292,082.17-0.86%201310.032.364.699.92
830 Sep 151,920.03
930 Jun 152,063.11Qtr to31/03/201530/06/201530/09/201531/12/2015
1029 May 152,107.39Open2,058.902,067.892,063.111,920.03
1131 Mar 152,067.89Close2,067.892,063.111,920.032,043.94
1231 Dec 142,058.90Return0.44%-0.23%-6.94%6.45%
1330 Dec 142,080.35
1429 Dec 142,090.57Qtr to31/03/201430/06/201430/09/201431/12/2014
1526 Dec 142,088.77Open1,848.361,872.341,960.231,972.29
1624 Dec 142,081.88Close1,872.341,960.231,972.292,058.90
1723 Dec 142,082.17Return1.30%4.69%0.62%4.39%
1830 Sep 141,972.29
1930 Jun 141,960.23Qtr to31/03/201330/06/201330/09/201331/12/2013
2031 Mar 141,872.34Open1,426.191,569.191,606.281,681.55
2131 Dec 131,848.36Close1,569.191,606.281,681.551,848.36
2230 Sep 131,681.55Return10.03%2.36%4.69%9.92%
2328 Jun 131,606.28
2428 Mar 131,569.19
2531 Dec 121,426.19

<tbody>
</tbody>

Last edited:
Stephen,

This is awesome! I completely agree it's easier using the ratio method. I am not very familiar with the iserror function but it looks like it automatically looks at the next closest available date. I am just curious - If the data was in ascending order would you prefer to use the lookup function over the index/match function? Thank you so much for the help. I may have a few questions regarding calculating caps on index returns, but let me get my spreadsheet fixed first. you are a life saver.

I'd be calculating returns based on the ratios of index closing values. Given that your dates are in descending order, VLOOKUP is out, and we need a variation on INDEX/MATCH. Here's my take on an abridged version of your data.

C2: =INDEX(B\$2:B\$25,MATCH(EDATE(A2,-12),A\$2:A\$25,-1)+ISERROR(MATCH(EDATE(A2,-12),A\$2:A\$25,)))
D2: =B2/C2-1

For example, the 28 Dec 15 1-yr rolling return is based on the 26 Dec 14 closing value of 2,088.77.

I have used the same methodology to calculate some quarterly returns, and matched the numbers quoted by Morningstar here:
S&P 500 PR (SPX) Fund Performance and Returns

G10: =INDEX(\$B\$2:\$B\$25,MATCH(EDATE(G9+1,-3)-1,\$A\$2:\$A\$25,-1)+ISERROR(MATCH(EDATE(G9+1,-3)-1,\$A\$2:\$A\$25,)))
G11: =INDEX(\$B\$2:\$B\$25,MATCH(G9,\$A\$2:\$A\$25,-1)+ISERROR(MATCH(G9,\$A\$2:\$A\$25,)))
(Copy down and across)

Note the values highlighted which are based on the previous available day (i.e. 28th in both cases here).

Excel 2010
ABCDEFGHIJ
1DateCloseStartRolling 1-yr
231 Dec 152,043.942,058.90-0.73%
330 Dec 152,063.362,080.35-0.82%Morningstar
429 Dec 152,078.362,090.57-0.58%
528 Dec 152,056.502,088.77-1.54%20150.44-0.23-6.946.45
624 Dec 152,060.992,081.88-1.00%20141.34.690.614.39
723 Dec 152,064.292,082.17-0.86%201310.032.364.699.92
830 Sep 151,920.03
930 Jun 152,063.11Qtr to31/03/201530/06/201530/09/201531/12/2015
1029 May 152,107.39Open2,058.902,067.892,063.111,920.03
1131 Mar 152,067.89Close2,067.892,063.111,920.032,043.94
1231 Dec 142,058.90Return0.44%-0.23%-6.94%6.45%
1330 Dec 142,080.35
1429 Dec 142,090.57Qtr to31/03/201430/06/201430/09/201431/12/2014
1526 Dec 142,088.77Open1,848.361,872.341,960.231,972.29
1624 Dec 142,081.88Close1,872.341,960.231,972.292,058.90
1723 Dec 142,082.17Return1.30%4.69%0.62%4.39%
1830 Sep 141,972.29
1930 Jun 141,960.23Qtr to31/03/201330/06/201330/09/201331/12/2013
2031 Mar 141,872.34Open1,426.191,569.191,606.281,681.55
2131 Dec 131,848.36Close1,569.191,606.281,681.551,848.36
2230 Sep 131,681.55Return10.03%2.36%4.69%9.92%
2328 Jun 131,606.28
2428 Mar 131,569.19
2531 Dec 121,426.19

<tbody>
</tbody>

I am not very familiar with the iserror function but it looks like it automatically looks at the next closest available date. I am just curious - If the data was in ascending order would you prefer to use the lookup function over the index/match function?

Yes, if the dates are in ascending order, you can use a simple VLOOKUP.

B7: =VLOOKUP(A7,A2:B4,2,TRUE)

But with dates in descending order, the formula is a little more complicated. There are various ways you could do this, but I have used:

E7: =INDEX(E2:E4,MATCH(D7,D2:D4,-1)+ISERROR(MATCH(D7,D2:D4,)))

MATCH(D7,D2:D4,-1) will return 1, i.e. corresponding to the date 2 July 2012.
Is this an exact match for 30 June 2012? No!
So ISERROR(MATCH(D7,D2:D4,) is TRUE, and 1 + TRUE = 2, i.e. we want the value in the 2nd row.

Excel 2010
ABCDE
1Ascending Date OrderDescending Date Order
2Thu 28 Jun 20121,000Mon 2 Jul 2012998
3Fri 29 Jun 20121,001Fri 29 Jun 20121,001
4Mon 2 Jul 2012998Thu 28 Jun 20121,000
5
6LookupLookup
7Sat 30 Jun 20121,001Sat 30 Jun 20121,001

<tbody>
</tbody>

Stephen,
Here is a tough one which is my ultimate goal. I have done this formula using multiple columns and then use a Max or Min comparison, but I was wondering if it can be done in one cell. Based on the returns I want to calculate the value of the return based on caps and predetermined loss protection... The return is a point to point return in this example 12 months. let's assume that over any 1 year period the max cap you can earn is 10% on the S&P 500. So if the index earns 12% for the year, your return is capped at 10%. If you earn 5% then you earn the entire 5%. Let's also assume that for any losses over that 1 year period you have protection of up to a 10% loss. So if you lose 8%, you lose nothing. If you lose 15%, then you only end up losing 5% because the first 10% loss is erased. I am assuming this would be a very complex if,then formula. Let me know if you can calculate this... Thanks so much for your help.

? Do you mean like this:

D6: =MEDIAN(PosCap,A6,MIN(A6+NegOffset,0))

Excel 2010
AB
1Positive Cap10%
2Negative Offset10%
3
4Returns:
612%10%
710%10%
88%8%
92%2%
100%0%
11-2%0%
12-10%0%
13-12%-2%
14-14%-4%

</tbody>

Wow! You really can come up with the same thing with unlimited formulas or approaches. Here is what I came up with.

=IF(\$J4>\$M\$2,MAX(\$M\$2),IF(AND(\$J4>0,\$J4<\$M\$2),\$J4,IF(\$J4<\$M\$3,-\$M\$3+\$J4,IF(AND(\$J4<0,\$J4>\$M\$3),0))))

J4 = Index return
M2 = PosCap = 10%
M3 = Negative offset

You seem to do everything much easier than I do.

Stepehen,
You have been tremendous. Now that the nuts and bolts of the spreadsheet are set. My goal is to run random dates comparing raw return versus cap/protected adjusted return on a set dollar amount over 1 year, 3 year and 6 year rolling periods. I would like to see each of the scenarios run for an investment period of 6 years. So if you calculate the one year rolling periods it would include 6 consecutive 1 year periods. If you use 3 year rolling periods, it would be 2 consecutive 3 year rolling periods and obviously 6 years would be one 6 year rolling period.

i.e. - input - A1 - date = 01/02/2003
A2 - \$amount - \$100,000

result #1 - compare six consecutive 1 year periods compounding the dollar amount on a raw versus cap/protected adjusted basis.

result #2 - compare 2 consecutive 3 year periods compounding the dollar amount on a raw versus cap/protected adjusted basis.

result #3 - compare one 6 year period compounding the dollar amount on a raw versus cap/protected adjusted basis.

The caps and offset will be adjusted to the length of rolling period. Below are the current caps and offsets for each term (rolling period).

 A B C D E F G H Term 1 1 3 3 6 6 6 Cap .1 .03 .26 .17 .82 .63 .43 Offset -.10 100 -.10 -.15 -.10 -.15 -.25

<tbody>
</tbody>

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>

I would only need to see how you calculate one rolling period over 6 years. Thank you so much for your help.

Replies
2
Views
102
Replies
8
Views
942
Replies
0
Views
488
Replies
5
Views
311
Replies
8
Views
1K

1,218,845
Messages
6,144,807
Members
450,567
Latest member
Mplz

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back