 Personal Finance, Excel, Practice Problem, Cash Value Insurance Payment, 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 from a blank sheet. If you do have access, we got three tabs down below an example tab, a practice tab and a blank tab, the example tab in essence being an answer key. Let's take a look at it now. We've got the information on the left-hand side calculating on the right-hand side. We're looking at the cash value calculation for an insurance payment. The second tab, the blue tab, will have some pre-formatted sales. So if you don't want to spend as much time formatting the sales, you can work through the problem with the pre-formatted sales. The third tab, the blank tab, will have more of a blank sheet where we're going to build everything that we're going to do the calculations on. If you don't have access, then you can just add this information on the left-hand side and move from there. Note that I would add the information on the left-hand side because that's good practice to kind of set up your Excel worksheets. So you're pulling data from a source data and that helps you to change things up and make different projections. So if you don't have this sheet, you might start off by formatting the whole sheet. I would select the triangle, right-click, format the entire worksheet. I usually use currency, brackets, and then remove the dollar sign and zero decibels. I'm not going to do it now because I have the sheet. And then close this, then change the formatting as needed as you enter this data. Nothing really needs to be changed here. And then we can move forward with the calculations. So we're going to imagine here, we got a cash value insurance coverage. So we're going to have, we got insurance coverage for a cash value and furniture is destroyed in a fire. So it was insured with a cash value insurance policy. So the year old of actual furniture, meaning we're going to assume the furniture that was, I got to interpret the sayings I have over here. Can't you write it better than that? Well, I'm interpreting it for you. It was five years old. This furniture that was destroyed, the cost to replace it is the 4,500. The estimated life is eight years. The original cost of the furniture we're going to say is $4,000. So the terms that we have to understand here, if we're trying to think about how much the payout will be, is are they going to be paying us the actual cash value or are they going to be paying us the replacement costs? Because if I have furniture and I purchased it five years ago, I paid $4,000 for it. But if I want to replace the furniture, now it costs me $4,500. So to get me back to the same point, I'd have to buy the newer furniture, which would cost more than it did when I bought it originally. Also, the $4,000 furniture that we purchased, if it's five years old, you would expect, like most things, it would deteriorate in value. So is the insurance company going to give me the replacement cost to get me the new furniture to get up to date, or are they going to try to give me the cost or the value of the actual furniture? We would like the replacement cost, so they give us the 4,500. But if I have that kind of policy, it probably is going to cost more on the premium side of things. They're going to tell us here that they're giving us the replacement cost. So they're going to give us some amount of money that they think is the value of the furniture, and then I'm going to have to put in the difference if I want to get myself back to the same point to buy the new furniture at the 4,500. So we got to understand that difference between actual cash value and replacement costs. We're talking replacement costs here. So let's build a table. I'm going to make this a little bit wider by putting my cursor in between here and then widening this out, holding down left. I'm going to type in here. This is going to be the insurance, let's say, payment. And I'm going to say brackets actual cash value and brackets. Now, I'm not going to make this cell as white as that because I'm not going to put that actual cash value on the bottom. So I'm going to make it maybe like that wide right there. And then I'm going to make this black and white for our header. Let's make it wide enough so two cells cover that right there. And then I'm going to make this black and white up top. And that's what I do for the header. So I'm in the home tab font group, making it black and white, header format. That's the header format. This is the head of stuff. Header format is black and white. And then I'm going to say we're going to start with the cost to replace it. So I'm basically doing a depreciation calculation. Note that the depreciation calculation is a little bit different than you might see if you're doing this from an accounting standpoint where we would start with the original cost allocating the cost over the useful life. Here, we're going to try to start from the replacement cost and then depreciate it down to for the five years that have passed. Little bit different. Maybe I'll do a comparison just so you can see the difference. So what I'm going to do here is I'm going to start with the replacement cost and say there's the 4,500. Notice I'm saying equals. And I'm pointing to the source data because that's how you want to set up your worksheets generally. And then I'm going to say the estimated life. I'm just going to do a straight year straight line depreciation from there. This is if you know depreciation, this is going to be where we're at now. This is going to be eight years. I'm going to put an underlined here. I'm going to say font group underline it. And this is going to be I'm going to call this the depreciation per year or the decline in value per year, whatever you want to call it. This is going to be equal to the 4,500 divided by eight. So that means we're going to assume that if it declined in value evenly over what we assume the life to be eight years, it would decline in value 563 per year. And we have this piece of property for five years. So that means that I'm going to say this is how many years old it is. Years old of actual furniture. The years old, it was five years old. So if it was five years old, then we have the depreciation. Total depreciation or the accumulated depreciation, if you want to use that kind of terminology, would be the 563 about. This could be rounded times five. So we've got the 2008-13 that has been depreciated. So the replacement cost 4,500 minus the 2,813. So this is going to be then the insurance payment that we would expect based on this calculation, the 4,500 minus the 2,813, which is a little depressing because we're like, I need to buy a new couch. And it costs 4,500. And you're giving me 1,688. My couch burnt down. What am I supposed to replace it with? Like a bench? You want me to buy a bench or something? No, we're going to have to, because the couch was old. You could buy a used couch, but that's gross. I don't want to use. So that's going to be the difference between the replacement cost and the end. So there we go. I'm going to go up to here. Let's put an underline. Let's make this blue and bordered, as is our custom. Font group. Let's put some borders around it. Let's make it blue. Now we can do this calculation in another way. Let's just do it in another way. So I'm going to make a skinny F. So we can do a calculation next to it. So I'm going to take skinny C here. It's C out skinny. I'm going to go to the home tab, clipboard, format, paint it, and then make a skinny F. And then I'm going to hide from C to F. So I got some room, putting my cursor on C, rolling on over to E. C E, it's right there. See it? C E, right click. I'm going to right click and hide that stuff. So now let's do the same thing in another calculation. So the same kind of calculation, but I'll do it differently. Insurance, payment, payment. And so actual cash value brackets. I'm going to widen the cell just like we did before to like right there. So it covers it with two cells. So it's like right there. So it's not hanging over two cells. Make that black and white like we did before. Home tab and home tab, font, black and white. Black and white. OK, so then we're going to start with the replacement cost again, just like we did before. This is where the difference is between accounting, because in accounting we'd be using the OG cost, the original cost, in other words, which you say OG is the cool way you say original. So then, or at least it used to be like 20 years ago. I don't know what people say now. Anyways, whatever. So we're going to say the ratio is going to be, I'm going to call it the years remaining. And if it was an eight year life and five had been expired, I'm just going to do a bit of subtraction. We're going to say this is going to be eight minus the five. That's going to give us our three years, three years here. And I'm going to compare that then to the useful life, estimated life, which is eight. There's the ratio. I'm going to move this 4,500 to the outside, because I'm going to make this like a sub-calculation so I'm going to say control X on this one. And I'm just going to move it, control V right there, move it on over, move it on over. And then we're going to say ratio outside. Let's put this in the outside. This is going to be our division. This is the three divided by the eight, three over eight. Let's make that a percent so we can see some numbers here, home tab, number group, percentifying it, not a word, but I'm making it, I'm making it a word. And then decimalizing it as well so we can see some stuff. And then let's go and then make this an indentation. So I'm going to select these three and go up top, do some indentation, because we've got a sub-category, home tab, alignment, indent, and then double indent right there, up, double done, double indent done. And then let's put an underline here, font group underline. And this is going to be then the insurance. And I'm not going to put that actual cash value. Calculate this one 4,500 times the 37.5%. And we get another calculation in a similar way, a little bit shorter of a calculation style underline. Let's put some stuff here and let's make it blue and bordered, blue and bordered, font group, blue, and I'm, blue and, yeah, that was the border. That's not blue, that was the border, border blue. Now let's compare that to what we would do under a system where we're doing accounting style system. So I'm going to select from B to G, bug, and I'm going to right click and unhide those cells. And let's do the same thing and calculate accounting depreciation, or let's say accounting, let's say book value calculation. So meaning what would be the value of it from an accounting term. And if I use straight line depreciation, which would be a similar calculation. So I'm going to make this black and white. Let's go up top and say, let's make this black and white up top. So in this case, the only difference is I'm going to do the whole thing, but I'm going to start with the original cost. Because on accounting, we take the original cost and we allocate it over the useful life so that we get the expense of the original cost over when we used it. So that's where the difference is going to lie. So it would be this, and then I could copy the rest of it. I'm just going to say this equals the stuff above, and I'm just going to copy that down to do this quickly like. And so there it is. And then I'm going to say I could copy this above. So the estimated useful life is still eight. And then I'm not going to copy the total, obviously. We're going to do the actual calculation down here. Let's underline it. And so this would be equal to the 4,000 divided by eight. So that's a bit different, 500 depreciation per year, as opposed to the 563, because we bought it for 4,000, not the replacement cost. And then the years old of the furniture, that's going to be five, just like it was before, because we had that couch or whatever it is for five years, and it's getting kind of old. We were pretty, we weren't nice to our couch. You know, it's kind of, it's showing its age there. It's not the years, it's the mileage. That's what Indiana Jones said. Okay, so this is going to be the 500 times five. So there's the 2,500 depreciation. So we would have 4,000 original cost minus the 2,500. We got the 1,005. Let's put an underline here under the accounting method as opposed to up top. You could kind of simplify that if it was straight line to something like this, which might be a little bit shorter. It's actually, you know, somewhat same calculation, but you could do this ratio kind of in one step really. So I could say this is going to be equaling this up. Well, this is going to be equaling this. And let's make that black and white here. And then I'll just copy it down. We'll say border, black and white. I mean, you're saying the wrong stuff. You're saying stuff and you're doing different stuff. I know, do what I do, not what I say, replacement cost. No, this is going to be the original cost. That's the difference. That's the difference, 4,000. And then we've got a ratio. This is where it's going to be the same. So I'll just copy the thing above it and I'll just copy it down. Let's just take, grab the fill handle, grab that and drag it down. And let's do some estimations here or indentations. Home tab, alignment, indent. You could also copy the formatting. Like I might just say, why don't I just copy the formatting up top. I'll just take this whole thing, home tab, clipboard, paint brush it and just put the whole formatting right there. Then you get the indentations all at the same time. Why are you doing it the long way? Because it's practice. Because I was trying to get people to practice. So we're going to say the years remaining are three and that calculation was, let's just do that again so we could see it. If the total years are eight and five have passed, we got three. That's where the three comes from. Useful life was eight. We'll put the underline. I don't have to underline it because I copied the formatting three over eight. There's our 37.5%. If we take that 4,000 times that percent, we get to the 1,500 again, as opposed to the 1,688 given the fact that we have the different original number, original cost versus the replacement cost. Let's copy the formatting on this one this time and format paint brush it and paint that blue paint right there. Boom, get that nice blue. Let's put some double underlines under this one. That'll make it look nicer. Double underlines font group, font underline double. Check the spelling, review it. Does the spelling look okay, original? That's in the source data is not even right for crying out loud, actual, actual, actual. Okay, all right. I see what you're saying on that spelling. I won't argue with it. So there we go.