Question

In: Finance

The company would like to buy a machine for 20 mil. USD. Machine would be depreciated...

The company would like to buy a machine for 20 mil. USD. Machine would be depreciated for 3 years using 3-years MACRS method. Company has following options:

Loan: maturity 3 years, monthly payment, interest 6 % p.a., equal annuity payment
Leasing: leasing coefficient 1.3; advanced payment 30 %; maturity 3 years; monthly payment Corporate tax rate is 19 %.

Which type of financing is better for us? PS: please use Excel to calculate it and share the formula please.

Solutions

Expert Solution

Please find below payment schedule for Loan:-

Month Amount Interest PMT Principal Net Principal Remain
1 $2,00,00,000.00 $1,00,000.00 $6,08,438.75 $5,08,438.75 $1,94,91,561.25
2 $1,94,91,561.25 $97,457.81 $6,08,438.75 $5,10,980.94 $1,89,80,580.31
3 $1,89,80,580.31 $94,902.90 $6,08,438.75 $5,13,535.85 $1,84,67,044.46
4 $1,84,67,044.46 $92,335.22 $6,08,438.75 $5,16,103.53 $1,79,50,940.93
5 $1,79,50,940.93 $89,754.70 $6,08,438.75 $5,18,684.05 $1,74,32,256.88
6 $1,74,32,256.88 $87,161.28 $6,08,438.75 $5,21,277.47 $1,69,10,979.42
7 $1,69,10,979.42 $84,554.90 $6,08,438.75 $5,23,883.85 $1,63,87,095.57
8 $1,63,87,095.57 $81,935.48 $6,08,438.75 $5,26,503.27 $1,58,60,592.29
9 $1,58,60,592.29 $79,302.96 $6,08,438.75 $5,29,135.79 $1,53,31,456.51
10 $1,53,31,456.51 $76,657.28 $6,08,438.75 $5,31,781.47 $1,47,99,675.04
11 $1,47,99,675.04 $73,998.38 $6,08,438.75 $5,34,440.37 $1,42,65,234.66
12 $1,42,65,234.66 $71,326.17 $6,08,438.75 $5,37,112.58 $1,37,28,122.09
13 $1,37,28,122.09 $68,640.61 $6,08,438.75 $5,39,798.14 $1,31,88,323.95
14 $1,31,88,323.95 $65,941.62 $6,08,438.75 $5,42,497.13 $1,26,45,826.82
15 $1,26,45,826.82 $63,229.13 $6,08,438.75 $5,45,209.62 $1,21,00,617.20
16 $1,21,00,617.20 $60,503.09 $6,08,438.75 $5,47,935.66 $1,15,52,681.54
17 $1,15,52,681.54 $57,763.41 $6,08,438.75 $5,50,675.34 $1,10,02,006.19
18 $1,10,02,006.19 $55,010.03 $6,08,438.75 $5,53,428.72 $1,04,48,577.48
19 $1,04,48,577.48 $52,242.89 $6,08,438.75 $5,56,195.86 $98,92,381.61
20 $98,92,381.61 $49,461.91 $6,08,438.75 $5,58,976.84 $93,33,404.77
21 $93,33,404.77 $46,667.02 $6,08,438.75 $5,61,771.73 $87,71,633.04
22 $87,71,633.04 $43,858.17 $6,08,438.75 $5,64,580.58 $82,07,052.46
23 $82,07,052.46 $41,035.26 $6,08,438.75 $5,67,403.49 $76,39,648.97
24 $76,39,648.97 $38,198.24 $6,08,438.75 $5,70,240.51 $70,69,408.47
25 $70,69,408.47 $35,347.04 $6,08,438.75 $5,73,091.71 $64,96,316.76
26 $64,96,316.76 $32,481.58 $6,08,438.75 $5,75,957.17 $59,20,359.59
27 $59,20,359.59 $29,601.80 $6,08,438.75 $5,78,836.95 $53,41,522.64
28 $53,41,522.64 $26,707.61 $6,08,438.75 $5,81,731.14 $47,59,791.50
29 $47,59,791.50 $23,798.96 $6,08,438.75 $5,84,639.79 $41,75,151.71
30 $41,75,151.71 $20,875.76 $6,08,438.75 $5,87,562.99 $35,87,588.72
31 $35,87,588.72 $17,937.94 $6,08,438.75 $5,90,500.81 $29,97,087.91
32 $29,97,087.91 $14,985.44 $6,08,438.75 $5,93,453.31 $24,03,634.60
33 $24,03,634.60 $12,018.17 $6,08,438.75 $5,96,420.58 $18,07,214.03
34 $18,07,214.03 $9,036.07 $6,08,438.75 $5,99,402.68 $12,07,811.35
35 $12,07,811.35 $6,039.06 $6,08,438.75 $6,02,399.69 $6,05,411.65
36 $6,05,411.65 $3,027.06 $6,08,438.75 $6,05,411.69 -$0.04

In above table for each month interest, per month payment, principal amount for each month and remaining principal for each month is given.

For creating above table in excel, please use below formula's;-

or interest = Respective cell in amount column * 0.06 / 12 (copy this in whole interest column)

for PMT = (Rate(0.06/12), nper(36), pv(-20000000), fv(0), type(0))

For principal = PMT - Interest

Net Principal = Amount - Principal

and next amount will be the previous month net principal amount

Likewise please also find the table for payment of lease.

Please note that this table has been created with 10% interest rate (assumed) and $14000000 (after advanced payment of 6 miliion).

Month Amount Interest PMT Principal Net Principal Remain
1 $1,40,00,000.00 $1,16,666.67 $4,51,740.62 $3,35,073.95 $1,36,64,926.05
2 $1,36,64,926.05 $1,13,874.38 $4,51,740.62 $3,37,866.24 $1,33,27,059.81
3 $1,33,27,059.81 $1,11,058.83 $4,51,740.62 $3,40,681.79 $1,29,86,378.02
4 $1,29,86,378.02 $1,08,219.82 $4,51,740.62 $3,43,520.80 $1,26,42,857.22
5 $1,26,42,857.22 $1,05,357.14 $4,51,740.62 $3,46,383.48 $1,22,96,473.74
6 $1,22,96,473.74 $1,02,470.61 $4,51,740.62 $3,49,270.01 $1,19,47,203.73
7 $1,19,47,203.73 $99,560.03 $4,51,740.62 $3,52,180.59 $1,15,95,023.14
8 $1,15,95,023.14 $96,625.19 $4,51,740.62 $3,55,115.43 $1,12,39,907.72
9 $1,12,39,907.72 $93,665.90 $4,51,740.62 $3,58,074.72 $1,08,81,832.99
10 $1,08,81,832.99 $90,681.94 $4,51,740.62 $3,61,058.68 $1,05,20,774.31
11 $1,05,20,774.31 $87,673.12 $4,51,740.62 $3,64,067.50 $1,01,56,706.81
12 $1,01,56,706.81 $84,639.22 $4,51,740.62 $3,67,101.40 $97,89,605.41
13 $97,89,605.41 $81,580.05 $4,51,740.62 $3,70,160.58 $94,19,444.84
14 $94,19,444.84 $78,495.37 $4,51,740.62 $3,73,245.25 $90,46,199.59
15 $90,46,199.59 $75,385.00 $4,51,740.62 $3,76,355.62 $86,69,843.97
16 $86,69,843.97 $72,248.70 $4,51,740.62 $3,79,491.92 $82,90,352.05
17 $82,90,352.05 $69,086.27 $4,51,740.62 $3,82,654.35 $79,07,697.69
18 $79,07,697.69 $65,897.48 $4,51,740.62 $3,85,843.14 $75,21,854.55
19 $75,21,854.55 $62,682.12 $4,51,740.62 $3,89,058.50 $71,32,796.05
20 $71,32,796.05 $59,439.97 $4,51,740.62 $3,92,300.65 $67,40,495.40
21 $67,40,495.40 $56,170.79 $4,51,740.62 $3,95,569.83 $63,44,925.57
22 $63,44,925.57 $52,874.38 $4,51,740.62 $3,98,866.24 $59,46,059.33
23 $59,46,059.33 $49,550.49 $4,51,740.62 $4,02,190.13 $55,43,869.21
24 $55,43,869.21 $46,198.91 $4,51,740.62 $4,05,541.71 $51,38,327.50
25 $51,38,327.50 $42,819.40 $4,51,740.62 $4,08,921.22 $47,29,406.27
26 $47,29,406.27 $39,411.72 $4,51,740.62 $4,12,328.90 $43,17,077.37
27 $43,17,077.37 $35,975.64 $4,51,740.62 $4,15,764.98 $39,01,312.39
28 $39,01,312.39 $32,510.94 $4,51,740.62 $4,19,229.68 $34,82,082.71
29 $34,82,082.71 $29,017.36 $4,51,740.62 $4,22,723.26 $30,59,359.44
30 $30,59,359.44 $25,494.66 $4,51,740.62 $4,26,245.96 $26,33,113.49
31 $26,33,113.49 $21,942.61 $4,51,740.62 $4,29,798.01 $22,03,315.48
32 $22,03,315.48 $18,360.96 $4,51,740.62 $4,33,379.66 $17,69,935.82
33 $17,69,935.82 $14,749.47 $4,51,740.62 $4,36,991.16 $13,32,944.66
34 $13,32,944.66 $11,107.87 $4,51,740.62 $4,40,632.75 $8,92,311.92
35 $8,92,311.92 $7,435.93 $4,51,740.62 $4,44,304.69 $4,48,007.23
36 $4,48,007.23 $3,733.39 $4,51,740.62 $4,48,007.23 $0.00

Now we know that machine is depriciated for 3 years using MACRS method so we will calculate each year depriciation amount.

Please refer below table:-

Year Depriciation % Depriciation amount (mil)
0
1 0.33 $6.60
2 0.4445 $8.89
3 0.1481 $2.96

In above table depriciation percentage is the standard for 3 year depriciation schedule.

Now

We will create a hypothetical income statement and cash flow from operation associated so that we could check NPV for the cases and accordingly come to a conclusion for a better financing option.

Please refer below table created for Loan:;-

Year 0 1 2 3
EBITDA $2,00,00,000.00 $2,00,00,000.00 $2,00,00,000.00
Depriciation $66,00,000.00 $88,90,000.00 $29,62,000.00
Interest Expense $10,29,387.09 $6,42,551.38 $2,31,856.49
PBT $1,23,70,612.91 $1,04,67,448.62 $1,68,06,143.51
Tax $23,50,416.45 $19,88,815.24 $31,93,167.27
Net Income $1,00,20,196.46 $84,78,633.38 $1,36,12,976.24
CF With loan $1,66,20,196.46 $1,73,68,633.38 $1,65,74,976.24
NPV at 10% $4,19,16,536.95

In above table we have assumed EBITDA (20 Mil USD).

Above table in excel can be created with below formula

- Depriciation as from depriciation calculation table for respective year

- Interest Expanse is taken form first table. Sum of First 12 month interest than sum of month interest from 13 to 24 than 25 to 36.

PBT (Profit before tax) = EBITDA - Depriciation - Interest Expanse.

Tax = PBT * 0.19

Net Income = PBT - Tax

CF with loan = Cash flow = Net income adusted for depriciation = Net income + depriciation

Now we have calculated NPV at 10% discount rate from below excel formula:-

=NPV(rate(0.1), year 1 cf, year 2 cf, year 3 cf)

Note:- NPV calculated is hypothetical only here, we have calculated just for comparison purpose.

Like wise find the below table for lease;-

Year 0 1 2 3
Initial Payment -$60,00,000.00
EBITDA $2,00,00,000.00 $2,00,00,000.00 $2,00,00,000.00
Depriciation $66,00,000.00 $88,90,000.00 $29,62,000.00
Interest Expense $12,10,492.86 $7,69,609.53 $2,82,559.95
Tax $23,16,006.36 $19,64,674.19 $31,83,533.61
Net Income $98,73,500.78 $83,75,716.28 $1,35,71,906.44
CF With lease -$60,00,000.00 $1,64,73,500.78 $1,72,65,716.28 $1,65,33,906.44
NPV at 10% $3,56,67,265.43

Here the same formula's has been used which are explained for loan table, although please note below formula for calculation of NPV

Here NPV formula in excel will be -

= cell of -$6000000+NPV(rate(0.1), year 1 cf, year 2 cf, year 3 cf) (NPV in excel considers first value as after first year)

We can also see that during the leasing we paid 30% as an advanced payment which has decreased our NPV.

So from above it is clear than loan is an good financing option out of these 2.

Thank You!!


Related Solutions

A US manufacturing company has reached an EBIT of USD 500 mil. and paid USD 170...
A US manufacturing company has reached an EBIT of USD 500 mil. and paid USD 170 mil. in taxes last year. The Debt/Total Capital ration has been 25% (debt in the form of corporate bonds). The company is targeting a constant D/Capital ratio in the future. US 30-year T-Bond's current rate of return is 3%, the average excess return of the US stock market over the last 50 years is 5%. The beta of assets for the firm is 1.1...
The Sweetwater Candy Company would like to buy a new machine that would automatically “dip” chocolates....
The Sweetwater Candy Company would like to buy a new machine that would automatically “dip” chocolates. The dipping operation currently is done largely by hand. The machine the company is considering costs $150,000. The manufacturer estimates that the machine would be usable for five years but would require the replacement of several key parts at the end of the third year. These parts would cost $9,600, including installation. After five years, the machine could be sold for $7,000. The company...
The Sweetwater Candy Company would like to buy a new machine that would automatically “dip” chocolates....
The Sweetwater Candy Company would like to buy a new machine that would automatically “dip” chocolates. The dipping operation currently is done largely by hand. The machine the company is considering costs $180,000. The manufacturer estimates that the machine would be usable for five years but would require the replacement of several key parts at the end of the third year. These parts would cost $9,900, including installation. After five years, the machine could be sold for $5,000. The company...
The Sweetwater Candy Company would like to buy a new machine that would automatically “dip” chocolates....
The Sweetwater Candy Company would like to buy a new machine that would automatically “dip” chocolates. The dipping operation currently is done largely by hand. The machine the company is considering costs $120,000. The manufacturer estimates that the machine would be usable for five years but would require the replacement of several key parts at the end of the third year. These parts would cost $9,300, including installation. After five years, the machine could be sold for $4,000. The company...
The Sweetwater Candy Company would like to buy a new machine that would automatically “dip” chocolates....
The Sweetwater Candy Company would like to buy a new machine that would automatically “dip” chocolates. The dipping operation is currently done largely by hand. The machine the company is considering costs $200,000. The manufacturer estimates that the machine would be usable for five years but would require the replacement of several key parts at the end of the third year. These parts would cost $10,100, including installation. After five years, the machine could be sold for $9,000.       The company...
The Sweetwater Candy Company would like to buy a new machine that would automatically “dip” chocolates....
The Sweetwater Candy Company would like to buy a new machine that would automatically “dip” chocolates. The dipping operation currently is done largely by hand. The machine the company is considering costs $150,000. The manufacturer estimates that the machine would be usable for five years but would require the replacement of several key parts at the end of the third year. These parts would cost $9,600, including installation. After five years, the machine could be sold for $7,000. The company...
The Sweetwater Candy Company would like to buy a new machine that would automatically “dip” chocolates....
The Sweetwater Candy Company would like to buy a new machine that would automatically “dip” chocolates. The dipping operation currently is done largely by hand. The machine the company is considering costs $260,000. The manufacturer estimates that the machine would be usable for five years but would require the replacement of several key parts at the end of the third year. These parts would cost $10,700, including installation. After five years, the machine could be sold for $10,000. The company...
Doughboy Bakery would like to buy a new machine for putting icing and other toppings on...
Doughboy Bakery would like to buy a new machine for putting icing and other toppings on pastries. These are now put on by hand. The machine that the bakery is considering costs $80,000 new. It would last the bakery for ten years but would require a $6,000 overhaul at the end of the seventh year. After ten years, the machine could be sold for $5,000. The bakery estimates that it will cost $10,000 per year to operate the new machine....
Ponpon produces cans of jelly. The company would like to purchase a canning machine. The machine...
Ponpon produces cans of jelly. The company would like to purchase a canning machine. The machine costs $25,000 and the company needs a loan to make the purchase. Before agreeing to the loan, their bank requires Ponpon to provide both current (2020) and budgeted (3 months in 2021) financial statements. Use the following information from Ponpon to provide the bankers with the 2021 budgeted financial states. Balance Sheet Cash                                        $50,000    Accounts Receivable               $31,000    Inventory                                $12,000 Fixed Assets                            $37,000 Total Assets                            $130,000 Accounts Payable                   $22,500...
Ponpon produces cans of jelly. The company would like to purchase a canning machine. The machine...
Ponpon produces cans of jelly. The company would like to purchase a canning machine. The machine costs $25,000 and the company needs a loan to make the purchase. Before agreeing to the loan, their bank requires Ponpon to provide both current (2020) and budgeted (3 months in 2021) financial statements. Use the following information from Ponpon to provide the bankers with the 2021 budgeted financial states. Balance Sheet Cash                                        $50,000    Accounts Receivable               $31,000    Inventory                                $12,000 Fixed Assets                            $37,000 Total Assets                            $130,000 Accounts Payable                   $22,500...
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT