 Personal finance practice problem using Excel Co-insurance provision 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. 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 have the information on the left-hand side, calculations in the blue area on the right-hand side. We're gonna have a calculation of an insurance that's being paid out, but we've gotta deal with the Co-insurance provision. And then the second tab, the practice tab, will have some pre-formatted cells on the right-hand side so you can go through the practice problem without doing so much formatting in the Excel worksheet. The third tab, basically a blank tab with the information as our starting point. If you don't have this worksheet, you could start with a new Excel worksheet, which I would first select the entire worksheet, format the cells that you would generally like them to be in, which I usually go right-click, format cells, and I make them currency, brackets, no dollar sign, and no decimals to start out with, and then I change them as necessary. I'm not gonna do that here because I've already set this up, and then you might wanna put your starting information, which I recommend doing because we want to be drawing from a source data because that's just good practice and redo any cells, such as making this cell a percentage, it will be at the same point. We do have another item down here, but that's not necessary for you to work along with the practice problem. So now we've got the home value at the 300,000, the home insured for or coverage amount at the 230,000, which of course is less than the home value. We got the Co-insurance provision of the 80%, and then we're gonna imagine that there's a claim of 15,000. First, let's take a look at the definition of Co-insurance, that's what this little blurb is down here. Co-insurance, a property insurance provision that penalizes the insured's loss recovery if the limit of insurance purchased by the insured is not equal to or greater than a specified percent, commonly 80% of the value of the insured property. So if we have to insure this property of 300,000 at 80% for the Co-insurance provision, then that's gonna be our first kind of thought and that could have an impact on the claim that we're having here. So the Co-insurance provision specifies that the insured will recover no more than the following. The amount of the loss multiplied by the ratio of the amount of insurance purchased, the limit of insurance to the amount of insurance required, the value of the property on the date of loss multiplied by the Co-insurance percentage less the deductible. So we don't have any deductible that we'll be dealing with. Obviously the deductible works like a deductible does. So the amount of loss that is not payable to the insured as a result of failure to comply with the Co-insurance provision as commonly referred to as a Co-insurance penalty, a commercial property. So let's leave it at that. So that's our calculation. First we're gonna think about this calculation to see what percent that we have of the home that is insured to see if it's less than the 80%. So in other words, let's say this is gonna be the percent insured or covered that we have here. And I'll select these and let's make this our header format which is black and white. I'm gonna put that down here. So insured or covered, let's say. And this is gonna be, let's say this is going to be the amount that we have covered. The coverage amount is 230,000. And then the home value that we're saying is then up top at the 300,000, the 300,000. Let's take that percent and call that the percent insured or covered. And that'll equal a good old division problem equals up twice to 230,000 divided by up once to 300,000. I'm gonna make that sell a percent so we can see what it is, hometown number percentified. We can add some decimals. So it's under 80%. So we can see it's under 80%. So we've got this coinsurance provision kicking in. So if I have a claim of the 15,000 that they're gonna pay us, we've got this coinsurance thing that's messing things up a bit. So let's put an underline here. We're gonna go to the home tab, font group underline. Let's make this blue and border, the border blue, home tab, font group, borders, and then the blue, border blue. So then we're gonna say, okay, let's calculate the required coverage up top. So the required, required coverage for the coinsurance policy then, I'm gonna make this a little bit wider, meaning we're supposed to be covering 80% of the 300,000 to not have this coinsurance thing mess up our claim amount. So I'm gonna go up top and say, this is gonna be home, font group, and let's make this black and white. So let's say that we had the home value of the 300,000. And then we had the 80% that's required to be coverage. So it's an 80% coinsurance provision. Let's make that a percent, put in our cursor there. Let's percentify that. And then we're gonna go to the font group and underline it. That means that the required coverage is gonna be equal to the 300,000 times the 80% or 240,000. So we only have 300, 230,000. So we're under the 80% required coverage. And so that means when we have this claim that's gonna be out here, it's gonna be reduced by that amount. Let's do that calculation, not by that amount, but it's gonna be reduced in part because we don't have insurance up to the 80%. So let's make that border blue, font group, border and blue as has been our custom. So then the claim amount, the claim payment is gonna be reduced because of this issue. So let's make this, I'm gonna make this black and white out to here for our header, home tab, font group, making that black and white, black and white. And so then we're gonna say that the claim amount was for 15,000. So we got this 15,000 that we have a claim for, but because we have this coinsurance provision and we have less than 80% covered, we're gonna do our ratio calculation, which is gonna be the comparison of the home insurance amount, how much we have it insured up to, which is equal to the 230,000. So this equals to 230,000. And we're gonna compare that then to the required coverage, which we said was 80% of the value of the home. So we wanted to have the required coverage, they're saying should have been the 240,000. Let's put an underline there, font group and underline. So there's gonna be our ratio calculation, ratio, putting that into the outer column now. Gonna do just a division problem, that's our ratio. We got the 230,000 on the top divided by or over the 240,000 enter. Gonna have to percentify that so we can see some action happen in home tab. Numbers percentify, let's add a couple of decimals. So it's about 95.83%. Let's put an underline on that, underline on it too. Let's do some indentation here because we got like a sub calculation. That's why I did this whole thing over here. So we got a sub calculation. Let's highlight these three, go to the home group, alignment indent. And then with the ratio, I'm gonna indent that again, alignment indent double time, double time indentation. And so then we're gonna scroll down and this is gonna be the claim payment. Then we'll say the calculation will be equal to the 15 times the 95.83%. So we had the reduction of the claim due to the coinsurance provision and because we didn't have it up to 80% of the value. So we've got it reduced to the 14.375. Let's put brackets, let's put some border blue on that, put some good old border blue on it. So there we have it. Now one last thing you might do to try to kind of automate this whole worksheet. So in other words, if I wanted to change the data up top, you might say, well, what if I want this number right here to show, to calculate automatically, but if I'm over the 80%, then I wouldn't have this calculation. In other words, if I was to have insured 245,000, then I would be over the 80% at the 240 and notice this calculation is still calculating it and now it's acting like I'm gonna get a claim greater than the 15,000, which isn't the case. So we could use like an if then or logic kind of formula down here so that we can make some adjustments if we wanted to. So I'm gonna undo that and say, in other words, this cell, if it's greater than one, I want it to just be one. And if it's less than one, that's when I wanna have it be this. So let's do an if function. So it's gonna be equals if brackets and the logic test is going to be, we're gonna say if this cell divided by this cell, same thing, if that divided by that, is let's say greater than one. If it's greater than one, then comma, what do I want it to do? Well, if it's greater than one, I want it to just put the one there. So I just wanna keep it at the 15,000. In other words, we'll multiply it times one or 100%. So I'll put one. If not, then what do I want it to do? I want it to do that actual calculation. I want it to take this divided by this. And then I'll close up the brackets. And so there we have it. So now it's there. And if this was over, so if I had my insurance, my home at the 248,000 or something, so now it's gonna stop at the 100%. So now you can see this is why it's useful to have your data on the left-hand side, because of course now you can play with these numbers over here and work different calculations with it and get a result from it, or at least check your result and try to automate your worksheets as much as possible. So let's bring it down to the 230. Obviously, if it was less than that, 200,000, you could start to kind of automate your sheets here and see how much of a penalty that you could have in those instances, 230,000. Here we go.