Question

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.

Answer 1

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