 Personal finance practice problem using Excel, mutual fund gain loss, dividend income and capital gain distribution 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 tab, in essence, answer key. Let's look at it now. Information on the left, calculations on the right, imagining an investment in a mutual fund, calculating the different forms of gains and losses we might have from an investment in a mutual fund, which might be the gains and losses from selling the mutual fund, dividends from the mutual fund and capital gains distributions from the mutual fund. We'll talk a little bit more about those shortly. The second tab, the practice tab, having some pre-formatted cells on the right-hand side so you could work the practice problem with less Excel formatting. The third tab, the blank tab, we're going to do some Excel formatting. If you don't have any of this, that's okay. You can open up a blank Excel worksheet, add the data on the left. If that's what you choose to do, then I would first select the entire worksheet, possibly by selecting the triangle up top, formatting it by right-clicking on it, formatting the cells to lay down the baseline formatting. I typically go to currencies and then remove the dollar sign and the decimals. I'm not going to hit okay because I already have this. I'm just going to X out of it up top, then add the data on your left, widening the cells as you need to, changing the cells as you need to, such as decimals here, which you would go to the home tab, number, add some decimals, make a skinny C column, and we're good to go. First of all, we're thinking about investments in a mutual fund. Remember that's similar but different than investing in individual stocks, individual stocks representing an ownership interest in a corporation. Corporations being separate legal entities with their ownership interest broken out into even shares or stocks. We're also typically thinking about publicly traded companies, those traded on a public exchange. When we look at a mutual fund, we're now thinking about pooling our money together with other investors under the fund, which then has to diversified with those broader base of funds to have a broader base of investments such as stocks and possibly bonds and so on and so forth, in accordance with the rules of the mutual fund. When we're investing in the mutual fund and trying to track our investment, it can be a little bit similar or similar in many ways to kind of tracking how we might break out our investments for individual stocks because we still can break them down into kind of standardized units of the fund. So it's a similar nature, but you want to keep a mutual fund separate in your mind than investing in individual stocks. Okay, so we're going to imagine here that the shares purchased are 400 shares that cost per share $20. The other kind of income that you might have, so if we were just to purchase the mutual fund for example, we might get income such as dividend income because they might be invested say in stocks and those stocks might distribute dividends. Remember that dividends represent the earnings of the company that rather than putting back in the company, they're given to the owners, the shareholders to us on a per share basis. So we could get dividends. We also might have capital gain distributions per share. This can be a little bit confusing because you might say, hey, the capital gain calculation shouldn't be there until I realize the gain when I sell the shares. But remember that these are not shares in stocks, they're shares in a mutual fund and the fund manager within the mutual fund will be buying and selling stocks possibly and they could then result in a capital gain within the fund which would then be income to the owners of the mutual fund. So even if you don't sell the fund, you might have some capital gain distribution. These two things being reported to individuals on like a 1099 for example and then also be reported to the government. So you'd have to pay taxes on it if you're not under the umbrella of like a retirement plan. Remember if you have your mutual fund under an umbrella of a 401k or an IRA, it gets a little bit more confusing there too because then these kind of incomes might be deferred until you pull the money out. So it's the same kind of thing, mutual fund inside the IRA or 401k as outside in the instrument that's being used as typically a mutual fund. But if it's under the umbrella of an IRA, you're doing so to get a tax benefit to defer income such as the dividends and the capital gains until the point in time that you pull it out in basically retirement. Also note that you might have interest income too if you're invested in bonds, similar kind of thing, you might have interest income that you'd have to report. Okay, then imagine we sell the shares of the mutual fund at a price later and then we got the gains just like we would with the stocks for selling the shares. So let's first think about just the buying and the selling of the stocks and the gain that would be resulting in that. That's going to be resemblance of what would happen with buying and selling stocks, for example. And then we'll add that to the dividends per share in the capital gain distributions to think about our total earnings. So we're going to say, okay, look, we got the cost. Let's start with the total cost. When we purchase this stuff, how much does it cost to purchase? And I'll make this sell a little bit wider. And let's make D1 to E1 our header setup, which is by going to the home tab font group bucket drop down. We're going to make that black lettering white. So there we have that. And then I'm going to go down. We're just going to say this is equaling to the shares. I'm just going to pull that data from our data on the left tab. This is going to be equal to the 400 shares. I'm going to hit enter this time. This is going to equal the cost per share tab. And this is going to be equal to the $20 and enter. Now note, you could do that a little bit faster, possibly, but I'm going to delete these two and delete this one. Just taking the relative references. I'm going to make this sell a little bit wider. Putting my cursor here and dragging the fill handle to the right, for example. And then it pulls in the relative cell reference dragging the fill handle down. And it should pull in the relative cell reference on down. Okay, and then I'm going to make this add some decimals in case I change the decimals in my data. Up top, home tab, number group, adding a couple decimals. Let's underline it, font group, underline. And this will be the total cost. The total cost equal to 400 times 20. Let's add a couple decimals just in case we need it later. Home tab, number group, decimal, decimalizing it. I'm going to make that blue and bordered because that's what we typically do around here. Making this selecting those cells, home tab, font group, border drop down, old borders, bucket drop down. We're going to that blue. If you don't have it, send the more colors. Send the standard. And we want that one. That's the one I'm picking in any case. You can pick what you want. You can pick what you want. This is going to be the sales proceeds. And I'm going to make these two black and white up top from D6 to E6, home tab, font group, bucket drop down, black and white here. Now this is when we sell them. Remember that you don't necessarily have to sell them to say that you have an increase in the value. So you could still calculate this and it would be like unrealized gains because you haven't yet sold them. So just keep that in mind as well. But in any case, we're going to say this is going to be equal to if we have the same number of shares down here. I'm going to pick up the shares on the sales side this time. We're on the sales side. This is going to be equal to 400. And then the price that we sold them for equals the price tab. And this is going to equal the $28 enter. I'm going to add some decimals to that 28 home tab number group, adding some decimals, underlining it, home tab, font group, underline. And this will be the sales proceeds tab. Multiplying that out, this is going to be equal to the $400 times the $28 price. Adding some decimals in case we might need them later, number group, adding some decimals. And then I'll make that blue and border, selecting these items, font group, borders and blue. That's how much we sold it for. But the difference between the sales price and what we bought them for would be the gain, which might be called capital gains, which might have tax implications on and so on that we will not dive into at this point. So the profit then or loss, let's say profit or loss this time, selecting these two, we're going to make that a header, home tab, font group. We're going to hit the bucket drop down, make that black and white. And then we're going to say, okay, so now we're going to say the total cost. Let's take this total sales proceeds first, which will just equal the item above it, equals the $11.2. And then we'll multiply that times the total, not times, minus the total cost. The total cost, which is the two things we calculated, the proceeds minus the cost, underlining that, font group, underline. That's going to give us the profit in this case or loss. I'm going to put or loss just in case we want to change the data on the left in the event that we have a loss, which would be a negative number. Sometimes you'll see the loss in brackets to indicate, hey, if that's going negative, that means it's a loss. So this could be equal to the $11.2 minus to $800. Let's add some decimals just in case we need decimals later on. And we'll do that by going to the home tab, number group, adding some decimals. Let's make this whole thing black and white or border blue, I should say, font group, borders and blue. Now I'm going to do that whole thing another way because another way could give us some benefits. And so we'll see that when we change the data on the left, meaning here we sold everything, all the shares. So this calculation works out. But we might want to break it down to how much we got per share and we might think in the future, what if I didn't sell 400 shares, but I sold some part of those shares. Also realize that when you're buying and selling the mutual fund, you might first think, hey, I have $8,000 that I'm going to put into the mutual fund and then back into how many shares you can buy given the current share price of the $20. And when you sell the mutual fund, you might be thinking, hey, I need $11,200 or whatever and then figure out how many shares you need to sell in order to get that much proceeds that you need unless you're selling the whole thing like we're doing here to get the whole thing out. So here we go. We're going to then go to column F, make that a skinny column. I'm going to make skinny C. I want to make it just a skinny a C. F needs to be a skinny a C. So we're going to go to the home tab and format paint skinny C and put that on F. So now we've got a skinny F and we'll do the profit or loss calculation a little bit differently. Same calculation, a little bit different. I'm going to make column G a little bit wider to start this thing out and we'll then select. I'm going to go from G1 to I1. I think we're going to need another column here. So I'm going to go to the home tab font group and make that black and white. And so we're going to have a subcategory this time in which is the per share profit. So we want the profit per share. I'm going to put a colon because this will be a sub calculation and enter. And so we're going to say the sales price what we sold it for was the $28 down here sales price. This equals the $28 28 and then we'll subtract that from the cost. This is going to be equal to the cost. This will be equal to the $20. We'll put an underline here. I'll add the decimals in a second font group underline and that'll be the per share profit and no colon. And we'll subtract that out which is going to be equal to the $28 minus $20 is $8 that we have. So we're going to pick up $8 per share that we sell. So I'm going to add some decimals just in case we need them if we wanted to change the data on the left at a later time. Home tab number add some decimals. I'm going to indent these to make it a little bit more fancy because this is a sub calculation that's all kind of under that subcategorization. Home tab alignment indent. This one will do a double indent. Home tab alignment double indent. So we're going to take that we're going to multiply at times the shares that we sold. So notice here with this calculation I can have a different number of shares that we sold than we purchased. Whereas if I change my data on this calculation it'll get a little bit messed up. That's one of the benefits to the calculation on the right. So this will be 400. We'll demonstrate that in a second. So you can see what I'm talking about. Home tab font group underline. And this will be the profit. Let's just do it this way. This will be equal to the same thing down here profit or loss. And we'll multiply this equals the $8 times the 400 shares. Let's add a couple decimals. Number group decimalize in it. And then we'll make that blue and bordered as has been our custom. Home tab font group brackets bucket drop down border. So there we have it. So now so now we're going to add to that what we got from the dividends and the capital gain distributions. But before we do just note that if I bought 400 shares I don't have to sell 400 shares. I could sell like like 100 shares right. And then if I did that that messes up this calculation because because I base that on the 400 that we bought in the 100 that we sold. Which doesn't make sense right but this calculation still works. That's why this is kind of nice for that kind of projection because 28 minus 20 still still holds. And then we multiply that times the 100 shares just the amount that we sold to calculate the gain that we had. So if we wanted to adjust this over here I'd have to adjust this to like the 100 to calculate the amount that we bought and sold and not just the total amount that we bought. Okay so that's one issue. If I bring this back up to 400 you might you might then change the data to where you have a loss for example. So you might say what if I sold it for $15 well then you can have a loss that's going to be the negative number down here and over here. And then you could have tax implications if you sold these items as well. Capital gains losses could net out the capital gains and so on and so forth. Okay we won't dive into that at this point though. So what was the price on this thing again? This was what was that what was that $28. Thank you $28. Okay so now let's think okay we also have the dividend income. So let's figure out how much we got in the dividend income. So we we got a gain from selling it but we also might be getting income from the mutual fund. Note that if it's under the umbrella of an IRA or a 401k you're probably not going to get that because they're going to roll it back in to the IRA reinvest it. But if it's outside of that then you're going to get the 1099s you're going to get the dividends and so on and pay taxes on it. So we're going to then say okay and even if it's under the 401k you're still getting the dividends. It's just that they're going to reinvest the dividends and you might not have to pay taxes on it. You might not get the documentation of a 1099 because it's not subject to tax until you pull it out. Okay we're going to say paintbrush column J and let's call this the dividend income. Dividend income. We'll make this a little bit wider on this side. Dividend income and so I'll make this black and white. We're going to go up top home tab font group black and white on the bucket. And then we'll just pick up this equals the shares purchased and we're going to say 400 shares. Now we're going to say dividends for the year. The dividends could be distributed quarterly or so on and so forth. Right. And then you'd have to figure out how much dividends you got in a year. Oftentimes we want to think about things in terms of a year. So if we get quarterly dividends we might annualize them because the year is the standard metric usually that we're going to be making comparisons to other types of things that we earned. Right. How much did we earn in the time frame of a year is kind of the standard thing that we typically do for comparisons sake. So we're going to say this is the dividends per share. This is equal to the 60. So we're going to get 60 cents per share in terms of the dividends home tab numbers. Here we go. Now remember that the mutual fund. I mean it's really kind of like the underlying stocks that are paying dividends. So we've got we're holding on to a mutual fund. The mutual fund then holds a basket of stocks and whatever else and some of those stocks might pay dividends. Right. And then the dividends are the things that are being distributed by you know the mutual funds. So we're counting it on the shares of mutual fund that we have and the dividends are flowing through from the companies to the mutual fund to the shareholders of the mutual fund. Okay. So we're going to say home tab font group underline. This is going to be the dividend income dividend income which will be equal to 400 times 60. So we also got 240. Let's add some decimals just in case and let's put some blue borders around it. So we're going to go font group border and blue border and blue. And then we also have capital gain distributions capital gain distributions. And this is the one that's kind of confusing because you would think about this as generally capital gains. If it was outside of an IRA and you sold it and whatnot you'd have capital gains when you sold it. But you might have capital gains just from holding the mutual fund because the fund itself is selling the stocks or possibly activity in the fund which could result in capital gains from buying selling stocks within the fund. Even if you didn't actively sell the mutual fund which could still result in capital gain distributions. Okay. So we're going to select these two. We're going to go home tab font group and bucket drop down make this black and white. And this is going to be once again the shares purchased the shares purchased are going to be equal to the 400. And what's happening here we can imagine the fund managers buying and selling funds when they sell it. They have a gain similar to this calculation on whatever stock they sold and then they're allocating that gain over to the owners of the shares in the mutual fund. So then we're going to say this is going to be the capital gain which is going to be this one. Got to make this a little bit larger to see this kind of a wide column. Does it have to be that wide? It's okay. I'm not going to wrap it or anything. It's awfully wide. Just like my belly. Okay. It's not that wide. It's not that wide. We're going to see we're going to add some decimals. Let's put an underlying font group underline. And this is going to be the capital gain distribution. This will equal the 400 times the 70. We'll add a couple pennies just in case for later on sake. And we'll then make it blue and bordered. Blue and bordered. Blue or border blue. So there we have that. So what we got then in terms of our total proceeds from the mutual fund could include one, the increase in the value of the fund, which we're assuming here that we sold the fund. But even if we didn't sell the fund, we can think of this as an unrealized gain and increase in the value of the fund. Hopefully the fund went up in value. It could go down in value just like stocks can go up and down. And then we might have dividends, which would be dependent upon the funds that are whatever the fund is invested in, which could include stocks that pay out dividends, which would be possibly distributed to us unless we're having them kind of roll over and reinvest back in the fund. We might tell the fund, hey, just reinvest it back in the fund. But still we got income and then they reinvested that back in the fund. And we might have income tax implications on that unless it's under the umbrella of an IRA or some kind of retirement account. And then we could have capital gains, which once again is the fund possibly realizing gains such as this one when they buy and sell stocks within the fund, which again would be distributed possibly to us. Or it might be rolled back into the fund, which would be us basically buying more shares of the fund with the capital gains and dividends depending on how we've kind of set up our system. So then we're going to say this is going to be the total return then might equal then this, which would be what we actually bought and sold. Or you can think of maybe the unrealized gain if it was not yet realized plus the dividend income plus the capital gain distribution. All right. And so then we're going to say selecting these items, home tab, font group, blue, bordered. Let's check the spelling on this thing. Review spelling is perfect. It's perfect just like my spelling bees back in grade school. Okay, there it is.