 Personal finance practice problem using Excel. Dividends and gain on investment 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, in essence, answer key. Let's look at it now. Information on the left, calculations on the right. We're imagining we're investing in stocks which is kind of like an ownership interest in typically a publicly traded company. We're usually looking for a return which could come in two formats. One, dividends, which would be the earnings, the retaining of the earnings that are being distributed to the owners similar to taking draws for example and a sole proprietorship or partnership. And then we have the increase in the value of the stock which we would realize at the point in time that we sell the stock. So we're gonna be calculating the dividends and the gains here. And so we've got the practice tab which has some pre-formatted cells so you can work through the practice problem with less Excel formatting. We got the blank tab where we're gonna be doing the Excel formatting. If you don't have any of this, you could just open up a new worksheet and just lay down your baseline formatting. I would do that by selecting the triangle up top, whole sheet, right click on it, format the cells. If this was a blank worksheet, then go to the currency. I usually go to brackets, red, no dollar sign, no decimals for my baseline formatting. I'm not gonna hit okay because I already have this. I'm just gonna exit out up top, then add your data on the left hand side, adjust any cells needed such as indentation and possibly decimals and then add a skinny C column and we're good to go. So we're gonna imagine that we purchased shares for 300 shares at a cost of common stock so we're just imagining we're purchasing company stock here and we had to pay broker commission of $6 to make that happen. We're doing that to get a return and we're getting a return. We're expecting generally a return in the form of dividends, distributions of the earnings and or an increase in the value of the stock. If the stock increases in value, then that's great but we haven't actually realized that increase until we sell it because of course the stock price can go up and down over time. So when we lock in that gain if it is a gain that's at the point of sale. Also you have tax implications that we're not gonna get into at this point. So then we're gonna go to the sold the stock a year later. So a year is a nice convenient time to imagine to think about what has happened over a year because oftentimes we wanna make comparisons between the returns over say a year's timeframe which is a standard timeframe to do so. So again, even if we didn't sell the stock after a year we could think about our unrealized gains that would be the increase in the stock price in kind of a similar way. But when we realize them that's when we sell them that's also when we have that tax consequence we could have tax consequences on dividends as well. And it would also impact whether or not we have the stuff inside an umbrella of an IRA but we won't get into that kind of stuff. So right now at least shares 300 we're gonna sell at a price of 47. So they went up in value and we sold them and then we had to pay broker commission of $10 to make that happen. The dividends per share for the year we're gonna be is $2.10. So let's first think about how much we got in terms of dividends to think about what we got from this investment. This was a good investment. We did a good job here, whoever's our broker. So we're gonna then let's make this a little bit wider and let's say this is gonna be the total dividend, dividend income. And I'm gonna make this black and white up top. So I'm gonna select these two and make our headers as is our custom black and white home tab, font group, bucket dropdown, black and white. And then we're gonna say the number of shares. I'm gonna pull this from our data set on the left hand side. I'll pick that up up top. We'll say shares are at 300 shares. And then I'm gonna pull in the dividends per share. Down here, this equals the dividends per share. $2.10 equals the $2.10. They paid us $2.10 and they had a significant increase in value. That's excellent. So we got the $2.10 pulling in from the data set. Note that pulling it in from the data set allows us to change the data, work in multiple practice problems, run in different scenarios. This is how you wanna set things up so you can do that kind of stuff. And then we're gonna go to the home tab, number, add some decimals. Let's put an underline under it, font group underline. And we'll just multiply this out and call it total dividend income. And we'll multiply this out. This is equal to up to the 300 times up one, the 2.10. And let's add some decimals just in case we're gonna say number group. We like to see the pennies, but there aren't no pennies. I knew that, but I'm put the pennies on. We're gonna make this a little bit wider here. Let's make this a little bit wider. Let's make this blue and bordered as is our custom. So we're gonna select these items, home tab, font group, drop down. I'm gonna use that blue if you don't got it. If you don't have it, home or more colors, standard. So you get the wheel, there's the blue that I'm gonna use right here. You can use any color you want. You don't have to use this color, but that's the one I use all the time. So we're gonna hit the drop down and all borders. Put some borders around it. Okay, so then let's do the cost to purchase. So now we're gonna say let's make a skinny F by taking that skinny C and copying it over so it's the same skinny. I don't want different skinniness. So we'll get paintbrushy and then right on the F. So we got a skinny F now. And let's say this is gonna be the cost to purchase the stocks. So now we're gonna think about this so we can kind of figure out the game. You could format these different ways, but let's first think about how much did it cost as to purchase the stock. So I'm gonna make this a little bit whiter. Let's make this black and white for our header. I'm gonna select from G1 to I1, GI1, G to I1. And then we're gonna go to hometown, font group, bucket, black, and then letters white. And let's make a subcategory cost for the stock because I'm gonna calculate the stock cost first, doing a subcalculation, then I'll tack on the commission that we had to pay for it. So we're gonna say the shares are gonna be equal to the number of shares, 300. So this equals then the 300. And the cost per share was equal to, I'm just gonna call it cost or pick up the cost and then equals the 14. Let's put an underline under the 14, font group and underline. And then this is gonna be the cost for stocks. I'm gonna just copy this. You could call it total down here, but I'm just gonna say cost per stock, get rid of the colon. This is my subcalculation total, which I'm gonna put on the outside this time and multiplying this out. This equals the 300 times the 14. We're at the 4,200. Let's do some indentation here. I'm gonna select these three and indent them by going to the home tab, alignment indent. I'll indent this one one more time. Alignment indent again. So now I'm just gonna tack on the broker's commission. Gotta pay the broker to make that one happen. So we're gonna say that's another $6, a whopping $6 on top of that. Let's put it underline, home tab, font group underline. And that's gonna be the, let's call it total cost to purchase the stock, multiplying this out equals the 4,200 times the, or plus these $6, or we could use the trustee sum function, which would be better. That's way cooler to use the sum function in this type of scenario. So let's go ahead and make these a little bit thinner, little skinnerize them a bit. We can make them a little thinner to give us a little bit more room. I could do that over here too, by the way, if you want. Let's make this blue and bordered. So let's go home tab, font group blue or bordered and then blue, either order of the border blue, border blue, blue bordered. Sounds cooler saying border blue, but you know, you don't have to do it that way just cause it sounds cool. You can do it the other way, even though it doesn't. Anyway, so then we're gonna take the total sales proceeds. Let's make a skinny J now. I'm gonna copy the skinny F, skinny F format paint that and put that on the skinny J, skinny F to skinny J. Now you could hide some cells. We can practice hiding some cells here. So I could say, hey, look, I'd like to work with the data set right next to what I'm doing. So I might go and say, let's go from skinny C to I, CI investigations. And then right click and let's hide those, let's hide those. Not delete them, don't delete them because they're still there B to J. You can see there's still cells in there, but we hide them and now I can work on this side by side without having to roam around. So this is gonna be the total, let's call this, let's say it's the total sales proceeds. So how much did we get when we sold the stock? That's what we wanna know. Let's make this a little bit wider and let's make this black and white on the header going from K1 to M1, home tab, font group, bucket, drop down, black and white. And so first we'll calculate the proceeds in a similar fashion on the sale of a stock and then we'll tack on the broker commission we had to pay. So we're gonna say it's the pro proceeds before commissions, let's call it that, colon. And so that's gonna be equal to the shares that we sold. We just sold the whole thing. We didn't have to sell the whole thing, by the way, you could sell some of the shares and not all the shares but we just said, hey, look, it's $47 from 14. It's time to exit this position, tell you what, we're gonna, we're gonna be out. So 300 and we're gonna say then the cost was up here at the 14, I'm sorry, I'm not doing the difference. I'm gonna say the sales price, the sales price was 47 equal to the 47. Let's put an underline under it, thought group underline and then I'll just copy this thing down here, copy, paste, get rid of the colon. Notice I can't double click over here to get rid of the colon, of course, I gotta be in the cell and then scroll over there to get rid of the colon and then we'll multiply this out on the outside. This is gonna be equal to the 300 shares we sold for 47 each times 47 gives us the 14,100. Let's do some indentation for our subcategories, selecting these three, going to the alignment indent and do it again, alignment and indent down here. Let's make column K a little bit wider. There we go, so there we go, there we go. Okay, so then we're gonna have, now we've got the broker commission. Let's tack on the broker commission that we had to pay to make this happen. This is equal to the $10, so we'll subtract that out from the proceeds. So the total sales proceeds after the commissions is gonna be equal to the 14,100 minus $10, 14,090. Let's put an underline here and let's make this black and white. Let's make this black and white, or not black and white. Let's make it blue and border. Thought group bordered and blue. Okay, so now we can take the difference between the two, the sales proceeds and the cost to get our gain, which is in essence from a capital gain, if we put in our income tax terms, which might be tax on capital gains rates, as opposed to the dividends, which might be subject to dividends rates, which still both being different, possibly than the ordinary income rates, but we'll talk about that later. We're gonna then highlight from B to K because I need to unhide those sales to get the difference between the proceeds and the cost. So let's put our cursor on B to K, right click and unhide, unhide, not hide, unhide. And then we're gonna say, let's put the capital gain way out here, let's make a skinny in, make the skinny J, format paint it, home tab, format paint to the skinny in. And then we're gonna say this is the capital gains. And let's make this a little bit wider and we'll make the top component here black and white, home tab, font group, bucket, drop down, black and white. And this is gonna be the total sales proceeds, which I could just say equals and pick up the sales proceeds, total sales proceeds. Let's make this a little bit wider. I'm just gonna double click on it this time, making it wider that way. And this is gonna be equal to the, the way to say that's the cost. Hold on, let's take the sales proceeds on top. That's gonna be the second one we did, sales proceeds. And then the cost is gonna be equal to the 14, the proceeds of the 1490 and then the cost. So the cost that we have here is gonna be equal to the 4206. And then I'm gonna say this is the capital gains. Subtracting those two out, this equals the 1490 minus the 4206. Therefore we got a gain of the 9,884. Now notice if I didn't sell the stock, I still could call it kind of a gain, but it would be an unrealized gain because the stock price could go down again. We're not triggering the same kind of tax consequences in that case, but you might think of it that way when you're looking at your returns on your investments, even though you haven't realized them. So home tab, thought group underline, let's make this black or not black and white, blue and bordered, bordered and blue. All right, and so then we could say, okay, what's our total returns on the year? Total returns that we got making this black and white, we can go to the thought group making this black and white would be the returns we got from the dividends, which they just paid us out dividends, which was nice. They just gave us flat out money that was earnings from the corporation. So that's good. And the value of the stock went up and we sold it. Therefore we've got the capital gains that we realized, otherwise we might have unrealized gains if we did not sell it, which would still be beneficial typically, but so that's gonna be the capital gains that we got. Okay, so then let's sum those up. Let's put an underline, font group and underline. This will be the total returns. This will be equal to the sum of these two. Wow, that was incredibly good. And now let's make that black and white or blue and bordered. And we're gonna go to the home tab, font group, border blue on that one, border blue. And then we got the return on investment we could think about return on investment, the ROI, which we usually think of over a timeframe of one year, which we have one years of returns here, font group making this black and white. And so if we got returns, total returns of the 10,514, and we invested the original investment that we put in place was the cost that we put in place here of the 4206. We got an ROI, font group, underline. We got a return on investment equal to the return 10,514 divided by the cost of the 4206. Let's make that a percent. We're gonna say that number group percentifying it 250%. Wow, let's repeat that process. Any case, let's go ahead and make this blue and border, blue and bordered. Now you could see this calculate, like these gains calculations done differently. The commissions kind of throw things off a little bit, but you might just try to calculate directly to like the capital gains, for example. So you might do something, you might format it a little bit differently. You might say, okay, let's put my end here and paintbrush that over here. And let's just say that we do this a little bit differently. Let's say this is gonna be my capital gains calculation. And I'll make this a little bit wider and I'll take these and say this is gonna be font group and we're gonna say black and white. So you might say that we've got the difference per share, sales price minus the cost per share. So in other words, the sales price per share is gonna be equal to the, what did we say, $47? And the cost per share is equal to, we bought them for $14. So the gain per share before commissions, let's call it that, gain per share before commissions. I'll put that up here too with a colon. And that'll be our total is gonna be the difference there. Let's put an underline here, font group underline. So this will be equal to the 47 minus the 14. So you could say, okay, I made $33 in essence per share on the number of shares, which is equal to, let's say shares, which are equal to the 300 shares. So now we've got the gain per share. Before commissions, which is equal to the 33 times the 300, and the commissions can be somewhat, might not be as relevant to that calculator. So you can get there fairly easily. Oftentimes you'll see this kind of calculation, but then you would have to take into consider the commissions. And so commission on purchase, commission on sale is gonna be equal to the six and the 10. And you might make another subcategory. Let's make that into another subcategory. I'm gonna say control X, put that here, just copy, cut, and then paste in. And I'm gonna say this is gonna be the commissions colon, and I'll do total commissions. And let's do some indentation here. Go into the alignment indent here, alignment indent again. And let's bring these to the inside. I'm gonna say control X and put them here, control V, underline, font group underline. So total commissions is gonna be the 16. And then we could subtract out here and say this is gonna be the capital gains. Game. This will be equal to the 9900 minus the 16. And so there we have the 9884 again. And you could also try to allocate the commission to on a per share basis, for example, kind of thing. But sometimes it's useful to see the calculation of this way to take the difference between on a per unit basis, the shares price sale minus the cost times the number of shares and the commissions kind of muddy the waters a little bit. So in any case, let's do some indentation here. Let's go to alignment indent. Let's do this one here, alignment indent. That looks good. We've got the number of shares that looks good. Let's put an underline here, font group underline. Let's put an underline here, font group underline. Let's make this black and white, font group black, white. And let's make this blue and bordered. So font group bordered and blue. So there we go. And let's make these, we can make these a little bit skinnier. If you want the S and the T, we can thin them up. If we wanna get picky, we can make these a little thinner probably, probably make these a little thinner. If we choose, why not? So there we have it. So that looks pretty good. This one needs to be a little wider still. That one still needs, cause I can't see the words all the way across. Okay, so there we go. That looks, that looks pretty good. Let's do a spell check on it, review spell checking. And there it is. Perfect spelling. Perfect.