Stock market rolling period returns

mdelzi23

New Member
Joined
Feb 17, 2016
Messages
6
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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows
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

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

 

mdelzi23

New Member
Joined
Feb 17, 2016
Messages
6
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...:LOL:

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 CloseOne year 1 year
12/31/20152060.592062.542043.62 12/31/20152043.941/1/2015#N/A
12/30/20152077.342077.342061.9712/30/20152063.3612/31/2014-0.90%
12/29/20152060.542081.562060.5412/29/20152078.3612/30/2014-0.49%
12/28/20152057.772057.772044.212/28/20152056.512/29/2014-1.49%
12/24/20152063.522067.362058.7312/24/20152060.9912/25/2014#N/A
12/23/20152042.22064.732042.212/23/20152064.2912/24/2014-0.91%
12/22/20152023.152042.742020.4912/22/20152038.9712/23/2014-2.04%
12/21/20152010.272022.92005.9312/21/20152021.1512/22/2014-2.33%
12/18/20152040.812040.812005.3312/18/20152005.5512/19/2014-2.69%
12/17/20152073.762076.372041.6612/17/20152041.8912/18/20141.13%
12/16/20152046.52076.722042.4312/16/20152073.0712/17/20145.03%
12/15/20152025.552053.872025.5512/15/20152043.4112/16/20142.85%
12/14/20152013.372022.921993.2612/14/20152021.9412/15/20140.84%
12/11/20152047.272047.272008.812/11/20152012.3712/12/2014-0.89%
12/10/20152047.932067.652045.6712/10/20152052.2312/11/20141.20%
12/9/20152061.172080.332036.5312/9/20152047.6212/10/2014-0.55%
12/8/20152073.392073.852052.3212/8/20152063.5912/9/20140.34%
12/7/20152090.422090.422066.7812/7/20152077.0712/8/20140.11%
12/4/20152051.242093.842051.2412/4/20152091.6912/5/20140.91%
12/3/20152080.7120852042.3512/3/20152049.6212/4/2014-1.16%
12/2/20152101.712104.272077.1112/2/20152079.5112/3/20140.58%
12/1/20152082.932103.372082.9312/1/20152102.6312/2/20142.38%
11/30/20152090.952093.812080.4111/30/20152080.4112/1/20140.71%
11/27/20152088.822093.292084.1311/27/20152090.1111/28/20140.74%
11/25/20152089.320932086.311/25/20152088.8711/26/20141.04%
11/24/20152084.422094.122070.2911/24/20152089.1411/25/20140.92%
11/23/20152089.412095.612081.3911/23/20152086.5911/24/20141.04%
11/20/20152082.822097.062082.8211/20/20152089.1711/21/20141.54%
11/19/20152083.72086.742078.7611/19/20152081.2411/20/20141.73%
11/18/20152051.992085.312051.9911/18/20152083.5811/19/20141.58%
11/17/20152053.672066.692045.911/17/20152050.4411/18/20140.44%
11/16/20152022.082053.222019.3911/16/20152053.1911/17/20140.73%
11/13/20152044.642044.642022.0211/13/20152023.0411/14/2014-0.82%
11/12/20152072.292072.292045.6611/12/20152045.9711/13/20140.33%
11/11/20152083.412086.942074.8511/11/2015207511/12/20141.83%
11/10/20152077.192083.672069.9111/10/20152081.7211/11/20142.14%
11/9/20152096.562096.562068.2411/9/20152078.5811/10/20142.29%
11/6/20152098.62101.912083.7411/6/20152099.211/7/20143.29%
11/5/20152101.682108.782090.4111/5/20152099.9311/6/20143.79%
11/4/20152110.62114.592096.9811/4/20152102.3111/5/20144.32%
11/3/20152102.632116.482097.5111/3/20152109.7911/4/20144.66%
11/2/20152080.762106.22080.7611/2/20152104.0511/3/20144.25%
10/30/201520902094.322079.3410/30/20152079.3610/31/20143.91%
10/29/20152088.352092.522082.6310/29/20152089.4110/30/20145.55%
10/28/20152066.482090.352063.1110/28/20152090.3510/29/20145.40%
10/27/20152068.752070.372058.8410/27/20152065.8910/28/20145.18%
10/26/20152075.082075.142066.5310/26/20152071.1810/27/20145.51%
10/23/20152058.192079.742058.1910/23/20152075.1510/24/20146.33%
10/22/20152021.882055.22021.8810/22/20152052.5110/23/20146.29%
10/21/20152033.472037.972017.2210/21/20152018.9410/22/20144.00%
10/20/20152033.132039.122026.6110/20/20152030.7710/21/20146.36%
10/19/20152031.732034.452022.3110/19/20152033.6610/20/20147.85%
10/16/20152024.372033.542020.4610/16/20152033.1110/17/20149.02%
10/15/20151996.472024.151996.4710/15/20152023.8610/16/20149.05%
10/14/20152003.662009.561990.7310/14/20151994.2410/15/20146.41%
10/13/201520152022.342001.7810/13/20152003.6910/14/20146.74%
10/12/20152015.652018.662010.5510/12/20152017.4610/13/20145.87%
10/9/20152013.732020.132007.6110/9/20152014.8910/10/20144.64%
10/8/20151994.012016.51987.5310/8/20152013.4310/9/20142.33%
10/7/20151982.341999.311976.4410/7/20151995.8310/8/20143.11%
10/6/20151986.631991.621971.9910/6/20151979.9210/7/20140.89%
10/5/20151954.331989.171954.3310/5/20151987.0510/6/20140.86%
10/2/20151921.771951.361893.710/2/20151951.3610/3/20140.17%
10/1/20151919.651927.211900.710/1/20151923.8210/2/2014-1.13%
9/30/20151887.141920.531887.149/30/20151920.0310/1/2014-2.61%
9/29/20151881.91899.481871.919/29/20151884.099/30/2014-4.76%
9/28/20151929.181929.181879.219/28/20151881.779/29/2014-4.91%
9/25/20151935.931952.891921.59/25/20151931.349/26/2014-1.77%
9/24/20151934.811937.171908.929/24/20151932.249/25/2014-3.26%
9/23/20151943.241949.521932.579/23/20151938.769/24/2014-2.25%
9/22/20151961.391961.391929.229/22/20151942.749/23/2014-2.51%
9/21/20151960.841979.641955.89/21/20151966.979/22/2014-2.10%
9/18/20151989.661989.661953.459/18/20151958.039/19/2014-2.72%
9/17/20151995.332020.861986.739/17/20151990.29/18/2014-0.64%
9/16/20151978.021997.261977.939/16/20151995.319/17/2014-0.20%
9/15/20151955.11983.191954.39/15/20151978.099/16/2014-0.19%
9/14/20151963.061963.061948.279/14/20151953.039/15/2014-1.66%
9/11/20151951.451961.051939.199/11/20151961.059/12/2014-1.79%
9/10/20151941.591965.291937.199/10/20151952.299/11/2014-2.04%
9/9/20151971.451988.631937.889/9/20151942.049/10/2014-2.33%
9/8/20151927.31970.421927.39/8/20151969.419/9/2014-1.57%
9/4/20151947.761947.761911.219/4/20151921.229/5/2014-3.84%
9/3/20151950.791975.011944.729/3/20151951.139/4/2014-2.52%
9/2/20151916.521948.911916.529/2/20151948.869/3/2014-2.73%
9/1/20151970.091970.091903.079/1/20151913.859/2/2014-4.50%
8/31/20151986.731986.731965.988/31/20151972.189/1/2014#N/A
8/28/20151986.061993.481975.198/28/20151988.878/29/2014-0.48%
8/27/20151942.771989.61942.778/27/20151987.668/28/2014-0.49%
8/26/20151872.751943.091872.758/26/20151940.518/27/2014-3.00%
8/25/20151898.081948.041867.088/25/20151867.618/26/2014-6.55%
8/24/20151965.151965.151867.018/24/20151893.218/25/2014-4.95%
8/21/20152034.082034.081970.898/21/20151970.898/22/2014-1.09%
8/20/20152076.612076.612035.738/20/20152035.738/21/20142.46%
8/19/20152095.692096.172070.538/19/20152079.618/20/20145.01%
8/18/20152101.992103.472094.148/18/20152096.928/19/20146.30%
8/17/20152089.72102.872079.38/17/20152102.448/18/20147.36%
8/14/20152083.152092.452080.618/14/20152091.548/15/20146.77%
8/13/20152086.192092.932078.268/13/20152083.398/14/20146.98%
8/12/20152081.12089.062052.098/12/20152086.058/13/20147.77%
8/11/20152102.662102.662076.498/11/20152084.078/12/20147.66%
8/10/20152080.982105.352080.988/10/20152104.188/11/20148.83%
8/7/20152082.612082.612067.918/7/20152077.578/8/20148.75%
8/6/20152100.752103.322075.538/6/20152083.568/7/20148.35%
8/5/20152095.272112.662095.278/5/20152099.848/6/20149.52%
8/4/20152097.682102.512088.68/4/20152093.328/5/20148.11%
8/3/20152104.492105.72087.318/3/20152098.048/4/20148.90%
7/31/20152111.62114.242102.077/31/20152103.848/1/20149.02%
7/30/20152106.782110.482094.977/30/20152108.637/31/20147.30%
7/29/20152094.72110.62094.087/29/20152108.577/30/20146.86%
7/28/20152070.752095.62069.097/28/20152093.257/29/20145.72%
7/27/20152078.192078.192063.527/27/20152067.647/28/20144.52%
7/24/20152102.242106.012077.097/24/20152079.657/25/20144.79%
7/23/20152114.162116.872098.637/23/20152102.157/24/20145.74%
7/22/20152118.212118.5121107/22/20152114.157/23/20146.49%
7/21/20152127.552128.492115.47/21/20152119.217/22/20147.27%
7/20/20152126.852132.822123.667/20/20152128.287/21/20147.66%
7/17/20152126.82128.912119.887/17/20152126.647/18/20148.42%
7/16/20152110.552124.422110.557/16/20152124.297/17/20147.30%
7/15/20152109.012114.142102.497/15/20152107.47/16/20146.63%
7/14/20152099.722111.982098.187/14/20152108.957/15/20146.65%
7/13/20152080.032100.672080.037/13/20152099.67/14/20146.59%
7/10/20152052.742081.312052.747/10/20152076.627/11/20145.64%
7/9/20152049.732074.282049.737/9/20152051.317/10/20144.30%
7/8/20152077.662077.662044.667/8/20152046.687/9/20144.15%
7/7/20152069.522083.742044.027/7/20152081.347/8/20145.31%
7/6/20152073.952078.612058.47/6/20152068.767/7/20144.26%
7/2/20152078.032085.062071.027/2/20152076.787/3/20145.11%
7/1/201520672082.7820677/1/20152077.427/2/20145.29%
6/30/20152061.192074.282056.326/30/20152063.117/1/20145.14%
6/29/20152098.632098.632056.646/29/20152057.646/30/20144.94%
6/26/20152102.622108.922095.386/26/20152101.496/27/20147.41%
6/25/20152109.962116.042101.786/25/20152102.316/26/20147.27%
6/24/20152123.652125.12108.586/24/20152108.586/25/20148.17%
6/23/20152123.162128.032119.896/23/20152124.26/24/20148.27%
6/22/20152112.52129.872112.56/22/20152122.856/23/20148.15%
6/19/20152121.062121.642109.456/19/20152109.996/20/20147.63%
6/18/20152101.582126.652101.586/18/20152121.246/19/20148.36%
6/17/20152097.42106.792088.866/17/20152100.446/18/20148.12%
6/16/20152084.262097.42082.16/16/20152096.296/17/20148.22%
6/15/20152091.342091.342072.496/15/20152084.436/16/20147.73%
6/12/20152107.432107.432091.336/12/20152094.116/13/20148.46%
6/11/20152106.242115.022106.246/11/20152108.866/12/20148.52%
6/10/20152081.122108.52081.126/10/20152105.26/11/20147.99%
6/9/20152079.072085.622072.146/9/20152080.156/10/20146.66%
6/8/20152092.342093.012079.116/8/20152079.286/9/20146.69%
6/5/20152095.092100.992085.676/5/20152092.836/6/20147.74%
6/4/20152112.352112.892093.236/4/20152095.846/5/20148.68%
6/3/20152110.642121.922109.616/3/20152114.076/4/20149.93%
6/2/20152110.412117.592099.146/2/20152109.66/3/20149.70%
6/1/20152108.642119.152102.546/1/20152111.736/2/20149.76%
5/29/20152120.662120.662104.895/29/20152107.395/30/20149.74%
5/28/20152122.272122.272112.865/28/20152120.795/29/201411.00%
5/27/20152105.132126.222105.135/27/20152123.485/28/201411.07%
5/26/20152125.342125.342099.185/26/20152104.25/27/201410.63%
5/22/20152130.362132.152126.065/22/20152126.065/23/201412.29%
5/21/20152125.552134.282122.955/21/20152130.825/22/201412.85%
5/20/20152127.792134.722122.595/20/20152125.855/21/201413.48%
5/19/20152129.452133.022124.55/19/20152127.835/20/201412.89%
5/18/20152121.32131.782120.015/18/20152129.25/19/201413.46%
5/15/20152122.072123.892116.815/15/20152122.735/16/201413.44%
5/14/20152100.432121.452100.435/14/20152121.15/15/201412.34%
5/13/20152099.622110.192096.045/13/20152098.485/14/201410.61%
5/12/20152102.872105.062085.575/12/20152099.125/13/201410.67%
5/11/20152115.562117.692104.585/11/20152105.335/12/201411.98%
5/8/20152092.132117.662092.135/8/20152116.15/9/201412.84%
5/7/20152079.962092.92074.995/7/201520885/8/201411.22%
5/6/20152091.262098.422067.935/6/20152080.155/7/201411.33%
5/5/20152112.632115.242088.465/5/20152089.465/6/201410.92%
5/4/20152110.232120.952110.235/4/20152114.495/5/201412.51%
5/1/20152087.382108.412087.385/1/20152108.295/2/201411.83%
4/30/20152105.522105.522077.594/30/20152085.515/1/201410.67%
4/29/20152112.492113.652097.414/29/20152106.854/30/201412.24%
4/28/20152108.352116.042094.894/28/20152114.764/29/201413.04%
4/27/20152119.292125.922107.044/27/20152108.924/28/201413.08%
4/24/20152112.82120.922112.84/24/20152117.694/25/201412.78%
4/23/20152107.212120.492103.194/23/20152112.934/24/201412.27%
4/22/20152098.272109.982091.054/22/20152107.964/23/201412.17%
4/21/20152102.822109.642094.384/21/20152097.294/22/201412.00%
4/20/20152084.112103.942084.114/20/20152100.44/21/201412.57%
4/17/20152102.582102.582072.374/17/20152081.184/18/2014#N/A
4/16/20152105.962111.32100.024/16/20152104.994/17/201413.07%
4/15/20152097.822111.912097.824/15/20152106.634/16/201414.12%
4/14/20152092.282098.622083.244/14/20152095.844/15/201414.44%
4/13/20152102.032107.652092.334/13/20152092.434/14/201415.08%
4/10/20152091.512102.612091.514/10/20152102.064/11/201414.83%
4/9/20152081.292093.312074.294/9/20152091.184/10/201411.69%
4/8/20152076.942086.692073.34/8/20152081.94/9/201412.37%
4/7/20152080.792089.812076.14/7/20152076.334/8/201412.51%
4/6/20152064.872086.992056.524/6/20152080.624/7/201411.63%
4/2/20152060.032072.172057.324/2/20152066.964/3/20149.28%
4/1/20152067.632067.632048.384/1/20152059.694/2/20149.17%
3/31/20152084.052084.052067.043/31/20152067.894/1/201410.35%
3/30/20152064.112088.972064.113/30/20152086.243/31/201412.21%
3/27/20152055.782062.832052.963/27/20152061.023/28/201411.40%
3/26/20152059.942067.152045.53/26/20152056.153/27/201411.02%
3/25/20152093.12097.432061.053/25/20152061.053/26/201410.39%
3/24/20152103.942107.632091.53/24/20152091.53/25/201412.48%
3/23/20152107.992114.862104.423/23/20152104.423/24/201412.68%
3/20/20152090.322113.922090.323/20/20152108.13/21/201412.46%
3/19/20152098.692098.692085.563/19/20152089.273/20/201412.32%
3/18/20152072.842106.852061.233/18/20152099.53/19/201412.14%
3/17/20152080.592080.592065.083/17/20152074.283/18/201411.59%
3/16/20152055.352081.412055.353/16/20152081.193/17/201412.94%
3/13/20152064.562064.562041.173/13/20152053.43/14/201411.29%
3/12/20152041.12066.412041.13/12/20152065.953/13/201410.53%
3/11/20152044.692050.082039.693/11/20152040.243/12/20149.33%
3/10/20152076.142076.142044.163/10/20152044.163/11/20148.83%
3/9/20152072.252083.492072.213/9/20152079.433/10/201410.73%
3/6/20152100.912100.912067.273/6/20152071.263/7/201410.26%
3/5/20152098.542104.252095.223/5/20152101.043/6/201412.10%
3/4/20152107.722107.722094.493/4/20152098.533/5/201411.98%
3/3/20152115.762115.762098.263/3/20152107.783/4/201413.98%
3/2/20152105.232117.522104.53/2/20152117.393/3/201413.98%
2/27/20152110.882112.742103.752/27/20152104.52/28/201413.44%
2/26/20152113.912113.912103.762/26/20152110.742/27/201414.41%
2/25/20152115.32119.592109.892/25/20152113.862/26/201414.52%
2/24/20152109.12117.942105.872/24/20152115.482/25/201414.50%
2/23/20152109.832110.0521032/23/20152109.662/24/201414.86%
2/20/20152097.652110.612085.442/20/20152110.32/21/201414.62%
2/19/20152099.252102.132090.792/19/20152097.452/20/201414.66%
2/18/20152099.162100.232092.152/18/20152099.682/19/201414.18%
2/17/20152096.472101.32089.82/17/20152100.342/18/201414.21%
2/13/20152088.782097.032086.72/13/20152096.992/14/201414.69%
2/12/20152069.982088.532069.982/12/20152088.482/13/201415.08%
2/11/20152068.552073.482057.992/11/20152068.532/12/201413.65%
2/10/20152049.382070.862048.622/10/20152068.592/11/201414.89%
2/9/20152053.472056.162041.882/9/20152046.742/10/201413.95%
2/6/20152062.282072.42049.972/6/20152055.472/7/201415.74%
2/5/20152043.452063.552043.452/5/20152062.522/6/201417.66%
2/4/20152048.862054.742036.722/4/20152041.512/5/201416.43%
2/3/20152022.712050.32022.712/3/20152050.032/4/201417.56%
2/2/20151996.672021.661980.92/2/20152020.852/3/201413.36%
1/30/20152019.352023.321993.381/30/20151994.991/31/201411.40%
1/29/20152002.452024.641989.181/29/20152021.251/30/201413.73%
1/28/20152032.342042.492001.491/28/20152002.161/29/201411.84%
1/27/20152047.862047.862019.911/27/20152029.551/28/201413.83%
1/26/20152050.422057.622040.971/26/20152057.091/27/201414.86%
1/23/20152062.982062.982050.541/23/20152051.821/24/201412.31%
1/22/20152034.32064.622026.381/22/20152063.151/23/201411.99%
1/21/20152020.192038.292012.041/21/20152032.121/22/201410.16%
1/20/20152020.762028.942004.491/20/20152022.551/21/20149.86%
1/16/20151992.252020.461988.121/16/20152019.421/17/20149.50%
1/15/20152013.752021.351991.471/15/20151992.671/16/20147.83%
1/14/20152018.42018.41988.441/14/20152011.271/15/20149.28%
1/13/20152031.582056.932008.251/13/20152023.031/14/201411.07%
1/12/20152046.132049.32022.581/12/20152028.261/13/201410.16%
1/9/20152063.452064.432038.331/9/20152044.811/10/201411.13%
1/8/20152030.612064.082030.611/8/20152062.141/9/201412.13%
1/7/20152005.552029.612005.551/7/20152025.91/8/201410.23%
1/6/20152022.152030.251992.441/6/20152002.611/7/20149.51%
1/5/20152054.442054.442017.341/5/20152020.581/6/201410.28%
1/2/20152058.92072.362046.041/2/20152058.21/3/201412.27%
12/31/20142082.112085.582057.9412/31/20142058.91/1/2014#N/A
12/30/20142088.492088.492079.5312/30/20142080.3512/31/201312.90%
12/29/20142087.632093.552085.7512/29/20142090.5712/30/201313.53%
12/26/20142084.32092.72084.312/26/20142088.7712/27/201313.34%

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

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows
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:

mdelzi23

New Member
Joined
Feb 17, 2016
Messages
6

ADVERTISEMENT

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>
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows
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>
 

mdelzi23

New Member
Joined
Feb 17, 2016
Messages
6

ADVERTISEMENT

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

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows
? Do you mean like this:

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

Excel 2010
AB
1Positive Cap10%
2Negative Offset10%
3
4Returns:
5RawAdjusted
612%10%
710%10%
88%8%
92%2%
100%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

New Member
Joined
Feb 17, 2016
Messages
6
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. :eek:
 

mdelzi23

New Member
Joined
Feb 17, 2016
Messages
6
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
BCDEFGH
Term1133666
Cap.1.03.26.17.82.63.43
Offset-.10100-.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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,169
Messages
5,640,550
Members
417,151
Latest member
ChickenTenderer

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top