Description
Excel on Capital Structure Analysis of companies in the Oil and Gas sector.
RIL
Tax Rate Rf 0.35 0.075 Rm Bu 0.15824 0.849103
Ke = Rf + BL*(Rm-Rf) BL = Bu + Bu(1-t)(D/E)
Kd
7.80% 8.00% 8.15% 8.30% 8.40% 8.60% 8.85% 9.10% 9.30% 9.50% 9.70% 10.00% 10.40% 10.90% 11.50% 12.20% 13.00% 15.00% 20.00% 25.00%
Debt Equity (%) (%)
0% 5% 10% 15% 20% 25% 30% 35% 40% 45% 50% 55% 60% 65% 70% 75% 80% 85% 90% 95% 100% 100% 95% 90% 85% 80% 75% 70% 65% 60% 55% 50% 45% 40% 35% 30% 25% 20% 15% 10% 5% 0%
Beta
0.849103 0.878151 0.910427 0.9465 0.987082 1.033075 1.085639 1.146289 1.217047 1.300671 1.40102 1.523668 1.676978 1.874091 2.136909 2.504854 3.05677 3.976632 5.816355 11.33552
Rf
0.075 0.075 0.075 0.075 0.075 0.075 0.075 0.075 0.075 0.075 0.075 0.075 0.075 0.075 0.075 0.075 0.075 0.075 0.075 0.075
Rm
Ke
Kd (aftr tax) WACC
5.07% 5.20% 5.30% 5.40% 5.46% 5.59% 5.75% 5.92% 6.05% 6.18% 6.31% 6.50% 6.76% 7.09% 7.48% 7.93% 8.45% 9.75% 13.00% 16.25% 14.57% 14.33% 14.10% 13.88% 13.67% 13.47% 13.30% 13.15% 13.00% 12.86% 12.73% 12.66% 12.64% 12.69% 12.82% 13.04% 13.35% 14.38% 17.29% 20.53%
PBIAT
16,046.28 16,046.28 16,046.28 16,046.28 16,046.28 16,046.28 16,046.28 16,046.28 16,046.28 16,046.28 16,046.28 16,046.28 16,046.28 16,046.28 16,046.28 16,046.28 16,046.28 16,046.28 16,046.28 16,046.28
0.1582 14.57% 0.1582 14.81% 0.1582 15.08% 0.1582 15.38% 0.1582 15.72% 0.1582 16.10% 0.1582 16.54% 0.1582 17.04% 0.1582 17.63% 0.1582 18.33% 0.1582 19.16% 0.1582 20.18% 0.1582 21.46% 0.1582 23.10% 0.1582 25.29% 0.1582 28.35% 0.1582 32.94% 0.1582 40.60% 0.1582 55.92% 0.1582 101.86%
PAT Int PBIAT
15346 1077.36 16046.28
Current Structure Structure where Firm Value Max.
+ Bu(1-t)(D/E)
Value - Firm Value - Debt Int
110,147.98 111,982.77 113,800.97 115,597.94 117,424.63 119,108.50 120,634.70 122,049.47 123,466.94 124,791.42 126,015.85 126,774.38 126,951.80 126,445.99 125,177.85 123,100.58 120,206.69 111,605.19 92,798.50 78,158.86 5,599.14 11,380.10 17,339.69 23,484.93 29,777.13 36,190.41 42,717.31 49,386.77 56,156.14 63,007.92 69,725.91 76,171.08 82,189.89 87,624.49 92,325.43 96,165.35 94,864.41 83,518.65 74,250.92 447.93 927.48 1,439.19 1,972.73 2,560.83 3,202.85 3,887.28 4,592.97 5,334.83 6,111.77 6,972.59 7,921.79 8,958.70 10,076.82 11,263.70 12,501.50 14,229.66 16,703.73 18,562.73
Int PAT (After Tax) (Rs. Crore)
291.16 602.86 935.48 1,282.28 1,664.54 2,081.85 2,526.73 2,985.43 3,467.64 3,972.65 4,532.18 5,149.16 5,823.15 6,549.93 7,321.41 8,125.97 9,249.28 10,857.42 12,065.77 16,046.28 15,755.13 15,443.42 15,110.81 14,764.01 14,381.74 13,964.43 13,519.55 13,060.85 12,578.64 12,073.63 11,514.10 10,897.12 10,223.13 9,496.35 8,724.88 7,920.31 6,797.00 5,188.86 3,980.51
Value of Firm (Value of Value - Equity Equity +Debt)
110,147.98 106,383.63 102,420.87 98,258.25 93,939.70 89,331.38 84,444.29 79,332.15 74,080.16 68,635.28 63,007.92 57,048.47 50,780.72 44,256.10 37,553.35 30,775.14 24,041.34 16,740.78 9,279.85 3,907.94 110,147.98 111,982.77 113,800.97 115,597.94 117,424.63 119,108.50 120,634.70 122,049.47 123,466.94 124,791.42 126,015.85 126,774.38 126,951.80 126,445.99 125,177.85 123,100.58 120,206.69 111,605.19 92,798.50 78,158.86
No. of Shares Value Per Share
100,000 95,000 90,000 85,000 80,000 75,000 70,000 65,000 60,000 55,000 50,000 45,000 40,000 35,000 30,000 25,000 20,000 15,000 10,000 5,000 1.10 1.12 1.14 1.16 1.17 1.19 1.21 1.22 1.23 1.25 1.26 1.27 1.27 1.26 1.25 1.23 1.20 1.12 0.93 0.78
ONGC
Tax Rate Rf Debt Equity (%) (%) 0% 100% 5% 95% 10% 90% 15% 85% 20% 80% 25% 75% 30% 70% 35% 65% 40% 60% 45% 55% 50% 50% 55% 45% 60% 40% 65% 35% 70% 30% 75% 25% 80% 20% 85% 15% 90% 10% 95% 5% 100% 0% 0.35 0.075 Rm Bu 0.15824 1.02591
BL = Bu + Bu(1-t)(D/E)
Kd (aftr tax) WACC Kd PBIAT 7.80% 5.07% 16.04% 16,721.64 8.00% 5.20% 15.78% 16,721.64 8.15% 5.30% 15.52% 16,721.64 8.30% 5.40% 15.28% 16,721.64 8.40% 5.46% 15.03% 16,721.64 8.60% 5.59% 14.81% 16,721.64 8.85% 5.75% 14.62% 16,721.64 9.10% 5.92% 14.44% 16,721.64 9.30% 6.05% 14.26% 16,721.64 9.50% 6.18% 14.10% 16,721.64 9.70% 6.31% 13.95% 16,721.64 10.00% 6.50% 13.85% 16,721.64 10.40% 6.76% 13.80% 16,721.64 10.80% 7.02% 13.78% 16,721.64 11.50% 7.48% 13.93% 16,721.64 12.20% 7.93% 14.12% 16,721.64 13.00% 8.45% 14.41% 16,721.64 15.00% 9.75% 15.41% 16,721.64 20.00% 13.00% 18.30% 16,721.64 25.00% 16.25% 21.51% 16,721.64
Beta Rf 1.02591 0.075 1.061 0.075 1.1 0.075 1.14358 0.075 1.19262 0.075 1.24819 0.075 1.3117 0.075 1.38497 0.075 1.47047 0.075 1.5715 0.075 1.69275 0.075 1.84093 0.075 2.02617 0.075 2.26432 0.075 2.58187 0.075 3.02642 0.075 3.69326 0.075 4.80466 0.075 7.02746 0.075 13.6959 0.075
Rm Ke 0.1582 16.04% 0.1582 16.33% 0.1582 16.66% 0.1582 17.02% 0.1582 17.43% 0.1582 17.89% 0.1582 18.42% 0.1582 19.03% 0.1582 19.74% 0.1582 20.58% 0.1582 21.59% 0.1582 22.82% 0.1582 24.37% 0.1582 26.35% 0.1582 28.99% 0.1582 32.69% 0.1582 38.24% 0.1582 47.49% 0.1582 66.00% 0.1582 121.50%
PAT Int PBIAT
16701 58.98 16721.6
Current Structure Structure where Firm Value Max.
+ Bu(1-t)(D/E)
Value - Firm Value - Debt
104,251.93 105,999.54 107,739.00 109,466.60 111,226.45 112,870.22 114,385.02 115,810.55 117,245.33 118,606.65 119,888.09 120,770.81 121,150.94 121,304.25 120,041.08 118,421.12 116,053.64 108,500.35 91,376.80 77,729.12 5,299.98 10,773.90 16,419.99 22,245.29 28,217.56 34,315.51 40,533.69 46,898.13 53,372.99 59,944.05 66,423.94 72,690.57 78,847.76 84,028.75 88,815.84 92,842.91 92,225.30 82,239.12 73,842.66 -
Int
424.00 878.07 1,362.86 1,868.60 2,426.71 3,036.92 3,688.57 4,361.53 5,070.43 5,814.57 6,642.39 7,559.82 8,515.56 9,663.31 10,835.53 12,069.58 13,833.79 16,447.82 18,460.66
Int (After Tax) PAT
275.60 570.75 885.86 1,214.59 1,577.36 1,974.00 2,397.57 2,834.99 3,295.78 3,779.47 4,317.56 4,913.88 5,535.11 6,281.15 7,043.10 7,845.23 8,991.97 10,691.09 11,999.43 16,721.64 16,446.04 16,150.90 15,835.78 15,507.05 15,144.28 14,747.64 14,324.08 13,886.65 13,425.86 12,942.17 12,404.09 11,807.76 11,186.53 10,440.49 9,678.55 8,876.42 7,729.68 6,030.56 4,722.21
Value - Equity
104,251.93 100,699.56 96,965.10 93,046.61 88,981.16 84,652.67 80,069.52 75,276.86 70,347.20 65,233.65 59,944.05 54,346.86 48,460.38 42,456.49 36,012.32 29,605.28 23,210.73 16,275.05 9,137.68 3,886.46
ure where Firm Value Max.
Value of Firm (Value of Equity +Debt)
104,251.93 105,999.54 107,739.00 109,466.60 111,226.45 112,870.22 114,385.02 115,810.55 117,245.33 118,606.65 119,888.09 120,770.81 121,150.94 121,304.25 120,041.08 118,421.12 116,053.64 108,500.35 91,376.80 77,729.12
No. of Shares
100,000 95,000 90,000 85,000 80,000 75,000 70,000 65,000 60,000 55,000 50,000 45,000 40,000 35,000 30,000 25,000 20,000 15,000 10,000 5,000
Value Per Share
1.04 1.06 1.08 1.09 1.11 1.13 1.14 1.16 1.17 1.19 1.20 1.21 1.21 1.21 1.20 1.18 1.16 1.09 0.91 0.78
ABAN Offshore Ltd.
Tax Rate Rf Debt Equity (%) (%) 0% 100% 5% 95% 10% 90% 15% 85% 20% 80% 25% 75% 30% 70% 35% 65% 40% 60% 45% 55% 50% 50% 55% 45% 60% 40% 65% 35% 70% 30% 75% 25% 80% 20% 85% 15% 90% 10% 95% 5% 100% 0% 0.35 0.075 Rm Bu 0.15824 0.73924
BL = Bu + Bu(1-t)(D/E)
Kd 7.80% 8.00% 8.15% 8.30% 8.40% 8.60% 8.85% 9.10% 9.30% 9.50% 9.70% 10.00% 10.40% 10.80% 11.50% 12.20% 13.00% 15.00% 20.00% 25.00% Kd (aftr tax) WACC PBIAT 5.07% 13.65% 182.93 5.20% 13.43% 182.93 5.30% 13.22% 182.93 5.40% 13.01% 182.93 5.46% 12.81% 182.93 5.59% 12.64% 182.93 5.75% 12.48% 182.93 5.92% 12.34% 182.93 6.05% 12.21% 182.93 6.18% 12.09% 182.93 6.31% 11.98% 182.93 6.50% 11.92% 182.93 6.76% 11.92% 182.93 7.02% 11.94% 182.93 7.48% 12.13% 182.93 7.93% 12.36% 182.93 8.45% 12.69% 182.93 9.75% 13.74% 182.93 13.00% 16.67% 182.93 16.25% 19.92% 182.93
Beta Rf 0.739 0.075 0.765 0.075 0.793 0.075 0.824 0.075 0.859 0.075 0.899 0.075 0.945 0.075 0.998 0.075 1.06 0.075 1.132 0.075 1.22 0.075 1.327 0.075 1.46 0.075 1.632 0.075 1.86 0.075 2.181 0.075 2.661 0.075 3.462 0.075 5.064 0.075 9.869 0.075
Rm Ke 0.1582 13.65% 0.1582 13.86% 0.1582 14.10% 0.1582 14.36% 0.1582 14.65% 0.1582 14.99% 0.1582 15.37% 0.1582 15.81% 0.1582 16.32% 0.1582 16.93% 0.1582 17.65% 0.1582 18.54% 0.1582 19.65% 0.1582 21.08% 0.1582 22.99% 0.1582 25.65% 0.1582 29.65% 0.1582 36.32% 0.1582 49.65% 0.1582 89.65%
31355 69935 Net worth 101290 93796 51835 Debt 145631 PAT Int PBIAT 158 72.66 204.96
PAT Int PBIAT
157.73 72 182.93
Current Structure Structure where Firm Value Max.
+ Bu(1-t)(D/E)
Value - Firm Value - Debt
1,339.81 1,362.02 1,383.97 1,405.57 1,427.50 1,447.52 1,465.42 1,481.83 1,498.20 1,513.32 1,527.08 1,534.79 1,535.01 1,531.88 1,508.28 1,479.94 1,441.47 1,331.82 1,097.68 918.33 68.10 138.40 210.84 285.50 361.88 439.63 518.64 599.28 680.99 763.54 844.13 921.00 995.72 1,055.80 1,109.95 1,153.18 1,132.05 987.91 872.41 -
Int
5.45 11.28 17.50 23.98 31.12 38.91 47.20 55.73 64.69 74.06 84.41 95.78 107.54 121.42 135.41 149.91 169.81 197.58 218.10
Int (After Tax) PAT
3.54 7.33 11.37 15.59 20.23 25.29 30.68 36.23 42.05 48.14 54.87 62.26 69.90 78.92 88.02 97.44 110.37 128.43 141.77 182.93 179.39 175.60 171.56 167.34 162.70 157.64 152.25 146.70 140.88 134.79 128.06 120.67 113.03 104.01 94.91 85.49 72.56 54.50 41.16
Value - Equity
1,339.81 1,293.92 1,245.57 1,194.74 1,142.00 1,085.64 1,025.80 963.19 898.92 832.32 763.54 690.66 614.00 536.16 452.49 369.98 288.29 199.77 109.77 45.92
Value of Firm (Value of Equity +Debt)
1,339.81 1,362.02 1,383.97 1,405.57 1,427.50 1,447.52 1,465.42 1,481.83 1,498.20 1,513.32 1,527.08 1,534.79 1,535.01 1,531.88 1,508.28 1,479.94 1,441.47 1,331.82 1,097.68 918.33
No. of Shares
100,000 95,000 90,000 85,000 80,000 75,000 70,000 65,000 60,000 55,000 50,000 45,000 40,000 35,000 30,000 25,000 20,000 15,000 10,000 5,000
Value Per Share
0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.02 0.02 0.02 0.02 0.02 0.02 0.01 0.01 0.01 0.01 0.01
BPCL
Tax Rate Rf Debt Equity (%) (%) 0% 100% 5% 95% 10% 90% 15% 85% 20% 80% 25% 75% 30% 70% 35% 65% 40% 60% 45% 55% 50% 50% 55% 45% 60% 40% 65% 35% 70% 30% 75% 25% 80% 20% 85% 15% 90% 10% 95% 5% 100% 0% 0.35 0.075 Rm Bu 0.1582 0.4861
BL = Bu + Bu(1-t)(D/E)
Kd 7.80% 8.00% 8.15% 8.30% 8.40% 8.60% 8.85% 9.10% 9.30% 9.50% 9.70% 10.00% 10.40% 10.80% 11.50% 12.20% 13.00% 15.00% 20.00% 25.00% Kd (aftr tax) WACC PBIAT 5.07% 11.55% 1,787.03 5.20% 11.36% 1,787.03 5.30% 11.18% 1,787.03 5.40% 11.02% 1,787.03 5.46% 10.85% 1,787.03 5.59% 10.71% 1,787.03 5.75% 10.60% 1,787.03 5.92% 10.50% 1,787.03 6.05% 10.40% 1,787.03 6.18% 10.31% 1,787.03 6.31% 10.24% 1,787.03 6.50% 10.22% 1,787.03 6.76% 10.25% 1,787.03 7.02% 10.31% 1,787.03 7.48% 10.54% 1,787.03 7.93% 10.81% 1,787.03 8.45% 11.17% 1,787.03 9.75% 12.25% 1,787.03 13.00% 15.22% 1,787.03 16.25% 18.51% 1,787.03
Beta Rf 0.48608 0.075 0.5027 0.075 0.52118 0.075 0.54183 0.075 0.56506 0.075 0.59139 0.075 0.62148 0.075 0.6562 0.075 0.69671 0.075 0.74458 0.075 0.80203 0.075 0.87224 0.075 0.96 0.075 1.07284 0.075 1.22329 0.075 1.43392 0.075 1.74987 0.075 2.27646 0.075 3.32962 0.075 6.48911 0.075
Rm 0.158 0.158 0.158 0.158 0.158 0.158 0.158 0.158 0.158 0.158 0.158 0.158 0.158 0.158 0.158 0.158 0.158 0.158 0.158 0.158
Ke 11.55% 11.68% 11.84% 12.01% 12.20% 12.42% 12.67% 12.96% 13.30% 13.70% 14.18% 14.76% 15.49% 16.43% 17.68% 19.44% 22.07% 26.45% 35.22% 61.52%
PAT Int PBIAT
1551.67 672.47 1787.03
Current Structure Structure where Firm Value Max.
+ Bu(1-t)(D/E)
Value - Firm Value - Debt
15,477.39 15,730.54 15,978.16 16,219.34 16,462.98 16,678.56 16,863.58 17,026.35 17,186.92 17,328.67 17,450.61 17,490.41 17,430.37 17,326.96 16,959.13 16,536.67 15,993.95 14,582.22 11,740.14 9,652.72 786.53 1,597.82 2,432.90 3,292.60 4,169.64 5,059.07 5,959.22 6,874.77 7,797.90 8,725.30 9,619.73 10,458.22 11,262.52 11,871.39 12,402.50 12,795.16 12,394.89 10,566.13 9,170.09 -
Int
62.92 130.22 201.93 276.58 358.59 447.73 542.29 639.35 740.80 846.35 961.97 1,087.66 1,216.35 1,365.21 1,513.11 1,663.37 1,859.23 2,113.23 2,292.52
Int (After Tax) PAT
40.90 84.64 131.25 179.78 233.08 291.02 352.49 415.58 481.52 550.13 625.28 706.98 790.63 887.39 983.52 1,081.19 1,208.50 1,373.60 1,490.14 1,787.03 1,746.14 1,702.39 1,655.78 1,607.26 1,553.95 1,496.01 1,434.55 1,371.45 1,305.51 1,236.90 1,161.75 1,080.06 996.41 899.65 803.52 705.84 578.53 413.44 296.90
Value - Equity
15,477.39 14,944.01 14,380.34 13,786.44 13,170.38 12,508.92 11,804.51 11,067.13 10,312.15 9,530.77 8,725.30 7,870.69 6,972.15 6,064.43 5,087.74 4,134.17 3,198.79 2,187.33 1,174.01 482.64
Value of Firm (Value of Equity +Debt)
15,477.39 15,730.54 15,978.16 16,219.34 16,462.98 16,678.56 16,863.58 17,026.35 17,186.92 17,328.67 17,450.61 17,490.41 17,430.37 17,326.96 16,959.13 16,536.67 15,993.95 14,582.22 11,740.14 9,652.72
No. of Shares
10,000 9,500 9,000 8,500 8,000 7,500 7,000 6,500 6,000 5,500 5,000 4,500 4,000 3,500 3,000 2,500 2,000 1,500 1,000 500
Value Per Share
1.55 1.57 1.60 1.62 1.65 1.67 1.69 1.70 1.72 1.73 1.75 1.75 1.74 1.73 1.70 1.65 1.60 1.46 1.17 0.97
HPCL
Tax Rate Rf 0.35 0.075 Rm Bu 0.1528 0.4101
BL = Bu + Bu(1-t)(D/E)
Debt Equity Kd (%) (%) (aftr tax) WACC Beta Rf Rm Ke Kd PBIAT 0% 100% 0.41013 0.075 0.1528 10.69% 7.80% 5.07% 10.69% 1,786.14 5% 95% 0.42416 0.075 0.1528 10.80% 8.00% 5.20% 10.52% 1,786.14 10% 90% 0.43975 0.075 0.1528 10.92% 8.15% 5.30% 10.36% 1,786.14 15% 85% 0.45717 0.075 0.1528 11.06% 8.30% 5.40% 10.21% 1,786.14 20% 80% 0.47677 0.075 0.1528 11.21% 8.40% 5.46% 10.06% 1,786.14 25% 75% 0.49899 0.075 0.1528 11.38% 8.60% 5.59% 9.94% 1,786.14 30% 70% 0.52438 0.075 0.1528 11.58% 8.85% 5.75% 9.83% 1,786.14 35% 65% 0.55367 0.075 0.1528 11.81% 9.10% 5.92% 9.75% 1,786.14 40% 60% 0.58785 0.075 0.1528 12.08% 9.30% 6.05% 9.66% 1,786.14 45% 55% 0.62824 0.075 0.1528 12.39% 9.50% 6.18% 9.59% 1,786.14 50% 50% 0.67671 0.075 0.1528 12.77% 9.70% 6.31% 9.54% 1,786.14 55% 45% 0.73595 0.075 0.1528 13.23% 10.00% 6.50% 9.53% 1,786.14 60% 40% 0.81 0.075 0.1528 13.81% 10.40% 6.76% 9.58% 1,786.14 65% 35% 0.90521 0.075 0.1528 14.55% 10.80% 7.02% 9.65% 1,786.14 70% 30% 1.03215 0.075 0.1528 15.53% 11.50% 7.48% 9.89% 1,786.14 75% 25% 1.20987 0.075 0.1528 16.92% 12.20% 7.93% 10.18% 1,786.14 80% 20% 1.47646 0.075 0.1528 18.99% 13.00% 8.45% 10.56% 1,786.14 85% 15% 1.92076 0.075 0.1528 22.45% 15.00% 9.75% 11.66% 1,786.14 90% 10% 2.80937 0.075 0.1528 29.37% 20.00% 13.00% 14.64% 1,786.14 95% 5% 5.47519 0.075 0.1528 50.12% 25.00% 16.25% 17.94% 1,786.14 100% 0%
PAT Int PBIAT
1271.34 792 1786.14
17300 10700
0.3821
Current Structure Structure where Firm Value Max.
+ Bu(1-t)(D/E)
Value - Firm Value - Debt
16,704.72 16,976.00 17,240.00 17,495.61 17,753.18 17,977.19 18,164.81 18,325.77 18,483.39 18,618.49 18,730.00 18,746.45 18,648.33 18,501.27 18,054.98 17,550.90 16,916.53 15,324.86 12,203.07 9,954.28 848.80 1,724.00 2,624.34 3,550.64 4,494.30 5,449.44 6,414.02 7,393.36 8,378.32 9,365.00 10,310.55 11,189.00 12,025.82 12,638.49 13,163.18 13,533.23 13,026.13 10,982.76 9,456.56 -
Int
67.90 140.51 217.82 298.25 386.51 482.28 583.68 687.58 795.94 908.41 1,031.05 1,163.66 1,298.79 1,453.43 1,605.91 1,759.32 1,953.92 2,196.55 2,364.14
Int (After Tax) PAT
44.14 91.33 141.58 193.86 251.23 313.48 379.39 446.93 517.36 590.46 670.19 756.38 844.21 944.73 1,043.84 1,143.56 1,270.05 1,427.76 1,536.69 1,786.14 1,742.00 1,694.81 1,644.56 1,592.28 1,534.91 1,472.66 1,406.75 1,339.21 1,268.78 1,195.68 1,115.95 1,029.76 941.93 841.41 742.30 642.58 516.09 358.38 249.45
Value - Equity
16,704.72 16,127.20 15,516.00 14,871.27 14,202.55 13,482.90 12,715.36 11,911.75 11,090.04 10,240.17 9,365.00 8,435.90 7,459.33 6,475.44 5,416.50 4,387.73 3,383.31 2,298.73 1,220.31 497.71
Value of Firm (Value of Equity +Debt)
16,704.72 16,976.00 17,240.00 17,495.61 17,753.18 17,977.19 18,164.81 18,325.77 18,483.39 18,618.49 18,730.00 18,746.45 18,648.33 18,501.27 18,054.98 17,550.90 16,916.53 15,324.86 12,203.07 9,954.28
No. of Shares
10,000 9,500 9,000 8,500 8,000 7,500 7,000 6,500 6,000 5,500 5,000 4,500 4,000 3,500 3,000 2,500 2,000 1,500 1,000 500
Value Per Share
1.67 1.70 1.72 1.75 1.78 1.80 1.82 1.83 1.85 1.86 1.87 1.87 1.86 1.85 1.81 1.76 1.69 1.53 1.22 1.00
IOC
Tax Rate Rf Debt Equity (%) (%) 0% 100% 5% 95% 10% 90% 15% 85% 20% 80% 25% 75% 30% 70% 35% 65% 40% 60% 45% 55% 50% 50% 55% 45% 60% 40% 65% 35% 70% 30% 75% 25% 80% 20% 85% 15% 90% 10% 95% 5% 100% 0% 0.35 0.075 Rm Bu 0.15824 0.46667
BL = Bu + Bu(1-t)(D/E)
Kd (aftr tax) WACC Kd PBIAT 7.80% 5.07% 11.38% 8,226.85 8.00% 5.20% 11.20% 8,226.85 8.15% 5.30% 11.03% 8,226.85 8.30% 5.40% 10.86% 8,226.85 8.40% 5.46% 10.70% 8,226.85 8.60% 5.59% 10.57% 8,226.85 8.85% 5.75% 10.45% 8,226.85 9.10% 5.92% 10.35% 8,226.85 9.30% 6.05% 10.26% 8,226.85 9.50% 6.18% 10.18% 8,226.85 9.70% 6.31% 10.11% 8,226.85 10.00% 6.50% 10.09% 8,226.85 10.40% 6.76% 10.12% 8,226.85 10.80% 7.02% 10.19% 8,226.85 11.50% 7.48% 10.42% 8,226.85 12.20% 7.93% 10.69% 8,226.85 13.00% 8.45% 11.06% 8,226.85 15.00% 9.75% 12.14% 8,226.85 20.00% 13.00% 15.11% 8,226.85 25.00% 16.25% 18.41% 8,226.85
Beta Rf 0.4667 0.075 0.4826 0.075 0.5004 0.075 0.5202 0.075 0.5425 0.075 0.5678 0.075 0.5967 0.075 0.63 0.075 0.6689 0.075 0.7148 0.075 0.77 0.075 0.8374 0.075 0.9217 0.075 1.03 0.075 1.1744 0.075 1.3767 0.075 1.68 0.075 2.1856 0.075 3.1967 0.075 6.23 0.075
Rm Ke 0.158 11.38% 0.158 11.52% 0.158 11.67% 0.158 11.83% 0.158 12.02% 0.158 12.23% 0.158 12.47% 0.158 12.74% 0.158 13.07% 0.158 13.45% 0.158 13.91% 0.158 14.47% 0.158 15.17% 0.158 16.07% 0.158 17.28% 0.158 18.96% 0.158 21.48% 0.158 25.69% 0.158 34.11% 0.158 59.36%
PAT Int PBIAT
7194 1589 8226.9
82548 45805
0.357
Current Structure Structure where Firm Value Max.
+ Bu(1-t)(D/E)
Value - Firm Value - Debt
72,263.39 73,443.83 74,597.46 75,719.90 76,853.29 77,853.16 78,707.71 79,456.32 80,193.89 80,841.89 81,395.61 81,560.87 81,254.59 80,744.04 78,987.95 76,977.50 74,404.91 67,758.75 54,443.13 44,697.96 3,672.19 7,459.75 11,357.98 15,370.66 19,463.29 23,612.31 27,809.71 32,077.56 36,378.85 40,697.81 44,858.48 48,752.76 52,483.62 55,291.57 57,733.13 59,523.93 57,594.93 48,998.82 42,463.06 -
Int
293.78 607.97 942.71 1,291.14 1,673.84 2,089.69 2,530.68 2,983.21 3,455.99 3,947.69 4,485.85 5,070.29 5,668.23 6,358.53 7,043.44 7,738.11 8,639.24 9,799.76 10,615.77
Int (After Tax) PAT
190.95 395.18 612.76 839.24 1,088.00 1,358.30 1,644.94 1,939.09 2,246.39 2,566.00 2,915.80 3,295.69 3,684.35 4,133.04 4,578.24 5,029.77 5,615.51 6,369.85 6,900.25 8,226.85 8,035.90 7,831.67 7,614.09 7,387.61 7,138.85 6,868.55 6,581.91 6,287.76 5,980.46 5,660.85 5,311.05 4,931.16 4,542.50 4,093.81 3,648.61 3,197.08 2,611.34 1,857.00 1,326.60
Value - Equity
72,263.39 69,771.64 67,137.71 64,361.91 61,482.64 58,389.87 55,095.39 51,646.61 48,116.34 44,463.04 40,697.81 36,702.39 32,501.84 28,260.41 23,696.39 19,244.38 14,880.98 10,163.81 5,444.31 2,234.90
Value of Firm (Value of Equity +Debt)
72,263.39 73,443.83 74,597.46 75,719.90 76,853.29 77,853.16 78,707.71 79,456.32 80,193.89 80,841.89 81,395.61 81,560.87 81,254.59 80,744.04 78,987.95 76,977.50 74,404.91 67,758.75 54,443.13 44,697.96
No. of Shares
100,000 95,000 90,000 85,000 80,000 75,000 70,000 65,000 60,000 55,000 50,000 45,000 40,000 35,000 30,000 25,000 20,000 15,000 10,000 5,000
Value Per Share
0.72 0.73 0.75 0.76 0.77 0.78 0.79 0.79 0.80 0.81 0.81 0.82 0.81 0.81 0.79 0.77 0.74 0.68 0.54 0.45
100
RIL ONGC Aban BPCL HPCL IOC
Current Eqt % Ideal Eqt % 85 40 90 35 40 40 40 45 40 45 35 45
90 80 70 60 50 40 30 20 10 0 RIL ONGC Aban
Current Eqt % Ideal Eqt %
Aban
BPCL
HPCL
IOC
doc_772963070.xls
Excel on Capital Structure Analysis of companies in the Oil and Gas sector.
RIL
Tax Rate Rf 0.35 0.075 Rm Bu 0.15824 0.849103
Ke = Rf + BL*(Rm-Rf) BL = Bu + Bu(1-t)(D/E)
Kd
7.80% 8.00% 8.15% 8.30% 8.40% 8.60% 8.85% 9.10% 9.30% 9.50% 9.70% 10.00% 10.40% 10.90% 11.50% 12.20% 13.00% 15.00% 20.00% 25.00%
Debt Equity (%) (%)
0% 5% 10% 15% 20% 25% 30% 35% 40% 45% 50% 55% 60% 65% 70% 75% 80% 85% 90% 95% 100% 100% 95% 90% 85% 80% 75% 70% 65% 60% 55% 50% 45% 40% 35% 30% 25% 20% 15% 10% 5% 0%
Beta
0.849103 0.878151 0.910427 0.9465 0.987082 1.033075 1.085639 1.146289 1.217047 1.300671 1.40102 1.523668 1.676978 1.874091 2.136909 2.504854 3.05677 3.976632 5.816355 11.33552
Rf
0.075 0.075 0.075 0.075 0.075 0.075 0.075 0.075 0.075 0.075 0.075 0.075 0.075 0.075 0.075 0.075 0.075 0.075 0.075 0.075
Rm
Ke
Kd (aftr tax) WACC
5.07% 5.20% 5.30% 5.40% 5.46% 5.59% 5.75% 5.92% 6.05% 6.18% 6.31% 6.50% 6.76% 7.09% 7.48% 7.93% 8.45% 9.75% 13.00% 16.25% 14.57% 14.33% 14.10% 13.88% 13.67% 13.47% 13.30% 13.15% 13.00% 12.86% 12.73% 12.66% 12.64% 12.69% 12.82% 13.04% 13.35% 14.38% 17.29% 20.53%
PBIAT
16,046.28 16,046.28 16,046.28 16,046.28 16,046.28 16,046.28 16,046.28 16,046.28 16,046.28 16,046.28 16,046.28 16,046.28 16,046.28 16,046.28 16,046.28 16,046.28 16,046.28 16,046.28 16,046.28 16,046.28
0.1582 14.57% 0.1582 14.81% 0.1582 15.08% 0.1582 15.38% 0.1582 15.72% 0.1582 16.10% 0.1582 16.54% 0.1582 17.04% 0.1582 17.63% 0.1582 18.33% 0.1582 19.16% 0.1582 20.18% 0.1582 21.46% 0.1582 23.10% 0.1582 25.29% 0.1582 28.35% 0.1582 32.94% 0.1582 40.60% 0.1582 55.92% 0.1582 101.86%
PAT Int PBIAT
15346 1077.36 16046.28
Current Structure Structure where Firm Value Max.
+ Bu(1-t)(D/E)
Value - Firm Value - Debt Int
110,147.98 111,982.77 113,800.97 115,597.94 117,424.63 119,108.50 120,634.70 122,049.47 123,466.94 124,791.42 126,015.85 126,774.38 126,951.80 126,445.99 125,177.85 123,100.58 120,206.69 111,605.19 92,798.50 78,158.86 5,599.14 11,380.10 17,339.69 23,484.93 29,777.13 36,190.41 42,717.31 49,386.77 56,156.14 63,007.92 69,725.91 76,171.08 82,189.89 87,624.49 92,325.43 96,165.35 94,864.41 83,518.65 74,250.92 447.93 927.48 1,439.19 1,972.73 2,560.83 3,202.85 3,887.28 4,592.97 5,334.83 6,111.77 6,972.59 7,921.79 8,958.70 10,076.82 11,263.70 12,501.50 14,229.66 16,703.73 18,562.73
Int PAT (After Tax) (Rs. Crore)
291.16 602.86 935.48 1,282.28 1,664.54 2,081.85 2,526.73 2,985.43 3,467.64 3,972.65 4,532.18 5,149.16 5,823.15 6,549.93 7,321.41 8,125.97 9,249.28 10,857.42 12,065.77 16,046.28 15,755.13 15,443.42 15,110.81 14,764.01 14,381.74 13,964.43 13,519.55 13,060.85 12,578.64 12,073.63 11,514.10 10,897.12 10,223.13 9,496.35 8,724.88 7,920.31 6,797.00 5,188.86 3,980.51
Value of Firm (Value of Value - Equity Equity +Debt)
110,147.98 106,383.63 102,420.87 98,258.25 93,939.70 89,331.38 84,444.29 79,332.15 74,080.16 68,635.28 63,007.92 57,048.47 50,780.72 44,256.10 37,553.35 30,775.14 24,041.34 16,740.78 9,279.85 3,907.94 110,147.98 111,982.77 113,800.97 115,597.94 117,424.63 119,108.50 120,634.70 122,049.47 123,466.94 124,791.42 126,015.85 126,774.38 126,951.80 126,445.99 125,177.85 123,100.58 120,206.69 111,605.19 92,798.50 78,158.86
No. of Shares Value Per Share
100,000 95,000 90,000 85,000 80,000 75,000 70,000 65,000 60,000 55,000 50,000 45,000 40,000 35,000 30,000 25,000 20,000 15,000 10,000 5,000 1.10 1.12 1.14 1.16 1.17 1.19 1.21 1.22 1.23 1.25 1.26 1.27 1.27 1.26 1.25 1.23 1.20 1.12 0.93 0.78
ONGC
Tax Rate Rf Debt Equity (%) (%) 0% 100% 5% 95% 10% 90% 15% 85% 20% 80% 25% 75% 30% 70% 35% 65% 40% 60% 45% 55% 50% 50% 55% 45% 60% 40% 65% 35% 70% 30% 75% 25% 80% 20% 85% 15% 90% 10% 95% 5% 100% 0% 0.35 0.075 Rm Bu 0.15824 1.02591
BL = Bu + Bu(1-t)(D/E)
Kd (aftr tax) WACC Kd PBIAT 7.80% 5.07% 16.04% 16,721.64 8.00% 5.20% 15.78% 16,721.64 8.15% 5.30% 15.52% 16,721.64 8.30% 5.40% 15.28% 16,721.64 8.40% 5.46% 15.03% 16,721.64 8.60% 5.59% 14.81% 16,721.64 8.85% 5.75% 14.62% 16,721.64 9.10% 5.92% 14.44% 16,721.64 9.30% 6.05% 14.26% 16,721.64 9.50% 6.18% 14.10% 16,721.64 9.70% 6.31% 13.95% 16,721.64 10.00% 6.50% 13.85% 16,721.64 10.40% 6.76% 13.80% 16,721.64 10.80% 7.02% 13.78% 16,721.64 11.50% 7.48% 13.93% 16,721.64 12.20% 7.93% 14.12% 16,721.64 13.00% 8.45% 14.41% 16,721.64 15.00% 9.75% 15.41% 16,721.64 20.00% 13.00% 18.30% 16,721.64 25.00% 16.25% 21.51% 16,721.64
Beta Rf 1.02591 0.075 1.061 0.075 1.1 0.075 1.14358 0.075 1.19262 0.075 1.24819 0.075 1.3117 0.075 1.38497 0.075 1.47047 0.075 1.5715 0.075 1.69275 0.075 1.84093 0.075 2.02617 0.075 2.26432 0.075 2.58187 0.075 3.02642 0.075 3.69326 0.075 4.80466 0.075 7.02746 0.075 13.6959 0.075
Rm Ke 0.1582 16.04% 0.1582 16.33% 0.1582 16.66% 0.1582 17.02% 0.1582 17.43% 0.1582 17.89% 0.1582 18.42% 0.1582 19.03% 0.1582 19.74% 0.1582 20.58% 0.1582 21.59% 0.1582 22.82% 0.1582 24.37% 0.1582 26.35% 0.1582 28.99% 0.1582 32.69% 0.1582 38.24% 0.1582 47.49% 0.1582 66.00% 0.1582 121.50%
PAT Int PBIAT
16701 58.98 16721.6
Current Structure Structure where Firm Value Max.
+ Bu(1-t)(D/E)
Value - Firm Value - Debt
104,251.93 105,999.54 107,739.00 109,466.60 111,226.45 112,870.22 114,385.02 115,810.55 117,245.33 118,606.65 119,888.09 120,770.81 121,150.94 121,304.25 120,041.08 118,421.12 116,053.64 108,500.35 91,376.80 77,729.12 5,299.98 10,773.90 16,419.99 22,245.29 28,217.56 34,315.51 40,533.69 46,898.13 53,372.99 59,944.05 66,423.94 72,690.57 78,847.76 84,028.75 88,815.84 92,842.91 92,225.30 82,239.12 73,842.66 -
Int
424.00 878.07 1,362.86 1,868.60 2,426.71 3,036.92 3,688.57 4,361.53 5,070.43 5,814.57 6,642.39 7,559.82 8,515.56 9,663.31 10,835.53 12,069.58 13,833.79 16,447.82 18,460.66
Int (After Tax) PAT
275.60 570.75 885.86 1,214.59 1,577.36 1,974.00 2,397.57 2,834.99 3,295.78 3,779.47 4,317.56 4,913.88 5,535.11 6,281.15 7,043.10 7,845.23 8,991.97 10,691.09 11,999.43 16,721.64 16,446.04 16,150.90 15,835.78 15,507.05 15,144.28 14,747.64 14,324.08 13,886.65 13,425.86 12,942.17 12,404.09 11,807.76 11,186.53 10,440.49 9,678.55 8,876.42 7,729.68 6,030.56 4,722.21
Value - Equity
104,251.93 100,699.56 96,965.10 93,046.61 88,981.16 84,652.67 80,069.52 75,276.86 70,347.20 65,233.65 59,944.05 54,346.86 48,460.38 42,456.49 36,012.32 29,605.28 23,210.73 16,275.05 9,137.68 3,886.46
ure where Firm Value Max.
Value of Firm (Value of Equity +Debt)
104,251.93 105,999.54 107,739.00 109,466.60 111,226.45 112,870.22 114,385.02 115,810.55 117,245.33 118,606.65 119,888.09 120,770.81 121,150.94 121,304.25 120,041.08 118,421.12 116,053.64 108,500.35 91,376.80 77,729.12
No. of Shares
100,000 95,000 90,000 85,000 80,000 75,000 70,000 65,000 60,000 55,000 50,000 45,000 40,000 35,000 30,000 25,000 20,000 15,000 10,000 5,000
Value Per Share
1.04 1.06 1.08 1.09 1.11 1.13 1.14 1.16 1.17 1.19 1.20 1.21 1.21 1.21 1.20 1.18 1.16 1.09 0.91 0.78
ABAN Offshore Ltd.
Tax Rate Rf Debt Equity (%) (%) 0% 100% 5% 95% 10% 90% 15% 85% 20% 80% 25% 75% 30% 70% 35% 65% 40% 60% 45% 55% 50% 50% 55% 45% 60% 40% 65% 35% 70% 30% 75% 25% 80% 20% 85% 15% 90% 10% 95% 5% 100% 0% 0.35 0.075 Rm Bu 0.15824 0.73924
BL = Bu + Bu(1-t)(D/E)
Kd 7.80% 8.00% 8.15% 8.30% 8.40% 8.60% 8.85% 9.10% 9.30% 9.50% 9.70% 10.00% 10.40% 10.80% 11.50% 12.20% 13.00% 15.00% 20.00% 25.00% Kd (aftr tax) WACC PBIAT 5.07% 13.65% 182.93 5.20% 13.43% 182.93 5.30% 13.22% 182.93 5.40% 13.01% 182.93 5.46% 12.81% 182.93 5.59% 12.64% 182.93 5.75% 12.48% 182.93 5.92% 12.34% 182.93 6.05% 12.21% 182.93 6.18% 12.09% 182.93 6.31% 11.98% 182.93 6.50% 11.92% 182.93 6.76% 11.92% 182.93 7.02% 11.94% 182.93 7.48% 12.13% 182.93 7.93% 12.36% 182.93 8.45% 12.69% 182.93 9.75% 13.74% 182.93 13.00% 16.67% 182.93 16.25% 19.92% 182.93
Beta Rf 0.739 0.075 0.765 0.075 0.793 0.075 0.824 0.075 0.859 0.075 0.899 0.075 0.945 0.075 0.998 0.075 1.06 0.075 1.132 0.075 1.22 0.075 1.327 0.075 1.46 0.075 1.632 0.075 1.86 0.075 2.181 0.075 2.661 0.075 3.462 0.075 5.064 0.075 9.869 0.075
Rm Ke 0.1582 13.65% 0.1582 13.86% 0.1582 14.10% 0.1582 14.36% 0.1582 14.65% 0.1582 14.99% 0.1582 15.37% 0.1582 15.81% 0.1582 16.32% 0.1582 16.93% 0.1582 17.65% 0.1582 18.54% 0.1582 19.65% 0.1582 21.08% 0.1582 22.99% 0.1582 25.65% 0.1582 29.65% 0.1582 36.32% 0.1582 49.65% 0.1582 89.65%
31355 69935 Net worth 101290 93796 51835 Debt 145631 PAT Int PBIAT 158 72.66 204.96
PAT Int PBIAT
157.73 72 182.93
Current Structure Structure where Firm Value Max.
+ Bu(1-t)(D/E)
Value - Firm Value - Debt
1,339.81 1,362.02 1,383.97 1,405.57 1,427.50 1,447.52 1,465.42 1,481.83 1,498.20 1,513.32 1,527.08 1,534.79 1,535.01 1,531.88 1,508.28 1,479.94 1,441.47 1,331.82 1,097.68 918.33 68.10 138.40 210.84 285.50 361.88 439.63 518.64 599.28 680.99 763.54 844.13 921.00 995.72 1,055.80 1,109.95 1,153.18 1,132.05 987.91 872.41 -
Int
5.45 11.28 17.50 23.98 31.12 38.91 47.20 55.73 64.69 74.06 84.41 95.78 107.54 121.42 135.41 149.91 169.81 197.58 218.10
Int (After Tax) PAT
3.54 7.33 11.37 15.59 20.23 25.29 30.68 36.23 42.05 48.14 54.87 62.26 69.90 78.92 88.02 97.44 110.37 128.43 141.77 182.93 179.39 175.60 171.56 167.34 162.70 157.64 152.25 146.70 140.88 134.79 128.06 120.67 113.03 104.01 94.91 85.49 72.56 54.50 41.16
Value - Equity
1,339.81 1,293.92 1,245.57 1,194.74 1,142.00 1,085.64 1,025.80 963.19 898.92 832.32 763.54 690.66 614.00 536.16 452.49 369.98 288.29 199.77 109.77 45.92
Value of Firm (Value of Equity +Debt)
1,339.81 1,362.02 1,383.97 1,405.57 1,427.50 1,447.52 1,465.42 1,481.83 1,498.20 1,513.32 1,527.08 1,534.79 1,535.01 1,531.88 1,508.28 1,479.94 1,441.47 1,331.82 1,097.68 918.33
No. of Shares
100,000 95,000 90,000 85,000 80,000 75,000 70,000 65,000 60,000 55,000 50,000 45,000 40,000 35,000 30,000 25,000 20,000 15,000 10,000 5,000
Value Per Share
0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.01 0.02 0.02 0.02 0.02 0.02 0.02 0.01 0.01 0.01 0.01 0.01
BPCL
Tax Rate Rf Debt Equity (%) (%) 0% 100% 5% 95% 10% 90% 15% 85% 20% 80% 25% 75% 30% 70% 35% 65% 40% 60% 45% 55% 50% 50% 55% 45% 60% 40% 65% 35% 70% 30% 75% 25% 80% 20% 85% 15% 90% 10% 95% 5% 100% 0% 0.35 0.075 Rm Bu 0.1582 0.4861
BL = Bu + Bu(1-t)(D/E)
Kd 7.80% 8.00% 8.15% 8.30% 8.40% 8.60% 8.85% 9.10% 9.30% 9.50% 9.70% 10.00% 10.40% 10.80% 11.50% 12.20% 13.00% 15.00% 20.00% 25.00% Kd (aftr tax) WACC PBIAT 5.07% 11.55% 1,787.03 5.20% 11.36% 1,787.03 5.30% 11.18% 1,787.03 5.40% 11.02% 1,787.03 5.46% 10.85% 1,787.03 5.59% 10.71% 1,787.03 5.75% 10.60% 1,787.03 5.92% 10.50% 1,787.03 6.05% 10.40% 1,787.03 6.18% 10.31% 1,787.03 6.31% 10.24% 1,787.03 6.50% 10.22% 1,787.03 6.76% 10.25% 1,787.03 7.02% 10.31% 1,787.03 7.48% 10.54% 1,787.03 7.93% 10.81% 1,787.03 8.45% 11.17% 1,787.03 9.75% 12.25% 1,787.03 13.00% 15.22% 1,787.03 16.25% 18.51% 1,787.03
Beta Rf 0.48608 0.075 0.5027 0.075 0.52118 0.075 0.54183 0.075 0.56506 0.075 0.59139 0.075 0.62148 0.075 0.6562 0.075 0.69671 0.075 0.74458 0.075 0.80203 0.075 0.87224 0.075 0.96 0.075 1.07284 0.075 1.22329 0.075 1.43392 0.075 1.74987 0.075 2.27646 0.075 3.32962 0.075 6.48911 0.075
Rm 0.158 0.158 0.158 0.158 0.158 0.158 0.158 0.158 0.158 0.158 0.158 0.158 0.158 0.158 0.158 0.158 0.158 0.158 0.158 0.158
Ke 11.55% 11.68% 11.84% 12.01% 12.20% 12.42% 12.67% 12.96% 13.30% 13.70% 14.18% 14.76% 15.49% 16.43% 17.68% 19.44% 22.07% 26.45% 35.22% 61.52%
PAT Int PBIAT
1551.67 672.47 1787.03
Current Structure Structure where Firm Value Max.
+ Bu(1-t)(D/E)
Value - Firm Value - Debt
15,477.39 15,730.54 15,978.16 16,219.34 16,462.98 16,678.56 16,863.58 17,026.35 17,186.92 17,328.67 17,450.61 17,490.41 17,430.37 17,326.96 16,959.13 16,536.67 15,993.95 14,582.22 11,740.14 9,652.72 786.53 1,597.82 2,432.90 3,292.60 4,169.64 5,059.07 5,959.22 6,874.77 7,797.90 8,725.30 9,619.73 10,458.22 11,262.52 11,871.39 12,402.50 12,795.16 12,394.89 10,566.13 9,170.09 -
Int
62.92 130.22 201.93 276.58 358.59 447.73 542.29 639.35 740.80 846.35 961.97 1,087.66 1,216.35 1,365.21 1,513.11 1,663.37 1,859.23 2,113.23 2,292.52
Int (After Tax) PAT
40.90 84.64 131.25 179.78 233.08 291.02 352.49 415.58 481.52 550.13 625.28 706.98 790.63 887.39 983.52 1,081.19 1,208.50 1,373.60 1,490.14 1,787.03 1,746.14 1,702.39 1,655.78 1,607.26 1,553.95 1,496.01 1,434.55 1,371.45 1,305.51 1,236.90 1,161.75 1,080.06 996.41 899.65 803.52 705.84 578.53 413.44 296.90
Value - Equity
15,477.39 14,944.01 14,380.34 13,786.44 13,170.38 12,508.92 11,804.51 11,067.13 10,312.15 9,530.77 8,725.30 7,870.69 6,972.15 6,064.43 5,087.74 4,134.17 3,198.79 2,187.33 1,174.01 482.64
Value of Firm (Value of Equity +Debt)
15,477.39 15,730.54 15,978.16 16,219.34 16,462.98 16,678.56 16,863.58 17,026.35 17,186.92 17,328.67 17,450.61 17,490.41 17,430.37 17,326.96 16,959.13 16,536.67 15,993.95 14,582.22 11,740.14 9,652.72
No. of Shares
10,000 9,500 9,000 8,500 8,000 7,500 7,000 6,500 6,000 5,500 5,000 4,500 4,000 3,500 3,000 2,500 2,000 1,500 1,000 500
Value Per Share
1.55 1.57 1.60 1.62 1.65 1.67 1.69 1.70 1.72 1.73 1.75 1.75 1.74 1.73 1.70 1.65 1.60 1.46 1.17 0.97
HPCL
Tax Rate Rf 0.35 0.075 Rm Bu 0.1528 0.4101
BL = Bu + Bu(1-t)(D/E)
Debt Equity Kd (%) (%) (aftr tax) WACC Beta Rf Rm Ke Kd PBIAT 0% 100% 0.41013 0.075 0.1528 10.69% 7.80% 5.07% 10.69% 1,786.14 5% 95% 0.42416 0.075 0.1528 10.80% 8.00% 5.20% 10.52% 1,786.14 10% 90% 0.43975 0.075 0.1528 10.92% 8.15% 5.30% 10.36% 1,786.14 15% 85% 0.45717 0.075 0.1528 11.06% 8.30% 5.40% 10.21% 1,786.14 20% 80% 0.47677 0.075 0.1528 11.21% 8.40% 5.46% 10.06% 1,786.14 25% 75% 0.49899 0.075 0.1528 11.38% 8.60% 5.59% 9.94% 1,786.14 30% 70% 0.52438 0.075 0.1528 11.58% 8.85% 5.75% 9.83% 1,786.14 35% 65% 0.55367 0.075 0.1528 11.81% 9.10% 5.92% 9.75% 1,786.14 40% 60% 0.58785 0.075 0.1528 12.08% 9.30% 6.05% 9.66% 1,786.14 45% 55% 0.62824 0.075 0.1528 12.39% 9.50% 6.18% 9.59% 1,786.14 50% 50% 0.67671 0.075 0.1528 12.77% 9.70% 6.31% 9.54% 1,786.14 55% 45% 0.73595 0.075 0.1528 13.23% 10.00% 6.50% 9.53% 1,786.14 60% 40% 0.81 0.075 0.1528 13.81% 10.40% 6.76% 9.58% 1,786.14 65% 35% 0.90521 0.075 0.1528 14.55% 10.80% 7.02% 9.65% 1,786.14 70% 30% 1.03215 0.075 0.1528 15.53% 11.50% 7.48% 9.89% 1,786.14 75% 25% 1.20987 0.075 0.1528 16.92% 12.20% 7.93% 10.18% 1,786.14 80% 20% 1.47646 0.075 0.1528 18.99% 13.00% 8.45% 10.56% 1,786.14 85% 15% 1.92076 0.075 0.1528 22.45% 15.00% 9.75% 11.66% 1,786.14 90% 10% 2.80937 0.075 0.1528 29.37% 20.00% 13.00% 14.64% 1,786.14 95% 5% 5.47519 0.075 0.1528 50.12% 25.00% 16.25% 17.94% 1,786.14 100% 0%
PAT Int PBIAT
1271.34 792 1786.14
17300 10700
0.3821
Current Structure Structure where Firm Value Max.
+ Bu(1-t)(D/E)
Value - Firm Value - Debt
16,704.72 16,976.00 17,240.00 17,495.61 17,753.18 17,977.19 18,164.81 18,325.77 18,483.39 18,618.49 18,730.00 18,746.45 18,648.33 18,501.27 18,054.98 17,550.90 16,916.53 15,324.86 12,203.07 9,954.28 848.80 1,724.00 2,624.34 3,550.64 4,494.30 5,449.44 6,414.02 7,393.36 8,378.32 9,365.00 10,310.55 11,189.00 12,025.82 12,638.49 13,163.18 13,533.23 13,026.13 10,982.76 9,456.56 -
Int
67.90 140.51 217.82 298.25 386.51 482.28 583.68 687.58 795.94 908.41 1,031.05 1,163.66 1,298.79 1,453.43 1,605.91 1,759.32 1,953.92 2,196.55 2,364.14
Int (After Tax) PAT
44.14 91.33 141.58 193.86 251.23 313.48 379.39 446.93 517.36 590.46 670.19 756.38 844.21 944.73 1,043.84 1,143.56 1,270.05 1,427.76 1,536.69 1,786.14 1,742.00 1,694.81 1,644.56 1,592.28 1,534.91 1,472.66 1,406.75 1,339.21 1,268.78 1,195.68 1,115.95 1,029.76 941.93 841.41 742.30 642.58 516.09 358.38 249.45
Value - Equity
16,704.72 16,127.20 15,516.00 14,871.27 14,202.55 13,482.90 12,715.36 11,911.75 11,090.04 10,240.17 9,365.00 8,435.90 7,459.33 6,475.44 5,416.50 4,387.73 3,383.31 2,298.73 1,220.31 497.71
Value of Firm (Value of Equity +Debt)
16,704.72 16,976.00 17,240.00 17,495.61 17,753.18 17,977.19 18,164.81 18,325.77 18,483.39 18,618.49 18,730.00 18,746.45 18,648.33 18,501.27 18,054.98 17,550.90 16,916.53 15,324.86 12,203.07 9,954.28
No. of Shares
10,000 9,500 9,000 8,500 8,000 7,500 7,000 6,500 6,000 5,500 5,000 4,500 4,000 3,500 3,000 2,500 2,000 1,500 1,000 500
Value Per Share
1.67 1.70 1.72 1.75 1.78 1.80 1.82 1.83 1.85 1.86 1.87 1.87 1.86 1.85 1.81 1.76 1.69 1.53 1.22 1.00
IOC
Tax Rate Rf Debt Equity (%) (%) 0% 100% 5% 95% 10% 90% 15% 85% 20% 80% 25% 75% 30% 70% 35% 65% 40% 60% 45% 55% 50% 50% 55% 45% 60% 40% 65% 35% 70% 30% 75% 25% 80% 20% 85% 15% 90% 10% 95% 5% 100% 0% 0.35 0.075 Rm Bu 0.15824 0.46667
BL = Bu + Bu(1-t)(D/E)
Kd (aftr tax) WACC Kd PBIAT 7.80% 5.07% 11.38% 8,226.85 8.00% 5.20% 11.20% 8,226.85 8.15% 5.30% 11.03% 8,226.85 8.30% 5.40% 10.86% 8,226.85 8.40% 5.46% 10.70% 8,226.85 8.60% 5.59% 10.57% 8,226.85 8.85% 5.75% 10.45% 8,226.85 9.10% 5.92% 10.35% 8,226.85 9.30% 6.05% 10.26% 8,226.85 9.50% 6.18% 10.18% 8,226.85 9.70% 6.31% 10.11% 8,226.85 10.00% 6.50% 10.09% 8,226.85 10.40% 6.76% 10.12% 8,226.85 10.80% 7.02% 10.19% 8,226.85 11.50% 7.48% 10.42% 8,226.85 12.20% 7.93% 10.69% 8,226.85 13.00% 8.45% 11.06% 8,226.85 15.00% 9.75% 12.14% 8,226.85 20.00% 13.00% 15.11% 8,226.85 25.00% 16.25% 18.41% 8,226.85
Beta Rf 0.4667 0.075 0.4826 0.075 0.5004 0.075 0.5202 0.075 0.5425 0.075 0.5678 0.075 0.5967 0.075 0.63 0.075 0.6689 0.075 0.7148 0.075 0.77 0.075 0.8374 0.075 0.9217 0.075 1.03 0.075 1.1744 0.075 1.3767 0.075 1.68 0.075 2.1856 0.075 3.1967 0.075 6.23 0.075
Rm Ke 0.158 11.38% 0.158 11.52% 0.158 11.67% 0.158 11.83% 0.158 12.02% 0.158 12.23% 0.158 12.47% 0.158 12.74% 0.158 13.07% 0.158 13.45% 0.158 13.91% 0.158 14.47% 0.158 15.17% 0.158 16.07% 0.158 17.28% 0.158 18.96% 0.158 21.48% 0.158 25.69% 0.158 34.11% 0.158 59.36%
PAT Int PBIAT
7194 1589 8226.9
82548 45805
0.357
Current Structure Structure where Firm Value Max.
+ Bu(1-t)(D/E)
Value - Firm Value - Debt
72,263.39 73,443.83 74,597.46 75,719.90 76,853.29 77,853.16 78,707.71 79,456.32 80,193.89 80,841.89 81,395.61 81,560.87 81,254.59 80,744.04 78,987.95 76,977.50 74,404.91 67,758.75 54,443.13 44,697.96 3,672.19 7,459.75 11,357.98 15,370.66 19,463.29 23,612.31 27,809.71 32,077.56 36,378.85 40,697.81 44,858.48 48,752.76 52,483.62 55,291.57 57,733.13 59,523.93 57,594.93 48,998.82 42,463.06 -
Int
293.78 607.97 942.71 1,291.14 1,673.84 2,089.69 2,530.68 2,983.21 3,455.99 3,947.69 4,485.85 5,070.29 5,668.23 6,358.53 7,043.44 7,738.11 8,639.24 9,799.76 10,615.77
Int (After Tax) PAT
190.95 395.18 612.76 839.24 1,088.00 1,358.30 1,644.94 1,939.09 2,246.39 2,566.00 2,915.80 3,295.69 3,684.35 4,133.04 4,578.24 5,029.77 5,615.51 6,369.85 6,900.25 8,226.85 8,035.90 7,831.67 7,614.09 7,387.61 7,138.85 6,868.55 6,581.91 6,287.76 5,980.46 5,660.85 5,311.05 4,931.16 4,542.50 4,093.81 3,648.61 3,197.08 2,611.34 1,857.00 1,326.60
Value - Equity
72,263.39 69,771.64 67,137.71 64,361.91 61,482.64 58,389.87 55,095.39 51,646.61 48,116.34 44,463.04 40,697.81 36,702.39 32,501.84 28,260.41 23,696.39 19,244.38 14,880.98 10,163.81 5,444.31 2,234.90
Value of Firm (Value of Equity +Debt)
72,263.39 73,443.83 74,597.46 75,719.90 76,853.29 77,853.16 78,707.71 79,456.32 80,193.89 80,841.89 81,395.61 81,560.87 81,254.59 80,744.04 78,987.95 76,977.50 74,404.91 67,758.75 54,443.13 44,697.96
No. of Shares
100,000 95,000 90,000 85,000 80,000 75,000 70,000 65,000 60,000 55,000 50,000 45,000 40,000 35,000 30,000 25,000 20,000 15,000 10,000 5,000
Value Per Share
0.72 0.73 0.75 0.76 0.77 0.78 0.79 0.79 0.80 0.81 0.81 0.82 0.81 0.81 0.79 0.77 0.74 0.68 0.54 0.45
100
RIL ONGC Aban BPCL HPCL IOC
Current Eqt % Ideal Eqt % 85 40 90 35 40 40 40 45 40 45 35 45
90 80 70 60 50 40 30 20 10 0 RIL ONGC Aban
Current Eqt % Ideal Eqt %
Aban
BPCL
HPCL
IOC
doc_772963070.xls