Collaborate on Bill Making in Excel for Mortgage with Ease Using airSlate SignNow

Bill making in excel for Mortgage

today I'm walking you step by step through how to create a mortgage calculator in Excel stick around to the very end because we're going to calculate the time and amount saved on interest when you make prepayments on your mortgage but before we get started let me know in the comments what else can I help you with in Excel and while you're at it make sure to like the video and subscribe to the channel thank you so much and let's get started right now to get started we're going to talk about the financing Arrangement that we have in place the amount of the loan is three hundred and thirty thousand dollars the APR on the loan is seven percent and it's a fixed rate loan so that's not going to change and it's a 30-year loan this brings us to our first Excel formula to calculate the payment owed on this loan so I'm just going to press the plus sign which is the same as pressing the equal sign when you start a new formula and we're going to use the PMT formula and so the rate in this case is the APR and we're going to divide that by 12 because we're going to be making monthly payments the number of periods on the loan is 30 years times 12 months in the year and the present value on that loan is equal to the amount of the loan so I'm going to press enter and Excel calculates that payment as a negative amount and I want to change that to a positive amount so I'm going to press f2 to edit this formula and just enter a negative sign in front of that payment to convert it to a positive amount now we're going to jump down and start filling out the columns in our mortgage calculator so we've got period we've got beginning and when you want to start a new line in the same cell you can just press Alt Enter and this is the beginning balance interest principle a section for our extra payment and then ending balance we're going to populate periods using the sequence formula and the number of rows that we want to populate is the number of periods in the loan so that's going to be equal to this years times 12 columns we can ignore so I'm just going to press comma twice and we want to start at one and we want to step by one as well close parentheses and press enter and Excel populates all 360 periods of the loan the beginning balance is equal to the amount of the loan in Period one the interest is equal to the balance on the loan times the APR and we're going to drag this formula up to other cells so I'm going to press F4 to lock that APR into place and we want to divide it in 12 for those monthly payments principal is equal to payment which I'm going to lock in place again minus the interest we're not going to touch extra payment right now so so ending balance is just equal to that beginning balance minus the principal minus the extra payment the beginning balance for the next period is just the ending balance from the previous period and I'm going to drag down the interest principle and ending balance formulas and we can highlight these cells here and double click them to send them all the way down our table and when I jump to the bottom you'll see here that the load balance at the end of all the periods is equal to zero and that's a great sign that you've set up your calculator correctly so I'm going to press control home to jump up here to the top and I'm going to add a new column over here for original interest and what we're going to calculate in this column is the interest that we would pay on this loan if we didn't make prepayments in this case we're going to use the ipmt formula the rate is going to be that APR locked into place divided by 12. the period is going to just be the period that we've got here in our table the number of period is the years locked into place times 12. and the present value is our amount locked in place as well and press enter this is a negative value so I'm just going to edit that throw that negative sign to convert it back to positive this time our double click isn't going to work so to drag this formula down I'll jump over to the ending balance column press Ctrl D to get down to the bottom Ctrl shift up to jump back up to the top and Ctrl D to drag that formula all the way down and so now let's create a section up here to calculate the extra payment savings and we're going to calculate the interest extra payment savings and the month savings so the interest extra payment savings is just equal to the sum of the original interest column and from that we're going to subtract the sum of the interest column in our table that takes into consideration prepayments and so the current savings is zero which makes sense because we haven't layered in prepayments yet the months savings we're going to enter a multi-step formula the first step is an if formula so if and the test is going to be highlight this ending balance and then enter the less than symbol and then zero so if our ending balance is less than zero the value of true is yes close parentheses and press enter and this formula returns a range which is why there's a spill error here but that's what I expected because we have more to do next we're going to add an X lookup to the front of our if formula so X lookup we're going to look up the word yes in quotation marks the lookup array is going to be the entire if formula that we've already done and the return array is just going to be the period column in our mortgage table over here and close parentheses so what this formula is going to do is return the period when the ending balance is less than zero and in this case that's 360 because ending balance is less than zero and the last period of the loan and then lastly we're just going to compare that to the normal number of periods in the loan which is the years times 12 and subtract from that our X lookup again we haven't saved any months yet because we haven't layered in extra payments let's jump to period 13 and we're going to layer in five extra payments so I'm going to enter a five extra payments of a thousand dollars and I'm going to enter those in in our calculator shows us that those five extra payments save us more than thirty thousand dollars of interest on this loan and let us pay it off 16 months quicker than we otherwise would have check out this video with some simple Excel formatting tips to make your mortgage calculator you later look more professional and be easier to use thank you so much and I will see you next time