|
|
|
Forecasting Spreadsheet
Price: $49.99
For Excel 2010, '13, '16, '19, Microsoft 365 (including 365 for MAC) |
|
|
|
| ||
adding investments video transcript:
In this video, we're going to go over adding an investment. As you're aware, there's many types of investments. Try to create a section here that has the flexibility to account for the variety of things that you can do with an investment. You can contribute more to it, withdraw money from it. Change what your return on investment is, how much interest you think you're going to earn each year section for putting in your tax liability.
Obviously, it's hard to account for all the myriad of investments that are out there. And also when you're predicting the future, what you put in as your return on investment is going to have a huge impact on it. There's some very important notes over here that talk about adding an investment and what each of these things mean.
And in red is saying don't overestimate what your return on investment is going to be, because it could give you a very false picture of your future. And there's many things that impact, uh, your actual rate of return that go way beyond the scope of this spreadsheet. Okay. The first step to adding an investment is clicking the button up top here.
It says, add investment. When that comes up and adds it down at the bottom here, you can then name it, whatever you need to name it. So let's put a 401k in here and just walk through how to use it. So we're going to put in, do you have a beginning balance of $140,000 in your 401k and you might not have that much.
And a lot of people don't and you might have a lot more. And, uh, you know, this is just for purposes of an example, hopefully through going through this exercise will help you determine if you need to save more so that you have enough money for future retirement. So you put in that beginning balance there, and then what you're going to be putting in here as a first step is what do you plan on contribute each year?
So if you are going to put in 10% of your income, 10% of gross, you would type 10%. And again, you would want to copy that to the right now when you hit retirement. So you come out here to where you put your R's because you had zeroed out your grossing. There's no calculation being thrown in here as a contribution.
So that's working out well, we don't have to worry about that. And as you come down through here, like I said, putting in your return on investment is a huge factor in what your future picture looks like. So you do not want to be more too aggressive with this percentage because. It could really paint a rosy picture, but getting a 10% return each year.
Isn't real, real likely. And could maybe cause you to think that you're, you're doing better than you really are. So just to give you a quick example, I put four and a half percent in here, you know, starting at 140,000 and contributing this amount each year, 10% of the gross pay we had up there, which was 78,000.
You can see. By the time we get to 65, there's almost $800,000 in a retirement savings. Now watch what happens when I put in 10% more, got a copy of that to the right, suddenly that went up to 2.4 million. So that, that shows you the impact of this return on investment and how important. Because each year builds off the previous year.
And, you know, even if you have a 10% return, just one point in time, so let's just show you this. I'll put four and a half percent in here and copy it to the right. And let's just say right here, we had a 10% return, two years in a row. So, and then went back to four and a half percent. It put us up to the 860,000.
Now I'm going to put this back down. Is 812,000. So, uh, just two years of 10% early on made a big difference where you ended up
now, you looking at this and saying, why is it showing $2 million here for our total of return on investment? If you come out here to the right, you can see that it's carrying the hallway on the hallway to 50 years. So we have this going up to age 90. So if you. Just want to see where you end up by the time you retire.
I would suggest that you make a forecast that is just for your working years and then a second one that's for your retirement years, because it's very difficult to track what your expenses are going to be and all the different things on retirement years. And some of these numbers are hard to look at because you have half.
The time is a lot of income and half the time was less income. Okay. So coming back to the 401k, we have our conservative four and a half percent return on investment, which that is not even a given in certain years, but that's what we're going to use for the spreadsheet. And since it's a 401k, I don't have any tax liability in these years.
Uh, until I would start withdrawing the money. So that's very important that you know, what the tax implications are of your invest of your investments. If this was not a 401k and it was just a normal investment and you had investment returns and you sold your investments, you would have a tax liability.
And that's where you could put in a percent, you can calculate a percentage of your withdrawal. So you could take. This is withdrawals, and you could do times, whatever you think your percentage is going to be. And you could add that in there. So let's just say, um, I was going to withdraw 35,000.
Now you can see that that increase, that I need to make it a, a negative there. So you need to keep in mind if it's going up or down and let's put this in a. 20% and it's already capped. You don't have to type a formula in there. You put in 20% calculates the tax liability off that withdrawal amount. And then it's taking that withdraw and the tax liability out of your principal balance.
So at $35,000 withdrawal brought my balance from, we started at one 40. We added 7,800 subtracted, 35,000 had 7,000, our tax liability. Now we're at one 10. You have the ability there also to put in an actual dollar amount for your tax liability. So put in a 12,000, again, needs to be a negative, and I can change this to the format.
So it's not percentage. So you talk to your tax accountant. He says, you're going to owe 12,000 in taxes. You can put that in here and now you can see your new, new balance. So that feature is in there to enter withdrawal. And up here, you can enter contributions. If it's not always going to be a percentage of your gross pay, you can actually type in the dollar amount up here.
So you can put a percentage which would calculate a, of your gross, or you can put a dollar amount again, you just change that format. And now it's adding that in. She had full flexibility year to year to put in how much you think you'll be contributing, what your return on investment will be and what your tax liability may or may not.
When you get to retirement years, you stop contributing. And then here's where you can start putting in what you think you're going to be using each year. So if you think you're gonna use 32,000 a year to live off of, you can put that in here. Copy that to the right. So you saved up to $8,000 also at that point in time, uh, my oh, some taxes, depending on what tax bracket you're on.
And you can put that in there. Copy of that.
And again,
I'll put that in the right way. There we go. So the tax liability should be reducing your principal and you can see that it's doing that and there's no contributions going on. Are 800,000, you're taking 32,000 out a year and you're paying taxes on it. And you can see your balances is decreasing at the same time.
You're saying you're still getting four and a half percent return. So that's, what's helping offset the money that you're you're taking out is you're getting, you're still getting return on your investment. And since you have 800,000 in there and you're getting four and a half percent return, your return on your investment is still outpacing the amount you're withdrawing.
If you put this to a higher. That outpaced your return. Now you're going to see the balance, you know, a decline. So it's going from, um, the whole way down. And now you're at zero by the time you're 80. So you can see, uh, the flexibility you have there to calculate your future and what kind of money you think you're going to need to live off of what you think your tax liabilities are going to be.
And what I would say is you definitely take your best guess and then maybe sit down with your tax accountant or your financial advisor, have them look at it, see what they think. It's definitely a good exercise. Uh, you know, what kind of money do you think you'll need in retirement and how much money do you think you need to have?
If you do this exercise and you feel like you're going to need a million and a half, by the time you retire, when now you know that you need to start contributing more each year. So you could come in here, increase the percentage. You can increase the return on investment. But like I said, that's not a guarantee.
These things are feeding into the top. This is an asset, but you don't necessarily have access to. Uh, depends if it's a 401k, uh, so you wouldn't want to put, you know, a hundred percent access to this. So that's what this is doing down here. It says available savings and it's allows you to enter a percentage.
You could withdraw some money, but it would be at a penalty. So I would put this at zero at this point. And then when you get to retirement age, you can put this at a hundred. And then you can copy that to the right. So it shows you how much money is available. And the point of this available savings is if you come up here to the top, if you remember me talking in the overview video, you have a negative, you might have a negative cash position for a certain year, and you want to see if you have coverage and what gives you coverages, what you have in available collateral available, saving.
And, uh, worst case scenario, what you have available on secure credit, which would be credit cards. So that's how you had an investment. I realize there is many types of investments, and I think as you work through this, you'll see that you have lots of flexibility to adjust a return on investment contributions, withdrawals, and tax liabilities.
You can just type the exact amount if you don't know what the percentage is going to be. Once we get these different elements in. We're going to go to the top. You're going to see how this all layers together to give you full financial forecast and give you a complete picture of what the future looks like based off the known elements you have right now.
Thank you.
Click here to watch more videos : https://www.xlyourfinances.com/videos.html
Obviously, it's hard to account for all the myriad of investments that are out there. And also when you're predicting the future, what you put in as your return on investment is going to have a huge impact on it. There's some very important notes over here that talk about adding an investment and what each of these things mean.
And in red is saying don't overestimate what your return on investment is going to be, because it could give you a very false picture of your future. And there's many things that impact, uh, your actual rate of return that go way beyond the scope of this spreadsheet. Okay. The first step to adding an investment is clicking the button up top here.
It says, add investment. When that comes up and adds it down at the bottom here, you can then name it, whatever you need to name it. So let's put a 401k in here and just walk through how to use it. So we're going to put in, do you have a beginning balance of $140,000 in your 401k and you might not have that much.
And a lot of people don't and you might have a lot more. And, uh, you know, this is just for purposes of an example, hopefully through going through this exercise will help you determine if you need to save more so that you have enough money for future retirement. So you put in that beginning balance there, and then what you're going to be putting in here as a first step is what do you plan on contribute each year?
So if you are going to put in 10% of your income, 10% of gross, you would type 10%. And again, you would want to copy that to the right now when you hit retirement. So you come out here to where you put your R's because you had zeroed out your grossing. There's no calculation being thrown in here as a contribution.
So that's working out well, we don't have to worry about that. And as you come down through here, like I said, putting in your return on investment is a huge factor in what your future picture looks like. So you do not want to be more too aggressive with this percentage because. It could really paint a rosy picture, but getting a 10% return each year.
Isn't real, real likely. And could maybe cause you to think that you're, you're doing better than you really are. So just to give you a quick example, I put four and a half percent in here, you know, starting at 140,000 and contributing this amount each year, 10% of the gross pay we had up there, which was 78,000.
You can see. By the time we get to 65, there's almost $800,000 in a retirement savings. Now watch what happens when I put in 10% more, got a copy of that to the right, suddenly that went up to 2.4 million. So that, that shows you the impact of this return on investment and how important. Because each year builds off the previous year.
And, you know, even if you have a 10% return, just one point in time, so let's just show you this. I'll put four and a half percent in here and copy it to the right. And let's just say right here, we had a 10% return, two years in a row. So, and then went back to four and a half percent. It put us up to the 860,000.
Now I'm going to put this back down. Is 812,000. So, uh, just two years of 10% early on made a big difference where you ended up
now, you looking at this and saying, why is it showing $2 million here for our total of return on investment? If you come out here to the right, you can see that it's carrying the hallway on the hallway to 50 years. So we have this going up to age 90. So if you. Just want to see where you end up by the time you retire.
I would suggest that you make a forecast that is just for your working years and then a second one that's for your retirement years, because it's very difficult to track what your expenses are going to be and all the different things on retirement years. And some of these numbers are hard to look at because you have half.
The time is a lot of income and half the time was less income. Okay. So coming back to the 401k, we have our conservative four and a half percent return on investment, which that is not even a given in certain years, but that's what we're going to use for the spreadsheet. And since it's a 401k, I don't have any tax liability in these years.
Uh, until I would start withdrawing the money. So that's very important that you know, what the tax implications are of your invest of your investments. If this was not a 401k and it was just a normal investment and you had investment returns and you sold your investments, you would have a tax liability.
And that's where you could put in a percent, you can calculate a percentage of your withdrawal. So you could take. This is withdrawals, and you could do times, whatever you think your percentage is going to be. And you could add that in there. So let's just say, um, I was going to withdraw 35,000.
Now you can see that that increase, that I need to make it a, a negative there. So you need to keep in mind if it's going up or down and let's put this in a. 20% and it's already capped. You don't have to type a formula in there. You put in 20% calculates the tax liability off that withdrawal amount. And then it's taking that withdraw and the tax liability out of your principal balance.
So at $35,000 withdrawal brought my balance from, we started at one 40. We added 7,800 subtracted, 35,000 had 7,000, our tax liability. Now we're at one 10. You have the ability there also to put in an actual dollar amount for your tax liability. So put in a 12,000, again, needs to be a negative, and I can change this to the format.
So it's not percentage. So you talk to your tax accountant. He says, you're going to owe 12,000 in taxes. You can put that in here and now you can see your new, new balance. So that feature is in there to enter withdrawal. And up here, you can enter contributions. If it's not always going to be a percentage of your gross pay, you can actually type in the dollar amount up here.
So you can put a percentage which would calculate a, of your gross, or you can put a dollar amount again, you just change that format. And now it's adding that in. She had full flexibility year to year to put in how much you think you'll be contributing, what your return on investment will be and what your tax liability may or may not.
When you get to retirement years, you stop contributing. And then here's where you can start putting in what you think you're going to be using each year. So if you think you're gonna use 32,000 a year to live off of, you can put that in here. Copy that to the right. So you saved up to $8,000 also at that point in time, uh, my oh, some taxes, depending on what tax bracket you're on.
And you can put that in there. Copy of that.
And again,
I'll put that in the right way. There we go. So the tax liability should be reducing your principal and you can see that it's doing that and there's no contributions going on. Are 800,000, you're taking 32,000 out a year and you're paying taxes on it. And you can see your balances is decreasing at the same time.
You're saying you're still getting four and a half percent return. So that's, what's helping offset the money that you're you're taking out is you're getting, you're still getting return on your investment. And since you have 800,000 in there and you're getting four and a half percent return, your return on your investment is still outpacing the amount you're withdrawing.
If you put this to a higher. That outpaced your return. Now you're going to see the balance, you know, a decline. So it's going from, um, the whole way down. And now you're at zero by the time you're 80. So you can see, uh, the flexibility you have there to calculate your future and what kind of money you think you're going to need to live off of what you think your tax liabilities are going to be.
And what I would say is you definitely take your best guess and then maybe sit down with your tax accountant or your financial advisor, have them look at it, see what they think. It's definitely a good exercise. Uh, you know, what kind of money do you think you'll need in retirement and how much money do you think you need to have?
If you do this exercise and you feel like you're going to need a million and a half, by the time you retire, when now you know that you need to start contributing more each year. So you could come in here, increase the percentage. You can increase the return on investment. But like I said, that's not a guarantee.
These things are feeding into the top. This is an asset, but you don't necessarily have access to. Uh, depends if it's a 401k, uh, so you wouldn't want to put, you know, a hundred percent access to this. So that's what this is doing down here. It says available savings and it's allows you to enter a percentage.
You could withdraw some money, but it would be at a penalty. So I would put this at zero at this point. And then when you get to retirement age, you can put this at a hundred. And then you can copy that to the right. So it shows you how much money is available. And the point of this available savings is if you come up here to the top, if you remember me talking in the overview video, you have a negative, you might have a negative cash position for a certain year, and you want to see if you have coverage and what gives you coverages, what you have in available collateral available, saving.
And, uh, worst case scenario, what you have available on secure credit, which would be credit cards. So that's how you had an investment. I realize there is many types of investments, and I think as you work through this, you'll see that you have lots of flexibility to adjust a return on investment contributions, withdrawals, and tax liabilities.
You can just type the exact amount if you don't know what the percentage is going to be. Once we get these different elements in. We're going to go to the top. You're going to see how this all layers together to give you full financial forecast and give you a complete picture of what the future looks like based off the known elements you have right now.
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.