Create a Computer Bill Format in Excel for Mortgage Effortlessly

Computer bill format in excel for Mortgage

[Music] good morning so one of the things we're going to talk about or the one thing we're going to talk about today is building out a mortgage amortization table from scratch we're going to build this it's basically a model representation we're going to build this in a way that allows us to uh play with the model and then you can build one of these and if you're buying a house you can just kind of play with it do whatever you want okay we're gonna start with the price of the house price of the house uh and the down payment percentage right um and uh as we build this out one of the things i like to do is to plug in numbers let's call let's make it 150 000. let's make it 200 000. things are changing right 200 123 right and that's a dollar sign a pretty spreadsheet is a strong spreadsheet our down payment percentage is going to be 20 where that will shift most people don't put down 20 but i want to take the time to stop here and look at these guys um and say hey look these are inputs right and if you come over here to styles you'll have this input style and this tells every other spreadsheet nerd out there that these are input styles right you can also calculate here the loan amount right so actually you know before we do that let's call this the down payment and instead of the percentage sign let's call it a dollar sign so that's going to be equal to the price of the house times the uh percent down payment right so that's forty thousand dollars that makes sense this is a calculation and so if you go back to your data styles you'll have the calculation and we're going to want to include the loan [Music] balance the loan the original loan i'm going to call it the loan amount the original loan balance that's going to change over time and that guy's going to be equal to obviously the price of the house minus the down payment right again this is a calculation and then we're going to make a calculation when you turn to your friends your colleagues your clients your spouse whatever you can turn around and say hey look only play with the orange ones everything else is going to dance around so you want to see what it costs for a 250 000 house then you just plug it in you want to go 150 123 you plug it in all the calculations and all the other stuff is going to shift please don't mess with that stuff mess only with the stuff in orange right let's go back to 200 000 just for the sake of having it there the other things i need in order to construct the amortization table include the mortgage rate which is a percentage right let's go ahead and put a five percent rate here just whoops five percent rate just to have something and i'm also going to need the years uh and we're going to put in 30 right both of these are inputs both of these are inputs and then you can you know do whatever you know some people might want to keep this like if your ocd is rocking hard you can put this down here at the bottom right uh and delete those two rows that's fine your down payment amount and your dollar sign that's fine you do it whichever way you want doesn't matter as long as we point to the same place right all right the next thing we want to do is we want to calculate the actual loan payments right and we're going to do this we're going to mortgage this out on we're going to immort this out on a monthly basis so let's do a pmt equals pmt open parentheses you're going to end up with these other factors that you can throw in to calculate your payment beginning with the rate which is five years but remember that this is an annual rate and we are going to uh amortize we're going to compound this loan on a monthly basis so i'm going to take my rate and divide it by 12. i'm going to chop it into all the different months and the next thing i do is nper you can see it right here nper number of periods is 30 years again we're doing this monthly so i've got to multiply that one by 12 right comma present value is the loan amount not the price of the house remember we're paying a down payment there's a loan amount that goes with it comma future value the computer is going to assume that the future value is going to be zero you can type it in and finally the type means whether you're compounding at the beginning of the month or at the end of the month the um this calculation assumes that we're compounding at the end of the month so i'm not going to change it there it is end of the period beginning of the period zero or nothing is the end of the period so you can put another zero here but just ignore it all together uh because it's average it's what it's what happens on average usually uh and then i have a calculation for this loan payment you'll notice that this loan payment is negative that makes perfect sense if you're a traditional academic finance person yeah it makes sense because i'm over here it's positive because i'm receiving the money and over here it's negative because i'm paying the money that's great but it's going to mess up our amortization table in a minute so one little cheat that we can do is you can put a little negative sign out here in front of the payment and it'll make it pretty right it'll make it nice okay great but that's not our full loan payment or full monthly payment which also includes a bunch of other stuff like property taxes and it includes maybe hoa for you or maybe it includes uh property insurance let's call that property insurance which is different from pmi or mortgage insurance right mortgage insurance and what else can we put on here i don't know sometimes there's other stuff so let's put other and then i have my total monthly payments right uh let's put in some dollars in here just to i'm in texas so that's going to be a little expensive 400 hoa we don't like hoas so we're going to put zero uh actually that's not true a lot of you're going to have hoa so let's put 100 property insurance down here is fairly cheap let's put 125 pmi 6 and it's going to be 120 other is going to be zero and this guy is finally going to be a summation of all of my different payments close parentheses enter these are all dollar signs so i'm going to highlight the dollar sign again a pretty spreadsheet is a strong spreadsheet this guy is the first one is a calculation i have calculated it from these inputs on the left hand side the rest of these guys are inputs so you have to go in and modify those and finally this guy at the bottom is also a calculation right and i think that's all we need to get started um let's begin our table monthly uh beginning balance i can never be sure that i spelled beginning where is it two ends or one n i don't remember our interest our monthly payment uh let's do an extra payment we'll put that in in a second and then my ending balance for the month right again a pretty spreadsheet is a strong spreadsheet so i'm going to take the time to center and center and i like to wrap the text because it makes it nice and pretty and it saves some space with the big fancy ones [Music] i'm going to put a little black line here at the bottom it shouldn't say monthly it should say month so just kind of again formalize month one month two and three month four and so on right i'm going to go down here and i'm going to create 360 months 350 360. you know what i'm going to do 400 and i'll show you why we're going to do 400 in a minute it's rare that you're going to have a loan over 30 years unless you live in los angeles then you know they're doing 40-year notes over there it's pretty it's pretty insane maybe san francisco's doing the same just ridiculous okay and now i've got all my months all right let's go back up and take a look at the big picture where do we begin we begin with the loan amount not with the price of the house and this is going to be a pointer that says equals a8 enter and that this is a linked cell the difference between a link cell and calculation is obviously that they're calculated so whatever's in in one is gonna show up in the other and there's no calculation thereof this reminds me this formatting reminds me not to touch that one that it is going to be different from the other stuff we're about to do let's calculate the interest for the month that's going to be this beginning balance do not grab the one from the top grab the one from the particular month and for those of you who are already thinking ahead you know that we're going to do the same thing next month and the month after that and so on and multiply it by the interest rate or the mortgage rate two things number one i'm going to hit f4 which is going to lock up that particular cell because you know that i'm going to copy paste it down to the next ones and i don't want this guy to move right number two we're doing this on a monthly basis and this is an annual mortgage rate so i have to divide that guy by 12 in order to get the appropriate interest 600 oh that looks that looks bad um my monthly payment is going to be equal to my calculated loan payment not my total monthly payment right i only care about the loan property taxes are going to go to the county hoa goes to the hoa property insurance goes to the insurance company mortgage insurance goes to the other mortgage insurance company none of this stuff in the middle here in the second column pays down your note only the loan payment at the very top right to that end i'm going to hit f4 and i'm going to lock up that cell so it's exactly the same all the way down extra payment for now that's going to be zero but why not include it right let's create another one up here extra payment and for now i'm going to put 0 uh and this guy like my regular payment is going to be locked up there at c10 it should be zero it is zero my ending balance then is going to be what all the calculation across for that particular month the beginning balance of the month plus right it's going to go up by the total amount of interest that i pay remember this gets compounded monthly it gets compounded either on the last day of the month or on the first day of the next month right and from it i'm going to subtract my payment which is going to be my regular monthly payment plus my extra payment notice that i did this in parentheses because i want the monthly payments to get combined before i subtract them i don't want to subtract one and then subtract the other you don't want to get messy you want to look at these as uh entities and you can figure out the arithmetic on this if it makes you feel better that's fine we're not going to mess with it too much right i'm going to spread this out just a little bit to make it nice and clean yeah to make it nice and clean these are all dollar signs everything looks pretty good to me right let's talk about the second month where do we begin the second month well if 159 807 is the ending balance at the end of the first month then the beginning balance at the beginning of the second month is going to be the exact same thing right now you can come back here and turn this into a linked cell if you want but at this point it's getting a little tedious the great thing about the way that we constructed these four not the beginning balance these four is that i can copy and paste them on the next line and they should be good right this calculates the beginning balance of month number two times this locked up mortgage rate up here at the top yeah that makes sense the monthly payment locked up it's going to be this forever that's the way it is extra payment here you can pay extra but you're assuming that your extra payment is going to be the same every month what i suggest is once you do all of this and you're like well i'm going to throw in an extra 100 bucks at christmas then go down here to month 12 and put you know 100 here right you can play with it as you want that's up to you ending balance should be the one that scrolls down on a week a month to month basis so all of these variables should be on this line number two that looks good to me now here's my favorite excel trick down here in the bottom right hand corner of your highlighted area is this little green box right not this not this quick analysis thing but that little green box right there that little green box is my shortcut key to highlight down so i'm gonna hit this and i'm gonna click and drag oh and if i click and drag down it keeps the whole highlighted cell together and that nice you just do them all or if you want to do them all double click it one two boom it's going to go down all the way down to the 400 mark 400 bam that's fantastic but what is this why is this negative we shouldn't have negatives we do have negatives and we're going to play with that in just a second but first let's get pretty let's do our beginning month in balance and let's highlight that all the way down to 400 control shift down i'll get you there let me do it again control right or sorry control shift right ctrl shift up gets me to the entire thing and i'm going to insert a bar graph ooh isn't that pretty now when you do the bar graph you any graph in excel it's going to insert wherever you whatever row you have present right so here i've inserted it at row three you might find yourself inserting this particular graphic down at row 400 and then it's just a pain so you don't i mean you know just keep an eye on that all right i like to bring this guy up here to the top you can make it pretty later and kind of play with it but what bothers me about this guy is this little this little actually you know i'm not gonna mess with that this little patch of negative down here at the bottom that bothers me i don't like it let me scoot this over to the right a little bit make it nicer make a little fatter yeah that's nicer you can play with this and make it pretty later these are all these are supposed to be dollar signs you get it so we have to do a couple of things to make that negative go away and if we go down to 360 first of all we should see that there are zeros at 360. here's 360. my ending balance is zero sometimes just kind of an fyi sometimes this isn't actually zero sometimes you'll have oh there it is fractions of a penny way down here at the bottom that's going to be problematic problematic for us momentarily because we're not at zero right we're at some millionth trillionth of a penny and that's just really annoying so let's go back let's go back to normal let's go back to two decimals and let's take this back to where you know some degree of normalcy and so we're gonna have to fight that here in a minute uh but more importantly we're at 360 that's 30 years times 12 months and we're at zero for all practical purposes so we're good there that's fantastic let's go back and let's create a more and you would call this an intermediate kind of step i mean if you want to stop here and you're good because you see the number hey that's perfectly fine but let's go on and do some intermediate type of stuff here let's start the monthly payment and we're going to create an if statement right equals if open parentheses and what we want is to do we want to minimize the we don't want to make any additional payments after our balance has gone to zero so when our beginning balance is equal to zero make way that's not right when our beginning balance is greater than zero go ahead and make the payment whoops let me try that again let me zoom in a little more so you can see it if my beginning balance b1 is greater than zero comma go ahead and make the payment why am i doing this all right let me show you another trick i'm going to hit the f2 button and that's going to put me in like this editing mode inside of a cell so if you ever want to edit a cell and you're having problems hit the f2 button if open parenthesis the beginning balance is greater than zero this should work now it did it again f2 delete all of that f2 there you go f2 works else do 0 close parentheses what does this mean if my beginning balance is greater than zero then i still owe money go ahead and make a payment uh else make this zero now we could do this deal where you could you know your your fractions of a dollar or you're less than the actual monthly payment or whatever that's fine we'll call that an advanced trick for now but we also know because we just looked at it that there are these hundredth of a penny fractional whatever and that's a problem so what i'm going to do is i'm going to cheat this a little bit and i'm going to make it one penny point 0.01 right if i am greater than 0.01 and you can make it 0.01 if you want to right it doesn't really matter it's just those fractional pennies that we want to we don't want them to mess up our our calculation here in a minute so put some you know a hundredth of a penny you could actually put greater than or equal to 0.01 that's probably better greater than or equal to 0.01 right and again i have my little green box down here in the bottom right hand corner double click it oh something happened to my graph what happened to my graph oh there we go and it stops at zero right how cool is that oh it's beautiful let's go take a look let's go take a look do i need to change all the other stuff i don't know that i'm going to have to change anything else ah fantastic zero zero zero zero zero zero zero you can make that if statement work here for extra payments you can make that extra that if statement work here for a matter of fact i think we want to uh work here for the payment so let's go do it for extra payments just like we did for our regular payment if greater than or equal to a penny make the payment else zero so let's do that again here if open parentheses the beginning balance is greater than or equal to 0.01 then c10 f2 right arrow else do 0 close parentheses enter and thank you scroll it down if you didn't get a good look at it there it is if the balance at the beginning of the month is greater than or equal to a penny greater than or equal to a penny then go ahead and make a payment else make the payment zero things are going to get weird when they're not oops when they're not perfect down there at the bottom but for the most part this works right let's zoom out let's now we can take a look at stuff what happens if i make an extra payment of 100 oh fantastic i paid so much earlier excellent excellent excellent there are a couple of things i might want to know so this is that's going to stop our our construction of our amor table things look pretty good but if you want to do a little bit more you can i encourage you to play with this as much as you want i want i may want to know what my total payments are payments right so i'm going to get the sum of all of my payments right that seems to work excellent so let's go um that seems to work so let's go grab my payment from the first month my extra payment from the first month and scroll all the way down to the bottom including all of these zeros right because you might want to do more down to the 400th i hit enter and my total payments are 309 000 that seems like a lot uh this is a calculation right so i don't want my client or my boss or my peers to mess with it that's fine i may want to calculate total interest right which is going to be the sum of all of my interest calculations so i'm going to go here to c13 and grab everything that's in that column enter that's a control shift down same thing this is a calculation i don't want anybody to mess with that if you think that that's a little wonky or something doesn't work then you can test it right total payments minus the total loan amount is 149 209 25 total interest fantastic that looks good um what else could we do here i think that's pretty good i think that's pretty good we could actually calculate the ending month right the first month that's more of an advanced deal i think this is good right where it stands uh you can come back here and i don't need months make months go away delete you can play with this and make this prettier if you want at the very least you know where you're at you can change the axis you can calculate this axis to divide it by 12 so that you can have the year instead of the month there's a bunch of different things you can do here to make this work and work well but for the most part you can now hand this over to your spouse or to your client or to your boss or whatever and say hey what happens if well what happens if we make this a hundred and eighty thousand dollar house instead of a two hundred thousand dollar house everything should shift right um uh the one i would encourage you to consider is switching a 15-year uh note look take a look at this guy control c and i'm gonna do the performance this is the 30 year right this is a 30 year i'll turn those back into dollars right and what happens on a 15 year so if i turn this to 15 i mean that's fantastic what's the difference it's about an additional 300 bucks a month that you're going to have to make in a total mortgage payment here the other thing you might want to consider doing is once you get two or three of these models that you like you can plug this in you know copy plug this in copy this whole thing and go ctrl c go over to a new sheet make this 30 year right and when you paste it paste it with a right click and hit one two three so that you only get the values well can i paste it with the values and the formula or and the formatting there you go something like that uh go back to sheet1 to make it 15 years right copy the same thing again ctrl c do a 15 year and you get the point you get to do all of your formatting copy the values copy the formatting there you go and now you can print those off or you can do whatever it is that you want with them to make them look nice and pretty or you can stack them or whatever you want either way what you have here is a nice beautiful amortization table and a very simple chart where you can start to manipulate your hypothetical house purchase i hope you enjoyed it good luck feel free to click and subscribe we'll put some more out as we go along