Simplify Your Bill Book in Excel for Real Estate Management

Bill book in excel for Real Estate

hi guys welcome to a hate maath group today I'm going to show you how to build an amortization table in Excel from scratch so let's do it so I have the amount borrow which in this case is going to be $100,000 the periods are going to be the following well let's say that this is 30 years but I want to do this monthly so I'm going to multiply this by 12 because you have 360 periods the rate is going to be let's say that they charge me 133% but I'm going to put it uh monthly so this is3 divided by 12 and finally I'm going to have the payment which is going to be the following equals PMT parenthesis and then Excel is going to ask me hey what is the rate well the rate is going to be this guy comma the periods is going to be 3 60 and the present value is 100,000 put a negative in the front because if not Excel will give you a negative value as your payment comma zero because that's a future value and then just close your parenthesis this is going to be your payment let's do it now your month is number one the beginning balance is actually going to be $100,000 so I can go ahead and just type it the payment is going to be this guy right here now how do I calculate the interest the interest is calculated in the following way you do equals the beginning balance times the rate this is how much you're going to be paying on interest every month but remember this amount is going to change all the time how do I do the principal well this is how much you make on your payment minus the interest that you pay that's going to be your principal and your ending balance is going to be the the beginning balance minus what you pay on your principal now let's do month two the beginning balance is actually going to be the ending balance of the first month the payment is actually going to be the same but you know what this becomes very tedious to write it over and over so I'm going to go ahead and do the following equals and then click on C4 but actually you can lock this cell by doing uh dollar sign letter dollar sign this is basically going to block the cell and it's going to repeat it all the way to the 360 periods well the interest remember is going to be the beginning which is in this case this but my rate will always be the same well you know what I'm going to do I'm going to go ahead and lock the rate again I do dollar sign and then dollar sign between the letters because look in Excel B7 B8 that's going to change but the interest is so the rate is always going to be the same now the principle again how do I calculate it my payment minus my interest and the ending balance is finally going to be the beginning balance minus the principle once you build the second uh row go ahead and shade it and go all the way down to 360 360 and you can see on the right side I have the numbers all the way to 360 I'm almost done I pass it 360 guila I just build my amortization table as you can see here the principle look what's happening it's actually going up and the interest is actually going down little by little I can see that my ending balance is going all the way oops I pass it to zero now how do I know this table is correct well look what I'm going to do here this is how much you pay for your principal if you do the sum of all the payments that you made all the way to one you should get the $100,000 which I did and then if you want to figure it out the interest pay all over the loan just just do the sum of all the interest payments and don't cry at the end of the table because it's usually a lot but you know that's how time value works so this is basically how much money you pay during the loan and this is how much you repay back and that's it how easy was that please don't forget to watch our other videos also remember you can Skype with us and also thanks so much for learning