Signing a loan agreement, provide in order of calculation and payment of interest at maturity, monthly, as repayment of principal, etc. in addition, discuss the possibility of using so-called compound interest by adding the amount of interest for a certain period to the original amount of the loan and fees in the total amount. This method is more profitable for the lender, but disadvantageous for the borrower.
If the contract of loan set payment of principal and interest in a lump sum at maturity, monthly interest charge according to the formula: interest = (loan amount) x (annual interest rate) x (number of days in month) / 365 (366) days a year. When calculating, consider the actual number of days in a year, and in each of the months. The starting point for interest accrual is the day following the date of the loan.
In that case, if the contract established a schedule of principal repayment, charge interest to the extent of its payments. In this case, use the following calculation formula: interest = (outstanding loan balance) x (annual interest rate) x (number of days in period) / 365 (366) days a year.
The loan agreement may also provide for a partial refund as the borrower's available reserves to calculate on loans. In practice there are cases when the loan is repaid in tranches several times a month or a week. In such cases, it is convenient to charge interest on the outstanding balance daily in electronic form.
Create the Excel spreadsheet include columns: date, amount owed, interest rate, number of days in the month, the number of days in a year. Add a total column "Accrued interest", write the formula and then copy it for each day. Daily record in the table the balance of the debt, and at the end of the month summarize the automatically calculated interest.