Borrowed Money (part II)

PRESENTER: So there are four types of loan, balloon payment loan, interest only loan, constant amortization loan, and constant payment loan, which is the most common one. I explained the first three types in previous videos, and in this video, I'm going to explain the constant payment loan. Now let's work on an example and see how we can apply the loan calculation, how we can apply these principles and calculations to a project with borrowed money.

First, let's assume the simple case that there is no borrowed money and consider an investment of a project that records the capital cost of a million dollar. The capital cost is depreciable over four years using MACRS-3 year half convention. The capital-- the machine is going to generate annual revenue of $625,000, and the operating cost is going to be $220,000 from year one to year four. The discount rate is going to be 10% and tax will be 4%. And we are going to consider the working capital of $100,000.

So we summarized this example, as in this slide. The project life is four years, capital cost $1 million, annual revenue $625,000 and operating costs of $220,000 from year one to year four. Depreciation using MACRS-3 year half year convention. A zero salvage, capital cost-- working capital of $100,000, and income tax is 40%, and discount rate or minimal rate of return is going to be 10%.

So we draw our table. First, I enter the revenue of $625,000 from year one to year five. Then we deduct the operating cost of $220,000 depreciation. We multiply the capital cost of $1 million by depreciation rates that we read from table one from IRS website. And we enter the depreciation from year one to year four here. The other item is working capital write-off. Well, this is tax deductible so we enter that with a negative sign. $100,000 we deducted from revenue as tax deduction.

Taxable income, which is a summation over each column, income tax of 40% and net income. Then we add depreciation-- add back the depreciation, which equals to this row, but with a positive sign. Then we add back the working capital write-off, because it was deductible from revenue as tax deduction, so we need to add them back-- add that back again with a positive sign. Then we start adding the costs and capital costs, so working capital of $100,000 at present time with a negative sign. And capital costs of $1 million as present time with a negative sign. We calculate after tax cash flow and rate of return and NPV of the project as 11.33% for rate of return and NPV at a discount rate of 10% of $30,492.

Now, let's assume the investor takes a loan of $1 million at time zero with annual interest of 8% that has to be paid over four years. So the first step is calculating the equal annual payments. We use a capitol recovery factor or A over P factor, 8% and four years, and we calculate the annual payment of $301,921 that has to be paid for each year from year one to year four.

So then we need to calculate the interest and principal portion of each payment for each year. Payments are equal from year one to year four. For year one, we have to calculate interest. Interest equals balance multiply interest rate. Balance of $1 million multiply interest rate of 8%, which gives $80,000. And then principal equals payment minus interest, which gives us the principal at year one and that balance equals the balance of previous year minus the principal that is paid, which gives us the remaining balance.

And these are the numbers for year one. We repeat the calculations for year two. We calculate the interest as the remaining balance, which we calculated here multiplied by interest rate. The principal equals the payment, which we calculated here, minus the interest. And the balance equals the balance of previous year, which is here, minus the principal which we calculated here. And this is the remaining balance.

We repeat the calculation for year three. Interest equals remaining balance of the previous year multiplied interest rates, and the principal equals the payment, which is up here, minus the interest, which we calculated here, and it gives us the principal. And remaining balance equals the balance minus the principal that we calculated here, which gives us the remaining balance at year three.

We repeat the equation for the last year. Interest equals remaining balance multiplied interest rate. Principal equals the payment minus the interest, and if we calculate everything correctly, the principal in the last year should equal the balance, the remaining balance-- the last remaining balance, and it should gives us the zero remaining balance for the last year.

So we are going to need these two rows of interest and principal portions of each payment, and let's see how we have to enter them into the table for our calculations. So the revenue, operating cost, and depreciation in working capital are the same. And the other item that we have to enter to the table as an amount that has to be deducted from the revenue as tax deduction is the principal portion of these annual payments. So this loan interest is the interest that we paid that we calculated here.

This is the row-- we entered this row, the interest row, as the amount that has-- that have to be deducted from-- deducted from revenue as tax deductions. And we calculate the taxable income. We calculate the tax. We calculate net income. We add back the depreciation working capital write-off and working capital with a negative sign. So very important point here, because we [INAUDIBLE] need to enter the principal portion of those annual payments. So the principal portion of the loan that we calculated here, we extract these and we entered them to the table.

So these are the principal of loan. Then we enter the capital cost, which was $1 million and the loan. So here, as you notice that, because we take $1 million exactly equal to the capital cost, these two cancel out, but still-- but we still keep them in a table. This is because you keep them organized, because there might be cases that your loan might be lower than the capital cost that you need.

For example, let's say you're going to take the loan of $400,000 or $600,000. So in that case, you will need the capital costs, then you will need to write the entire money record for the capital cost, and you say, OK, I got $400,000 or $600,000, a loan for that. And we calculate the rate of return and NPV. So, again, note that when we have borrowed money in our projects, we need to be very careful. The interest portion of annual payments are deductible from revenue as tax deductions, and principal portion of those annual payments should be entered in the table with a negative sign.

So, again, you still need to write the capital cost with a negative sign and the loan with a positive sign. If they are equal, they cancel out. But if they are not equal, if the loan that you are getting is lower than the capital cost that you are needing, then they won't cancel out. And so we calculate the after tax cash flow, and we calculate the rate of return as almost 90%. And you can see that how this project is leveraged after we took the loan.

PRESENTER: So there are four types of loan-- balloon payment loan, interest only loan, constant amortization loan, and constant payment loan, which is the most common one. I explained the first three types in previous videos. And in this video, I'm going to explain the constant payment loan. So let's work on this example, using Excel spreadsheet, a briefly explained example.

The life of the project is four years. The capital cost record is $1 million, annual revenue of $625,000, annual operating cost of $220,000 from year 1 to year 4. The depreciation method is MACRS 3-year half year convention, salvage zero, working capital of $100,000, income tax of 40%, and discount rate of 10%.

So the first is revenue. So revenue is $625,000 from year 1 to year 4. And then we are going to have the operating cost, with a negative sign, of -$220,000 from year 1 to year 4. Then we are going to have the depreciation from year 1 to year 4. So I extracted the rates here, and I'm just going to apply them with the negative sign-- $1 million of capital cost. Multiply these rates from year 1 to year 4.

Then we are going to have working capital write-off, write-off. So it is going to be-- working capital can be deducted from revenue as tax deductions. So this was $100,000. And then we are going to calculate the taxable income, which is a summation of each column.

Then we calculate the tax, which was 40%. So we multiply the taxable income by 40%. And we can enter, with a negative sign, then net income, which equals this plus tax with a negative sum plus taxable income. Then we add back the depreciation, with positive sign. So it is negative.

I multiply it with a negative sign, to which I get a positive sign. The other is working capital write-off. This here was with a negative sign. I have to enter it with a positive sign here-- I just use abbreviation-- then the capital cost in the working capital.

And after tax cash flow, which is going to be the summation of these numbers. And then I'm going to calculate the rate of return for this project, using the IR function. And then the NPV, which was-- I have a payment at present time, plus NPV of 10% and the other.

Now, let's assume we are going to take a loan of $1 million at present time with 8% of interest. And we want to see how it is going to affect the project. So the first thing is we have to calculate the payments, so from year 1, 2, 3, 4. The payment equals this $1 million. Multiply the factor A/P, or capital recovery factor, which is I multiply 1 plus I power 4 years, divided by 1 plus I power 4 minus 1.

So it is going to give us almost $302,000 per year. And they are equal for year 1 to year 4. Then we need to calculate the interest. The interest equals the balance multiply the interest rate. The balance for year 1 is $1 million. Multiply the interest. And the principal is the payment minus the interest. The principal equals payment minus interest. And the balance equals the balance of previous year, which was $1 million minus the principal that is paid at-- the principal portion of the payment that is paid at year 1.

For year 2, the interest equals the balance of the previous year minus multiply the interest rate, which was 8%. And the principal equals payment minus the interest rate. And the balance equals the balance of previous year minus the principal portion that is paid. So I can just apply this to the other years. And as you can see here, if we calculate everything correctly, the balance of the principal of the last year should equal exactly same as the last remaining balance.

Now, we need to draw our table. First, start with year 1, 2, 3, 4-- sorry, we start at present time-- 1, 2, 3, 4. We start with revenue, which was, starting at the year 1, $625,000. And it was from year 1 to year 4; then the operating cost, with the negative sign. Then I will add depreciation. I can extract rates from the table and write them here. So depreciation equals minus $1 million. Multiply these rates. Then we add the working capital write-off-- I just use that abbreviation-- which was minus $100,000.

And the important part here is the interest portion of these annual payments, loan annual payments, are deductible from revenue as tax deductions. So I will enter them with the negative sign. So this is the interest. Then I will calculate taxable income, which is the summation over this column, and the tax, which is 40% of this number, and net income.

Then we add back the depreciation, with positive sign, equals this amount; then the working capital write-off, which is a positive sign here. And then we have to enter the loan principal, with a negative sign, here. So this is the loan principal from year 1 to year 4. Then we enter the capital cost, with a negative sign. And then we enter the loan, with the positive sign.

And we are going to have. And then we calculate the after-tax cash flow, which is the summation of this part. Sorry, I missed the working capital in the table, working capital, with the negative $100,000, and after-tax cash flow, and rate of return, and the NPV.