 Personal finance practice problem using Excel. Disability insurance payment calculation. Prepare 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 a blank sheet. If you do have access, there's three tabs down below. We've got the example tab, the practice tab and the blank tab. The example tab in essence being an answer key. Let's take a look at it now. Information on the left related to disability insurance claim, calculation for it on the right. The second tab is the practice tab which will have a pre-formatted worksheet so you can work the problem with less formatting in Excel. The third tab, basically a blank tab with just the information on the left hand side which if you don't have the Excel sheet, you can add yourself. In that case, I would select the whole sheet first putting down your baseline formatting which would be I would right click and this is what I would use, format the cells, all of them to start off with baseline, the underneath formatting currency. I'd go with the brackets, no dollar sign, no decibels. That's usually where I start. I'm not going to hit okay but just X out of it because we already have this set up here and then add your data on the left to change in the format when necessary. For example, changing the cell to a percent, making a skinny C column and then we're ready to go after we enter the data. We can then change this information on the left hand side to run different scenarios. That's just best practices for working in Excel. So we've got the disability insurance. We're going to say the take home pay is $2,500. Disability insurance coverage replaces percent of wages at 55 percent but there's a waiting period of four weeks. So the weeks of missed work due to illness we're going to say are 14. In that case, what might we get in the workers' compensation? Now the workers' compensation is kind of like a disability insurance of course and it's kind of like a short term typically disability insurance. So we'd like to be able to think possibly how can we work that into our overall basically risk mitigation strategy so we'll get a better idea hopefully on what the calculation looks like here. We want to self-insure to some degree if possible meaning having savings to help us out in the event that our workflow of course goes short for a bit. So we're going to keep that in mind. So as we go here we're going to say this, let's start off with what we'll call this disability benefit payment calculation. And I'm going to say, okay, I made this D cell a little bit larger by clicking in between here making it a little bit bigger here like right about there. And then I'm going to make this top cell black and white these top two cells from D to E, D1 to E1. And we're going to go to the home tab font group. Let's make it black and white. And so then we're going to say that the weeks missed, weeks of missed work. Let's start there because there were 14. Now after we enter this data we can then test out different scenarios. So when you're kind of thinking about workers compensation you might just say, I don't know how to really go about it. You might just start off with the data and then run different scenarios to get a feel for what would happen given different scenarios. We're going to say the waiting period but then there's a waiting period where you get nothing. You get nothing during the four weeks. This is going to be equal to the four weeks of waiting. And then we're going to go to the home tab font group and underline. And then we've got the weeks after waiting period. Let's call it something like that. Then it's going to be equal to the 14 minus four. That's when this thing kicks in after the waiting period. That's 10 in this case. So then the pay per week. So what is the take home pay per week because it's going to be a percentage of our take home pay per week. That happens to be equal to that $2,500. So there is that. And so that would be the pay after waiting period. So the full so the pay after the waiting period waiting period would be equal to the 10 times the 2,500. Let's put an underline here underline. So that then if we that would be the 25,000. And then we're going to say the percent covered percent coverage is 55% because they're only going to give us 55% of our wages instead of a whole thing. Because it's the it's the disability benefit 55. Let's percentify that cell by going to the home tab. Number group percentify you best percentify font group and then we'll underline it. And then we're going to say this is going to be the disability benefit disability benefit payments or amount. Let's just say amount total amount. This would be equal to the 25,000 times the 55. So we'd get the the 13 750 13 750. Let's go ahead and make this blue and bordered. And we'll do it. We'll do the calculation a little bit differently so we can see it a couple different ways. I'm going to go up top and say let's go home tab font group put a border around it. Border blue people border blue if you don't have that blue it's right here. More colors standard and it's that blue right there. That's the one. So there we so there we have that and then we can we can run different scenarios then changing this data up on the left hand side. If we so choose to do and for example you might say well what if what if the weeks were less than the waiting period. Three or something well then then we get a negative number and you probably wouldn't get anything at that point because then be less than the waiting period. But formula wise that's an opportunity for us to practice our if then logical function. So I'm going to say if it goes below zero we want a zero there so I'm going to say let's do this equals the if brackets. Let's take the up arrow three minus the four if that is less than zero then we don't want you to go negative then means comma. That means we're going to go over to the value if then we just want you to put a zero there don't go negative comma. And if it's greater than zero then we want you to do the calculation which is this cell three happens to be there now minus the cell for that's going to give us zero this time. Enter because it's less than zero so there we have it so now no disability so that makes sense let's put this back up to where it was which was 14 I think. So that looks good and so everything else I think works out so my if the pay was higher like 3000 you can start to say OK there's the difference in my calculation if you got this set up nicely. If you're percent that they pay out with something like 60% then of course your calculation would be populating for you and if you had changes to the number of weeks you could say well what if I was out for like 20. You could have a cap there might be some cap depending on you know the rules and regulations on how many weeks you might be out for but if the waiting period was the same and we're still under whatever cap. Then you got the 16 and you can adjust your calculations thusly now you might want to do it a little bit differently let's do the same kind of thing. But try to calculate the amount that we might get like on a per paycheck kind of thing because we might be budgeting paycheck to paycheck so I'm going to say let's make this another skinny I'm going to go from home tab. Let's hit the paintbrush and make a skinny F and I'll do the same thing I'll copy the header here let's copy the header I'm going to bring this out a bit. And let's first think about how much we would get we might get per pay period of this time so let's say let's say my take home pay let's calculate first the percent percent of take home pay that we would get colon. And so that's going to be equal to. Can I make this sell a little smaller because I can I could see my there there it is. Thank you for letting me do that we're going to say this is going to be equal to the the take home pay of the two thousand five hundred this equals the two thousand five hundred. And then let's multiply that times my rate over here which is the percent coverage which is misspelled but I'll fix that later maybe maybe if we're lucky and there's the fifty five so then let's put in let's put a home tab numbers percent to find that let's put an underline under it font group and underline and this would be the percent take home pay I'm just going to copy this and put it there get rid of the colon double click get rid of the colon. I'll put this in the outer column now in the outside this equals I'm going to scroll up to the two thousand five hundred times the fifty five percent so that would be about one thousand three seventy five we took out the pennies you'll you'll bear with and remember on that. And then we might do the calculation for the weeks after waiting period which I'll copy here even though it's spelled wrong again and I'll put a colon. Here and now we'll do this calculation so we'll calculate the weeks of missed work. And I'm just going to pick up this calculation the fourteen and then the waiting period of four so you can see this is a little bit longer but might be a little bit nicer to see the that monthly number up top format of the calculation. And it gets us an opportunity to do our indentations as well. So I'm going to put an underlying font group and underline. Let's do some formatting here. Let's I got a colon here so let's take these three and do some indentations their home tab alignment indent. I'm going to double indent the answer pulled it out to the right hand side home tab alignment double indent. I'm going to make column G a little larger G whiz G wide G widen G widen G whiz you got a G widen G column. So then this is going to be let's copy this put that here get rid of the colon and we'll say this is the total. So let's indent this indent and then indent that one and then we'll say subtraction problem in the outer column. This is going to be equal to the 14 minus the four which is 10 and then we can do the final calculation in the outer column. I'll just say this is equal to this number or that name and the final calculation the final countdown this equals the 1375 times the 10. So there we get to that that 13 750 again which is an indication we did it properly. But now you got this nice breakout on a on a month by month after you clear the the for the four month waiting period which could be useful number to be able to visualize for the budgeting purposes on a month by month basis when might when and when does that money actually come in around around here because I need to like eat. So I'm going to say I can't go anywhere because I got this I'm sick. And so let's make this blue and bordered. So we're going to go to the home tab font group border blue. And so there's that let's put an underline here home tab font group and underline. So that looks good. And then we can still do that that thing here where like like if this number was below for let's check that out again. Like if this was three just so we can practice our our logic function now we're at two. So I'm going to say OK this do a logic function here it shouldn't go below zero. So I'm going to say this equals if brackets if this cell minus this cell is less than zero. You don't go negative comma what do you do in that case comma means what do you do in that case you put a zero there then people. That's what I want you to do Excel. So that's what you do. You don't go negative and then comma what do you do then if it's if it's positive well then you do the calculation that we wanted which is this so minus this cell brackets and enter. So it's zero. But if I then bring it on back up bring it on back to 14 then it's back up to that 13 that 137. So there it is. And like I say once we get that set up we can practice adjusting these these numbers on the left hand side and get an idea of what you know what the workers compensation might kick in when it would kick in. And we could try to plan for that in our own budgeting although ideally no matter what we would kind of like to be able to clear a few months. You know for from our own wage standpoint in terms of our own budgeting for just basically self self insurance. Calculations but in any case let's go ahead and review this let's do some spell check because I know I misspelled a lot of stuff. I know I hear you I hear you come telling me about it. I know it's wrong. I'm going to fix it now. So. Okay. Good enough. So there it is.