Simplify Your Electricity Bill Format in Excel for Mortgage Applications

Electricity bill format in excel for Mortgage

hey everyone in today's video i'm going to show you how you can create a mortgage payment calculator in excel and i'm going to show you how we can prove out that calculation to see you know what your total interest payments would be over the course of the mortgage and the principal all within this one sheet so to start i'm going to create some fields one for a house price down payments and a mortgage amount so let's say we're buying a house for a million dollars and the down payment is going to be 20 of that so what's left over is the mortgage it's gonna add some formatting here to make it easier to read so i've got my starting mortgage amount i also need to add an interest rate for that i'm gonna use one percent and then the term in years i'm going to say is 30 years and then i want to calculate a payment amount so to do this i can just use excel's pmt function which as you can see it says calculates the payment for a loan based on constant payments and a constant interest rate so the first argument is the rate or the interest rate this gives me the one percent but because i want monthly payments i need to divide this by 12. the number of periods the second argument is the term and again because this is 30 years i need to multiply this by 12 months in a year now you can change these change these values obviously if you want to put in a monthly interest rate and a monthly term but typically when you're looking at you know mortgage payments you're talking about annual interest rates and annual terms so just for the sake and consistency i'm going to show you how you could convert that this way the present value is going to be a negative 800 000 just think of it from the mindset that you know you're starting at a negative in the sense that you owe that money and each payment will bring down your balance and that's where the future value is set to zero the type i'm going to ignore but you can specify you want end a period or beginning your period and by default it goes to the end of the period and so now when i hit enter it tells me that's my monthly mortgage payment twenty five hundred seventy three dollars and twelve cents now if i change any one of these variables say this two percent three percent it automatically updates and so it's a really easy way to calculate a mortgage payment if you wanted to know you know what your payments might look like if interest rates go go up or down and the next part of this i'm going to create an amortization table to show you to sort of prove out this calculation and so i'm going to create some columns here one for the period one for the beginning balance one for the interest payment the principal and the ending balance i'm just going to expand these uh a little bit here so i'm going to start at period one and the first period is going to be that mortgage amount of 800 000 the interest is going to be that 800 000 times the interest rate divided by 12 so you know how much we're paying interest per month the principal is just going to be this payment amount so i'm going to freeze this and the ending balance is going to be equal to the beginning balance plus the interest less the principal and so i'm going to change i'm going to freeze the interest rate so d8 here just so when i copy this down the formulas will will adjust for period two the beginning balance is going to be equal to the ending balance of the previous period let's copy the formatting down and i'll copy the formulas from below using ctrl d now what i can do is now that i got these formulas set up i'm going to copy this down you can see it looks to be okay now with 360 payments i'm gonna have to copy this all the way down to about roughly that line so a little bit too far but as you can see payment 360 is right there it's got that nice neat zero balance to show that it has been paid off so as you can see the interest rates over time amount to eventually zero and so if you want to see you know how much interest you're going to pay over the course of mortgage you can simply select column i and if i format this properly you see 126 000 over the course of this mortgage so we've got that beginning balance of 800 000 and our total principal payments are 926 321. the difference being being that 800 000 mortgage amount plus that 126 thousand dollars for interest payments so we can see how our our mortgage is gonna move over the course of of the years and and months and again we can change this so if it's two percent you can see that our interest skyrocketed significantly now it's up to 264 000 so even just the one percent change in the interest rate can have a significant impact over a 30-year mortgage another thing i'll show you is let's say you wanted to know what your mortgage balance was after two years and you don't want to go through this whole process of creating this this amortization table what you can do in that case is use the future future value formula in excel which is just fv so in this case we're again using our rate rate divided by 12 because we're looking at months and let's say we want to know after two years what our balance would be so two years 12 months will be 24 periods right so we're looking at this amount here and so for the payment amount we're going to select this payment and the present value is going to be a negative 800 000 because that's where starting from now the important thing to remember regardless of how you do this you want to make sure that your payment and your mortgage amount are not both positive one needs to be negative the other one needs to be positive for this to calculate correctly now if we close this out it tells me that the future value is going to be 760 276.99 which matches up to this value from period 24. so you don't need to create a whole amortization schedule each time if you just want to see you know what your future pay future balance is going to be you know if you wanted to change this to you know five years then you're looking at 60 months or 60 periods 697 634 if we go down to period 60 697 634.99 so that's a way where you can shortcut it and just quickly pull in the value that you want now i also have a mortgage calculator template on my website that you can download for free i'll link to it um in this in this video if you want to uh download it but as you can see just doing these calculations yourself aren't isn't it isn't hopefully too difficult it's a matter of just using really that pmt function or the fv function if you want to jump ahead to see what your future balance is the key thing to remember is to make sure that your interest rates and your term are lining up if you're looking at monthly payments the interest rate also needs to be monthly but on that that's how you can set up a mortgage childhood template in excel