Excel Finance Class 80: Recognizing Patterns of Cash Flows For Discounted Cash Flow Analysis

welcome to finance an excel video number 80 hey if you want to download this workbook chapter 9 click on the link directly below the video and scroll all the way down to the Excel finance class hey we've been talking about discounted cash flow analysis net present value whether to valuation techniques for assets but we want to just in this video look at patterns of cash flows there's two projects they both cost fifteen thousand but notice that the cash flows eight thousand six thousand one thousand one are exactly the reverse of the other now you're probably never going to get cash flows in projects like this but recognizing the difference between these will help us understand cash flow patterns and getting used to the data when you're doing this net these Ness present values and estimating future cash flows is very helpful so first thing we want to notice is that the total for both of these is exactly the same so if I click here and hit alt equals control enter and copy it over so totals of cash flows are the same hey when would that happen if we were gonna plot net present value on this axis and required rate of return on this axis where would it be because they're the same hey that's when the discount rate is zero so be right on this axis right here now what we want to see here is our what question I want to ask is which one of these is always going to be better bigger now we're gonna plot it we're gonna actually calculate and prove it to ourselves but you should be able to just look at these cash flows and go wait a second eight thousand six thousand five one they're reversed here that means all this project a all of the bigger cash flows are earlier in the number of years right your one we get eight thousand back immediately you think a bigger cash flow earlier there's less time to discount this and take out the earnings or the interest whereas this one this one has to be discounted across four time periods so that means it's going to take out all a lot more interest or earnings right the whole idea behind present value going backwards from a future value to times 0 which is finding the present day you're taking out the earnings you think you're gonna earn or the interest so bigger cash flow later it's gonna discount back and take out way more interest cash flow or bigger cash flow earlier less time to take out interest or earnings so automatically this one right here because they're exactly the same total cash flows which means net present value at times zero cash flows earlier at all points the net present value would be bigger so you don't even have to plot anything you just say I want this project here now if you were gonna draw this and I'm gonna actually gonna draw this I'm gonna go to insert shapes I'm going to select a line I'm gonna estimate 5,000 so that's somewhere right about here I'm just gonna go like this and let's say right right here somewhere so that would be my estimate for a actually I'm gonna move the line out here like this right and that it crosses this net present value profile crosses at net present value equals 0 right that's actually the point at which i RR whatever that rate is there there that's the internal rate of return well let's do the same thing again I'm going to click right there Boop start at the same point but I'm just going to go to a different point here so this one right here if I go to mmm right there right click edit text and I'm going to say this one's a and we'll prove this to ourselves we're just estimating this this is something you should be able to just you know look at these patterns of cash flow and got immediately net present value is higher at all points as compared to B so I'm going to cop actually I'm going to point to ctrl click on the edge and then drag over here this one will be and I'm going to edit this double-click if I can get in there take B oh that didn't work I didn't I'm gonna hold try that again control click and I'm gonna let go of the mouse but not control and that'll copy it a right so those are the profiles we expect because the cash flows are bigger later on this will be lower at all points now let's go ahead and plot this I'm going to click right here equals net present value the rate I'm going to click right there comma the values this is project day so I'm gonna below and then add the negative value right there so I that's wrong that's what I'm checking it down here you could see the green and the purple moved so I forgot to lock them so I'm gonna have to put this one in edit mode click right there hi actually I could highlight and hit the f4 key and then click my cursor right there and hit the f4 key control enter and now if I double click and send it down it will populate that column with a new formula I click in the N and see I can see I got it right all right now watch this I'm gonna cheat here this is a trick I'm going to copy that escaped put it into edit mode f2 and then control V and watch this I'm just gonna move the green box I'm pointing to the edge that little cursor right there is the move cursor so now I got these cash flows with that rate now I can plot this and if we're gonna do we're gonna do an XY scatter the x value is here however many columns you have over here it will interpret each one as a separate Y value with this being the X highlight that go up to insert scatter I'm gonna select this one right here and you could see we estimate it with a straight line but now we can see point on the edge click down here alright and I'm going to actually at right click there control 1 or right click format and say put the legend at the top I'm going to add a label here go up to the chart tools layout axis I'm going to go horizontal and the horizontal I'm going to immediately type RRR enter that's required rate of return then go back up to axis title vertical that one and I'm immediately going to type in PV enter so now we have our chart and just as we suspected right we are trying to recognize cash flow patterns without even having to plot it or calculate net present value but you can clearly see our guess was a was gonna be bigger at all points because bigger cash flows earlier in the project B was going to be smaller at all points because bigger cash flows later our chart our NPV function and our chart show that our estimate here was correct now this will give you the actual values right but this is just the concept the idea the recognizing patterns of cash flows future cash flows when doing discounting cash flow analysis to value an asset alright we'll see you next video

test attribution text

Add Comment