Create Your Billing Sheet in Excel for Mortgage Effortlessly

Billing sheet in excel for Mortgage

today we're going to be talking about loan amortization tables uh specifically with an extra payments added feature within excel all right so a loan amortization table is super useful in determining how much principal or how much interest you'll be paying for a loan each month or the lifetime of a loan the total amount that you'd be paying and and so on so to kind of start a loan amortization table you need to know three things you need to know the the loan the loan amount the the interest rate and the periods are the number of years that that loan is going to be lasting for right so let's say we want to take out 150 000 loan um with a 5.5 percent interest rate over 30 years all right um so with this information what we need to do is calculate the payment in order to start this table um so we need to know the present value the rate and the number of periods or in this case that will be monthly and the rate also needs to be translated to monthly because we're trying to find the payments per month all right um so our present value we know to be 150 000 our rate is 5.5 percent but we've got to divide that by 12 to put it into months and then our number of periods would be 30 years but times that by 12 to get 360 total months all right so then for pmt we'll do a negative pmt function we'll hit the rate number of periods and the present value so over the lifetime of this loan so over 30 years we'll be paying 851.68 cents per month all right so to start an amortization table um we'll start with a couple of labels here we'll go with periods we'll need to know periods the payments um the the interest paid we'll also want to know the principal paid the and then we'll add an extra extra payments function here uh section there and then our remaining loan loan balance all right let's make this a little bigger all right and then we're going to go ahead and center all this stuff all right so our periods we'll start with period 0 1 um and we'll just go down to 360. scroll down here zoom zoom all right 360. so we'll have 360 periods because there's 360 months so each period will represent one month um and our loan balance will be for period zero will be 150 000 obviously in period zero the you know we're not gonna have any payments or interest or principal um quite yet so our payments are going to equal our monthly pmt that we already calculated so that'll be our monthly payment oh we got a lot make sure we we go ahead and lock this cell um and we can just drag that down so it'll be each payment per month we know to be 852 essentially all right so the interest that we pay off this payment is calculated by taking the loan amount times the rate all right so of this 852 payment we're paying 687 dollars in interest and the principal is just the payment minus the interest so um and then for now we'll keep our extra payments at zero um at zero dollars all right so then our loan balance is the previous month's loan balance and we will subtract the principal and then we will subtract any extra payments that we have all right so this is the remaining balance of the loan after the first month and what we can essentially do is if we've done our formulas correctly we'll need to go ahead and lock this we can drag them down we can lock that and drag it down all right and then our principal we can drag down as well extra payments we'll just have all of that as zero right now and then we will drag this formula down all the way and we will notice that our interest decreases each month and the principal we pay increases each month which is what you should expect all right so then if we did this correctly at the end of month 300 um 360 you should have a loan balance of zero so that way we know we did it correctly all right all right so now let's say we wanted to know the total the total interest the total interest we paid and we want to know the total is the total amount we paid for this specific loan so we'll go ahead and sum this column the interest column and we will have paid 156 000 dollars in interest all right and the total amount we paid um we can just highlight all the payments we made over the course of 360 months and determine that over 30 years we paid 306 thousand dollars to pay off this loan due to interest all right so that just goes to show the power of this interest rate when taking out a loan because if i were to reduce that by almost half to 2.5 percent i paid nearly 80 000 less with a lower interest rate and much less interest all right so these um so going over this quickly this and right here will highlight that yellow those are inputs that you can change all right so let's say instead of 150 000 loan i wanted a hundred eighty thousand dollar loan you know it would recalculate the payments for you and recalculate everything for your loan balance your interest in your principal and the total amounts right here um let's say you wanna decrease the interest rate obviously that'll take effect those are simple calculations that you can adjust depending on what you're looking for all right so we have everything set up for you now let's talk about these extra payments let's say that in the fourth month we paid an extra two thousand dollars and uh we want to know how how that will affect our total loan balance um and the total amount of interest so obviously we this this this will not be calculated correctly um but it will calculate the total interest correctly if we add extra payments so we will have paid much less interest by simply paying two thousand dollars in month four let's let's go ahead and scroll down and we can then determine what month we will pay off the loan so you'll notice that it'll start getting negative around a month 348 and 349 it goes negative so you can assume that by month 349 you'll have paid off the loan entirely all right so these these other couple months you will no longer have to pay um you will no longer have to make monthly payments because you already paid off the loan so you would essentially be sale saving nine thousand dollar nine thousand four hundred dollars by making an extra two thousand dollar payment in the fourth month all right so but let's say that we wanna be a little bit more consistent let's say we just add an extra 100 bucks to our to the payment um every month and so we can do the same go through the same process we can scroll down and wait for it to get negative we'll see here within month 280 and 281 it'll start to get negative so by month 281 we will have paid off the loan so essentially saving 80 days of payments by just adding an extra hundred dollars to that loan payment every every month all right so this loan amortization table is really useful in checking like hey well if i have extra payments how will that affect my my luck the amount of the the loan or the total interest i'll be paying um you can also adjust these these numbers to kind of determine um in different interest rates and loan amounts and how that affects everything um and so hopefully this was super simple and easy to follow along with um go ahead smash that like button and add a comment if you have any questions or if something wasn't super clear hopefully it was easy for you to understand and make sure you subscribe alright thanks everyone have a great day bye