12. Investing with Loans Part 2

Subtitles Enabled

Sign up for a free trial to continue watching this lesson.

Free trial


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.

Lesson Notes

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

Keyboard shortcuts

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


In the previous lesson, we derived a new formula for calculating annual cash flows when we invest with a loan.

We'll now return to Excel and implement these changes in our model.

Returning to our Excel sheet, I've added two new variables, earnings before interest and tax, named Ebit, and the tax rate.

Additional rows 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.

And I'll extend for the remaining years.

Next, we have our interest repayments.

And to calculate the interest repayments on an amortising loan, we use a function called IPMT.

So I'll write =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 ten 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 =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 principal payment for the loan.

And we'll first check if the current year is 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.

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% that the 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 loans impact in more detail and look at the additional risk that loans create on investments.