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:

	Index Return	Index Factor	Allocation Target	Portfolio Benchmark Return	PBR Factor
1/2/2019	-0.69%	0.9931	21.00%	(0.0014)	0.9986
1/3/2019	-0.13%	0.9987	21.00%	(0.0003)	0.9997
1/4/2019	1.64%	1.0164	21.00%	0.0034	1.0034
1/7/2019	1.04%	1.0104	21.00%	0.0022	1.0022
1/8/2019	0.37%	1.0037	21.00%	0.0008	1.0008
1/9/2019	1.51%	1.0151	21.00%	0.0032	1.0032
1/10/2019	0.25%	1.0025	21.00%	0.0005	1.0005
1/11/2019	0.03%	1.0003	21.00%	0.0001	1.0001
1/14/2019	-0.46%	0.9954	21.00%	(0.0010)	0.9990
1/15/2019	0.59%	1.0059	21.00%	0.0012	1.0012
1/16/2019	0.10%	1.0010	21.00%	0.0002	1.0002
1/17/2019	-0.03%	0.9997	21.00%	(0.0001)	0.9999
1/18/2019	1.16%	1.0116	21.00%	0.0024	1.0024
1/22/2019	-0.63%	0.9937	21.00%	(0.0013)	0.9987
1/23/2019	-0.08%	0.9992	21.00%	(0.0002)	0.9998
1/24/2019	0.32%	1.0032	21.00%	0.0007	1.0007
1/25/2019	1.09%	1.0109	21.00%	0.0023	1.0023
1/28/2019	-0.41%	0.9959	21.00%	(0.0009)	0.9991
1/29/2019	0.28%	1.0028	21.00%	0.0006	1.0006
1/30/2019	0.39%	1.0039	21.00%	0.0008	1.0008
1/31/2019	0.99%	1.0099	21.00%	0.0021	1.0021
	Index Return	7.548%		Pofo daily bmrk 1-mo return	1.550%
	Index*1Mo Return	1.585%		Variance	0.035%

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

Formulas:

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

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

 

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