|
adding income video transcript:
Okay. In this video, we're going to go over how to add income as the first step in getting started with your forecasting spreadsheet. If you've watched the overview video you saw that you could add your age and you put in, what are your working years at the w your retirement years with an R one of the things I wanted to pull out.
Uh, that's a very important as you can choose any starting year. So 2018 is almost over. You can put in a 2019 there, and you'll just want to correspond your current age. That you'll be that year. So when you come down to the bottom of your spreadsheet, income has already added, uh, I've typed this in earlier, but you type in the name of your employer.
So I'll put in employer. And if you have more than one employer, you hit add income up here and then you would type employer two or whatever. The name of that employer is and gross pay and take home pay is used in various calculations throughout the spreadsheet. So up here is cumulative gross pay and cumulative take-home pay.
And what it's doing is it's calculate. All of the gross pays. So if you added three or four incomes, it's going to add all those gross paste together and all those take-home home together so that you have a total gross pay and total take home pay, no matter how many employers you enter for this demonstration, I'm just going to have one.
So I'm going to type, delete here and hit delete, and that will remove. To enter into a employer. One, the income I have this paycheck breakdown and what this does is as you enter a dollar amounts from your paycheck, oh, multiplied by the number of paychecks you have a year. So if you get paid every two weeks, you put in 26, if you get paid twice a month, you put in 24.
So let's go ahead and fill this in so that we can get. With, uh, adding income. So looking at a paycheck, put in your gross pay. So you may or may not be familiar with the health savings account. You can type whatever items are on your paycheck into here. Obviously no one, no two paychecks are going to be the same.
I don't want to get into too much detail on a specific what are, what is on a specific paycheck? You have pre-tax pre-tax items. So other pre-tax deductions health insurance, dental, and then, uh, we're going to go into taxes. So if you're not familiar, how to write a formula in Excel and you have like four or five items for taxes, federal local state unemployment, if you just type a minus put in each amount, separated by a month.
And what you'll see is that we'll add it up for you. So get all those amounts in there and just put in some post-tax items, which would be expense, reimbursements, life insurance, vision coverage, some of those items. Um, but the whole point of this is to get to annual amounts that you can use. You also could, uh, use your W2.
But depending on when you're starting this, uh, forecast might not have a current W2 handy and you might have changed jobs. So I'm saying for this, just use your most recent paycheck to get started. So let's put some of these items in here. The gross pay was 78,000. So what you can do is click on that. If you remember from the overview video, you can hit copyright copies of.
So what I'm going to do, if I decide to change a dollar amount that I think I'll be earning down the road, I really want this to be a percentage. So you can do it either way. You can do a flat dollar amount or you can do a percent of gross. So I'm going to show you both ways. So if I did minus 24, 18 and hit enter, it's going to put that flat dollar amount the whole way across.
Or if I take that out, And I put a minus 3.1%. You can see it's calculating that the whole way across. Now, one thing I wanna point out is it's always good to copy this to the right, even though it will carry it through, it's not actually calculating off the individual year income. So if you think your income's going to go up $2,000, you could go and type that in.
And this amount didn't change it. Didn't take this times 3.1%. What it does is it looks to see if there's anything here. If there's nothing here, it just brings in the previous cells value. So what I'm going to do is click on this cell, hit copyright, and now it's going to multiply this 80 times at 3.1% and give me the new calculation.
When I take this 80 in here, it didn't change these to the right. You have to click copyright and I can change those. So let's get, um, I want to get this in here.
So that was the pre-tax deductions. And let's get this health savings account in here.
To savings. So these gray cells here, you can change them. And the dropdown, what makes up those is if you go to the top here, it's whatever you have entered in here. So the spreadsheet comes with default descriptions. Those descriptions are defined in the notes tab, but you can change them. If, if they don't see your needs, you can change them to whatever you need.
So. Uh, let's get the rest of these here. So standard 401k was the other description. Copy that, put that in there. And if I look at that, the 401k was 4.3%. The house savings was 4.9. So I'm just going to put those percentages in here. The reason, even though it's saving money, um, it reason I'm putting in as a minus.
It's because I'm deducting it from my gross pay. So we'll go into how this all works together at the top, but I'm trying to get a gross pay and a take-home pay.
So I got all of my pre tax items. Now I got to get these taxes in here. So if you notice I'm out of room here. Let's click on this yellow cell over here, hit, add, and hit this add button at the top. And you can do that as many times as you need and add whatever you need to and to here. So let's get these taxes and that's 18.3%.
When you paste that in there, it automatically wrapped techs and makes it a bigger row. If you wanted that small again, you can just click and drag it to. Do you want a big again, you can just go in between the cells there, double click, make it big to that. Minus 18.3, and we need a copy of that to the right.
And when you have one more for these post-tax deductions, so I'm going to copy this and hit, add.
You could call this a, there's probably a multiple things you could call this. Um, a lot of this is insurance related, so I think I'm going to label it insurance expense. So if you click on this dropdown there's insurance expense and it's not 18.3, it's 0.6, you can actually copy that exact percentage come in here and pay special.
So if you hit the drop down here that this pay special value. And then you got to remember to copy it to the right. All right. So, um, if, if I did everything correctly, starting out with 78 subtracted, uh, all these various amounts and, um, I didn't have the exact percentage here on this one. So it's thrown off by number of bits.
So I'm going to get pay special, the exact percentage. There we go. And. It needs to be a negative. So it's very important that you are validating the numbers that you're coming up with. And that's why I have you comparing to a paycheck because if you would go and put these amounts in posit these percentages in positive, or go send them the wrong way.
Your gross pay and your take home pay might not actually be accurate. And at that point you might be making wrong assumptions about how much money you're saving for the future, which is like these 401ks and how much money you actually have to spend. So the reason I have this T doing this step of this paycheck breakdown is to make sure that you're getting all the elements.
Correct. So let's just go through and check. This has 78,000. I want to do, I want to get these exact percentages in here.
So see that I put in as a positive and it, through my. Uh, so I'm just going to fix that here.
K and
53, 6 43. I'm at 54, 6 21. So I'm missing something or 88. Oh, this is a positive. So that's the point of putting your paycheck in there because you want to make sure that you are coming up with the right number. So there we go, 53, 6 43, and now that matches. And now I'm confident that I have accurately reflected this employer.
Uh, what our current income is. I'm projecting it out over the future. And as long as I am. Keeping these things consistent. So if this is a negative here, you want to keep those going negative. These percentages, um, you can make changes. So here you can see I increased the amount and all, everything now should be calculating down through, uh, the same way.
If at that same time you had a pay increase, you wanted to increase what you're putting into your 401k. You would type that in. So I put a minus five. And I can copy that to the right. So now I'm saving more money into my 401k. So what I want to point out is, as you slide out to the right, you can see when you hit retirement years.
And obviously you're not going to keep earning the same amount at that point in time. So I'm going to put a zero into these fields. And copy this into the income here. And I'm a copy of that to the right, and that basically zeroes out everything. Cause all these percentages are calculating off. Um, that dollar amount, if you had flat dollar amounts in here that we're not calculating off of, off of the percentage, you would have to go and put a zero in these cells and copy them to the right to completely zero this stuff out.
You just put a zero, it copied. And it doesn't take long
and you would do it that way. So now you have everything zeroed out for your retirement years for that employer. Um, so that you have an accurate number here. So what's going on in this column is it's adding up all your gross pay over those years. You know, if you think you're going to get pay increase or a percent increase each year cost of living, you could come in here and you could actually write a formula.
So if I am clicking on this 80,000, the say, I think at this point I'm a start getting a one and a half percent pay increase. Each year. I take a hit of equal sign click on the previous cell two times 1.015. If you think that it's a one and a half percent increase. And you can see how that, what that meant in terms of a pay increase.
And you could copy that hit copyright, and that formula is carrying out to the right now. You have to be careful when you hit copyright. It goes the whole way to the end. So, um, because I had zeroed out these percentages. It's no longer taken on any taxes and you can see it's continuing this income. So again, you need it.
You make a change and you hit that copy. Right? It's be careful because it carries it the whole way.
The other thing is I put that one and a half percent increase. You can see the income goes up pretty quickly. If you know that it's going to cap out at a certain point, then you would want to. Change that formula at that point in time of where you think it capped out and you can just click and drag that formula to the right as well.
You don't have to hit this copyright button and that way it stopped where I wanted it to stop. So a couple of ways that you can work with the spreadsheet and account for what you think is going to happen with your income as a years, go on. And if you plan on increasing what you're putting into your 401k.
You can do the same thing, you know, at the very specific points in time, make those changes. What I'll say is, uh, some of these things, this exercise of putting this forecasting spreadsheet together will help you make some of these decisions. You'll see that maybe you don't, you're not saving enough money and you'll have to, you'll see.
At what point in time you have large expenses coming and you'll be able to more accurately determine when you should start saving more money. And, uh, the spreadsheet should help you make some of those determinations. So, um, before we spend too much time trying to figure all that out, let's, let's get into some of the other elements of the spreadsheet and start working on some of the loans you might have in investments that you have.
So that's how you add an employer and your paycheck. Again, if you have a second employer, you just add them. Put them below and up top, it's going to accumulate your gross pay and your take home pay. All right. That's it for this video. Thank you.
Click here to watch more videos https://www.xlyourfinances.com/videos.html
Uh, that's a very important as you can choose any starting year. So 2018 is almost over. You can put in a 2019 there, and you'll just want to correspond your current age. That you'll be that year. So when you come down to the bottom of your spreadsheet, income has already added, uh, I've typed this in earlier, but you type in the name of your employer.
So I'll put in employer. And if you have more than one employer, you hit add income up here and then you would type employer two or whatever. The name of that employer is and gross pay and take home pay is used in various calculations throughout the spreadsheet. So up here is cumulative gross pay and cumulative take-home pay.
And what it's doing is it's calculate. All of the gross pays. So if you added three or four incomes, it's going to add all those gross paste together and all those take-home home together so that you have a total gross pay and total take home pay, no matter how many employers you enter for this demonstration, I'm just going to have one.
So I'm going to type, delete here and hit delete, and that will remove. To enter into a employer. One, the income I have this paycheck breakdown and what this does is as you enter a dollar amounts from your paycheck, oh, multiplied by the number of paychecks you have a year. So if you get paid every two weeks, you put in 26, if you get paid twice a month, you put in 24.
So let's go ahead and fill this in so that we can get. With, uh, adding income. So looking at a paycheck, put in your gross pay. So you may or may not be familiar with the health savings account. You can type whatever items are on your paycheck into here. Obviously no one, no two paychecks are going to be the same.
I don't want to get into too much detail on a specific what are, what is on a specific paycheck? You have pre-tax pre-tax items. So other pre-tax deductions health insurance, dental, and then, uh, we're going to go into taxes. So if you're not familiar, how to write a formula in Excel and you have like four or five items for taxes, federal local state unemployment, if you just type a minus put in each amount, separated by a month.
And what you'll see is that we'll add it up for you. So get all those amounts in there and just put in some post-tax items, which would be expense, reimbursements, life insurance, vision coverage, some of those items. Um, but the whole point of this is to get to annual amounts that you can use. You also could, uh, use your W2.
But depending on when you're starting this, uh, forecast might not have a current W2 handy and you might have changed jobs. So I'm saying for this, just use your most recent paycheck to get started. So let's put some of these items in here. The gross pay was 78,000. So what you can do is click on that. If you remember from the overview video, you can hit copyright copies of.
So what I'm going to do, if I decide to change a dollar amount that I think I'll be earning down the road, I really want this to be a percentage. So you can do it either way. You can do a flat dollar amount or you can do a percent of gross. So I'm going to show you both ways. So if I did minus 24, 18 and hit enter, it's going to put that flat dollar amount the whole way across.
Or if I take that out, And I put a minus 3.1%. You can see it's calculating that the whole way across. Now, one thing I wanna point out is it's always good to copy this to the right, even though it will carry it through, it's not actually calculating off the individual year income. So if you think your income's going to go up $2,000, you could go and type that in.
And this amount didn't change it. Didn't take this times 3.1%. What it does is it looks to see if there's anything here. If there's nothing here, it just brings in the previous cells value. So what I'm going to do is click on this cell, hit copyright, and now it's going to multiply this 80 times at 3.1% and give me the new calculation.
When I take this 80 in here, it didn't change these to the right. You have to click copyright and I can change those. So let's get, um, I want to get this in here.
So that was the pre-tax deductions. And let's get this health savings account in here.
To savings. So these gray cells here, you can change them. And the dropdown, what makes up those is if you go to the top here, it's whatever you have entered in here. So the spreadsheet comes with default descriptions. Those descriptions are defined in the notes tab, but you can change them. If, if they don't see your needs, you can change them to whatever you need.
So. Uh, let's get the rest of these here. So standard 401k was the other description. Copy that, put that in there. And if I look at that, the 401k was 4.3%. The house savings was 4.9. So I'm just going to put those percentages in here. The reason, even though it's saving money, um, it reason I'm putting in as a minus.
It's because I'm deducting it from my gross pay. So we'll go into how this all works together at the top, but I'm trying to get a gross pay and a take-home pay.
So I got all of my pre tax items. Now I got to get these taxes in here. So if you notice I'm out of room here. Let's click on this yellow cell over here, hit, add, and hit this add button at the top. And you can do that as many times as you need and add whatever you need to and to here. So let's get these taxes and that's 18.3%.
When you paste that in there, it automatically wrapped techs and makes it a bigger row. If you wanted that small again, you can just click and drag it to. Do you want a big again, you can just go in between the cells there, double click, make it big to that. Minus 18.3, and we need a copy of that to the right.
And when you have one more for these post-tax deductions, so I'm going to copy this and hit, add.
You could call this a, there's probably a multiple things you could call this. Um, a lot of this is insurance related, so I think I'm going to label it insurance expense. So if you click on this dropdown there's insurance expense and it's not 18.3, it's 0.6, you can actually copy that exact percentage come in here and pay special.
So if you hit the drop down here that this pay special value. And then you got to remember to copy it to the right. All right. So, um, if, if I did everything correctly, starting out with 78 subtracted, uh, all these various amounts and, um, I didn't have the exact percentage here on this one. So it's thrown off by number of bits.
So I'm going to get pay special, the exact percentage. There we go. And. It needs to be a negative. So it's very important that you are validating the numbers that you're coming up with. And that's why I have you comparing to a paycheck because if you would go and put these amounts in posit these percentages in positive, or go send them the wrong way.
Your gross pay and your take home pay might not actually be accurate. And at that point you might be making wrong assumptions about how much money you're saving for the future, which is like these 401ks and how much money you actually have to spend. So the reason I have this T doing this step of this paycheck breakdown is to make sure that you're getting all the elements.
Correct. So let's just go through and check. This has 78,000. I want to do, I want to get these exact percentages in here.
So see that I put in as a positive and it, through my. Uh, so I'm just going to fix that here.
K and
53, 6 43. I'm at 54, 6 21. So I'm missing something or 88. Oh, this is a positive. So that's the point of putting your paycheck in there because you want to make sure that you are coming up with the right number. So there we go, 53, 6 43, and now that matches. And now I'm confident that I have accurately reflected this employer.
Uh, what our current income is. I'm projecting it out over the future. And as long as I am. Keeping these things consistent. So if this is a negative here, you want to keep those going negative. These percentages, um, you can make changes. So here you can see I increased the amount and all, everything now should be calculating down through, uh, the same way.
If at that same time you had a pay increase, you wanted to increase what you're putting into your 401k. You would type that in. So I put a minus five. And I can copy that to the right. So now I'm saving more money into my 401k. So what I want to point out is, as you slide out to the right, you can see when you hit retirement years.
And obviously you're not going to keep earning the same amount at that point in time. So I'm going to put a zero into these fields. And copy this into the income here. And I'm a copy of that to the right, and that basically zeroes out everything. Cause all these percentages are calculating off. Um, that dollar amount, if you had flat dollar amounts in here that we're not calculating off of, off of the percentage, you would have to go and put a zero in these cells and copy them to the right to completely zero this stuff out.
You just put a zero, it copied. And it doesn't take long
and you would do it that way. So now you have everything zeroed out for your retirement years for that employer. Um, so that you have an accurate number here. So what's going on in this column is it's adding up all your gross pay over those years. You know, if you think you're going to get pay increase or a percent increase each year cost of living, you could come in here and you could actually write a formula.
So if I am clicking on this 80,000, the say, I think at this point I'm a start getting a one and a half percent pay increase. Each year. I take a hit of equal sign click on the previous cell two times 1.015. If you think that it's a one and a half percent increase. And you can see how that, what that meant in terms of a pay increase.
And you could copy that hit copyright, and that formula is carrying out to the right now. You have to be careful when you hit copyright. It goes the whole way to the end. So, um, because I had zeroed out these percentages. It's no longer taken on any taxes and you can see it's continuing this income. So again, you need it.
You make a change and you hit that copy. Right? It's be careful because it carries it the whole way.
The other thing is I put that one and a half percent increase. You can see the income goes up pretty quickly. If you know that it's going to cap out at a certain point, then you would want to. Change that formula at that point in time of where you think it capped out and you can just click and drag that formula to the right as well.
You don't have to hit this copyright button and that way it stopped where I wanted it to stop. So a couple of ways that you can work with the spreadsheet and account for what you think is going to happen with your income as a years, go on. And if you plan on increasing what you're putting into your 401k.
You can do the same thing, you know, at the very specific points in time, make those changes. What I'll say is, uh, some of these things, this exercise of putting this forecasting spreadsheet together will help you make some of these decisions. You'll see that maybe you don't, you're not saving enough money and you'll have to, you'll see.
At what point in time you have large expenses coming and you'll be able to more accurately determine when you should start saving more money. And, uh, the spreadsheet should help you make some of those determinations. So, um, before we spend too much time trying to figure all that out, let's, let's get into some of the other elements of the spreadsheet and start working on some of the loans you might have in investments that you have.
So that's how you add an employer and your paycheck. Again, if you have a second employer, you just add them. Put them below and up top, it's going to accumulate your gross pay and your take home pay. All right. That's it for this video. Thank you.
Click here to watch more videos https://www.xlyourfinances.com/videos.html
Notice:
All of XLYourFinances' spreadsheets are protected by copyright laws. Breaking into the spreadsheet’s macros or breaching its password protection not only makes guarantees of the spreadsheet’s usability and support for the spreadsheet null, but is also considered a copyright infringement, as the methodologies and techniques utilized are proprietary and owned by XLYourFinances, LLC. Please refer to the terms agreement for more details on the terms of use.
All of XLYourFinances' spreadsheets are protected by copyright laws. Breaking into the spreadsheet’s macros or breaching its password protection not only makes guarantees of the spreadsheet’s usability and support for the spreadsheet null, but is also considered a copyright infringement, as the methodologies and techniques utilized are proprietary and owned by XLYourFinances, LLC. Please refer to the terms agreement for more details on the terms of use.
| terms_agreement.pdf |
XLYourFinances.com is owned and operated by XLYourFinances, LLC.
All XLYourFinances.com spreadsheets are copyright protected, © 2010-2022.
All XLYourFinances.com spreadsheets are copyright protected, © 2010-2022.