How to calculate number the solution table shown. i hope can have more detail on it. For
Question:
How to calculate number the solution table shown. i hope can have more detail on it.
For example, how to calculate the Loan repayment, Depreciation, Interest expense, PMT and Principal Repayment
Example
B-Tel, Inc. is a telecommunication services provider looking to expand to a new territory Z; it is analyzing whether it should install its own telecom towers or lease them out from a prominent tower-sharing company T-share, Inc.
Leasing out 100 towers would involve payment of $5,000,000 per year for 5 years.
Erecting 100 news towers would cost $18,000,000 including the cost of equipment and installation, etc. The company has to obtain a long-term secured loan of $18 million at 5% per annum.
Owning a tower has some associated maintenance costs such as security, power and fueling, which amounts to $10,000 per annum per tower.
The company’s tax rate is 40% while its long-term weighted average cost of debt is 6%. The tax laws allow straight-line depreciation for 5 years.
Determine whether the company should erect its own towers or lease them out.
Solution
Annual cash out flows of leasing (Year 1 to Year 5) = $5,000,000 * (1 – 40%) = $3,000,000
Annual cash flows of purchasing have three components: the loan amount to be repaid in each period, the maintenance costs to be borne each year, the tax shields associated with maintenance costs, depreciation expense and interest expense. The following table summarizes the calculation of cash flows under this alternative.
| Period | | 1 | 2 | 3 | 4 | 5 |
| Loan repayment | A | 4,157,546 | 4,157,546 | 4,157,546 | 4,157,546 | 4,157,546 |
| Maintenance costs | B | 1,000,000 | 1,000,000 | 1,000,000 | 1,000,000 | 1,000,000 |
| Depreciation | D | 3,600,000 | 3,600,000 | 3,600,000 | 3,600,000 | 3,600,000 |
| Interest expense | I | 900,000 | 737,123 | 566,101 | 386,529 | 197,978 |
| Total tax deductions | T = B+D+I | 5,500,000 | 5,337,123 | 5,166,101 | 4,986,529 | 4,797,978 |
| Tax shield @ 40% | t = 0.4×T | 2,200,000 | 2,134,849 | 2,066,441 | 1,994,612 | 1,919,191 |
| Net cash flows | N = A+B–t | 2,957,546 | 3,022,697 | 3,091,106 | 3,162,935 | 3,238,355 |
Annual loan repayment is based on present value calculation; it is the amount paid at the end of each year for 5 years that would write off the loan completely. It is calculated using the following MS Excel function: PMT (5%,5,-18000000).
Interest expense are calculated in the following debt amortization table:
| Period | Opening | Total | Interest | Principal | Closing |
| 0 | 18,000,000 | - | - | - | 18,000,000 |
| 1 | 18,000,000 | 4,157,546 | 900,000 | 3,257,546 | 14,742,454 |
| 2 | 14,742,454 | 4,157,546 | 737,123 | 3,420,424 | 11,322,030 |
| 3 | 11,322,030 | 4,157,546 | 566,101 | 3,591,445 | 7,730,585 |
| 4 | 7,730,585 | 4,157,546 | 386,529 | 3,771,017 | 3,959,568 |
| 5 | 3,959,568 | 4,157,546 | 197,978 | 3,959,568 | - |
It is necessary to prepare amortization table because tax laws do not allow deduction of total loan amount, instead only interest expense is allowed as deduction.
Depreciation is calculated on straight-line basis using the 5-year useful life (i.e. $18,000,000/5 = $3,600,000).
Tax shield is subtracted from loan repayments and maintenance costs while calculating the net cash outflows because tax shield represents a cash inflow which arises due to tax deductibility of the expenses.
Now, we have to calculate the present value of cash outflows under both the options using the after-tax cost of debt which is 3.6% (6% * (1-40%))
Present value of leasing at 3.6% = $13,545,157
Present value of purchasing at 3.6% = $13,950,176
Since leasing has a lower present value of cash outflows, it should be the preferred option.
Expert Answer:
a Loan repayment and interest are coming from debt amortization schedule Here to prepare debt amort... View the full answer
