 Personal finance, Excel, practice, problem, insurance payment calculation, property damage. 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. 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 auto insurance policy. We're imagining some kind of accident happening, us doing the calculation, focusing in on the property damage this time as opposed to the bodily injury damage. The second tab has some pre-formatted cells so you can work through the practice problem with less Excel formatting. The third tab, the blank tab, is where we're going to do that Excel formatting as we go, starting with just the information on the left-hand side. If you don't have this worksheet, you can just build it. I would start off by selecting the whole sheet, right-clicking on everything, and formatting the cells. I usually go to currency to start with. Bracketed numbers, no dollar sign, no decimals. I'm not going to do that here because I already got it set up, but that's where we would start. Then put your information on the left-hand side, which is good practice to do because you want to set your tables up as you're pulling from information on the left. We're going to say that we have the auto insurance with those three funny numbers again. The 80, 160, and the 90. We're going to say that an accident happened, and we're an out-fault accident, so we're out-fault for it. Remember that insurance, when we talk about auto insurance, has multiple factors kind of involved in it because when we think of pure insurance, we usually categorize it in terms of is it liability insurance or property insurance and so on. When we talk about auto insurance, then we're talking about those other kind of categories of insurance that are the hood, so to speak, of the auto insurance because they're all kind of related to the car. So this time we're focusing in on the property side of things. So these three numbers we'll talk about shortly. We're at fault, but no one was hurt, so we don't have any bodily injuries. That's usually the bigger one or the more risk is involved there because clearly if someone has medical issues, that could stack up a lot quickly. And the property damage, less so unless you hit someone with a really expensive car or you hit something that's expensive, which we'll kind of say we hit some expensive stuff here. So we're going to say that we caused property damage. Now we're going to be the one at fault. If the other person was at fault, then clearly hopefully their insurance would take care of it. But we're going to say we didn't even possibly hit a person at this point in time or another car. Maybe we hit a fence and a store or something like that. We ran into a store or something at night or something like that and no actually person was injured. Thank God, but we've got a lot of property damage. So we're going to say person A had property damage of 30,000 and B at the 70,000. So first we got to think about, well, what are these three numbers mean here? The first number we can think about and I would write this out so we could put it in any kind of calculation. The data worksheet we put together is a max per person for injury. So injured, injured, so bodily injury that would be 80,000 per person. So I just took that 80, it's in thousand so you can think about so 80,000. And then we've got the max per accident for injury. So meaning we're going to have to pay someone 80,000 per person that was injured if we were to found at fault in general. But we're going to cap it at 160,000. But that's not really what we're talking about here though. We're talking about property damage because we ran into a fence and through a building and through a storefront at night that no one was in there or something. So we just had property damage, property damage, liability. We thought it was just like a fake store that someone drew on. I don't know what we were thinking. It was a strange night. But in any case, those are the three numbers as we can see with a little blurb down here. The first number represents bodily injury per person. The second number represents bodily injury per occurrence. And the third number represents property damage per occurrence. We're looking at that third number, the 90,000. So I'm going to say, okay, let's put some brackets around this. Obviously the 70 and the 30 add up to more than the 90,000. So it's going to be kind of capped at the 90,000. But let's go ahead and put our little table up top so we can figure out how this is going to work here. So we're going to say, and we can practice our Excel here, property damage. The amount that's going to be paid out of pocket, let's say out of pocket, how much we're going to actually have to pay. In other words, that the insurance company is not going to pay. We'll calculate here. Let's make this a little bit longer. There we go. And so then we're going to say let's make that our header thing up top, which is black and white. So I'm going to go up top and say home tab, font group, make it black and white, black on the bucket, white on the little A there, which stands for all the letters of the alphabet. So this is going to be equal to the A. This is person A who suffered injuries of 30,000. I'm going to copy that down. So I'm just going to say enter. I'm going to select both of these and I could copy it down. So it'll take the relative reference to be just practicing our Excel, putting our cursor on the fill handle, left clicking and dragging it down. So then that's going to be the total. So we've got total property damage equals the sum of those two. I'm going to sum them up. We're going to put a underlying under it font group underlying notice it's a little bit easier than the bodily injury, which had two caps to cap per person involved and then the cap for the total here. We don't really have the cap per person. We just got a total cap of the property of the 90,000. This of course came out to 100,000. And we say that there's a cap. There's a property, property, damage, liability limit, let's say limit. There's a limit of, let's do it here. Let's make this one limit down here. Property damage limit or max, let's say max. Max property damage. Max property damage per act per occurrence. Let's say we're going to say then that's going to be max property damage. This will be equal to the 90,000. And so I'm going to underline that. And of course the 90 is less than the 100,000. So that's the one we're going to pick. So we're going to say this is going to be the amount. This is the insurance payment. Insurance payment. We would expect then to be the lower of the two, the lesser of the two, which we're going to use the 90,000. But we'll do our men function to do this equals the men, the lesser of shift nine, the lesser of those two. And it picks the 90,000 of course. And that means how much we're going to have to pay out of pocket. So amount paid from pocket, the amount that we would have to pay over and above that would be the 100,000 minus the 90,000 or the 10,000. We could put an underline there. If we so choose possibly a double underline down here, double underline there. Let's put some brackets around this, make it border blue, font group brackets. And we're going to make it blue as well as has been our custom. Now, just to play with this a little bit further, you might say, well, what if I can't pay the 10,000? What if someone got an accident and there's property damage of the 100,000, but the insurance only pays 90,000? How much is going to go to A and B? You would think I would assume that it would be looking something like this. You could do a ratio calculation. This would be a common method that you might try to use in a situation like that. You'd say, okay, well, this first person has 30 divided by the total of 100,000. And if I make that a percent number group percent to find it, and the next person had the 70 over the 100,000 making that a percent percent to find it underline here. And then I'm going to say, well, that adds up to, of course, 100%. We make that a percent. And so you would expect then that if you were to get the 100,000 total payout here, I'm sorry, if you only got the 90,000 that was going to be received. So if I say, well, if they only get the max of the 90,000, let's say this 90,000 and 90,000, then you'd say, well, maybe we'll take 30,000 of the 90,000. And then maybe we'll take 70,000 of the 90,000 underline that font group underline, which would come up to the sum of the 90,000. So you might say, well, if they can't get the other 10,000, what would the insurance company do? They'd have to allocate, you know, you would think they'd allocate some rational way, but just to give an idea of how that kind of, how you might do an allocation method like that's very common kind of technique. Let's go up here. We've got a border blue. And now, of course, if you set something up like this, then you can run different scenarios. So you can say, well, what if the A was at 20,000, right? Then it would be at zero. What if it was at 10,000, right? Then we would be under the max, the 10 plus the 70, in other words, coming to the 80,000, which was less than the max of the 90,000. And so therefore, you would have the full payout happening in that instance. And we might actually make this one work a little bit better by saying, in that scenario, we're going to take the lesser of these two numbers. So we might say, we might say this is equal to min of these two numbers, right? Or we could just say it's going to be the insurance payment. Of course, I should be picking up that number so that then it would obviously be paying out the 100% at that case. So that would be a better way to do that. And so let's bring it back up. If we bring it back up to the 30,000, so there we have it at the 10 that would be owed. And then if the insurance, if you weren't paying out that 10, then they might only get the 90,000. The people that got the property damage and the insurance company might figure some kind of ratio calculation to determine what the payout would be, something like 3070 in this case.