|
|
|
Forecasting Spreadsheet
Price: $49.99
For Excel 2010, '13, '16, '19, Microsoft 365 (including 365 for MAC)
| ||||||
adding a loan with asset video transcript:
Okay. In this video, we're going to go over adding an asset with a loan. The two most common would be your house, which has a mortgage, most likely, or a car with a car loan. So let's get started. We're going to add a house. And in this example, so an asset with a loan and type in here, home mortgage,
let's say you have a 30 year loan and. You put in your beginning value. So it's going to show you two ways. One is you acquired this thing years ago and you at a set point in time, another one is you're going to, it's something that you're planning on acquiring in the future. So right now the first step we're going to go through, you have acquired it years ago.
So let's say the loan beginning balance was two 15 at that point in time and you acquired it back in 2008.
The loan beginning began in that year. And the first payment was in that year up here, you can put in what your interest rate has been. So if you had a fixed rate for no 3.5 and the cell here is to calculate, if you think it's appreciating or depreciating. So if you bought it for 2 25 and you think it appreciated 15,000 in those 10 years, You can type those in the default is a percentage.
So you see how that percent is there. I'm going to hit delete to get rid of that percentage and hit enter. Now it still shows it as a percentage. I'm gonna come up here and hit a comma. And I don't like seeing sense on a when you're estimating. So I'll get rid of the sense. So now we're up at 240,000. Now, since we've had the loan for several years, uh, It's a balance right now you can look at your mortgage statement.
Um, another thing I just want to show you real quick. If you come into this loan tab, you can put the same elements in here, and let's say it's a 30 year loan. So it it's a praise that two 40 at this time. And this might help you. Um, if you started it in 2008, you could actually put January. And what this would do is you're you're in beginning of 2019, you can look here and you can say, okay, the balance you amortized loan over 30 years, starting at 225,000.
Actually that was the value of the house. I think the loan was 215,000, put that in there three and a half percent interest rate. 30 years come down to 2019. Okay. We're at a hundred and sixty six fifteen. I'm going to put that in here. So that's my remaining loan balance
and the house appreciated 15,000. Over that period of time, what is it appreciating each individual? Um, I'm going to put 0.0033. So that's one third of 1%. I'm trying to be conservative. You don't want to put, you put a large appreciation in here. You're going to be saying that your house, I want to copy this.
Right. And just show you, you're going to be dramatically increasing the value of your house far beyond what it's going to be. So be very careful when you put in. How much you think your house is appreciating, you know, in some markets it might actually be depreciating. And, um, you know, so if, if you're not sure you might want to just avoid this altogether and not put anything in there, but I think a third of 1% over time is probably, um, conservative and okay.
For our purposes here. So. Tracking what the house is worth, and you'll see why that's important later. Here's your loan decreasing over time, based on these payments that you're making. So when you look at this, you're like, why is there only interest on this one year and not on these other years, this interest rate needs to be copied over to the right.
It's very important on this section because each loan payment and interest rate is. Individually. And the reason it's set up that way is because some people have a variable rate and if interest rates are going up or down, they need to go to change it. Um, from year to year changes, amount of interest they're paying, it changes what their mortgage payments are that they're paying.
Everything is changing, uh, based on that. I want this payment to be higher or lower. You have to change this loan term over here. So I just change it from 30 down to 20 and you can see it jumped dramatically. If I put this back to 30, now you can see what's going on here. Now you're saying it's a 30 year loan.
Why is it being paid off here in 19 years? Or remember we said the loan began in 2008 and that there is only 160,000 left. The, uh, Is is being calculated off this loan beginning balance. So that's something important to keep in mind. So let's just go through each element here. So you have what the, each year you now have a measure of what the house is worth.
Um, what's the balance of the loan, the payments you're gonna make that year, the interest you're going to pay on that and what the remaining liability is. That's a home equity line of. You might want to put in here additional borrowings if you're borrowing money from it and additional payments, if you're paying it down.
So my formula is subtracting this. So this needs to be put in as a positive amount. So if you're borrowing additional monies, you put that in as a positive. If you're making the principal payments, you put that in as a positive as well. So just need to clarify that there it's important as you use the spreadsheet that you pay attention to.
What's that. So what made me pause there is I put, uh, what I thought was an additional payment. My balance went up, so put additional payment in here, interest rates at 58,000 and we're paying it off in 2037. So you can see the impact of, of making an additional payment there. So let's say you plan to acquire this asset, um, with alone in the future, you can do that here by putting.
Acquired dear. So let's say we think we're combined in 2025 or saving for that the loan would begin in that year and the first payment would it begin in that year? Did you put all those things together? And, um, we don't need any appreciation at that point. So we believe the loan beginning bounces me to 15.
We can type that in here.
W w we haven't gotten to yet, but what you'll see is, as you go through each year into the future, you can see the impact of, Hey, we made, we acquired this asset towards 225,000. We owe 210,000. We made $11,000 of loan payments, and we have a liability of 210,000. And our equity is 14. And if you need to build a borrow against that equity, you can put, you know, what percent of that you think you'll want to build a tap into.
So let's say you want to borrow 20% of that. You can type a percentage down here and it will show this as available equity to cover expenses. So that's how you add an asset with a loan. It could be anything, it could be a car. You know, if you put a car in here instead of appreciating, you might want to be depreciating it.
So. This is do this quickly. You put a $20,000 car in and we're going to start it the sheer.
And we'll just keep that three and a half percent in there.
Uh, car loans, probably five-year term. Uh, the loan beginning balance is 20,000. Let's say we had a trade in 18,000, they gave you 2000 for it. So your loan beginning balance is 18,000 now. Like why does it still say home mortgage payments? Well, we changed this to a car, so it's called car with loan, which is called car loan.
All right. And you're saying, why is 196,000? Well, our remaining loan balance was. Not updated yet. There we go. So, and we're not going to borrow against the car loan so we can just zero out that there's. So here we go. We have $20,000 asset, $18,000 loan made nearly $4,000 in car payments and we paid $572 of interest.
Do you think that car is worth 20,000 the second year? Well, depending on how much you're driving it, car can depreciate pretty rapidly. A car is not appreciating in value. So what I would do up here is reduce, um, what it's worth by a percentage each year. So if you think it's going down 5% each year, you would put that in there, copy it to the right.
And that might, you know, for a car that actually, depending on how many miles you're putting on it, that might not be. Aggressive enough. So if we said it's reducing 15% a year, maybe, maybe that's more reasonable and, uh, you would just carry that out. You know, at some point you might say that, you know, the cars is not worth anything.
So at that point you can just put in an exact dollar amount to zero that out. So I saw it had so many delete this. It had 54 50 left. And then you can just say minus 54, 50 cars, not worth anything changes to a comma. So at some point, reducing a 15% each year, and then you get to a point where you say, Hey, this car is 10 years old.
You know, it's not worth anything. I want this off my calculation. I just put a minus the whole dollar amount. And, uh, it zeroes out the value of that car. So that when I put a percentage in, it changes that asset value that year. So it went from 20,000 down to 17. If you wanted this first year to be reduced, it actually doesn't allow you to do it that first year.
Um, even though it's, it's doing that calculation, you'd actually have to just reduce it right here and say, it's already gotten reduced to 19,000. So. One of the thing I want to point out. It went to 51, 77, even though I have a greater amount, I have 54 50, it didn't go negative, which is a nice feature.
There is no such thing as a negative asset. So now that we have this setup through this period of time, you have your asset value, which is up here. You have your liabilities, what you owed on that, and then you have your. So that's what an asset with the loan does. I showed you the house with a mortgage and a car with a loan.
Again, if you wanted to acquire in the future, you have that ability. You say this is in 2025. That's when the loan will begin and pretty much everything can, can be the same there. And you can see no impact through these years. 2025 and we're going to buy it. And now you can see, uh, what the impact is going to be.
All right. Next, we're going to go over how to add an investment.
Click here to watch more videos : https://www.xlyourfinances.com/videos.html
let's say you have a 30 year loan and. You put in your beginning value. So it's going to show you two ways. One is you acquired this thing years ago and you at a set point in time, another one is you're going to, it's something that you're planning on acquiring in the future. So right now the first step we're going to go through, you have acquired it years ago.
So let's say the loan beginning balance was two 15 at that point in time and you acquired it back in 2008.
The loan beginning began in that year. And the first payment was in that year up here, you can put in what your interest rate has been. So if you had a fixed rate for no 3.5 and the cell here is to calculate, if you think it's appreciating or depreciating. So if you bought it for 2 25 and you think it appreciated 15,000 in those 10 years, You can type those in the default is a percentage.
So you see how that percent is there. I'm going to hit delete to get rid of that percentage and hit enter. Now it still shows it as a percentage. I'm gonna come up here and hit a comma. And I don't like seeing sense on a when you're estimating. So I'll get rid of the sense. So now we're up at 240,000. Now, since we've had the loan for several years, uh, It's a balance right now you can look at your mortgage statement.
Um, another thing I just want to show you real quick. If you come into this loan tab, you can put the same elements in here, and let's say it's a 30 year loan. So it it's a praise that two 40 at this time. And this might help you. Um, if you started it in 2008, you could actually put January. And what this would do is you're you're in beginning of 2019, you can look here and you can say, okay, the balance you amortized loan over 30 years, starting at 225,000.
Actually that was the value of the house. I think the loan was 215,000, put that in there three and a half percent interest rate. 30 years come down to 2019. Okay. We're at a hundred and sixty six fifteen. I'm going to put that in here. So that's my remaining loan balance
and the house appreciated 15,000. Over that period of time, what is it appreciating each individual? Um, I'm going to put 0.0033. So that's one third of 1%. I'm trying to be conservative. You don't want to put, you put a large appreciation in here. You're going to be saying that your house, I want to copy this.
Right. And just show you, you're going to be dramatically increasing the value of your house far beyond what it's going to be. So be very careful when you put in. How much you think your house is appreciating, you know, in some markets it might actually be depreciating. And, um, you know, so if, if you're not sure you might want to just avoid this altogether and not put anything in there, but I think a third of 1% over time is probably, um, conservative and okay.
For our purposes here. So. Tracking what the house is worth, and you'll see why that's important later. Here's your loan decreasing over time, based on these payments that you're making. So when you look at this, you're like, why is there only interest on this one year and not on these other years, this interest rate needs to be copied over to the right.
It's very important on this section because each loan payment and interest rate is. Individually. And the reason it's set up that way is because some people have a variable rate and if interest rates are going up or down, they need to go to change it. Um, from year to year changes, amount of interest they're paying, it changes what their mortgage payments are that they're paying.
Everything is changing, uh, based on that. I want this payment to be higher or lower. You have to change this loan term over here. So I just change it from 30 down to 20 and you can see it jumped dramatically. If I put this back to 30, now you can see what's going on here. Now you're saying it's a 30 year loan.
Why is it being paid off here in 19 years? Or remember we said the loan began in 2008 and that there is only 160,000 left. The, uh, Is is being calculated off this loan beginning balance. So that's something important to keep in mind. So let's just go through each element here. So you have what the, each year you now have a measure of what the house is worth.
Um, what's the balance of the loan, the payments you're gonna make that year, the interest you're going to pay on that and what the remaining liability is. That's a home equity line of. You might want to put in here additional borrowings if you're borrowing money from it and additional payments, if you're paying it down.
So my formula is subtracting this. So this needs to be put in as a positive amount. So if you're borrowing additional monies, you put that in as a positive. If you're making the principal payments, you put that in as a positive as well. So just need to clarify that there it's important as you use the spreadsheet that you pay attention to.
What's that. So what made me pause there is I put, uh, what I thought was an additional payment. My balance went up, so put additional payment in here, interest rates at 58,000 and we're paying it off in 2037. So you can see the impact of, of making an additional payment there. So let's say you plan to acquire this asset, um, with alone in the future, you can do that here by putting.
Acquired dear. So let's say we think we're combined in 2025 or saving for that the loan would begin in that year and the first payment would it begin in that year? Did you put all those things together? And, um, we don't need any appreciation at that point. So we believe the loan beginning bounces me to 15.
We can type that in here.
W w we haven't gotten to yet, but what you'll see is, as you go through each year into the future, you can see the impact of, Hey, we made, we acquired this asset towards 225,000. We owe 210,000. We made $11,000 of loan payments, and we have a liability of 210,000. And our equity is 14. And if you need to build a borrow against that equity, you can put, you know, what percent of that you think you'll want to build a tap into.
So let's say you want to borrow 20% of that. You can type a percentage down here and it will show this as available equity to cover expenses. So that's how you add an asset with a loan. It could be anything, it could be a car. You know, if you put a car in here instead of appreciating, you might want to be depreciating it.
So. This is do this quickly. You put a $20,000 car in and we're going to start it the sheer.
And we'll just keep that three and a half percent in there.
Uh, car loans, probably five-year term. Uh, the loan beginning balance is 20,000. Let's say we had a trade in 18,000, they gave you 2000 for it. So your loan beginning balance is 18,000 now. Like why does it still say home mortgage payments? Well, we changed this to a car, so it's called car with loan, which is called car loan.
All right. And you're saying, why is 196,000? Well, our remaining loan balance was. Not updated yet. There we go. So, and we're not going to borrow against the car loan so we can just zero out that there's. So here we go. We have $20,000 asset, $18,000 loan made nearly $4,000 in car payments and we paid $572 of interest.
Do you think that car is worth 20,000 the second year? Well, depending on how much you're driving it, car can depreciate pretty rapidly. A car is not appreciating in value. So what I would do up here is reduce, um, what it's worth by a percentage each year. So if you think it's going down 5% each year, you would put that in there, copy it to the right.
And that might, you know, for a car that actually, depending on how many miles you're putting on it, that might not be. Aggressive enough. So if we said it's reducing 15% a year, maybe, maybe that's more reasonable and, uh, you would just carry that out. You know, at some point you might say that, you know, the cars is not worth anything.
So at that point you can just put in an exact dollar amount to zero that out. So I saw it had so many delete this. It had 54 50 left. And then you can just say minus 54, 50 cars, not worth anything changes to a comma. So at some point, reducing a 15% each year, and then you get to a point where you say, Hey, this car is 10 years old.
You know, it's not worth anything. I want this off my calculation. I just put a minus the whole dollar amount. And, uh, it zeroes out the value of that car. So that when I put a percentage in, it changes that asset value that year. So it went from 20,000 down to 17. If you wanted this first year to be reduced, it actually doesn't allow you to do it that first year.
Um, even though it's, it's doing that calculation, you'd actually have to just reduce it right here and say, it's already gotten reduced to 19,000. So. One of the thing I want to point out. It went to 51, 77, even though I have a greater amount, I have 54 50, it didn't go negative, which is a nice feature.
There is no such thing as a negative asset. So now that we have this setup through this period of time, you have your asset value, which is up here. You have your liabilities, what you owed on that, and then you have your. So that's what an asset with the loan does. I showed you the house with a mortgage and a car with a loan.
Again, if you wanted to acquire in the future, you have that ability. You say this is in 2025. That's when the loan will begin and pretty much everything can, can be the same there. And you can see no impact through these years. 2025 and we're going to buy it. And now you can see, uh, what the impact is going to be.
All right. Next, we're going to go over how to add an investment.
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.