Get the Best Mobile Bill Format in Excel for Mortgage

Mobile bill format in excel for Mortgage

hey everyone in today's video we are going to build a mortgage calculator spreadsheet that allows you to calculate what your mortgage payments are going to be this is a full amortization table so stick around to see how extra payments can impact the amount of total interest you end up paying and how much money can really save you getting started right off the bat you're going to need to know the amount of your loan the interest rate of your loan and what kind of loan in terms of the period so is it a 30-year loan a 15year loan or something else this will lead us to the amount of periods you have in months and then finally the monthly payment which will be fixed for the most part and before we get into it I like my spreadsheet to look a little bit nicer so I'm just going to Center everything change the font and resize this column to kind of hide it off to the side okay and as I already mentioned we are going to have the loan amount rate in periods as inputed Fields so all the inputed fields on this spreadsheet I'm going to Mark as yellow and then then everything that's calculated such as monthly periods we are going to leave as blank or white so for the monthly periods we can hit equals and then click on C4 * 12 which makes sense the amount of months is the amount of years time 12 and this basic table here I'm going to put a border around this part is separate from the amortization table which is going to fill the space below and I'm just going to add some basic information so we have a baseline so the loan amount will be 500,000 we can format that number in terms of accounting the rate can be 6.5% and we're going to format that as a percent switch it back to 6.5 and this will be a 30-year loan and I know I centered everything before but I like this table to be left aligned and the information here to be right aligned okay and now we're going to get into the main table which we'll start in row eight it has 10 distinct columns so it will have the p P date the beginning balance the payment which is made up of principal and interest any excess payments that you have the ending balance the period like which month of the loan it is the total principal paid and the total interest paid and with this the first thing I'm going to focus on is the period just to get that part out of the way so all I'm going to do is enter one 2 2 3 I can highlight these and drag them all the way down until I have 360 and there we are 360 months in this loan we're going to scroll back to the top and now start with the payment date for this I'm going to highlight column B in format it as a date in here and for the sake of this example the first payment date will be January 1st 2024 and instead of entering every date for 360 months after that we can just type in the equals e dat formula and select the date of January 1st in cell B9 as a start date add a comma and then we're just going to select the number one which will space everything one month apart hit enter and then now we can just drag this cell B10 all the way down to month 360 now that that's done we see the final payment in month 360 will be on December 1st 2053 scrolling back up we can now look at the beginning balance and ending balance so the beginning balance for each month is easy and sell C9 we're just going to hit equals and then click the loan amount C2 we hit enter and after that the cell in c10 is going to be equal to the ending balance H9 of the month prior and hit enter and that same ending balance and H9 is going to be equal to C9 the beginning balance minus E9 the principal paid and minus G9 any excess payments hit enter next we'll want to calculate this payment column here here but first we have to calculate the monthly payment in cell C6 so to do that we're going to use the payment function we'll type in equals PMT then in parenthesis it wants the rate comma number of periods in terms of months here 360 comma and the present value and then one thing I forgot to add is that we do C3 / 12 the rate divid by 12 hit enter and you see it shows up as a negative number we want it formatted as a positive number so we're going to do equals PMT and then one last adjustment we want to round this number as well so we'll click on that cell again and at the start of the formula type in round add a parenthesis go to the end add a comma and type in two for two decimal places and close parenthesis hit enter and now that we have this we know that our monthly payment for every period should be $3,160 34 so using this we can go into the payment column and sell D9 and type in equals C6 hit enter X out of this and we want to keep this referenced so we're going to add a dollar sign in front of c and in front of six and while this payment number is fixed it might not be the payment at the end so what we're going to do in cell d10 is Type in equals Min for the Min function so what we're going to do is enter two values and the result will be whatever the minimum of those two values are so going into that we're going to open our parentheses and the first value will be C10 plus F9 because in the final period the balance remaining plus the interest will be the payment not the $3,100 we add a comma and then for the other part of the Min function we're just going to click on Cell D9 we can hit enter and then go back into this and make sure that the these are referenced with two dollar signs in front of the D and in front of the nine hit enter I'm now realizing this is not properly formatted as accounting so what I'm going to do is highlight all of these and these columns and format them as accounting now we can move on to the principal and interest payments principal in cell E9 is simply going to be equal to D9 the total payment minus the interest payment in F9 after that we can calculate our interest payment by clicking on Cell F9 and typing in equals C9 the beginning balance times C3 the rate / by 12 and hit enter one last thing for the interest payment we are going to keep C3 referenced by adding a dollar sign in front of the C and the three and then also we're going to round this by adding the round function to the start and then at the end we're going to put a comma for two decimal places and close our parentheses hit enter and looking back at all these before we drag them down this is good to drag down this is good we're going to drag down from here and drag down here so now I can highlight all of these and drag them all the way down to month 360 when I do that you see that the ending balance after month 360 is -20 it's a smaller I wouldn't worry about it too much but that's going to happen with this sheet and it's hard for me to tell which column all these numbers belong to so that reminds me to scroll back up and I'm going to freeze by clicking on row eight hitting view freeze up to row eight and now when I scroll down you can see exactly the column which each number falls under and before I go any further I'm going to format this a little bit better by highlighting from this corner here all the way up to Total interest I'm going to add borders around this whole thing and then I'm also going to make these headers stand out a little bit more I'm going to make it bold uh make this a dark blue with a white font and if I go all the way back down to the bottom again right here drag all the way up to the top but not include the header I'm going to add some alternating colors get rid of the header by clicking out of it here and hit done and now this just looks way better so one other cool feature I want to add is so that you can see the total principle and total interest paid for any given month so these are going to tell you the total principal and interest paid after month one and over here it will tell you the total after 12 months and you can compare it and see when you're finally paying more principal than interest so these columns are pretty easy to set up in cell j9 for total principal it's going to be simply the principal paid in this period and then for the cell immediately underneath it it's going to be the total from the month before in cell j9 plus E10 we can hit enter and then total interest is going to be the same thing so total interest in K9 is equal to F9 the interest in Period one and then the interest in Period two will be equal to the total interest prior plus the interest paid in Period 2 which is cell F10 hit enter and these two cells here we can drag all the way down once again okay and there you have the total principal and interest paid you can see at the end you have roughly almost exactly $500,000 of your interest the total loan amount paid off and you can see you've paid $637,000 in interest over the 30 years and you may be saying to yourself wow $637,000 in interest that's more than the principal is there any way I can bring that down and you can with with excess payments so this last part is going to create a little table that shows what interest you're expected to pay the 637000 when you started your loan and how your excess payments have brought that number down and by how much so I'm going to scroll this all the way back up and then I'm going to start this next table in cell E2 with total principal total interest original interest so the amount of interest you expected to pay at the start of the loan and then interest saved over the life of the loan so total principal is equal to the maximum figure Max in column J so we're going to highlight everything here so the maximum number from j9 to J 368 is the final number of $500,000 and then we can do the same thing in in total interest and type in equals Max and then parentheses K9 to K 368 like that and hit enter so at this point we want to identify our original interest and highlight this as yellow because this will be an inputed number so we're going to type in the 637 7222 number and hit enter so the total interest at this point is equal to the original interest but interest saved will be equal to the original interest here minus total interest and hit enter and we've already set everything up for this to work successfully because the ending balance on any given period already subtracts column G which is the excess payment and because this is another inputed field that we're going to have I'm going to highlight this entire column as yellow by clicking and dragging it all the way to the top and then and again highlighting it yellow I'm going to format this similarly to the one on the left by aligning these CES to the left and now let's say in month 12 we make an excess payment of $5,000 for example on top of our original payment of $ 3160 hit enter and you can see that with our excess payment on our principal at $5,000 that's going to save a lot of interest it's going to save almost $27,000 worth of Interest over the life of our loan and the cool thing is you can play around you can see how that works in different periods so like a $5,000 payment in Period 12 saves 27,000 if you do the same $5,000 payment in Period 6 only 6 months earlier it's going to save another $1,000 because of how these interest payments work the earlier you make an excess payment the more interest it will save over the life of the loan and the more you pay early on the more you save in total does this necessarily mean that doing a heavy excess payment early on is the right move for you no but now you can kind of look at it and see how it will affect the finances of this loan and you can also play around with different loan amounts different rates different periods probably not the periods as much but you can see oh if I get a 6 and 12% loan um let's get let's get rid of this excess payment but if I get a 6 and a half% loan versus let's say a 6% loan originally the interest was 637000 now it's 579 so that half a percent is making a almost $60,000 difference over the life of your loan so this spreadsheet can help with your financial literacy whether you have a mortgage or not um if that's something you're planning to do I recommend playing with the numbers of it a little bit first but this spread sheet could work for other loans not just a loan for a house so I recommend downloading it I'm going to put the link to this in the description or the top comment so when you open that you're just going to have to make a copy first and then you can use it for yourself I hope you can find some use for this spreadsheet I worked hard on it so a like would mean the world and if you have any suggestions for anything in the future please drop a comment as always thanks for watching