Loan Calculation And Project Appraisal: A Financial Analysis

Part A: Loan Calculation

he solution to part A (i and ii) is presented here-in-below-

Sl No	Particulars	Amount ($)
1 Save Time On Research and Writing Hire a Pro to Write You a 100% Plagiarism-Free Paper. Get My Paper	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 (A(ii))	509378.61
8	Computation of interest at the beginning of 25th Year (A (i))	705.62

Loan Amount has been provided at $800000

Monthly Emi has been computed by PMT(rate, nper,pv,fv)= PMT(0.036/12,360(12*30),-800000,0)= $3637.16.

Total EMI = Monthly EMI* 360=$3637.16*360= $1309378.61

Interest paid over the loan tenure= Total EMI- Loan Amount=1309378.61-800000= $5,09,378.61

Interest at the beginning of 25^th Year has been computed by using IPMT(Rate, Per, Nper, PV, FV)= IPMT(0.036/12,289,360,-800000)=$705.62

Part B

On the basis of above table it may be seen that  the principal of loan amount stands at $ 8,00,000 and interest component on same over the period of loan stands at $5,09,378. The said amount being high on account of long tenure of the loan and stands at 63% of the loan amount. Further, in computation it has been assumed that interest is repaid first on loan and then principal. Further while observing amortisation schedule refer in Appendix-1 it can be seen that interest amount has been decreasing over the tenure of the loan on account of repayment of loan.  

The above computation has been carried out on the basis of the following assumptions:

• The loan has not been paid off before maturity;
• The loan set off intention is not presented before maturity;
• Interest rate remains constant over the tenure of the loan;
• Loan has not been defaulted.

Part C

The computation of Present value of loan has been detailed in Appendix. Further, the rate of discounting used is1.2 % compounded monthly. Accordingly, the present value stands at $ 1,09,9143.923. (Refer Appendix-1)

The present value has been computed by using the following formula:

EMI/ (1+Rate of Interest/ 12)^time *12

Present Value of 1^st EMI =3637.16/(1+.012/12)^1

………………..

Present Value of last EMI =3637.16/(1+.012/12)^360

Post that all the present valued have been added to derive the value of $ 1,09,9143.923.

Part D

Referring to Appendix1 below, it can be inferred that present value of loan computed by discounting @1.2% stands greater than the principal amount set out earlier by the lender. The computation has been based on time value of money. The difference above has been mainly on account of difference in rate of interest used for discounting the amount of Equated Monthly Instalment. Besides, the rate of discounting used in the project represent risk free interest rate as saving bank interest rate tantamount to risk free interest.

Further, the difference amount between the actual amount of loan and the present value as computed in Appendix is $ 2,99,143.9229. The meaning of above is derived from the fact that I am paying extra to lender on account of extra risk the lender by giving loan to me and the difference stands as reward to the lender on account of risk borne by him . The reward amount stands at $ 2,99,143.9229

Thus, the difference in short is on account of excess risk borne by lender.

Part A (i)

The Solution to Part A(i) is presented here-in-under:

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

The basis of above computation has been detailed here-in-below:

• Debt Cost = 8%

• Debt Cost net of tax = 8%*(1-tax rate)=8%(1-20%)=6.4%

• R_f=5%

• R_m=15%

• R_m-R_f= Market Return- Risk Free Rate=15%-5%=10%

• Beta of Bad Inc= 1.2

Beta Inc. is engaged in similar business as the concerned company. Hence, Beta of the same has been considered for the purpose of anlaysis while the other company is conglomerate engage in wide spread business, hence not used.

Beta (A)= Beta (E) /(1+ (1-tax)*D/E)

Part B: Interpretation of Loan Calculation Results

=1.2(1+(1+(1-0.2)*2/4)

=0.857

Beta of the Concerned Company 

Beta (E) = Beta (A) *(1+ (1-tax)*D/E)

=0.857*(1+(1-0.2)*3/6)

• Equity Cost = R_f+ R_m * Beta (E)

=5%+ 10% *1.2

• WACC = (debt Cost* debt weight + equity cost* Equity weight)/ total weight

=(6.4%*3+ 17%*6)/9

=13.47%

Part A (ii)

The appropriate discount rate shall be weighted average cost of the capital as the same shall remain consistent irrespective of capital structure of the company (assuming MM proposition 1 holds good). Further, the WACC implies suitable discount required by the person lending money for the required project. Thus, it represents the minimum requires rate of return by the investors of the project. Hence, the same is a hurdle rate below which the project cannot be carried out. Accordingly rate of 13.47% is considered appropriate in the concerned case. (CFI Education Inc., 2018)

Part B (I)

The computation of cash flows over next five years of the project along with changes in working capital has been described here-in-under:

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	Variable 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

The basis of above computation has been detailed here-in-below:

• Revenue Year 1= 800

Year 2= Year 1*1.2=800*1.2=960

Year 3= Year 2*1.2=960*1,2=1152

Year 5=Year 4*1.2=1382.4*1.2=1658.88

• Variable cost

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  Year 1 to 5 =$80 for every year
• Depreciation Year 1 to 5 = 600/5 = $ 120 every year600/5=120 every year
• EBIT=Revenue-variable cost-fixed cost-depreciation

For year 1

= 800-240-80-120=$360

Similarly for other years.

• Tax = EBIT*30%

For year 1

= $360*20%= $72

Similarly for other years.

• 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 basis of above computation has been detailed here-in-below:

• Net Working Capital Level

 Year 1 = Revenue for (Year 1)*10%=800*10%=$80

Year 5= Year 5 Revenur *10%=1658.88*10%=$165.89

• Change in Working Capital level

Year 1 = Year 1 Working Capital Level – Year 0 Working Capital level =80-0=$80

Year 2 = Year 2 Working Capital Level – Year 1 Working Capital level =90-80=$16

Year 5 = Year 5 Working Capital Level – Year 4 Working Capital level =165.88-138.24=$27.648

• Net Cash flow from Asset Disposal: Disposal/ Scrap Value *(1-Tax Rate )

=300*(1-20%) =$240

• Working Capital Realisation after 5 years = Net Working Capital (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

The basis of above computation of Present Value has been detailed here-in-below:

Present Value Year 1=Cash Flow (Year 1) *(1/1+ WACC)=328*.881316=289.717

Present Value Year 2=Cash Flow at (Year 2)*(1/(1+ WACC)^2)=328*.776718=374.067

Present Value Year 5=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=

Sigma of Present Value of Cash inflow from Year 1 to terminal year as reduced by initial outlay=2166.988-600=$1566.88

Part B(iv)

Referring to the above table, it can be seen that the Net present Value of the project is positive and hence the same shall be accepted based on the same. Further, it shall be noted that it is just a quantitative measure, qualitative measure should also be harped upon before accepting the project.

PART A (i)

Sl No	Particulars	Quantity	Rate	Amount
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 basis of above computation has been detailed here-in-below:

EBIT = $28000

No of Shares=5000

Earnings per share=EBIT/No of shares=28000/5000= $5.6= Dividend Per Share

No of Shares of Maureen=100

Receipt of Dividend by Maureen= 100*$5.6= $560

PART A (ii)

Sl No	Particulars	Quantity	Rate	Amount
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 basis of above computation has been detailed here-in-below:

Debt issued= No of instrument* issue price=150*1000=$150000

Share price at Market=$60

No of share to be bought back=Debt/Share price=150000/60=2500

PART A (iii)

Sl No	Particulars	Quantity	Rate	Amount
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			9000
13	Profit after Tax			19000
14	Dividend per share			7.6
15	Dividend received by Maureen	100	7.6	760	Answer (a(iii))

The basis of above computation has been detailed here-in-below:

EBIT = $28000

Interest= Bond Face Value* 6%=150000*60%= $9000

Part C: Project Appraisal using Present Value Calculation

EBT = EBIT-Interest=28000-9000=$19000

Earnings Per Share =EBIT/No of shares=19000/2500= $7.6=Dividend per share

No of Shares of Maureen=100

Receipt of Dividend 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	3000
6	Receipt of dividend	380
7	Interest	180
8	Total Receipt	560

The basis of above computation has been detailed here-in-below:

Debt proportion in company Capital=Debt/ Total capital=1500000/300000=50%

Maureen Capital = Number of Shares* Rate per share=100*60=$6000

Amount of share to be disposed = Capital*50%=6000*50%=$3000

Interest on Debt =Debt Amount * Rate of Coupon=3000*6%=$180

Dividend = No of Shares* Dividend per share=50*7.6=$380

Total Receipt=Interest + dividend= $180+$380=$560

PART A (v)

According to Modigliani Miller theory proposition one, capital structure of the company is not relevant in determining the value of the company. In reality what actually matters is the net operating cash flow of the company on account of weighted average cost of capital of the company remaining constant. Further, it shall be noted that increase in debt does not lead to a corresponding decrease in cost of equity on account of financial distress and higher return desired by equity shareholders for extra risk. However, the proposition is based on two assumptions which do not hold good in the practical world. The proposition are that there are no transaction cost to finance and there is no tax.

PART B (i,ii &iii)

Sl No	Particulars	Quantity	Rate	Amount
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			9000
13	Profit after Tax			15200
14	Dividend per share			6.08
15	Dividend received by Maureen	100	6.08	608

The basis of above computation has been detailed here-in-below:

EBIT = $28000

PAT =EBIT*(1-Tax rate)=28000*(1-20%)= $22400

No of Shares=5000

Earnings per share=EBIT/No of shares=22400/5000= $4.48= Dividend Per Share

No of Shares of Maureen=100

Receipt of Dividend by Maureen= 100*$4.48= $448 (Answer)

Debt issued= No of instrument* issue price=150*1000=$150000

Share price at Market=$60

No of share to be bought back=Debt/Share price=150000/60=2500 (Answer)

Interest= Bond Face Value* 6%=150000*60%= $9000

EBT = EBIT-Interest=28000-9000=$19000

PAT =EBIT*(1-Tax rate)=19000*(1-20%)= $15200

Earnings Per Share =EBIT/No of shares=15200/2500= $6.08=Dividend per share

No of Shares of Maureen=100

Receipt of Dividend 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	304
7	Interest	180
8	Total Receipt	484

The basis of above computation has been detailed here-in-below:

Debt proportion in company Capital=Debt/ Total capital=1500000/300000=50%

Maureen Capital = Number of Shares* Rate per share=100*60=$6000

Amount of share to be disposed = Capital*50%=6000*50%=$3000

Interest on Debt =Debt Amount * Rate of Coupon=3000*6%=$180

Dividend = No of Shares* Dividend per share=50*6.06=$304

Total Receipt=Interest + dividend= $180+$304=$484

PART B (v)

According to Modigliani Miller theory proposition one, capital structure of the company is not relevant in determining the value of the company. In reality what actually matters is the net operating cash flow of the company on account of weighted average cost of capital of the company remaining constant. Further, it shall be noted that increase in debt does not lead to a corresponding decrease in cost of equity on account of financial distress and higher return desired by equity shareholders for extra risk.

However, the proposition is based on two assumptions which do not hold good in the practical world. The proposition are that there are no transaction cost to finance and there is no tax. In the present scenario, the same does not hold good and accordingly reference is made to second proposition of MM where in it has been stated that value of company shall be increased by the present value of tax benefit on account of interest. Accordingly, the value of company is maximum at 100% debt.

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