 Hello and welcome to this session. This is Professor Farhad and this session will look on how to compute the MPV and the IRR using Excel. This topics could be covered in an introduction to data analytics and accounting. Simply put a data analytics course, managerial accounting, or introduction to finance course. As always I would like to remind you to connect with me on LinkedIn if you haven't done so. YouTube is where you would need to subscribe. I have 1,700 plus accounting, auditing, finance and tax lectures, as well as many Excel and CPA tutorials. This is a list of all the courses that I cover. Connect with me on Instagram. On my website you will find additional information but if you like this recording please like it, share it, subscribe and you will find additional resources on my website if you want to supplement your CPA education, CMA, enrolled agent or your accounting courses. I strongly suggest you check out my website. To illustrate these concepts I'm going to be using the following data. Now keep in mind I do explain in detail the net present value as well as the internal rate of return in my managerial and finance accounting on YouTube. However I will give a quick overview as I'm performing these computations. So this is what we are looking at here. We have a project and it will require today an outflow of half a million dollars. So basically your initial investment is negative because think about it from an educational perspective. When you're going to school first you put money you invest money then eventually you would earn your money later after you graduate. So basically at year zero you have to invest a sum of money and for our purposes it's half a million then in year one you're going to earn back 165,000 in year two 180,000 in year three 164,000 in year four 175,000. So those are the data that we are giving in this problem. So the first thing I'm going to do I'm going to compute the net present value in a sense manually to show you what we are doing then I will use the Excel formula because it's very important that you understand the concept and what we are assuming here for this project is a hurdle rate or a rate of return of five percent. So simply put the company will have to earn at least five percent that's their cost of capital. So how do we perform this computation using formulas but not the Excel formula? Okay so basically what we will do is first we would put the equal sign and we want to discount this half a million dollar at one plus the interest rate which is for a percent raised to the period the period here is zero. So basically simply put this is going to give us half a million today is worth half a million today into the future that's fine. Now the only thing I'm going to change here is I'm going to put the e1 the interest rate as a relative value this way it will carry on in the formula. So I'll put the dollar sign around the around the number then I'm going to I'm going to grab and grab the cell then scroll down and simply put what I did I discounted this 165,000 a year from now at five percent and it's worth 157, 142,000 almost 143,000 I did the same thing for the other three values. Well we find out that this project has a positive net present value of 106,000 and almost 50 dollars and 50 cent. What does that mean? It means we and in the in the language of net present value we will accept this project but really we did not we did not use our net present value formula what I did is I use regular formula I kind of based on the time value of money and I discounted each payment each payment separately. Now in excel there is a net present value formula that you that you can utilize. So let me show you how you utilize the net present value to compute this number a little bit more quicker basically and basically you'd have to put the equal sign to activate the cell and the first thing you have to do is you have to select your net your outflow your initial outflow of cash which happens to be b3 then we're going to add to that the net present value the NPV which is the which is the NPV notice the NPV and let me just try to yeah NPV and it's going to tell you if the first to put the rate what is what is our rate our rate is five percent select the select the rate and then you have to put the positive cash flow the positive cash flow is a range between 165 and 175 notice it's b4 to b7 so this is the formula so this is the b3 is the initial cash outflow plus the net present value using the interest rate of five percent for the cash inflows click on next and notice the formula gives me exactly the same number and obviously I would accept the project whether I use manually the excel or if I use the net present value that's fine now what is the disadvantage of the net present value the disadvantage of the net present value it tells you that your project is acceptable based on a dollar amount so it's yes it is acceptable why because it's above zero that's fine that's fine in dandy i'm okay with that it's above zero so it's good now the question is how much is this project really earning me so how much is this project really earning me i know i all that i have to do is beat five percent because that's my interest rate that's my hurdle that's my cost of capital well what we have to do as i have to compute my internal rate of return and my internal rate of return basically find the rate of return that's going to make the net present value equal to zero so the irr the irr basically what it's looking for is when when is the net present value equal to zero at which rate here's why if i increase this six percent to seven percent notice my net present value goes down if i increase it to eight percent it goes down if i increase it to 12 percent oops let me let me let me type 12 it keeps on going down and when the net present value equal to zero it tells me exactly how much this project is earning because right now five percent i know i can beat it's a great project because it has a net present value it's making more than five percent so i'm better off taking this project rather than invest in my money at the bank but the irr tells me exactly what should i do so how do i compute the irr now i can play with this with a trial and balance i can for example put 12 it's more than 12 let me try 14 it's less than 14 let me try 13.5 oops 13.5 let me do let me just make sure this is goes to two decimal points so this way we see what we're looking at here let's make it four decimal point 13.5 made let's try 13.75 so notice it's around 13.75 it's around 13 point let me try 85 now that's way too much 13.8 13.76 let's stop right there it's around 13.76 but i don't have to keep on doing this you know changing the interest rate to find out when my when my net present value equal to zero let me go back and put the initial one which is five percent what i can do i already we already know it's around 13 point something what i can do is i can activate my formula for the irr using excel because that's the beauty of excel so i put irr open parentheses and what i do first is i select my cash outflow my cash outflow is half a million because this is the this is the first value the value of cash flow then what i have to do actually i'm sorry i don't i don't i don't select my cash flow i just have to put the range the inflow and the outflow here are the inflows and the outflows and i just have to guess put any number one percent two percent three percent and it's going to come back with 13.76193 so this project is making 13.76193 i can test this and we knew it's around that much but if i put 13.76193 and it should give me an mpv of zero and voila it's an mpv of four pennies zero that's fine so this is the irr so the the irr tells you exactly what this project is earning because the mpv equal to zero so it's earning around 13.7619 not a bad return at all so i hope you like this recording and again if you want to learn about mpv and irr go to my managerial accounting or to my finance accounting on my youtube and please check out my other recordings check out my website if you're an accounting students i do have additional resources stay safe study hard especially during those coronavirus days good luck