Shows how to calculate IRR for a loan, and compare with available interest rates from other sources.
Hello and welcome in this video we’ll take a look at how to use the irr concept to evaluate a cash flow when you are borrowing money as opposed to actually investing so when you’re borrowing money you typically have an initial negative cash flow or initial positive cash flow followed by a set of negative cash flows and this in this example let’s say you want to
Purchase a car and you want to borrow you have the option to borrow let’s say five thousand dollars from a dealer and you can repay at the rate of two thousand dollars every year for the next three years so let’s just plot those cash flows so initially you borrow $5,000 and then you repay two thousand dollars every year thereafter and i’m just going to copy it like
This now the other alternative is that you can borrow this $5,000 from a bank at nine percent interest rate so you want to see if it is a good option to borrow the money from the car dealer or borrow it from the bank to do that we will compute the internal rate of return for these cash flows irr and i’m just going to select all of them and give us ten percent guess
Rate and what you see is there is a nine point seven percent internal rate of return that applies to these cash flows now is that good or bad now if you were investing this money and you were earning this money at the end of various years from your investment then your nine point seven percent is greater than let’s say the money that you would earn in the bank which
Is nine percent but so so it would be good but in this case you’re actually borrowing this money and really paying this money in the next three years and your internal rate of return is higher than the bank interest rate so this is actually bad for you so actually borrowing money and on these terms from the car dealer is actually bad for you because it’s internal
Rate of return at nine point seven percent is higher than the interest rate that you will earn by borrowing from the bank and to see that let’s estimate the cash flow from the bank and if you were to borrow this money from the bank you would borrow five thousand in the beginning and i’m going to use the pmt function to estimate how much you’ll pay on an annual
Basis the interest rate is nine percent the number of periods is three because you’re repaying in three years the present value is what you borrow initially the future value is zero because you intend to repay the full amount and the type here we want to say zero because you repay these dollar amounts at the end of each year instead of at the beginning so i will
Put at zero again and complete the parenthesis so you will be repaying one thousand nine seventy-five dollars every year if you were to borrow at nine percent and just so that we can copy this further down i am just going to apply the dollar you know absolute cell reference by i just click on that particular cell and press f4 and i click here inside b1 and press
F4 again and that will result in these cell references being absolute and from then i can just copy this further down like this by clicking and drying the fill handle and you can see that each year i pay one thousand nine seventy five which is lower than the two thousand dollars i would pay with the car dealer so that gives us further confirmation that borrowing
From the bank is a better option another way to look at this is to compute the net present value of the dealers you know of the cash flow of your cash flow when you borrow from the dealer at a nine percent bank interest rate and you will see that to be negative so equals 5,000 plus npv of the bank interest rate and these cash flows and you can see it’s actually
Negative now another way to look at this is to compute the internal rate of return of the bank cash flows and it will be exactly nine percent it will be exactly nine percent and the net present value of this at the bank interest rate will be 0 it is 0 so all this confirms what we are saying so the essence of this concept is that if you are evaluating a loan to
Yourself then your internal rate of return should be lower than or equal to what you can get the interest rate you can get from an alternative source such as a bank
Transcribed from video
Excel finance tutorial: calculating IRR for a loan By Codible
