Cost of Capital calc

[Sartaj Hussain]

Using appended example dataset, cost of capital is needed to be estimated following the scheme:

Step 1:
For each company i and year end t, calculate beta for each company i at year end t from the following regression using previous 36 months of data.
reg rt_raw - rf on mkt_raw – rf
Step 2:
Plug the beta so obtained into the following equation to obtain cost of capital (cc) for each company i at the year end t:
Cc_i,t = rf + beta_i,t * (mkt_raw - rf)
Where rf and mkt_raw in above equation are the average of rf and mkt_raw for each company i at the year end t over the previous 36 months.

* Example generated by -dataex-. For more info, type help dataex
clear
input float com_id str47 company_name int date double pr float(mdate rf index rt_raw mkt_raw)
1 "Ujjivan Small Finance Bank Ltd." 19084     . 627 .0020523905 4178.35          .            .
1 "Ujjivan Small Finance Bank Ltd." 19114     . 628 .0020523905 3913.05          .  -.063493975
1 "Ujjivan Small Finance Bank Ltd." 19145     . 629 .0020434163 4170.65          .    .06583096
1 "Ujjivan Small Finance Bank Ltd." 19175     . 630  .002025255 4126.45          .  -.010597798
1 "Ujjivan Small Finance Bank Ltd." 19206     . 631 .0020343787  4129.9          .  .0008359987
1 "Ujjivan Small Finance Bank Ltd." 19237     . 632  .002025255 4504.35          .     .0906681
1 "Ujjivan Small Finance Bank Ltd." 19267     . 633  .002025255 4448.85          .  -.012321423
1 "Ujjivan Small Finance Bank Ltd." 19298     . 634 .0020298264 4675.25          .    .05088953
1 "Ujjivan Small Finance Bank Ltd." 19328     . 635 .0020298264 4743.45          .   .014587497
1 "Ujjivan Small Finance Bank Ltd." 19359     . 636 .0020021512  4795.3          .    .01093078
1 "Ujjivan Small Finance Bank Ltd." 19390     . 637 .0020206752  4477.5          .   -.06627319
1 "Ujjivan Small Finance Bank Ltd." 19418     . 638 .0020298264 4438.35          .  -.008743697
1 "Ujjivan Small Finance Bank Ltd." 19449     . 639 .0019593341 4641.75          .    .04582782
1 "Ujjivan Small Finance Bank Ltd." 19479     . 640  .001929867 4681.45          .   .008552851
1 "Ujjivan Small Finance Bank Ltd." 19510     . 641 .0019495924  4510.9          .   -.03643108
1 "Ujjivan Small Finance Bank Ltd." 19540     . 642 .0023225213 4379.65          .   -.02909619
1 "Ujjivan Small Finance Bank Ltd." 19571     . 643  .002384807 4175.85          .   -.04653335
1 "Ujjivan Small Finance Bank Ltd." 19602     . 644 .0021832753 4392.05          .    .05177382
1 "Ujjivan Small Finance Bank Ltd." 19632     . 645 .0020745308 4804.85          .    .09398807
1 "Ujjivan Small Finance Bank Ltd." 19663     . 646 .0021090803  4770.1          .  -.007232276
1 "Ujjivan Small Finance Bank Ltd." 19693     . 647 .0020832652 4914.85          .   .030345276
1 "Ujjivan Small Finance Bank Ltd." 19724     . 648 .0021048181 4709.15          .   -.04185279
1 "Ujjivan Small Finance Bank Ltd." 19755     . 649 .0021301683  4849.5          .     .0298037
1 "Ujjivan Small Finance Bank Ltd." 19783     . 650 .0021005392 5224.85          .    .07739975
1 "Ujjivan Small Finance Bank Ltd." 19814     . 651 .0021005392 5255.65          .   .005894869
1 "Ujjivan Small Finance Bank Ltd." 19844     . 652 .0020789015 5802.85          .    .10411656
1 "Ujjivan Small Finance Bank Ltd." 19875     . 653  .002070132  6174.2          .    .06399443
1 "Ujjivan Small Finance Bank Ltd." 19905     . 654 .0020789015 6194.45          .   .003279777
1 "Ujjivan Small Finance Bank Ltd." 19936     . 655 .0020745308 6360.75          .    .02684658
1 "Ujjivan Small Finance Bank Ltd." 19967     . 656  .002065726  6415.7          .   .008638949
1 "Ujjivan Small Finance Bank Ltd." 19997     . 657 .0020568501 6685.75          .    .04209203
1 "Ujjivan Small Finance Bank Ltd." 20028     . 658 .0020389014 6918.05          .    .03474551
1 "Ujjivan Small Finance Bank Ltd." 20058     . 659 .0020434163 6773.65          .   -.02087292
1 "Ujjivan Small Finance Bank Ltd." 20089     . 660 .0020343787  7166.7          .    .05802637
1 "Ujjivan Small Finance Bank Ltd." 20120     . 661 .0020523905 7239.45          .   .010151115
1 "Ujjivan Small Finance Bank Ltd." 20148     . 662 .0020389014 6978.15          .  -.036093943
1 "Ujjivan Small Finance Bank Ltd." 20179     . 663 .0020021512 6749.65          .   -.03274507
1 "Ujjivan Small Finance Bank Ltd." 20209     . 664 .0019927756 6959.85          .   .031142386
1 "Ujjivan Small Finance Bank Ltd." 20240     . 665  .001973779  6897.2          .  -.009001616
1 "Ujjivan Small Finance Bank Ltd." 20270     . 666 .0019495924  7106.2          .    .03030215
1 "Ujjivan Small Finance Bank Ltd." 20301     . 667 .0019446946 6669.35          .    -.0614745
1 "Ujjivan Small Finance Bank Ltd." 20332     . 668  .001899626  6646.1          .  -.003486097
1 "Ujjivan Small Finance Bank Ltd." 20362     . 669  .001904719 6750.95          .   .015776183
1 "Ujjivan Small Finance Bank Ltd." 20393     . 670 .0019098005  6686.1          .   -.00960607
1 "Ujjivan Small Finance Bank Ltd." 20423     . 671   .00191988 6724.75          .   .005780635
1 "Ujjivan Small Finance Bank Ltd." 20454     . 672  .001929867 6339.45          .   -.05729578
1 "Ujjivan Small Finance Bank Ltd." 20485     . 673  .001934832 5829.95          .   -.08036975
1 "Ujjivan Small Finance Bank Ltd." 20514     . 674  .001924879 6452.15          .     .1067247
1 "Ujjivan Small Finance Bank Ltd." 20545     . 675 .0018685405 6588.55          .   .021140225
1 "Ujjivan Small Finance Bank Ltd." 20575     . 676 .0018737818    6804          .   .032700703
1 "Ujjivan Small Finance Bank Ltd." 20606     . 677 .0018579937  6980.8          .   .025984686
1 "Ujjivan Small Finance Bank Ltd." 20636     . 678  .001836585    7330          .    .05002295
1 "Ujjivan Small Finance Bank Ltd." 20667     . 679  .001836585 7490.65          .    .02191677
1 "Ujjivan Small Finance Bank Ltd." 20698     . 680 .0018311593 7394.85          .   -.01278925
1 "Ujjivan Small Finance Bank Ltd." 20728     . 681  .001809231 7501.45          .    .01441545
1 "Ujjivan Small Finance Bank Ltd." 20759     . 682 .0017524358  7079.4          .   -.05626249
1 "Ujjivan Small Finance Bank Ltd." 20789     . 683 .0017980967  6982.8          .  -.013645238
1 "Ujjivan Small Finance Bank Ltd." 20820     . 684 .0017924996  7379.3          .    .05678238
1 "Ujjivan Small Finance Bank Ltd." 20851     . 685  .001781203  7709.1          .    .04469263
1 "Ujjivan Small Finance Bank Ltd." 20879     . 686 .0017348136 7995.05          .    .03709249
1 "Ujjivan Small Finance Bank Ltd." 20910     . 687 .0017868595  8214.3          .    .02742322
1 "Ujjivan Small Finance Bank Ltd." 20940     . 688 .0018036785 8350.95          .   .016635671
1 "Ujjivan Small Finance Bank Ltd." 20971     . 689 .0017980967  8331.6          . -.0023171718
1 "Ujjivan Small Finance Bank Ltd." 21001     . 690  .001781203  8793.3          .    .05541555
1 "Ujjivan Small Finance Bank Ltd." 21032     . 691 .0017755028 8694.95          .  -.011184608
1 "Ujjivan Small Finance Bank Ltd." 21063     . 692 .0017755028    8600          .  -.010920154
1 "Ujjivan Small Finance Bank Ltd." 21093     . 693 .0017755028  9153.6          .    .06437205
1 "Ujjivan Small Finance Bank Ltd." 21124     . 694  .001781203  9154.5          . .00009836465
1 "Ujjivan Small Finance Bank Ltd." 21154     . 695 .0017868595 9490.65          .   .036719687
1 "Ujjivan Small Finance Bank Ltd." 21185     . 696  .001814755  9697.9          .    .02183728
1 "Ujjivan Small Finance Bank Ltd." 21216     . 697  .001809231  9261.9          .   -.04495819
1 "Ujjivan Small Finance Bank Ltd." 21244     . 698 .0017755028  8912.1          .   -.03776771
1 "Ujjivan Small Finance Bank Ltd." 21275     . 699 .0017868595  9496.5          .    .06557382
1 "Ujjivan Small Finance Bank Ltd." 21305     . 700  .001814755 9315.35          .   -.01907549
1 "Ujjivan Small Finance Bank Ltd." 21336     . 701 .0018311593 9162.45          .  -.016413707
1 "Ujjivan Small Finance Bank Ltd." 21366     . 702 .0018526748  9650.6          .    .05327717
1 "Ujjivan Small Finance Bank Ltd." 21397     . 703 .0018685405    9992          .    .03537608
1 "Ujjivan Small Finance Bank Ltd." 21428     . 704  .001914846    9116          .   -.08767013
1 "Ujjivan Small Finance Bank Ltd." 21458     . 705  .001884189 8753.45          .   -.03977071
1 "Ujjivan Small Finance Bank Ltd." 21489     . 706 .0018632737 9109.15          .    .04063543
1 "Ujjivan Small Finance Bank Ltd." 21519     . 707 .0018473298    9170          .   .006680053
1 "Ujjivan Small Finance Bank Ltd." 21550     . 708  .001836585 9003.85          .  -.018118909
1 "Ujjivan Small Finance Bank Ltd." 21581     . 709  .001814755 8955.95          .  -.005319882
1 "Ujjivan Small Finance Bank Ltd." 21609     . 710 .0018036785  9663.7          .    .07902567
1 "Ujjivan Small Finance Bank Ltd." 21640     . 711  .001820251  9664.3          .  .0000620476
1 "Ujjivan Small Finance Bank Ltd." 21670     . 712 .0017868595 9805.05          .    .01456391
1 "Ujjivan Small Finance Bank Ltd." 21701     . 713 .0017640246 9657.95          .  -.015002433
1 "Ujjivan Small Finance Bank Ltd." 21731     . 714 .0017108843 9044.95          .   -.06347103
1 "Ujjivan Small Finance Bank Ltd." 21762     . 715 .0016739655 8977.55          .  -.007451715
1 "Ujjivan Small Finance Bank Ltd." 21793     . 716 .0016739655  9340.9          .    .04047325
1 "Ujjivan Small Finance Bank Ltd." 21823     . 717  .001616172 9689.65          .     .0373358
1 "Ujjivan Small Finance Bank Ltd." 21854     . 718 .0016086884 9813.65          .    .01279716
1 "Ujjivan Small Finance Bank Ltd." 21884 53.85 719  .001614519 9872.55          .   .006001784
1 "Ujjivan Small Finance Bank Ltd." 21915  55.8 720 .0016305868 9861.45   .0362117   -.00112429
1 "Ujjivan Small Finance Bank Ltd." 21946  48.4 721 .0016230776 9236.05 -.13261649    -.0634187
1 "Ujjivan Small Finance Bank Ltd." 21975  27.3 722 .0015016556 6996.75  -.4359504    -.2424521
1 "Ujjivan Small Finance Bank Ltd." 22006 29.35 723 .0013648635  8012.9  .07509158     .1452317
1 "Ujjivan Small Finance Bank Ltd." 22036 26.85 724  .001268986  7822.4 -.08517887  -.023774164
1 "Ujjivan Small Finance Bank Ltd." 22067 29.85 725  .001269165  8474.8  .11173184     .0834015
1 "Ujjivan Small Finance Bank Ltd." 22097  34.4 726 .0012920127 9035.75   .1524288    .06619038
end
format %td date
format %tm mdate

[Clyde Schechter]

I believe the following is what you seek:

foreach x in rt mkt {
    gen `x'_red = `x'_raw - rf
}
rangestat (reg) rt_red mkt_red, by(com_id) interval(date . .)
rangestat (mean) rf_mean = rf mkt_red_mean = mkt_red, by(com_id) ///
    interval(mdate -36 -1)
gen capital_cost = rf_mean + b_mkt_red*(mkt_red_mean)

Note: I am using the suffix _red on a variable to denote the original variable minus rf.

-rangestat- is written by Robert Picard, Nick Cox, and Roberto Ferrer. It is available from SSC.

[Sartaj Hussain]

The cost of capital number is needed for each year t, (i.e. March end) using previous 36 months of data. I think #2 needs a bit change. The code #2 generates for each month. Further, please clarify why is reg_nobs > 36. As a restriction, each regression should be done using previous 36 months of data.

[Clyde Schechter]

Ah, I see. I missed where in step 1 you said to calculate the regression at the end of the year. I had a rolling regression every month.

However, now that I see that, I can no longer test the code because the variable rt_raw is missing in nearly all of the provided example data. So I think the following code is correct now, but if it isn't, please post back with a new example that has non-missing values of rt_raw.

foreach x in rt mkt {
    gen `x'_red = `x'_raw - rf
}


rangestat (reg) rt_red mkt_red, by(com_id) interval(date -36 -1)
gen year = year(date)
by com_id year (mdate), sort: replace b_mkt_red = b_mkt_red[_N]
rangestat (mean) rf_mean = rf mkt_red_mean = mkt_red, by(com_id) ///
    interval(mdate -36 -1)
gen capital_cost = rf_mean + b_mkt_red*(mkt_red_mean)

[Sartaj Hussain]

This error is faced while running #4

rangestat (reg) rt_red mkt_red, by(com_id) interval(date -36 -1)
no result for all obs: reg rt_red mkt_red

[Clyde Schechter]

This error is faced while running #4

rangestat (reg) rt_red mkt_red, by(com_id) interval(date -36 -1)
no result for all obs: reg rt_red mkt_red

Yes, that's because the regression can'[t be carried out. And the reason they can't be carried out is that the variable rt_red is mostly missing values. And that's because rt_raw is almost always missing. This is what I pointed out in #4: the problem is with the data.

[Sartaj Hussain]

Can't the code be set to do it for cases where there is data and leave rest cases blank which have missing data.

[Sartaj Hussain]

For example, we put a condition, missing if no_obs is <24

[Sartaj Hussain]

When we used #2 it gave no error, although #2 is not the correct code in this case

[Clyde Schechter]

Ah, wait. There was a mistake in the code in #4--my apologies. Here's a different one that should do the trick.

foreach x in rt mkt {
    gen `x'_red = `x'_raw - rf
}


rangestat (reg) rt_red mkt_red, by(com_id) interval(mdate -36 -1)
rangestat (mean) rf_mean = rf mkt_red_mean = mkt_red, by(com_id) ///
    interval(mdate -36 -1)
gen year = year(date)
foreach v of varlist b_mkt_red rf_mean mkt_red_mean {
    by com_id year (mdate), sort: replace `v' = `v'[_N]
}
gen capital_cost = rf_mean + b_mkt_red*(mkt_red_mean)

I have not put in a restriction on the minimum number of observations. If you want to limit it to 24 or more observations, then you can do this at the end:

by com_id year (mdate): replace capital_cost = . if reg_nobs[_N] < 24