Present Value & Future Value Practice Problems

1. Introduction : Present value and future value practice problems is a project-based course looking at multiple scenarios which would need time value of money concepts such as present value and future value calculations, focusing in on using Microsoft Excel to work through the practice problems. Although you might be able to use Google Sheets as well. Down below we have the worksheets that can be downloaded. The worksheets typically having at least two tabs, one with the answer key to it, so you can see the completed work, the second tab being used to work through the practice problem in a step-by-step fashion, along with the related instructional videos of that completed worksheets being the final project. 2. Rule of 72 & Running Balance Calculation: Personal finance practice problem using Excel, rule of 72 and running balance calculation. Get ready to pursue personal financial fitness by practicing personal finance. Here we are in our Excel worksheet. If you have access to the Excel worksheet, would like to follow along. Note that we're in that practice tab as opposed to the example tab. The example tab, in essence being an answer key, we have the information on the left-hand side. We're going to populate that into the blue area. On the right-hand side, we're going to look at the rule of 72, which is kind of a shortcut rule. And then we'll apply some other concepts which will be some more precise and some have other benefits to them. The information on the left says, assume the value of property is increased at a rate per year of 7%. How long before the property value doubles is going to be our question. So when we're thinking about something that's gonna be going up, we're usually thinking about some kind of investment. It might be like stocks or some kind of bonds and whatnot. And we're assuming than a rate of return at the 7%. Or it could be in something like a home, which could appreciate in value or property in that nature of land or something like that. Or we could be thinking about property, such as, if I was to purchase something, how much will the price of that thing go up over time if I was to purchase it a year from now, say from today. Obviously, if we were to purchase something today like a car, than it would be going down in value as it depreciates over time. So we're thinking about something that's going up. It's going up at the rate of the seven per cent we want to think about when it will double. Now we will think about an investment amount which will just pick a thousand dollars to think through it. But note that you don't even need the investment amount really to think about when it would double by applying basically the rule of 72 here. So that's what we'll start off with. We're not even going to consider the investment amount of 1 thousand. Now I'm gonna go through the Excel fairly, basically, fairly slowly, but this is not an Excel course, so I'll try to kinda go through it pretty slowly. But if you need more work on itself in practice, just your Excel Fundamentals here. So we're gonna say this, I'm going to start off and I'm gonna try to put these calculations into a table type of format. So the first number here we're gonna put as the Rule of 72. And I'm going to start off in the outer columns. So when the outer column here, I'm just going to type in 72. That's gonna be our starting point. And then we're going to divide into its gonna be 72 divided by the rate. But the rate does not, should not be in the format of a percent. Knowing that we can write the rate and the format of a percent or a decimal. So we got to basically say, okay, we're gonna take the rate here. And what I'm gonna do is try as much as possible to take the information from the data on the left-hand side. That's just good practice when working in Excel. So I'm going to say this equals that seven per cent. Now, note that now it doesn't have anything in here because this cell is not formatted, formatted as a percent. So I'm gonna go up to the Home tab up top, which I'm going to be in all the time. So I'm not even going to go up to the Home tab after awhile. I'm going to assume I'm in the home tab unless saying otherwise. Then we're gonna go into the numbers grouped down here, make this a percent. There's our 7%. Now, notice that you could make it a decimal if I was to say, let's not make it a percent undo. The percent notice the undo is up here. Just add decimals. It's the same number, but here's the decimals, number of group two decimals. What we would want to have it is I'm going to put it back into percent. And then I want to multiply it times a 100 here to give us just seven. That'll take it from a percent to just basically the number seven. So if I multiply this out, I'm going to say in this cell I need to do this an equation. Anytime I do an equation, I have to say equals. And then I'm going to point to the sales that I want to do something with. So I'm going to point to that cell right there, which is an E free, so equals point to E3, which is the 7% times on the keyboard, the asterix times this cell right here, which is E4. So equals e three times e for that then is going to give us seven. Now of course I couldn't make the 7, 8% number group percent, that would be 700 now, but we don't want to make it a percent, so I'm going to undo that and just keep it at the seven. So then I'm gonna go to Font Group and underline. And then I'm going to type down here our final result, which is going to be the years to double. How many years will it take to double? I'm going to divide the 72 divided by seven by selecting equals pointing to the 72 up top, which is in cell F2, f two. And then divide that by seven, which is in cell F5, F five, and then Enter, and we're gonna get ten. Now it's rounded because I don't have any decimals right now. So once again, I'm gonna go to the Home tab number group at a couple of decimals. It could go pass too, but I'm just going to keep it at two decimals out. Notice that it's actually longer than two decimals, but I'm going to round it to two decimals, noting that this cell actually has something that's not rounded in it if I use it to calculate something else, but I can only see up to two decimals at this point. That's kinda how Excel will work. So we're going to say, Okay, it'll double in 10.2910 years. That's when we would expect this to be 2 thousand. If our investment was 1 thousand, we would expect it to be 2000s. We can do that. That's a great little rule, but it's kind of abstract and it's obviously somewhat restricted to this idea of when something will double. So it's nice to know the actual valuation behind it as well. So now we're gonna run a table and kind of think about this. Okay, well, if I made 7% a year, just logically, what would happen? And this format is something that would be difficult to do if we didn't have Excel, but with Excel or some other spreadsheet, Google Sheets or something like that, it's pretty easy to do. So let's set this up and say, It's really useful to have these kinds of setups if you're really thinking through something and like I said, the spreadsheets are free at this point in time. So these kind of shortcut rules are great if you need to do something while you're under pressure. But if you're not under pressure, you can sit down at a computer or even with your phone these days. I guess you could do this on Excel worksheet, right? So I'm gonna just put our numbers here. I'm gonna say 101. I'm going to start at 012. Then Excel will know that that's a series. I'm going to select this series of numbers. Put my cursor on the fill handle down here. So it looks like this, not like this, but like that. And then I'm going to grab that and drag it down. And Excel then should pick up the series down to 11. I'm going to stop at 11 because I happen to know that's when the Dublin will happen, but I can go as far as I want it to in practice. And then I'm gonna go up top and to the Home tab and to the Alignment Group and center that there's going to be our starting point. But I'm gonna go into the investment over here and I'm going to assume an investment of 1 thousand. So a question might say, hey, I'm not even going to give you the investment number. You're going to tell me when it doubles, but then I can always plug in an investment number such as $1 thousand and determine when it will double in a way that's probably more intuitive. So I'm going to say equals, and I'm always going to point to the investment in our data on the left-hand side whenever possible because that's just good practice when you're doing scenarios. So I'm going to point to that and then I'm gonna multiply that times 7%. So obviously, if we did this with a calculator, if it goes up by 7%, it would be 1 thousand times 0.077 per cent would give us 70 plus the original seven, or 1 thousand. One thousand would give us 1070 after year one. Let's do that in Excel. So I'm going to say Alright and Excel, let's go ahead and say this is going to be equal to, I'm going to pick up this 1 thousand over here by selecting J2 and then say times. And I'm always going to try to pick up the data over here in our Data tab, the 7% instead of hard-coding in or type it and 0.07 and say Enter, There's our $70 and then the investment, this will be our running balance, will be equal to the prior $1 thousand. So I'm going to say equals J2, the 1 thousand plus the I, the I three, the 70, that'll give us 1070. Now, I'm going to set this up so I can copy it down and make it nice and easy to do. But before we do that, let's go ahead and do that a few more time so I can do that here and now it's up to 1070. And I would increase that for a year to here by multiplying a times 7%. So I'm going to take this equals the 1070 times the 7% again and enter. So it's a slightly higher rate of interests we would get because the investment has now been increased and it's compounding as they say. And now this is going to be equal to the 1070 plus the 75. And enter, and there we have it. Let's do it one or two more times. Do it a little bit faster. This equals the new number 1145. Now compounded times the 7% would be an increased interest to $80, and then the total would then be the prior amount, 1145 plus the $80. Let's do it one more time. We could say, Okay, this is gonna be equal to the 1 thousand to 25 times the 7% Enter. Then we'll add these up equals whenever we do a function, it's gonna be 1225 plus the 85 and enter. Okay, So now I'm actually going to delete everything I've done here and try to do this the easy way so we can just copy this down. So I'm going to delete the whole thing. I'm going to select these Excel, keep the 1 thousand up top, delete this. Let's do this 70 again so we can kinda copy it down. I'm going to take up that 1 thousand and then say times the 7%. Now there's 7% is not in the table. Anytime something is not in the table, you're typically going to need to make it an absolute reference whenever you copy it down. Let me show you what I mean by not doing that first. And I'm gonna say this equals the 1 thousand plus the 70. Now if I was to select these two and try to copy them down, I'm going to select both of them by highlighting them. Put my cursor on the autofill handle and drag it down. Then you can see this one doesn't do what we want if I double-click on it because it moved this cell down. So I want to tell Excel don't move stat cell down and I'm gonna do that with an absolute reference. So I'm going to put my cursor back on these to delete it. But my cursor back on the 70, that one in cell B3 right there, B3, I want to say Excel. Do not move that one down. Don't you move that one down Excel, That's what we're telling it. And I'm going to select F4 on the keyboard that puts the dollar sign before the B and the three. That is not a dollar sign, that means anything related to money. It's just kinda like code that tells Excel do not move the cell down. Now you only need a mixed reference $1 sign. But I'm just going to use an absolute reference because conceptually it's easier to do to figure out which dollar sign you need and whatnot. So I'm gonna go ahead and say, Okay, and now if I were to select these two cells, put our cursor on the fill handle, drag it down. It looks like it does what we wanted. Let's double-click on the 75 to double-check that it does do what we want. We've got the 7% times the 1070. It looks correct. That looks correct. So let's go ahead and put our highlight these two and drag it on down the hallway. I'm going to select these two and drag it on down the hallway. And there we have it. So there we have it. And you can see that if I double-click on this last one, it's still doing what we want pretty much. I still got the dollar signs right here, B3, that didn't move that cell down, but this other one did move down. These to move down beautifully. So it all worked out perfect. Just like we thought it would. Just like we knew from the beginning with no had no hesitation that it wouldn't. So any case, we're somewhere between years 1011. We double right here. You can see it and you can see this increase each year and you get to see how the interest is compounding in this way. So this is a really useful way, especially if you don't really have a good grasp of how this compounding thing works to double-check your work and it's pretty easy to do in Excel. And usually it gives people a better understanding of what's going on. Let's try to do the same. Next time we're gonna do a similar process, but we're gonna be using Excel formulas. And then we'll go to it, an actual mathematical problem to do similar kinds of calculations so we can see how all these things tie together. 3. Excel Functions Present Value, Future Value, & Number of Periods: Personal finance practice problem using Excel. Excel functions present value, future value and number of periods. Get ready to pursue personal financial fitness by practicing personal finance. Here we are in our Excel worksheet. If you have access to the Excel worksheet, I'd like to follow along note that we're in the practice tab as opposed to the example tab. The example tab, in essence being an answer key, we have the information on the left-hand side are going to populate that into the blue areas on the right hand side. Note that you may or may not have some hidden cells up top. We're going to first practice hiding and unhide in some sales so we can see what we did last time and continue on with our practice problem with the same scenarios. You can see up top we've got a, B, C, and then it skips one over two L, which is not how the alphabet goes. So there's gonna be some missing columns there. I'm going to unhide some columns and then rehire them just to show how to do this. What we want to do is be selecting from B to L. I'm going to put my cursor on B, so I just have this drop-down or left-click on it. I'm going to drag on over to L. And so we've selected those whole columns, not just the cells but the entire column. Then right-click on the selected area and unhide those columns going to unhide that. Now we can see what we did last time, which was the Rule of 72 and then this running balance. So the scenario that we looked at was to assume the value of the property increased at a rate per year of 7%. We asked how long would it take then at that rate for something to double. We looked at it first by not even having an investment account applying the rule of 72 to get to the 10.29 years. That's an estimate than we assumed an investment, which I think is more intuitive for most people to do and ran a running balance to kinda estimate that same number to get somewhere between 1011 period. Now let's do the same kind of thing, but this time using the functions in Excel. And it's really useful to use the Excel functions that can be quite intimidating at first. But notice that this rule, like the rule of 72 is quite restrictive. It's only telling you when something doubles and whatnot. If you want something more complex than that, you can use functions in order to do that or other methods. And those other methods are quite easy these days as compared to the past, given the functionality of something like Excel or other similar spreadsheets. Let's go ahead and hide the cells up top again, practice doing that. We're going to put our cursor from column D. I'm going to bring it on over here to k. So I'm gonna put my cursor on column D, drag on over to the k, and then let go. And you don't have to do this by the way, but it's easier to do. I'm going to right-click on the selected area and let's go ahead and hide those cells. And this allows us to work with our data right next to where we are gonna be putting our information. So we're gonna have the same kind of thing we wanna do with functions. Now, the first two functions you'd probably learn are gonna be the future value and present value functions. These functions are going to be related in nature. And with this question, we could use those two functions to find basically what we're looking for, which is going to be the number of periods. So let's actually start off with those doing these two functions and back in, in to the unknown, which is the number of periods that we're looking for, which will be how long it would take. In other words, for the investment of say, 1 thousand to get to 2 thousand if we had a 7% interest rate. Now if we did this mathematically and we have a mathematical formula, then typically we can take the function of something like a present value algebra and solve for the unknown, which would be the number of periods here. And we would rework the algebra and we'll actually do this in a second presentation. But first, let's take, let's think about this in terms of a function. With a function in Excel. We can't really rework it as well with the algebra, but we can still use a similar method to find any unknown that's in the function by using something called Goal Seek. So in order to set this up, let's go ahead and basically put something down here. I'm just going to assume or guests five, it's going to take five years as our starting point. Once we enter the calculation into Excel, I'm then going to change this number or ask Excel to change it to whatever it needs to be to make the end result be what it should be. And that way, using the algebraic concept of basically if I only have one unknown, I can back into whatever that unknown is. So let's take a look at this. I'm going to go through the functions a few different ways. One way we can do it is we can go, I'm gonna put my cursor in this cell. We want to go to the formulas up top and then go to the insert function on the left-hand side. This will give us a dialogue box, which can be a little bit easier sometimes to help us populate the function. I'm looking for a future value which I could type in like this. And sometimes it doesn't find it or f v, I know that that's the function. There it is. It gives us a description down below. It gives us the returns, the future value of an investment based on the periodic, constant rate and consistent interest rate, I'm going to say, okay, that's the one. We get our dialog box. So here's our dialog box. Now this dialogue box will be the same, whether we're talking about future value of an annuity or future value of just one. And then we're gonna have to change our input between the two. That's useful, but also a little bit confusing. We'll do multiple practice problems, so hopefully you'll get used to that. So we're going to pick up the rate and I'm going to pick up the rate all the time, just like with our prior problem from the dataset as much as possible. I'm going to pick up that 7%. Then I'm gonna put my cursor in the number of periods. Notice it gives you a nice little description down here as well. Number of periods, That's the one that we don't know, that is our unknown. But we're guessing what it is to start off with so that we can then change it later. So I'm gonna say, I'm gonna pick up that five that's in this cell that I set up simply so I can change it. I don't want to hard-code it, meaning simply type in a number, but have it in a cell. So then I can ask Excel to change that cell to whatever it needs to be to figure out the function. Now this is the tricky part. There's no payment that's going to be involved here because that payment relates to a new and annuity as if there was a series payments. We don't have a series of payments. We're talking about one investment that's going to basically just be growing over time. We're not putting another investment in $1000 each year, for example. So we skip that one. We're going to say they present value, is this $1 thousand. That's, that's gonna be the $1 thousand. And then once I say, okay, it's going to give us the result down here, which is showing notice 100403 that we're going to watch. Now what would I expect this to be? What I would like it to be is 2 thousand because we want this to double, meaning from 1 thousand to 2 thousand. It's not going to give us that because we don't have this input to be correct, then we'll go back and change this input. So I'm going to say, okay, there we have it now a couple of things. One is this, 100403 is negative. Two. I want it to be, I want it to be a positive two thousand, two thousand. So I'm gonna double-click on it. One way you can change the sign. Excel wants you to put a negative number on the number of periods. But usually for these future and present value once I just put a negative and I think it's just handy to know. It's useful to know that if you put a negative before most functions, it basically takes that function flips the sign, or in other words, multiplies it times negative one, flipping the sign. So that then makes it positive. Notice also, you could type this function in this way with this nice little dialogue box. So let's go ahead and do that. I'm going to actually type it in this way. It's just so we can see both ways of typing this. I'm going to delete this. I'm going to say equals future value. And this is the fastest way, the way you will default to. After you get to know a particular function, double-click on that function. There's r equals function f v, the brackets indicating the function. And we got this nice little box down here, which will indicate each set of the function dividing it out by a comma. Then I would pick up the rate, which once again, I'm not going to type in. I could put 0.07. That's what I would call hardcoding, just typing in the number. I don't wanna do that as much as I can. I want to pick the number up from the dataset so that I can then change my dataset to make projections in the future. It's just good practice to do and then comma, it moves them to the number of periods. The next in our dialog box, the number of periods is the unknown that we set up down here in our nice little box. That's gonna be five. That's the thing will change later. Then I'm going to say comma to get to the next argument, which is the payment. Now there is no payment. This is the tricky thing when we're using present value, future value formulas because it has the same formula for an annuity and a present value of one. This is not an annuity, meaning we're not making multiple 1000-dollar payments, but The same $1 thousand that is increasing over time. So we can either put two commas just in a row, taken us to the next argument. Sometimes when you're learning, it's easier to put a 0 there. Just as a placeholder. You can do that if you feel too to get us to the present value, the present value is 1 thousand. We want that 1 thousand then to be 2 thousand at the end of the day. And it won't be because it's going to be dependent on this function. So I'm going to say, okay, now I'd like to flip the sign again. So I'm going to double-click on it. Put my cursor in front of the f. Also know the function is up here as well. So if you're moving around in Excel, you can see it up top two. Put a negative in front of the f of v and then Enter, and that flips the sign to a positive number. Now I'd like to change this cell until I get this number to be correct, which I could do manually by putting a six here, put in a seven there, and so on. Or I could ask Excel to do that by going up top and saying, Let's go to the Data tab. And this is a really useful function. Not a lot of people know about it, but it's really useful to do. So once you have everything connected here, and I just want to change this unknown to make that function correct. I can go to the forecast section in the Data tab Data tab Forecast group. What If Analysis, Goal Seek. We're going to seek a goal. We get this little dialog box. Notice I'm not on the cell that I want to change. This is the cell I want to change. I don't need to be on it in order to change it. And then I'm gonna go to set this cell. I want to make this cell B. What I know the answer should be, which is 2 thousand. That's what it should be if that 1 thousand was to double. So I'm gonna go down here. You have to hardcode the 2 thousand. I can't put 1 thousand here times two. I have to actually hard-code this. And then, which means type it in there hard-code. And then we're gonna go down here by changing this cell. I want you Excel, please change this cell to wherever it needs to be to make that cell what I want it to be, which is 2 thousand. Then we're going to say, OK, and Excel just kinda forces it to work. And there are desert, There's the 10.24 does it with brute force instead of changing the function. So that's one way you could do this if you know a function, for example, and you don't know one of these unknowns in the function, which in this case we didn't know the number of periods. The other way you can do it, which we'll take a look at later, look at that number of periods and see if Excel has a function, which it does for the number of periods, which we'll do last. So you could do, you could do that as well, but you may not be as familiar with that function as you are with, say, the present value or future value. And it's useful to know how these things are related. Because notice we could do the same thing down here with a future value function, make the same kind of argument I can assume down here once again, for example, that I had six. And I can say, well, can I do a future value calculation if I know the investment amount, the amount which is 2 thousand and the percent, let's do the same argument using, using a present value. Sorry, there's a present value one. So I'm gonna put my cursor on this blue cell. Let's do it the two ways. Again, we're gonna go to the formulas. We're going to go to the functions, Insert Function, dialog box. I'm just going to type in present value this time. And that's going to return to the present value of an investment. So I could say, okay, that looks good. Okay. Dialog box, setup, dialog box here we have it the rate. Once again, I'm going to pull the rate over here. I could put 0.07, but I don't want to do that. I want to pull the rate from the cell in the data group, number of periods, That's the unknown, which we guessed to be six. So we're going to guess it at six. That's the one we're going to change again. Once again, there's no payment this time because we're not talking about an annuity. We're talking about one, present value of one, and then the future value. Here's the change, here is the different thing here is not going to be simply 1 thousand, it's gonna be 2000s because that's what it would be if it was to double. So I could take I could just type in 2 thousand here, but I would like to take as much as possible from this dataset as we can. So it's just good practice if I want to use that dataset in the future to change the numbers and then run scenarios. I'm going to pick that up times two. So there we have it. Notice it's a negative number down here again. So I'm gonna say, okay, There we have it. It's a negative. I usually like to flip the sign by double-clicking on it, going back into it, going before the p. And then I'm going to put a negative before the p, which in essence multiplies it times negative one, flipping the sign. And okay, so there we have that. Let's do the same now. What I what I would like that to be at the end of the day, if it's gonna be, the present value should be back to 1 thousand. So I can use the same goal seek to figure this cell, change this cell. So please, so that this cell will then be at the 1 thousand. But before we do, let's do this calculation again using the dialog box, which is what you will use, or just the normal function, which is what you will use once you get used to this because it's faster. So I'm gonna, I'm gonna delete it. I'm going to put my cursor back into it, type in equals present value. And then I'm going to double-click on the present value. Here, we have our same kind of set of arguments. The first item is gonna be the rate. So the rate is gonna be that 7%. I'm not going to type it in there, but pick it up from the dataset comma number of periods. That's the unknown that we set up down here. Then I'm going to say comma, which takes us to the next argument, comma payment that we have. There is no payment because the payment relates to an annuity which has a series of payments plus the interests. This is just the interest-only. So I could either put two commas, taken us to the next argument, or we can put a 0 and then a comma as a placeholder. And then the future value is going to be 2 thousand because that's what it would be when it doubles. Or, but I'd rather take this cell times two so that I can pick it up from the dataset as much as possible. Closing that up, and then Enter there it is. Again, it's a negative number. I'd like to flip the sign, double-clicking on it, putting my cursor before the p, I'm gonna put a negative before the P and enter. So there we have it. Now I'm going to ask Excel. I'm going to say Excel, could you change this cell to wherever it needs to be, to get that cell to be the present value of 1 thousand, which we could do by default, this is all Excel is gonna do. It's gonna say, well, I don't know, Let's try eight. Let's try nine. And it's just going to keep doing that until it gets close. But it does it faster than we can. So we're gonna go up top and go to the formulas in, I'm sorry, we're gonna go to the Data, Data tab up top. We're gonna go to the forecast. We're gonna go to the What If Analysis. We're doing some what-if analysis sizing here. And then Goal Seek, Goal Seek. And then we're going to say we would like to change this cell. We want to change that cell to be what we know it should be and you have to hardcode it or type it in here. $1 thousand, That's what we need it to be. By then changing this cell. So then I'm going to change that cell. So once again, we want, we want Excel to change this cell to $1 thousand by making this cell whatever it needs to be. So we're going to say, Alright, let's do it. Do it excel, does it for us and we're going to say, okay, you can kinda see how these, all, these are related. We use the rule of 72, we use a running balance, we use the future value, the present value. We're getting the same results here. Now you can also say, well, instead of using this kind of backwards method to back into this unknown by using Goal Seek. Why isn't there a function for that number of periods? You could look for the function for the unknown that you don't know and try to do it that way. And there is usually a function if you think about it that way as well. So I'm going to say, alright, let's use the number of periods. I'm not as familiar with that function because most likely I'm going to get to know the present value and future values first. But let's go ahead and use this function, right? So I'm going to say, let's look for that. Let's go to, let's find it. Let's go up top and say formulas, functions. And then I'm going to say, well that one was the number of periods NPER. There it is. They do have one. I knew they would returns the number of periods for an investment based on period constant payments. Okay? So let's do it. This is the most direct one, which we did last because it's probably the least well-known one that you would use. So I'm going to say rate. So I'm going to pick up the rate once again at the 7%. The payment, like, just like the other formulas, there's no payment there because that would be for an annuity and this isn't an annuity function. So we're gonna go to the present value. We do know the present value, which is our starting point before. It's going to double the 2 thousand, that's gonna be the 1 thousand. And we also know the future value. That's where we want it to be at the end, which is at 2000s, which I would like to do by not hardcoding 2000s, but rather taking the 1 thousand times two to get to the 2000s. Note that it doesn't give us a result down here because Excel wants a negative number and this is where it gets kinda confused and I won't get into the logistics of Y wants a negative number there. But notice if it doesn't give us a number and it gives us an error, usually the payment function, that's where Excel wants the negative number. So if I make that negative, it then gives us our result, which can be a little confusing. So there we have it, I'm going to say, okay, so there it is at the ten to for the most direct way to kinda get there as well, Let's go ahead and delete that and do it one more time with our function. I'm going to do it this way. Equals. And the way I know what this function is, once again, it's because I can see the unknown when I did the future value. So even if you don't want to use Goal Seek, you can use future value and say, I know this is the unknown right there, and it's called by Excel NPER. So most likely they might have a function called equals PER number of periods is what that stands for. So we're going to say, let's try that equals NPER. There it is. They do have it double-clicking on that. The rate, we're going to pick up the rate in this argument, which is gonna be the 7% not typing it in there, not hard-coding it, but picking it up from our dataset comma, then the payment amount, there is no payment because that's related to an annuity if there were a series of payments. So you can either put two commas or you can do that 0 and then a comma as a placeholder, the present value. That's where we start at when we're looking at this kind of system, we're going to say it's going to be the 1 thousand. And then comma, the future value is where we're going to end at, which is gonna be doubled or 2000s, which I don't want to hard-code by just typing in 2 thousand, But rather pick up that 1 thousand times two. And so they're in there, It's close it up. There, we have it. And notice it gave me a wrong thing because it's what I always do. I did that on purpose. By the way. If I go on notice it said gave me an error and that's because I got to put one of these as a negative, that I can't just put a negative in front of the n here because it didn't give me a negative number actually after go in here and put the foot, the argument that it wants as a negative number, which is this present value. Otherwise it won't, it won't work somewhere. Alright? Okay. Alright, so if that's how you're going to be, put a negative before the B8 and enter. And there we have it. And there we have it again. So that's gonna be, that's gonna be these ideas. If I unhide this, if I go to the b again and I'm gonna go back to L, integral from b to L. Right-click on those and then unhide. Notice we've looked at the Rule of 72 and found this, but that's very limited. Notice that this second way of doing it is quite useful because now that really gives you a lot more detail as to what's actually happening, how the interest is compounding. You don't get to see that even when you're doing these future value calculations, when people use and understand present value and future value in Excel, they often still have no real comprehension in their mind what is actually happening to the interests. That's just a magic number that basically pops up this running balance quite useful. Next time we'll do the same kind of thing, but we'll do it with formulas for the present value formula and the future value formula. And then we'll do the same thing using tables. Now these other two ways or not, what you would probably do in practice as much because it's more tedious to use formulas if you have access to something like Excel or a financial calculator. But they can be useful. In a book problem, you will often see these tables. And no matter what formula or way you want to look at this, what you really want to, what you really want to do also as be able to say, hey, if someone's doing something in some funny way, they're using formulas, they're using Excel or using the Rule of 72 that you're not like, Oh, there's some magic wizard doing something totally different. I don't know what they're doing. No. They're just doing some way, whatever way. Same thing. They're just doing it a different way. 4. Present Value & Future Value Mathematical Formulas: Personal finance practice problem using Excel, present value and future value mathematical formulas. Prepare to get financially fit by practicing personal finance. Here we are in our Excel worksheet. If you have access to the Excel worksheet, would like to follow along. Note that we're down here in the practice tab as opposed to the example tab. The example tab, in essence being an answer key, we have the information on the left-hand side. We're going to populate that into the blue area on the right-hand side. Before we do, however, we're going to unhide some cells. You may or may not have the cell's hidden on your worksheet. But we want to practice hiding and unhide in cells because that can be a useful skill as we maneuver around an Excel worksheet. So you'll note in this Excel worksheet we've got a, B, C, and then it skips to R over here, meaning missing letters in there. That's an indication that there's hidden cells. So we're going to unhide these columns in-between by selecting the columns around it. So I'm gonna put my cursor on column B. So I see this drop-down, left-click and then drag over to column R. Let go. And then right-click on that selected area. And then we should see something down here saying unhide, I'm going to unhide those columns so we can see what we did last time. We continue on with our worksheet. So the problem says we assume the value of property increased at a rate per year of 7%. Now this would be a similar kind of scenario if you're talking about any kind of thing that's going to increase like an investment, like a house, like land, like stocks or something like that, that you expect to be going up over time. We're gonna be asking how long it will take before something doubles. So you could ask that with an investment amount like $1 thousand or you can ask it abstractly, just basically saying how much would any investment take to double at a rate of 7%? You can find the answer using our rule of 72, which is kind of an approximation we did last time. We got to leptin 0.29, which is a great strategy, but it's restrictive. We then did our calculations with regards to a running balance table and found that it was somewhere between 1011, we could get more detailed than that. But just to show how you could do it that way, this is actually very useful because it gives you more detail about what's actually happening on a year-by-year basis or period by period basis. Then we looked at it with regards to formulas in Excel, this is probably the method that you would use most actually in practice, it's the most practical method. Although you want to use it in conjunction. Normally what they're running balanced table because this will give you more detail. And then now we're gonna be looking at it with regards to our formulas here. So now we've got our formulas for the present value calculations and we want to look at it from a mathematical standpoint. Now the mathematical standpoint, it's probably oftentimes when you learn these things in a classroom, the thing that they start off with. But it's also the thing that's most, least likely to actually, you're going to use in practice because you're probably going to be using Excel and the functions in Excel or a financial calculator more and more possibly using the Excel. But of course, the mathematical formula can give you a conceptual idea of what is going on in the calculation. They can also give you an idea algebraically how these formulas are related when you're looking at a future value and present value calculation. Now that's where we stand. Now, I'm gonna go back. Notice that I've repeated, we've repeated in the worksheet that data over here. So you don't really have to in that format hide cells, but I'm still going to go back and hide the columns again, just to get an idea of how that can be useful to see your data if we only had one set of data over here on the right-hand side. So what I'd like to do is I'm gonna, I'm gonna hide everything from column D on over to where we are, we're working which is column M. Also note that she can increase or decrease the size of the screen down here. So I'm going to make it a little bit smaller. It's now at 180 on my screen. And that's another way you kinda move around these Excel worksheets. So we're going to say, Alright, let's go from column B or column D over here. I'm gonna put my cursor on column D, select all the way over two. Then let's go to Column N, d2 n. Let go, right-click on those cells and hide them. Now, you're not required to do that because you'll, you'll still see the information over here if you don't do that, but I'm gonna go ahead and do that. I'm actually going to hide them from a to C so I can just keep the data that's in column O. Let's keep the data there. I'm gonna go ahead and hide that as well. So now I'm just working in column O on, onto the right. And like I said, you don't need to hide them. You could just work in the worksheet from that point forward. But just so we don't get any confusion that can be useful to use that hiding. Okay, so we have the calculation down below. This is going to be the present value calculation, which equals the future value of one over one plus r to the n. Now note, what we're looking for here is actually the number of periods. So we saw when we did the functions that you can actually use a different function and use it in a similar way as algebra to solve for the number of periods. Or you can use your present value and future value. Now, these two formulas, present value and future value, are related and they both have this number in it, which is going to be the number of periods. So you can then use either of these formulas. Solve for the unknown, which in this case would be the number of periods with it. So when you're thinking of a problem like this, note that you could do it one way. You can kinda try to think of another formula that would be solving for the number of periods. But most likely if you were to do this mathematically, that's not how you'd go about it. You would think of the present value formula or your future value formula. Plug in everything that you know and then solve for the unknown, basically using your algebra. So how that might look, if we look for our data up top, we know what the percent is, we know what the, what the investment the starting point which would be the present value. And we know where we want to be at the end, which is double that or 2000s. So if we were to plug this into our formula down below, it would look like this. We'd say that the end point, the end point should be, this should be equals to the 1 thousand, equals the future value. That's where we expect to be. That's where we want to be at the end of this process, which is double where we started at 1 thousand times two or 2 thousand, not going to put an equals in front of it. One thousand. This equals the 2000s. And then I'm going to put brackets around this. This is going to be, let's just say times. And then we're going to say one over. And then it's gonna be one plus the rate. And the rate is going to be that seven per cent. So we could say 0.07. And then this would then be going to the power of shift six. And then n would be the unknown. So it looks something like that. And then of course we can solve for the unknown of n. You can do the same thing for the future value formula, which would be the future value equals the present value times one plus r to the n, the number of periods. And you can plug that in here by saying, alright, well, I know the future value needs to be 2000s. That would be equal to the present value, which needs to be 1 thousand. And then we'd say times, and we'll say one plus r, which was that 0.07. And then to the power of n, which is the unknown. So once again, we're left with that one unknown of n in either formula. And then we can go in and solve for that unknown in this instance. So I won't do the algebra here is you can work through the algebra yourself. What I would like to do here is then take this formula and put it into a table type of format that exercise in and of itself can be quite useful because oftentimes it's useful to put something into this type of format as you're working with tables and different types of scenarios. And then we're going to see that Goal Seek activity working again. And possibly a more familiar format where you're looking at an algebraic problem which we will solve not by working the algebra for n, but by using that trial and error method. Hopefully giving you a better understanding of how we would use that feature in a function type of format as we did in the prior presentation. So let's go ahead and try to build a, a table based on this formula. Whenever you have a formula like this and you're building basically a table on it. You're kinda thinking, you're basically taking an algebraic formula and building something that looks more like a tax return. You're trying to put it into cells. And that can be quite useful for problem-solving. It's another way to visualize the problem. And again, it's another way to build scenarios and just work on your Excel tables. So basically what you wanna do is usually, if I was to put this into Excel, I'd like my outer column to have the end result of the numerator and the denominator. And any more detailed calculations I'm going to have on the, inside the calculation. Also note that whenever we take something like the future value over r times one over something, you can rewrite this as just the future value over one plus r to the n, which is going to be the calculation. If I bring this up top, I'm going to say, alright, well the future value is basically the numerator. So I'll say, let's start off with the future value here. And that's gonna be the amount that's going to be equal to the 1 thousand times two. And notice I'm doing this with a function. I'm going to put that in the outer column because that's basically the numerator. And I'm going to pull my data as much as possible from the data on the left, just practicing good Excel skills. So I'm going to say this is going to be equal to the 1 thousand times two, instead of just hard-coding or typing 2000s. Now, then we have the denominator. So I'm gonna go ahead and try to label what I'm gonna do down below by putting this one plus r to the n. And that's going to basically be what I'm going to pull into the inside kind of a subcategory that we will have in our calculation until we get to the end result, which will be the whole function of this denominator that I'm going to pull over to the outside. So I'm just going to simply say I'm not going to label the one. I'm just going to bring that one over here and say one. And then I'm going to say adding the rate. So we'll say the rate is gonna be the 7%, which once again, I'm going to pull from my data as I build my table. So I'm going to be pulling that from the 7%. Notice that in our table it is now in there as a 0 because it doesn't have any decimals or percents. I'm gonna go to my Home tab numbers group. You could add decimals to get to the 7% or you can make it a percent, moving the decimal over automatically and adding the percent sign to it. So there's our percent. Then I'm going to underline that by going to the Home tab, font group and underline. And that's gonna give us, I'm going to call this just a sub category of one plus r, which is going to be, now here I can do this, this way. I can say this equals this number, the one plus this cell here, which is going to be the rate-limiting. Do that again, hold on a second. Something funny happened. This is going to equal to the one plus this, plus this. So S4, S5, or we can use our sum function here. So let's use our trusty some function. And if you don't know the sum function, most useful function is equals SUM. We're going to use it all the time. It's just adding up a column, usually a column or some series of numbers. And then we're going to add the sum of this two here. So there's our trusty some function adding that up once again, it's not in the format that we would like to see it in however. So I'm gonna go to the Home tab number group. We can add decimals to be at the 1.07 or we can make it a percent, which is I'm going to prefer the percent at 107%. So that's gonna give us then the value of the two. I'm going to call it to n periods. So the number of periods, which is gonna be this n here. So that's the unknown. We don't know what that is. And I'm not going to rework my table in order to figure it out. I'm going to use the same method we looked at last time. I'm going to say, let's guess what it is first. And then I'm gonna go down here and rework this, change this number that we're guessing to get closer and closer to the right answer, then we'll use Excel to use that same method called Goalseek to find that answer exactly. So I'm just going to choose like five here. And then I'm going to use that cell instead of hard-coding five. I'm going to use this cell to populate that five up top. Now because that cell is really the cell that we're guessing on, that we're going to be changing. I'm going to make that yellow by going to the Home tab font group and making it yellow here with the yellow bucket. And that's gonna give us then the total denominator, which you could just call denominator, I'm going to say one plus r to the carrot, carrot n periods, periods. And that's gonna be, I'm going to bring it to the outside, which of course is the whole denominator. Now that's the whole denominator. So I've got to bring this to the carrot now it's a little bit like how do I take something to the power of when it's in a computer or in Excel, it's gonna be equal to, I'm going to point to this 10, 7%. And then you've gotta go shift six, that carrot right there. Shift six on the keyboard is to the power of and then point to this cell, which will be five. So S6, carrot S7 and Enter. And once again, we got to add some decimals possibly here. So I'm just going to add decimals. So there we have it. Let's keep it there. I'm going to add a lot of decimals just so I can get more detailed in the answer. Notice it doesn't, no matter how many decimals you show, Excel actually has the real number going on to whatever infinite number of decimals or however many decimals Excel can handle, which is a lot. So then we're gonna go up top to the font group and underline. And then that's going to give us our present value calculation because now we have the numerator and denominator in the outer columns. I'm going to divide this out. This equals the numerator divided by the denominator. So this divided by this. And so we're going to say Enter, and there we have it. We might add a couple of decimals by going to the Home tab number group coupled decimals here and there we have it. Now this kind of table can be useful because then you can kind of adjust your calculations on the left and in Excel and you can run different scenarios that way. So what we are solving for is actually this number of periods here. And so notice we could do this algebraically if we wrote it out this way. We can then solve for N Algebraically, or we can use trial and error. And this is the concept that we used when we use the Excel functions. That's why I think it's relevant to take a look at here. So we can then change the cell and say, okay, I need this end result to be 1 thousand. So what if I change this five to six? What if I change it to seven, and so on and so forth. And then I can ask Excel to do that method for us using that Goal Seek again. So I'm going to ask Excel, and let's change it down here. I'm going to say this equals to five again, I should have changed this number down here to six to seven. And that changed the cell up top. So I'm going to ask Excel, would you change this cell, this data input cell, to whatever number it needs to be to make this number b, what I know it should be, which is 1 thousand, that then getting us to the end result without using the algebra instead of solving for n. In other words, we're basically using the algebraic concept of having one unknown, but just basically forcing it to work through trial and error and having a computer do that trial and error for us. So I'm gonna put my cursor off the cell. I'm gonna go to the Data tab up top. Do this to the forecast, the What If Analysis and Goal Seek. And then once you're going to say Okay, excel, would you please set this cell to be the end result should be 1 thousand because we're looking to the present value to pull that 2 thousand bank to 1 thousand. So this needs to be 1 thousand. We want you to do that by changing this cell, the data input cell to whatever it needs to be to make that the end result that it should be 1 thousand. So we're gonna say, okay, and so there we have it. So I'm gonna say, okay here, add a couple of decimals because it should be 10.24. So I'm going to go to the Home tab number group coupled decimals, and there we have it. So hopefully you can see if you had a long formula or if you're using functions where you cannot do the algebra, then you can see how this Goal Seek method could work. If you set up your table, then you're kinda solving for basically some kind of unknown situation. Now we can do the same thing for the future value calculation. Over here is the future value calculation, is the future value equals the present value times one plus R to the end. Let's do our same thing building a table from this. And then, and then we'll have the unknown which is n, which will populate here. Then we'll use our goal seek to find that number. So this is the same kind of thing. This time I would like to present value and this whole outer section to be in the outer column. And then I'm going to pull any subcategories like this one plus r to the n into the inner column. And that's how you can set up your table, kinda like a tax return type of table to set up so that you can then put your data input possibly a little bit easier depending on what your workflow is and what you're working on. Let's start off with the present value. Then present value is gonna be, I'm gonna put that in the outer column. Present value. I'm going to put that out here, and that's going to be the 1 thousand. That's where we are at the start. And where do we want to be at the end? 2000s, that's where the future value is going to be. So then I'm gonna say the denominator. I'm going to put this in there with a colon so we can see that it's gonna be a sub calculation of this whole thing. This isn't the denominator. This is the other thing we're multiplying by, in this case, one plus r to the n. So I'm just going to put one here. The rate, the rate is going to be 7%. This is gonna be equal to u, then that 7%. Notice I'm not just hard-coding, are typing in to 7%. I'm going to pick it up from our data table. And then we're gonna go over, I'm gonna make that go to the home group. Number, number of group, home tab number group. We could add decimal 7%, but we would like to make it a percent by selecting the percent item, would like to underline it by going to the font group and then underlining it. And then that's gonna give us our one plus the rate, one plus the rate else have for the subcategory. You could do this by saying this equals to one plus the rate or z4 plus Z5 or deleting that use the trustee some function which you want to be able to see when you're going to use that and use it most often because it's the most common function equals the SUM of these two functions. This is by far, by far, by very far, the most used functions that you should know. Everybody needs to know that if you do anything in Excel, and then we're gonna go up top and go to the Home tab number group, adding a couple of decimals. There's our 1.07 or we can see it in the form of a percent per cent to find it. So there we have that. And then that's going to give us to the n periods and we're going to take that to the n periods. So we're taking that to the n periods here, That's our unknown. We don't know that. So I'm gonna go down here and guess once again, let's put our trusty five, that's gonna be our guessing point. Then I'm going to take that from my data, equals that five and enter. I'm gonna make that yellow because that's gonna be our focus point font group making it yellow. We will then adjust that after we, after we do our calculation. And that'll give us then our rates. We're going to call this one plus the rate shifts six to the n periods, periods. And so that's gonna be on the outer column here. So this is going to be equal to the 107 per cent, which is Z6 shift six or carrot to five periods, five years, and enter. There, we have it. I'm going to underline the five before I forget font group and underline. I'm going to then adjust the calculation here by going to the Home tab number group. You could add decimals, you could make it a percent. Let's just add decimals. So we can see multiple decimals here. Excel will calculate the decimals, whether you show the decimals are not. So no matter even if this cell was only showing one. It's still really is not one. If you calculate with it, it's going to be including those decimals. Really need to know that when you're working with Excel or it will confuse you. And so then we're gonna go to the outer column. And we'll multiply these two out because now we have these two components in the outer columns. So this is gonna be the 1 thousand times this decimal number we came to. There, we have it. Let's add a couple of decimals here by going to the number of group, picking up a couple of decimals there we have it. Now this time, this end result here, we know should be 2 thousand because it's the future value and we want it to double because that's the question. When is that going to double? We need to be changing then this period here, which we could do by solving algebraically, or we're going to use the Goal Seek method to use that same concept of having one unknown. But instead of rework in the problem, simply use trial and error to make it work, and then use Excel to do the trial and error quite quickly with the Goal Seek feature. So in other words, I could just change this cell and say, well, what if this was six? What if it's seven? What if it's eight, and so on until I get up to 2 thousand, or I can ask Excel to do that. By then go into the Data tab, go into the forecast, what if analysis, and then the Goal Seek tool. And we're going to say Excel. Would you please set this cell right there to be the end result we know it should be, which is 1 thousand times two or 2 thousand future value. And then by changing the data input for the number of periods which is in this cell, the eight. So change this cell to wherever it needs to be, which will change this cell, which will then change this cell to what it should be, which is the 2000s. So we're going to say, okay, and then Excel just does it really fast. That's what Excel does. There we have it. And then I'm going to add a couple of decimals up top here. Let's go to the Home tab. Let's add a couple of decimals here. So there we have it. So that's it, that's useful skills to basically have these skills that we have. Setting up tables may not be as useful for test-taking skills where you, they're just going to give you a piece of paper and a pencil. But in practice, this kind of skills, hopefully you can see how they would have value and you, when you're looking at more complex situations, understanding how to set up a table, understanding how to basically pull that information from a set of data. Understanding how to basically be able then to adjust this set of data and looking to see how to use the Goal Seek feature to solve for an unknown function or components can be quite useful, especially in circumstances when you're looking at a scenario that's bit more complex, then something that you can just solve algebraically or even this equation is getting to the point where it's, you know, it's a little difficult to solve algebraically. Let's go ahead and format this a little bit more while you're here. Notice that this, this colon has a subcategory kinda calculation. A lot of times then you will indent the inner columns just to make it look nice. And this is the end result over here. So what I'm gonna do is I'm going to select from the rate down to this period. I'm gonna go to the Home tab up top numbers group. Let's increase the indenting. So notice I can increase the indenting without double-clicking in it and spacing it out that way. So it's all uniform in that way. And you can give it a nice little look. I'm going to indent this one more time because I pulled this into the outer column. So I'm gonna go to the Home tab Alignment and indent that again. So there we have it. Now you've got this colon means there's a sub calculation. That means we're pulling it into the inner column. These are indented and then this is the subcategory or end of the sub categorization, which has now been pulled out to the outer column. Again. Let's put a bracket around here. Bracket around that. Okay, let's do that. Same thing on this one. So we're going to say, alright, this is the subcategory. All these cells dinner are going to be indented to indicate that it's a subcategory also indicated by the colon by going to the Home tab Alignment indent. And then this is the end of that subcategory that we put out into the outer category, which I'm going to indicate by a double indentation, Home tab Alignment, indent again. 5. Present Value & Future Value Tables: Personal finance practice problem using Excel, present value, and future value tables prepare to get financially fit by practicing personal finance. We are in our Excel worksheet. If you have access to the Excel worksheet, what I'd like to follow along, note that we're down here in the practice to have as opposed to the example tab. The example tab in essence being an answer key. And this worksheet, we're all the way to the right and column AC, where we're going to see our tables that we will be working with to get an idea of where we stand and what we've done thus far. I'm going to scroll to the left until we get to the end here. Notice this worksheet is, has hidden cells before column o because we Starting at column a. So I'm going to unhide cells by selecting column o, putting my cursor on column, ON trying to drag all the way to the left that I can let go. Right-click on the selected area. And I'm going to unhide these cells. I'm going to unhide and that should give us all the sales. So I can now scroll to the left and get all the way back to column a. So our original scenario we had that were worked multiple ways is we're going to assume the value of the property is increased at a rate of 7% per year. We're thinking about how many years it would take for something to double. We used the rule of 72 to get 10.29 years. Then we made it more concrete, assuming an actual value of 1 thousand that we want to double that meaning to get it to 2 thousand, How long would it take? And once again, we can do that multiple different ways. We did it with a running balance. We got between 1011, of course, close to the 10.29. We did it with present value and future value formulas to get to a more accurate answer of the 10.2410 points to four. We did it with formulas to also get to that 10.2 for calculation present value and future value formulas. Now we want to see the same thing with regards to the tables. Note that the tables are something that we're quite useful, especially before we had things like financial calculators and excel, or when they weren't as relevant or prevalent. At that point in time. You could structure the tables and that would be a way to easily do the calculations without having to do the math. That can be quite useful, especially as you get into more complex situations where you have multiple present value calculations. So they're not as useful these days because obviously in practice you'd probably be using Excel or you be using a financial calculator. I think Excel will become more and more prevalent than even a financial calculator, possibly because the spreadsheet program gives more flexibility, or you're gonna be doing it in that format. Therefore, you still want to know the tables though, because it's useful to know kinda how the tables were built, what they're doing. And then if you do a test question scenario, then they're often going to use the tables because that's the way to take away excel, take away your financial calculator. So if you're in a school setting, you're quite likely will still see tables even though they're kind of out of date to some degree for standard use. In practice. That means so we're gonna do the same thing if we had this question, then notice we did this with a present value table and a future value table, even though we're not really getting to the end result of the future value or present value. We're looking for the number of periods. So in this case, when I, when I use these tables, we gotta, we gotta use them a little bit more. Unusually, basically backing into the format that we know which is going to be, that we want to know which is gonna be the number of periods. So how can we do that? First of all, we got to think about the correct table to be using. Note that when you think about future value and present value, there's basically four tables you want to keep in mind, and you want to be thinking about them in groupings of present value and future value. Present value tables, future value tables, each of them then have present value of one and the future value of an annuity or an annuity. So we have the present value of one and the present value of an annuity. An annuity means there's a series of payments as opposed to a one, which is just one singular payments. So if they're talking about something out in the future than if there's only one payment, then you're pulling that back. You're using present value of one. If it's an annuity situation, multiple kind of payment things, then it would be an annuity future value, same kind of concept. You've got the future value of one, which is what we're thinking about now. We're using now this one concept, not the annuity or the future value of annuity, which you would have multiple payments. And trying to think about where you'd be at the end with multiple payments. So you've got to make sure you pick the right table then clearly, most of the time the table is going to be labeled present value, future value. And then the tricky thing for most people is to figure out whether you're using an annuity or present value of one. Again, once you get the concept between an annuity and present value of one, not too complex. We're using the one table's not annuity tables. Here's a present value table. You can see up top, we've got the percentages and on the left-hand side we've got the number of periods. Now the number of periods can be confusing because oftentimes we think of that as yours. Like concretely, that's not necessarily the case all the time because the periods Or whatever the periods need to be to tie out to the interest rate. So if we were to be compounding monthly or half yearly or something like that, then the periods are not necessarily years in our case, they are years because we're looking at this in a compound year situation if the rates up top are similarly confusing. And that when you hear a rate, usually the rate means per year. So if I was to say even on something like a mortgage interests or something like that. And someone wants to say you're gonna be paying 6% or something, you know that the payments are going to happen on a monthly basis, but they're not usually given you a monthly rate unless they tell you it's a monthly rate, they're giving you the yearly rate. Because the yearly rate big enough that it's usually between 0100 and it's an easy way to give. However, when you're looking at monthly rates, then you wouldn't need to match up the right amount to the period. And so you'd need to be matched up whatever the period is, two the rate. Now, as you're looking at periods that are less than a year, the tables become less relevant because the rates will be more confusing. If you're looking at monthly rate, you're gonna get to very small numbers, oftentimes. And that's why the tables are gonna become less useful. But the concept would be whatever the rate is has to match the period. If it's a year down here, then these are gonna be yearly rates. If the periods are half years or something like that, then you can imagine these to be basically half-year rates. Now what we're looking for then is the number of periods. The unknown is on this column on the left-hand side. And we're incident, we know what the rate is. The rate was 7%. So we know we're somewhere in this column. If I was to look at the table and I'm going to be somewhere down down below in terms of how far down do I go to get to the number of periods. So what I'm looking for then, these, these rates, I'm looking for the rate that's gonna get me basically to the present value, imagining a situation in our scenario that we know the future value, which would be 1 thousand times two, which would be 2 thousand. And we're trying to get it back to the present value, which means we'd be taking half of it. So just using the table conceptually knowing that you can say, alright, well that would mean then you can imagine like a book problem given you this table and asking you this question. Well, then I met the seven-percent. I'm looking at it to be cut in half. I'm looking for the percent then to be 0.5 about. So if I scroll on down, you can see between 1011, sometime between 1011, the rate goes from 0.50832.4751. So you would think then the time period that it would double at seven per cent would be somewhere between periods 1011, which of course it is, and recalculate it to be the 10.24. That's one way. That's one way we can look at if we're using these present value tables. Notice we can also look at it with the future value tables. So you can imagine if we were given a future value table down here. Same kind of thing they're saying, Okay, well, we've got the rates over here. We don't know the number of periods. If I'm looking at a future value table, then I would imagine that I'm starting in our data at the 1 thousand. And I'm trying to get to the end point, the end value, which is going to be then 2000s. So what I would assume then as I'm looking for the amount that's going to be times two. So if I go to my table down here and I was given this data, say, alright, well that means that I know I'm in the 7% column. I know I'm in the 7% column and I need, I need something that's gonna be a two basically, so that it would be doubled. I'm looking for this column, the unknown period over here, I'm in the 7%, I'm looking for it to get the two. And obviously once again, that happens someplace between 1011, that, that's when the rate goes from 1.9672 to 2.1049, indicating that we would see that that kinda doublings situation happening between 1011. Which of course again, that's the same result, that's 10.24 is the actual result we have. So that's another way you can kind of see these calculations. Now, note again that we kinda back that we used a problem here we're backing in to the present value calculations, which we saw when we looked at it as calculating it this way, looking for the period. But that kind of problem is one that shows you the relationship between the present value and the future value. And hopefully you can see how the table is kind of tied together in that way as well. And if you are working with book problems and they're giving you test questions with the use of tables taken away your calculator, quite likely that they ask you a question like this, which is a little bit more unconventional, which is forcing you to back into the period as opposed to looking at the, at the rate down here to simply calculate the present value and future value. So we'll do a lot more practice on the present value and future value. We will try to show it as we go through each of these problems, the different formats that you would, you could use, you could be asked to use or that you might see other people use and you want to be aware of what they are doing so that you can conceptually follow what they're doing and then best practices for you to use for whatever engagement you're in within practice. 6. Rule of 72 & Running Balance Calculation Prob: Personal finance practice problem using Excel, rule of 72 and running balance calculation. Problem number two, prepare to get financially fit by practicing personal finance. Here we are in our Excel worksheet. If you have access to the Excel worksheet, I'd like to follow along note that we're in the practice tab as opposed to the example tab. The example tab, in essence being an answer key, we have the information on the left-hand side going to populate that into the blue area on the right-hand side. This is the second of a similar problem. Therefore, we're going to pick up the speed a little bit in the Excel worksheets. So if you want to go to a problem that might be a little bit slower as we worked through it in Excel, take a look at the prior problem that is of a similar nature, will still try to keep it relatively slow as we go through. We have the yearly earnings per cent of an investment is 12%. So we are assuming we have some kind of investment that could be stocks, that could be some bonds, that could be a savings account or a CD or something like that, which we're going to make the assumption is going to be increasing annually at 12%. Then ask the question of how long it will take for the investment to double. So note what we don't have until we get to this point down here is what the investment actually is. You could ask this question, you might be asked in a test question, how long it would take to double without actually having the investment. And sometimes if you were to be asked that it's useful to then think of an investment like a $1000 or 100 or 2 thousand, whatever the starting point might be to help you to visualize what's actually happening. For most people, it makes it a bit more concrete. But for the first rule, the rule of 72, we're gonna, we're gonna not be using the investment amount down here and just think about any kind of thing that's going to be appreciating, how long would it take to double if the rate of return was at the 12 per cent? So the rule of 72 is an estimating rule, a shortcut rule. It can be useful if you have to be doing something on the go or in pressure, but also it's limited. And we will talk of course about those limitations and other ways you could do a similar calculation that's more flexible and possibly some that will give you more information in the future. So the rule of 72 is simply going to be we're going to take the Rule of 72, I'm going to call it. Note that I'm going to start to build our table whenever we do these calculations, even though this is a basic calculation to get an idea and field for entering this into an Excel sheet and also pulling our data from a data input tab on the left whenever possible, that allows us to run multiple scenarios. I'm going to start off with a hard-coded number, just 72 because it's the Rule of 72. We'll type in here. And then I'm going to pick up the rate down below. We'll type in the rate. Now the rate is going to be, I'm going to pull that into the inner calm because we're going to make a little adjustment to it. We have the rate at the 12 per cent up top, which you could simply type in here. But we would like to take the rate from our Data tab on the left-hand side. Therefore, I'm going to say equals. So I can pull the rate over here from this cell by simply clicking on it. There's B2. And the rate I'm going to say Enter, it shows 0 because the cell has no decimals in it. It's not actually 0, but it shows 0. We're gonna go to the Home tab Number group and we could add a couple of decimals that would give us our 0.12 or we could make it a percent by goto, the number group and presenting it, making it a per cent, I call that percent, define it, which is not a formal term. And then we can go down here and we're just going to take that and multiply it times 100% because the Rule of 72 really isn't a percent. We want to make that percentage into just a number which is 12. So it's just gonna be 12 instead of 0.12 or 12 per cent. And so I'm gonna do that by going to the Font group underlying this cell. And then I'll just multiply it out. I'm going to put that in the outer column. So we're simply going to be taking the 72 divided by 12. And so we're going to say this will equal then the 12 per cent, which is in cell E3 times the asterix on the keyboard, the 100, that will then give us simply 12. And then I'm going to underline it font group and underline. And this is gonna be the years, years, years to double bowl, which is going to be equal to the 72 divided by the 12, which is going to be F2 divided by F5. And there we have it. Now I'm going to add a couple of decimals just in case it's not even, I think it is even in this case. So we're gonna go number, Group, number of group down here, add a couple of decimals. So we're simply at, so there we have it. And now that's an estimated number. It's not exact, but that gives you a nice estimate. And obviously we did this in a formal fashion. You could just say, Okay, it's the percent which I'm going to make into just a number which is 0.12, but 12, when we're using the Rule of 72. And then I take 72 divided by whatever the interest rate is. Like a number format instead of a decimal or percent format. To get to the years that it would take to double. Let's go ahead and prove that in one sense and give us a little bit more information by doing a running balance calculation. Now these running balance calculations are really useful to be doing. So, to do this, let's hide some cells. I'd like to see my data on the left-hand side, right next to the cells that I'm gonna be working with on the right-hand side. So I'd like to hide columns d over two, whatever. This is, G over here. I'm going to put my cursor on the drop-down, left-click, drag over to G. And you're not required to do this by the way, but just to practice hiding sales to maneuver around your Excel worksheet, right-click on that selected area and hide it. Now we got our new data, new table right next to our data so we can work with it. So what I wanna do here is just set up our table. This is a running balance table, very useful to set up. Very easy to set up once done a few times. First I'm just going to set the years up on the columns. So I'm going to say one, enter, two, enter. That's all you need for Excel to see the series, which is obviously 1234. Actually, sorry, I'm gonna start here at 0012. Let's do that 012. Then I'm going to select those three cells. And then I'm gonna put my cursor on this little item here, which is the fill handle, they call it. And I'm going to click on it. Notice the fill handle can't look like that. It's got to look like this. Click on it, left-click and then drag it on down. Seven periods. Noticed a little seven that pops up, giving us that series of numbers for us, which is a nice, useful tip. And then we're gonna go up top Home tab. I'm gonna go to the alignment. I'd like to center those years and have them centered. So I'm going to center them there. We have it. That's just you don't have to do that, but that's just I think it looks better. It looks better, so I'm going to do it. So then we want to start off with our investment. Now we're going to assume the investment is 2000s, actually using an investment and now making it more concrete for us, determine how long it will take for that 2 thousand to double, which means it should be 4 thousand. If it were to double. Obviously, if we were just picking a number and we weren't given a number, and we want to figure out how long something would take to double. We might better use like 100 or 1 thousand or something like that. But just note that this Dublin concept we can use whatever the investment is because it's gonna be the rate that will be the factor to determine how long it will take for it to double. So I'm going to store it over here. I'm going to say this equals, I'm not just going to type in 2 thousand, I'm going to say it equals this 2 thousand and then Enter. We'll do our calculation now, if we, if we did this in a calculator, it would be the 2 thousand times 12%. Let's just pull up a trusty calculator. So when the trusty calculator, we just simply have the 2 thousand times 0.12 and that would give us the 240 interests for the first year. And so that's what that's gonna be our calculation. We'll do that basically here. But once again, I want to use it, not hard-coding the numbers. I want to use the data so I can copy this thing down into the table. So this is going to be equal to u. I'm going to pick up this 2000s up top. That's why I put period 0 because I don't want to pick it up down here because I would like to copy the table down. And then I'm going to multiply it times this 12%, which is in the format of a decimal. And then I'm going to say Enter. So there we have it, 240 to 40. Now we might add a couple of decimals there just in case there were percentages. There's not here, but there may be in the future if you want to see the pennies that's in the numbers group adding a couple of pennies. Then we can add this up. We could say, alright, well that means that we now have what we had at the start, 2 thousand starting investment plus the 240, which is gonna give us the 2 thousand to 42 thousand to 40. You can get to that in number, of course, by taking the 2000s times the 0.12, there's the 240 plus the 2 thousand. Or you could say, well, if this is 12%, that's a 100% plus 12%, 1 plus 1.12, which gives us 1.12 or 112% times the 2000s. And that'll also get you to that 2 thousand to 40. So we'll go ahead and copy this down. But before we do, let's do that same calculation a few more times just to get a feel for it. So now we're gonna be here in cell I4, I4. And I'm gonna do it again. This equals the 2 thousand to 40 and cell J3 times the 12 per cent. So J3 times B2 enter. And you could add some decimals there. So now we've got a couple of decimals, number group adding a couple of decimals. The investment now is where we were before, 2 thousand to 40 plus the 2.86800. We could add decimals to these 32 by going to the number of group at a couple of decimals. So there we have that there and we can keep, continue this on down. This equals then the 2508. Now it's compounding 0.8 there on J4 times the 12%. The interest is going up. Now it's at 301. Let's add some decimals, number group at a couple of decimals. Then we can then say, Okay, this is where we were before, after a period of two to 5.808 plus the 3.0601, adding a couple of decimals, number of group coupled decimals, that gets us to the 280986. Let's do it one more time. This equals the 2.86809 times the 12% 12% J5 times b2, adding a couple of decimals, number group, just animals. I call that desk normalizing it, which is a made-up number. But I think it's kinda fun to say desk the normalized it. And then we're going to say this is gonna be, you probably don't want to say that professionally, but could still fun to say. We're going to say that 280689 plus the 3.1837. That'll give us this number. We're going to add a couple of decimals. We're at the 3.041747. Now let's do this. I'm going to actually delete what we have done and tried to copy this down easily so we can do this and see how fast we can build the table and work on our Excel skills. So I'm actually going to delete these columns. We're going to recalculate it and think about how we could set this up so we could copy it down. This calculation once again in period one was the 2 thousand times the 12, and this one once again was the 2000s plus the 240. Now, if I copy these down, one way to think about this, what if I copy this down? Does it do what I want it to do if I, if I select those cells, take my auto fill handle and drag it down, does it do what I want? This doesn't look right, that 0 doesn't look right. If I double-click on it, then it pulled this cell down. And typically if you have a cell that's outside of your table, meaning it's not in the blue area here, then it's gonna be something you're going to have to tell Excel. I don't want you to move that down by using what we call an absolute reference. This cell is actually doing what we want. This cell is moving down relative. That's actually what we want. And this sound is moving down, so this one is what we want. And you can kinda tell even though this is more complex, because the two cells involved here are both inside the table. They're not coming from the dataset. So it's less likely that we'll have to do anything to them when I copy them down. So what I'm gonna do is I'm going to delete these two and I'm going to fix that one, which is this 12%. I'm going to double-click on this item that 12% is in cell B2, so it's in B2. I'm going to put my cursor in the b2 and that formula plus F4 on the keyboard that puts a dollar sign before the B and the two, noting that you really only need a mixed reference with $1 sign. But an absolute reference is easier conceptually because it can get a little confusing to know which dollar signs you need. It's useful to figure out which dollar sign you need because that gets your Excel skills compounded. It gets you thinking better, but there it is. Now, note also that the dollar signs have nothing to do with money. By the way, that it doesn't have anything to do with money. It's just like a code. It's Excel code in essence saying Excel, do not move this cell down. Do not move the b, the column, or the row down when I copy it. Then we're gonna do it again. I'm going to select these two. Sometimes I'll test it if I'm not confident in myself, and I'll put my cursor right here and the auto-fill and drag it down. So it does it do what I wanna do this time? Double-click here. 12 looks right. That number looks right. It does indeed that B2 stayed the same because it has the dollar signs. That's exactly what I expected. So that's this one did just what it should. Okay, then let's copy it down. Let's select these two. Put our cursor on the fill handle, fill handle. So it looks like that. And then drag it on down, dragging it on down and there we have it. So we can see between year 67, that's when it's going to double, which is what we were looking for getting from 2 thousand to the 4 thousand. Now we could get a little bit more precise and say, well, what would be the fraction of a year and whatnot and calculate that using this method as well. But for now, I just want to show how to do this running balance to get that estimate or approximation and point out the fact this gives you a lot more data than just the rule of 72 or even formulas or functions that we can use in Excel, because it not only gives you the n number, meaning just the number of years, around six years, right? It gives you actually why. It gives you more detail in terms of the interests that's going to be involved. So the rule of 72, just to recap, if I unhide some cells, I'm going to put my cursor on column B, drag it over to column H. I'm going to left-click on it and drag from B to H So I can unhide these cells. Right-click on those cells, unhide them. So this the rule of 70 to give us a round six, it's actually an estimate, but that's a pretty good estimate, but it's restrictive. Notice that how much more detail you get when you run the table out. Now the same thing will be true when we go to two functions over here. I can use this function in Excel, which we'll do next time to get the payment, which we'll get a little bit more exact than the Rule of 72. And it can be used to figure out dates that are more than just doubled, right? We can say however long, we can figure different things out rather than just be restricted to a doubling. The Rule of 72 is, but it's still restrictive. It'll give us a magic number. So this running table, being able to understand that, put that together, really gets your mind understanding a little bit more on what is going on. In future presentations. We'll do the same thing with, with Excel formulas. We will then do mathematical formulas, will then consider Excel tables. These are all related concepts when you're thinking about the time value of money. And you want to know at least of all of them so that when people use them, you can follow what they're doing. 7. Excel Functions Present Value, Future Value, & Number of Period: Personal finance practice problem using Excel. Excel functions present value, future value, and number of period functions. Prepare to get financially fit by practicing personal finance. Here we are in our Excel worksheet. If you have access to the Excel worksheet, I'd like to follow along note that we're in the practice tab as opposed to the example tab. The example tab, in essence being an answer key, we have the information on the left are going to populate that into the blue area on the right-hand side. But before we do, just notice that this Excel worksheet and possibly your Excel worksheet, if fall away long has some hidden columns in it. We just want to note those hidden columns, what we have done in the past and be able to hide and unhide columns because that's a useful tool for working around with Excel worksheets. So we know there's hidden columns because the alphabet goes a, B, L, which is not how it normally goes. There's letters missing. So we're gonna put our cursor on column B here, left-click on it. I'm going to drag over to column L so that we have the hidden cells in between. Let go, right-click on that selected area and unhide so that we can see what we did last time, which was the Rule of 72 and then a running balance type of calculation. You don't need to have done these in order to continue along. But just note these are different ways we're calculating basically the same thing, which is the information on the left being yearly earnings per cent of an investment or 12%. In other words, we expect the investment to be increasing by 12% yearly. And then we're asking the question how long it would take for the investment to double, which we can do without even knowing the original investment, which we're going to then assumed to be 2 thousand. But we got to six years. That's an approximation with the rule of 72. And then we assumed the investment starting at 2 thousand, then for it to double, it would need to be at 4 thousand. We ran a running balance to basically show that that would happen sometime between your 67. This running balance given us more information. Now we're gonna do this in Excel multiple different ways using functions. So before we do, let's hide these cells back up again. So we have our data on the left, right next to our calculations, which will be on the right. It very useful skill to know, we're going to put our cursor on column D, drag on over to column J or the one right after it, the skinny column K or H or whatever's after j. Then we're going to right-click on that selected area and hide them, hide those, those columns. So we're gonna be using our Excel functions to look at the unknown and we're gonna be using the present value function, the future value, the number of periods, all related functions to find the current unknown, we'll also be using Goal Seek so we can get an idea of how to use that tool as well. So once again, the data on the left says we have the yearly earnings per cent of an investment are going to be increasing 12%. We want to know how long it'll take to double. We're then going to assume the investment, the starting point at 2 thousand, that means for it to double, it would then be at 4 thousand. So the starting point would be at 2 thousand. We want to get the ending point sometime in the future to be 4 thousand and we want to know how long that will take. Now if we give that, if we have that question, then the most common functions that we'll be using most likely will be the present value function and the future value function. Those are the two functions most people know best. But the function itself, for example, on the present value function, is designed just like an algebraic equation to give the answer of the present value, which here is known. The present value is 2000s. What we're trying to find is the number of periods. But within this function is the function of a number of periods which is similar to an algebraic equation, which has one unknown. And as long as there's only one unknown, we can still use the algebraic concept back into what that unknown will be. And so we'll do that and we'll do that using basically a Goal Seek function. So instead of reformatting the equation, we'll use a Goal Seek function, which will basically use trial and error to use that same algebraic concept to search for the one unknown. So we're actually going to start this time down here with the present value calculation, because that's probably the first calculation that comes to most people's minds. And then we're going to back into the unknown using our Goal Seek. Then we'll do the same thing for the future value that helping us to see how these formulas are related, functions are related. And then we'll go to the most straightforward, but least well-known of the three functions with the number of period function, which is actually the direct function that will be used to find the answer most clearly are most easily. Okay, So let's start with our present value calculation. We're going to enter the function now last time. In the first problem, we use the dialog box to do this and went back and forth between the dialog box and just entering it straight out. The dialog box can be found by going to the formulas up top and the functions library section, and then the insert function, you could find the dialog box up here. I'm not gonna do that this time because I think it's useful just to note how to enter this data using a straight formula. And so I'm going to just enter the straight formula. So I'm gonna go back to the Home tab up top, which is where we usually want to be if we're not. Using our ribbon up top, I'm going to go down here. I'm just going to type in equals present value. There's our formula. We could double-click on it down below, or we can hit Shift Nine, put in the brackets in place. And then here's our series of data that will be separated by commas. There we just want to get used to entering in this format. And we'll be able to kinda figure this out as we go, as Excel sees it using kind of like the code of Excel down here, that kinda computer code the language of Excel with this little dialogue box. So we've got the present value, we're going to pick up the rate. Now I could just type in the rate at 0.12, but I want to pick it up from our data on the right-hand side, on the left-hand side. So I'm going to select cell which is cell B2. There's the rate. I'm going to put a comma which will take us to the next argument or the next component, the number of periods. Now that number of periods is what we do not yet know. So what I'd like to do is put something there. I want to put something that's in this box there. Notice it's blank right now because that's the unknown. That's what I would like to back into. So also note there's a couple of ways that we could do that. I could look for another function in Excel, which would be the NPER function to get to that number directly. Or I could use the algebraic concept here of saying, hey, that's the unknown. As long as they only have one unknown, I'm going to back into it by using a trial and error function. I won't rework the equation, but I'll simply use trial and error. So I'm going to pick a number here. I'm going to keep it at 0 or add some number there first, and then I'll go back in there and adjust it. So then I'm gonna go to the next argument which is gonna be the payment. The payment is used for an annuity calculation. And we're not talking about an annuity because there's not multiple payments here. There's only basically one amount that we're trying to see how much it will grow by. If it was just growing by average or same amount. You could then put two comments here, or you could put a 0 and a comma. Last time we did the 0. Once you get used to this, You're gonna get, you're going to want to just do two commas. That's the more professional thing to do, which will take you to the next argument, the future value where we expect to be at the end, we know to be 4 thousand, because if we were going to double this 2000s, we need to be at 4 thousand. I'm not going to type in 4 thousand, but rather I'm going to select cell 2 thousand times two to keep with the practice of taking the data from our information on the left. Now I could simply hit Enter and it won't give me an error, I do not believe, but I should close up the brackets, which is Shift 0. That'll close the brackets and Enter. So there we have it. It gives us a negative number here. So we don't really, we don't want a negative general, I will typically change the sign by double-clicking on it. And then normal, a lot of people will argue that you should put the negative in the number of periods, the payment calculation or the future value calculation. But I'm just gonna put it right before the P, flipping the sign of this thing that'll basically multiply it times negative one. Or you can think of it as just simply flipping the sign. And that'll give us a positive 4 thousand. Now that equation doesn't quite work if I'm using a cell that has nothing in it. So what I'd like to do is put something, just guess a number here, like five and then see what the result will be where at the 2000s to 70. So what I'm gonna do then is say, Well what if i guess, what If I guess like 5.5 or something like that? Now we're at the 2145. What if I select seven? Now we're at the 18 O2. What we want to do is to get this cell to be where it needs to be, to get that number then to 2 thousand. And then we can use a Goal Seek function in order to do that, which will just basically use trial and error instead of reworking the algebra. So I'm gonna go back up top and do, and do that by going to the Data tab. Notice I'm not on any of the cells. You don't need to be on a sale. I typically don't like being on a cell that I'm gonna be working in because this isn't entering in a new function. It's just going to change the relationship of the numbers that are already in place. So we're gonna go to the forecast. What if analysis. We can want to go to Goal Seek, Goal Seek. And then I'm going to say, we're seeking the goal. We want to set this cell to be what we know it should be. This is the present value, meaning I put it in the formula that it's going to end at 4 thousand. I want to bring it back to 2000s here. So I want the ending result to be 2000s by changing then this number right there on the Goal Seek. And then we're going to say, okay, and it does that for us. We're at the 6.12, which is close to what we got with the rule of 72. This number is a bit more exact. Notice that might not be exactly decimals out. If I go to the Home tab and I add some more decimals, it's a longer number then that will round it to basically to decimals. And I'm in the number of group adding and subtracting decimals. Now we could do a similar situation with the future value. The future value we can have the same concept. And that'll hopefully give an idea of how these two functions are related. So this is the second most well-known function, depending on what you kinda use your calculations for. Let's do the future value calculation. Once again, you could do that with a dialog box formats up top. The insert function, this will give you a cheat screen somewhat that we saw last time to help you to understand it, possibly in a little bit more depth. It doesn't add a whole lot and it's not as quick to enter. So you really want to understand how it looks when you just enter the data. So I'm gonna go back to the Home tab and not use a dialog box this time and just simply enter this in, like we want to get used to being able to do by saying equals. So anytime we have a function or a formula, we're going to say equals and then FV, that'll bring up our functions. There's the future value which I could double-click on, or I can say shift nine, putting the brackets, which takes us into the future value calculation, our argument box down below, we've got then the rate which we could type in 0.12. But we want to take this from our dataset on the left, clicking on the 12% comma taken us to the next argument, which is the number of periods, That's the unknown, that's what we do not know. We're going to just select this cell, which we're then going to populate with some data and then use our trial and error and Goalseek to find that unknown comma, the payment that we have, that's gonna be a term that we'll use if we have an annuity calculation. This is not an annuity, but the future value of one calculation. So I can either put a 0 and a comma, or more professionally, just simply two commas which will take us to the next argument of the present value, starting point or 2000s. So we're starting at the 2000s. We want the end result to be 4 thousand because we want it to be doubled. Now then I could close this up with brackets Shift 0, or I can leave it open and Excel will basically close it up for us, saving us a keystroke at the end. So I'll just leave it open and say, Okay, Enter. And there's our 2000s. Now if I double-click on this again, going back into it, There's our bracket at the end, it added it for us. We then want to make it a positive number. We could do so by putting this argument, having a negative here, I'm sorry, negative in the present value. Or we could put a negative in front of the f, which is what I just typically to kind of flip the entire argument or the entire function, multiplying it by negative one. In essence, there's the 2000s. Now this number isn't accurate really because we don't have anything in this box. So I'm going to put in like five just as a starting point. Now we have something there. Once again, we can then use trial and error to get this in results to what we need it to be, which is 4 thousand, because we want to get this doubled to 4 thousand and know how long it would take to get there. So 5.5 and so on. 5.8, get us closer and closer. We could use Goal Seek, which I'll typically click off of any box here to do, to get Excel to do that for us. We're then going to go to the Data tab up top Forecast group. What If Analysis, Goal Seek. And then we're going to ask Excel, we're going to say Excel. Would you please set this cell to be what we need it to be? The end result answer 4 thousand by changing the cell that we want to find, which is that five-point eight to whatever periods or years it would need to be to get to that end result. So we'll say, okay, and it does it for us. There it is, There's the 4 thousand and once again, we get to this point 12. So you can kinda see how these two functions are related to each other, of course. And now we can say, okay, well the fastest way I could go there, the other way I can do this is instead of me kind of backing in and using Goal Seek, I could say that's the unknown right there. That's what I don't know. Why don't I see if Excel has another function where they kind of do the algebra for us. They reworked. I can't rework the function myself algebraically like I could if I had one known and unknown in algebra. But I can look for another function which you would think Excel would name that NPER since that's what they named the argument, which of course they do. So let's do it that way. I can say this would be the most direct way, but probably the least common function that we would actually use, the one that you may not kinda visualize as well in your mind on what it is. So in any case, we're going to say equals the NPER. Note you can also look for this other function in the Formulas tab and the dialog box up top, but we're practicing simply typing them in this time. So I'm going to double-click on it. Hold on a second. You can double-click on it, or you can hit shift nine. That will take you into the argument. Let's do it one more time because I kinda, kinda bubble that one. Kind of messed it up. So let's try it again, equals NPER. So you can either double-click on it here or like the other arguments. You could say shift nine, that'll take you into the argument. We want the rate, then the rate is gonna be the 12%. I'm not going to type in 0.12. I'm going to click on the 12% in our dataset, comma, the payment, then there is no payment because we're not talking about an annuity but fact, a one kind of component. So we're going to say comma, comma. You can put a 0 and a comma to get to the next argument or more professional T, two commas, the present value, our starting point is gonna be that 2 thousand and the ending point is going to be 4 thousand, which I'm not simply going to type in there, but rather take our information from the dataset as much as possible. Picking up that 2 thousand times two. Then I can close this up, shift 0, or I can leave it open and Excel will close it for us. So I'm going to say Enter. And there we have it now note, it gave me an error here. That's not because I didn't close it. If I double-click on it, it closed it up for us. But Excel wants a negative number somewhere, and it wants the negative number, I believe in the Present Value tab, you get an error like that. And I'm not gonna get into technicalities of why that is. But just realize that it usually wants a negative number in the present value. When you look at some of these present value calculations, if you're looking at these two down below like we, like we did, it'll give you a negative number and you get flipped the sign by putting the negative before the full function. But some of the other present value calculations, they're going to want an argument to be negative, to populate properly. So I'm gonna put a negative here and then Enter. And there we have it. There's our 6.12. So there's multiple ways we can kinda figure this. Now. Now note as we look at those calculations, they still are not. These are the fastest way that you can do these in practice. This is what you would probably do a test situation than they might make you do it with a algebraic equation and, or with tables or use the Rule of 72. And then also in practice, notice that you'd still want that running balance ability to do the running balance calculation because this is still kind of a magic number, people will learn to calculate this number and have no idea what that means or how to apply it to decision-making. And if you do the running balance calculation, that'll usually give you a better idea of what is actually happening and how this could be useful for you and practice with actual decision-making. 8. Present Value & Future Value Mathematical Formulas Prob: Personal finance practice problem using Excel, present value and future value mathematical formulas. Problem number two, get ready because finance is getting personal with personal finance. And we are in our Excel worksheet. If you have access to the Excel worksheet, would like to follow along. Note that we're in the practice tab down here as opposed to the example tab. The example tab, in essence being an answer key, we have the information on the left-hand side going to populate that into the blue area on the right-hand side. But before we do, we wanted to take a look at what we have done thus far. Note that you don't need to go through or have done the prior problems to work this component, but we want to see how the multiple calculations can't fit together. We also want to look at how to maneuver in our Excel worksheet noting here, which is probably the case on your worksheet, that we have some hidden tabs or hidden columns to the left because we're starting at column O. So I'm going to practice on hiding the columns so we could see what we've done prior to this by putting my cursor on column O and then dragging over to the left to go to that triangle, let him go, right-click on the selected area. And then I'm going to say unhide, unhide. And there we have it. Now I could put my cursor back down. I'm hitting the left arrow so I could go all the way back. It's still didn't do it. I'm going to try it again. It only unheated one column. I want it to unhide all the columns. Right-click unhide. There, I think I did it now I'm going to put my cursor down, go all the way to the left here we are in column a where we have the information. The information will be the same for multiple formats. That is, the yearly earnings per cent of an investment or 12%. Then we said, how long will it take for the investment to double? We did that with the rule of 72, basically taking 72 divided by 12, that gives us an approximation of six. We did a running balance calculation so that we can see the actual interest as it accumulates upward. We then did the future value, present value, and number of periods, functions in Excel, which is probably the easiest way, do the calculations in practice. And now we're going to take a look at the format with regards to the calculations. For a formula. To do these calculations, we have a similar type of problem as we did when we did our functions over here. As we look at the formulas in that, we will typically be looking at the present value and future value formulas when we're considering formulas, note that in practice, most of the time you'll probably be using Excel functions and running balanced type of tables. But in a course, you're probably going to look at the formula. And of course, the formula can help you to see the relationship between the present value and future value. We're going to use the present value and future value formula to figure out the number of periods, which is once again the unknown that's within the formula. Realize that if you look up the answer to some question like this in a book problem, they will probably rework the formula as we saw here in the function section, to be solving for the number of periods. But in practice, what you would most likely do is plug in the information either to the present value or future value formula. Solve for the unknown, which will be the number of periods. And that's how we'll format it here. So now I'm gonna go back and I'm going to hide the sales again so that we can get back to where we were in practice hiding the sales. So I can just see column o and to the right. And you don't need to do this, but I think it's good practice to do so I'm gonna put my cursor on this little skinny column, which is the n column, left-click drag all the way to call them a. Let go, right-click on the selected area and hide it. And so now we're just working with the information from column o to the right. Now we have our calculations for the present value. Here's the formula, Here's the formula for the future value. So the present value formula can be formatted as present value equals the future value. And you can think about that as future value as the numerator because it, because it times one over or future value as the numerator over one plus r, which is the rate to n, which is gonna be the number of periods. Now, obviously we can just simply plug our information into that formula because what we have up top is the rate. So we know what the rate is, we know what the investment is, which we're going to assume to be 2 thousand. If they didn't give you the rate, then and they're trying to get the number of periods, the number of periods, then you can basically pick up, pick a number like 1 thousand or 2 thousand or 100 or something like that to work through a problem. If it asks you to double it and you want to use a formula method to do so. Then we've got that, we've got to the number of periods, which is the unknown, and that's what we would then solve for. So if we were to do this algebraically, it would look something like this. We know what, we know what the present value is. That's gonna be 2000s. That's gonna be equal to the future value, which I'm going to just say over future value would be the 2 thousand times two, because we want it to double or 4 thousand divided by, divided by one plus r, which was the 0.12 or 12%. And then we're going to take that to the carrot of the number of periods n. Which is our unknown. And then of course we can solve for n using our algebra, reworking the equation to basically solve for that n number, given it's the only unknown, I'm not going to do the algebra for us here, but rather I'm gonna put this into the table in this format and then use our user trial and error method to do the Goal Seek. Because I think that's useful to build a table. And it's also useful to basically think about how the Goal Seek will work with an equation that we can kinda see how to work algebraically so that if we were to use functions or something like that, we can see how to apply the same concept. So I'm basically going to take this formula and I put this into a table which you can think of kind of like a tax return type of format, which is often useful to do basically in practice, if you were doing a test question, then you would of course, use the algebra with a paper and pencil. If you're in practice, you're often going to be using spreadsheets. And I would highly recommend using spreadsheets more and more often when you can build in tables can be useful. If I was gonna put this formula into a table format, I would try to get the numerator and the denominator on the outer column and any more subcategory calculations in the inner columns. This is also how financial statements will be built. So you can use the same types of conventions when building this out as you would when building basically a financial statement. We'll start with the future value, which is basically the numerator. So I'm going to say future value. We know that number because it's going to be twice. What does investment is of 2000s. So it's going to be 4 thousand. I'm not going to type in 4 thousand. However, I'm gonna say equals and polar data from the dataset because that's just our good practice to do. Times two. There's going to be our 4 thousand. Then we're going to pick up a subcategory which I'm going to label. And we could just label it basically as the denominator, but I'm going to label it as one plus r to the n. I'm gonna put a colon indicating that this is going to be something that we're going to pull it into the inner column and then sub-categorize it outside once we've finished the calculation. So I'm going to say one. And then the rate is going to be, to be this 12 per cent up top. So I'm not going to type in 0.12, but rather say equals and point to the 12 per cent. I'll call it the rate over here. That's gonna be the rate. And then this is at 0 because we don't have it in a percentage format. So we're going to go up top to the, to the Home tab numbers group. We could add decimals to make it 0.12, or we could make it a percent moving the decimal over two places and adding a percent sign. Then we're gonna go to the font group and underline it. There we have that and that's gonna give us our OnePlus are all, call it. We'll say that's the one plus r component and brackets. And that's going to, I'm going to add those up. Now you could just say add them up like the one plus d12 percent. And also note that you might see this one as a percent. If I was to format this as a percent, it would of course be numbers group format percent, 100% plus the 12%, which is gonna give us 112%. So I could use the trustee some function, most common function that you really should and must know. And that's gonna be the sum by far the most popular and function in Excel. So there we have it. Now I'm going to add that. I'm gonna go to the Home tab numbers group. You could make it a decimal which would be 1.12, or if you make that a percent, that would be 112%. And then we're going to take to the number of periods. So I'm gonna to the n, to n, which periods periodically. The number of periods is our unknown that we don't know what that is. I'm gonna put that down here as, as just picking a number. I'm going to say just five down here. And then I'm going to use that number in our equation and use basically the Goal Seek feature to then fix that number to what it needs to be to make our end result correct and see how to use the Goal Seek function in that way. So we're going to say equals the five down here. I'm going to make that yellow Home tab font group, making it yellow so that we can indicate that that's the thing that we're going to be adjusting later. And then that's gonna be our total here, which we could just call the denominator. But I'm going to say one plus r to the carriage shift six periods, periods. And I'm going to put that in the outer column. And this is gonna be the 112 to the carrot, five power of five. So this is gonna be equal to the 112th. That the way you go to the power of as they shift six or the carrot and then the five. So six, S6, S7, and enter there, we have it. I'm going to add some decimals here by going to the Home tab number group. Add some decimals which I called desk normalizing it. Which isn't, again, it's not a real word. You probably don't want to use that in a professional setting, but I kinda like saying it desk and it's been just normalized. In any case, it could have more decimals than that. I'm going to underline the five here, Home tab, font group and underline. And that's gonna give us our present value, bottom-line number down here, present value, which is now the numerator and the denominator are in the outer columns, so we can just divide them out because that's what we do with numerators and denominators. So we're going to take the num or numerator divided by the denominator or denominator. And that's gonna give us our 2270. Now we know what this end results. It should be 2000s because we said we were going to be at 4 thousand the doubling point. We want to bring it back to the starting point, which we know is going to be the 2000s. So we could then adjust this. We could say, well what if I adjust my number down here to kinda figure that out? So we're using our algebraic concept, which is basically saying there's only one unknown in this series of numbers. I know what the end result would be, but there's only one component. I don't know that I can figure out what that is. But instead of doing so by reworking the equation to solve for that component algebraically, I want to use the Goal Seek, which is quite useful, especially when you're looking at a much more complex tables situation or one in which functions are used. So we could then say, okay, well what if this was like six? What if this was seven, and so on and so forth like that? Or we can use the Goal Seek function. I'm going to click off the cell. I'm gonna go to the Data tab up top. We're gonna go to the forecast and say do our what-if analysis. What if? Then we're gonna go to the goal and we get the dialog box. We're just basically going to say OK, Excel, we would like you to set this cell to be by going to this cell here and taking it to what we know it should be, which is the end result of 2000s. Make that 2 thousand, please do so by changing then this cell with the seven in it. So take this cell to wherever it needs to be to make that end result what we know it should be, which is 2000s, we're going to say, Okay, and so there it gives it, that gives us that 6.1 to 6.12. And we're going to say, Okay, let's add a couple of decimals up top, making that a bit more exact here at a couple of decimals in there, we have it. Okay, so now we're going to, I'm just going to format it a little bit. So this indentation, I'm going to select these columns underneath it. And so I'm going to select it and show that these are basically a subcategory by going to the Home tab Alignment, indent them. So it's shown by the colon, it's shown by them bringing into the inside and now being indented. So it's redundant, but it's kinda useful and nicer to see. And then this one brings it back to the outside. So I'm gonna go up Home tab Alignment and indent there. That kinda shows that it's gonna be pulled over to the outside. Let's put an underline under this one. Home tab font group underline. We could do a double underline here if we want to be fancy, Home tab font group, and then double underline the double d on that one. Okay, So then let's do this again using the future value. So we can get to the same answer using the future value given us an idea of the relationship between these formulas, Let's go on over to the future value. Same scenario where we got the future value equals the present value times one plus r to the n. If I was to do that algebraically, we got all the data we need here because the future value is twice the starting point. So that would be 4 thousand, which would be equal to the present value, which is where the starting point is two thousand, two thousand. And then that would be times this time instead of a division problem, brackets one plus r, which is gonna be the 0.12 brackets to the carrot of shifts. And that would be the unknown. And then we can solve for it algebraically. So that's one method we could use that to what you were doing, a book problem most likely. But we're going to build this table out once again and then think about our Goal Seek function. The table being a useful thing to be able to build Goalseek, a good tool, especially with more complex problems. So we've got the future value equals the present value times one plus R somewhat this and this to be in my outer column. That's what I'm visualizing the table to be. Any other sub-categories such as this. I'm going to put inside and the inner column and represent that as a sub calculation. So we've got the present value's going to be the starting point here. Put that right in the outer column. That's the starting point of the 2 thousand. I'm not going to type in 2 thousand, but rather say equals point to that 2000's there, we have it. Then we're going to say that we want one plus r to the n, which is the other half here. The other half were working with, I'm going to make a colon after it indicating that this is gonna be a sub calculation that we're going to pull into the inner column. I'm just going to say one. And then the r is going to be the rate. And the rate I'm going to say is equal to the 12%. I'm not just going to type it in or hard-coded as they say, but point to W2 cell, add some decimals by going to the Home tab number, we can add some decimals, 0.12 or see it as a percent number, group percent define it. That's what I call it percent define it. That's not a real word. But I kinda like the ring of it. I liked the sound. I like the sound of the percent of five, spin per cent of five. So in any case, Home tab, font group. And then we're gonna go to the drop-down. We're gonna put an underline here. And then this is gonna be the one plus the rate, which we'll call it. I'm going to sum that up. And notice this, you can think of this as 100%, this 11 plus r. So you can Home tab number. If I made that a percent, it would be a 100%. We can add this up equals the SQL. 100% plus 12% is 112% showing one until we format this thing, Home tab number, adding some decimals would make it 1.12. Or if we move the decimal two places to the right and add a percent, percent, define it as I would call it, but no one else does, but I think it sounds good. It would be 112%. So then we got to n periods, periods, periods and bracket. So it's going to go to the number of periods. This is what we do not know. So I'm just going to pick a number down here. Let's just pick like three just to have a number to be working with. And then I'm gonna go back up top and just say this is going to be equal to that three, that's the number we're going to change. I'm gonna make it yellow to indicate that the varying component, Home tab font group, yellow, yellow, fight it, then font group and underline it. And then this is going to be the brackets, one plus the rate, and then close it up, shift six carrot n periods, pure Riyadh. This is gonna be in the outer columns. So I'm taking the 1 12th% to the three periods to the power of three cubed, as they say. So this is gonna be equal to the 112%. Shift, six carrot to the three, to the power of three, and then enter. Now we're going to make that a percent by going to the Home tab number or we could just add decimals or percent defy it. I'm going to add a lot of decimals. This is when, this is when desk and normalizing comes in because there's a lot of decimals, you just add as many as you can just to get a good feel for it, knowing that the decimal still keep on going in Excel. Whenever you, no matter how many calculations or how many decimal points you show, Excel will calculate based on the actual number that's in there. So even if you're only showing one or 1.4, It's really calculating 1.404928. You got to know that or you'll get frustrated with rounding. So then we're going to say, here we have this, we're going to multiply out the outer column because we've got these two sides now. Outer column, that is the 2000s times the 1.404928. And that's gonna give us the 2810. So we'll use our algebraic concept once again knowing that this end result needs to be 4 thousand because we're getting to the future value, which is the 2 thousand times two or 4 thousand. And we want to get there by changing the component of three up top the number of periods. So you could do that with trial and error instead of reworking it algebraically down here and say, well, what if I put four here? What if I put five and so on and so forth? Or you can ask Excel to do that. So let's do that using the Goal Seek feature by going to the Data tab up top, go into the forecast, go into the What If Analysis. What if? Let's do some what if analysis with the Goal Seek. We want to say let's set what we're going to ask Excel because we're polite. We'd like to ask Excel, would you set that cell to be 4 thousand because we know it should be 4 thousand by changing, by changing this cell down here, would you possibly be able to perform that for us? It would be so nice if you would. In Excel is like, yeah, I can do that. And there it is. There's the 4 thousand they changed this to the 6.12. We're going to say, okay. And then we're gonna go to the Home tab up top font group. Let's just do some formatting here. This is indicating that it's indented again. So I'm going to indicate that as well by selecting these tabs and indent it by going to the Home tab, Alignment and debt there. And then I'll go to this last one down here and indent that again by going to the Home tab Alignment, indent again. So there we have our 6.12 and the 6.12 that we saw last time, kind of give it an idea of the future value and present value calculations similar to each other. We did similar things with the tables and or we did similar things with the functions. Next time, we'll take a look at how we can use tables to basically do a similar type of thing as well. So that'll be exciting. Make sure to return for that. 9. Present Value & Future Value Tables: Personal finance practice problem using Excel, present value and future value calculations with the help and use of tables, problem number two, prepare to get financially fit by practicing personal finance. Here we are in our Excel worksheet. If you have access to the Excel worksheet, would like to follow along. Note that we're down here in the practice tab as opposed to the example tab. The example to have in essence being an answer key within the practice worksheet. We're way over here and column AC, that's where our tables are going to be before we go into them, however, let's take a look at what we have done thus far. You don't need to have worked the prior practice problems to look at the table. It's useful to get an idea of how you can calculate this a few different ways as we do get into the tables, we also want to think about how we can move and maneuver around this Excel worksheet. I'm going to put the cursor back down. I'm going to go all the way to the left over here. Note that I have some hidden columns on the right because it should start at column a and I'm starting on column O. So what I'm gonna do is put my cursor on column o. Left-click, drag on over to the left as far as I can go, right-click on the selected area and then unhide, unhide. And so there we have it now I'm going to drag all the way to the left if it unhide everything. There, we have it. So now we're back to column a here. You'll recall our situation was we have the yearly earnings per cent of an investment, meaning an investment we expect to be increasing annually by 12%. We ask the question then, how long will it take for the investment to double? We got an approximate answer by using the Rule of 72 without even knowing the actual investment amount, 72 divided by 12 gives us the approximation of six. Then we assumed an investment amount of 2000s, knowing them that if it was to double, it would be at 4 thousand, asking how long it would take to get to the 4 thousand. The Rule of 72 saying around six years. We did it then with a running balance calculation and saw that that was indeed true, that in between 67, That's when we got to between 34003944 thousand. And we get to see our interests calculations in this format, which gives us a little bit more detail. We we then did the calculation using the future value and the Goal Seek method, the present value with the Goal Seek method and the number of periods functions. To get an idea of those functions, we did a similar type of calculation with the Excel mathematica. I'm sorry, just simply the mathematical formulas for the present value getting the results here and the future value getting the results here as well. Now, we're going to take a look at the use of the tables. Now note the use of the tables is gonna be similar. We have a similar kind of issue that we did with the present value and future value calculations. Because you'll recall what we're missing here. The data that we are missing is gonna be the number of periods. And when I look at the calculations that we're going to be considering, present value and future value calculations. Those are solving, of course, for the future value or present value. The number of periods is going to be an item within that algebraic calculation that we're basically solving for. So you could write it out algebraically as we sold down here and then solve for N when using something like the mathematical equation, you might say, well how can I do that with the tables? Then if I'm using the tables, then how can I kinda back into the number of n periods when I'm using tables, because typically with the tables, I would be using the appropriate tables to take the amount from the table to calculate the present or future value. So let's see how we can do this. This is a common kind of book problem. Also know, just remember that the tables were quite useful before we had things like financial calculators and Excel commonly. And then you'd need to use a table with tables can help you with calculations are much more quickly and they're still used oftentimes for test questions. So if you take this formally in a course, then they might try to take your financial calculator away or your Excel away and make your use tables. So it's still useful to understand the tables. It's useful to understand kind of how they're constructed, especially if you're gonna be doing this in a school type problems setting. So when you think about the tables, there's four tables for present value and future value. That one you want to consider too for present values, too for future values. So when you're thinking about a present value calculation, you've got to make sure that you're picking the right one of the two. Each category of present value and future value has two categories within it, which is going to be of one and then an annuity. So you have present value tables of one and then present value tables of an annuity. Annuity tables representing multiple series of payments. Whereas the present value of one, which is what we're looking at here, just involving the interests on one kind of components. Future value, same thing. You've got future value of one, future value of an annuity, the annuity viewing a series of payments. So we're not dealing with a series of payments here. We're dealing with r one, with r one number. The difference between the two not being multiple payments but being interests. So we're using present or future value of one table. So we can use either table. Let's first take a look at the. The present value table here, note that up top, you've got the percentages on the left-hand side, you've got the periods that periods to us our years. And if we're using the periods of years than the rates up top are going to need to be considered to be yearly rates. So just realize that those two things have to match. They happen to be yearly for us here. But that's not necessarily the case because the periods can be whatever the period is that it's kinda compounding at. So it might be monthly. Some bonds would be like semi monthly possibly that you would have the periods here. So these periods could represent something other than years, such as half years, semi years, six-month periods, in other words, or monthly. However, that the interest rates then up top would also have to reflect the rate per period. And remember that whenever you're talking about interest rates, when someone quotes a rate to you, even if they're talking about a monthly rate on something like your car payment which you know, you make monthly or something like that. If they say it's gonna be 7%, then they're usually talking about a yearly rate there, even though you're gonna be paying monthly. Because yearly rates is the convention in a similar way as to if I was to say what someone's salary is, I would probably if I said like 70 thousand or something, I'm talking a year. I don't have to say a year. That's the convention. The reason it's part of a convention is that the rates for a yearly rate are somewhere between 1100 generally. Whereas if you're talking about monthly rates or if you're talking about semi monthly or semiannual rates, six month rates, then you're going to start talking about rates that aren't even. And they're going to start talking about rates that are gonna be less, smaller type of rates which are harder to communicate and they're also harder to make tables with. So if you're dealing with book problems that are less likely to deal with problems that are monthly kind of calculations because they don't fit nicely into a table, because the table is not going to have the rates that are that small. But obviously in practice, you would use those because you would be using Excel, which can handle those types of calculations quite easily. So that's gonna be the idea. Now, what we have here is that how many percent, the percent that we have is 12%. So we know we're gonna be in the 12 per cent column wherever here. And what we're going to just look for is the point in time. I'm going to make this a little bit smaller, little bit smaller. So there we have it. And we're looking for the point in time that we get to the proper decimal so we know what the decimal amount should be. If it's a present value calculation, then we're imagining that we're starting in the future, which we're talking about something that doubled. So if it was $2 thousand and it doubled, we're starting at 4 thousand, we're thinking about the percent that we would multiply it back by to get to the answer of 2000s. So that would be 0.5. Obviously, 0.5 would be the percent that we're looking for, the decimal that we're looking for. So I know it's in column 12.5 as the amount. And then I'm looking to see what the periods will be on the left-hand side. So if I go down to column 12, I'm going to say, All right, where is 0.5 happen? And it looks like it happens somewhere between 67. So there it is, somewhere between 67 over here we got from 0.5 to 0.4523. So we would think, okay, the periods in years have to be somewhere between 67, which of course it is, it was 6.12. Same thing for that future value table. If you're used to a future value table, we can use the same kind of concept. Let me pull this table down here. It's tables all squished up. The tables all squished up and the other tip, so this is the future value table. Same thing, rates are up top periods on the left-hand side. This time, we're starting at the 2000s. We're trying to see what would be the, the number down here that would get it to double. Which of course would be two that we're looking for the column number 12 again, that would get us the number in the middle here of two because that's the that's the amount that would be needed for it to double. And then we'll see the related period on the left-hand side. So if we look at column 12, we go, Okay, where's the, where does it get to two? And it happens sometime between periods six years, six years, seven, it goes from 0.1.793822.2107. So right there between your 67 somewhere between there that's where that's what we're looking for. The doubling rate, which again of course is proper answer because that would approximate the 6.12. Now, note that this is a problem. If you're looking at portions of a period, that's something that table is not good at dealing with. That's a limitation of the table. So the big benefit of the table these days is often for test questions. And test questions that's going to limit you to tables will oftentimes then be using nice round numbers and be using then numbers that are going to be yearly type of number of calculations. If they then move to Excel or if then, or a financial calculator, then you might have rates that deal with months. And you really gotta kinda understand the relationship between these, these periods. And the percentage is, as we'll talk about possibly in future presentations whenever you're talking about something that's not a yearly kind of thing. And you need to kinda understand the convention of someone saying something is a yearly rate, even though you're, you're calculating it monthly. So then you gotta you gotta take that into consideration when you do a monthly type of calculation. 10. Rule of 72 & Running Balance Calculation Prob: Personal finance practice problem using Excel rule of 72 and running balance calculation at problem number three, prepare to get financially fit by practicing personal finance. Here we are in our Excel worksheet. If you have access to the Excel worksheet, would like to follow along. Note that we're down here in the practice tab as opposed to the example tab. The example tab, in essence being an answer key, we have the information on the left-hand side. I'm going to populate that into the blue area on the right-hand side. This is the third problem of a similar nature. Notice as we populate this problem, we're going to do something a little bit different with regards to just maneuvering around in Excel, we're going to try to use the keyboard a little bit more than what we did in prior presentations when we did more of the point and click with the mouse, using the keyboard and the arrows being a little bit faster, a little bit more efficient. So we're going to practice that our information on the left-hand side is we have the yearly earnings per cent in a savings account is 18%. Now, note, obviously that's high for most time periods, at least in the United States. But we want to basically use a higher percentage rate for practice. And note that you can have basically a rate like that, one in different types of accounts. If it was like an investment account or stock account or something like that, you might have a higher rate or estimating a return that you might have for something like a home than you might see a higher rate like that as well. Also note that no matter what the rates currently are for you, then you could end up with periods of high or lower inflation rates some point in the future, which can be a little bit difficult to imagine if you're in a period of very like one set of rate, if the rates are really low for a long period of time. In other words, it becomes difficult to imagine rate's going up and inflation taking place and whatnot. If you've been in a period of inflation for a period of time, it's kinda difficult to imagine that you'd ever be able to get that thing under control and have rates go down when you're doing your financial investments, you want to take into consideration that most likely at least hedge against the fact that inflation rates whatnot will most likely change over your lifetime. And you want to basically be taking that into consideration with your risk assessment and understanding the severe impact that can have on your different investment strategies. So we have how long will it take for the account to double? So we can answer that question then without even having an investment amount. And then we will consider the investment amount in this case being free thousand dollars. To think about, if you had $3 thousand as just simply a starting point, how long would it take to double? In other words, how long would it take to get to $6 thousand? So we're looking for the period of time then for it to double, we'll start off with the rule of 72, which is kind of a shortcut type of method. And then we'll go to different methods as we work through the practice problems, including a running balance. In this presentation, we're going to build our tables. We want to work on good practices with the tables taking our information as much as possible from the dataset on the left-hand side, starting with the rule of 72. Forward calculation, we're going to make it a little bit longer just to make sure that we understand it a little bit better. I'm going to type in rule of 72. We're going to start off in the outer column, simply hardcoding or typing in 72, which is not something we normally do because we usually take our information from the data. But given this is the rule of 72, we're simply going to hard-code that first number in place. Then we're gonna take the rate. Now normally you just basically divide by the rate. But we want to make sure that we understand what we mean by the rate when we're talking about the Rule of 72 because the rate can be expressed here in format of a percent at, in the format of a decimal 0.18. Or in this case, we want to convert it to an actual dollar amount or an actual number of 18. So the way we're going to convert it, instead of just typing it in here, I want to say equals. And now I'm going to use my, my keyboard and start practicing with my arrows instead of a mouse. This is what's new here with our Excel practice. I'm going to say left, left, left up on the keyboard. Notice the changing number here. We're now on cell B2. I'm going to say Enter, I'm going to hit the up arrow, go back on that cell. I need to change it so I can see something in that cell by going to the Home tab numbers group, I could add some decimals. There's the 0.18, or we could then change it to a percent, moving the decimal two places to the right effectively and adding in essence a percent. So there we have the 100-percent. Now what we're gonna do is multiply that times 100 because the Rule of 72 really wants 18 in number format, not in the format of a percent or decimal equivalent. So I'm gonna go back on over here, Home tab, font group underline that. I'm going to take this to the outer column, multiplying those two out. So I'm gonna do that with the equal sign. I'm going to say left arrow up arrow, up arrow. So there we are in E3. Note when I multiply and I say times, if I say any kind of multiplication or adding or subtracting any mathematical function there. And then go to the left again, it's not going to start from the 18. It's going to start down here on this cell. So I'm going to lay left and then up. Takes me to the 100. So there's the E3, E4 and enter. That gives us our 18 just in terms of dollars. I'm gonna go to the Home tab, font group and underline. And then finally, we're gonna get this as the amount of the years to double. And I'm going to go ahead and just divide this out, the two numbers in the outer column. This is gonna be equal. I'm gonna hit the up arrow now, up, up, up, up the F2 divided by Up Arrow, onetime F5 and enter. We have four. I think it is even in this case, which is simply an estimate. It's not actually exactly that number, but it's an estimate from the Rule of 72. I'm going to add some decimals anyways, Home tab number, adding a couple of decimals, about four years. So it'll take about four years to double. That means if we're starting with the 3 thousand, takes us about 144 years to get to the 6 thousand. If we're starting with, if we have an 18% growth rate, which would be quite high if inflation was low, and so on. Okay, so let's go ahead and hide some cells now. And let's see how that would look in terms of a running balance calculation. Really useful thing to be able to do with a running balance calculation. Before we do, let's hide some cells. I'd like to see this column over here, column H right next to our data. I want to hide from cells C to F. Let's hide from cell C to F. I'm going to put my cursor on the column, see the little skinny column. And then I'm going to drag on over to column F. And so I still want this space from column G, so I'm not going to delete that one or hide it. Don't delete them. By the way, hide them. Then right-click on the selected area and hide, not delete, but hide. So now it's been hidden. So we can see that up top between B and G, there's hidden columns there because that's not how the alphabet goes. It doesn't go from B to G. There's like letters in-between that I know there's at least a C in there somewhere. So now we're gonna do are running balance kinda calculation. I'll start with the ears. I'm going to say 0. What? I want to start with year 02 and so on. I would then like to select these cells and have Excel helped me out to do the rest of the calculations which I could have just typed in there. But if you're talking about a long series of numbers, it's quite easy to do with Excel with the autofill. Putting our cursor on the autofill handle, left clicking on it, dragging it on down to get to five. Then I'm going to go to the Home tab, Alignment Group Center. Those just to make them look a little bit nicer. It looks way nicer like you don't have to do that, but I think it looks nicer. And then we're going to start with the investment at the 3 thousand. I'm not going to type it in there because we're going with our theory of bringing the information from our dataset so that if we wanted to test it out, what would happen if 6 thousand or something, we can do that. So I'm going to say equals using the arrows again. Down, down, down, down, down left, left, left, left. Now we're on B7, that's what we want and enter. So now let's multiply this out. So we're going to multiply it out in the interests. This is one year out. We're going to take the 3 thousand times 18%. You can do that in a calculator 3 thousand times 0.18 or 18%. I'm gonna do that with Excel pulling the information from the 3 thousand here and 18 on the left, noting that we're thinking about later copying this down, which we will do anything that's outside your blue area, the table you're working in, anything that's in the dataset. When you copy it down, likely it will need to be absolute reference. It. In other words, you'll need to tell Excel, don't move this cell down. We'll do that later. We won't do it yet. So I'm going to say equals right arrow up arrow. There we are on J3, J2. I'm going to say times that brings me back into this cell. And then I'm going to say left from that cell, left, left, and up to the 1800s and enter. There's the 540. Now I'm going to add that to the 3 thousand using our cell references equals Up arrow. That brings me to the 3 thousand plus, that brings me back into this cell. Left arrow, there's the 540 and Enter. Let's do it again. Now we've got a new balance of the 3,540 times the 1800s, the interest is increasing because it's compounding. This is equals, I'm gonna go right arrow and then up to J3 times that pulls me back into the cell in I4. So when I go left, I'm coming from that cell up, up to the 1800s. So J3 times b2 and Enter. Then I'm going to add these two up again, equals up arrow the one above it. Plus that brings me back into the cell left arrow, the one to the left of it, and enter. Let's do it at least one more time here. So now we've got the 4,177 at the starting point equals right arrow up, arrow 4177 in J4. And then I'm going to say times bringing us back into the cell. And J5, left, left, left, up, up, up. So there's the J4 times b2 and enter. And then we're gonna go back over here in J5 equals up arrow once plus left arrow once, and there we have it and so on and so forth. Now we're gonna go back and say, what if I tried to copy this down and do this more quickly? I'm going to actually delete what we have done thus far so we can do that the easy way. This is the heartless is the long way. Let's do it the easy way. So I'm going to delete this. I'm going to start at the same point, which will be equals. Right arrow up to 3 thousand plus left, left, left up the 1800s, or times the one-thousandth, I'm sorry, 3 thousand times 18% or 0.18. And then we're in the 3 thousand plus D5 40, which will be equals up to the 3 thousand J2 plus left to the 540 in I3. I'm going to test this out. It's going to have a problem. When I auto fill it, I'm going to select these two cells. See what would happen if I copy this down using the fill handle, putting my cursor on the fill handle dragging down, does it do what I want it to do? It looks like it has a problem there. Let's double-click on it. The problem being that 1800s needs to be absolute reference, right? Because it's moving down, it moved down. I don't want it to move down because it's outside the table. Anything that's outside your table from a dataset often is the thing that you're going to need to make an absolute reference if you copy it down. So I'm going to delete what happened here and say, Okay, I'm going to fix that, then. I'm going to fix it. That's not a problem. That's not a problem. This one in cell B2, we're going to make that an absolute reference, putting our cursor in B2, you could do this by selecting F4 on the keyboard, putting a dollar sign before the B and the two, you only need a mixed reference $1 sign because you're only copying down here. But an absolute reference is usually easier conceptually just to understand, just telling Excel don't move that. So when I copy it somewhere, so we'll say, okay, oftentimes I will test this by taking it down one cell just to make sure it does what I want it to do unless I'm confident, and then I'll bring it all the way down. I'm confident here, but I'm still going to test it because that's what I do. If I wasn't confident, I'm going to auto-fill it down. And then I'm just saying is it doing what I wanted to let's double-click that one. Yeah, kept that one the same. B2 didn't move this one did move down just like it should from the 3 thousand to 435440. This one moved down this way. It looks a little complex, but everything's inside the table. It moved down just like it would, just like it should. So we're going to say, okay, let's copy it on down and see what she'd do it the whole way. I'm going to select these two. Put my cursor on the fill handle here and left-click, drag it on down to the five periods. And there we have it. Now we can see the interest increasing. We can see the impact on the investment. Note there are decimals involved. So if you wanted to see the pennies here, you can select this whole thing. We can go to the Home tab number group, add a couple of pennies and get the pennies involved there. And we can see then that sometime between year four and year five, we get to 6 thousand, so a little bit over your four. So it's not exactly four years as we got with the rule of 72, but it's close, it's around there. Next time we'll do more exact calculations with the Excel functions. Before we do, let's go ahead and unhide the cells up top. So now we've got these sales is unhide by putting our cursor on column B, dragging over to H, and then let go, right-click the selected area and then unhide those cells. So there we have it. So there we have our four with the rule of 72, a little bit over four. We can see with our running balance, we could make it more exact with this running balance to try to figure out the fraction of a year and whatnot. But we'll leave it there for now and we'll get to those more exact calculations over here next time. When we start to do our calculations with functions in Excel, then we'll use tables, and then we'll use the mathematical formulas to do the same. 11. Excel Functions Present Value, Future Value, & Number of Period Prob: Personal finance practice problem using Excel. Excel functions related to present value, future value, and number of periods at problem number three, prepare to get financially fit by practicing a personal finance. Here we are in our Excel worksheet. If you have access to the Excel worksheet, would like to follow along. Note that we're down here in the practice to have as opposed to the example tab. The example tab, in essence being an answer key, information is on the left-hand side, is going to populate that into the blue area on the right-hand side. But before we do note that we do have some hidden cells and this worksheet, you may have hidden cells and your worksheet, we'd like to practice on hiding and hiding the cells. And then think about different formats that we've looked at in prior presentations for these types of calculations. Not that you need to do them here to follow up with the calculations in the Excel worksheet. But just to get an idea of the different methods that can be used. So in order to unhide these cells, we're gonna put our cursor on column B, left-click on it, drag over to column l, let go, right-click on the selected area and unhide those cells. We have the information in column a. Now that we see here the yearly earnings per cent in a savings account, we're going to say is going to be 18%. Now again, that's high for a savings account, but we're imagining that there's gonna be an increase in some kind of investment of that 18%. You can have a similar kind of thing if we're talking about investments in stocks or investments at a home that's gonna be increasing in value. We then calculated how long it would take for it to double using the Rule of 72, which was around four years. We then estimated the beginning balance of 3 thousand, thinking about how long it would take to double to get to, in other words, 6 thousand if it were to double, we did so using then our calculation for a yearly calculation of interest. And we got between 45 years, which is around the same thing we got for the Rule of 72, which is an estimate. Now we're gonna do the same thing with regards to our functions in Excel. Notice that when using the functions, probably the best thing to use or what she was used most often if you were in practice, but you still have the limitation of the functions given you a magical answers such as four over here, similar answer without giving you a detail of what you can see if you actually make the table, which will give you the calculations of the interest on a yearly basis. So this kind of table format still is useful to do. Now we're going to hide some cells. We're gonna hide these cells again to practice, That's what I'd like to see is this data on the left-hand side, right next to where Column L or column K here. So we're going to hide the column. So I'm gonna go back on over to the right. And you could, if you want to see all the columns at one time, go down here and make this a little bit smaller and bring it down so I can see everything at once. And now I'm going to be hiding from c to k, putting my cursor on column C. So I hit the drop-down, dragging over to column J, letting go, then right-clicking on the selected area. And then I'm gonna go ahead and hide the selected area. So there we go, We hit it. So now we've got our data right next to our data input. I'm going to make this a little bit larger down here again, I'm at 220. I'm going to make it as large as I can to make it as easy to see as possible where at the 280. Alright, so now we're gonna go ahead and do the same thing with our present value, future value calculations. The most direct function to do this is actually the number of periods function, but that's also probably the most or least well-known function. When you work with present value and future calculations, you're probably more familiar with the future value and present value calculations. Let's start with those and think about how we could use them in order to back into the number of periods in a similar way as we can do with a mathematical function that has an unknown in it. That's not basically the name of the present value. If we use the present value formula algebraically, we can back into some unknown as long as we only have one unknown, same concept here, although we cannot rework a function, we can basically use that function to kinda figure out the unknown by using a tool called The Goal Seek. So that's what we'll do here. Now, here's our present value. Let's start with the present value of this is probably the first one people work with when they think about these kinds of formulas that would imagine that you have some number out in the future that you're trying to pull back to the current time period taken into consideration time value of money. So we're going to use our present value. We're going to imagine then if we're trying to think about how long it would take for something to double using our present value. To do that, we're thinking that the end point then would be doubling this 3 thousand to 6 thousand and then pulling it back to the current period with the discount rate of the 1800s, seen how long it would take the distance then in time to do so, and that would be the number of periods. So let's kinda figure that out. Let's start off here with the present value. And I'm just going to type this and note that. You could, and we did this with the first practice problem. Go to the formulas here, look at the insert function. To look at the insert function at table. I'm not gonna do that for these problems because I'd rather just type it in, which is probably the best way to do it once you get used to these formulas. And so that's what I think the best practice to actually do would be. So I'm going to say equals and we're just going to type in present value. You could double-click on the present value down below or say shift nine to put the brackets around it. Now we have our arguments down here with the rate, the number of periods, and so on. We're gonna be picking up the rates. I'm going to try to do this, navigating with the arrow keys in Excel as opposed to pointing with the mouse. So much because this is the third of a similar problem. So I'm gonna go up, up, up, up right, left, left, up, up to the 1800s. And then notice that this little dialogue box moved up. You can actually move it back down. If you want to place it wherever you think is appropriate, then I'm going to hit a comma to move to the next argument. That's the number of periods, That's the unknown, that's what we do not know. So we can do two things here. I can look for another function for the number of periods, which is gonna be the number of periods function which will be Excel kind of work, reworking it as you would think to do algebraically solving then for that component. Or I could use the function that I know present value and use the Goal Seek to figure out the unknown components. So what I'm gonna do is just choose this cell down here, which doesn't have anything in it but will populate soon, get the answer. And then we'll use basically Goal Seek to kinda back into using trial and error, what the answer should be. Then I'm going to say comma again. And then we got the payment. Payment is a term that would be used in these present value functions if you have an annuity, this is not an annuity present value of one. So you can either put 0 and a comma or more professionally, just simply two commas, then we want the future value. The future value where we end at would be the 3 thousand times two because it's gonna be doubled 6 thousand. So in other words, we're gonna give the value of 6 thousand discount rate at 18%. How long would the distance b in time then to get to the 3 thousand present value component, we're then going to say, then this is going to be, I'm gonna do this cell down, left, left, up 3 thousand times two. Notice I'm not hard-coding the 6 thousand. I'm not going to close up the brackets. You could, but you don't need to because Excel will do that for us. I'm going to say Enter, and there we have it. Now, this number is not accurate given the fact that there's nothing in this cell. So then you'd want to populate something in here just to make sure that the function is calculated properly. So I'm gonna put something like what if I put ten in here? Now we're at this 1146. The result in a present value will typically be negative. What you want, oftentimes a positive number. If you want a positive number, double-click on it. You could then put a negative number before the payment amount or the future value amount. But I like to just put it in front of the p here, taking the whole function, in essence, multiplying it times negative one. So I just put a negative right there, and then we get a positive 1146. Now we know what the end results should be. The end result should be, we're trying to do a present value. So we started off with 6 thousand to bring it back to 3 thousand. So we need it to be 3 thousand. So I could say, now I'm just going to adjust this cell down here to get closer to the answer to 3 thousand, right? If I get 976 and so on. And then I can use Excel to use that trial and error method to back into the unknown being the amount in this cell using Goal Seek. So we're gonna go up top to the Data tab. To do that, we're going to go to the forecast group. We're gonna go into the What If Analysis and Goal Seek. Goal Seek. Then we're going to ask Excel, we're going to say, would you set this cell right here to be what we need it to be, which we know is 3 thousand by changing then the cell with the six, with the six in it. Let's get to the cell with the six in it. And so we'll ask Excel to do that and say OK, and there we have it. And it changes it to the 4.19, which is more exact than we saw with the rule of 72. And I'm just going to say, okay, that looks good. Now we can do the same thing from the future value equation standpoint as well. So in the future value kinda thing where we would start, we're imagining we're starting at 3 thousand. We know what the end point should be, which is 6 thousand. We want to know how long it would take to get there. It's just going from the other direction. So let's look at this formula. We put our cursor in here equals that would be the future value. Now you could double-click on the FV or I can say shift nine to give us our dialog box down below. And then we're going to pick up the rate which I'm not going to type in as 0.18, but rather I'm going to use my arrows left, left, left, up to pick up cell B2. You could move this back down again. But note once I hit the comma, it moves it back down in essence for us. And then we've got the number of periods. That's the unknown. We don't know the number of periods which we're going to just pick up by populating this cell which has nothing in it currently. That's the one that we're going to adjust to get to the proper number. Then we're going to say comma. This payment represents if it were an annuity, it's not an annuity. So we're going to say comma, comma to go to the next argument, the present value, then that's our starting point, the present value, which is the $3 thousand. I'm going to get there using the arrows down, down, down, down left, left, left to the 3 thousand, you could close this up, shift to 0, but you don't need to, because Excel will basically do that for us. We have given us any problems. So I'm gonna say, okay, There we have it. It's not proper right now because once again, there's nothing in this cell. Let's estimate the ten again. Now we've got a negative 157 O two. I'm going to change the sign of this double-clicking on it, which you could do by putting something in the present value calculation, a negative number, in other words, or you could put it in front of the f, which I'm gonna do here. I'm just gonna put a negative in front of the f. There, we have it. Then I'm going to change this cell to until I get this number to what it should be, which I know if it's a future value, it should be twice the 3 thousand because we're trying to double it or 6 thousand, so it's too high. So I'm going to say, well what if I said like eight here, 76, and so on. And then I'm going to ask Excel to continue that process by going to the Data tab, go into the What If Analysis in the forecast group. Then we're going to say Goalseek. We're going to seek the goal. We want to set that cell to be 6 thousand by changing this cell. So please change that cell to whatever it needs to be Excel to make that cell B, what we need it to be twice the 3 thousand or 6 thousand. So we're going to say OK, and Excel does it and there's the 4.19 again. So we just did the same thing from the opposite direction, showing the relationship hopefully between the present value of future value calculations. Now the other way we can do this, of course is say, Well, why don't I just see if there's a function in Excel that's for the NPER, which is the unknown that we're solving for using the Goal Seek. So that's the next method we'll do up here. And we're going to say, Alright, that's kind of like reworking it algebraically. Although we can't do that ourselves, we can see if Excel has created another function, solving for that component, which in this case they have, of course, this equals the NPER. Double-clicking the NPER, or we could just sit shift nine to go into it. We have the rate. Then once again the rate I'm going to go over to the 1800s, hard-coding it in, but going left, left, left down to the 1800's, I'm going to say comma payment. Pmt stands for payment. There is no payment here because this is not an annuity. The payment would be for an annuity. So just simply comma, the present value is going to be our starting point, which is the 3 thousand. Now this is a little bit tricky. Excel is going to want us to have that as a negative number. I'm not gonna do it now. I'll put the negative in later just to show you what will happen. It'll be frustrated. And if you're not aware of that, and then comma the future value, we also know because that's double the 3 thousand or 6 thousand. I'm not going to type in 6 thousand because I want to pick this up from our dataset. So I'm going to say down, down, down, down, down, down left, left, left, times two. Then again, we could close this up, but we don't need to because Excel will do it for it for us, I'm going to say Enter. Now it gives us this number problem, saying we have a problem, that I'm interpreting that to be that we've got a problem. So I'm going to double-click on it. Now, if there's a problem with these functions, it's usually that Excel wants a negative number somewhere, which we solved down here by putting a negative, negative sign before the end. That's not typically where you want it here. You want to put it in front of the payment one, I think the present value item, I'm going to put a negative there. And then I'll say, Okay, and now it gives us the proper answers. So those are the three ways we can kinda see hopefully the relationship between these future value, the present value, and the number of periods functions in Excel. Notice even using it this way, we're still got this magic number that pops up here and we don't get to see the interests actually compounding as we do with the running balance that we saw last time, that running balance still quite important. Next time we'll take a look at this similar thing using the actual equations. So we can think about how to do it mathematically. And then we'll think about how to use tables, which is often used when you're thinking about book problems. 12. Present Value & Future Value Mathematical Formulas Prob: Personal finance practice problem using Excel, present value and future value mathematical formulas, calculations. Problem number three, prepare to get financially fit by practicing personal finance. Here we are in our Excel worksheet. If you have access to the Excel worksheet, would like to follow along note that we're in the practice tab as opposed to the example tab. The example tab, in essence being an answer key, we have the information on the left-hand side. I'm going to populate that into the blue area on the right side. But before we do, let's first take a look at what we've done thus far and similar problems on this same worksheet. You don't need to have done them in order to continue here. But we want to get an idea of the similar calculations that can be done to get the same result and practice maneuvering around our worksheet. You'll note here that we're starting on column o. There are hidden cells on this worksheet to the left. So I'm gonna go ahead and unhide them by clicking on column o, dragging to the left until we get to the triangle. Let go right-click on the selected area and unhide, unhide, that I'm going to put my cursor down, go all the way to the left with the arrows to see what we have in column a, which says yearly earnings per cent in a savings account, 18%, meaning we have a savings account that we're expected to increase by 18%, which is quite high, of course, but we want to practice a higher percentage here just because we've seen some similar problems in the past with other percents. And just realize that if you're talking about any kind of investment which a home, or if you're talking about stocks, it would have a similar kind of increased process. And if you're talking about a situation where things differ, like inflation hits or something like that, then you could end up with high percentages on things even like savings accounts and CDs and whatnot. So in any case, then we add how long it would take for an investment to double using the Rule of 72, we got four years at the 1800s percent. We then guessed an amount or pick up an amount of an investment of 3 thousand. And then thought about how long it would take with a running balance calculation to get to the double of that, which would be 6 thousand. And we saw that it was indeed between 45 years that that took place, the balance going from 5 thousand to 6 thousand. We then did a more accurate calculation with the functions in Excel using both or all of the period function where we got 4.19 years more exactly. We did that with a future value calculation and Goal Seek and the present value calculation using Goal Seek. Now we're on the present value calculations. We're gonna do this with formulas at this point in time. The formulas being a mathematical format similar to what we did over here with Excel giving us that one result number. And it has the same kind of limitations as well, in that we don't get the detail that we do with the running balance. But running balance still being important. Okay, so now let's hide these cells backup, so on you can just see column O and then we'll move forward. I'm going to put my cursor on column and drop-down. And I'm going to go all the way to the left until we get all the way to call them a let go right-click on the selected area and we're going to hide those items so that we start off with column. Oh, okay, So now we're gonna do the same thing with our present value calculations, our force. So here is our formula down below for the present value. We have a similar formula for the future value. It will look very similar to what we did with the functions. Notice we're not going to be solving for the present value, but rather using the present value formula to solve for the one unknown. We don't know in the formula, which is actually n, the number of periods. So you could do that algebraically. It would look something like this. The present value would then be 3 thousand. That's going to be our starting point here, equals the future value. We would know that because that's our ending point and we want this to double. Therefore it would be 3 thousand times two or I'm just going to put 6 thousand. That would basically be the numerator because future value could be over. You can also write this as the future value over one plus r to the n. So then I'm gonna divide this by divide by brackets. And I'm going to say one plus the rate, which we said was 18%, which I'm going to put in the format of a decimal 0.18. Close up the brackets. Carrot shift six to the n, and n is the unknown. So we have that one unknown that we can basically solve for it. Now we could do it in that format algebraically, I won't go through the algebra. I'm also going to try to plug this into our Excel worksheet in the format of a table to practice putting something in the format of a table, useful skill. And so that we can practice basically using our algebraic concepts, solving for one unknown by using kind of a forcing mechanism or trial and error using the Goal Seek method. Very useful concept, especially when you can't use algebra because you're using something like functions or a more complex table. So when we construct our table, I'm just going to rebuild this. I'm not going to solve for N, but rather keep it as solving for the present value. And then we'll back into the unknown of n. And that'll give us an idea of what we did with the functions as well when we did the present value functions. So I want to get the numerator and a denominator in the outer column here in our table. Whenever I set up a table like this, what for a kind of a complex function or formula. Then we want any of the inner workings, more complex calculations to be on the inside with sub calculations. So we're going to start off with the future value, which is basically the numerator. I want to bring that over here. I'm gonna say equals and I'm going to use my arrows instead of simply pointed to it with the mouse, using the arrows more. So I'm gonna go left, left, left, down, down, down. That's gonna be the 3 thousand times two times two, because the future value will be where we end at, at 6 thousand that we're going to pull back and see how long it takes or the distance between the two at the rate of discounting at 18%. Okay? Then we're going to pick up the denominator. And I'm going to list this by saying you could just say denominator and colon, but I'm going to list what is in the denominator here. And then we'll put this down under, below. We're going to say this is gonna be one. And then the rate I'll list out rate. And the rate is gonna be that 18%. I'm not going to type it in but say equals left arrow, left arrow, left arrow, up arrow, up arrow, up arrow, and enter. Now I'm gonna go back on that cell. I'm going to format it so we can see what's there were in the Home tab number group, we could add some decimals. There's the 0.18, but I want to see it in the format of a percent. So we're gonna go back on over to the percent percent of it, which isn't a real word, I don t think, but I kinda like it. I recommend using it if it's not in a professional setting. So what we're going to say that, so there we have that and then we're going to say this is gonna be the one plus r. We can sum this up. Remember that one is basically 100% If it were in percent form, the 18% in percent form instead of decimal form, we can still sum the two cells up. You gotta know the sum function if you don't know it. Highly useful function. Notice how I'm typing this in some shift nine, I'm hitting the up arrow, holding down Shift, hitting the up arrow again, doing it all with the keyboard instead of using the mouse enter. And that's highly geeky. To do that more geeky than using the mouse, which is good. That's what we're going for. Geeky, good because it's faster, the keyboards faster. And then we're gonna go to the Home tab, the number group, we can add some decimals. There were at the 1.18, or we can make it a percent hidden the percentage by moving the decimal two places over, making it 118%. So this is gonna be then to the n periods, to the n. Here we are closing up the brackets. And that's what we don't know we're gonna be solving for that. I'd like to put something there and I want to make it come from an outside source, which I'm gonna put down here. This is the thing that we will change with the Goal Seek. I'm just going to put, let's just put ten here. Scrolling back up. This is gonna be equal to the ten. I'm gonna make that yellow to indicate that that's the thing that we're gonna be very keen and changing font group yellow, yellow rised it. And then we're gonna go to the font group and underline. And then, then we have the outer component here, which is going to be brackets one plus r to the carriage shifts six and periods. Periods. And I'm gonna put this in the outer column now. So I'm just gonna be 118% to the power of ten to the tenth, power to the power of ten equals left arrow up, arrow, up arrow. And S6, there's the 1800's six, there's the carrot left arrow up arrow to the ten and S7 and enter there, we have it, but it needs some decimals. I think there's gonna be decimals here. So we're gonna go to the Home tab and we're going to go to the number of groups so we can see more detail. Hit the decimals. Decimal lies in it, which isn't a word again, but kinda like it. And then we're gonna go to the font group and underlining it. And then we're going to say this is gonna be the end result, which is the present value, or P V, P V. And then we can then divide this because we have the numerator and denominator now and we've got those down to one number. And what you do when that happens is you take the one divided by the other. So we're going to say equals Up, up, up, up, up to the 6 thousand divided by up to that desk normalized number that we had and enter. There, we have it. Now this end result, we know what it should be if we started at 6 thousand, it needs to be present valued back down to 3 thousand because we have, we're going to have it here taking it in half because we're talking about the distance between the two to double it. So we're going to take it in half, we're going to cut it in half. And we're gonna do that by changing this cell. We could do it manually down here. I could say, well, what if this was nine? What if this was a, what if this was seven? And what if this was six? And you get the idea. So we want to keep doing that until it's numbers down to 3 thousand here. So let's do that using the Goal Seek. We're going to go up top to the Data tab. We're going to go to the forecast. We're going to say what if, what if this was the case? Let's just imagine a scenario and we're going to ask Excel, would you please set this cell right there? Would you please set that to know to what we know it should be, which is 3 thousand hard-code in 3 thousand, in other words, typing it in by changing this cell, but currently has these six in it to whatever it needs to be to make that what we think it knows, which should be, which is 3 thousand. So we're going to say, Okay, and there it does it for us. So we're at 4.19, which I believe is the same number we got last time when we did the functions. Let's go to the Home tab to the number of group. Let's add some decimals. There's the 4.19. Okay, let's do it again this time with the future value calculations, same kinds of concepts, but with future value related formula. So now we've got the future value equals the present value times one plus r to the n. We can do that algebraically, knowing the future value is the 3 thousand times two or 6 thousand, the end point would be equal to the starting point present value of the 3 thousand times than the one plus the rate 0.18. Closing the brackets to the carriage shift of n, n being the unknown. So we can solve for it algebraically in that format. I'm going to plug this into our table in this format, not solving it for n, but plugging it in there as the future value formula using trial and error and Goalseek to find the answer through basically the brute force kinda computer method to get to what end should be. So let's put this up here. I want the two components in the outer columns and any kind of complexity to be a sub calculation. This is how you'd see tax returns. This is good practice for also financial statements. Also note, let's go back to this last one just to format it a little bit. We had the colon here indicating it's going to be in the inner column and we have a subcategory. Let's indent, which is further indication of the same thing. It's kind of redundant, but it's useful sometimes it's also useful to know if you go to the Home tab Alignment and hit indent, that's easier or better than double-clicking in here and hitting the space bar four times or something like that, which is not as professional. So we're gonna go down here. You could do that again like you can go up top and say, I want to indent twice but not move it into another cell. So you don't have all these little cells next to each other to try to indent things or something like that. Now you use the indent button and that's the way it's done or that's the way I do it. Which obviously, if it's the way I do it, the right way, that's what the right way. That's how you define the right way. The way I do. Now we're gonna do the same thing here. We'll do the future value starts off with the present value calculation. So I'm just entering this formula, I'm going to put that in the outer column, the present values where we start at, which is this 3 thousand. I'm not going to enter 3 thousand as a hard-coded number, but rather hit equals left, left, left, left, down, down, down, down, down for W27 and enter. And then we're going to have the other side of it, which I'm going to label as, as this one over here though. There we have it colon indicating that it's gonna be indented and brought into the inner column. Then I'm going to be picking up the one plus r to the n, So I'm going to say one. And then the rate is going to be r, which is 18%. But I want to pick it up by saying equals left, left, left, up, up, up on the arrows and enter, making that then a decimal or percent by going to the Home tab number group, adding a couple of decimals there it is in decimal format, and we want it in percent format. So we'll hit the percent, moving the decimal two places over, adding the percent sign. Let's go to the Font group while we're here and underline it like so, like so. And then we're going to have this will be the subcategory of one plus the rate. And that's gonna be the sum of these two. Remember that this one basically a 100%. If I was to identify it, it's a 100%. You can add it up whether it be one or 100%, it would still work. We're gonna go ahead and hit the equals SUM. Notice you gotta know the sum function. I'm going to hit Shift Nine to start the brackets up arrow and then hold down Shift so that I can update again, choosing those two cells together, closing the brackets and Enter. Then I'm gonna go back up to the Home tab. We're gonna go to the number group, adds some decimals. We're at the 1.18 and then we can identify which would convert that to 118%. Then we're going to take this to the number of periods. So we're going to say this is going to be going to n periods. Here we odds. And that's gonna be what we don't know. So I'm going to plug that in down here. I'm just going to put ten like we did before. And then I'm going to go up top and say this is going to point to that cell, that ten then is what we're going to change. So I'm going to make this yellow indicating that's the thing that we're gonna be adjusting with brute force, Goal Seek, Excel power, Home tab, font group, yellow, phi it, underline it font group and underline, like so. And then we're going to have the outer column, which is gonna be the one plus the rate, the rate close the bracket shift six n periods. And we'll bring that to the outer column, 118% to the power of ten, which is going to be equal to left arrow up, arrow, up arrow, shift six carrot, left up arrow and Enter. And now let's add some decimals to this. We're going to add multiple decimals so we can see more activity in it. Homed group, home tab number, group, multiple decimals, Decimal analyzing it. And then we're gonna go to the font group and underlying notice that this keeps on going forever. Excel will calculate whatever actual number it is, no matter how many decimals you actually expose. Very important to note, otherwise you'll get confused by rounding. So it makes sure that you keep that mind when you're working with Excel. And then we're going to say, let's multiply this thing out. We're going to multiply this thing out, which is going to be equal to the 3 thousand and then times this one. I don't know why I have future value. You want Let's get this 11 year. Okay, we'll take that out. Okay. So then there we have it. There we have it now. We know what the answer should be. It should be 6 thousand because we want the future value which would be twice, because we're doubling it twice to 3 thousand. So we could change this and say, well, what if I put seven there? What if I put six and so on, or we can ask Excel to do that using the trustee goals seek feature. Let's do that now by going to the Home tab. We're gonna go to no, not the Home tab Data tab, Data tab, Data tab. In case I miss that Forecast group than what if, what if analysis, we're going to seek a goal. So therefore, we're using Goal Seek. And the goal that we seek will be to set this cell right there to be what we know. It should be, which is twice the starting point because we're going to double it, which is 6 thousand. So I'm gonna type in here 6 thousand. And then we want to do that by changing this cell. So we're going to ask Excel, we do it politely. We say please because we're nice to excel and then excels, There's nice back to us normally, when we say Excel, could you change that? So please, whatever it needs to be in order to change this cell because they're connected to what we know. It should be twice the starting 0.3 thousand because we're doubling it up 6 thousand. And we say, OK, and Excel says, Sure, we can do that. Not a problem. It's 4.194.19. So that's the same thing we got last time, I think so it looks right. We're gonna go to the Home tab. Let's add a couple of decimals, number group coupled decimals. Let's do some formatting the clean this thing up. We got our decimals indicating that we brought this into the inner column to sub-categorize it. Let's also indicate that by indented selecting these columns, these cells, and then go into the Home tab, go into the alignment group and increasing the end dentine. And then go into this tab down here and doing it again, Home tab Alignment and indent. And there we have it. So hopefully we can see kind of the relationship between the future value, present value in the formula basis. Learn how to build our tables and format them in a similar way as we might see in a tax return or financial statements. And kinda get an idea of how we would use Goal Seek and how we can apply that to something like functions, as well as more complex tables. 13. Present Value & Future Value Tables: Personal finance practice problem using Excel, present value and future value calculations using tables. Prepare to get financially fit by practicing personal finance. And we are in our Excel worksheet. If you have access to the Excel worksheet, would like to follow along note that we're in the practice tab as opposed to the example tab. The example tab in essence being an answer key, we're all the way over here in column AC where our table is add before we go through the table, however, let's review what we've done thus far in this Excel worksheet. You do not have to have worked the prior problems in order to work this problem. But we want to get an idea of how to do the same kind of calculations and also how to maneuver through the worksheet. This worksheet is currently hidden up through columns a through O. So I'm going to unhide some cells to see what we have done thus far by going on column o, dragging on over to the prior column or to the triangle, letting go, right-click and unhide. So we're going to unhide that I'm going to go all the way to the left so we can get back to column a and see what we have here. We have the information that said the yearly earnings per cent in a savings account, It's gonna be increasing by 18%, which again is quite high at this point in time. But of course, you could have inflation at any point in time and that could be an, a higher item. And also the same thing would be apparent or could occur in other types of investments like a home or land, or stocks or something like that as well. We wanted to see how long it would take at that percentage rate for is for the investment to double, which we did with the rule of 72, divide 72 by 18 given us for the approximate answer, we did a running balance type of calculation which gives us a nice detail of the interest per year and got a similar answer between four and year five for being an approximate with the rule of 72. Then we got a more concrete answer, a 4.19 as we ran the functions in Excel functions, then we got the answer using Excel formulas, including the Excel formulas, just mathematical formulas including the present value formula and the future value formula. Now we're going to do a similar process as we did with these formulas and the table. And the formulas and the mathematical formulas and functions in the tables are kind of a mix between the mathematical formulas and the Excel function. The tables being designed to be a step easier then the mathematical formula, especially at a time before they had a lot of financial calculators and Excel or spreadsheets to work through the problem. They're still useful at points in time when you're gonna be doing test questions and they take away Excel and, or financial calculators. So you will quite often see tables in things like school settings where as in practice, you'll probably be using Excel most often. Now note, like we did last time when we did the formulas, we're not actually solving for the future value or the present value, but rather we're using future value and present value to solve for the unknown, which is n here. So that's what we did last time. Now, in the tables, you might say, well, how would I do that with the tables given the fact that basically the tables are typically used to multiply something by a rate of some kind in order to get the present value or future value. How can I back into the number of periods then using the table? So let's consider that now. Now also remember that there's four tables you want to keep in mind. Whenever you think of these tables, you've got to make sure that you get the right one. There's two present value tables to future value tables. Each of the present value and future value categories have present and future value of one and then an annuity table. So they have an annuity table and a non annuity table. Annuity means a series of payments. Therefore, what the present values, you have, the present value of one table, which is what we're working with. And you have the present value of an annuity table, which is different. You got to keep that distinct in your mind with the future value. We got the future value of one and the future value of an annuity. We will be dealing with the future value of one, not the annuity tables. So here's the present value table where we have the percentage is up top. We've got the numbers, which stands for the periods on the column on the left hand side. Note that in our case, the periods will be in years. The percentages are yearly rates. That doesn't have to be the case though. Remember that when someone tells you a rate, they usually mean per year unless they say otherwise. So even if they say something like the loan, that's a monthly loan has a rate of five per cent. They mean 5% a year even though you're gonna be paying monthly. So when you calculate what the actual payment will be, you've got to make sure that you understand what rate you will be using. Here on the periods we currently have the compounding happening per year, but you could have the compounding happening per month. Or bonds often happen on a semiannual every six month period. So these periods could represent whatever the compounding function is. And then you'd have to apply the rate. To match whatever the period is. So it could be a monthly rate then or six-month rate. Now note that if you are forced to use tables, then book problems will typically be forced them to use more simple calculations that usually are in years and using yearly rates. Because if you use something like a half year or month, you're going to end up with very small percentages and often percentages that are not even. And therefore it's kinda gonna be harder to apply the table to. Notice we have nice even percents and nice whole units over here on the periods. So that's gonna be a restriction when using tables. So the present value table typically would be used by saying if I have something in the future, I'm going to basically look at the percent and the number of periods to get the number to bring it back to the present value. So in this case though, we're looking for the number of periods, we know then that the that the table is 18. Which does it go to? 18? It doesn't even go to 181818 would be out between 1624. We don't have the 800s. Well, let's just approximate it here. What we have is the 16 and the 20, so we don't have the 18. It would be somewhere between 620 and we would be looking for the item because if we're talking about the present value, we would assume we're starting at the future point, which would be 6 thousand. And we're going to basically bring it back in half because we said we're talking about doubling it, but now we're starting at the endpoint and bringing it back. So we're looking at then the point at which this number in the middle would be 0.5, because that would be the number that will take it in half. So if I look at column 16, which is close to our 18%, that looks like it's happening some here, sometime between period, period six where I got 0.57 or period for period five. So that's pretty close. And then well, that's period 15 of periods 16. Yeah. Sometimes between period for in period five. And then if I jumped up to period 20 because our numbers actually in 18, then it's going to happen sometime between period three and period for. The actual answer is gonna be right here in-between period for period five, it was actually 4.19. And that's how you can basically estimate this, this item over here and then come over here and say it's between basically period for period five. Now note that because we're at a partial period, this is a limitation in the table because I can only get an approximation both because in this case they don't have the percentage, which is often the case when you have higher percentages and when you're talking about fractions of a percent. And because there's a fraction of a period over here, which is going to be making it more difficult to find in this format as well. We could do the same thing on the future value tables. So if we were to consider the future value table, we have a similar scenario. Now, note when we're talking about the future value, that means we're gonna be starting at the beginning point, which we said was 3 thousand. And we're trying to see how long it would take to get to doubling it, which would be 26 thousand, how would we get to three to six? That would mean that this number in the middle would have to be too. We're looking for the number two. Once again, we would look at the rate which would be somewhere between 1620. So we're going to be estimating it again. Then we're going to correlate that to wherever the period will be when this number in the middle gets step two, which over here we can see it goes from 1.8106 for period four, and in period five is at the 2.1. So with the 16, it's between period 45. And with the 20th we jump up to 20. It's between period three and period for the correct answer is right around four, right? It was right around four years. So that's our approximate answer here. We actually got the actual answer is 4.19. It's going to be once again an approximation given the fact that we don't have the actual percent here, so we can approximate it and we don't have the actual number, that's gonna be a fraction of a year. So note book problems will often ask you a question like this and make you back into it using the tables in a way that you don't often use the tables, but they'll usually contrive it so it'll work out perfectly for you and you can basically work it out perfectly. They might get a little twist. So it's gonna be in-between a certain year as well. But oftentimes they're going to be highly restricted by the percentages being even and only going up so far and then the numbers in terms of having even periods to be working with.