Chapter 7 Video: IRR

In this Excel tutorial I'm going to show you how to solve for internal rate of return graphically. Here's the information we have. We have in Year 0 $500 is invested and then year through 1 years 1 through 7 here's the money that's been made. And what we're going to be doing is we're going to be taking varying interest rates solving for the present worth and then graphing present worth versus the interest rate and see where does my graph cross 0. Remember to solve for your internal rate of return, that's you set your present worth or present value equal to 0 and solve for the interest rate that makes that happen. So what I need to do is I need to deal with these cash flows and I need to get them back to Year 0.

So in this case, this would equal Net Present Value. First thing it asks me for is my rate and my first rate I'm going to try is in E3 comma. Then I need to bring my cash flows back to Year 0. Remember the negative $500 is already sitting there so I just need to pick up C4 through C10 and close my parentheses. Plus, I need to add on that cash flow that's already sitting there, the $500. Now in order to make the rest of these calculations really easy I'm going to lock the C4 by highlighting and hitting F4, highlight the C10, hit F4 and highlight the C3 and hit F4. What I'm doing here is I'm locking these down so that when I grab this corner and pull this formula down it will only increment the interest values.

It makes my calculations go really fast. So here we go. Get it, grab the corner when you get the crosshairs, pulldown. Notice what it's done is it’s kept everything the same but notice how it’s incrementing my interest rate. Makes the formula go really fast. Okay, let's take a look at this graphically. So I'm going to highlight my present worth, go to insert and I'm going to start a line chart. I'm going to pick the first one. And bring it over here so you can see it. I have present worth. But my horizontal is definitely not my Is, so an easy way to do this is just right click, notice how it's highlighted that axes there, and I'm going to pick select data.

Okay. Now I can do the... I don't want to mess with the legend over here, I want to mess with the horizontal. So I'm going to hit edit and it's going to ask me, what is the axis label range? Well, here it is. It's the 0 to 28% percent. Hit OK. Hit OK again, and I'm done. But I still don't like where this is sitting. Notice it's above the $200. So while it's still highlighted right click and we can...oops! It's it's not highlighting what I want here. There we go. Got my axes and I want to format axes. So in this case, I want them to be not next to the axis, but really low and I like them on the tick marks. So you can see now I've got it looking the way I want.

So I can tell it's somewhere around 22%. Remember, you can always directly solve for this by simply typing equals, IRR and highlighting the cash flow and you get 22%. Which is what it's looking like graphically. Now, I put this in in a line chart. Notice if I change this to 2% it does something really weird. So a line chart was probably not my good choice. So let me put this back to 4%. I probably should have done a scatter plot. So if you want to change the chart type simply click on the chart, right click and hit change tar…chart type.

I'm going to go down to XY scatter and then I'm just going to pick the first one it gives me. And notice now when I change this to 2% it accurately shifts the present worth. So in this Excel tutorial, I've shown you graphically how you can look at internal rate of return. And notice that you can see it graphically or you can also solve for it directly. It's a great way to take a look at a..at a chart and see what is my internal rate of return and how it is changing based on your interest rate.