 Personal finance practice problem using Excel. Estimated stock, return on investment, assuming constant dividends and 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 to 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 predicting future return for a stock investment, which of course may be helpful for investment decisions. Noting that when investing in stocks, we're talking about a separate legal entity, a corporation breaking out their ownership into equal units being stocks. We're usually talking about publicly traded stocks, corporations traded on a public exchange. As we buy stocks, we usually are expecting a return in the format of two ways. One, the dividends, which is going to be the earnings that are distributed to the owners, which will be determined by the board of directors and management. And two, increase in the value of stock. We have to make some assumptions about that in order to think about what our future return will be. We will be assuming here that we have a constant growth rate and constant dividends. The second tab, the practice tab will have some free formatted worksheets here. So you can work the practice problem with less Excel formatting. The third tab, the blank tab will do the Excel formatting. If you don't have any of this, that's okay because you can start with a blank sheet. And if you do, I would then select the full sheet so that you can throw down the baseline formatting. Right click on that sheet and format those cells. I usually go to currency and then negative numbers, bracket and red, no dollar sign, no decimals. I'm not going to hit okay because I already have this. I'm just going to X out up top and then you can add your data on the left hand side. We're going to be focusing in on just this data up top. We'll do this afterwards, but you could just start with this data up top. Format the cells as necessary, for example, putting a percent format in here. Number percent formatting, making a skinny C column. We're good to go. So we're going to assume here that the dividends at your end are $7. We're going to assume that the dividends are going to be basically constant going forward. We're going to say that the stock price is $80. We know that because it's trading on the public exchange. So we're going to say $80 is the current stock price and then the constant growth. Now the growth, of course, is these are going to be the two assumptions that we're going to have to kind of figure out to try to determine what growth will be in the future, possibly thinking of this in a way similar to bonds, in which case we value bonds by looking at future cash flows, those being in the form of interest payments and in the maturity value. They're a lot more straightforward in bonds because they're determined by the bond itself. Using a similar kind of analysis for stocks, we've got to make predictions about what the cash flows will be. For example, the dividends, they could change in the future, but we're going to assume that they're going to be constant dividends. Remember that corporations like dividends to go up but not down typically. So they typically might lock in the dividends, for example. And then the growth, meaning the value of the stock going up, of course, would be an assumption as well determined in part on past history. The orientation of where the stock is at this point in time and our predictions in terms of what's going to happen in the future. Given that, let's calculate our rate of return, our estimated rate of return. So I'm going to say this is going to be the rate of return. And I'm going to make the cell a little bit larger for cell D or column D, the whole column's larger, not just a cell. I'm going to select from D1 to F1, D1 to F1. Go to the home tab font group and the bucket dropdown will make this black and then the lettering needs to be white. That's what we do with our headers. And so this is going to be the ROI if it was just the dividends. So let's assume we don't have the constant growth rate and we're just looking at basically the dividends that we're going to be receiving in the future to start out. And so the dividends, dividends, or yearly dividends, we're going to assume here are the $7. So we're going to assume a yearly dividends. We'd have to calculate or annualize the dividends if they're paid quarterly or something like that. We're going to have the dividends at $7 on the year. And then the stock price is $80. That's what the market price is. That's what it's selling for. It is what it is. All the stocks are the same. So if they're selling for $80, that's the market price. Let's put an underline here, font group and underline. If we think that we're going to have constant dividends of $70 and the stock price was $80, and that's the valuation we're going to be depending upon, then we would get an ROI for the just dividends, which would be equal to the 7 divided by the $80. And so that might look a little bit abstract right now, especially since it's zero. So we've got to percentify to recognize, home tab, number group, let's percentify so we can recognize that number. And then we've got to add some decimals if we really want to recognize it a little bit more. So that might look a little bit abstract. We'll try to prove that so you can get an idea in your mind or at least conceptualize it a little bit differently in a second. But then we're going to have the constant growth. So the constant growth rate is going to be the 5%. So the value of the stock we're also going to say is going to go up. We're not just getting the dividends. We think the value of the stock is going to go up by that 5% constant. Home tab, number group, percentify it. Let's underline it. That means that we're going to say the rate of return, rate of return that we might be able to compare to other investments, for example, would be the sum of these two to help us to determine our investment decisions. Let's go into the number group, percentify, add a couple of decimals there so we can recognize. Okay, so let's make this blue and bordered. I'm going to select these items. We'll go up top, home tab, font group. Let's make that a border. Let's put some borders around all borders. I'm going to hit the bucket, drop down, make it blue, because that's what I do. I make it blue. We're going to go to the more colors to do that down here. If you don't have the blue, that's the one. I'm in the standard color. We're going to go to that one right there. That's the one we use. That's what I use. Again, you can do whatever you want. I'm not telling you what to do. I'm not telling you can do whatever you want, but that's what I do. I'm going to make this smaller. Let's make this a little smaller. Okay, to try to understand that return, because now we can kind of compare that to other returns. That would be how we might use this. Let's try to think this through and say, let's make a skinny G column. I'll put my cursor on C, home tab, clipboard, format, paint, make a skinny C. Let's pretend we're just going to use an annuity, the ROI, just dividends, just the dividend rate calculation. Let's pretend that we're trying to value this kind of like we're trying to value bonds, where we're taking the future stream of dividends and trying to say that's going to basically be the price. But there's no maturity date. That's a problem. We have to assume that the dividends are going to be fixed. Whereas with the bonds, we know the interest rates are going to be fixed because it's in the terms of the bonds. We have to make those estimates. Let's first do that. And let's say, okay, let's do a negative present value type of calculation rate. And we're going to say then the rate, let's just use this 8.75 that we determined by taking the seven bonds per year divided by the stock price. And I'm going to say, comma, and then the number of periods. I don't know the number of periods because we don't know the maturity. There is no maturity. In theory, it could go on forever. But because of the time value of money, the later dividends are going to be very small. So I can just pick a fairly large number, like 100 years, for example, comma, the payment is going to be that $7. So if I had a series of dividend payments out into the future $7 at that return, 8.75, that's going to get us back to that $80, right? There's the $80 about, because we're kind of estimating because it's 100 years. We're taking 100 years out into the future. So that's kind of to justify this calculation to see if we were just thinking about it as a series of $7 dividends out, in essence, indefinitely into the future. Obviously, if I took this and I said it was a thousand years out, then we're basically exactly at $80, right? But then we've got this constant growth rate also of the $5. So we've got the dividends and this constant growth. So let's do the same annuity, annuity ROI with the dividends and the growth just to get, just to see what will happen and we'll see how this kind of plays in in a second. So if I said negative, present value, shift nine, and we took this rate that we calculated after we added in the dividends and the growth and then comma, number of periods, let's just say 100 again, comma, and then the payment, we're going to say, this is $7 on the dividends. Then if that were the case, we're going to value it at 51. That's going to be useful. I'll try to use that in a second as we try to expand this thought process out a little bit more. So let's make a skinny J. I'm going to go to the skinny G so I can make a skinny J. Home tab, font group, paintbrush, and then click right on the J to make it skinny. So let's say that we have our periods here. I'm going to put a total upfront and then I'm going to make our periods onto the right. So I'm going to say this is going to be one, two. We're going to buckle our shoe and then we're going to select those two items, put my cursor on the fill handle and drag it out 100 periods into the future. Just to think about this 100 periods into the future. Why that long? Because there is no maturity. We're just going to go out for quite some time. I'll make that black and white font group, making it black, white because it's a header. Let's center it. Let's make this black, white and centered, black, white and centered. And this one can be black and white and centered too. And then this is going to be the dividends, the dividends for each year, which we're assuming will be the same constant, which we're going to say is just that $7. Let's say F4 in the keyboard. So I can make it absolute. You only need a mixed reference, but an absolute one works dollar sign before the B in the one so that I can just copy that across, putting my cursor on the fill handle, copying that all the way across to 100 periods. 100 periods. And so then we've got the annuity. Let's do our calculation for the, let's say ROI, just dividend rate that we're going to use. So let's make this a little bit larger. And so we'll basically calculate kind of the prices. So if I used just the dividend rate on here, we're going to get to this $80 or an approximation of it here by proving this, using this rate, using that calculation. So I'm going to take that $7 a year from now. If I discount it back at this 8.75 that we determined ROI, if we just have the dividends and we'll use that to see, to take each dividend payment in the future and bring it back to the present to try to get a better conceptual understanding of what we're doing. So I'm going to say negative present value shift nine, the rate, I'm going to use this rate. I'm going to select F4 on the keyboard, dollar sign before the F in the five. So when I copy it to the right, it doesn't move to the right. You only need a mixed reference, but an absolute one works comma number of periods. I'm going to go up to the number one. I want to use that one because if I move it to the right, I do want it to move with me to number two, number three, number four, and so on. Comma, no payment because this is not an annuity. It's just the present value of one. Therefore comma again, future value is going to be that $7. I do want it to move to the right. So I'm not going to make it absolute or anything like that. So we got the $6. We can add some pennies, number of pennies. So if I brought the $7 back at the 8.75% to one period, it gets us to the 644. If I drag that all the way out, you can see that the $7 way out 100 periods out is quite small. Even if I add some more decimals like four decimals, it's, you know, less than a, less than a penny right out there. So we're going to say, okay, that's, that's pretty small. And so if I sum this up, then equals the sum of this future stream of payments a hundred periods out, we're going to get to around that $80 right around the $80. It's more like $79.98 because remember here, I used a thousand instead of a hundred, but that's kind of the general idea. If I add up the dividends over a hundred years, $7 dividends per year, we get $700, but takes a hundred years to get the $700. If I did the same kind of calculation, which was the ROI dividend and the growth, same kind of calculation, but I'm going to use then this number, 1375, for example, I could do the same thing, negative present value, shift nine, rate this time is going to be the 1375, F4 and the keyboard making an absolute, you only need a mixed reference, but an absolute one works, number of periods is going to be that one comma. We don't have a payment because it's not an annuity comma. Future value is the seven and enter. Let's add some decimals, number group, couple of decimals, put our cursor on the fill handle, drag that on out to let's make it four decimals. Do the same thing we did up top and drag that on out. Drag it on out to the right. And so there we go. And so there's that one. And then if I copy that down, that will give us our 5091. So what I'm going to do now is I'm going to assume that that 5091 is the price. If there was no dividend price, if there was no dividend is going to be the 5091. I'm going to add some decimals here. We're going to go home tab number group, add some decimals. So now we've gotten kind of to this number. We've gotten to this number. And then we're going to get back to this number again in a little bit different way. So I'm going to start at the 5091 here and assume that we have that constant gross that we determined to be 5%. So if this was my starting price then and it was going to be increasing by 5% in period one, it would be this number times 1 plus 5%, or 105%, 5% plus 100%, the 50.91 plus the 5%. So this is going to be 1 plus, where I'll just pick up the 5% here. I'm going to say F4 on the keyboard so I can copy it to the right, put some brackets around it. And so it's going to go up to here. Let's add some decimals, number group, add some decimals so it goes up 5% to go to the 5345. If I copy that all the way across, then we can see that we've got that increase that we're assuming in value or price and it gets quite large as we get out into the future. And then we're going to say that the increase, we can calculate a couple different ways. In period one, it's the change. So it's going to be the 5345 minus the 5091, adding some decimals, number group, couple decimals, number. You could take the 5091 times the 5%. That's going to be 255. Let's do it the first way. This equals this minus this 255. I'm going to copy that all the way across. If I copy it, for example, you got the change that's going to be relative. I'm going to copy that all the way across. So there is our increase that goes up as we go in the value in the price. So let's then take into consideration our dividends. The dividends are going to be the seven. The $7, I can copy that all the way across. And that's going to be constant all the way across. And we could say that that's going to be, I'm going to copy this summing and sum these two up. So these are our increases. This is the dividends. So these two represent the return that we're going to have in the form of increase in the value of the price and the stock. So I'm going to put an underline here. Let's put an underline across. And we might want to put another space below the 50. Now let's keep it like that. I'm going to put an underline all the way across here. So let's put an underline. Go into the home tab, fought group underline. And then we're going to say this will be the return. The return is going to be just the 255 and the 7, not the 5345, because we're taking the difference between those two. So I'm going to say this is the sum of the 255 and the 7. So there's our return. Let's add some decimals, number group, couple decimals, adding that across. And we'll just take that all the way across. Boom. And so there we have it. And so there's going to be our return. If I copy the sum again down here, so we could sum that up. And then if I take the present value, and I'll use this present value calculation of that stream of future value that we're assuming that we're having. Let's do that based on this rate, the 1375. So I'm going to say this is a negative present value, shift 9, the rate. The rate is going to be this 1375, F4 on the keyboard, because I want to be able to copy that to the right comma. Number of periods is going to be one comma. We don't have a payment because it's not an annuity. Two commas, future value is going to be that 955 and enter. And then I'm going to add some decimals. Let's add like four decimals, completely decimalizing it. And then we'll grab the fill handle and drag that to the right, all the way to the right, and boom, Chaka Laka. And so then I'm going to copy this down. And so there we got, again, about that 7099. So you can kind of play with these time value money calculations to better understand kind of this more simplified calculation and try to figure out, that helps you to kind of figure out what's actually happening and see the assumptions that are kind of being involved there. So that you can actually use that knowledge more applicably, I think, that way. So I'm going to then make this blue and bordered. Let's make this border blue. And let's make this whole thing border blue. Border blue. And home tab, making it font group border blue. Now we could do the same thing down here. Notice, again, you could change the data. Like I could say, let's make this 455 and 12. And I can see the data change up here. You can also make a relative change. I might copy the entire thing undoing that and post the same data down here so I can see the things side by side. If you do that, you just have to have your relative data set to the information up top and then I could just copy all of this information up top and say, well, what if I had this? I'll just copy this whole thing and I'll just make sure it's relative right there so it needs to start at the same point. This is the same three data set type numbers. So I'm just going to say, pasting, if I had dividends of four, price 55 and 12, now we're getting to the return of the 1927 based on that number. Here's kind of our proof calculations to the right. There's the 55 here. There's our 20. If I bring the calculations over here, we're at more like the 50, 90, 91 because we took it out a hundred, a hundred periods and that's not quite right because this $7 up top should be our new dividends. I made an absolute reference. So this needs to be this one, the $4. And one way you can kind of stop that from happening is instead of using an absolute reference, I could say this equals the one before it and that way it would have copied down relative and I can copy that all the way across then like so. And so now it's at four. So is there any other problems here? That was a problem. I've got to change this absolute reference. So this number right here, I'm going to say is this number here, absolute F4 in the keyboard. So I've got to adjust the absolute references, copy that all the way across. Okay. And then this one is picking up an absolute reference of that number. So let's change that to this number, F4 in the keyboard and then pick that up and copy it across. Copy it across. Okay. So that gets us to the 54 about this number looks good.