In: Accounting
An airline is considering purchasing a new Boeing aircraft that
is quoted at
$35 million per unit. Boeing requires a 10% down payment paid at
the time of delivery,
and the balance is to be paid over a 10-year period at an interest
rate of 9% compounded
annually (see the hints below for better explanation). The actual
payment schedule calls
for making only interest payments over the 10-year period, with the
original principal
amount to be paid off at the end of the 10th year. The expected
annual revenue is $40
million, while the annual operating and maintenance cost is $30
million. The aircraft is
expected to have a 15-year service life with a salvage value of 15%
of the original
purchase price, and will be depreciated by the seven-year MACRS
property
classification. The firm’s combined federal and state marginal tax
rate is 38%. The
MARR is 18%.
(a.) Determine the cash flow of the entire project associated with
the debt
financing. Use excel spreadsheet and present your calculation using
the cash
flow table
- Assume all values are in today’s money (do not inflate).
- 10% down payment is paid at year 0 (10% of the $35M)
- 9% of the unpaid principal ($35M - $3.5M down payment) is paid
every year
to Boeing from year 1 to year 10.
- Unpaid principal ($35M - $3.5M down payment) is paid as lump sum
at year
10.
Depreciation | Purchase price(a) | Rate(b) | Depreciation(c= a*b) | Tax rate(d) | Tax benefit(c*d) |
1 | 35 | 14.29% | 5.0015 | 38% | 1.90057 |
2 | 35 | 24.49% | 8.5715 | 38% | 3.25717 |
3 | 35 | 17.49% | 6.1215 | 38% | 2.32617 |
4 | 35 | 12.49% | 4.3715 | 38% | 1.66117 |
5 | 35 | 8.93% | 3.1255 | 38% | 1.18769 |
6 | 35 | 8.92% | 3.122 | 38% | 1.18636 |
7 | 35 | 8.93% | 3.1255 | 38% | 1.18769 |
8 | 35 | 4.46% | 1.561 | 38% | 0.59318 |
Particulars | Year(in million) | ||||||||||
0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | |
Initial down payment(a) | -3.5 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | -31.5 |
Annual revenue(b) | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | 40 | |
Operating expenses(c) | -30 | -30 | -30 | -30 | -30 | -30 | -30 | -30 | -30 | -30 | |
Depreciation benefit(see table)(d) | 1.90057 | 3.25717 | 2.32617 | 1.66117 | 1.18769 | 1.18636 | 1.18769 | 0.59318 | 0 | 0 | |
FV at compound interest @ 9%(e) using FV function in excel | ₹ -34.34 | ₹ -37.43 | ₹ -40.79 | ₹ -44.46 | ₹ -48.47 | ₹ -52.83 | ₹ -57.58 | ₹ -62.77 | ₹ -68.41 | ₹ -74.57 | |
Interest expense(f) | ₹ -2.84 | ₹ -3.09 | ₹ -3.37 | ₹ -3.67 | ₹ -4.00 | ₹ -4.36 | ₹ -4.75 | ₹ -5.18 | ₹ -5.65 | ₹ -6.16 | |
Tax benefit at 38%(g) | ₹ 1.08 | ₹ 1.17 | ₹ 1.28 | ₹ 1.40 | ₹ 1.52 | ₹ 1.66 | ₹ 1.81 | ₹ 1.97 | ₹ 2.15 | ₹ 2.34 | |
Net cash flow(a+b-c+d-f+g) | ₹ -3.50 | ₹ 10.14 | ₹ 11.34 | ₹ 10.24 | ₹ 9.38 | ₹ 8.71 | ₹ 8.48 | ₹ 8.24 | ₹ 7.38 | ₹ 6.50 | ₹ -25.32 |
Discount factor at 18% | 1 | 0.847458 | 0.718184 | 0.608631 | 0.515789 | 0.437109 | 0.370432 | 0.313925 | 0.266038 | 0.225456 | 0.191064 |
Present value of cash flow(discount factor * net cash flow) | ₹ -3.50 | ₹ 8.60 | ₹ 8.15 | ₹ 6.23 | ₹ 4.84 | ₹ 3.81 | ₹ 3.14 | ₹ 2.59 | ₹ 1.96 | ₹ 1.46 | ₹ -4.84 |
FV function formula used is FV= FV(rate,Period,payment,PV,Type) using excel the values have been derived.
Interest expense | (in million) | ||
Year | FV(a) | Interest | Calculation |
0 | 31.5 | ||
1 | 34.34 | 2.84 | 34.34-31.5 |
2 | 37.43 | 3.09 | 37.43-34.34 |
3 | 40.79 | 3.36 | 40.79-37.43 |
4 | 44.46 | 3.67 | |
5 | 48.47 | 4.01 | |
6 | 52.83 | 4.36 | |
7 | 57.58 | 4.75 | |
8 | 62.77 | 5.19 | |
9 | 68.41 | 5.64 | |
10 | 74.57 | 6.16 |