Stock market rolling period returns

[mdelzi23]

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

 

[StephenCrump]

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

	A	B
1	Available index values
2	Thu 28 Jun 2012	1,000
3	Fri 29 Jun 2012	1,001
4	Mon 2 Jul 2012	998

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

​

 

[mdelzi23]

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

	A	B
1	Available index values
2	Thu 28 Jun 2012	1,000
3	Fri 29 Jun 2012	1,001
4	Mon 2 Jul 2012	998

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

 

[StephenCrump]

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

	A	B	C	D	E	F	G	H	I	J
1	Date	Close	Start	Rolling 1-yr
2	31 Dec 15	2,043.94	2,058.90	-0.73%
3	30 Dec 15	2,063.36	2,080.35	-0.82%		Morningstar
4	29 Dec 15	2,078.36	2,090.57	-0.58%
5	28 Dec 15	2,056.50	2,088.77	-1.54%		2015	0.44	-0.23	-6.94	6.45
6	24 Dec 15	2,060.99	2,081.88	-1.00%		2014	1.3	4.69	0.61	4.39
7	23 Dec 15	2,064.29	2,082.17	-0.86%		2013	10.03	2.36	4.69	9.92
8	30 Sep 15	1,920.03
9	30 Jun 15	2,063.11				Qtr to	31/03/2015	30/06/2015	30/09/2015	31/12/2015
10	29 May 15	2,107.39				Open	2,058.90	2,067.89	2,063.11	1,920.03
11	31 Mar 15	2,067.89				Close	2,067.89	2,063.11	1,920.03	2,043.94
12	31 Dec 14	2,058.90				Return	0.44%	-0.23%	-6.94%	6.45%
13	30 Dec 14	2,080.35
14	29 Dec 14	2,090.57				Qtr to	31/03/2014	30/06/2014	30/09/2014	31/12/2014
15	26 Dec 14	2,088.77				Open	1,848.36	1,872.34	1,960.23	1,972.29
16	24 Dec 14	2,081.88				Close	1,872.34	1,960.23	1,972.29	2,058.90
17	23 Dec 14	2,082.17				Return	1.30%	4.69%	0.62%	4.39%
18	30 Sep 14	1,972.29
19	30 Jun 14	1,960.23				Qtr to	31/03/2013	30/06/2013	30/09/2013	31/12/2013
20	31 Mar 14	1,872.34				Open	1,426.19	1,569.19	1,606.28	1,681.55
21	31 Dec 13	1,848.36				Close	1,569.19	1,606.28	1,681.55	1,848.36
22	30 Sep 13	1,681.55				Return	10.03%	2.36%	4.69%	9.92%
23	28 Jun 13	1,606.28
24	28 Mar 13	1,569.19
25	31 Dec 12	1,426.19

<tbody>
</tbody>

 

[mdelzi23]

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

	A	B	C	D	E	F	G	H	I	J
1	Date	Close	Start	Rolling 1-yr
2	31 Dec 15	2,043.94	2,058.90	-0.73%
3	30 Dec 15	2,063.36	2,080.35	-0.82%		Morningstar
4	29 Dec 15	2,078.36	2,090.57	-0.58%
5	28 Dec 15	2,056.50	2,088.77	-1.54%		2015	0.44	-0.23	-6.94	6.45
6	24 Dec 15	2,060.99	2,081.88	-1.00%		2014	1.3	4.69	0.61	4.39
7	23 Dec 15	2,064.29	2,082.17	-0.86%		2013	10.03	2.36	4.69	9.92
8	30 Sep 15	1,920.03
9	30 Jun 15	2,063.11				Qtr to	31/03/2015	30/06/2015	30/09/2015	31/12/2015
10	29 May 15	2,107.39				Open	2,058.90	2,067.89	2,063.11	1,920.03
11	31 Mar 15	2,067.89				Close	2,067.89	2,063.11	1,920.03	2,043.94
12	31 Dec 14	2,058.90				Return	0.44%	-0.23%	-6.94%	6.45%
13	30 Dec 14	2,080.35
14	29 Dec 14	2,090.57				Qtr to	31/03/2014	30/06/2014	30/09/2014	31/12/2014
15	26 Dec 14	2,088.77				Open	1,848.36	1,872.34	1,960.23	1,972.29
16	24 Dec 14	2,081.88				Close	1,872.34	1,960.23	1,972.29	2,058.90
17	23 Dec 14	2,082.17				Return	1.30%	4.69%	0.62%	4.39%
18	30 Sep 14	1,972.29
19	30 Jun 14	1,960.23				Qtr to	31/03/2013	30/06/2013	30/09/2013	31/12/2013
20	31 Mar 14	1,872.34				Open	1,426.19	1,569.19	1,606.28	1,681.55
21	31 Dec 13	1,848.36				Close	1,569.19	1,606.28	1,681.55	1,848.36
22	30 Sep 13	1,681.55				Return	10.03%	2.36%	4.69%	9.92%
23	28 Jun 13	1,606.28
24	28 Mar 13	1,569.19
25	31 Dec 12	1,426.19

<tbody>
</tbody>

 

[StephenCrump]

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

	A	B	C	D	E
1	Ascending Date Order			Descending Date Order
2	Thu 28 Jun 2012	1,000		Mon 2 Jul 2012	998
3	Fri 29 Jun 2012	1,001		Fri 29 Jun 2012	1,001
4	Mon 2 Jul 2012	998		Thu 28 Jun 2012	1,000
5
6	Lookup			Lookup
7	Sat 30 Jun 2012	1,001		Sat 30 Jun 2012	1,001

<tbody>
</tbody>

 

[mdelzi23]

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.

 

[StephenCrump]

? Do you mean like this:

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

Excel 2010

	A	B
1	Positive Cap	10%
2	Negative Offset	10%
3
4	Returns:
5	Raw	Adjusted
6	12%	10%
7	10%	10%
8	8%	8%
9	2%	2%
10	0%	0%
11	-2%	0%
12	-10%	0%
13	-12%	-2%
14	-14%	-4%

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

​

 

[mdelzi23]

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.

 

[mdelzi23]

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.