Computing benchmark return daily

juneauscott

New Member
Joined
Aug 26, 2019
Messages
2
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 ReturnIndex FactorAllocation TargetPortfolio Benchmark ReturnPBR 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/20191.64% 1.0164 21.00% 0.0034 1.0034
1/7/20191.04% 1.0104 21.00% 0.0022 1.0022
1/8/20190.37% 1.0037 21.00% 0.0008 1.0008
1/9/20191.51% 1.0151 21.00% 0.0032 1.0032
1/10/20190.25% 1.0025 21.00% 0.0005 1.0005
1/11/20190.03% 1.0003 21.00% 0.0001 1.0001
1/14/2019-0.46% 0.9954 21.00% (0.0010) 0.9990
1/15/20190.59% 1.0059 21.00% 0.0012 1.0012
1/16/20190.10% 1.0010 21.00% 0.0002 1.0002
1/17/2019-0.03% 0.9997 21.00% (0.0001) 0.9999
1/18/20191.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/20190.32% 1.0032 21.00% 0.0007 1.0007
1/25/20191.09% 1.0109 21.00% 0.0023 1.0023
1/28/2019-0.41% 0.9959 21.00% (0.0009) 0.9991
1/29/20190.28% 1.0028 21.00% 0.0006 1.0006
1/30/20190.39% 1.0039 21.00% 0.0008 1.0008
1/31/20190.99% 1.0099 21.00% 0.0021 1.0021
Index Return7.548%Pofo daily bmrk 1-mo return1.550%
Index*1Mo Return1.585%Variance0.035%

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

Formulas:
Index ReturnIndex FactorAllocation TargetPortfolio Benchmark ReturnPBR Factor
1/2/2019=C2-10.993108117020410.21=B2*D2=E2+1
1/3/2019=C3-10.9987377461272980.21=B3*D3=E3+1
1/4/2019=C4-11.016358657415710.21=B4*D4=E4+1
1/7/2019=C5-11.010437762591650.21=B5*D5=E5+1
1/8/2019=C6-11.003693413365220.21=B6*D6=E6+1
1/9/2019=C7-11.015061752521280.21=B7*D7=E7+1
1/10/2019=C8-11.002503728958020.21=B8*D8=E8+1
1/11/2019=C9-11.000279206438440.21=B9*D9=E9+1
1/14/2019=C10-10.995435153881820.21=B10*D10=E10+1
1/15/2019=C11-11.005892324062380.21=B11*D11=E11+1
1/16/2019=C12-11.001049444422730.21=B12*D12=E12+1
1/17/2019=C13-10.9997429115890160.21=B13*D13=E13+1
1/18/2019=C14-11.011648216333450.21=B14*D14=E14+1
1/22/2019=C15-10.9937108913002810.21=B15*D15=E15+1
1/23/2019=C16-10.9991632070125940.21=B16*D16=E16+1
1/24/2019=C17-11.003215253961080.21=B17*D17=E17+1
1/25/2019=C18-11.010853910153440.21=B18*D18=E18+1
1/28/2019=C19-10.9958625525626560.21=B19*D19=E19+1
1/29/2019=C20-11.002802764073640.21=B20*D20=E20+1
1/30/2019=C21-11.003886571716440.21=B21*D21=E21+1
1/31/2019=C22-11.009909682636970.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*D22Variance=C25-F24

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

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

StephenCrump

Well-known Member
Joined
Sep 18, 2013
Messages
3,800
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 26, 2019
Messages
2
Thanks, that solved it for me. Yes, that was a big month and is what the index returned.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,973
Messages
5,525,988
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top