In this screen cast, I'm going to talk to you about lines of credit. Lines of credit are different from some of the other types of loans that we have considered in this course. In a line of credit, it's like a tank and you have access to money and you can withdraw from the tank and then you can put back into the tank, you can withdraw and so on, and at the end of the billing period, you're charged interest. Here we have the daily balance as a function of days of the month and you see that the bars will increase in size if you're withdrawing from that line of credit or you taking what's called a loan advance from the line of credit. Then these green arrows represent payments. So these bars will decrease in size if you make a payment to the line of credit. So you're basically borrowing from the line of credit, you're paying it down over time, and at the end of the billing period, you're charged for the interest that has accrued. A lot of times people will use lines of credit for short periods of time. So we might have a short period of time here where we make expenses. We use the line of credit, but then we try to pay it back as fast as we can. Then we wait some time, we make some more expenses and then we pay down back to zero and so on. Even though at the end of the billing period we might have no balance, we still are going to owe interest. I'm going to talk to you about how we calculate the interest owed on a line of credit in the screen cast. So lines of credit usually have a limit that the loan balance cannot exceed. Lines of credit usually require a minimum monthly payment. This is also sometimes known as a finance charge, which is generally the interest earned in the billing period. The monthly payment sometimes is called a regular payment. You can pay more than the minimum if you wish to pay down the loan balance, this is actually a good idea. You want to try to minimize the daily balance in your line of credit to minimize the amount of interest that you're paying each month. In a line of credit, you have two periods. You have something known as draw period, and that's when you're actually withdrawing from the line of credit and then you're paying it back, withdrawing, paying it back. After that draw period, the balance at the end of that draw period is usually placed on an amortized loan and this is known as the repayment period. The interest rate can be fixed or variable. Lines of credit can be secured. An example of this would be a home equity line of credit, where you're essentially offering your house or your home as collateral, or it can be unsecured, something like a credit card. Most lines of credit use something known as the average daily balance method. The rest of this screen cast, I'm going to be showing you how to implement the average daily balance method in Excel. So to use this method, each day we calculate a daily balance. The daily balance is going to be a sum of all the charges and loan advances, payments, deposits, and withdrawals plus the previous day's ending balance. Then we multiply the daily balance by the daily rate, that's the daily interest rate to get the daily interest. Note that this is simple interest since we're not adding the previous day's interest to the principal or loan amount. So in the average daily balance method, we're not using compound interest, we're using simple interest. The daily rate is the annual percentage rate divided by 365, that converts it to a daily rate. Then finally, we can sum the daily interests over the billing period to get the finance charge in regular payment. So let me show you how we can implement the average daily balance method in Excel. I've got this file called line of credit that I'm going to be working through. This is just an example of perhaps the last several months of a home remodeling project. Just to note here, in my example, the billing cycle is every month. So we're going to be making payments the first of every month and we're going to accrue interest through the end of every month. So I've got this setup, I have dates over here, I have a beginning balance. So let's say starting April 1st of 2020, you have a beginning balance carried over from March. We have a column here where this is the charge, also known as loan advance. So this is the amount of money that we borrow or withdraw from the line of credit. Then we have a column here for the payment or the deposit. Now this is the payment or deposit that's not the regular payment or the finance charge, that's over here on the right. The interest that accrues is summed into something known as regular payment or a finance charge, but that's different than a payment or a deposit. A payment or deposit is an additional payment that's meant to bring down the balance of the loan. I've also got a column over here for the rate. So the first thing we need to do, we're going to go day by day, we have to calculate the end balance or the daily balance. To do that, it's always the beginning balance. So what we started the day with plus any charges or advances minus any payments. So I'm just going to subtract, I'm going to take the beginning balance, I'm going to add any daily charges or loan advances, and then we're going to subtract any payments or deposits, and that's going to be the daily ending balance and that's known as daily balance. The daily interest then, we're going to take our daily balance times the annual percentage rate of 7.49 percent and we're going to convert it to a daily rate by dividing by 365. So that's our daily interest charge, it's simple interest on the ending balance. Now we go to the next day. The beginning balance of the next day is equal to the ending balance of the previous day. So I can just type in the simple formula there to link to the previous day's ending balance and we can drag down this formula. Because we're not taking any loan advances that day, we're not making any payments, the ending balance is going to be the same. We can drag this down. So we're dragging this down and on that second day of April, we're getting the same interest charge. Remember, this is simple interest, it's not compounded. This daily interests from April 1st, is not added to the balance. You're not going to know beforehand necessarily what all these charges and the schedule of payments, but typically it's just done day at a time. So you would just add one row at a time every day. You would start with your beginning balance, which is the ending balance for the previous day and then you would tally up your charges for that day, any payments made and that's how this would work. But I've just put in some stuff here so you can fill out this spreadsheet. So I'm going to drag this down. Let's just go to the end of May for now. We have our loan advance, our charges, our payments, and deposits. The daily balance is always equal to the beginning balance plus any charges minus payments. So I can copy that down. Because I've set this up as a relative reference, I can just double-click this down. So that's how we would calculate this. So every day of the billing period, we are charged a daily interest. In this method then, the finance charge or the regular payment is going to be the sum of all of the daily interests for that period. So if we're going month by month, which typically you will do, we can just sum the daily interest charges for that entire month. So I can sum the entire month like that, so that's 37.52. For May, I could do the same thing. So for May, I could go down here to the end of May, that's going to be the sum of the entire month of May, May 1st up to May 31st, and that's going to be our payment for May. So those are the charges for the line of credit as a function of time. So then as you go along, you can keep track of this. So you can do this at home. Let's just say we can drag this down and maybe that day you make a charge of $45.89, maybe you make a payment of a $150, so then you would know for that day you're charged 67 cents of interest. This is how you can keep track of your line of credit. So let me back up a little bit. Let's just assume that the loan ended at the end of May. So let's just go ahead and delete anything below here. So at the end of May, you got an ending balance of $3,873.52, this amount is what would be placed on an amortized loan. Let's just say we had a 10-year loan at five percent, then you could do payment. So the rate 0.05 divided by 12 if paid monthly, so five percent. That would be over 10 years, you'd have a 120 months. The loan amount to the present value would be the ending balance of your line of credit and then that $41 would be your payment for the next 10 years to pay off your line of credit. So these lines of credit have a draw period. The draw period would be up until May 31st, then if this were 10-year amortized loan, the repayment period would be 10 years. It's an amortized loan where we take the ending balance of our draw period and we place in an amortized loan. So that is how lines of credit work. Thanks for watching.
