Sign in or start a free trial to avail of this feature.
11. Investing with Loans Part 2
Loan interest repayments act as a tax shield on our investment income. In this lesson, I'll implement this shield in our Excel model to find our final rate of return.
Calculating interest repayments for amortising loans
- The amount of interest paid on the loan decreases over time as principal decreases
- To calculate the interest paid in a particular year, we use the IMPT function
- We then subtract this amount from EBIT to calculate the taxable income
Calculating principal repayment for amortising loans
- The principal repayment is subtracted from earnings after taxes
- To calculate the principal repayment, subtract interest repayment from total annual payment
- Alternatively, we can use the PPMT function to calculate the principal repayment
TAB: Finish cell name when writing formulas
CTRL + →: Move to the last cell in the data region
SHIFT + →: Select next cell
ALT + E , S , F: Paste formulas
F2: Jump back inside a formula
F4: Anchor cells
Returning to our Excel sheet, I've added two new variables, earnings before interest in tax, named EBIT, and the tax rate. Additional roles for EBIT, interest repayment, earnings before tax, and taxes paid have also been added to the model. Let's start by adding the values for our earnings before tax. So this will simply be equal to EBIT.
I'll extend for the remaining years.
Next we have our interest repayments, and to calculate the interest repayments on an amortizing loan we use a function called IPMT.
So I'll write equals IPMT and open the bracket.
And our first input is the interest rate, which I'll anchor.
Next will be the period in question, which is simply the year.
Next the number of periods, which is eight, and again I'll anchor. And finally the value of the loan, which is equal to the percentage multiplied by the sale price.
I'll then close the bracket and press enter. I'll then copy across for the remaining years.
And when I do this I can see that the interest payment is calculated correctly for the first eight years. But I have two number errors at the end, and this is because periods nine and 10 operate outside the loan term. To fix these errors I'll need to slightly adjust my formula. And in my formula I'll simply add an if function that checks if the current year is within the loan term.
So I'll press F2 to enter the formula.
And then equals if and open a bracket.
And the logical test will be if the current year is less than or equal to the loan term.
And if it is I'll return the PMT, and if it isn't I'll return zero.
I'll then close the bracket and press enter. I'll then copy and paste for the remaining cells.
Now to calculate earnings before tax I'll simply sum these two values.
And taxes paid will simply be 35% of the earnings before tax, which is simply equal to earnings before tax multiplied by minus the tax rate.
And again I'll paste for the remaining values.
Lastly we must add in the principle payment for the loan.
And we'll first check if the current year was less than or equal to the loan term.
And if it is, we'll calculate the principle payment by taking the annual loan repayment and subtracting the interest payment.
And if it isn't, I'll simply return zero. Let's close the bracket and press enter.
I'll then copy and paste for the remaining cells.
Lastly we simply have to calculate the annual cash flows, which will equal to the earnings before tax plus the taxes paid, plus the principle payment.
And again, I'll copy across for the remaining cells.
And now our model is complete.
The IRR of this project is now 16.18%, which is almost double the original IRR of 8.14%.
Note that project returned without a loan. So it's clear that loans do serve to increase our rates of return. In the next lesson we'll examine the loan's impact in more detail and look at the additional risk that loans create on investments.