 Personal finance practice problem using Excel. Savings from insurance discount calculation. Get ready to get financially fit by practicing personal finance. Here we are in our Excel worksheet. If you don't have access to the Excel worksheet, that's okay because we'll basically build this from scratch. If you do have access, there's three tabs down below example, practice and blank. The example basically being an answer key. Let's take a look at it now. We've got the information on the left hand side. We are imagining that we have insurance coverage and if we have a group coverage, we get a discount and we want to do some calculations using our time value of money to consider the value of the discount. Obviously in every section, we wanna be putting in our time value of money calculations which we will practice here. The second tab, the practice tab will have the pre-formatted worksheets so you can work through the practice problem with less Excel formatting. The third tab, the blank tab will have in essence a blank sheet where we can work on the Excel formatting more. So if you don't have this tab, then you might just start a blank sheet. I would select the whole sheet first, right click on it, format the cells for the baseline underline formatting which for me is typically currency, bracketed, no dollar sign, no decimals and then you change them as you go. I'm just gonna X out of this because I already have the worksheet set up and then I'd put the data on the left hand side which is good practice because you wanna be drawing your information from the data on the left, formatting the cells such as percentages in the format that would be appropriate for that cell, make a skinny C column and then we're ready to go. So we've got A and B considering ensuring their cars with the same company in exchange for a discount. So we're imagining we've got these two people that have different insurance companies and if they insured together, then they could get a discount. So what's gonna be the value of the discount is what we'll try to think about. So they currently pay, we're gonna say 750 and 950 and the discount they're gonna get is 15% if they combine together. So then once we calculate the discount we can think about what the future savings are of it and whenever we think about this time value of money we're gonna say we got the interest rates, what if we would have saved that amount for 10 years? How much would we have after 10 years? But we also wanna think about what would happen if I was to discount it back. For example, in other words, I wanna be on the position of both. I'm gonna argue the best case scenario in terms of I'm trying to argue for basically taking the discount and then kind of the worst case scenario which you might put together if you were in an argument situation where you're saying well the discount might not be worth it so you can see both sides and of course when you're making an even decision, a fair decision, you wanna look at things from multiple different angles whenever we're using a statistical analysis and consider the viewpoint of the person who's given us the statistical analysis to see if they might be giving us kind of one view just like whenever you hear an argument with someone who might have an interest in the outcome especially a financial one. In any case, let's do some calculations here. So we're gonna say the savings, let's first calculate those savings. We're gonna say that the savings per year calculation is the first thing we need to put together. Let's make this a header tab up top, making it black and white. Going to the home tab up top, font group, we're gonna make this black and white for the headers because that's our custom, that's what we do every time. So this is gonna be the current costs what they're currently paying, the current premiums in other words for our two folk here A and B. Can't you come up with a name at least A and B? So lame, can't even like Smith or Jane or John or something. I mean, is it that hard? Whatever, A and B, copy it down. That's person A and person B. And then we're gonna say this equals to 750 enter. I'm gonna copy that down because I could use the relative reference. Notice we're polling as much as we can from the data on the left hand side. That's just the good way to do things. That's how we wanna set things up. We don't wanna hard code the numbers if we don't have to, we want the data set over there. That's why we could adjust things that way. This is the total current costs. It's in the outer column using the trusty sum function here equals to SUM sum it up, sum it up. There it is. Let's do some indentations because we got it like a subcategory type of thing we did with the colon and everything. So let's make an indentation for these three, home tab, alignment indent, indent this one again, home tab, alignment indent, that looks wonderful. Then let's put an underline here that'll make it look even better. Home tab, font group and underline, there we have it. So now we've got the discount. The discount in percentage format is gonna be equal to the 15%. Notice I'm just drawing this from the data. We wanna be drawing from the data. That is not a percent now. Let's make it a percent. Let's percentize that. So percentize, home tab, number, percentification and then font group and underline and that's gonna be the savings, the savings we would have from the discount. So that's gonna be equal to the 1,007 times the 15%. So we're gonna save like every year $255. That doesn't sound like much but what if we saved it for 10 years and got a 7% interest rate on it? Where would we stand after that point? That's where we're going. Yeah, you've seen it before in prior sections. We gotta do one of these kind of like at least one every section because we practice our time value of money. Let's select these items. We're gonna go to the home tab, font group. Let's put up some borders around it, some brackets. And now again, you could think of this from two perspectives. You might say, if I'm trying to argue and make this look as good as possible that we have to take this, we could say, look where we will be if we were to save that much for 10 years and we were able to invest it and get a 7% rate of return. Well, we could do that calculation. I could say, okay, that would be a future value calculation. Value calculation. And let's do our trustee future value. We're gonna notice I got my data down here. We got the interest rate. We're gonna be able to get a 7% a year for 10 years. Okay, so this is gonna be equal to, we'll say the future value. Let's say negative of the future value. That'll flip the sign. And I think it's the easiest thing to do, although possibly not the most proper place to put the negative, but it's easy to do that. So I'm gonna say this equals negative future value. Instead of equals, the rate is going to be that 7%. It's a yearly rate. That's what we're doing. So we don't need to break it down into months or anything, comma, number of periods. We're gonna say 10 years. We're gonna save 10 years. We're gonna do this. And then comma, and we're gonna have another payment because we're gonna save $255 each year. And we're gonna put that into the bank account or into like stocks and bonds maybe and earn 7%. So we're gonna pick that amount up. So it's an annuity, not just one value in other words. So we're gonna say enter. So we would be at $3,532 or $3,523 after 10 years if we were able to do that. But you might also make the argument, and again, that's the optimistic argument. But you might also say, well, yeah, but I mean, there are a couple factors that you can look at from another angle. You might say, well, maybe I'm not gonna put it in the savings account. You know that we're gonna spend the money in any case. And if I get $255 next year and the year after that and the year after that, then it's actually worth less. You might make the argument that I should be discounting it back at the same value, meaning if I got a stream of annuity payments every year for 10 years, it would be worth less than if I got the money today. You're talking about a future value number here. In other words, you might say, let me take the present value of this stream of payments that I'm gonna discount back at the 7%. Just to do it that way, I'd say, okay, this would be negative present value shift. That would be the rate, which would be the 7%, comma. The number of periods is gonna be 10 and then comma. And then we're gonna save the payment now because it's still in annuity payment that we're gonna get 255. And notice we'd probably get it a monthly, maybe, unless we pay on a yearly basis, but we're gonna estimate yearly. So you might say, really, it would be saving more like 1,791. And you might, of course, just do the most straightforward calculation if I pull these down and I say, what if I save that for 10 years? Saving over, say, over years of 10, we would have savings for 10 years would be equal to the 255 times 10. That would be with no time value of money calculation. So let's put an underline there. Let's put some brackets in blue. So you could say, okay, that would be what I would save over 10 years. But on the optimistic side, you'd be like, well, I wouldn't be at 2,055. We would be investing that and we'd get a 7% return. So we'd have a future value of the 3,523, but that's a future value. And we're currently in the present. And you might say, well, yeah, really, what's happening is I'm gonna spend that money and I should be discounting it back at the discount rate, let's say. And that would bring us back to the 1,791. Or you might say, well, really, I think I'm gonna spend the money and I'm not gonna get the 7% return. And I think inflation is 3.5%. So you're gonna say, well, if inflation is 3.5%, then I can discount it back using the present value using the inflation rate. And you also might argue that obviously if inflation happens, you're not gonna have the same, the insurance company might up their premiums from year to year. It's not like we have a contract for 10 years and they're not gonna increase their insurance prices because with inflation, you would think they would increase their insurance prices and so on. But let's say they kept it static and then we have these savings over the 10 years and let's discount it with the insurance, with the inflation calculation. So we're gonna say this is present value for inflation. So you're gonna say, okay, this is negative present value, shift nine. The rate is now gonna be the 3.5 because we think the inflation rate's gonna be 3.5, let's say, comma, the number of periods is going to be 10, comma. And we're gonna take the savings, which was the 255 per year. So we'd come up to like 2,121. So it kinda depends where you're coming from with the argument. So just note when someone talks about future value, you're talking about future value terms and they're making some assumptions in what you're gonna do. And then of course, you're talking about money that's in future terms instead of the present term. Then you might take the annuity and say, well, I'm gonna discount it back to present value. You might try to remove the time value of money calculation in your argument. But the point is you wanna look at these kind of things with different, from different angles so that you get a fuller picture of what's going on for your decision-making process. And this same concept applies when you go to more complex decisions and people are arguing one particular thing or another, just like when they argue with words, right? They're gonna give you an argument that only has one side to something. Doesn't mean they're lying, per se, although they may be, but it does mean they're lying. Maybe they got a legit statistic there or a legit argument, but it's only one side that happens to be the most favorable side. So we see that, the same thing happens with numbers. So in any case, let's try to then say, let's map this one out, let's map this first one out. I'm gonna make this a skinny G, let's copy the skinny C. I'm gonna copy the skinny C format and then put that over here. And let's do a mapping out of this information. So I'm gonna say years, let's say the growth and the savings and then the value is gonna be here. So we can map out this kind of annuity that we got to. We're gonna get to this number and then we'll actually discount it back to this number just to see how these present value calculations and future values, time value, money calculations fit together. So I'm gonna select these three. Let's do our centering, alignment and center. Let's make it black and white. We're gonna go font group. Let's make it black and white. Let's make the year a little bit skinnier. We're gonna skinnerize it. So we're skinnerizing it. You can fatten the cell and skinnerize the cells. So this is gonna be your one, two, three and let's copy those three or just select them. Use our fill handle to drag that series on down to 10. 10, 10. Then we're gonna then, I'm gonna go to the alignment and center these ones. And I'm gonna say the first savings happened in year one and with a normal annuity, we started like the end of the year so we don't have any earnings on it in year one. It's just gonna be the savings of the 255. So I'm gonna say that's the value then at the end. Then we're gonna say growth happened on that 255 of 7%. And this is, we're gonna get up to that 357 here. So we're gonna say, all right, we got that 255 times the 7% and enter. So there's the 18, the savings, we got the same savings which was that 255. I'm gonna do some absolute references shortly and copy it down but let's just do a couple of these to get a feel for it. So there it is and we're gonna say this is equal to the prior value plus the growth plus the savings. These are rounded numbers so they could be some pennies involved here. Now we've got the growth again, which is on this number which we're gonna earn 7% on. So we're gonna say the 528 times the 7%. There it is, we put in another 255. So now we have equals the prior value plus the growth plus the savings. And let's do it one more time and then we'll do absolute references and copy it down. Now we've got the 820 times the 7%. There's the growth tab, the savings. We're gonna put another, we saved another 255 and this equals the prior balance. Let's do it this way this time plus the sum of these two items, the growth and the savings close up the brackets. There it is. Okay, let's see, I'm gonna delete these two now. I'm gonna try to make this as fast as possible. I'm gonna try to do some absolute references. So if I double click on this one, this one is in my table so I don't need an absolute reference but this one is in like our data set or it's outside of the table that I'm in and I want it to be the same when I copy it down. So that's gonna be in, that's the one that I drew in here which is in B12, actually B12 right there. B12, we're gonna say F4, B12. You only need a mixed reference but an absolute works dollar sign before the B and the 12. So this one is coming from something outside of the current table I'm in and therefore I wanna make it an absolute reference F4. So dollar sign before the F and seven. So when I copy it down, it'll stay the same. You only need a mixed reference but an absolute works. This one doesn't need anything happening to it even though it's complex looking because these three cells are inside the table. Gonna select these three tabs and I could just double click the fill handle which I can then call a fill button. I just call it a fill button at that point. There it is, there's the three, five, two, three. I can select these, let's make that blue and bordered, blue and bordered. So now you can see how this kind of growth happens to see how we got to that number. And just to tie this out, let's say, well this is a future value now, just to see how it kind of ties out with the present value. What if I was to take that future value which is 10 years from now, that's how much we would have in 10 years and I was to discount it back with the present value of one calculation using that same 7%, right? So I could say, okay, well what if I took, what if I took the present value of that number? So I'm gonna say this is gonna be negative present value to get it back to today's dollars. I'm gonna say the rate, I'm gonna use that same rate, the 7%, comma, number of periods is going to be let's say 10 right there to get it back to today and then comma. And then I'm not gonna have a payment here because it's not an annuity. I'm just gonna present value of one, so two commas so you could put a zero there. And then the future value, which is that's where we would be in 10 years is that number, close up the brackets, that brings us back to the 1,791, which we calculated when we did this present value calculation and the annuity calculation. Just so you can kind of see how those numbers tie together. So let's put some blue and border around this one. I'm gonna font, group, blue and border it. And for some reason I put it down here in row four. So I'm gonna actually delete these cells above it to move it up. So I did that on purpose so we could do this, right? That's why I did it. So I'm gonna go from H1 to K3. I'm gonna right click on those cells and delete them and I wanna make sure that I move them up so it'll move everything up. So I'm gonna move them up and so there that is. So that looks good. So now, again, if you were to make this argument, if you had like a, you know, if you're trying to look at it objectively or if you're trying to talk to someone who's trying to convince you one way or the other or what should be done with time value of money, they might say, you know, the most straightforward calculation would be, well, if you saved 255 for 10 years, then you'd be saving 2,550. That's pretty good. Or they might take it a step further and say, well, what if you put that 2,055 into a savings account and you earn 7% or more or so on and notice I can adjust this on the left-hand side, I could say, well, what if it was 8%? What if it was 9%? That's why we have this on data on the left-hand side. If you did that back to seven, we would be at the 3,523. And then if you were on the pessimistic side of things, you might be saying, hey, look, you're not even going to be saving that. You're not going to put it into the stocks and bonds and earning 7%, you're probably going to spend it. So we should be discounting with the 7% maybe. So not only would we not have the 255, 2,550, because that's future value of money, we would actually have, if I discount it back at 7%, 1,791 or you could say, well, at least I'll discount it back at inflation rates or something like that, 3.5%, which would be the 2,121. And then let's do one more calculation just to practice our time value of money to break down this annuity into a series of present value of one calculations. So I'm going to take this skinny column right here, home tabs, taking that skinny, I'm going to put that over here and I'm going to say this is going to be the year and this is going to be the savings and this is going to be the present value on the savings per year. Let's make this black and white, home tab font group, making that black and white. Let's center it, do some centerization, making this one a little bit smaller and then we'll do the same thing here, one, two, three. I'm going to select those three, put my cursor on the fill handle and drag it on down to 10. And now I'm just going to put my savings, which I didn't spell right saving, INGS and I'm going to say that that's going to be equal to the 255. I'll just copy that down. That's going to be the same. But then I'm going to do my present value, or let's say this is, I'm sorry, this is going to be the future value calculations here. So this 255 that I had at the end we're saying of year one is going to be there for nine years after that. So I'm going to do a future value of one calculation, sum them all up and I should get to the same number as we got here just to see this calculation another way to see the annuities versus the values of one. So this is going to be negative future value. Let's say brackets, the rate is going to be over here. We have that 7% still on the rate comma. And then I'm going to say that this is going to be the number of periods. Now this is where it's a little tricky because I'm going to have this for nine years because this happened at the end of year one. We're going to say I don't want to hard code nine because I want to copy it down. So the tricky little thing we're going to do is we're going to take that 10 down here minus the this number, which will give us nine. But I'm going to make this first one absolute because when I copy it down I don't want that 10 to move but I do want that one to move. So I'm going to say F4 on the keyboard making that absolute and then comma and then the payment amount is not what we're going to use because this is not an annuity but a present value. I mean future value of one. So comma we're going to take the present value which is that 255 and enter. So there we have it. And so then I can do that again down here. I'll do it one more time and then I'll adjust this one to copy it down. Negative future value shift nine rate is all the way to the left is going to be that 7% comma. And then we're going to say that we have the number of periods which once again is now this is at the end of period two which would be eight, 10 minus two but I'm going to do it this way which is going to be this and then I would say F4. So that one doesn't move when I copy it down minus two which would be eight and then comma no payment because we're not talking about an annuity double comma or you could put a zero and a comma and the present value is 255 and enter. So there we have the next one and we're going to do that series down. I'm going to delete the second one now and then we'll double click on this first one and I'm going to adjust it so we can copy it down. This first B12 is that interest which is over here somewhere. It's outside of the table we're working in therefore I don't want it to move. So I'm going to select F4 making a dollar sign before the B and the 12. This represents that 10 which I don't want to move when I copy it down. This represents that one which I do want to move therefore it's not absolute. Note that you only need mixed references but absolutes work and this one represents that 255 and I do want it to move down no absolutes for it therefore. Putting my cursor on this number, double click in the fill handle which I call the fill button because it's just working like a button right there and this is going to be the total and we'll sum this up. So we can now sum up the savings. So we can see the savings calculation again with no time value calculation which obviously equals that number and then I can copy that over using the fill handle and dragging that to the right and so there's our calculation of the 3523 with a series of future value of one which is going to be the same as the annuity calculation we got over here just to practice our time value of money concept. So we're going to go up top, home tab, font group. Let's put some brackets around it. Let's make it blue. Let's put our cursor off that. Let's do some spelling. Did you spell stuff right, years? You can't even spell years, idiot. I just wanted to give spell check something to do. So we've got that, okay. So there it is.