Loan Calculation, WACC, And Project Analysis
Loan Calculation
Part A (i)
The interest that will be paid in first month of 25th year of loan is $ 705.6246.
The formula for computation of interest is IPMT(Rate, Per, Nper, PV, FV)
Accordingly, the formula put in was IPMT(0.036/12,289,360,-800000)
The solution that was arrived stands at $ 705.62
Part A (ii)
The total amount of interest that will be paid over the life of the loan is $509378.61.
|
Sl No |
Particulars |
Amount ($) |
|
1 |
Loan amount |
800000 |
|
2 |
Tenure |
30 years |
|
3 |
Saving Interest Rate |
1.20% |
|
4 |
Loan Interest rate |
3.60% |
|
5 |
Monthly EMI |
3637.16 |
|
6 |
Total Amount of Emi |
1309378.61 |
|
7 |
Interest |
509378.61 |
The computation of Monthly EMI has been done by using the formula PMT(rate, nper,pv,fv)
Accordingly, the values have been put in PMT(0.036/12,360(12*30),-800000,0)
The value derived was $ 3637.16 per month EMI.
Further to compute the entire amount of EMI that has been paid, EMI has been multiplied by 360= 3637.16 *360=$1309378.61 and the interest amount that has been paid on principal has been derived by reducing the loan amount from the total EMI=
Total Emi-Principal Loan
1309378.61-800000= $5,09,378.61
Part B
The Principal amount of loan under the said programme is AUD 800,000 and interest on the same over the tenure of the loan i.e. 360 months is $5,09,378 roughly 63% of principal. Further, in case of repayment of loan initially the interest is set off and then principal leading to such huge interest on the loan over the tenure. It shall also be worthwhile to note that the amount of interest decreases over the tenure of the loan.
The above computation is based on the understanding that there are no prepayment of intention to set off the loan earlier. Further, the interest rate on such loan does not fluctuate over the period and borrower does not default the loan.
Part C
For computation of present value of the loan EMI paid over the period of 360 months have been discounted using the rate of 1.2% p.a. compounded monthly. Accordingly the formula used has been stated here-in-below:
T( Sigma)t=0= Cash flow/(1+r)t
Accordingly the present value of 1st EMI has been calculated as =3637.16/(1+.012/12)^1
Last EMI = 3637.16/(1+.012/12)^360
The resulting value has been added to arrive at the value $ 1,09,9143.923. (Refer Excel for detailed computation)
Part D
The present value has been computed on the basis of time value of money and the rate used for discounting is rate for deposit which can be considered equivalent to risk free interest rate. Further, it shall be noted that loan repayment is inclusive of interest and principal.
The difference between the actual amount of loan granted and the present value of such loan is $ 2,99,143.9229 which represent that I am paying extra by taking the loan as the person who lent me the money has earned extra after considering the risk free rate of interest. The extra amount represent payment for undertaking risk.
WACC Calculation
The rational for such difference is use of lower discount rate i.e 1.2% instead of 3.6%. Thus, the difference is mainly on account of risk undertaken by the person lending the money.
Part A (i)
The computation of WACC is presented here-in-below:
- Cost of Debt of the company before tax is 8%
- Cost of the debt net of tax = 8%*(1-tax rate)=8%(1-20%)=6.4%
- Risk Free Rate is 5%
- Market Return is 15%
- Risk Premium= Market Return- Risk Free Rate=15%-5%=10%
- Beta of Bad Inc= 1.2
Since Beta Inc is similar to the tested company, Beta of asset of the same needs to be computed to derive the beta of the tested company:
Beta Asset-= Beta Equity/(1+ (1-tax)*Debt/Equity)
=1.2(1+(1+(1-0.2)*2/4)
=0.857
Beta of the tested company =
Beta of Equity = Beta of Asset *(1+ (1-tax)*Debt/Equity)
=0.857*(1+(1-0.2)*3/6)
=1.2
- Cost of Equity = Risk Free Rate + Risk Premium* Beta of Equity
=5%+ 10% *1.2
=17%
- Weighted Average Cost of Capital of Company = (Cost of debt* weight of debt + Cost of equity* Weight of Equity)/ total weight
=(6.4%*3+ 17%*6)/9
=13.47%
|
Weighted Average Cost of capital |
||
|
Sl NO |
Particular |
Cost |
|
1 |
Cost of Debt |
8% |
|
2 |
Cost of Debt post tax |
6.4% |
|
3 |
Risk Free rate |
5% |
|
4 |
Market return |
15% |
|
5 |
Risk Premium |
10% |
|
6 |
Beta |
1.20 |
|
7 |
Cost of Equity |
17.00% |
|
8 |
Weight of Debt |
3.00 |
|
9 |
Weight of Equity |
6 |
|
10 |
Weight of Debt |
3 |
|
11 |
WACC |
13.47% |
|
Computation of beta |
||
|
Sl NO |
Particular |
Cost |
|
1 |
Beta Levered |
1.2 |
|
2 |
Beta Unlevered |
0.857142857 |
|
3 |
Beta of Proposed project |
1.2 |
For computation of cost of equity, only Bad Inc. has been considered as Bad Inc. business is similar to that of the company being analysed. Thus, on the basis of same WACC has been estimated @13.47%.
Part A (ii)
The suitable discount rate is Weighted Average Cost of capital of the project i.e. 13.47% as the same justifies the require rate of return desired by the people who are financing the project. Thus, WACC shall serve the purpose of suitable discount rate below which the project shall not be accepted. (CFI Education Inc., 2018)
In addition, the above represents the hurdle rate and the minimum rate of return that shall be given to investor for the project to survive and continue. Thus, it shall be prudent to use this rate as the same represent reward for the risk undertaken by the investor,
Part B (i)
The operating cash flows of the project for the first five years have been detailed here-in-below:
|
Sl No |
Particular |
year 0 |
year 1 |
year 2 |
year 3 |
year 4 |
year 5 |
Terminal Value |
|
1 |
Revenue |
800 |
960 |
1152 |
1382.4 |
1658.88 |
||
|
2 |
Annual Working Cost (Variable) |
-240 |
-288 |
-345.6 |
-414.72 |
-497.664 |
||
|
3 |
Fixed Cost |
-80 |
-80 |
-80 |
-80 |
-80 |
||
|
4 |
Depreciation |
-120 |
-120 |
-120 |
-120 |
-120 |
||
|
5 |
EBIT (1-2-3-4) |
360 |
472 |
606.4 |
767.68 |
961.216 |
||
|
6 |
Tax (5*20%) |
-72 |
-94.4 |
-121.28 |
-153.536 |
-192.2432 |
||
|
7 |
EBI (5-6) |
288 |
377.6 |
485.12 |
614.144 |
768.9728 |
||
|
8 |
Depreciation |
120 |
120 |
120 |
120 |
120 |
||
|
9 |
OCF |
408 |
497.6 |
605.12 |
734.144 |
888.9728 |
- Revenue for year 1 has been provided and from year 2 the same has been computed like
Year 1*1.2=800*1.2=960
Year 2*1.2=960*1,2=1152
…….
Year 4*1.2=1382.4*1.2=1658.88
- Annual Working Cost or Variable cost has been computed in the following manner
Year 1= Revenue Year 1*30%=800*30%=240
Year 2= Revenue Year 2*30%=960*30%=288
Year 5= Revenue Year 5*30%=1658.88*30%=497.664
- Fixed cost has been provided at $80.
- Depreciation has been computed by allocating the plant cost over a period of 5 year i,e
600/5=120 every year
- Earnings Before Interest and Tax has been computed by = Revenue-variable cost-fixed cost-depreciation
For year 1
= 800-240-80-120=$360
For other year similar calculation has been done.
- Tax has been computed = EBIT*30%
For year 1
= $360*20%= $72
For other years similar computation has been done.
- Operating Cash Flow = EBIT-Tax + Depreciation
For year 1
= $360-$72+$120=$408
For year 5
=961.216-192.2432+120=$888.9728
Part B(ii)
The change is working capital over next five years has been presented here-in- below:
|
Sl No |
Particular |
year 0 |
year 1 |
year 2 |
year 3 |
year 4 |
year 5 |
Terminal Value |
|
1 |
Net Working Capital Level |
80 |
96 |
115.2 |
138.24 |
165.888 |
||
|
2 |
Change in Net Working Capital |
80 |
16 |
19.2 |
23.04 |
27.648 |
||
|
3 |
Net Cash flow from sale of Asset |
240 |
||||||
|
4 |
Realisation of Net working capital at end |
165.888 |
The said computation has been derived in the following manner:
- Net Working Capital Level
For year 1 = Revenue for year 1*10%=800*10%=$80
For Year 5= Revenue for year 5*10%=1658.88*10%=$165.89
- Change in Working Capital level
For year 1 = Working Capital Level in Year 1- Working Capital level in Year 0=80-0=$80
For year 2 = Working Capital Level in Year 2- Working Capital level in Year 1=90-80=$16
For year 5 = Working Capital Level in Year 5- Working Capital level in Year 4=165.88-138.24=$27.648
- Net Cash flow from sale of Asset : Sale Value *(1-Tax Rate )
Projected Cash Flows and Financial Analysis for Project
=300*(1-20%) =$240
- Realisation of Working Capital at the end of the project = Net Working Capital level at year 5 = $165.89
Part B(iii)
|
Sl No |
Particular |
year 0 |
year 1 |
year 2 |
year 3 |
year 4 |
year 5 |
Terminal Value |
|
1 |
Infrastructural Investment |
-600 |
||||||
|
2 |
Depreciation |
-120 |
-120 |
-120 |
-120 |
-120 |
||
|
3 |
Salvage Value |
240 |
||||||
|
4 |
Revenue |
800 |
960 |
1152 |
1382.4 |
1658.88 |
||
|
5 |
Annual Working Cost |
-240 |
-288 |
-345.6 |
-414.72 |
-497.664 |
||
|
6 |
Working Capital |
-80 |
-16 |
-19.2 |
-23.04 |
-27.64 |
165.88 |
|
|
7 |
Fixed Cost |
-80 |
-80 |
-80 |
-80 |
-80 |
||
|
8 |
Net Cash Flow |
280 |
456 |
587.2 |
744.64 |
933.576 |
405.88 |
|
|
9 |
Tax |
-72 |
-94.4 |
-121.28 |
-153.536 |
-192.2432 |
||
|
10 |
Cash Flow after Tax |
208 |
361.6 |
465.92 |
591.104 |
741.3328 |
405.88 |
|
|
11 |
Depreciation |
120 |
120 |
120 |
120 |
120 |
||
|
12 |
Cash Flow after Tax & Depreciation |
328 |
481.6 |
585.92 |
711.104 |
861.3328 |
405.88 |
|
|
13 |
Discounting factor |
1 |
0.881316 |
0.776718 |
0.684534 |
0.603291 |
0.53169003 |
0.53169003 |
|
14 |
Present Value of Cash flows |
-600 |
289.0717 |
374.0674 |
401.0822 |
429.0026 |
457.9620621 |
215.8023493 |
|
15 |
Net Present Value |
1566.988 |
Uptill cash flow after tax and depreciation computation has been detailed above
Post that the computation of present value has been provided here-in-under:
Cash Flow at year 1 *(1/1+ WACC)=328*.881316=289.717
Cash Flow at year 2 *(1/(1+ WACC)^2)=328*.776718=374.067
Cash Flow at year 5 *(1/(1+ WACC)^5)=861.322*.53169=457.962
Further Net Present Value has been computed by using the formula=
Summation of Present value of csh flow form year 1 to terminal Value- Year 0=2166.988-600=$1566.88
Part B(iv)
Yes, the project shall be accepted based on quantitative analysis of the project as the Net Present value of project is $1567.651 at 13.47 % rate of discounting. Further, positive Net Present Value indicates that project is feasible.
PART A (i)
|
Sl No |
Particulars |
Quantity (a) |
Rate (b) |
Amount(C=a*b) |
|
|
1 |
Share |
5000 |
60 |
300000 |
|
|
2 |
10 Year Bond |
150 |
1000 |
150000 |
|
|
3 |
Buy Back Shares |
2500 |
60 |
150000 |
|
|
4 |
Cost of Debt before Tax |
6% |
|||
|
5 |
Earnings Before Interest and Tax |
28000 |
|||
|
6 |
Tax Rate |
NiL |
|||
|
7 |
Profit after Tax |
28000 |
|||
|
8 |
Dividend Per Share |
5.6 |
|||
|
9 |
Dividend received by Maureen |
100 |
5.6 |
560 |
Answer (a(i)) |
The computation has been derived in the following manner
Earnings Before Interest and Tax = $28000
No of Shares=5000
Dividend per share=EBIT/No of shares=28000/5000= $5.6
No of Shares of Maureen=100
Dividend Received by Maureen= 100*$5.6= $560
Part A (ii)
|
Sl No |
Particulars |
Quantity (a) |
Rate (b) |
Amount(C=a*b) |
|
|
1 |
Share |
5000 |
60 |
300000 |
|
|
2 |
10 Year Bond |
150 |
1000 |
150000 |
|
|
3 |
Buy Back Shares |
2500 |
60 |
150000 |
|
|
4 |
Cost of Debt before Tax |
6% |
|||
|
5 |
Earnings Before Interest and Tax |
28000 |
|||
|
6 |
Tax Rate |
NiL |
|||
|
7 |
Profit after Tax |
28000 |
|||
|
8 |
Dividend Per Share |
5.6 |
|||
|
9 |
Dividend received by Maureen |
100 |
5.6 |
560 |
|
|
10 |
Buy Back Shares |
2500 |
60 |
150000 |
Answer (a(ii)) |
The detail of computation has been provided here-in-below:
Amount of debt issued= 150*1000=$150000
Share price=$60
No of share to be bought back=Debt/Share price=150000/60=2500
PART A (iii)
|
Sl No |
Particulars |
Quantity (a) |
Rate (b) |
Amount(C=a*b) |
|
|
1 |
Share |
5000 |
60 |
300000 |
|
|
2 |
10 Year Bond |
150 |
1000 |
150000 |
|
|
3 |
Buy Back Shares |
2500 |
60 |
150000 |
|
|
4 |
Cost of Debt before Tax |
6% |
|||
|
5 |
Earnings Before Interest and Tax |
28000 |
|||
|
6 |
Tax Rate |
NiL |
|||
|
7 |
Profit after Tax |
28000 |
|||
|
8 |
Dividend Per Share |
5.6 |
|||
|
9 |
Dividend received by Maureen |
100 |
5.6 |
560 |
|
|
10 |
Buy Back Shares |
2500 |
60 |
150000 |
|
|
11 |
Earnings Before Interest and Tax |
28000 |
|||
|
12 |
Interest (2*4) |
9000 |
|||
|
13 |
Profit after Tax (11-12) |
19000 |
|||
|
14 |
Dividend per share (13/3) |
7.6 |
|||
|
15 |
Dividend received by Maureen |
100 |
7.6 |
760 |
Answer (a(iii)) |
The computation has been derived in the following manner
Earnings Before Interest and Tax = $28000
Interest= Debt Amount* 6%=150000*60%= $9000
Earning before Tax= EBIT-Interest=28000-9000=$19000
Dividend per share=EBIT/No of shares=19000/2500= $7.6
No of Shares of Maureen=100
Dividend Received by Maureen= 100*$7.6= $760
PART A (iv)
|
Sl No |
Particulars |
Amount |
|
1 |
No of shares under existing Capital Structure |
100 |
|
2 |
Proportion of debt in the capital structure of the company |
50% |
|
3 |
Total Capital of Maureen |
6000 |
|
4 |
Total value of shares to be sold |
3000 |
|
5 |
Total debt to be let out (3-4) |
3000 |
|
6 |
Receipt of dividend (50*7.6) |
380 |
|
7 |
Interest (3000*6%) |
180 |
|
8 |
Total Receipt (6+7) |
560 |
The computation has been derived in the following manner
The proportion of debt in the capital of the company=Debt/ Total capital=1500000/300000=50%
Total Capital of Maureen = Number of Shares* Rate per share=100*60=$6000
Value of share to be sold to incorporate debt= Capital*50%=6000*50%=$3000
Interest on Debt =Debt Amount * Rate of Coupon=3000*6%=$180
Dividend Receipt= No of Shares* Dividend per share=50*7.6=$380
Total Receipt=Interest + dividend= $180+$380=$560
Part (v)
In terms of Modigliani Miller theory, capital structure is irrelevant and the value of the company is dependent on the net operating cash flow of the company. However, under such thesis there is no transaction cost and taxes. Further, if the environment compasses tax then the above proposition does not hold true as the value of the company is increased by the present value of tax benefit. Further, cost of equity of the company shall rise on account of debt in the capital structure in terms of MM Theory leasing to same WACC.
Part B (i,ii &iii)
|
Sl No |
Particulars |
Quantity (a) |
Rate (b) |
Amount(C=a*b) |
|
1 |
Share |
5000 |
60 |
300000 |
|
2 |
10 Year Bond |
150 |
1000 |
150000 |
|
3 |
Buy Back Shares |
2500 |
60 |
150000 |
|
4 |
Cost of Debt before Tax |
6% |
||
|
5 |
Earnings Before Interest and Tax |
28000 |
||
|
6 |
Tax Rate |
20% |
||
|
7 |
Profit after Tax |
22400 |
||
|
8 |
Dividend Per Share |
4.48 |
||
|
9 |
Dividend received by Maureen |
100 |
4.48 |
448 |
|
10 |
Buy Back Shares |
2500 |
60 |
150000 |
|
11 |
Earnings Before Interest and Tax |
28000 |
||
|
12 |
Interest (2*4) |
9000 |
||
|
13 |
Profit after Tax (11-12)*80% |
15200 |
||
|
14 |
Dividend per share (13/3) |
6.08 |
||
|
15 |
Dividend received by Maureen |
100 |
6.08 |
608 |
The computation has been provided here-in-below:
Earnings Before Interest and Tax = $28000
Earning After Tax =EBIT*(1-Tax rate)=28000*(1-20%)= $22400
No of Shares=5000
Dividend per share=EBIT/No of shares=22400/5000= $4.48
No of Shares of Maureen=100
Dividend Received by Maureen= 100*$4.48= $448 (Answer)
Amount of debt issued=$150000
Share price=$60
No of share to be bought back=Debt/Share price=150000/60=2500 (Answer)
Interest= Debt Amount* 6%=150000*60%= $9000
Earning before Tax= EBIT-Interest=28000-9000=$19000
Earning After Tax= EBT*(1-Tax rate)=19000*(1-20%)= $15200
Dividend per share=EBIT/No of shares=15200/2500= $6.08
No of Shares of Maureen=100
Dividend Received by Maureen= 100*$6.08= $608(Answer)
PART B (iv)
|
Sl No |
Particulars |
Amount |
|
1 |
No of shares under existing Capital Structure |
100 |
|
2 |
Proportion of debt in the capital structure of the company |
50% |
|
3 |
Total Capital of Maureen |
6000 |
|
4 |
Total value of shares to be sold |
3000 |
|
5 |
Total debt to be let out |
3000 |
|
6 |
Receipt of dividend (50*6.08) |
304 |
|
7 |
Interest (3000*6%) |
180 |
|
8 |
Total Receipt (6+7) |
484 |
The computation has been provided here-in-below:
The proportion of debt in the capital of the company=Debt/ Total capital=1500000/300000=50%
Total Capital of Maureen = Number of Shares* Rate per share=100*60=$6000
Value of share to be sold to incorporate debt= Capital*50%=6000*50%=$3000
Interest on Debt =Debt Amount * Rate of Coupon=3000*6%=$180
Dividend Receipt= No of Shares* Dividend per share=50*6.06=$304
Total Receipt=Interest + dividend= $180+$304=$484
PART B (v)
In terms of Modigliani Miller theory, capital structure is irrelevant and the value of the company is dependent on the net operating cash flow of the company. However, under such thesis there is no transaction cost and taxes. Further, if the environment compasses tax then the above proposition does not hold true as the value of the company is increased by the present value of tax benefit. Further, cost of equity of the company shall rise on account of debt in the capital structure in terms of MM Theory leasing to same WACC. (Study.com, 2018)
References:
CFI Education Inc. (2018). WACC. Retrieved October 3, 2018, from corporatefinanceinstitute.com: https://corporatefinanceinstitute.com/resources/knowledge/finance/what-is-wacc-formula/
Study.com. (2018). The Modigliani-Miller Theorem: Definition, Formula & Examples. Retrieved October 3, 2018, from Study.com: https://study.com/academy/lesson/the-modigliani-miller-theorem-definition-formula-examples.html