 Personal finance practice problem using Excel. Stock price calculation assuming constant dividends and no growth. Prepare to get financially fit by practicing personal finance. Here we are in our Excel worksheet if you don't have access to it that's okay because we'll basically build this from a blank sheet but if you do have access three tabs down below example practice blank example in essence answer key let's look at it now information on the left calculations on the right we're trying to value a common stock possibly to make a decision as to whether the stock is over or undervalued as we compare it to the market price possibly helping us out with decisions such as should we be purchasing or selling the stock we're going to be using a method similar to how we value things like bonds using a time value of money present valuing the future stream of income from the investment however stocks are a little bit more confusing than bonds because bonds are more kind of set in stone as they say with regards to the terms of the bonds meaning you typically have a stream of payments that you'll be receiving interest payments as well as the lump sum at maturity meaning you also have a maturity date with stocks we have two kinds of valuations that we might be receiving one could be the form of dividends and two could be the form of the value of the stock going up and we have no end maturity date so we got to make some assumptions if we want to use the same kind of cash flow type of method and we might make different assumptions or use different methods depending on the type of stock we are valuing if we're valuing a stock that is kind of an established stock they don't need to grow much anymore because they're already established like a utility company or something like that the infrastructure is already set in place we expect them to earn steady earnings into the future and they're just going to be giving out dividends for their earnings because they don't have to put it back in the company at that point that's kind of the easiest valuation we can think of because then we can think of a steady rate of dividends going forward and uh and so that's the first assumption that we'll make and then we might do some other assumptions later okay so second tab has some pre-formatted worksheet so you can work the practice problem with less excel formatting the third tab is a blank tab where we will do the excel formatting if you don't have anything you can open up a new tab and you can start off by just laying down the underlying formatting i would do that by selecting the whole worksheet possibly by hitting the triangle up top right click in the selected area format the cells and then i typically go to currency bracketed and negative or read for negative numbers no decimals or no dollar signed and no decimals i'm not going to hit okay because i already have this i'm just going to close out x and out here then enter your data on the left hand side adding percentages for example when necessary formatting the cells as needed make a skinny c column we also have a table down here but you don't really need to worry about that we might not even use the table down below that's another way we can do the present values but we'll basically be using excel formatting as our primary tool okay so let's do our valuations so remember we're kind of imagining that we've got this $15 that are going to be the annual dividends how do we know it's the annual dividends because we've looked at them in the past and that's what they have been and we're going to assume that the dividends are going to be basically constant going forward so plan to keep this dividend for some time into the future required rate of return for the common stockholder so we're going to say this is our rate of return the discount rate that we're going to be using to determine whether or not it be worthwhile to be investing in the stock we're going to be using the 12 percent in our time value of money calculation and you can think about that as the rate that we might be getting from other similar investments taking into account the risks related with other kinds of investments for example the 12 percent in essence being kind of like our hurdle rate that we would have to be clearing in order to put our money here as opposed to possibly somewhere else now one way we can think about doing this is to kind of map out all the future payments and we'll take a look at that but there's kind of a shortcut calculation a straightforward easy way to do this kind of calculation and then we'll basically do the longer calculations which I think helps us to visualize it better so if I was to calculate the price making these assumptions we're going to say let's go ahead and make the top here black and white home tab font group we're going to make that black and white and then I'm going to say we've got the annual cash dividends we're just going to take this equals the annual cash dividends I'm going to make sell D a little bit wider put my cursor between D and E making it wider we're going to then say in IE 2 equals the $15 and then we're going to have the required rate of return so we're going to say this is the required rate of return return and this is going to be equal to the 12 percent down here and we'll then adjust the percent to make it a percentized sale home tab number group percentify it percentify you best got percentified a record font group underline and then so the price is going to be then equal to the 15 times uh the 12 I'm sorry 15 divided by the 12 it equals the 15 divided by the 12 let's add a couple decimals home tab number group a couple decimals fairly straightforward calculation again remember that what we're doing is assuming that this 15 dividends are basically going out into the future basically kind of indefinitely now let's kind of prove that that works with a longer calculation before we do so we'll do you know the reverse order of this calculation let's make this blue and bordered you're getting ahead of yourself hold on a second home tab font group we're going to put some borders around this thing all borders make it blue with a bucket drop down going down to the more colors if you don't have the blue there already standard there's the blue I use you could use whatever you want you can make whatever color you want as long as you can read it required it doesn't bother your supervisor or anything of return okay so then we're going to make this black and white we're going to say this is going to be home tab font group drop down black and white so we can imagine then if the price were this 125 the and the required rate of return was 12 so if we knew the price then possibly looking at the market price if the market price was 125 and our required rate of return then was 12 home tab font group percent let's put an underline font group in underline we would want the dividends to be annual cash dividends assuming the dividends are are only really increased in value because we're talking about stocks that are have peaked out and their growth and really are just kind of marching along like utility companies or something like that so we're going to say that would mean that we would need a dividend of 125 divided by 12 I'm sorry now it's multiplied 125 times 12 which would give us the 15 I'll add a couple decimals home tab number group couple decimals 15 even let's make that blue and bordered selecting these items here home tab font group bordered bucket drop down and blue now let's make that let's kind of prove that that what that is doing remember like if you if you think about like a bond what we're doing is trying to look at the future cash payments in present valuing them so one way we could do that is I could say let's make a skinny F column here making I'll click on the skinny C so that I can go to the home tab clipboard make a paintbrush painter and make a skinny F out of it and I could use an annuity formula annuity and I could say okay well if I'm going to get $15 out into an infinity and beyond into the future well I could just kind of I know that if it goes way out into the future then that $15 because of time value money is going to be worth less and less and less that's why I can take that infinite series and basically for the most part for practical purposes bringing about down to a defined number here right so I could do something like negative present value shift nine for my present value formula the rate is going to be this 12 percent down here the rate of return we want comma number of periods I don't have a number of periods because it goes on forever so why don't I just use a fairly big number right like even just a hundred would do it I think right because if I'm a hundred years into the future then the $15 I'm going to get a hundred years from now it's pretty small might not have a big impact on my calculation so then I say comma and then the annuity payment calculation is going to be the 15 so we're going to get 15 for a hundred years discounting at the 12 percent what would that series of payments be worth about $125 right home tab number group and you might say well that's not very specific or exact because that you know it goes out into infinity it's worth infinity because it goes on forever but you know those amounts in the future are getting smaller and smaller due to time value money let's prove that another way a couple other ways let's select these two we're going to go to the home tab font group make that border blue let's make a skinny eye column a skinny eye home tab font group like when I got poked in the eye I had a skinny eye for a while because I was squinting we're going to make a skinny eye like that home tab font group and then skinny eye okay so then we're gonna let's let's do it this way first I'm going to put a total here because I'm going to try to total it at the front end and then I'm going to put my periods at the top one two I'm going to select those two periods and I'm going to drag it all the way out to 100 periods out to the right 100 periods out to the right which is too far we don't really need to go that far but that's what we did on our annuity so we'll do it we'll do it just to prove a point we go very far to prove points around here home tab we're going to go to the alignment center it and then let's make it black and white drop down black and white let's make this centered and black and white and then we're going to say that this is going to be the dividends dividends each year $15 a year on out to infinity because it's a utility company or something they're just paying out steady dividends steady dividends steady as she goes so there it is I'm going to say f4 on the keyboard f4 and making a dollar sign before the b in the three you only need a mixed reference but an absolute will work I'm going to take that grab that then with the fill handle and drag it all the way to the right till we get to the 100 and there it is so there's our $15 going back to the start we're going to then have like the price or the present value let's say and the price when we total it the present value each period I'm going to take each of these $15 and bring them back to the current period so $15 one year from now let's do that calculation present value shift nine rate to the left is that 12% I want to make it absolute because I want to copy it to the right it's outside my table I don't want it to move to the right when I go to the right therefore I'm going to say f4 and keyboard dollar sign before the b and eight you only need a mixed reference but an absolute one works comma number of periods I'm going to say up to that's going to be the one I want that to move to the right to be number two when I go to the right and copy it to the right so I'm going to keep that as is comma the payment is up one the 15 I also want that to move to the right when I go to the right so we'll keep that as it is and hold on it's not a payment it's not a payment I was wrong thank you for telling me that we're going to say two commas because it's not an annuity it's a it's a present value of one therefore the future value is 15 and I want that to move to the right when I go to the right we don't even need to close it up I'm just going to hit enter there it is I'm going to grab it on the fill handle and if I did everything right I'll do it just one time to the right this time take it to the right one time and see if it does what I would expect double clicking on it looks correct everything looks in order so now let's grab it again and pull it all the way to the hundred hundred periods out hundred periods out boom and then you get a bunch of zeros down here but I'm going to add some decimals number group decimalizing it decimalized I'll decimalize this one home tab decimalized and now we only usually go to a couple pennies but just to show you can see obviously as we go out that fifteen dollars we're going to get 20 years from now is getting quite small it's a dollar fifty so fifteen dollars out 30 years is is getting fairly small and if I go obviously past we're below a penny of value after 66 years so fifteen dollars 66 years for now we value back at our discount rate at 85 right and if I go all the way out to a hundred years you can see it gets quite small to the point where it's kind of insignificant which is why if I sum this series of payments up if we sum this series of payments up on the right here equals the sum of this series of payments all the way on down we get about 125 so we're going to go home tab number group a couple decimals let's pull that up to the right add up to 15 so we had 1500 dollars over over a hundred years is only worth 125 if we discounted back using that 12 percent which is kind of it's not just inflation that 12 percent that's including the rate of return that we expect to get that's why we would say the price then would be like 125 if we were going to use that as our valuation we would expect the price to be 125 if the price was over that we might say what might be overvalued based on this under that on the market price we might say it's undervalued okay so let's do it let's do this one more time let's make this blue and and bordered and I just want to do that calculation a couple more times one because it's fun and two because you might see it a different way and three because other people might do it a different way and you got to deal with the they're funny minds doing things different but still like correct but like weirdly so I'm the only one that does things correct all the the right way all the time other people have they do things other ways sometimes and I've got to figure out why they did it funny so we're going to go all the way to the right and we sometimes it's easier to make this vertically structured so I'm going to make a skinny sail over here it's harder to put the the headers sometimes but it's easier to do the calculation so for example if I went the periods periods and I can just go from one two and bring that let's let's start but let's do that one two I'm going to bring that down to a hundred right I'm going to bring it down to a hundred again just build in my table this way instead of the other way centering it and there we go and then we can say the dividends dividends are this way notice I'm getting kind of I don't have as much space up top the headers that's where the problem is here but it's usually easier to construct this way equals and I'm just I'll just pick up that 15 right there even though that's kind of cheating I'm going to say f4 on the keyboard dollar sign before the dg and the two enter and then what's nice about this is I can just double click right here and boom it takes it down which I couldn't do if I go to the right so that's kind of a neat thing and then we've got the present value present value same calculation negative pv shift nine rate I got to find that all the way to the left so I'm just going to hold the left arrow until I hit the wall going to the wall boom I almost knocked that two right out of the wall right there because I was had so much speed when I was barreling down I'm going to say f4 on the keyboard dollar sign before the b and the two we're going to say comma number of periods it's going to be that one because we're going to bring it back one year just like we did with this one over here same calculations except in a vertical format comma comma because it's not an annuity but present value of one future values that 15 we're going to bring that 15 back one year that brings it to the 13 or so let's add some decimals home tab find creep let's be more exact than even going to the penny so that we can then just double click this is why it's easier to do vertical double click boom and we could see if I discount that 15 back four years it's at the 950 and you could see of course again it gets quite small as we go down to 100 if I can total it up down here I could total it up down here equals the sum of all that 15s I'm going to copy that to the right and you get that 125 again 125 now you might be familiar with like using a hurdle rate with when you're calculating like capital projects and stuff like that to see so let's let's add one more little twist to this I'm going to put some underlines here home tab underline and let's make this blue and bordered home tab font group you're getting ahead of yourself again stop that wait for yourself I'm tired myself is tired but I don't want to wait for myself home tab alignment center this is going to be blue and bordered okay so let's let's copy like from the skinny to DK and then we'll do one little twist on it we're going to copy that and I'm going to paste that right here because I got the skinny coming with it so I'm going to say boom and then I'm just going to because you might you might be looking at this and figuring well look this series of payments if it's greater than zero that means we cleared the hurdle rate meaning if this is our rate of return 12 percent and I look at my series of future payments if it comes out and you look at normal capital budgeting kind of techniques if it comes out above zero then it would be something that cleared the 100 that that percent the 12 percent and therefore might be something worth investing in and the reason that it's the same thing really is because we have to put the outflow which would be period zero so in other words if I assume that we paid 125 for the stock I'm going to right I'm going to select these three cells right click and insert and move these cells down and then I'm going to format it with well let's copy the stuff at the bottom here and format paint at home tab clipboard format paint that right there and this is going to be period zero right in period zero I'm going to assume that we have we don't have a dividend but this is a payment that we have this would be a payment negative to show it cash outflow all the dividends are cash inflows right so if I if I say this is going to be equal to that 125 that 125 outflow we could put a little message here this is not a dividend this is an outflow this is let's put a note on it this is uh payment this is payment price that we have for the stock so we pay that I don't need to really present value it I could because it would be at period zero so I could just copy this up and it should be it should be well 125 it's the same okay so then so then if I copy that all the way down I'd say here's really what my cashflow will be this has to copy this has to take into consideration the 125 I'm just going to pull that up to the 125 now we paid or we received 1375 net after the 125 outflow up front copying that across and we get to zero in essence on on here because now we're showing all the all the activity here's my initial cash outflow here's all the inflows we get from the dividends the fact that it's zero doesn't mean that we didn't earn any profit in that case the fact that it's zero means we cleared the hurdle rate which was the 12 percent which is the rate that not interest rate that's not the interest rate that's the rate that we think we can get in a similar kind of investment so that's hopefully that kind of clarifies this 12 percent a little bit and you can and again I hope if you work if you work on other like budgeting projects or capital budget projects and stuff like that then you can you'll be using oftentimes similar techniques and that kind of hurdle rate how what that rate actually means can be a little bit confusing to people oftentimes