 Personal finance practice problem using one note, home loan comparison. Get ready to get financially fit by practicing personal finance. One note, you're not required to, but if you have access and would like to follow along, we're in the icon on the left-hand side, the practice problems tab in the 7060 home loan comparison tab. Also, take a look at the immersive reader. Our practice problems are also in the text area, same name, same number, but with transcripts, they can be translated into multiple languages and either listened to or read in them. Informations on the left-hand side, we're gonna use that to do calculations on the right-hand side. We got the three loans that we're gonna be doing comparing and contrasting with. We're gonna do a payment calculation for the three of them, amortization tables. We'll break the amortization table out into year-by-year summaries. We'll also discuss the different tools that you could use for the calculations of the amortization tables. For example, which include online tools and we'll compare and contrasting making amortization tables online and doing the payment calculation online with the tools such as Excel and how you might integrate that information into a more comprehensive strategy as you're doing your planning. So when you're doing planning, you might first think, well, what kind of loans could I get and try to figure out the basics of the loan? We might first come up with a loan amount, of course, and we could get the loan amount by either trying to think about how much the home costs, how much down payment I have to put and therefore how much loan amount I would need to get and then try to figure out the payment from there and the standard loan structure, which could be like a 30-year fix would be the standard structure and then we could vary it from that point. Or you might try to think of it from the ground up, try to think about how much revenue or income you have and how much banks are willing to possibly finance in terms of our income strategy, think about how much financing we can get and then how much down payment we have and then how much loan we can get. So those are the two ways you might come up with basically the loan amounts. Once we have the loan amounts, we might compare and contrast different loan amounts and compare and contrast different number of years and then of course the interest rates or something that could vary as well. So we'll do the three comparisons, loan one, two, and this should be three down here for the different loan amounts, the different years here and the different rates. So the first thing we'll do is we'll do a calculation for the amount, so we're gonna say for the payment amount. So there we got the 168,000, the number of years, we're gonna say are the 15 years and then the rate is going to be that 6.5 so we can do a payment calculation. Now you could use Excel to do the payment calculation or you might use one of these online tools. Notice this is rounded to the nearest dollar. So I could use the online tool, I could go to the 168,000 for example and jump over here online, say 168,000. I'm not promoting this particular tool, there's a lot of different loan calculators that have different pros and cons to them but if you type in loan calculator into your browser you can find some kind of loan calculation tool and then I'm gonna say this is 15 years and we'll say the rate of interest, what did we say the interest rate was 6.5, 6.5 and then we're gonna say it's a monthly and every month let's do the calculation and so we get to the 1463 about and then 46 cents, so we'll say 1463 because we rounded it over here and so there's our calculation. We can also do it with Excel, I highly recommend using Excel because note that you could every time you make a change just input the data back into this calculator, you can also copy right click and have duplicate tabs so you can go back and forth between the two tabs but if you have this information into something like Excel you can populate the data on the left hand side and change the data much more easily in my opinion and it's pretty easy once you get the Excel formulas down looks a little bit tricky but you got this little tool down here we do do this in Excel if you wanna practice doing it in Excel for practice. So I put the negative up front that'll make it a positive number and the end result that's how we got this calculation and then brackets the rate would be the 6.5% so that's this cell right here divided by 12 because notice whenever we use the rates here these are yearly rates we don't talk about monthly rates because the rates are typically small if we do that so we talk about yearly rates but then because it's a monthly kind of component we got to divide it by 12 to get it to the months the number of periods is gonna be then the 15 now that is in years we have to have that in months too 15 years times 12 will give us the number of months and then comma and then finally we have the present value which is the starting point of the lowing loan which is the 168,000 then if we go to loan number two and do a similar calculation this one is the 223,000 it's a 30 year loan so we're going to the standard 30 these are the two most common you would think loan years 15 or 30 a 30 might be the more standard and then 15 and then we're gonna say that that's gonna calculate to the 1484 I could put that into my loan calculator and again I might copy so I got two tabs here and I can toggle back and forth which is kind of nice I could do that I could say this is gonna be 223 223 and this is 30 year and the rate for that one is 7% 7% calculated out and we get about a 1484 about if I round it up so I'm gonna say okay that's good 1484 that's about what we have here because I rounded it we can also do that in Excel which I recommend doing we do do this in Excel which would be equals negative I put the negative up front because I think it's the easiest thing to type that way payment and then brackets and we're gonna say the rate now is gonna be the 7% but that's a yearly rate so we divide it by 12 and then comma the number of periods is gonna be 30 but that's in years we need months so we multiply it times 12 and then comma the present value is the 223,000 that's gonna be our 1484 so you can see these end amounts here are fairly fairly close even though we've got this difference in terms of the loan amount in the years and the rate so it's really kind of nice to have these things kind of side by side and you can run different scenarios and see change each one of these factors such as the numbers of years and the rate and the loan balance one by one so you can get a feel so for what's the impact on the payments but that's not the only thing we wanna get a feel for we also wanna get a feel for what's the impact on the interest and the principal components as well because those will have an impact on the taxes that we're paying possibly and the amount of equity in the home because the more we pay down on the loan the bigger the difference between the loan value and the home value which is the equity that's our net asset value the last one we got the 199,000 we got the number of years is 20 and then we've got the 6.5 on the rate so that's gonna give us the 1484 again very you know we got the same number here even though we've got the different inputs because the loans different and the years and the rate so let's go over here and calculate that one back online again let's make another tab I'm gonna right click and duplicate the tab and say this then is for 199 199 199 000 and the rate then is gonna be 20 or 20 years 20 and then we said that this was 6.5 6.5 on the rate calculate and so that's gonna be then the 1484 about as we saw so we got this over here 1484 so note the tools we can have online we got our three calculators we can put them in separate tabs and do some toggling back and forth to see the differences there but I still think it's easier to do in Excel because in Excel you got these three comparisons right there side by side and if I want to change any of the data on the left hand side it will automatically change this table this table on the right hand side I can then look at my payment calculation which would be negative I put the negative up front and it would be then the rate again which would be the 6.5 that's a yearly rate so we divide it by 12 comma number of periods would be 20 this time that's in years so we multiply it times 12 and then comma the present value this time at the one nine nine a thousand and that gives us the 1484 as well so now we're going to go to the amortization table which also is something that we can do online so I could say okay let's do the amortization table for this first one so I can go over here and say let's check it out online go to that first one and here all I've got to do is press this view the amortization table and boom they give us an amortization table on down below which is nice but again it's kind of static I can change the data up top but I can't really do cell references and I can't summarize the data on a year by year basis which we really kind of want to do as easily some online tools might do that a little bit more clearly but in Excel if we get good at the Excel we can do that quite easily so once we'll do the amortization table and we'll look at it and then we'll break it out on a year by year basis this year by year basis breakout is something that's a lot nicer to use when you're trying to figure things like your yearly tax impact and when you're trying to figure things like the effect on the equity on a yearly basis not so much on a monthly basis now of course this will be a long table because we'd have to calculate this down for on a month by month basis this first one was for 15 years so it's going to have a lot of components but we'll just do a couple calculations up top and then in Excel you can just copy the formulas down so although it looks quite tedious it's pretty easy to do so we've got the 168th that's where we would start we've got then the first payment and we calculated the payments to be this 1464 that is a rounded number the interest calculation then would be the prior balance 168,000 times the rate which is 0.065 6.5% that would be the interest for a year then we divide it by 12 because we're talking about months that's where we're getting let's do that one more time because I messed up 168,000 this is going to be times 0.065 divided by 12 so there's the 910 we could also do it this way we could say let's take the monthly rate 0.065 divided by 12 and there's the monthly rate which is quite small that's why we don't talk about monthly rates but I could do that first and then multiply it times the amount of the 168,000 and we get that 910 again so then we could subtract this out so if this is the payment and that's the interest 1463 minus the 910 gives us the 553 now remember the payment here we're trying to keep that constant and therefore the payment for that the cost of having of doing that that convenience is there's going to be a change in the interest and principal from period to period so this will not remain the same and that's going to cause us our problems when we try to do projections into the future for things like taxes and the amount of equity that's going to be remaining on the loan the interest is like the rent on the money so we're borrowing the money it's not free we got to pay interest on it just like if we were borrowing like renting a place or something like that and the rent just goes away we're just renting for the use of the money and then the loan amount the loan decrease is the amount that's going to decrease the loan balance so if I'm paying 1463 minus the rent on the money 910 the loan balance is going down by the 553 that therefore if my prior balance was 168,000 minus the 553 you got to decrease to the 167447 that's the new loan balance you might call this just the balance or the principal and then the decrease in the principal or other terms you might use and then we do the same thing but notice it's slightly different here same payment, slightly different breakout between the interest and the loan that's going to cause us problems when we're trying to estimate out into the future this calculation looks something like this 167447 this number here divided by we're going to say the, I mean sorry, multiply times the 6.5, 0.065 that's for a year divided by 12 to get a month and so we've got the 907 so there's the 907 so it's a slightly different because we took a smaller loan balance to calculate it so if I take this 1463 minus the 907 that gives us our 556 with the loan decrease and if we take our prior loan balance 167447 minus the 556 we get to the 166890 now if I do this all the way down you'll note that there's same payment slightly difference in the interest and the loan the interest going down each time because the rent on the money is lowering given the fact that we're lowering the loan balance or the principal if the payment stays the same and the rent on the money is going down that means the loan decreases going up meaning the loan is going down at an increasing rate right so you can see this trend going down here the interest of course then is going down and down each month the loan is going up and as we go we're gonna do this all the way down if we look at this all the way down then at the bottom of this table and this looks quite tedious but again you could just copy it all the way down if you did this in Excel we get down to zero on down below at that last payment and here's the breakout there's the payment there's the interest there's the principal a lot different down here that has a lot different impact on our tax calculation and our equity calculation on a payment by payment basis but we don't typically wanna see it on a payment by payment basis we typically wanna see it on a year by year basis so if I compared it to a year by year basis I could take this data on the left hand side everything with a one over here is in year one so here's all the year ones down to 12 months and here's all the year twos that go down to 24 months and so if I can summarize that data meaning taking all the payments and putting them in a year by year taking all the interest in a year by year and so on it would look something like this so in year one we'd have actually this is another amortization table let me move over here I'm gonna move over to the right so it's gonna be over here and so now we've got year one is at the 17, 562 now that number is fairly easy to get because I could take I could take this amount I could take this amount and multiply it times 12 1463 times 12 about that number's rounded so it's not gonna be exact but we get about the 17, 560, 62 it's again it's rounded so it's not exact but that number fairly easy the interest we can't do that though because the interest changes each time we make a payment so we actually have to add up the interest in the amortization table that's something with our online tool we cannot do as well you might have some tools they can get you more detail but it's something that most tools don't do as well they give you the amortization table and the payment amount and then that's basically it in Excel you can start to pull a whole lot more data from this information so that's gonna give us then that and same with the loan decrease the loan decrease over here is also something that I'd have to add them all up for the entire year to get the proper amount the loan balance is the amount that I want at the end of the period 161, 159 that's this number at the end of the first year 161, 157 and that will help us to see where we stand or where we will stand at the end of year one so this is a really nice table to have because then I can start to think about okay how much am I gonna pay on a yearly basis and then I can think about what's gonna be my interest which I can use for my tax calculations on a yearly basis and I can start to get a more in depth kind of scenario or projection that way and I can look at my loan decrease which will help me to decide what the impact on the equity is the difference between the loan balance and the home value which has two factors we can consider and one is gonna be well the loan itself as the loan goes down that's this factor that as the principal goes down then there's gonna be a bigger or widening gap even if the value of the home remains the same and the other is we're hoping the value of the home goes up so if the value of the home goes up then that would also increase the equity so then year two the payments are the same notice the interest will be different and the loan balance will be different this interest is gonna be significant if we're gonna do tax calculations so we wanna make sure that we're taken into consideration there are significant differences in the interest payments and the loan decrease even though the payment amount is the same which is gonna have a significant impact on our tax calculations as well as where we stand in terms of equity value in the home we could do the same thing of course for the other loans here so if I did the amortization table for the second loan so here's the second one the 223 we could do that with an online tool so here's the 223 I could just say amortization table boom but I can't really do anything else with this I could still toggle back and forth compare and contrast but I can't really make it into a year by year breakout or like use any formulas to tie to it or anything like that as we could with Excel and Excel we could build the amortization table fairly easily and we could do it by basically here's the 233 I'll just do one calculation on the loan amount seven percent so it's gonna be then this 223 000 times 0.07 and then we'll say divided by 12 so there's gonna be the interest of 1301 about so if I took the 1484 minus the 1301 we get about 183 loan decrease if I took the 223 000 minus the 183 we get about 222 817 if we do that all the way down you can see this difference between the interest and the loan decrease all the way down and this one goes all the way down to the 30 years and after 30 years it'll get down to zero it's a tedious calculation if you were to look at this it would make your it obviously makes our eyes roll over to look at those many numbers and it's hard for us to use projections on it it would be more useful for us to break that down into a year by year summary which would look something like this if we broke that out on a year by year so now we've got the 17807 that first number we can get to fairly easily by taking this 1484 times 12 17808 about rounding and so there we have that but the interest and the loan balance those are gonna change on a year by year so then we can we could take a look at the differences from year to year as we can see here and we can break this out on a year by year breakout and we can then do some comparing and contrasting to this loan versus the prior loan on a year by year breakout it's going down to 30 years instead of the 15 years which can cause us a little bit of confusing but it's a lot more easy for us to consider this compared to this for the two loans then comparing the two amortization tables we could do the last one as well so that's the 119, 20s years we're gonna say okay let's check that one out that's the third one there's the 119 I can make an amortization table with our online calculator like that we can also go over here we did it in Excel we can make our amortization table so here's our amortization table 199 we would do the same calculations and you could see the full amortization table this is going down 20 years now 20 years there is that and then of course we would like to break it out on possibly a year by year breakout which would look something like this so now we've broken it out year by year here's the payment, here's the interest loan decrease and the loan balance this is a lot easier to compare to this than the two amortization tables are to do the comparison it's a lot easier to do this in Excel now in Excel you could do this with formulas or you can use pivot tables so here's the same thing with a pivot table type of calculation we did here and so you could use the pivot tables we do do that in the practice problem so if you're interested in looking at this and how to calculate these in Excel which I think is the best way to go if you're really trying to dig into the numbers then take a look at those practice problems and we'll build these amortization tables I'll show you a couple of ways to break that out into a year by year summary