 Personal finance practice problem using Excel mutual fund gain loss 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're imagining that we're investing in a mutual fund we want to calculate the cost the proceeds and the profit gain or loss the second tab is going to have some pre-formatted cells on the right so you could work through the practice problem with less Excel formatting the third tab where we will be we're going to do some of that Excel formatting if you don't have any of this that's okay you can open up a blank worksheet add this information on the left if that's what you're doing I would start out by formatting an underlying baseline formatting of the worksheet first by selecting the entire worksheet possibly with the triangle up top right clicking on the selected area format the cells and I will typically go to currency to start out with negative numbers as bracketed and read no dollar sign no decimals as the baseline starting point I'm not gonna hit okay because I already have this I'm just gonna X out of it then add your data on the left making the cells wider as necessary changing these cells when needed for example adding decimals here which you can do by going to the home tab numbers and adding decimals make a skinny C column and then we're ready to go so we're going to imagine we have a mutual fund here remember that mutual funds are a little bit different than investing directly in say stocks for example when we direct when we invest directly in stocks they represent an ownership interest of the company companies being separate legal entities that have their ownership interest broken out into standardized units of shares or stocks and then we're typically thinking about those that are traded on public exchanges making them more transparent and accessible to individual investors so then we might think of a mutual fund as basically taking multiple or investing in multiple stocks possibly us putting money into the mutual fund as an investor which was then pooled with other investors money so that it can then be allocated on a broader array of stocks and helping us to be more diversified with less of an initial investment so when we think about buying and selling mutual funds however we can think of it in a similar kind of fashion as to whether we buy or when we buy say one stock for example by breaking down the mutual fund into basically shares of in essence a mutual fund noting though that the mutual fund reflects this investment in a fund that then has the investments within the mutual fund diversified and spread out over other stocks and possibly bonds and so on other securities okay so the shares that we're going to purchase we're going to be purchasing 200 and we're going to say that the cost per share is 1424 so the only difference here we're purchasing shares of a mutual fund instead of shares of the stocks but it's going to be working in a similar fashion as to if we just purchased stocks for example in terms of the gain calculation we're going to say a year later we sell the shares and we sell all 200 shares for a price of $17 so we have a gain from the 1425 to the 17 so let's do this on a step-by-step basis we're going to say all right well what is the total cost and when we buy the shares then we're going to say all right well let's break out the total cost total cost and this is going to be my header I'm going to make column D a little bit wider put my cursor between D and E left clicking dragging it to the right just a bit just a hair selecting the top two cells D1 E1 and then go into the home tab font group bucket drop down and we're going to make that black and the lettering we're going to make that white black and white on the lettering so we're going to say that we purchased 200 shares so we're going to say we had 200 shares I'm just going to say equals and draw my data from the left that's always good practice that's how you want to set up your information so you can change the data on the left and have it automatically populate on the right so here we go we're going to say this equals the cost per share cost per share I'm hitting tab and then in E3 I'm going to say equals that 1425 and enter now you could have done that more easily I could have just entered one cell I'm going to delete these two just to demonstrate and delete this one to demonstrate if I had this cell I could drag that to the right using the fill handle or a copy and paste it if I so choose grabbing the fill handle drag it to the right there it goes it brings over the relative cell if I select these two and drag it down with the fill handle it should take their relative cells down I need to add some decimals to this one so I'm going to go up top and number group and we're going to add some decimals decimalizing it and then we're going to go to the font group and underline and this will be the total cost total cost we're going to just say it's going to be equal to 200 up to that's any two times the 1425 up one E3 and you could add some panties just to check out if there's any panties involved here number group add some decimals no panties involved we're going to then make it blue and bordered so I'm going to select these items here and let's make it blue and bordered that's more normal custom I'm going to write well let's do it up top let's go to the font group bucket drop down and I made it black on accident that's the blue I want right there let's go in the more colors it's also in here on the standard color wheel that blue that's the one I choose typically home tab font group let's make some borders around it because you can't see any borders now and I like the borders personally borders are good they help us define what's going on around here any case and then we got the sales proceeds so when we sold it we sold 200 shares for 17 now also note that you might when investing do this actually in the reverse because you might be saying hey I'm going to invest 2850 per paycheck or whatever per month or something like that and then you would be determining how many shares you would be purchasing you might not be first thinking about when investing in a mutual fund the number of shares you want and then trying to figure out how much you need to invest right you might be saying this is how much I need to invest which means that I'm going to be purchasing 200 shares right because you're probably thinking that you're going to invest some certain amount into a mutual fund and buy the appropriate a number of shares that you could get for basically that investment okay so then we're going to say when we sell the proceeds we're going to say sales proceeds so we're going to say we sold all 200 shares at a future date one year later we'll say selecting these two we're going to go up top home tab font group let's go to the bucket drop down and make that black and white so when now we're going to sell the number of shares I'm going to say equals and pick up the shares on the bottom this equals the shares now note we could sell something other than the full 200 shares at the at the end here right so we don't have to sell all the shares I'm going to say that we're selling all the shares we could then change the data if we so choose and we might check that out after we do this calculation so this is going to equal the price of the 17 so price this equals the 17 and so let's make let's add some decimals even though we don't need them because we might like change the data and the future and add some decimals let's put an underline here font group and underline and that'll be these sales proceeds multiplying this out this equals up to that being an e7 times up one e8 that's going to give us the 3400 let's add some decimals just to be consistent with what we have up top even though there's no pennies at this time then we're going to go and make that blue and bordered so let's make this sale we're going to go home tab bucket drop down blue and border now again when you're putting money out or taking money out of the mutual funds you might be saying I want to take out 3400 might be the way you're thinking about it and then at the price of 17 the current price how many shares would you be removing meaning when you're putting money in and out of the mutual fund you're probably thinking in terms of total dollar amount that you're investing and then buying the number of shares that would be appropriate and when you're selling you're probably thinking about how much money you need and then how many shares you're going to sell in order to get how much money you need unless you're selling the whole thing as we're doing here okay so then we got the gain or loss this is the profit or gain and there could be tax consequences if we were to sell the mutual fund and so on so forth depending on how where the mutual fund is and whatnot so you know is it under an IRA or 401k plan whatnot but the difference between will be the game that we had that we had so home tab font group let's make this black and white black and white and we're just going to say right this is equal to the sales proceeds and this is going to be equal to the 3400 and this is going to be equal to the total cost notice how everything is nice and connected here so that if I change my data on the left hand side everything should change automatically and this is going to be the profit or gain it might you could have a loss to depending on the circumstances and we might change the data so you could check that out but in any case we're going to say this is going to be equal to the 3004 minus the 2850 there's the 550 let's add some decimals to all the stuff home tab number group decimal lies and all of it at once put in an underline here home tab font group underline making some blue borders selecting these items font group border it up and hit the bucket drop down make it blue there we go okay let's do this one more time this time we're going to we're going to do the game calculation a little bit more directly it's nice to be able to see how to do this a couple different ways you'll see it presented a couple different ways it might be easier at times to do them one way or the other so let's do the same calculation a little bit differently over here I'm going to make a skinny F column and then we're going to work in column G so to make a skinny F I'm going to take the skinny C because I want to be just as skinny as C so it's all everything's all nice and uniform so we're going to hit the format paint brush on skinny C column and then just apply that paint brush one stroke down one paint brush stroke in the whole fence is painted so here we go so now we're going to get this is going to be the profit or gain calculation we'll do it kind of directly and let's make this sell a little bit wider and I'll select from G1 to I1 G1 to I1 font group bucket drop down we'll make this black and white and this is going to be I'm going to do a sub calculation now this is going to be per share profit per share profit brackets and I'm putting a colon there because it's a sub calculation so now we're going to think about it on a per share basis so we'll let's first think about the sales proceeds which is the $17 so this is going to be equal to the $17 and then we're going to think about the cost which is equal to the $14.25 so this is the $14.25 so the so the per share profit I hit two space bars because I don't want a colon there tab tab I'll put on the outer column I know I need to add some decimals I'm going to do it all at once this will equal the $17 minus the $14 let's add some decimals so we could see a bit more detail we're going to go to the home tab number group you want to recognize you got a decimal eyes so you can fully recognize what's happening with down to the penny so let's put an underline here we're going to go to the home tab font group and underline and let's add some let's add some board some indentation that's what I'm trying to say spit it out man spit it out would you I was trying to say indentation home tab alignment will indent it here and then double indent home tab alignment double indent okay so then we got it so then so that's how much we're getting per share on terms of the gain and then we just need to think how many how many did we sell how many shares did we sell to hundo to hundo that's how many shares we sold of the mutual fund let's put an underline there and that'll get us to once again I'll just equals the same bottom line here profit or gain slash loss and that's going to be equal to the 275 we got per share times the 200 shares boom 550 let's add some decimals just to just in case for later because we can then change the data on the left and play around with it a bit which we might try playing with it a bit we're going to select these items let's make that border blue font group border bucket drop down blue border blue okay so then now now obviously note a couple things we don't have to sell all the 200 down here so what if we just decided to sell a few shares we could say what if we sold 50 shares then I'll make this a little bit a little bit larger notice that this calculation doesn't quite work because because we took it based on based on the total cost minus the proceeds at the 200 but you could see the calculation is going to basically adjust over here as we sold it we're just looking at the number of shares we sold so notice the way you format your worksheet can basically be useful in terms of you know what's what's the purpose of what you're going to be using it for and also to kind of understand the calculation in a few different ways let's bring that back well let's not do it that way I'm going to bring this back to 200 and then we could have a loss so if I sold this for if I sold this for or I bought it for like $21 and then I sold it for 17 then of course we would have a loss down here this time they're both the same both calculations are correct because I'm sell I'm selling the same amount as I bought the whole basically investment on either side and then you could see the the calculations down below now remember you could have tax implications when you sell the mutual funds like you do when you sell shares because you could have capital gains and you might have capital losses that you can net net out against the capital gains and so on and so forth we won't get into all the tax implications at this time let's just bring this back to the starting point this is the 14.25 and like I say we want to be practicing these are great little problems to practice with building your tables drawing your information from the data that you set up on the right or somewhere which is a data set and then being able to adjust your practice problems adjusting the data letting that flow through your Excel worksheet so you can run multiple scenarios