Loans have become an integral part of modern life. A lot of credit organizations offer their services, advertising favorable terms. But the most profitable loan is the one for which the smallest overpayment. When choosing a credit program for yourself, you can calculate this overpayment.
It is necessary
Loan terms - loan amount, interest rate, period. Microsoft Excel
Instructions
Step 1
The amount of overpayment on the loan is made up of actually paid interest obligations. In order to calculate the amount of overpayment, you need to add up all the amounts of interest payments for the entire period of the loan.
Step 2
The monthly payment on the principal debt is calculated as follows. The total loan amount is divided by the loan term in the number of months.
Step 3
You open the page in Microsoft Excel and draw up a table with the actual data of a particular loan. To do this, make a table with a header:
1 column) No. p / p (number of months), 2 columns) the number of days in a month, 3 columns) loan amount, 4 columns) repayment of part of the principal debt for the period, 5 column) payment of interest for the period, 6 column) loan payment (the amount of 3 and 4 columns).
Step 4
In the first line "loan amount" indicate the full amount. In the second line - put the cursor on the cell of the table and write the formula: = amount from the first line - payment of the principal in the previous month. Copy this formula in this column to the end of the table. Thus, each subsequent amount of the principal debt will decrease by the amount of repayment of the principal debt on the loan in the previous month.
Step 5
In the first line of the loan interest column, place the cursor on the table cell and write the formula: = principal amount (from the current line) *% rate in shares / 365 * per cell, reflecting the number of days in a month in the same table line. This is how the amount of accrued interest for the current month is obtained. This formula must also be copied to the end of the table.
Step 6
In the first line of the column displaying the loan payment, put the cursor on the cell of the table and write the formula: = the amount of the monthly payment on the principal debt + the amount of interest, which is indicated in the same line of the table (current billing period).
Step 7
At the end of the table, under the columns of the monthly payment of the principal, interest and loan payment, put the sign of the sum of all cells in the corresponding column. So in the column of the monthly payment on the principal debt, the total amount should be equal to the loan amount. In the interest column, the total amount will indicate the amount of the overpayment on the loan. And in the column of payment on the loan, the total amount will be the payment of the principal debt + interest for the entire period of the loan. This way you can check the correctness of the calculations.
