 Personal finance practice problem using Excel. Investment estimated gains time value of money. Prepare to get financially fit by practicing personal finance. Here we are in our Excel worksheet. If you don't have access to the worksheet, 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, and blank the example tab in essence being an answer key. Let's take a look at it now. Information on the left, calculations on the right. When we're thinking about investments, we typically want to see those investments grow. We will be using time value of money calculations to see how we can build tables on the right based on the data on the left doing so in such a way so that if we change the data on the left, it should populate on the right, allowing us to run different scenarios. The second tab, pre-formatted information or pre-formatted cells so you can work the practice problem with less Excel formatting. The third tab will have in essence a blank sheet on the right, just the data on the left-hand side so we can practice the Excel formatting. If you don't have this, you can add the data on the left. That's the first place you would want to start, possibly by selecting the whole sheet, by selecting the triangle up top. I would right click on it, format the cells to my underline or baseline formatting, which for me will typically be currency, bracketed and negative numbers, no dollar signs and no decimals to start with and then I'll change those cells as needed. I'm not gonna hit okay here, but X out because I already have the data on the left. Then populate your data, adjust the cells as needed, making these percentages for example, and then we can make a skinny D column and we're ready to go with the data on the left and we're gonna use that data to calculate our tables. So we're gonna imagine that we have a deposit of 4,000. We've got a rate of return, which is gonna be varying here, 4%, 7%, 6%, and 9%. We've got the maturities at 20. This is how long we expect the investment to be invested, 20, 20, 30, 30. Now obviously whenever we make an investment, oftentimes these are gonna be estimates if we're investing like the stock market. We don't know what the return will be exactly. If we're investing in an individual bond or something like that, we might have a locked down rate we might have more surety in those cases, but they could be estimates. Also note that we could basically build our table with one set of data and then adjust the data to adjust the tables. However, sometimes we would like to see the side by side comparisons on the table. So that's what we would like to build here. I'd like to build a side by side comparison with the same 4,000 basically deposit, but then have these different rates and different maturities and see what the result would be in a nice condensed format, a nice condensed wave. So to do that, I'm gonna put my cursor over here. Let's make a skinny E column. I'm gonna make a skinny E because I'm gonna put the letters here for A, B, C and D. So A dot B dot C dot and D. That's what comes after C, D does. And then I'm gonna put my annual deposits here. So I could do it this way. I could say equals the annual deposit like that. But then I'd have to format the cell by basically going to the home tab, alignment and wrap the text. And I don't like doing that because then it makes this wide number one. And I wanna do that unless I have to. So if I'm not gonna make a table out of it, what I'll typically I'm gonna undo that is I'm gonna use two cells if I need to and then I'll make it look like a header by basically using a header formatting. So to do that, I need to get myself a little bit of space. I think I'm gonna need possibly two more cells up top. So I'm gonna move these down. A couple of ways I can move these down. I could select these four. I can put my cursor here and drag them down. It's probably easier to cut, control X, and then paste them. I'm gonna paste them right here, control V. So now I'm gonna put that down here and then I'm gonna type in here. I'm gonna set annual and then enter deposit on the next line. I'm gonna format it a little bit more later. Let's put the rest of the headers in. I'm gonna say rate of return. That's gonna be my next column here. And then I'm gonna have number, I'm gonna say of years. Notice I'm using two cells here as I go. And then this one I think I'm gonna need three cells to get it to fit it. I'm gonna call this the investment value at maturity. Let's say maturity, maturity, maturity. Is that how you spell it? Maturity. I don't know how to spell mature. I don't know why that is. Any case. And then I'm gonna say this one's gonna be the total amount of investment. And then this one's gonna be the total amount of earnings. Okay, so those are the headers we'll try to build in our table. Let's make them look a little nicer here. I'm gonna select this whole thing and make it look like a table by going to my home tab alignment. Let's center them and let's make them black and white. So I'm gonna go to the font group. I'm gonna say let's make this black and white so that you don't see all those lines and they're centered. This one needs to be a little wider. So I'm gonna just widen this one up but touch just a tab. You could double click on it if you want, it does it automatically. Okay, so there we have it. So then I'm gonna be putting down here. Let's put my annual deposit. So I'm gonna say this is gonna be equal to the 4,000. I'm pulling this information from the data set and enter, I'm gonna drag it down. I'm gonna use my fill handle, put in my cursor on the fill handle so you get that little cross, little plus button. And I'm gonna left click and drag it down so that it pulls the rest of them. So you can see it dragged it down, pulled all of them over here. Rates of return, I'm gonna do the same thing. This equals the 4% enter. Now that's not a percent. If I wanna see it, then if I wanna see it, you've gotta percentize it if you wanna recognize it, number group. And so we're gonna say percentize that thing 4%. And then I can copy that down, put in my cursor on the fill handle and drag it down and it'll format the cell for us and pull in the data. Number of years is gonna be from down here. So same thing, I'm just pulling in the data, pull it in, I put a lasso around its neck like a horse and then I pulled it in like that. I can't really do that with a horse because horses are probably stronger than me. But so there's the 20 here. I'm gonna put my plus button and drag it down. So there we have that. Okay, so the investment at maturity. So now we're saying where are we gonna be at the end of the 20 years? Now we're assuming here that this isn't a one-time investment. We're saying here it's an annual deposit. So we're talking annuity, an annuity calculation. So we're gonna do a future value calculation to see how much we will have in future value dollars. Easiest way to do that I believe is to say negative instead of equals. You could put in equals, negative might not be the most proper, but that's the easiest I believe. Future value, FV standing for future value brackets. The rate, we're just gonna pick that rate up right there 4% yearly rate, nothing else you need to do to it, comma. We're now on the number of periods, we're gonna say 20 years. We're in years, therefore nothing else we need to do, comma. We're gonna use the payment because this is gonna be assuming an annuity format in that it's 4,000 per year for 20 years. Picking that, I don't even need to close it up. I could just basically stop it there and enter and we get the 1, 1, 9, 1, 1, 2. Let's do it a couple more times because that's kind of the biggest calculation, but we can copy it down, then it'll copy it down. So I'm gonna say negative future value brackets. The rate, 7%, comma. Number of periods is gonna be the 20, comma. And then the payment is 4,000 and boom. So now we're at the 163. Obviously if we went from 4% return to 7% return, we got a significant difference in terms of future value dollars at the end of 20 years. Let's do it down here with the 30 years, we got negative future value brackets. The rate, 6%, comma. Number of periods is now 30, comma. And the payment is 4,000. So now 30 years at the 6%, we're at the 316, 233. Future value after 30 years. Let's do it one more time at the 9%, future value brackets, rate 9%, comma. Number of periods, 30, comma. And the 4,000 per year, 30. That gets us to the 5, 45, 230. Now I could have just copied down the first one if I delete these. You're gonna delete them really? No, don't do it. It's okay, it's okay. Cause I can just copy, I could just copy this one down. I'm gonna put my cursor here, put my cursor on the fill handle cross hairs on the bottom right and then drag it down, drag it down. Drag it down, you lassoed it and now you drag it, you drag it down. Doesn't seem nice. Can't you just ask it to go down there without dragging it down? No, you have to drag it. You've gotta drag it down there. So total amount of investment then. So how much are we gonna invest in order to get that 119, 112 after 20 years? Well, we're putting 4,000 down 20 times. So that one's we could just take using normal math without any fanciness. It's just gonna be the 4,000 times 20, for F4 times H4. So we put 80,000 down in order to get 119, 112. The difference between the two clearly being then the earnings on it, whether that be interest, whether that be gains of some sort, depending on the investment, be that bonds or possibly stock. So the 119, 112 minus the 80,000. We can do that again here. Here it would be the 4,000 times 20 again in order to get to the 163, 982. This is gonna be equal to what we got in total at the end of the maturity date, 20 years minus how much we invested after 20 years. There's the earnings that we would have received. Let's do it a couple more times. We're gonna say the 4,000 times 30 this time because this was 30 years, 120,000 is what we invested but we got or we're gonna end up with hopefully 316, 233 if everything goes according to the plan. And I made my plan, my plan always comes together. It's like the A team. My plan always comes together. And I love it when it comes together, when my plan comes together. That's what the A team guy says. So this is the 545, 230 minus the 120. Now of course we could have copied that down. You're probably saying, why are you doing all that work? I'm just gonna delete this. What you could have done is just selected these two and copied that down using the fill handle. Just grab that fill handle and drag it down. Get down here thing, drag that thing down. And then let's make this blue and bordered as is our custom. So home tab, we're gonna go alignment. I'm gonna make that blue, this blue right there. If you don't have, wait, that's the letters. I want the bucket. Let's go to this blue right here. If you don't have that blue by the way, it's in the more colors, standard color wheel. We're in that blue right there. That's the one. That's the one. And then we'll say borders. And then let's make this one black and white too. Up top, make this one black and white. Now, we could do the same thing and assume this wasn't an annual deposit, but just one deposit just to see that. Let's try that just for the fun of it. Just for the bells and whistles or the... So I'm just gonna copy this down. I copied it down. I'm gonna delete all the stuff though. I'm gonna have to repopulate it. I'm gonna delete the stuff and repopulate. So I'm gonna say annual deposit. No, it's not gonna be annual. I'm just gonna say one deposit. One deposit, not annual. And so this will be slightly different. This is gonna be equal to the 4,000. We can copy that down, put my cursor on it, fill handle, drag it down. Drag the fill handle down. Rate of return is gonna be equal to the 4%. And then we grab the fill handle, drag it down. And then it fills it. Drag the fill handle down. And then it fills in the cells. That's why it's named that. And then enter. And then we're gonna grab the fill handle again. We're gonna drag it down. So now what are we gonna have after 20 years if we just put 4,000 down and we just let it ride? Just let it ride. I'm not putting any more in. I'm just letting it grow for 20 years at 4%. Same formula, negative future value, brackets. But this time we're gonna have a little bit different, little twist at the end. It's like a story. It's like one of the modern stories these days where they put a twist at the ending. Except this one makes sense. Unlike most stories these days. So this is gonna be the rate. It's gonna be the 4%, comma, number of periods is gonna be 20. So that's the same. Here's the twist. We're not gonna do the payment. We're not doing the payment because that stands for an annuity. We're gonna put two commas moving us over to the present value, which is currently the 4,000 because that's where we are at the beginning in order to calculate the future value of one this time instead of an annuity. So enter. So there we have it. Obviously quite different than up top because we just have the 4,000 one time deposit. Let's do it again. We could copy it down, but let's do it again just so we can practice our future value, bracket. The rate 7%, comma, number of periods is gonna be 20, comma, and then the twist. Two commas bringing us over to the present value. And that's gonna be the 4,000. So we can calculate the future value where we will be in 20 years if we just put 4,000 down, letting it ride for 20 years. We're at the 15479. Let's do it again. Negative future value brackets, rate 6%, comma, number of periods is at 30 this time, comma, comma, because we're now at the present value here instead of the payment because it's not an annuity. It's not an annuity, but it's not an annuity. Negative present value shift nine rate is gonna be nine comma, number of periods 30, comma, come on and we'll bring that on over to the 4,000 and enter. So hold on a second, negative future value. Did I do a present value? Rate, comma, number of periods 30, comma, comma, comma, and then the present value is the 4,000. So there we have it. Now we could of course copy that down. So I'm gonna delete it. We could just take that top one up top and just copy it down. Why are you doing all this work? It's like you're just trying to make a long video. So I know I thought it was important. And so now we've got the amount of investment, which is just the 4,000. I really don't need this column because it's repetitive. So what I'm gonna do is try to delete this. Let's go here. I wanna move these cells from the left to the right. That's how I wanna delete it, not moving from the bottom to the top. So I'm gonna right click on it. I'm gonna delete, but then it says shift cells up. No, I wanna shift these cells to the left. So that's the one I want the top one. Boom. So now I can say how much did we earn? We earned the 8764 minus the deposit that we put in 4,000, enter. And I'm just gonna copy it down this time. Just do it the easy way this time. Don't waste my time with this recalculating when you can just copy it down. So there we have it. So then let's kinda recalculate this one up top just so we can understand it a little bit more. And it's useful to do this because then you can see how it is growing on a year by year basis. So I'm gonna make another skinny column and a skinny column L. I'm gonna copy the skinny column D by going to the home tab, clipboard, paint brushy, that thing, and then put my cursor on L. So there we have it. And I'm gonna call this A and I'm gonna recalculate A in like a table. So I'm gonna say this is gonna be my earnings, my headers earnings. This is my payment and this is my total. Let's make that our header. So let's make this one a little skinnier. That one needs to be skinnier. Selecting these make them our header home tab, font group, black and white as the header. Let's centerize that in the alignment area, centerizing it. And then I'm gonna bring this down to 20 years. So we'll bring it down on 20. I'm gonna say one, two, buckle my shoe because we're going down 20 years. We're gonna put my cursor on the crosshairs and drag it down 20. That's why you gotta have your shoes buckled because that's quite a ways away. So then I'm gonna go to the home tab, alignment, center it. Trying to go 20 years and you walked out of the house without even any shoes on. It's ridiculous. The first payment we're gonna say happens at the end of the year because that's how the annuity kind of works. We're gonna say that's gonna be the 4,000 here. I'm gonna select F4 on the keyboard because the payment will remain the same and I just wanna copy it down. That's gonna put a dollar sign before the F dollar sign before the four, making it an absolute reference, telling the system, don't move that cell down. When I copy it down, you only need a mixed reference by the way, but an absolute one works and it's the easiest thing to do in my opinion. And I do the easy thing when I can because I don't have time. I don't have time. Okay, so this is gonna equal the 4,000 total and then we're gonna have the earnings. The earnings are gonna be equal to the 4,000, the prior total times the 4%. Now that 4% is outside the table that we're currently working in. That usually means that we have to make it an absolute reference. The other one here, we don't need to make absolute because that's within the table that we're currently working in. Therefore, I'm gonna select F4 on the keyboard, dollar sign before the G and four. You only need a mixed reference but an absolute one is easy. So there we got the, hold on a second. Was that right? Yeah, we got the 160, that's right. And so then this payment, I'm just gonna copy it down, put my cursor on the crosshair and copy it down. And now this one, I'm gonna pick the prior period plus the current information. So this is the prior period plus the earnings, 1,160 plus the 4,000 that we put in again, that's gonna get us up to the 8,160. I could do that this way. You might do it this way, equals the one above it plus the SUM embedding a sum function inside of it. And then you gotta close up the brackets on that one because it'll also give you a little beep or something, no big deal. But so you do that, that can look a little bit more fancier. So you probably wanna make, I mean, if you can look fancy, you probably wanna do it if it doesn't take any more time because that's a little impressed people. So then I'm gonna select these three and I'm just gonna double click on the fill handle which I would now call a fill button because it acts like a button because you just double click on it. If I handle, you don't just double click on a handle. It's a misnomer in my opinion. So that's gonna bring us down to the 119, 112, 119, 112, so it kinda proves what we did it and you can see the earnings or you can see the growth as our money grows in the total each year, you can see the earnings growth. That's why it's important to be able to do this kind of table because it gives you a little bit more information. It gives you a little bit more insight. Let's just see what's actually happening. Let's make that blue and border. I'm gonna select this whole thing like so and blue and border. I'm gonna do it for the other couple here. I'll do it faster for these ones. I'm gonna put the border because it's the same routine. It's the same routine, but with different numbers. So I'm gonna scroll over here. I'm gonna make a skinny L or I'm gonna make a skinny Q from the skinny L. The L's already skinny but then I'm gonna make the Q skinny. Home tab, clipboard, format painter, skinny Q. I'm gonna copy the headers. Copy the header, control C, put that in column R, control V but now I'm gonna change it from an A to a B because we're on B now. And then we'll go ahead and make B a little bit more skinny. Skin horizon B. And let's put this, this goes down from one to 20 again. One, two, buckle your shoe. If you don't have buckles, you can tie your shoe but just make sure you got your shoes on and they're secure because we're gonna copy that down all the way down 20 years, which is a long ways. You don't wanna be going out there barefoot. So there we have that. We're gonna go to the home tab, alignment, center that. And then the payment is gonna be equal to, the payment will be equal to the 4,000. Once again, I'm gonna select F4 on the keyboard, dollar sign before the F and the five, making it absolute because I wanna be able to drag that down without that cell moving down. There we go. The total is just gonna be equal to the same one. And then the earnings are gonna be equal to the 4,000 times scrolling to the left. I'm gonna find the rate that we had. We had 7% for number B or letter B. That's not a number. It's a letter. It's letters, call it letters, numbers. Anyway, F4 on the keyboard making it absolute because that number or letter was outside of the table. And therefore we want it to not move down when we copy it down. Just when we do wanna move down. So I'm gonna say enter. This payment's gonna stay the same for period two. So I'm gonna put my cursor on the fill handle, drag it down and then the total's just gonna be equal to the prior total plus the SUM, some brackets of the earnings plus the 4,000. So we got, we're just adding those three numbers up to 4,280 and the 4,000. You could do that different ways like we saw last time, but that's probably the coolest way to do it. And if it doesn't take any more time to be cool, then let's be cool about it, why not? I'm gonna put my cursor on the fill handle, which I call a fill button. If I'm gonna use it as a button instead of a handle, which I am, so we double click on the fill button. Selecting all this stuff again, you can see how things change and growth over that 20 year timeframe between the two here during a compare and contrast. I'm gonna go up top, home tab, font group, blue and border. So again, you can see how it grows here at a different rate for, at an accelerated rate here. Let's do two more of these, two more. It's gonna be great. Gonna put my cursor on skinny queue, home tab, clipboard and format paint. Put my cursor on skinny V, or it's not skinny yet, but watch when I click on it. Now it's skinny V, it wasn't skinny before, but then it like changed, went miraculously skinny. I wish I can go miraculously skinny like that. That's amazing. I'm gonna make W a little bit skinnier here too, and this has gotta go down 30 years, so you gotta make sure when you go one, two, make sure you got those shoes buckled or whatever you got on, put the Velcro straps over, cause we're going down with the fill handle 30 years, 30 years on down, no problem, no problem with Excel. You can get there easy. It's like having like an open highway in your car with no one in your way, and you got a super reliable car. So this is gonna be equal to, and this is gonna be equal to the 4,000. I'm gonna select F4 on the keyboard again, F4 on the keyboard, making it absolute, cause I don't want that to move down. When I copy it down, this total.