In: Economics
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.
MARR = 18% , Tax = 38%, Interest on loan = 9%
Salvage value = 15% of original value
MACRS rate used
Year | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |
Rate | 0.1429 | 0.2449 | 0.1749 | 0.1249 | 0.0893 | 0.0892 | 0.0893 | 0.0446 |
using excel
Year | Initial cost | Loan payment | Annual revenue | Annual O&M cost | Depreciation | Salvage value | Taxable Income | Tax | ATCF | Discount factor | Present value |
0 | -3500000 | -3500000 | 1.00000 | -3500000.00 | |||||||
1 | -2835000 | 40000000 | -30000000 | 5001500 | 2163500 | 822130 | 6342870 | 0.84746 | 5375313.56 | ||
2 | -2835000 | 40000000 | -30000000 | 8571500 | -1406500 | -534470 | 7699470 | 0.71818 | 5529639.47 | ||
3 | -2835000 | 40000000 | -30000000 | 6121500 | 1043500 | 396530 | 6768470 | 0.60863 | 4119499.80 | ||
4 | -2835000 | 40000000 | -30000000 | 4371500 | 2793500 | 1061530 | 6103470 | 0.51579 | 3148101.93 | ||
5 | -2835000 | 40000000 | -30000000 | 3125500 | 4039500 | 1535010 | 5629990 | 0.43711 | 2460920.52 | ||
6 | -2835000 | 40000000 | -30000000 | 3122000 | 4043000 | 1536340 | 5628660 | 0.37043 | 2085033.19 | ||
7 | -2835000 | 40000000 | -30000000 | 3125500 | 4039500 | 1535010 | 5629990 | 0.31393 | 1767394.80 | ||
8 | -2835000 | 40000000 | -30000000 | 1561000 | 5604000 | 2129520 | 5035480 | 0.26604 | 1339629.85 | ||
9 | -2835000 | 40000000 | -30000000 | 7165000 | 2722700 | 4442300 | 0.22546 | 1001543.50 | |||
10 | -34335000 | 40000000 | -30000000 | -24335000 | -9247300 | -15087700 | 0.19106 | -2882723.36 | |||
11 | 40000000 | -30000000 | 10000000 | 3800000 | 6200000 | 0.16192 | 1003898.05 | ||||
12 | 40000000 | -30000000 | 10000000 | 3800000 | 6200000 | 0.13722 | 850761.06 | ||||
13 | 40000000 | -30000000 | 10000000 | 3800000 | 6200000 | 0.11629 | 720983.95 | ||||
14 | 40000000 | -30000000 | 10000000 | 3800000 | 6200000 | 0.09855 | 611003.34 | ||||
15 | 40000000 | -30000000 | 5250000 | 15250000 | 5795000 | 9455000 | 0.08352 | 789644.15 | |||
NPV | 24,420,643.81 |
NPV = 24,420,644 is positive so project must be selected
Showing formula in excel
Year | Initial cost | Loan payment | Annual revenue | Annual O&M cost | Depreciation | Salvage value | Taxable Income | Tax | ATCF | Discount factor | Present value |
0 | =-35000000*0.1 | =B3 | =1/(1+0.18)^A3 | =J3*K3 | |||||||
1 | =-(35000000-3500000)*0.09 | 40000000 | -30000000 | =0.1429*35000000 | =D4+E4-F4+C4+G4 | =H4*0.38 | =H4-I4+F4 | =1/(1+0.18)^A4 | =J4*K4 | ||
2 | =-(35000000-3500000)*0.09 | 40000000 | -30000000 | =0.2449*35000000 | =D5+E5-F5+C5+G5 | =H5*0.38 | =H5-I5+F5 | =1/(1+0.18)^A5 | =J5*K5 | ||
3 | =-(35000000-3500000)*0.09 | 40000000 | -30000000 | =0.1749*35000000 | =D6+E6-F6+C6+G6 | =H6*0.38 | =H6-I6+F6 | =1/(1+0.18)^A6 | =J6*K6 | ||
4 | =-(35000000-3500000)*0.09 | 40000000 | -30000000 | =0.1249*35000000 | =D7+E7-F7+C7+G7 | =H7*0.38 | =H7-I7+F7 | =1/(1+0.18)^A7 | =J7*K7 | ||
5 | =-(35000000-3500000)*0.09 | 40000000 | -30000000 | =0.0893*35000000 | =D8+E8-F8+C8+G8 | =H8*0.38 | =H8-I8+F8 | =1/(1+0.18)^A8 | =J8*K8 | ||
6 | =-(35000000-3500000)*0.09 | 40000000 | -30000000 | =0.0892*35000000 | =D9+E9-F9+C9+G9 | =H9*0.38 | =H9-I9+F9 | =1/(1+0.18)^A9 | =J9*K9 | ||
7 | =-(35000000-3500000)*0.09 | 40000000 | -30000000 | =0.0893*35000000 | =D10+E10-F10+C10+G10 | =H10*0.38 | =H10-I10+F10 | =1/(1+0.18)^A10 | =J10*K10 | ||
8 | =-(35000000-3500000)*0.09 | 40000000 | -30000000 | =0.0446*35000000 | =D11+E11-F11+C11+G11 | =H11*0.38 | =H11-I11+F11 | =1/(1+0.18)^A11 | =J11*K11 | ||
9 | =-(35000000-3500000)*0.09 | 40000000 | -30000000 | =D12+E12-F12+C12+G12 | =H12*0.38 | =H12-I12+F12 | =1/(1+0.18)^A12 | =J12*K12 | |||
10 | =-(35000000-3500000)*0.09+(-31500000) | 40000000 | -30000000 | =D13+E13-F13+C13+G13 | =H13*0.38 | =H13-I13+F13 | =1/(1+0.18)^A13 | =J13*K13 | |||
11 | 40000000 | -30000000 | =D14+E14-F14+C14+G14 | =H14*0.38 | =H14-I14+F14 | =1/(1+0.18)^A14 | =J14*K14 | ||||
12 | 40000000 | -30000000 | =D15+E15-F15+C15+G15 | =H15*0.38 | =H15-I15+F15 | =1/(1+0.18)^A15 | =J15*K15 | ||||
13 | 40000000 | -30000000 | =D16+E16-F16+C16+G16 | =H16*0.38 | =H16-I16+F16 | =1/(1+0.18)^A16 | =J16*K16 | ||||
14 | 40000000 | -30000000 | =D17+E17-F17+C17+G17 | =H17*0.38 | =H17-I17+F17 | =1/(1+0.18)^A17 | =J17*K17 | ||||
15 | 40000000 | -30000000 | =0.15*35000000 | =D18+E18-F18+C18+G18 | =H18*0.38 | =H18-I18+F18 | =1/(1+0.18)^A18 | =J18*K18 | |||
NPV | =SUM(L3:L18) |