Computing benchmark return daily

[juneauscott]

Hi,

I'm trying to calculate a 1 month benchmark return for an investment portfolio. I've encountered an issue where by using the daily index rate of return to compute a 1 month rate of return and multiplying that by the allocation does not give me the same result as calculating each days index return using the allocation and the daily return of the index. I've used array formulas to compute the product where required. Can someone help me resolve this variance? Thank you in advance.

Results:
[TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="class: xl65, width: 64"]Index Return[/TD]
[TD="class: xl65, width: 64"]Index Factor[/TD]
[TD="class: xl65, width: 64"]Allocation Target[/TD]
[TD="class: xl65, width: 64"]Portfolio Benchmark Return[/TD]
[TD="class: xl65, width: 64"]PBR Factor[/TD]
[/TR]
[TR]
[TD="align: left"]1/2/2019[/TD]
[TD="class: xl66, align: right"]-0.69%[/TD]
[TD="class: xl67, align: left"] 0.9931 [/TD]
[TD="class: xl66, align: right"]21.00%[/TD]
[TD="class: xl67, align: left"] (0.0014)[/TD]
[TD="class: xl67, align: left"] 0.9986 [/TD]
[/TR]
[TR]
[TD="align: left"]1/3/2019[/TD]
[TD="class: xl66, align: right"]-0.13%[/TD]
[TD="class: xl67, align: left"] 0.9987 [/TD]
[TD="class: xl66, align: right"]21.00%[/TD]
[TD="class: xl67, align: left"] (0.0003)[/TD]
[TD="class: xl67, align: left"] 0.9997 [/TD]
[/TR]
[TR]
[TD="align: left"]1/4/2019[/TD]
[TD="class: xl66, align: right"]1.64%[/TD]
[TD="class: xl67, align: left"] 1.0164 [/TD]
[TD="class: xl66, align: right"]21.00%[/TD]
[TD="class: xl67, align: left"] 0.0034 [/TD]
[TD="class: xl67, align: left"] 1.0034 [/TD]
[/TR]
[TR]
[TD="align: left"]1/7/2019[/TD]
[TD="class: xl66, align: right"]1.04%[/TD]
[TD="class: xl67, align: left"] 1.0104 [/TD]
[TD="class: xl66, align: right"]21.00%[/TD]
[TD="class: xl67, align: left"] 0.0022 [/TD]
[TD="class: xl67, align: left"] 1.0022 [/TD]
[/TR]
[TR]
[TD="align: left"]1/8/2019[/TD]
[TD="class: xl66, align: right"]0.37%[/TD]
[TD="class: xl67, align: left"] 1.0037 [/TD]
[TD="class: xl66, align: right"]21.00%[/TD]
[TD="class: xl67, align: left"] 0.0008 [/TD]
[TD="class: xl67, align: left"] 1.0008 [/TD]
[/TR]
[TR]
[TD="align: left"]1/9/2019[/TD]
[TD="class: xl66, align: right"]1.51%[/TD]
[TD="class: xl67, align: left"] 1.0151 [/TD]
[TD="class: xl66, align: right"]21.00%[/TD]
[TD="class: xl67, align: left"] 0.0032 [/TD]
[TD="class: xl67, align: left"] 1.0032 [/TD]
[/TR]
[TR]
[TD="align: left"]1/10/2019[/TD]
[TD="class: xl66, align: right"]0.25%[/TD]
[TD="class: xl67, align: left"] 1.0025 [/TD]
[TD="class: xl66, align: right"]21.00%[/TD]
[TD="class: xl67, align: left"] 0.0005 [/TD]
[TD="class: xl67, align: left"] 1.0005 [/TD]
[/TR]
[TR]
[TD="align: left"]1/11/2019[/TD]
[TD="class: xl66, align: right"]0.03%[/TD]
[TD="class: xl67, align: left"] 1.0003 [/TD]
[TD="class: xl66, align: right"]21.00%[/TD]
[TD="class: xl67, align: left"] 0.0001 [/TD]
[TD="class: xl67, align: left"] 1.0001 [/TD]
[/TR]
[TR]
[TD="align: left"]1/14/2019[/TD]
[TD="class: xl66, align: right"]-0.46%[/TD]
[TD="class: xl67, align: left"] 0.9954 [/TD]
[TD="class: xl66, align: right"]21.00%[/TD]
[TD="class: xl67, align: left"] (0.0010)[/TD]
[TD="class: xl67, align: left"] 0.9990 [/TD]
[/TR]
[TR]
[TD="align: left"]1/15/2019[/TD]
[TD="class: xl66, align: right"]0.59%[/TD]
[TD="class: xl67, align: left"] 1.0059 [/TD]
[TD="class: xl66, align: right"]21.00%[/TD]
[TD="class: xl67, align: left"] 0.0012 [/TD]
[TD="class: xl67, align: left"] 1.0012 [/TD]
[/TR]
[TR]
[TD="align: left"]1/16/2019[/TD]
[TD="class: xl66, align: right"]0.10%[/TD]
[TD="class: xl67, align: left"] 1.0010 [/TD]
[TD="class: xl66, align: right"]21.00%[/TD]
[TD="class: xl67, align: left"] 0.0002 [/TD]
[TD="class: xl67, align: left"] 1.0002 [/TD]
[/TR]
[TR]
[TD="align: left"]1/17/2019[/TD]
[TD="class: xl66, align: right"]-0.03%[/TD]
[TD="class: xl67, align: left"] 0.9997 [/TD]
[TD="class: xl66, align: right"]21.00%[/TD]
[TD="class: xl67, align: left"] (0.0001)[/TD]
[TD="class: xl67, align: left"] 0.9999 [/TD]
[/TR]
[TR]
[TD="align: left"]1/18/2019[/TD]
[TD="class: xl66, align: right"]1.16%[/TD]
[TD="class: xl67, align: left"] 1.0116 [/TD]
[TD="class: xl66, align: right"]21.00%[/TD]
[TD="class: xl67, align: left"] 0.0024 [/TD]
[TD="class: xl67, align: left"] 1.0024 [/TD]
[/TR]
[TR]
[TD="align: left"]1/22/2019[/TD]
[TD="class: xl66, align: right"]-0.63%[/TD]
[TD="class: xl67, align: left"] 0.9937 [/TD]
[TD="class: xl66, align: right"]21.00%[/TD]
[TD="class: xl67, align: left"] (0.0013)[/TD]
[TD="class: xl67, align: left"] 0.9987 [/TD]
[/TR]
[TR]
[TD="align: left"]1/23/2019[/TD]
[TD="class: xl66, align: right"]-0.08%[/TD]
[TD="class: xl67, align: left"] 0.9992 [/TD]
[TD="class: xl66, align: right"]21.00%[/TD]
[TD="class: xl67, align: left"] (0.0002)[/TD]
[TD="class: xl67, align: left"] 0.9998 [/TD]
[/TR]
[TR]
[TD="align: left"]1/24/2019[/TD]
[TD="class: xl66, align: right"]0.32%[/TD]
[TD="class: xl67, align: left"] 1.0032 [/TD]
[TD="class: xl66, align: right"]21.00%[/TD]
[TD="class: xl67, align: left"] 0.0007 [/TD]
[TD="class: xl67, align: left"] 1.0007 [/TD]
[/TR]
[TR]
[TD="align: left"]1/25/2019[/TD]
[TD="class: xl66, align: right"]1.09%[/TD]
[TD="class: xl67, align: left"] 1.0109 [/TD]
[TD="class: xl66, align: right"]21.00%[/TD]
[TD="class: xl67, align: left"] 0.0023 [/TD]
[TD="class: xl67, align: left"] 1.0023 [/TD]
[/TR]
[TR]
[TD="align: left"]1/28/2019[/TD]
[TD="class: xl66, align: right"]-0.41%[/TD]
[TD="class: xl67, align: left"] 0.9959 [/TD]
[TD="class: xl66, align: right"]21.00%[/TD]
[TD="class: xl67, align: left"] (0.0009)[/TD]
[TD="class: xl67, align: left"] 0.9991 [/TD]
[/TR]
[TR]
[TD="align: left"]1/29/2019[/TD]
[TD="class: xl66, align: right"]0.28%[/TD]
[TD="class: xl67, align: left"] 1.0028 [/TD]
[TD="class: xl66, align: right"]21.00%[/TD]
[TD="class: xl67, align: left"] 0.0006 [/TD]
[TD="class: xl67, align: left"] 1.0006 [/TD]
[/TR]
[TR]
[TD="align: left"]1/30/2019[/TD]
[TD="class: xl66, align: right"]0.39%[/TD]
[TD="class: xl67, align: left"] 1.0039 [/TD]
[TD="class: xl66, align: right"]21.00%[/TD]
[TD="class: xl67, align: left"] 0.0008 [/TD]
[TD="class: xl67, align: left"] 1.0008 [/TD]
[/TR]
[TR]
[TD="align: left"]1/31/2019[/TD]
[TD="class: xl66, align: right"]0.99%[/TD]
[TD="class: xl67, align: left"] 1.0099 [/TD]
[TD="class: xl66, align: right"]21.00%[/TD]
[TD="class: xl67, align: left"] 0.0021 [/TD]
[TD="class: xl67, align: left"] 1.0021 [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl68"]Index Return[/TD]
[TD="class: xl69, align: right"]7.548%[/TD]
[TD][/TD]
[TD="class: xl70"]Pofo daily bmrk 1-mo return[/TD]
[TD="class: xl69, align: right"]1.550%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl68"]Index*1Mo Return[/TD]
[TD="class: xl69, align: right"]1.585%[/TD]
[TD][/TD]
[TD="class: xl71"]Variance[/TD]
[TD="class: xl72, align: right"]0.035%[/TD]
[/TR]
</tbody>[/TABLE]

Formulas:
[TABLE="width: 818"]
<colgroup><col span="3"><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Index Return[/TD]
[TD]Index Factor[/TD]
[TD]Allocation Target[/TD]
[TD]Portfolio Benchmark Return[/TD]
[TD]PBR Factor[/TD]
[/TR]
[TR]
[TD="align: left"]1/2/2019[/TD]
[TD="align: left"]=C2-1[/TD]
[TD="align: left"]0.99310811702041[/TD]
[TD="align: left"]0.21[/TD]
[TD="align: left"]=B2*D2[/TD]
[TD="align: left"]=E2+1[/TD]
[/TR]
[TR]
[TD="align: left"]1/3/2019[/TD]
[TD="align: left"]=C3-1[/TD]
[TD="align: left"]0.998737746127298[/TD]
[TD="align: left"]0.21[/TD]
[TD="align: left"]=B3*D3[/TD]
[TD="align: left"]=E3+1[/TD]
[/TR]
[TR]
[TD="align: left"]1/4/2019[/TD]
[TD="align: left"]=C4-1[/TD]
[TD="align: left"]1.01635865741571[/TD]
[TD="align: left"]0.21[/TD]
[TD="align: left"]=B4*D4[/TD]
[TD="align: left"]=E4+1[/TD]
[/TR]
[TR]
[TD="align: left"]1/7/2019[/TD]
[TD="align: left"]=C5-1[/TD]
[TD="align: left"]1.01043776259165[/TD]
[TD="align: left"]0.21[/TD]
[TD="align: left"]=B5*D5[/TD]
[TD="align: left"]=E5+1[/TD]
[/TR]
[TR]
[TD="align: left"]1/8/2019[/TD]
[TD="align: left"]=C6-1[/TD]
[TD="align: left"]1.00369341336522[/TD]
[TD="align: left"]0.21[/TD]
[TD="align: left"]=B6*D6[/TD]
[TD="align: left"]=E6+1[/TD]
[/TR]
[TR]
[TD="align: left"]1/9/2019[/TD]
[TD="align: left"]=C7-1[/TD]
[TD="align: left"]1.01506175252128[/TD]
[TD="align: left"]0.21[/TD]
[TD="align: left"]=B7*D7[/TD]
[TD="align: left"]=E7+1[/TD]
[/TR]
[TR]
[TD="align: left"]1/10/2019[/TD]
[TD="align: left"]=C8-1[/TD]
[TD="align: left"]1.00250372895802[/TD]
[TD="align: left"]0.21[/TD]
[TD="align: left"]=B8*D8[/TD]
[TD="align: left"]=E8+1[/TD]
[/TR]
[TR]
[TD="align: left"]1/11/2019[/TD]
[TD="align: left"]=C9-1[/TD]
[TD="align: left"]1.00027920643844[/TD]
[TD="align: left"]0.21[/TD]
[TD="align: left"]=B9*D9[/TD]
[TD="align: left"]=E9+1[/TD]
[/TR]
[TR]
[TD="align: left"]1/14/2019[/TD]
[TD="align: left"]=C10-1[/TD]
[TD="align: left"]0.99543515388182[/TD]
[TD="align: left"]0.21[/TD]
[TD="align: left"]=B10*D10[/TD]
[TD="align: left"]=E10+1[/TD]
[/TR]
[TR]
[TD="align: left"]1/15/2019[/TD]
[TD="align: left"]=C11-1[/TD]
[TD="align: left"]1.00589232406238[/TD]
[TD="align: left"]0.21[/TD]
[TD="align: left"]=B11*D11[/TD]
[TD="align: left"]=E11+1[/TD]
[/TR]
[TR]
[TD="align: left"]1/16/2019[/TD]
[TD="align: left"]=C12-1[/TD]
[TD="align: left"]1.00104944442273[/TD]
[TD="align: left"]0.21[/TD]
[TD="align: left"]=B12*D12[/TD]
[TD="align: left"]=E12+1[/TD]
[/TR]
[TR]
[TD="align: left"]1/17/2019[/TD]
[TD="align: left"]=C13-1[/TD]
[TD="align: left"]0.999742911589016[/TD]
[TD="align: left"]0.21[/TD]
[TD="align: left"]=B13*D13[/TD]
[TD="align: left"]=E13+1[/TD]
[/TR]
[TR]
[TD="align: left"]1/18/2019[/TD]
[TD="align: left"]=C14-1[/TD]
[TD="align: left"]1.01164821633345[/TD]
[TD="align: left"]0.21[/TD]
[TD="align: left"]=B14*D14[/TD]
[TD="align: left"]=E14+1[/TD]
[/TR]
[TR]
[TD="align: left"]1/22/2019[/TD]
[TD="align: left"]=C15-1[/TD]
[TD="align: left"]0.993710891300281[/TD]
[TD="align: left"]0.21[/TD]
[TD="align: left"]=B15*D15[/TD]
[TD="align: left"]=E15+1[/TD]
[/TR]
[TR]
[TD="align: left"]1/23/2019[/TD]
[TD="align: left"]=C16-1[/TD]
[TD="align: left"]0.999163207012594[/TD]
[TD="align: left"]0.21[/TD]
[TD="align: left"]=B16*D16[/TD]
[TD="align: left"]=E16+1[/TD]
[/TR]
[TR]
[TD="align: left"]1/24/2019[/TD]
[TD="align: left"]=C17-1[/TD]
[TD="align: left"]1.00321525396108[/TD]
[TD="align: left"]0.21[/TD]
[TD="align: left"]=B17*D17[/TD]
[TD="align: left"]=E17+1[/TD]
[/TR]
[TR]
[TD="align: left"]1/25/2019[/TD]
[TD="align: left"]=C18-1[/TD]
[TD="align: left"]1.01085391015344[/TD]
[TD="align: left"]0.21[/TD]
[TD="align: left"]=B18*D18[/TD]
[TD="align: left"]=E18+1[/TD]
[/TR]
[TR]
[TD="align: left"]1/28/2019[/TD]
[TD="align: left"]=C19-1[/TD]
[TD="align: left"]0.995862552562656[/TD]
[TD="align: left"]0.21[/TD]
[TD="align: left"]=B19*D19[/TD]
[TD="align: left"]=E19+1[/TD]
[/TR]
[TR]
[TD="align: left"]1/29/2019[/TD]
[TD="align: left"]=C20-1[/TD]
[TD="align: left"]1.00280276407364[/TD]
[TD="align: left"]0.21[/TD]
[TD="align: left"]=B20*D20[/TD]
[TD="align: left"]=E20+1[/TD]
[/TR]
[TR]
[TD="align: left"]1/30/2019[/TD]
[TD="align: left"]=C21-1[/TD]
[TD="align: left"]1.00388657171644[/TD]
[TD="align: left"]0.21[/TD]
[TD="align: left"]=B21*D21[/TD]
[TD="align: left"]=E21+1[/TD]
[/TR]
[TR]
[TD="align: left"]1/31/2019[/TD]
[TD="align: left"]=C22-1[/TD]
[TD="align: left"]1.00990968263697[/TD]
[TD="align: left"]0.21[/TD]
[TD="align: left"]=B22*D22[/TD]
[TD="align: left"]=E22+1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Index Return[/TD]
[TD="align: left"]{=PRODUCT(C2:C22)-1}[/TD]
[TD][/TD]
[TD]Pofo daily bmrk 1-mo return[/TD]
[TD="align: left"]{=PRODUCT(F2:F22)-1}[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Index*1Mo Return[/TD]
[TD="align: left"]=C24*D22[/TD]
[TD][/TD]
[TD]Variance[/TD]
[TD="align: left"]=C25-F24[/TD]
[/TR]
</tbody>[/TABLE]

 

[StephenCrump]

Your formula for the 21% allocation is not correct. Let's use a simple example to illustrate ...

If you can invest for two years at 5% p.a. you'll have a cumulative return of 1.05^2 - 1 = 10.25%. This return will apply regardless of the amount invested.

If you have $100,000 and allocate 21% ($21,000) on this basis, you'll earn 10.25% on $21,000 = $2,152.50. This is equivalent to 21% x 10.25% = 2.1525% on the total $100,000 amount.

You're incorrectly adjusting your investment returns and saying you will earn 21% of 5% = 1.05% p.a., so you'll earn $100,000 x (1.0105^2 - 1) = $2,111.03.

By the way, are you sure you're interpreting your index values correctly? Has this index really returned 7.5% for the month?

 

[juneauscott]

Thanks, that solved it for me. Yes, that was a big month and is what the index returned.