 Personal finance practice problem using Excel stock price calculation assuming constant growth and dividends. 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 answer key let's look at it now. Information on the left calculations on the right we're calculating these estimated stock price possibly being used in a situation when we're trying to determine and compare it to the market price to determine if the market price is under or overvalued possibly to help us decide whether or not we should be buying or selling a particular stock. We're using a similar method as we use with bonds meaning we're trying to value the future cash flows and present value those cash flows to determine the price something easier to do with bonds because of the fixed nature of bonds we have the steady payments that are going to happen in interest payments and we've got the amount that we're going to receive at maturity with stocks we have two kinds of value that we might be looking at one flows in the future of dividends which may change depending on the corporation and what they determine the dividends to be as well as increase in value of the stocks and we don't have a set ending point in terms of maturity so we have to make some assumptions if we're going to value the stock based on future cash flows the assumptions we're going to make here are that we're going to get a constant dividend and we're going to assume a constant growth rate in the value of the stocks and note the assumptions you make might differ depending on the types of stock you're looking at if you're talking about very secure stocks that are large stocks like utilities already established then they might not be growing a whole lot but might have set dividends other stocks that are still growing at a stable rate you might consider here this strategy where we're going to say we think the rates going to be constant into the future and we think the dividends are going to be constant into the future you might see other stocks that are putting a lot more money in terms of increasing in the in the value of growth and not giving much in terms of the dividends there's usually more uncertainty with those kinds of stock given the fact that they're in that kind of growth phase the second tab here is going to be the practice tab where we have some pre-formatted worksheets you can work through the practice problem with less excel formatting the third tab link tab we're going to be doing the excel formatting if you don't have any of this that's okay you can open up blank excel worksheet I would then start by selecting the whole worksheet possibly with the triangle right clicking on it formatting the cells laying down the baseline formatting I usually start with currency the negative numbers bracketed and read no dollar sign no decimals I'm not going to hit okay here because I already have this I'm going to X out of it then enter your data on the left which includes this that's where we're going to start here we're going to have some other kind of scenarios down below but you can start with just this data on the left formatting the cells as needed like putting a percent here for these two items percentifying them in the number group making a skinny C column then we're good to go the assumptions we have common stock price with stable growth so we're going to say that the annual cash dividends are $10 so we're going to annualize the cash dividends assume them to be $10 stable growth rate we're going to say is 7% we're going to think that the value of the stocks going to go up by 7% in other words required rate of return for the common stockholders 20% meaning this is the rate that we need to clear kind of like the hurdle rate for example the rate that we think we can buy similar stocks and get a return for meaning we're taking into consideration risk and so on as we calculate what we think other stocks or what the with the kind of like the hurdle rate would be what we can get in other investments to compare this one too all right so first we can do kind of like a quick calculation with it and then we'll try to explain it by mapping out the future payments so we can see it in context and hopefully get a better conceptual understanding so we got the price here that we're going to be calculating I'm going to make make the first component so the first couple sales black and white home tab font group we're going to go to the bucket drop-down make this black and the lettering white and I'll make column D a little bit larger whitening that up we've got the annual cash dividend I'm going to put this in the outer cell because I'm going to have a sub calculation below it and we're going to say this is $10 so $10 we're going to think is the constant dividend and then we're going to be calculating the rate of return I'm going to call it return after growth for our sub calculation and so we've got the required rate of return required required rate of return that's not how you spoke I got two letter return return this is going to be equal to 20 so overall we want a 20% return and I'm going to go up top number percent that's because we think we can get that kind of return in similar types of investments taking under consideration risk and so on but we think some of that return is going to be a stable growth so we've estimated that the stock possibly based on past trend analysis and so on into the future we think there's going to be a 7% growth going forward in the value of the stocks I'm going to go to the home tab number percent of five at let's go to the font group and underline it and so therefore the return return after we consider growth in the stock for the dividends is the difference I'm going to take the 20% minus the 7% percent to find the cell home tab number group percent of five so we could recognize and then we're going to go that's going to give us then the price which will be equal to the 10 up top time or divided by I should say 13% that's going to give us about 77 adding some pennies home tab number group adding some well that's not the pennies that's not what I want to undo that I'm going to add some pennies to it so about 7692 for the price let's make that blue and border so I'm going to select this whole thing here we're going to go up top and say this is going to be home tab font group border it and make it blue as well border blue we can also make it a little bit more skinny for columns E and F let's make them a little bit more skinny skin arising them a bit so we got some more room for other stuff let's make column C a little bit more skinny so now let's see if we can let's make an underline here for some formatting make it look a little nicer okay that's that's good for the formatting move on okay so now let's see if we can understand this a little bit so so for example if I took let's make a skinny column on the G I'm going to go to the C here go to the home tab and paintbrush and make a skinny G and let's say like I could understand this like with the annuity calculations remember we're talking about a series of payments that are going to happen basically out into the future so if I take for example just just the the amount of dividends that we're going to be receiving at the return of the 13% we can use a similar calculation that we saw in a prior presentation so it could look something like this negative present value shift 9 the rate I'm going to say is the 13% after considering the stable growth of the 7% I'm going to say comma number of periods is going to be I'm just going to assume it we don't have a maturity date it goes on basically forever I'm just going to pick a big number like 100 for example because we know as we get further out into the future and we present value those dollar amounts they're going to be worth less and less so even though it keeps going out forever they're going to be worth less and less due to present value and then comma the payment that we're going to have is going to be the dividends of the $10 and that's going to give us our our 76 home tab I'm going to go to the number group and 7692 so that's another way we can kind of calculate that future component to hopefully get a better grasp of this let's do it let's try to map it out now in terms of a series of payments so I'm going to make I'm going to make this blue and bordered see if we can understand this a little bit more I'm going to make a skinny J column putting my cursor on skinny G home tab and paintbrush to go to the skinny J skinny G to skinny J they sound like they're related or something skinny G and skinny J have their their brothers I think any case so we're going to say the total is over here and then I'm going to put the numbers up top one two for our period so let's take it out a hundred periods which is a little bit excessive but that's what we use in our annuity calculation so let's bring it all the way out a hundred periods hundred periods you have it's counting down for us there we'll bring it out to a hundred which goes out to DH and before I click on anything else I'm going to format those sales home tab and let's go to the font group and make that black and white and let's center it to center and then let's do the same thing here black white and center and so then we're going to have our dividends dividends I'm going to make this a little bit larger and say the dividends are going to be equal to in period one ten dollars all the way across I'm just going to pick up the ten right here in our in our little calculation I'm going to say F4 on the keyboard to absolutize it you only need a mixed reference but that'll allow me to copy it to the right so I'm going to take that I'm going to grab it on the fill handle and copy it all the way to the right copying it all the way to the right okay and then I'm going to do our present value calculations and let me just fix this annuity real quick I'll do this annuity a couple different ways this annuity up top I'm going to show that we use the return after the growth rate to calculate that one and then I'm going to copy I'm going to copy this down and I'm going to say this is an annuity calculation but this time we're going to use the required rate of return I'll just call it ROI ROI and let's do the same thing with this 20 percent instead of the 13 negative present value shift 9 the rate I'm going to be picking up the 20 percent F4 on the keyboard I don't really need to say F4 I'm just going to pick up the 20 percent and then comma number of periods I'm going to say a hundred again just picking a random large number and then comma the payment is going to be then the $10 on the dividends and that's going to give us around 50 I think 50 exactly or pretty close home tab number adding a couple decimals let's make that blue and bordered now that one will help us out or we'll tie that into the same thing we're doing over here in a second here so this number right here the 7692 matches the price this will see how that kind of fits into our calculations as we map out the future payments in a second so we're going to then say let's do the present value same same kind of thank you I'm going to call this present present value are we using the return after growth and I know I misspelled it over there I'm going to set I'm going to make that a little larger and this is going to be the present value present value using the ROI so we'll do it a couple different ways ROI so we'll start here basically recalculating this 7692 but we'll do it on a period by period basis taking the $10 of each period and bringing them back to the current period so I'm going to say negative present value shift 9 the rate is going to be here the 13 I need to make that absolute so I can copy it to the right without it moving F4 in the keyboard you only need a mixed reference but an absolute one works comma number of periods I'm going to say is one because we're discounting one period into the future back comma comma again because there's no payment it's not an annuity but future value calculation and then we pick up that $10 and enter so there we have it let's add a couple decimals so we're going to bring it back and it's around let's bring a bunch more than pennies it's 8.8496 let's pull that all the way to the right out a hundred periods and you could see of course as we get out into the future as we saw in prior presentations with the other assumption of just dividends the future dividends although they could go on forever get quite small and therefore aren't going to have as big an impact on our price calculation if I sum this up then summing this up that's going to give us our 70 77 if I add a couple decimals about 7692 that we got here and here okay so but now let's kind of assume that that 10 was all we're gonna get and figure and we'll get this ROI calculation as if we're assuming that we didn't get the stable growth of the 7% just to get a better idea of kind of the future stream of revenue that we're gonna get in a sense or return that we're gonna get so let's do the same calculation and we'll try to get to this $50 use in the other growth rate so I'm gonna say negative present value shift 9 we're gonna say the rate now is going to be the 20% required rate of return not considering the stable growth rate and then I'm gonna say f4 on the keyboard dollar sign before the e and the 4 comma number of periods is going to be the one that's going to copy across as we go across comma comma to get to the future value that $10 discounted back at 20% instead of 13% I'm gonna add some decimals number group adding a couple decimals is is a little bit lower of course because now we're discounted it back 20% instead of the 13 if I copy that all the way across all the way across to a hundred periods we're gonna get to to and then sum it up over here I'm gonna copy this down copy that down which sums that up there's our $50 so there's our 50 that we calculated here so now to get it to get a sense of what's happening on both of these future cast streams let's try this we're gonna say okay what's happening here is I'm getting a dividend future cash flow and I'm getting a return on the investment which is a little bit confusing so let's first start with the price assuming no dividend if we as if we say the price assuming no dividend starts at this $50 I'm gonna start at that $50 and then I'm gonna say okay the price is gonna be increasing by the stable growth rate of 7% that we're going to be receiving right so we're gonna get a 7% stable growth so to calculate that that would be equal to the prior rate times 7% plus 100% so I gotta say brackets I could do that this way one or 100% plus the 7% closing up the brackets so we're taking 50 times 7% plus the 50 or 50 times a 100 and 7% or 1.07 and that's gonna give us the 53 I'm gonna do it again because it's a little bit different formula here because now we've got we've got this as the prior number so I'm gonna say this is gonna be equal to the prior the prior calculation in the prior period and then it's gonna increase 7% each time so I'm gonna say okay that's gonna be brackets one plus the 7% I'm gonna say F4 on the 7% over here because I want it not to move to the right as I copy it F4 on the keyboard dollar sign before the E and the 5 bracket that and so that hold on a second I missed the multiplication sign times the one plus that number and enter so now I should be able to copy this all the way across and we can say okay the price is gonna be increasing at that 6% as we go all the way out here which means it's gonna be increasing more and more as we go so let's take a look at what that difference looks like then so if I started over here and I say there's gonna be an increase in price so an increase in price not of the value of the stock we're gonna say it started out as an increase in period one is equal to the 53 minus the 50 you get the three let's add a couple decimals 3.5 or you can say it's gonna be the 50 times the 0.0 set times that we'll take it up over here times the 7% right the 3.5 you could do either way other way you want to see it but I'll take the difference calculation this minus through this I'm gonna copy that all the way across so notice we don't get a constant number because we're assuming a growth here that's happening so that means you know as we get out into the future that difference is becoming more substantial right obviously because the value of the stock we're assuming has that constant growth rate which is acting different than the dividends which we're saying are the same all the way across so then if I if I think about that and I have my dividends which are which are the dividends I'm just gonna copy over the $10 I'll say this equals the 10 and I'll copy that all the way across copying that all the way across to 100 and let's actually make a little space between these two because these two represents the increase the increase that we've got this one just represents where the price is so let's put a space above this column just so we don't get that kind of confused so I'll select this all the way across and I'll put my cursor on it right click and insert and I'm gonna shift these down I'm gonna shift them down so now we got this kind of space in between so now these two represent our our return and the form of dividends and then increase in the value of the stock so I'll sum that up equals the sum of these two we'll copy that across copying that across so we'll copy that all the way out summing it up let's put an underline on this all the way across so I'm gonna take this and put an underline on it taking that all the way all the way here underline okay I'm gonna go all the way back here again let's do this now that's I'm gonna go all the way back here again sometimes this is easier to do vertically but then we'll sum it up which I'm just gonna I'm gonna copy these summings I'm just gonna copy that I'm gonna paste it all the way down here so I'm gonna make this a little bit larger larger so so there we have it so so there's the total increases we have there's the the dividends which I could copy that would be the same up top here the dividends up top there and so we can we can recalculate this this plus this we should get the same number here I can put an underline home tab font group an underline okay so now let's take the present value the present value of the full return using using our our full ROI which is the 20 percent because now we're valuing the full future return we're going to get here we're saying okay we got the dividends ten dollars into the future plus this increase in the value each period into the future so then I can present value the full 13 bringing it back using my full 20 rate of return instead of the 13 percent negative present value shift nine the rate is going to be the 13 percent I'm sorry the 20 percent F4 on the keyboard because I want to be able to copy it to the right comma number of periods is going to be that one up top and then comma no payment because it's not an annuity comma future value is going to be that 13 and enter and then I can pick up that 11 and copy that all the way to the right all the way to the right and so you can see then it gets quite small again so I'm going to make this decimalized decimalizing it and so you can see even though these these are increasing because we have an increased return it's still going to become quite small as we go way out into the future right so we still get a fairly small number less than a penny you know a hundred a hundred periods out even though the total dollar amount when I go way out here is a few thousand dollars so that so so that allows us to kind of say okay that the the price is still if we go out further enough into the future if we present value all of them you know we could kind of present value even though in theory it kind of goes out into infinity generally so I can then copy this down present value and all that and once again we get to about that seventy seventy six ninety two that we calculated here so all that to kind of kind of verify this this type of calculation here so if you were able to to get an accurate rate of return against other stocks if you were able to guess what the future growth rate was and it was stable at the seven percent and if you were able to determine and guess that the dividends were going to be consonant which I misspelled there at the ten for forever out you can kind of do this kind of calculation using those types of assumptions and then you can kind of work it and understand it yourself and see what you're actually doing by thinking about the future time value money calculations a little bit more more in depth and actually mapping it out for a significant number of years into the future to see conceptually what you're actually kind of doing on it once you've mapped all this out then you can change your numbers over here and let's say this was like like 30 percent or 15 percent or something like that and that's going to change that's going to change your your numbers so let's undo i'm going to undo that i'm going to i'm going to blue and border this whole thing it's blue and border this whole thing so we're going to go up top and say home tab font group blue and border and then let's if you had a couple other calculations we'll just do some comparisons on down below and i'll try to copy them down so that we can see how we can kind of we can kind of copy this down to another data set down here so for example if i have my data set down here and i kind of line it up in the same format so i take this whole thing from here all the way out i'm taking this all the way out and i'm going to copy it down hopefully i went down far enough to like here maybe and then so that works and then copy that and then if i as long as i lined it up because i have the same data set down below it's got to be lined up to right there about because because this 10 right there needs to be a relative same cell which is right there i think so if i control v all the way across then we can run a whole another data set by just copying down the data set in that way i can have two side by side kind of comparisons right so if i've used it if i used the 12 annual return the stable growth at the 25 required ready to return we get to the 70 59 we can do our annuity calculations here and then again we can map it out this way you could also you could also change this by just taking this data and pasting it up top right uh pasting values only let's say well hold well this is it's because this this data is coming from the data up top but in any case you can copy the data up top and you would change everything up here right i can make this 12 i can make this 8 and 25 or whatever and that would that would make this one change because it's based on the one above it you could do it that way but you might want to try to copy the data down so you could see them side by side and then kind of toggle between the two and see what's happening so i'll do that one more time if i if i take all of this data all the way to the right for example and then i have a similar data set right underneath it i just got to line it up so that price needs to be right there because the first calculation is here relative to that cell control v and i didn't line it up quite right i think it needs to be one so up right there control v there it is so i'll make this a little bit wider so now we've got the 100 right that's pulling from the 15 we've got the 20 minus the 5 gives us our 15 there's our 100 recalculating here recalculating here and then again you could see the impact mapped out this way and now you can kind of go between these these calculations and you can try to see what the what the impact would be as you kind of adjust these assumptions on the left hand side which you could do individually or you can you could try to copy the whole thing down and then try to be able to compare them more easily uh side by side or top to bottom as they are here let's do a spell check because i know i misspelled a bunch of stuff here so we're gonna say return return return dividend return dividend change all of them change all of them shut them all off okay i think that's it also note that when we copy the data over it will not work as well when we're using kind of like absolute references so some of the items over here when we try to expand out the calculation when we're using absolute references in the calculations you need to go through and kind of adjust those so i'm not gonna i'm not gonna pick up and do that right now because we're over time i'm just gonna delete the data down here and you could kind of recalculate it if you so choose i'm just gonna delete this and i'm gonna delete this data here and so we won't go through that right now and then but these items again you can kind of copy down because they're not using the absolute references as we can see there