 Personal finance practice problem using Excel. Projected health care costs. Prepare to get financially fit by practicing personal finance. Here we are in our Excel worksheet. If you don't have access to it, that's okay because we'll basically build this from a blank sheet. But if you do have access, there's three tabs down below. Example, practice and blank. The example in essence being an answer key. Let's take a look at it now. Information on the left calculations on the right we're basically looking at the current health care costs and trying to project into the future what future costs will be possibly taken into consideration, say inflation. The second tab is gonna be the practice tab and this has some pre-formatted cells. So you could work the practice problem with less Excel formatting. The third tab is the blank tab where we just have the information on the left. If you don't have any of this, you can just open up a blank worksheet and I would first put down the baseline formatting set down the base beat before you put anything on top of that music stream compilation. I don't know when I'm talking, right click on it. I would usually go to the formatting down here, format the cells and I would start out if you have a blank sheet with currency, the brackets and no decimals and no dollar sign. That's my baseline formatting. Typically I'm not gonna hit okay because I already have this set up. I'm just gonna X out of it then put your information on the left hand side changing cells as needed, such as this cell being a percentage and then making a skinny C column. We're ready to go and that's good practice to do because anytime you set up a worksheet, you typically want your data separate from your worksheet pulling from the data making your worksheet adjustable. Okay, so we got the healthcare costs. We're gonna say our 7,000 and what we're gonna use is our future value of money to project what they are gonna be in the future so we can kind of get an idea of our time value of money. We always gotta be working in our time value of money because there's a thing called inflation and so we know that the dollar is gonna get weaker in the future so we could say what if inflation is like 2% right now inflation is looking like it's gonna go above that. So historically as of this recording inflation has been kept quite low to the point where people actually think of it as not really even a factor really, right? But historically before that inflation can get quite significant. So we'll say 2%, that's like what they aim for on the Fed but notice they could go way above that in terms of inflation. That's gonna increase the cost of healthcare and so on for budgeting purposes and whatnot into the future. So how much would be spent in 10 years then? So let's say 10 years into the future what would the healthcare costs be out then? So we could do our future value calculations. I'm gonna first start off in D here. Let's put our, let's call it just future value, future, future value cost. I'm gonna make column D a little bit wider putting my cursor between D and E and opening it up a bit. So we're just gonna do a future value calculation. You could start with equal. I like to start with negative because the result will then be a positive number. It's probably better or more proper to put the negative inside the formula but I think this is the fastest way to do it. So I say negative, future value. There it is. You could double click on the future value here or just put shift nine for the brackets. Here's our argument down below. So we're gonna say let's pick up our interest rate which I'm gonna pull from the data that 2% right there, comma takes me to the next argument which is the number of periods. The number of periods is just gonna be the 10 years. We're just gonna do this in years. We don't have to convert it to months or anything fancy or tricky like that. And then comma and then the payments. Now this is where it gets a little tricky because remember this is not an annuity that we're not putting 7,000 in time after time. So we're skipping that one and we're gonna say comma, comma or you could put a zero there, comma, comma, chameleon and then we're gonna go to the present value and that's gonna then be the 7,000, 7,000. So the future value then, we could close up the brackets but we don't have to, we would close it for us if we just hit enter. There it is, the 8553, so we think 33. So we would think if we're paying 7,000 now there's interest at 2% for 10 years. You would think that the costs in 10 years would be future value costs $8,533 for example. Let's try to map that out in a little bit so we can understand it a bit more. Let's make this one first, let's format this. Don't get ahead of yourself, font group. I hate waiting for myself, I'm so slow. I always wanna get ahead of myself because I just hurry up self, hurry up. And then this one needs to be white, black and white. Let's make this one blue. This is gonna be font group. We're gonna go to the bucket down here and then if you don't have that blue you can go to the more colors down below and then we're gonna be in the standard here and I'm gonna pick up that blue right there. That's the one and okay, doke, font group and let's put a border around it, border blue. Let's do it year by year so we can understand what's happening on a year by year basis just to better understand our formulas. I wanna make a skinny F. To make a skinny F I want it to be the same skininess as skinny C over here. So I'm gonna select skinny C, home tab, clipboard, format, paint it and then just click right on the skinny F with the paint brush, brushing the paint of the skininess on it. So then I'm gonna say this is gonna be years. Let's say there's gonna be an increase due to inflation and the cost and see what happens year over year. We're gonna make this into our header kind of formatting selecting these three cells G to I1, G to I1, home tab. We're gonna go then font group, bucket, black and white on the lettering. Let's center those alignment and center them. Gotta get centered people. If you wanna do this kind of work you gotta be centered. This is gonna be zero, one, two. Let's select those three and we're gonna use our auto fill formatting function by putting our cursor on the fill handle and just dragging it down. You get the little number there. We're taking it down to 10. Taking it down to 10 cause there's 10 periods, 10 years you'll recall. And then we're gonna go up top, home tab, alignment and let's center that up, center it. And I'll make this one a little bit skinnier, skinnerize it, thin it up, thin it up please. And then we're gonna start with the cost of 7,000 in period zero. And then if it increases by 2% because of inflation this is what happens to cost over time when inflation gets out of hand. And notice if we do this properly with our data on the left we can increase the inflation. Say, well what happens if inflation goes crazy to like 8% which it looks like it's on the way or like 14% or like 20% like what's gonna happen? It's gonna be crazy. Seven, I've never seen that before. What kind of world are we living in? But that's not too uncommon historically speaking. So we gotta be okay with that. And then we're gonna pick up the 2% and I could say F4 cause I wanna copy that one down putting a dollar sign between the B and the two and copy it down. And then this one is gonna be it was 7,000 now plus 140. So in year one we would then have the 7,140. Let's do it a couple more times. So I'm just gonna say this equals to 1,740 times 2%. I'm gonna say F4 make an absolute dollar sign before the B and the two and then tab. This now equals to 7,140 plus the 134, 143, sorry dyslexic. And then this one's gonna do it again. So this is gonna be equal to 7,283 times the 2% F4 dollars on B dollars on two tab. So this now equals to 7,283 plus the 146 taking it to 7,428. And then if I select those two because this one's absolutized, we absolutized the item that was outside of the table we're working in. It's in the data set. And then I'm just gonna drag it down with the fill handle taking that fill handle dragging it down. There we go. And we get to that same 8533 that we had up here. Same 8533. And so there it is. Let's go ahead and border blue eyes, border blue. Selecting this stuff. We're gonna go to the font group border and blue poor five or now just to see how this present value stuff works. Just let's now end this time value of money calculation. Let's calculate the present value just so we can get back and say, okay, well if this is the future value now that we calculated after 10 years and that's in 10 years value numbers what if I discount that back to the current day we should get back to the 7,000, right? Just to see how these formulas work. So let's try it. Let's say a present value. I'm gonna say negative instead of equal because I think that's the easiest thing to do. And then we're gonna say present value PV could double click on this or I could say shift nine. And I'm gonna say the rate then is gonna be up here at the 2%, 2% again, comma. And then the number of periods I could pick up this 10, 10 periods and then comma. And then it's not a payment because we're not doing an annuity. So I'm gonna skip that function comma comma or you could put a zero and I'm gonna take the future value 10 years out. We've got the 8,533. If I bring that back to the current day, enter, I get the 7,000 just to see how this kind of works. Let's do one more kind of table on this. I'm gonna go to the font group brackets and you might say, well, maybe I wanna... I could do this with a future value of one kind of calculation. So let's make a table like that. I'm gonna put the F here and make a skinny. I'm gonna go to the paint brush and make a skinny J. And so let's say we have year and then let's say this is the base year amount and then we've got the future value. So now let's say that we're gonna make this our border or header format, home tab font group. We'll make this black and white. Let's put a centering on it. Let's make the years going once again from zero, one, two, selecting those three using our fill handle to drag down to 10 again. And I'm gonna make this a little smaller. I'm gonna center it, alignment centers. And then we're gonna say that the base year is equal to 7,000. I'll keep this, this is at 7,002. And let's say F4 on that one, I'm gonna say F4. So the base year is gonna be 7,000 all the way down. And then we might do our future value calculations now each one so we could do it like a future value of one calculation for each period, for example. So I might say then this equals the future value, let's say negative future value, shift nine. The rate is gonna be the 2%, the 2%. And let's say F4 on that one so that I can copy it down. So it's an absolute reference dollar sign B, dollar sign two comma number of periods. I'm gonna say this is one year out now. So I'm gonna say that's one year out comma. We don't have a payment because it's not an annuity. So another comma, it's gonna be the present value and the present value is gonna be the 7,000 brackets and enter. And so that's another way we can kind of set up this table getting a year by year breakout. Let's do it a couple more times. I'll copy it down then. Negative future value shift nine, the rate is gonna be the 2%. I'd say F4 if I was to copy it down, dollar sign before the B and the two by the way you only need a mixed reference but an absolute one is just easy to do. Oftentimes the number of periods I'm just gonna pick up is the two now. So we're talking this 7,002 years out into the future comma, no payment because it's not an annuity. So another comma, the present value is the base year of the 7,000 again. So that gives us this seven, two, eight, three. Let's do it one more time and then we'll copy it down. Negative future value shift nine, the rate is gonna be the 2% F4 on the keyboard dollar sign before the B and the two comma, number of periods is now the three and then comma, no payment because it's not an annuity. So two commas of present value is once again the 7,000 and then close it up, enter, there we have it. So I can copy this down using the autofill handle and that's another way we can get to basically this stream of numbers. I think this format is kind of nicer because you get to see the increases here but just another way just to see how these kind of future value and present value formulas work, you can see the increases as well by adding another column, which would be a running balance column, which would say, okay, that would be this number minus the prior period, right? And then you get the increase that we can say in crease. So you might see these, it's useful to know different ways to put these together because one, it gets you a better understanding of how the present value, future value calculations work and two, other people are gonna do it differently. They're just gonna see it in their head differently than you. So when you're talking to other people, they might do different stuff. I'm gonna copy that down. So there's basically our interest, let's call this base year. You also may not need the base year because you could use these formulas pulling from this cell over here and so that you could still do this with like three columns, for example. And then let's make this the same format. Let's put the border blue around it, font group, blue and borders. So that looks good. Can make this a little bit more skinny. I'm gonna take two non-adjacent cells. I'm gonna select this column and call them K and make them both a little skinnier. So they're like the same skinny. You know what I'm talking, same skinny. So then of course you can adjust things. So you can say, well, what if we're talking, 5%, it starts to get crazy after 10 years. What if you're talking, what if the interest rate goes up to 9% then obviously this has a significant impact. You could also change things because we have this in the base year of the 8,000, for example. That's how our system is set up for a starting point. We could do our calculations thusly. You can change the years here, but that's a little bit more difficult because clearly our tables on this side are only going down to 10 years, but our future cost here will show the result for that as well. And notice when you talk to other people, they'll probably only do this, but it's really actually kind of nice to see the increase and say, okay, what is happening each year? Yeah, it's going up by $100 to $200 each year or whatever, that gives you a better visual kind of understanding of what's going on.