You will need
  • Loan terms - loan amount, interest rate, period. Microsoft Excel.
Instruction
1
The amount of the overpayment on the loan is the sum of actually paid interest-bearing liabilities. In order to calculate the amount of the overpayment, you need to add up all the amounts the payment of interest for the entire period of the loan.
2
The monthly payment on the basic debt is calculated as follows. The full amount of the loan divided by the loan term in number of months.
3
Open the page in Microsoft Excel and create a table with the actual data of the specific loan. To do this the table header:
1 count) № p/p (number of months),
2 count) the number of days in the month
3 graph) the amount of the loan
4 graph), the repayment of the principal debt for the period
Count 5) payment of percent for the period
6 count) loan payment (sum of 3 and 4 count).
4
The first line of the "loan amount" indicate the full amount. The second line - put the cursor on the table cell and write the formula: = sum from the first row - the payment of principal in the previous month. This formula copy this column to the end of the table. Thus, each subsequent principal amount will be reduced by the amount of principal repayment on the loan in the previous month.
5
In the first row of graphs of interest on the loan put the cursor on the table cell and write the formula: = principal amount (the current row) * % rate of shares / 365 * a cell that represents the number of days in the month in the same table. So it turns out the amount of accrued interest for the current month. This formula also need to copy to the end of the table.
6
The first line of the graphs showing the loan payment put the cursor on the table cell and write the formula: = total monthly payment of principal + interest amount that is specified in the same table row (current pay period).
7
At the end of the table under the graph, paying the monthly payment of principal, interest and loan payment put the sign of the sum of all cells in the corresponding column. So in the graph of the monthly payment of principal total amount should equal the amount of the loan. In the column percent of the total amount will indicate the amount of the overpayment on the loan. And the loan payment the total amount will be the payment of the principal debt + interest for the entire period of the loan. So it is possible to check the correctness of calculations.