Watch videos with subtitles in your language, upload your videos, create your own subtitles! Click here to learn more on "how to Dotsub"

FIN500 Mod06 P2a NPV and IRR in Excel 2010

0 (0 Likes / 0 Dislikes)
♪ Music playing in background ♪ Text on screen: NPV and IRR in Excel 2010 Hello and welcome! In this screencast we'll take a look at net present value and internal rate of return, both very important concepts in introductory finance. So let's get started with the simple spreadsheet here, that gives you a bunch of cash flows that occur in various years. Years 0, 1, 2, 3, and 4 the cash flows are $350 dollars going out and so it's negative, it's a negative value, and its in a parenthesis because that's how accountants like to show negative values, So a $350 dollar cash flow is going out. In the 0 year that is at the very start of our time interval of interest. At the end of that first year you have a cash flow of a $100 dollars coming in. At the end of the second year you have a cash flow of $200 dollars coming in. And at the end of the third year, you have a cash flow of $150 dollars coming in. And fourth year, $75 dollars. So how do you calculate the net present value of all these cash flows subject to an interest rate of 5%? So, in other words, what we're asking is if the cost of our money is 5% that is if we were to put this money in the bank we'd get a 5% return interest rate on this. Now given that opportunity cost that our money can earn, what if we put this money of $350 dollars inside this venture that it yielded $100 dollars, $200 dollars and so on, over the four years? Which is better? Is it better to put money in the bank—and at 5% or is it better to money in this venture and earn this return? We need a way to compare that. So that's what we're trying to find out with the net present value. If the net present value of these cash flows had this hurdle interest rate or had this cost of capital. If the net present value is positive, then it's better to invest in this. If the net present value is negative it's better to put money in the bank and earn 5% interest rate. So to calculate net present value I'm first going to do it the longer way, which is to say, that I will calculate the present value of each of these cash flows. The present value is the cash flow divided by the discount rate (the discount rate is the interest rate) raised to the power of how many, how much time periods away I will be getting the money from. So, in this case, it is zero because we're immediately paying off the money. So I'm just going to press enter, and I also want to enter F4 so the F1 cell here becomes $F$1 so that as I copy this formula down, the interest rate cell remains F1. And I copy this down to all the other cells. So now you have the present value of $100 dollars one year from now, is $95.24. The present value of $200 dollars that you will get in 2 years from now is worth, today, $181.41. And so on for these two. And the net present value is nothing but the sum of all these cash flows, the negative one in the beginning and the positive ones later on. And that is $117.92. Excel provides an NPV function, and we ill now use that function to calculate the net present value. To use NPV function this is how we will do it. I would first add up the initial negative cash flow plus NPV with a parenthesis the interest rate and all the values (not the first one, the negative one, but all the other values) and that's exactly $117.92. So if we were to put $350 dollars in this venture which will yield $100 dollars in the first year, $200 dollars in the second year, and $150 dollars in the third year, and $75 in the fourth year, then we will be better off by $117.92. So that means that is our gain out of the venture, and we should probably put money in this venture as opposed to putting the money in the bank. That brings us to the concept of the internal rate of return. As you can increase this interest rate, let me just show you this, supposing instead of having 5% if this was 7%. The net present value has just decreased. If the interest rate was instead 10% the net present value has decreased even further. So if you find, if you can find an alternative investment that pays higher interest rates than this particular investment appears less, and less attractive because its net present value is lesser and lesser. So what is that value of this interest rate? Right? Is there as sufficiently high value of this interest rate at which the net present value becomes zero? That interest rate is the internal rate of return. So let me calculate that using the excel formula. equals IRR with a parenthesis, here are all the values (now I'm selecting the first one as as all the others) and I have a guess interest rate (this can be any number it doesn't have to anything at all) I could just put say 3% here or something, some reasonable interest number, interest rate number. Or I can just choose this number here as well, let me just say 6% so totally random number, I could choose any number here within range. (it should be a reasonable interest rate) and just press enter. And the internal rate of return is 19%. Let me actually make this show the decimals. So this is the internal rate of return, so at this interest rate, if this interest rate was 19.25614% then the net present value would be zero. Let me see if there is a case, I'm just going to say 19.25614 and you can see that the net present value has become 0. So if your alternative investment can yield something greater than 19.25614%, so if it can yield 20%, then you're actually losing money by putting it in this venture because the alternative investment can yield more. And now your net present value is actually negative, so that is the significance of the internal rate of return. How do company's use internal rate of return? They evaluate their projects based on what is the internal rate of return that this project has. So if the internal rate of return is sufficiently high, say 20%, 25%, then you know that the project is probably worth doing because it's going to result in a net, in a positive net present value, or NPV, and you proceed with the project. If the internal rate of return is low, with 3 or 4%, then it's more risky to do that project because it could be higher, projects that yield higher returns. So that was a brief discussion of net present value and internal rate of return and how the two are related. I just want to leave you with one part that is the NPV function in Excel does not calculate net present value as understood by the finance community. In the finance committee, in finance textbooks, net present value includes the initial amount that you put in and the present value is not in that, whereas in fiance you do not include the initial value. We have to add the initial value explicitly in the beginning. So I hope you found it useful and thanks for watching and check out more videos on Have a great day. ♪ Music playing in background ♪

Video Details

Duration: 8 minutes and 59 seconds
Country: Andorra
Language: English
License: Dotsub - Standard License
Genre: None
Views: 26
Posted by: christineward on Mar 2, 2016

NPV and IRR in Excel 2010

Caption and Translate

    Sign In/Register for Dotsub to translate this video.