 Personal finance, Excel, practice problem, even investment, stock portfolio calculation. 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 are imagining we're gonna be putting a fixed amount in in standard intervals, in this case on a yearly basis into stocks. Now note, as we go through this process, you can think of a simpler process as if you are investing instead of at a yearly basis on possibly a pay period by pay period basis, which might be bi-weekly, weekly, semi-monthly, monthly. And then you have a fixed amount that you're going to be investing, possibly in that case in something like ETFs or mutual funds. And then the questions we wanna answer is, well, what's the calculation of a total investment we would have in this case after four time frames or four periods, four years in this case? And what's the total number of shares that we might have at the end of that time, which will differ not as easy of a calculation as we might think at first glance because clearly, even though we're putting the same amount in every year, the price is going to change each year. So then we're gonna also take a look at what the average cost per share is. The second tab here is the practice tab. We'll have some pre-formatted cells so you can work the practice problem without as much Excel formatting. The third tab, we're gonna work on the Excel formatting in the blank tab. If you don't have any of this worksheet, you can open up a new worksheet. I would then start by formatting the worksheet. I would do that by selecting the entire worksheet, possibly with the triangle, right-clicking on the selected area, format the cells. I'll usually start with the accounting or currency, brackets and red for the negative numbers, no dollar sign, no decimal. And then I'm not gonna hit okay because we already have this, I'm gonna say X out. Then enter your data on the left-hand side. Just enter the data, make a skinny C column and then we're good to go. Okay, so the information on the left, we've got the investment in company stocks. We're gonna imagine we're investing in one company stock, but remember, you could expand this to investing in, say ETFs, which are basically gonna help you to diversify over a large portfolio, for example, in that fashion, but we're gonna say the stock and we're gonna invest 7,000 each period. So we can imagine basically at the end of each year, let's say, we're gonna invest 7,000. Remember that you could apply the same strategy, this is a common strategy for people to apply when investing, say, for retirement in that they might just take some money out of their paycheck, whenever they get paid, that might happen more likely on a weekly basis, semi-weekly, semi-monthly, bi-weekly, and you have a similar kind of process or similar kind of thing that will be happening with that kind of investment strategy. So note that in the first year, we're gonna say that we have the cost of the stock is at $86, and then in year two, it's gonna go down, so now it goes down to 74, and then in year three, X6 in this case, it's gonna go to 57, and X7 to 77. So when we're putting the same amount of money in, we're gonna be buying at the peaks and troughs. And remember that when you're thinking about the investment strategy in the short term, you're gonna have these peaks and troughs, and you would like to be buying at the bottom, because we'd like to buy low, sell high, but in the long run, we would hope that there's an upward trend, and so if we're long-term investing, a strategy might be that we're just gonna put money in every period, possibly whenever we get paid, and let it ride from there. I'm not gonna try to guess the short term peaks and troughs would be one common strategy to use for individual investors on a long-term time horizon. But let's see what the impact of this would be. So let's see what would be our total investment after the four periods. We've got the total investment, and let's make this column a little bit wider, and let's make this black and white up top. So we're gonna go to the home tab, font group, make this with a bucket. Let's make that black and the lettering white for our header, and then we're gonna say that we have the investment per year. Investment per year, it's not gonna change each year, it's just gonna be 7,000 each year that we're gonna put in. We can imagine we put it in at the end of each year. So we're gonna say the years then that we're covering here are four years that we're thinking about, and that's gonna give us a total investment after four years, which will be equal to, of course, 7,000 times four. So after four years, we will have put in 28,000 would be the idea. I'm gonna make this blue and bordered up top, home tab, font group, brackets. Let's make some borders, and let's go to the font group, bucket, drop down. There's the blue I usually use. I'll go to more colors if you don't have it. It's in the standard color wheel. There's the blue right there. So there we have it. So now we're gonna think about the number of shares that we're gonna have. Let's make a skinny F column. So I'm gonna put my cursor in the C here and make a paint brush it, home tab, paint brush. So we can just brush the F column. One stroke, one brush stroke down and the whole thing's paint brushed. So now we got the number of shares. And so I'll start with, I'll have the year. Let's put the headers here years, investment and then the price and then the shares are gonna be our headers. Let's make the whole thing black and white. Let's choose the whole thing here. Go to the home tab, font group and make it black and white. And then down on these headers, let's center those ones. So let's go to the alignment and center those. This one needs to be a little wider. I'm just gonna double click on the H to do that. This one can be a little smaller, I think, the years. And we'll just pick up the years X4, X5, X6, X7. So this equals the, this, I'm gonna copy it down, I'm gonna copy it down, put in my cursor on the fill handle, copy it down. And so there we have it. So that looks good. The investment that we're gonna put in each year is gonna be equal to 7,000, which is this number. I could select F4 on the keyboard making an absolute dollar sign before the B and the two. You only need a mixed reference, but an absolute one works so that I can simply double click on it and it'll copy it down. You can also, oftentimes I'll just delete these and say there's the first one. Here's equal to one above it. You can also use spill, but I'm just gonna do it this way. I'm gonna copy those down. So that's another way you can commonly enter that. And then we're gonna enter the price. So the price, this is what changes each time frame, right? So we started off in X4 at 86 and then the price, if I copy that down, let's just double click. It goes to 74 and then to 58 and then to 77 is the fluctuations within the price. So if I think about how many shares we're purchasing, even though the investments stay the same, we're investing, we're buying a different number of shares each time. So I could say, okay, let's take the 7,000 divided by the 86, that would give us 81. If there was a fraction, if we can get a fraction, it would be 81.40 shares. And if I do that all the way down, this is gonna be this divided by the 74. We're putting 7,000 divided by 57 and this equals 7,000 divided by 77. We could of course copy that down as well. We can delete these for example and just double click on the fill handle to do those calculations. So even though I'm putting in the same 7,000, I only got 81 shares, around 81 shares in X4 because the price was higher and then the price actually went down, which isn't good for our investment. So notice, there's pros and cons to the, when we look at our investment strategy, we would like of course the investment to go up. The current shares that we have, the 81 shares, we would like them to go up. We lost money if they went down, but now that might be a time for us to buy, right? Because now at the end of the second year, we were buying at a lower price because the stock market went down. So we're putting in the same 7,000, but now the price is 74, so we get to buy more shares. So notice how the shares are being differentiated even though the amount we're putting in is the same. And instead of us trying to pick when the floor is, we just keep putting money in every period. In this case, every year, you might be doing it same kind of concept every week when you get paid or every two weeks. And then it went down further to 57, so now you got 7,000 and we invested again, right? Because now we're investing into it going down. We got a lot more shares, and then it goes back up to 7,000, goes back up to 77. So we bought the 90 shares, 91 about. So the total number of shares that we got is gonna be equal to the sum of these items. I'm gonna say let's go up top and add some decimals. So obviously, especially if you think about this on a week-by-week basis, you might say, well, man, if I really knew what I was doing, I wouldn't have bought here and I would have waited to the following week until it got down to here, and then I would have purchased all these shares at 7,000. I would have purchased using these three months, buying them all at 57, buying them at the floor, right? And then when it goes back up, then I don't buy when it goes back up, right? I buy at the dips, that's what I'd like to do. However, of course, no one knows when the dips are, and if you're trying to outbeat the market as an individual investor that's not spending all their time trying to figure out where the dips are, that's usually a problem. So most of the time, oftentimes for individual investors, you might use a strategy like this except investing per pay check, for example, and that means you're investing at the peaks, possibly, but you're also investing at the troughs, so you're probably gonna be averaging out over the long run because you're not looking to make the gains on the short-term peaks and troughs, but rather you're trying to be a long-term investor and saying, I'm gonna take advantage, I'm gonna hit the peaks and the troughs because I'm investing as a standard kind of method and I'm a long-term investor, that would be kind of the general idea. Now you could come up with some more complex methods to investing if you wanted to and try to figure out, you know, try to invest more when the stock is basically going down and use some different strategies that we might take a look at in future presentations to apply there, but that's the general idea. I'm gonna select these, we're gonna go to the home tab, font group, border and blue. So let's think about what the average cost per share is then. So let's make another skinny column over here, I'm gonna make a skinny K by going to the skinny F, home tab and paintbrush to the skinny K and we'll call this the average cost per share. Average cost per share, I'm gonna make this a little bit wider and then we've got the total investment. So the total investment we made over the four periods, four years in this case is the 28. So I'm gonna say this equals to 28, that's how much money we put in. Let's make the header. We're getting ahead of yourself. You're getting ahead of yourself. Slow down so you can catch up to yourself. I'm gonna go to the home tab, font group and make this black and white and then the total number of shares is gonna be here. So this is the total number we have after the four year time frame. Let's make this a little bit wider and let's put an underlying home tab, font group, underline. So that means the average cost per share is equal to the 28,000 divided by the 390 about and we can add some decimals maybe on these two. So there we have it. So you can see the fluctuation in the price after the four year time frame, the average cost is at the 70, the 71.85. And again, as more time passes, you would think if you're investing over like 30 years or something like that, we're hoping the trend in the market is an upward trend. And then we're gonna see these peaks and troughs when we look and zoom in on the market, which people, it's inevitable you're gonna do that and then stress yourself out on the peaks and troughs. But usually for many investors that are using a kind of a fixed strategy of investing for retirement, for example, they're trying to be a long-term investor and they might simply be causing them self-stress if they create a strategy and then agonize over it by every time there's a peak or the market fluctuates. But in any case, we're then gonna make this, we're gonna make this, that's part of investing. So whatever, gotta agonize over every move. But you don't really have to, but in any case, let's go ahead and review, let's check this out. So there we have it, spelling looks good. Let's put an underline here, home tab and underline. So that's the general idea. So we're gonna have the same investment will result in us buying different number of shares per timeframe of the investment based on the market price. And if you just put a fixed amount in per interval, then you will be on the short-term buying at the peaks and the troughs. But hopefully you won't pass up a good time to buy, even though you might hit some bad times to buy and you're looking for that long-run trend typically.