**You can take notes as you view lessons.**

Sign in or start a free trial to avail of this feature.

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

**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

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.

4 mins

3 mins

3 mins

3 mins

Sign in or start a free trial to avail of this feature.