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!!
jeff jeffy answered 1 year agoRelated 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
Latest Questions
- In order to manage change and implement change strategies, it is important to avoid implementing irrelevant...
- Smoky Mountain Corporation makes two types of hiking boots—Xtreme and the Pathfinder. Data concerning these two...
- Drug delivery systems are often ‘pelletized’ as spheres. Construct the control volume. Determine the continuity equation...
- NIST cybersecurity framework. Summarize the purpose, approach, goals, and scope of this topic publication.( cite references)
- Site for the significance of vitamins in the body
- 1) What are a few (name at least three) important values in Japanese culture? 2) What...
- S&P 500 Index is at 2780. A European June 21, 2019 SPX call option struck at...
ADVERTISEMENT