# Excel finance tutorial: calculating IRR for a loan

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

See also  Banking Degree Requirement, Work in a Bank, Accounting Degree, Finance Degree, Economics Degree

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