Spreadsheet: 108587d1305626671-excel-2007-calculating-covariance-for-visible-cells-only-beta
Sheet: Arvind Pipes & Fittings

 |  | BF utilities |  |  | CESC |  |  | Gujarat Ind. Power |  |  | NLCL |  |  | Reliance Infra |  |  | Tata Power |  |  | BSE 500
 | Date | Close | returns |  | Close | returns |  | Close | returns |  | Close | returns |  | Close | returns |  | Close | returns |  | Close | returns
 | 2010-03-01 00:00:00 | 964.2 | -0.1320550904671888 |  | 383.6 | -0.05656665027053615 |  | 103.05 | -0.0703653585926928 |  | 156.1 | 0.004504504504504432 |  | 1003.95 | -0.03725546605293432 |  | 1212.95 | -0.06187400904907382 |  | 6518.38 | -0.006535330104278768
 | 2010-02-01 00:00:00 | 1110.9 | -0.1324482623975009 |  | 406.6 | 0.05186909843487262 |  | 110.85 | -0.05979643765903317 |  | 155.4 | 0.01568627450980396 |  | 1042.8 | -0.0907664138111432 |  | 1292.95 | -0.0616858376573896 |  | 6561.26 | -0.005869696969696936
 | 2010-01-01 00:00:00 | 1280.5 | 0.03095688579364764 |  | 386.55 | -0.01239141543178326 |  | 117.9 | 0.002551020408163362 |  | 153 | 0.009567799406136513 |  | 1146.9 | 0.06120749479528114 |  | 1377.95 | 0.01357116586980511 |  | 6600 | -0.01548375548196548
 | 2009-12-01 00:00:00 | 1242.05 | 0.5830359418812132 |  | 391.4 | 0.05484436059830202 |  | 117.6 | 0.08387096774193543 |  | 151.55 | 0.1711746522411129 |  | 1080.75 | 0.02111678004535139 |  | 1359.5 | 0.01213519952352587 |  | 6703.8 | 0.09139216889732563
 | 2009-11-02 00:00:00 | 784.6 | -0.1215853112404837 |  | 371.05 | -0.02355263157894734 |  | 108.5 | -0.02471910112359551 |  | 129.4 | -0.01969696969696965 |  | 1058.4 | -0.1221332890971675 |  | 1343.2 | 0.04071591833572227 |  | 6142.43 | -0.06312411440006462
 | 2009-10-01 00:00:00 | 893.2 | 0.03848389722125337 |  | 380 | 0.04281009879253574 |  | 111.25 | 0.002252252252252252 |  | 132 | 0.01734104046242774 |  | 1205.65 | 0.07273778805943588 |  | 1290.65 | -0.008374630248549701 |  | 6556.29 | 0.09560191103557882
 | 2009-09-01 00:00:00 | 860.1 | 0.1344720701708106 |  | 364.4 | 0.1220939183987682 |  | 111 | 0.2464907355418305 |  | 129.75 | -0.07321428571428572 |  | 1123.9 | -0.05459286675639289 |  | 1301.55 | 0.002580496071483522 |  | 5984.19 | -0.004380652589127202
 | 2009-08-04 00:00:00 | 758.15 | -0.02331723027375204 |  | 324.75 | 0.1604430945149186 |  | 89.05 | 0.1272151898734177 |  | 140 | 0.07650903498654354 |  | 1188.8 | -0.03608205627179113 |  | 1298.2 | 0.1329086307705733 |  | 6010.52 | 0.08290828506310413
 | 2009-07-01 00:00:00 | 776.25 | -0.05628837152756666 |  | 279.85 | -0.2257573661640613 |  | 79 | -0.0650887573964497 |  | 130.05 | -0.094359331476323 |  | 1233.3 | -0.05192758580927852 |  | 1145.9 | 0.05530229773909857 |  | 5550.35 | -0.01438026185643526
 | 2009-06-01 00:00:00 | 822.55 | 0.3958085864585101 |  | 361.45 | 0.5492927561080153 |  | 84.5 | 0.4048212801330008 |  | 143.6 | 0.6207674943566592 |  | 1300.85 | 0.871188147295742 |  | 1085.85 | 0.214665249734325 |  | 5631.33 | 0.360086657875288
 | 2009-05-01 00:00:00 | 589.3 | 0.8642834546029735 |  | 233.3 | 0.07067462138595688 |  | 60.15 | 0.2977346278317151 |  | 88.6 | 0.04666272888363837 |  | 695.2 | 0.2650350286598128 |  | 893.95 | 0.1251022591403941 |  | 4140.42 | 0.1548547791912932
 | 2009-04-01 00:00:00 | 316.1 | -0.09904517600114009 |  | 217.9 | 0.06474468604935255 |  | 46.35 | 0.1062052505966588 |  | 84.65 | 0.2153625269203158 |  | 549.55 | 0.2325894359089378 |  | 794.55 | 0.1164114093016719 |  | 3585.23 | 0.142123991347811
 | 2009-03-02 00:00:00 | 350.85 | -0.2182486631016042 |  | 204.65 | -0.09905348888399736 |  | 41.9 | -0.1178947368421053 |  | 69.65 | -0.103026400515132 |  | 445.85 | -0.1444881512040679 |  | 711.7 | -0.04763816405727273 |  | 3139.09 | -0.05421463499877978
 | 2009-02-02 00:00:00 | 448.8 | -0.2400948188283102 |  | 227.15 | -0.1211065970207003 |  | 47.5 | -0.1121495327102804 |  | 77.65 | 0.08298465829846587 |  | 521.15 | -0.1734337827121332 |  | 747.3 | -0.02992146426948798 |  | 3319.03 | -0.1090761210936871
 | 2009-01-02 00:00:00 | 590.6 | 0.04995555555555559 |  | 258.45 | 0.02417277590647907 |  | 53.5 | 0.06467661691542288 |  | 71.7 | 0.02722063037249292 |  | 630.5 | 0.0295558458523841 |  | 770.35 | -0.0169718624385886 |  | 3725.38 | 0.009109500914200612
 | 2009-01-01 00:00:00 | 562.5 | -0.05826217980914106 |  | 252.35 | 0.1962550367385637 |  | 50.25 | 0.2934362934362934 |  | 69.8 | 0.4663865546218486 |  | 612.4 | 0.3096663815226688 |  | 783.65 | 0.2129866109434255 |  | 3691.75 | 0.1453644038086256
 | 2008-12-01 00:00:00 | 597.3 | -0.1475667189952906 |  | 210.95 | 0.02154963680387404 |  | 38.85 | -0.1654135338345864 |  | 47.6 | -0.2 |  | 467.6 | -0.1261446458605868 |  | 646.05 | -0.0680851063829788 |  | 3223.21 | -0.1423002312418672
 | 2008-11-03 00:00:00 | 700.7 | -0.4632091010073926 |  | 206.5 | -0.2832349878514405 |  | 46.55 | -0.2049530315969257 |  | 59.5 | -0.3160919540229885 |  | 535.1 | -0.3152911068458093 |  | 693.25 | -0.2675647120972002 |  | 3757.97 | -0.2413689382714056
 | 2008-10-01 00:00:00 | 1305.35 | -0.4049279722830051 |  | 288.1 | -0.1505233672416334 |  | 58.55 | -0.2690387016229713 |  | 87 | -0.2375109553023663 |  | 781.5 | -0.2048634074375541 |  | 946.5 | -0.08907174823155763 |  | 4953.62 | -0.1181210299940183
 | 2008-09-01 00:00:00 | 2193.6 | 0.05415925801335952 |  | 339.15 | -0.03100000000000007 |  | 80.1 | 0.01908396946564886 |  | 114.1 | -0.04318658280922437 |  | 982.85 | -0.02745893528596873 |  | 1039.05 | -0.06936856247201079 |  | 5617.12 | -0.004510356096601339
 | 2008-08-01 00:00:00 | 2080.9 | 0.7815924657534248 |  | 350 | -0.07858365144135848 |  | 78.6 | 0.06720977596741329 |  | 119.25 | 0.2149770759042282 |  | 1010.6 | 0.4394986112100279 |  | 1116.5 | 0.1076388888888889 |  | 5642.57 | 0.1278733679270174
 | 2008-07-01 00:00:00 | 1168 | -0.3351548269581056 |  | 379.85 | -0.2078206465067778 |  | 73.65 | -0.2426735218508997 |  | 98.15 | -0.277511961722488 |  | 702.05 | -0.4001623376623378 |  | 1008 | -0.2198142414860681 |  | 5002.84 | -0.2087957273919156
 | 2008-06-02 00:00:00 | 1756.8 | -0.1223679280629449 |  | 479.5 | -0.03743852253337344 |  | 97.25 | -0.04234367306745443 |  | 135.85 | -0.1333333333333334 |  | 1170.4 | -0.2316680890172651 |  | 1292 | -0.09482607629523236 |  | 6323.07 | -0.09542884037922048
 | 2008-05-02 00:00:00 | 2001.75 | 0.8210143279508756 |  | 498.15 | 0.2568436987511037 |  | 101.55 | 0.101409978308026 |  | 156.75 | 0.3426124197002141 |  | 1523.3 | 0.1823650405557496 |  | 1427.35 | 0.2152830991911451 |  | 6990.13 | 0.1367377372021811
 | 2008-04-01 00:00:00 | 1099.25 | -0.03629509490202962 |  | 396.35 | -0.2081710118869244 |  | 92.2 | -0.1451089476124246 |  | 116.75 | -0.1708096590909092 |  | 1288.35 | -0.132745447813941 |  | 1174.5 | -0.1007235557597335 |  | 6149.29 | -0.08926122411500029
 | 2008-03-03 00:00:00 | 1140.65 | -0.1965556103402127 |  | 500.55 | 0.0175848749745884 |  | 107.85 | 0.02082347373402734 |  | 140.8 | 0.003206270039187867 |  | 1485.55 | -0.2590773067331671 |  | 1306.05 | -0.0261352620982775 |  | 6751.98 | -0.0753688880976673
 | 2008-02-01 00:00:00 | 1419.7 | -0.3674478702548565 |  | 491.9 | -0.2248049799070207 |  | 105.65 | -0.2147900408769974 |  | 140.35 | -0.4488513646181033 |  | 2005 | -0.1202281702501097 |  | 1341.1 | -0.1343832698638095 |  | 7302.35 | -0.1568338561975282
 | 2008-01-01 00:00:00 | 2244.4 | 0.08477525374577095 |  | 634.55 | 0.008823529411764633 |  | 134.55 | 0.2226260790549751 |  | 254.65 | 0.05249018392229807 |  | 2279 | 0.1993789963950214 |  | 1549.3 | 0.2268282060418894 |  | 8660.63 | 0.07999486229196633
 | 2007-12-03 00:00:00 | 2069 | -0.1617372984361072 |  | 629 | 0.08889465939582797 |  | 110.05 | 0.02419730107026519 |  | 241.95 | 0.748193641618497 |  | 1900.15 | 0.07486706641022749 |  | 1262.85 | 0.02745911642665365 |  | 8019.14 | 0.03995435122324458
 | 2007-11-01 00:00:00 | 2468.2 | 0.04708976752078738 |  | 577.65 | 0.1367706385909672 |  | 107.45 | 0.2148106274731487 |  | 138.4 | 0.2832637923041262 |  | 1767.8 | 0.3100637320290498 |  | 1229.1 | 0.3494729907773386 |  | 7711.05 | 0.1293840056476158
 | 2007-10-01 00:00:00 | 2357.2 | 0.157959374155675 |  | 508.15 | 0.09209112400601754 |  | 88.45 | 0.3787996882307092 |  | 107.85 | 0.2900717703349283 |  | 1349.4 | 0.73679129931141 |  | 910.8 | 0.3354838709677418 |  | 6827.66 | 0.1352999765545887
 | 2007-09-03 00:00:00 | 2035.65 | -0.1473360140738879 |  | 465.3 | 0.04128902316213492 |  | 64.15 | 0.0994001713796059 |  | 83.6 | 0.1161548731642188 |  | 776.95 | 0.05377729553777308 |  | 682 | -0.02703473856908488 |  | 6013.97 | 0.03406552782482352
 | 2007-08-01 00:00:00 | 2387.4 | 0.2638098515126652 |  | 446.85 | 0.2012096774193549 |  | 58.35 | -0.1316964285714286 |  | 74.9 | 0.1832543443917853 |  | 737.3 | 0.1810027230498158 |  | 700.95 | 0.05883685800604237 |  | 5815.85 | 0.002404384770506339
 | 2007-07-02 00:00:00 | 1889.05 | 0.02545938170073005 |  | 372 | 0.02479338842975207 |  | 67.2 | -0.01610541727672027 |  | 63.3 | -0.03799392097264438 |  | 624.3 | 0.1524829241277458 |  | 662 | 0.1201353637901861 |  | 5801.9 | 0.02299215375121214
 | 2007-06-01 00:00:00 | 1842.15 | 0.02658196104656025 |  | 363 | -0.0717299578059072 |  | 68.3 | 0.07813733228097862 |  | 65.8 | -0.006042296072507639 |  | 541.7 | 0.0571818891491024 |  | 591 | -0.02362464893441261 |  | 5671.5 | 0.05240207084670902
 | 2007-05-03 00:00:00 | 1794.45 | -0.1889858085510259 |  | 391.05 | 0.0696115973741794 |  | 63.35 | 0.1709796672828096 |  | 66.2 | 0.2597526165556614 |  | 512.4 | 0.07477713686418454 |  | 605.3 | 0.2201169119129206 |  | 5389.1 | 0.13614754253886
 | 2007-04-02 00:00:00 | 2212.6 | 0.2706233669279582 |  | 365.6 | 0.04293253458850381 |  | 54.1 | -0.07757885763000848 |  | 52.55 | -0.08209606986899569 |  | 476.75 | -0.007907605868275958 |  | 496.1 | -0.08257050392972719 |  | 4743.31 | -0.05004796513665551
 | 2007-03-01 00:00:00 | 1741.35 | -0.4179979946524064 |  | 350.55 | -0.02353760445682448 |  | 58.65 | -0.08215962441314555 |  | 57.25 | -0.06986190089358241 |  | 480.55 | -0.08379408960915155 |  | 540.75 | -0.08872598584428712 |  | 4993.21 | -0.08673210898761934
 | 2007-02-01 00:00:00 | 2992 |  |  | 359 |  |  | 63.9 |  |  | 61.55 |  |  | 524.5 |  |  | 593.4 |  |  | 5467.41
 |  | BETA |  |  | BETA |  |  | BETA |  |  | BETA |  |  | BETA |  |  | BETA
 |  | BSE 500 | 1.699790799381282 |  | BSE 500 | 0.9894015582146346 |  | BSE 500 | 1.186908872939652 |  | BSE 500 | 1.570571467410716 |  | BSE 500 | 1.834636793619225 |  | BSE 500 | 0.9469115753401034
 |  | BSE 500 | Beta | D/E Ratio | % Tax | Asset Beta
 |  | 1 | 1.699790799381282 | 0.95 | 0.5230769230769231 | 1.16978720973831 | BF utilities
 |  | 2 | 0.9894015582146346 | 0.85 | 0.1186998515714071 | 0.5656615736895931 | CESC
 |  | 3 | 1.186908872939652 | 0.47 | 0.2131328967997786 | 0.8664659160201984 | Gujarat Ind. Power
 |  | 4 | 1.570571467410716 | 0.37 | 0.2150266249844648 | 1.217081992469835 | NLCL
 |  | 5 | 1.834636793619225 | 0.51 | 0.04558862965942612 | 1.233991620519524 | Reliance Infra
 |  | 6 | 0.9469115753401034 | 0.43 | 0.1731150304410591 | 0.6985387591013438 | TATA POWER
 |  |  |  |  | Average | 0.9585878452564675
 |  |  | BSE 500
 |  |  | Start Date |  | 1999-02-01 00:00:00
 |  |  | Base value |  | 1000
 |  |  | End Date |  | 2010-03-01 00:00:00
 |  |  | Close Value |  | 6518.38
 |  |  | Total maturity in Yr. |  | 11.08333333333333
 |  |  | Market Return |  | 0.1842849527232397
 |  |  | Rf |  | 0.075814 |  | http://www.rbi.org.in/scripts/BS_ViewBulletin.aspx?Id=11067
 |  |  | MRP |  | 0.1084709527232397
 |  |  | Beta ( βa ) |  | 0.9585878452564675
 |  |  | ER |  | 0.1797929368438865 | Equity IRR