|
your forecast video transcript:
Bringing it all together in this video, we're going to go through how all these layers and all these elements work together to create your forecast. So far, we have added investments. We've added assets with loans, loans, income, large expenses, and our general expense. All of this information has been feeding up here into the top.
This cashflow section. I have not talked a whole lot about the cashflow, but at the end of the day, this really is what this forecast is all about. Are we saying we're going to spend more money than we have are our plans too expensive. This is where you find out. So right off the bat, if you see red. You have some issues.
And from looking at this, we have a lot of issues looking at this spreadsheet. So the first thing I want to do is make sure that I don't have duplicates in my different categories. So the way you can do this as you go through each one of these items. So right here, the first one is earnings. And if you remember, these items are being summed over here by year.
Based on what's in column N so you can pull this auto filter here for each of these items and then review what's going on with them. So I'm going to pour this auto filter and the first one is earnings and you can type earnings there. It pops up and there's only one item there, so that one's pretty straightforward.
You click on this auto field or again, hit clear it's due. The next one is speed. Start typing that in there, there it is. And the items feeding into this is my fixed and my variable categories clear that next item is large expenses,
and we just want to roll those. So I'm familiar with that. You can clear that. Next time was giving. So the reason you're doing this is you just want to make sure there's no surprises, something accidentally mislabeled. So let's do the next one insurance expense. I'm fine with that. And a clear that next one is taxed.
So there are two items feeding into here. One is from the paycheck and the other one is from the 401k tax liability. If I slide out here to the right now, we're estimating in retirement years,
Claire, that, so the next item on the list is savings. So let's take a look at this.
Okay, so a couple things going on here. If you recall at the top, the balance was 600 and some thousand. And if I just add these together, it's 195,000. So you're like, where is that number coming from? So if you remember the cashflow section of the top, let's go take a look at that. If you label something, money out, what the system is doing is it's taking the absolute value of all the items that fall into that category.
So anything that's in savings, the absolute value of those items is being added together. If you have money. It's doing the absolute value of anything with those items, with those labels and adding them together. If you want it to do a straight some, because you have money coming in and you have money going out, then you need to use a label in or out, or just leave this blank one or the either way.
So for savings since we have it money out, it's doing the absolute value of all those. So let's take a look again around click on savings. It's 630, 8,000.
So if you take the absolute value of each of these items, that's 417 and absolute value of 2 21. And if you add that together, that's where you get to over $600,000. But what exactly is going on here? Why are some negative and some positive and should they all be adding together? So we know we have four items labeled savings.
Let's take a look this and make sure it's right. Click off of this. So we can go down and take a look at them. So under employer, I put savings money. That's going into the 401k and money. It's going into the health savings account. Where else do I have saved? Well down here, the 401k investment and how savings account investment, both have the label savings.
So problem right off the bat here is I have a mount's going into the 401k that are not accurate because up here I said, I was putting this amount into the 401k. So that's one problem. Same thing with the HSA. I have different amounts going into the. Then reality, Ashley says is going in there. So we need to fix that.
The second thing is I don't want to double count these if I'm paying this plus this and to savings, and then I come down here and it's going to take the absolute value and add these two amounts. Again, that's been double counted. So you have a choice of removing this word here, the savings, if you blank it out.
So. Now, it's not going to add it up, up top here in the summary. Remember, it's doing a sum off anything with that label in column in so I can remove it there and I can remove it here. So I would take those two out, but now the danger is I have an amount here. That's not being summed up into the top. I need to make sure it's accurate.
So a fit. Is I'm going to make this line here instead of it being a percent, I'm going to just make it equal to the negative of what's coming out of the paycheck.
So now that matches that formula is making it equal. I'm going to hit copy. So now you can see the contributions 118,008 16. I come up here 118,008, 16. We're going to do the same thing with the health savings account. Whenever you can do this. So these investment sections are taken care of return on investment, withdrawals, taxes, additional contributions, and all of those things, but to think up.
Spreadsheet. You want to, whenever you can link it to the actual paycheck here. So there's the house savings account. I'm gonna take that formula. Copy it. Right. And now that formula is following. What's actually being contributed. So now if I come down through here and say, I want to contribute more at a certain point in time and I make this 600.
That's going to reflect down into this section. So let's, let's just test that. Let's say in 2027,
I'm going to up what I'm putting in to my HSA to 6%. So this 42 86 became 52 49. Now.
There's the amount going in 52, 49. Cause I have that formula, oops, down here. How savings? 52 49. And I have it linked up there. So we're going to do that for now. So now that I linked those together and I'm not double counting savings, the amount being put to savings is right here coming out of my paycheck.
Now, if you're making a difference. Contributions to your 401k or how savings account or you have other investments that you're saving money too. You can just add an investment and then you could keep the word savings there because it's money that's going towards saving some money out. That's not are already accounted for.
What was tricky about these two is they were already accounted for in the income section because they were already accounted for that. I was actually double counting them. So if you just start with your income and all you do is put your take home pay and you don't put all these elements and you list those elements separately, then you don't have that issue.
I like doing it this way though, because you can match this up to your actual paycheck and make sure that you're accurately reflecting the amount that your. Uh, way into the savings vehicles. All right. So we got, let's just come back up here to the top. And we went through savings. We got that corrected.
Now, now let's look at this withdrawal from savings. So,
so we have the main withdrawal from savings is from the 401k. And you can see that starts in the retirement years. So that's the only item there that looks like. Hey, Claire filter. And this last one is loan payments. So let's take a look at loan payments.
So we had this credit card payments, and then we have our main mortgage or home equity line of credit. And these are all the payments there.
All right. So we can clear that out. So we're at this point. This is showing. I feel pretty good about the items that I have represented here. They aren't being double counted anymore, and you want to look at things and make sure they look reasonable to you. So for this year, it says that you will net almost $10,000.
Is that how you're truly living? If so, you should be ending the year with some cash in your bank.
So some of the things to keep in mind here, this cashflow, it's very important that this cumulative cash position stays positive. If these items are going negative, then you need to do something different. So let's take a look at this second year. How do we go from a $9,800 positive to a $1,500. So if I look at the different items that change year over year, there's $3,500, more large expense, and this loan payment went way up.
So let's go take a look at the loan payment. So coming down through here, the first thing I see is this bank of America loan payment. So we're trying to pay off this 0% interest here. And we have as Wells Fargo panel. And then this interest rate got changed to 5%. So if you have a variable rate and you think interest rates are going up, maybe it's going up.
If you have a fixed rate, then it would be staying the same. So I'm going to have some put that back to three and a half. Assume we have a fixed rate and that's it for the loan payments. So let's go back to the top. All right. Changing that interest rate on the mortgage. Put us back into the positive for that year, which is good.
You can see our cumulative cash position is growing, which also is good. And then here's another year where the amount goes negative. So what's driving the difference here. You can see year to year, if there's changes and we paid off the credit cards from this year to this year, hopefully we'd own accumulate more of the.
You can see some large expenses starting to hit and everything. There is a little bit more income. Uh, some of the expending must be a percentage of the income. So that went up as well, which is probably reasonable because we tend to spend more as we make. And let's take a look at some of these large expenses that hit this year.
So we had this trip to Disney and we had this replace a car. So this replace car was basically saying you're buying a $20,000 car. And as we look out through the upcoming years and we see some of these cumulative cash positions of being negative going the whole way up to over 150, almost 150,000, we say, you know what?
We've got to make some different decisions. And this is the whole point of the forecast, the decisions you're making today, control what you're able to do tomorrow. And if you can get out of debt and you can save money and live well below your means, then you can afford to do some of these large expenses that you want to do down the road.
But it might mean that you're gonna have to make some sacrifices along the way. So the trip to Disney world might not be feasible. But another way of looking at this is saying, Hey, this car, we don't need a $20,000 car. We need a $10,000 car. So one change we're going to make is to change this to a $10,000 car.
Now that means some of our repairs or maintenance might be a little higher, and we might need to take that into consideration up here, but for simplicity reasons, I'm just going to make some of these changes. We have a lot of rides. So I'm going to want to change all of these cars down to $10,000 cars, which may or may not be reasonable.
That's something you're going to have to make a determination for. And let's see what that does up here. So some of these expenses become a little more bearable and we're back to netting a little bit better cash position year over. Now we get to 2025, a negative 10,000. So what's happening here? Large expense here.
22,000. Okay. So this is the college contribution where we were hoping we could give 20,000 a year per kid. Well, that's 240,000. I knew that was going to be high, but I was just putting it out there just to show you an example. So let's put. Down to something that might be a little more attainable and a lot of school loans are gonna be needed and other things, but let's just get some numbers in here of what we think we're gonna need to contribute to help a child get through college.
And we'll take this down
and let's see. What happens here now, we're going to go back up 2025 and you can see pretty much just a $400 loss for the year. And this year we get to this $10,000 loss area where you have multiple kids in school. So this is a tough year. And then this is a tough year here and that's reflected up here.
So a negative cumulative cash position. Is a problem. Your cash forecast really isn't accurate because people don't just give you stuff for free. You have to have no red down here. You have to be able to cover your expenses. So that's what this available collateral available and secure credit available savings are indicating you're short, and you're going to have to tap into some of this collateral.
So you look at this and you say, okay, well I have plenty of coverage and so I'm done, but the answer, that's not the correct answer. The reality is you might have coverage, but you need to account for these shortages somewhere down below, so that you then are taking the hit on interest expense and other domino effect of things that happens.
Let's just show you this, these five years. Negative. And I need to get this to a positive. So just want to come down here. Say so right here, the home equity line of credit, we're saying we're willing to borrow up to 80% of our equity. So let's, let's do that. We're going to take these five numbers here.
And I'm in 2029, I'm going to come down here to the home equity line of credit past it. And we're going to say 2029, these are additional borrowings. Now my pace of those in there, it put them in as a negative, which actually is reducing my loan balance. We know that's wrong. So I'm going to show you an Excel trick to get these, to be positive numbers.
It's going to put a minus.
Copy that highlight these items pay special, multiply and hit. Okay. And it just changed those to positive numbers. So what this is doing is increasing my loan and you can see the previous year, the loan balance was 89. We make all these payments without the. It would have been take this out would have been 80, but because we borrowed that money now it's 83.
The other thing I want to point out is with these new borrowing additional borrowings, you can see the loan gets paid off in 2042 out here. If I take this off, you can see the loan gets paid off in 2037. So we're gonna hit on. So it adds an additional five years to paint off this loan. Now, if I hit delete now, the thing it does, here's the interest I was going to pay was 59,000.
Let's put those numbers back in interest, goes up to 73,000. So this is the power of this spreadsheet. Is it shows you, Hey, in these future years, I'm going to have to borrow from our home equity line. To help the children get through college and that's going to extend out how long we pay our mortgage and the amount of interest that we're paying.
So you take a look at this and say, okay, I need to make sure this amount shows up above. So one of the things that I had in here earlier was I just had this labeled as loan payments. The idea was whether you're borrowing money, paying down principal, Or your normal mortgage payment, it's all just payments going against the loan.
So if I do that, this 41,000 is going to show up here as additional loan payments. So let's go up here and you can see here, the loan payments basically are reduced. So we're paying 11,000 a year. Now they go way down because we borrowed money from that and it brought our cumulative cash position to a positive, the ride goes away.
Now I really don't like that because it's really hard to see what's going on because it's just buried in loan payments. So what I want to do is say borrow from Wheelock home equity line of credit. And I'm going to go home down here because I added that. Anything you add in the yellow here is available to select down below.
So now it can come down here, find this line and instead of a loan payments, select borrow from hilar. So now when I come up top here, now it's clear to see what's happening. Still making the loan payments, borrowing money against the equity cash position. Now stays positive. So you can see how important it is to have this cashflow section working properly, to make sure there's nothing being double counted, to make sure that you're keeping a positive cumulative cash position.
So, and that you are properly labeling things. So you can understand what you're looking at when you go to update your forecast into the, into the future. So as I go out to the right here, you can see income stops spending is reduced. As you hit retirement age, you're withdrawing money from savings, and you can see your net cash position for the year.
So I'm looking at this and saying something doesn't look quite right. Why would my net cash position be so strong in these retirement years? And I'm going to come down here and here's where the problem. This is calculating all percent of the take home. And you can see, take home, pay, went to zero at this point.
So I need to change this back and we're going to say that the fixed expenses are 72 50. Get, get rid of the percentage
and I'm going to change it. Format copy of that to.
So let's take a look. Okay. Still think that's a little strong. The other thing is you can see this available savings depleting down to zero really quick. And I think 45,000 taking 45,000 out a year is too strong, especially cause if you, if you're netting 11,000. You really want to be taken out what you need.
So if you're netting 11,000, you'd need to be putting it back into some other types of savings. So let's get this more in line to expenses, 30 3007. So let's just put 34,000 down here. So we're going to go down to a 401k,
I think this 34,000 and copy of that. And what Excel, your finances, forecasting spreadsheet will not allow you to go negative. So your withdrawals here is, is adding up this line, which once you run out of money, see that last one, it was only 31 and that zero. So it's not adding up all these 30 fours, even though you put them in there, you still wandering earning four and a half percent interest and paying 20% tax liability of the money you take.
So let's take a look at what that does come back up to the top.
And this looks a lot more reasonable. You have, you hit retirement age, you're pulling out enough money and your term at savings is lasting a lot longer than it goes up to 87. Something still doesn't look right on. These assets down here. My main asset is the 401k and the other asset would be the home. So let's go take a look at what's going down there.
So 2052, it says 780,000. I just want to see what the assets are. Actually, what I'm going to do is use the auto filter just to identify them. Okay. So here's the home. We said it towards two 50 here's the 401k says it's worth 4 27, but why is my health savings accounts sitting here at 102,000 across all these years?
So I had added the health savings account earlier, but then I really didn't take time to deplete it or use it up and using expensive. And so this thing's just sitting out here. So that's why it's important to analyze each use the auto filter and analyze each item assets. Let's just take a look a lot.
We'll fix that, but let's take a look at liabilities.
Okay. So it's saying we're going to have no liabilities once we get to age 65, because we're paying off all of our debt and that's great. And that looks in lines. That's going to take care of that health savings count and talk through real quick, how this would work. So you're putting money into here, but if you have a health savings account, basically, you know, you need to be taking money out as well.
Now, the only reason you wouldn't be taking money out of here is because you have medical expenses contained in your variable or fixed expenses up above. However, if you're doing that, what I would recommend is take the medical expenses out of the. And put them down here as withdrawals, because that way it's going to impact how much money is here and available for earning interest, uh, and tax implications and the like, so I'm going to come down here and say based on life easily going to be spending 2,500 a year.
If I take that out to the right now, You're putting in 3,800 spending 2,500, you have 1300 left and you repeat that throughout the years and you can see what's going on. You get to a point where you stopped working, you stop contributing. And so the balance stopped growing and in the balance is depleting.
And then it gets so point where there is no balance. So that's more in line with. And so we're going to leave it like that for now. If you find yourself spending all the money in your house savings account, then really probably don't even need this section would just be money coming out of your paycheck.
It's a hundred percent spend and there is no advantage, no money being leftover for future savings. So you wouldn't want to do this. If it's not in line with reality, this seems reasonable for, for our purposes here today. I'll just say. Come up to the top again. And now when I look at my total assets, this reflecting the home, the 401k, and what's left of the health savings account.
So that's it for now. I hope you see how there is limitless possibilities for your future. And depending on. Your hopes and dreams are and what your goals are. There are ways to get there and there's ways to make it happen. A lot of it comes down to how much money you're spending and what you can afford.
So if you take the time to put together a forecast and you really take the time to refine it and make it reflect your reality and adjust some of your goals and what you think you're going to be able to do in the future. I really think I can give you confidence to know if you're on the right track or not.
So I hope you enjoyed putting the forecast together, continue to refine it and test it. I think testing it for accuracy is paramount. There's a lot going on with the spreadsheet. It's, it's easy to put a number in cupping out to the right and not give it any more thought, but the reality is. You need to be careful to make sure that the numbers you're putting in here are reflective of reality.
I would highly recommend to tell your finances, budget spreadsheet, which has detailed tracking of your expenses into categories so that you can create a good budget, but you can't create a good budget unless you know where you're, what you're spending and you can't create a good forecast unless you know what you're spending.
The first step might be buying the budget spreadsheet and getting a handle on where your money is going and building a good budget, and then tackling, putting together this forecast really look forward to feedback and hearing what you have to say and what you learned. And I really hope that this forecasting spreadsheet can help you make wise decisions and help you plan appropriately for your future.
Thank you.
click here to watch more videos https://www.xlyourfinances.com/videos.html
This cashflow section. I have not talked a whole lot about the cashflow, but at the end of the day, this really is what this forecast is all about. Are we saying we're going to spend more money than we have are our plans too expensive. This is where you find out. So right off the bat, if you see red. You have some issues.
And from looking at this, we have a lot of issues looking at this spreadsheet. So the first thing I want to do is make sure that I don't have duplicates in my different categories. So the way you can do this as you go through each one of these items. So right here, the first one is earnings. And if you remember, these items are being summed over here by year.
Based on what's in column N so you can pull this auto filter here for each of these items and then review what's going on with them. So I'm going to pour this auto filter and the first one is earnings and you can type earnings there. It pops up and there's only one item there, so that one's pretty straightforward.
You click on this auto field or again, hit clear it's due. The next one is speed. Start typing that in there, there it is. And the items feeding into this is my fixed and my variable categories clear that next item is large expenses,
and we just want to roll those. So I'm familiar with that. You can clear that. Next time was giving. So the reason you're doing this is you just want to make sure there's no surprises, something accidentally mislabeled. So let's do the next one insurance expense. I'm fine with that. And a clear that next one is taxed.
So there are two items feeding into here. One is from the paycheck and the other one is from the 401k tax liability. If I slide out here to the right now, we're estimating in retirement years,
Claire, that, so the next item on the list is savings. So let's take a look at this.
Okay, so a couple things going on here. If you recall at the top, the balance was 600 and some thousand. And if I just add these together, it's 195,000. So you're like, where is that number coming from? So if you remember the cashflow section of the top, let's go take a look at that. If you label something, money out, what the system is doing is it's taking the absolute value of all the items that fall into that category.
So anything that's in savings, the absolute value of those items is being added together. If you have money. It's doing the absolute value of anything with those items, with those labels and adding them together. If you want it to do a straight some, because you have money coming in and you have money going out, then you need to use a label in or out, or just leave this blank one or the either way.
So for savings since we have it money out, it's doing the absolute value of all those. So let's take a look again around click on savings. It's 630, 8,000.
So if you take the absolute value of each of these items, that's 417 and absolute value of 2 21. And if you add that together, that's where you get to over $600,000. But what exactly is going on here? Why are some negative and some positive and should they all be adding together? So we know we have four items labeled savings.
Let's take a look this and make sure it's right. Click off of this. So we can go down and take a look at them. So under employer, I put savings money. That's going into the 401k and money. It's going into the health savings account. Where else do I have saved? Well down here, the 401k investment and how savings account investment, both have the label savings.
So problem right off the bat here is I have a mount's going into the 401k that are not accurate because up here I said, I was putting this amount into the 401k. So that's one problem. Same thing with the HSA. I have different amounts going into the. Then reality, Ashley says is going in there. So we need to fix that.
The second thing is I don't want to double count these if I'm paying this plus this and to savings, and then I come down here and it's going to take the absolute value and add these two amounts. Again, that's been double counted. So you have a choice of removing this word here, the savings, if you blank it out.
So. Now, it's not going to add it up, up top here in the summary. Remember, it's doing a sum off anything with that label in column in so I can remove it there and I can remove it here. So I would take those two out, but now the danger is I have an amount here. That's not being summed up into the top. I need to make sure it's accurate.
So a fit. Is I'm going to make this line here instead of it being a percent, I'm going to just make it equal to the negative of what's coming out of the paycheck.
So now that matches that formula is making it equal. I'm going to hit copy. So now you can see the contributions 118,008 16. I come up here 118,008, 16. We're going to do the same thing with the health savings account. Whenever you can do this. So these investment sections are taken care of return on investment, withdrawals, taxes, additional contributions, and all of those things, but to think up.
Spreadsheet. You want to, whenever you can link it to the actual paycheck here. So there's the house savings account. I'm gonna take that formula. Copy it. Right. And now that formula is following. What's actually being contributed. So now if I come down through here and say, I want to contribute more at a certain point in time and I make this 600.
That's going to reflect down into this section. So let's, let's just test that. Let's say in 2027,
I'm going to up what I'm putting in to my HSA to 6%. So this 42 86 became 52 49. Now.
There's the amount going in 52, 49. Cause I have that formula, oops, down here. How savings? 52 49. And I have it linked up there. So we're going to do that for now. So now that I linked those together and I'm not double counting savings, the amount being put to savings is right here coming out of my paycheck.
Now, if you're making a difference. Contributions to your 401k or how savings account or you have other investments that you're saving money too. You can just add an investment and then you could keep the word savings there because it's money that's going towards saving some money out. That's not are already accounted for.
What was tricky about these two is they were already accounted for in the income section because they were already accounted for that. I was actually double counting them. So if you just start with your income and all you do is put your take home pay and you don't put all these elements and you list those elements separately, then you don't have that issue.
I like doing it this way though, because you can match this up to your actual paycheck and make sure that you're accurately reflecting the amount that your. Uh, way into the savings vehicles. All right. So we got, let's just come back up here to the top. And we went through savings. We got that corrected.
Now, now let's look at this withdrawal from savings. So,
so we have the main withdrawal from savings is from the 401k. And you can see that starts in the retirement years. So that's the only item there that looks like. Hey, Claire filter. And this last one is loan payments. So let's take a look at loan payments.
So we had this credit card payments, and then we have our main mortgage or home equity line of credit. And these are all the payments there.
All right. So we can clear that out. So we're at this point. This is showing. I feel pretty good about the items that I have represented here. They aren't being double counted anymore, and you want to look at things and make sure they look reasonable to you. So for this year, it says that you will net almost $10,000.
Is that how you're truly living? If so, you should be ending the year with some cash in your bank.
So some of the things to keep in mind here, this cashflow, it's very important that this cumulative cash position stays positive. If these items are going negative, then you need to do something different. So let's take a look at this second year. How do we go from a $9,800 positive to a $1,500. So if I look at the different items that change year over year, there's $3,500, more large expense, and this loan payment went way up.
So let's go take a look at the loan payment. So coming down through here, the first thing I see is this bank of America loan payment. So we're trying to pay off this 0% interest here. And we have as Wells Fargo panel. And then this interest rate got changed to 5%. So if you have a variable rate and you think interest rates are going up, maybe it's going up.
If you have a fixed rate, then it would be staying the same. So I'm going to have some put that back to three and a half. Assume we have a fixed rate and that's it for the loan payments. So let's go back to the top. All right. Changing that interest rate on the mortgage. Put us back into the positive for that year, which is good.
You can see our cumulative cash position is growing, which also is good. And then here's another year where the amount goes negative. So what's driving the difference here. You can see year to year, if there's changes and we paid off the credit cards from this year to this year, hopefully we'd own accumulate more of the.
You can see some large expenses starting to hit and everything. There is a little bit more income. Uh, some of the expending must be a percentage of the income. So that went up as well, which is probably reasonable because we tend to spend more as we make. And let's take a look at some of these large expenses that hit this year.
So we had this trip to Disney and we had this replace a car. So this replace car was basically saying you're buying a $20,000 car. And as we look out through the upcoming years and we see some of these cumulative cash positions of being negative going the whole way up to over 150, almost 150,000, we say, you know what?
We've got to make some different decisions. And this is the whole point of the forecast, the decisions you're making today, control what you're able to do tomorrow. And if you can get out of debt and you can save money and live well below your means, then you can afford to do some of these large expenses that you want to do down the road.
But it might mean that you're gonna have to make some sacrifices along the way. So the trip to Disney world might not be feasible. But another way of looking at this is saying, Hey, this car, we don't need a $20,000 car. We need a $10,000 car. So one change we're going to make is to change this to a $10,000 car.
Now that means some of our repairs or maintenance might be a little higher, and we might need to take that into consideration up here, but for simplicity reasons, I'm just going to make some of these changes. We have a lot of rides. So I'm going to want to change all of these cars down to $10,000 cars, which may or may not be reasonable.
That's something you're going to have to make a determination for. And let's see what that does up here. So some of these expenses become a little more bearable and we're back to netting a little bit better cash position year over. Now we get to 2025, a negative 10,000. So what's happening here? Large expense here.
22,000. Okay. So this is the college contribution where we were hoping we could give 20,000 a year per kid. Well, that's 240,000. I knew that was going to be high, but I was just putting it out there just to show you an example. So let's put. Down to something that might be a little more attainable and a lot of school loans are gonna be needed and other things, but let's just get some numbers in here of what we think we're gonna need to contribute to help a child get through college.
And we'll take this down
and let's see. What happens here now, we're going to go back up 2025 and you can see pretty much just a $400 loss for the year. And this year we get to this $10,000 loss area where you have multiple kids in school. So this is a tough year. And then this is a tough year here and that's reflected up here.
So a negative cumulative cash position. Is a problem. Your cash forecast really isn't accurate because people don't just give you stuff for free. You have to have no red down here. You have to be able to cover your expenses. So that's what this available collateral available and secure credit available savings are indicating you're short, and you're going to have to tap into some of this collateral.
So you look at this and you say, okay, well I have plenty of coverage and so I'm done, but the answer, that's not the correct answer. The reality is you might have coverage, but you need to account for these shortages somewhere down below, so that you then are taking the hit on interest expense and other domino effect of things that happens.
Let's just show you this, these five years. Negative. And I need to get this to a positive. So just want to come down here. Say so right here, the home equity line of credit, we're saying we're willing to borrow up to 80% of our equity. So let's, let's do that. We're going to take these five numbers here.
And I'm in 2029, I'm going to come down here to the home equity line of credit past it. And we're going to say 2029, these are additional borrowings. Now my pace of those in there, it put them in as a negative, which actually is reducing my loan balance. We know that's wrong. So I'm going to show you an Excel trick to get these, to be positive numbers.
It's going to put a minus.
Copy that highlight these items pay special, multiply and hit. Okay. And it just changed those to positive numbers. So what this is doing is increasing my loan and you can see the previous year, the loan balance was 89. We make all these payments without the. It would have been take this out would have been 80, but because we borrowed that money now it's 83.
The other thing I want to point out is with these new borrowing additional borrowings, you can see the loan gets paid off in 2042 out here. If I take this off, you can see the loan gets paid off in 2037. So we're gonna hit on. So it adds an additional five years to paint off this loan. Now, if I hit delete now, the thing it does, here's the interest I was going to pay was 59,000.
Let's put those numbers back in interest, goes up to 73,000. So this is the power of this spreadsheet. Is it shows you, Hey, in these future years, I'm going to have to borrow from our home equity line. To help the children get through college and that's going to extend out how long we pay our mortgage and the amount of interest that we're paying.
So you take a look at this and say, okay, I need to make sure this amount shows up above. So one of the things that I had in here earlier was I just had this labeled as loan payments. The idea was whether you're borrowing money, paying down principal, Or your normal mortgage payment, it's all just payments going against the loan.
So if I do that, this 41,000 is going to show up here as additional loan payments. So let's go up here and you can see here, the loan payments basically are reduced. So we're paying 11,000 a year. Now they go way down because we borrowed money from that and it brought our cumulative cash position to a positive, the ride goes away.
Now I really don't like that because it's really hard to see what's going on because it's just buried in loan payments. So what I want to do is say borrow from Wheelock home equity line of credit. And I'm going to go home down here because I added that. Anything you add in the yellow here is available to select down below.
So now it can come down here, find this line and instead of a loan payments, select borrow from hilar. So now when I come up top here, now it's clear to see what's happening. Still making the loan payments, borrowing money against the equity cash position. Now stays positive. So you can see how important it is to have this cashflow section working properly, to make sure there's nothing being double counted, to make sure that you're keeping a positive cumulative cash position.
So, and that you are properly labeling things. So you can understand what you're looking at when you go to update your forecast into the, into the future. So as I go out to the right here, you can see income stops spending is reduced. As you hit retirement age, you're withdrawing money from savings, and you can see your net cash position for the year.
So I'm looking at this and saying something doesn't look quite right. Why would my net cash position be so strong in these retirement years? And I'm going to come down here and here's where the problem. This is calculating all percent of the take home. And you can see, take home, pay, went to zero at this point.
So I need to change this back and we're going to say that the fixed expenses are 72 50. Get, get rid of the percentage
and I'm going to change it. Format copy of that to.
So let's take a look. Okay. Still think that's a little strong. The other thing is you can see this available savings depleting down to zero really quick. And I think 45,000 taking 45,000 out a year is too strong, especially cause if you, if you're netting 11,000. You really want to be taken out what you need.
So if you're netting 11,000, you'd need to be putting it back into some other types of savings. So let's get this more in line to expenses, 30 3007. So let's just put 34,000 down here. So we're going to go down to a 401k,
I think this 34,000 and copy of that. And what Excel, your finances, forecasting spreadsheet will not allow you to go negative. So your withdrawals here is, is adding up this line, which once you run out of money, see that last one, it was only 31 and that zero. So it's not adding up all these 30 fours, even though you put them in there, you still wandering earning four and a half percent interest and paying 20% tax liability of the money you take.
So let's take a look at what that does come back up to the top.
And this looks a lot more reasonable. You have, you hit retirement age, you're pulling out enough money and your term at savings is lasting a lot longer than it goes up to 87. Something still doesn't look right on. These assets down here. My main asset is the 401k and the other asset would be the home. So let's go take a look at what's going down there.
So 2052, it says 780,000. I just want to see what the assets are. Actually, what I'm going to do is use the auto filter just to identify them. Okay. So here's the home. We said it towards two 50 here's the 401k says it's worth 4 27, but why is my health savings accounts sitting here at 102,000 across all these years?
So I had added the health savings account earlier, but then I really didn't take time to deplete it or use it up and using expensive. And so this thing's just sitting out here. So that's why it's important to analyze each use the auto filter and analyze each item assets. Let's just take a look a lot.
We'll fix that, but let's take a look at liabilities.
Okay. So it's saying we're going to have no liabilities once we get to age 65, because we're paying off all of our debt and that's great. And that looks in lines. That's going to take care of that health savings count and talk through real quick, how this would work. So you're putting money into here, but if you have a health savings account, basically, you know, you need to be taking money out as well.
Now, the only reason you wouldn't be taking money out of here is because you have medical expenses contained in your variable or fixed expenses up above. However, if you're doing that, what I would recommend is take the medical expenses out of the. And put them down here as withdrawals, because that way it's going to impact how much money is here and available for earning interest, uh, and tax implications and the like, so I'm going to come down here and say based on life easily going to be spending 2,500 a year.
If I take that out to the right now, You're putting in 3,800 spending 2,500, you have 1300 left and you repeat that throughout the years and you can see what's going on. You get to a point where you stopped working, you stop contributing. And so the balance stopped growing and in the balance is depleting.
And then it gets so point where there is no balance. So that's more in line with. And so we're going to leave it like that for now. If you find yourself spending all the money in your house savings account, then really probably don't even need this section would just be money coming out of your paycheck.
It's a hundred percent spend and there is no advantage, no money being leftover for future savings. So you wouldn't want to do this. If it's not in line with reality, this seems reasonable for, for our purposes here today. I'll just say. Come up to the top again. And now when I look at my total assets, this reflecting the home, the 401k, and what's left of the health savings account.
So that's it for now. I hope you see how there is limitless possibilities for your future. And depending on. Your hopes and dreams are and what your goals are. There are ways to get there and there's ways to make it happen. A lot of it comes down to how much money you're spending and what you can afford.
So if you take the time to put together a forecast and you really take the time to refine it and make it reflect your reality and adjust some of your goals and what you think you're going to be able to do in the future. I really think I can give you confidence to know if you're on the right track or not.
So I hope you enjoyed putting the forecast together, continue to refine it and test it. I think testing it for accuracy is paramount. There's a lot going on with the spreadsheet. It's, it's easy to put a number in cupping out to the right and not give it any more thought, but the reality is. You need to be careful to make sure that the numbers you're putting in here are reflective of reality.
I would highly recommend to tell your finances, budget spreadsheet, which has detailed tracking of your expenses into categories so that you can create a good budget, but you can't create a good budget unless you know where you're, what you're spending and you can't create a good forecast unless you know what you're spending.
The first step might be buying the budget spreadsheet and getting a handle on where your money is going and building a good budget, and then tackling, putting together this forecast really look forward to feedback and hearing what you have to say and what you learned. And I really hope that this forecasting spreadsheet can help you make wise decisions and help you plan appropriately for your future.
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.