Simplify Your Billing Excel Sheet for Mortgage Management

Billing excel sheet for Mortgage

in this video we're going to talk about how to create an amortization schedule so let's say if we're taking out a mortgage or a loan of 300 000 that's going to be our principal amount and let's say it's a 30-year mortgage so the term is 30 years and we're going to say the annual interest rate is 5 and it's going to start july 1st 2020. now in cell d1 this is going to represent the number of payments now we're going to be paying the mortgage back every month and if this is a 30-year term the number of monthly payments is going to be 30 times 12. so this is going to be equal the value in cell b2 which is 30 years times 12. so we get 360 monthly payments now the monthly rate is simply the annual rate divided by 12. so we're going to take the value in cell b3 and then divided by 12. that gives us the monthly rate now to get the monthly mortgage payment we're going to use the pmt function type in equal pmt open parentheses now the rate this is the monthly rate so that's going to be cell d2 comma now the number of periods that's basically the number of payments and that's going to be the information in cell d1 that's 360 payments the present value of the loan is the principal which is cell b1 and we're going to add a negative sign to that since it's going to be decreasing over time and then we'll press comma 0 close parentheses and then press enter so this gives us our monthly mortgage payment of six hundred ten dollars and forty six cents if we adjust the principal that value will change but let's keep this at three hundred thousand dollars now the months this is going to be the first payment so we'll put one two for the second payment three for the third payment and we can extend it if you highlight uh these cells and then click on this button at the bottom right and then drag it you can extend the data but we'll start with the first three rows now the first payment is going to be on july 1st 2020 and then it's going to be on the first of each month after that so the third month is going to be september 1st 2020. the beginning balance is whatever we see in cell b1 that's the principle the payment is going to be the information found in cell d3 so we can type in equal d3 the interest it's going to be the product of the beginning balance times the monthly interest rate so we're going to type in equal cell c3 times the value in cell d2 actually i take that back this is supposed to be cell c7 the beginning balance times the value in d2 so that's gonna be the interest rate now the payment the portion of the payment that goes towards the the principal that's going to be the difference between the monthly payment which is found in cell d7 minus the interest payment which is an e7 so this payment that goes towards a principle is going to decrease the beginning balance by this value so the end imbalance is going to be equal cell c7 minus f7 so now let's move on to the second row the beginning balance in the second row is the ending balance in the first row so we're going to type in equal cell g7 now for the payment we don't want to just extend this because it's not going to look good even though it's the same instead we're going to type in instead of typing equal d3 which we did before we're going to type in equal dollar sign d dollar sign three because now if we extend it it remains the same now the interest that we uh got in row seven was c7 times d2 for row 8 it's going to be c8 times d2 it's the beginning balance in the second row times the monthly interest rate that we see here now the principle is going to be the payment minus the interest so for row 8 it's going to be d8 minus e8 so that's not going to change for row 7 is d7 minus e7 here d8 minus e8 and then we could just do the next one this is going to be equal d9 minus e9 but we don't have values for that so we'll do that later now the end imbalance is going to be the difference between the beginning balance and the principle so before it was c7 minus f7 now it's going to equal c8 minus f8 now moving on to the ninth row or the third month the beginning balance and the ninth row is the end imbalance in the april so that's going to be equal g8 the payment is going to be the same so let's extend this and then the interest is going to be the beginning balance which is in cell c9 times the monthly interest rate which is found in cell d2 and then the principal that's going to be the payment d9 minus the interest e9 and then the end imbalance that's going to be the beginning balance which is c9 minus the portion of the payment that went towards the principal that's found in f9 and so it's good just to check the formulas to make sure they're the same for each row now let's highlight the last two rows and let's extend it notice that we get an error the question is why if we analyze column g looking at the formulas they're okay c10 minus f10 i'm looking at this area by the way c11 minus f11 c12 minus f12 c13 minus f13 that column is fine looking at column f it follows the same pattern d9 minus e9 d10 minus e10 d11 minus e11 the payment column is okay the beginning balance g8 g9 g10 g11 g12 that's okay the problem is with the interest notice how the formula change c8 times d2 c9 times d2 here it is c10 times d4 so we're gonna have to do is we're gonna have to lock cell d2 in order for this to work instead of having just d2 here i'm going to put dollar sign d dollar sign 2 and then for the next entry in cell e9 we're going to add dollar sign d dollar sign two now let's extend the last two rows notice that it's working out so we're gonna go up to let's see the first month corresponds to row seven so we're going to go up to row 366. i went too far and we can see the last payment is the 360th payment and notice the ending balance it goes to zero now let's take this sum of the column of all the payments that we made so i'm going to scroll up i'm going to highlight everything here so it's equal sum open parenthesis d7 colon d366 close parenthesis press enter and this is the sum of all the payments that we've made over the course of 30 years now let's calculate the total interest paid on this load so if you type in equal sum and then for the previous column moves d7 to d366 this is going to be e7 colon e3 66 and then this gives us the total interest column f is the difference between the payment and the interest so this is the the value of the original loan this is the principal the loan was worth three hundred thousand dollars at the beginning this is the total interest paid two hundred seventy nine thousand seven hundred sixty seven dollars over the course of thirty years the total the total payments made that is the principal plus interest uh this is 579 000. so that's how much money you can be paying over the course of 30 years so this is how you can create an amortization schedule so now if you adjust the principal you could see how the monthly mortgage is going to change the monthly mortgage payment and how the beginning balance and the end imbalance will change now once you create this amortization schedule you can play around with it you can adjust the interest rate of them of your loan and see what the new monthly mortgage payment will be in this case it's going to be two thousand three hundred ninety eight dollars and twenty cents you can adjust the length of the loan let's say instead of a 30-year mortgage maybe you want a 20-year mortgage and that's going to affect the monthly payment if you're going to try to pay off the loan faster you need to put more money into the loan you got to increase the mortgage payment to pay off the loan faster and so it went up from 2300 to 2800 so that's basically it for this video now you know how to create an amortization schedule using excel thanks for watching