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 Principal	Total Repayment	Interest	Principal Repayment	Closing Principal
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.