In: Finance
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%.
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.
The paragraph at the top says:
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 assumptions towards the end of the question state:
- 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.
It's not clear which situation is to be dealt with.
I am therefore presenting below, project cash flows as desired in the first paragraph that is " 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."
==================
All financials below are in $ mn. Figures in parenthesis mean negative values.
Let's first get into the annual depreciation calculation. Please see the table below. Please see the second column. That will help you understand how each value has been calculated.
Year |
N |
0 |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
11 |
12 |
Asset Cost |
C |
35 |
||||||||||||
Seven-year MACRS property, depreciation rate |
D |
14.29% |
24.49% |
17.49% |
12.49% |
8.93% |
8.92% |
8.93% |
4.46% |
- |
- |
- |
- |
|
Annual depreciation |
Dep = C x D |
5.00 |
8.57 |
6.12 |
4.37 |
3.13 |
3.12 |
3.13 |
1.56 |
- |
- |
- |
- |
Post tax salvage Value = Pre tax salvage value - tax on gain on sale
Pre tax salvage value = 15% x asset cost = 15% x 35 = 5.25
Tax basis at the end of year 12 = Purchase cost - accumulated depreciation = 35 - 35 = 0
Gain on sale= Sale value - tax basis = 5.25 - 0 = 5.25
Tax rate = 38%
Hence, tax on gain on sale= Tax rate x Gain on sale = 38% x 5.25 = 1.995
Hence, port tax salvage value = 5.25 - 1.995 = 3.255 = 3.26 (rounded off to two places of decimal)
Situation: Unpaid principal ($35M - $3.5M down payment) is paid as lump sum at year 10.
Loan value = 35 - 3.5 = 31.5; interest rate = 9%, Annual interest over first 10 years = Loan value x Interest rate = 31.5 x 9% = 2.835 = 2.84 (rounded off to two places of decimal)
The table below presents the income statement of the entire project associated with the debt financing. Please see the second column. That will help you understand how each value has been calculated. The "[+] or [-]" sign will help you understand whether a particular variable has been added or subtracted to get the value in the next row.
Year |
N |
0 |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
11 |
12 |
Annual Revenue |
R |
40.00 |
40.00 |
40.00 |
40.00 |
40.00 |
40.00 |
40.00 |
40.00 |
40.00 |
40.00 |
40.00 |
40.00 |
|
[-] Annual operating and maintenance cost |
C |
30.00 |
30.00 |
30.00 |
30.00 |
30.00 |
30.00 |
30.00 |
30.00 |
30.00 |
30.00 |
30.00 |
30.00 |
|
[-] Annual depreciation |
D |
5.00 |
8.57 |
6.12 |
4.37 |
3.13 |
3.12 |
3.13 |
1.56 |
- |
- |
- |
- |
|
Operating income |
EBIT = R - C - D |
5.00 |
1.43 |
3.88 |
5.63 |
6.87 |
6.88 |
6.87 |
8.44 |
10.00 |
10.00 |
10.00 |
10.00 |
|
[-] Interest expense |
I |
2.84 |
2.84 |
2.84 |
2.84 |
2.84 |
2.84 |
2.84 |
2.84 |
2.84 |
2.84 |
- |
- |
|
Pre tax income |
EBT = EBIT - I |
2.16 |
(1.41) |
1.04 |
2.79 |
4.04 |
4.04 |
4.04 |
5.60 |
7.17 |
7.17 |
10.00 |
10.00 |
|
[-] Taxes |
EBT x Tax rate of 38% |
0.82 |
(0.53) |
0.40 |
1.06 |
1.54 |
1.54 |
1.54 |
2.13 |
2.72 |
2.72 |
3.80 |
3.80 |
|
Net Income |
EBT - Taxes |
1.34 |
(0.87) |
0.65 |
1.73 |
2.50 |
2.51 |
2.50 |
3.47 |
4.44 |
4.44 |
6.20 |
6.20 |
The statement of cash flow will be as shown below:
Year |
N |
0 |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
8 |
9 |
10 |
11 |
12 |
Operating cash flow |
||||||||||||||
Operating income |
EBIT |
5.00 |
1.43 |
3.88 |
5.63 |
6.87 |
6.88 |
6.87 |
8.44 |
10.00 |
10.00 |
10.00 |
10.00 |
|
[-] Taxes |
Taxes |
0.82 |
(0.53) |
0.40 |
1.06 |
1.54 |
1.54 |
1.54 |
2.13 |
2.72 |
2.72 |
3.80 |
3.80 |
|
[+] Depreciation |
D |
5.00 |
8.57 |
6.12 |
4.37 |
3.13 |
3.12 |
3.13 |
1.56 |
- |
- |
- |
- |
|
Operating Cash flow |
CFO = EBIT - Taxes + D |
- |
9.18 |
10.53 |
9.60 |
8.94 |
8.46 |
8.46 |
8.46 |
7.87 |
7.28 |
7.28 |
6.20 |
6.20 |
[-] Capital expenditure |
C |
35.00 |
||||||||||||
[+] Post tax salvage value |
S |
3.26 |
||||||||||||
Cash flow from investing |
CFI |
(35.00) |
||||||||||||
[-] Interest paid |
I |
2.84 |
2.84 |
2.84 |
2.84 |
2.84 |
2.84 |
2.84 |
2.84 |
2.84 |
2.84 |
- |
- |
|
[+] Net borrowing |
B |
31.50 |
- |
- |
- |
- |
- |
- |
- |
- |
(31.50) |
- |
- |
|
[+] Own funds |
3.50 |
|||||||||||||
Cash flow from financing |
CFF = - I + B |
35.00 |
(2.84) |
(2.84) |
(2.84) |
(2.84) |
(2.84) |
(2.84) |
(2.84) |
(2.84) |
(2.84) |
(34.34) |
- |
- |
Net cash flow |
CFO + CFI + CFF |
- |
6.34 |
7.70 |
6.77 |
6.10 |
5.63 |
5.63 |
5.63 |
5.04 |
4.44 |
(27.06) |
6.20 |
6.20 |
Cumulative cash flows |
- |
6.34 |
14.04 |
20.81 |
26.91 |
32.54 |
38.17 |
43.80 |
48.84 |
53.28 |
26.22 |
32.42 |
38.62 |