Not all loans and credits are created equal. Understanding the principles of calculating monthly payments on a loan, including the total amount of interest that you will eventually have to pay for the use of borrowed funds, is very useful in choosing the loan conditions that are ideal for you. Calculating the exact amount requires some calculations using a rather complex formula, but this process can be simplified if you take advantage of the functionality of Excel.
Steps
Method 1 of 3: Learning basic information about the planned loan
Step 1. Enter your prospective loan details into the loan calculator to quickly calculate interest payments online
The calculation of interest payments is not limited to the simplest calculations. But fortunately, a quick search on the net for the phrase "loan calculator" will allow you to easily calculate the regular amount of annuity payments on a loan, if you know the initial terms of the loan, which should be entered into the calculator.
- Loan principal - the amount of the loan given to you. If you take out a loan of 50,000 rubles, then the principal amount of the loan is also 50,000 rubles.
- Interest rate - the percentage of the loan amount that you pay to the lender for the use of borrowed funds. It can be presented as a percentage (for example, 10% per annum), and in decimal (0, 10).
- Loan terms - the period of full repayment of the loan, usually indicated in months. For long term mortgages, it can be quoted in years. It is also always useful to familiarize yourself with the terms of early repayment of the loan, as some banks may set certain limits on the amount of partial repayment.
- Payment type - option for loan payments. In most cases, the loan is repaid in equal annuity payments (this is more profitable for the bank and more convenient for the borrower), but sometimes payments can be differentiated (decreasing). If you have any doubts, before taking a loan, check with the bank what are the specific terms of loan payments and whether you have a choice.
Step 2. Find out the interest rate on the loan before taking out a loan
The interest rate determines the cost of using borrowed funds. It also forms the basis for calculating the total amount of interest that you pay to the lender for the entire loan period. It is beneficial for you that the interest rate is as low as possible. Even 0.5% of the difference can represent a huge amount in monetary terms. If you choose to pay lower loan amounts, you will likely need to lengthen the loan term, agree to a higher interest rate (because of the increased risk for the lender) and pay more interest, but thereby reduce the financial burden on your monthly budget. This option of lending is preferred by people with less savings and those whose salaries are highly dependent on bonuses and commissions. Nevertheless, we recommend that you try to find a loan option in which the interest rate on the loan will not exceed 10%. At present, in Russia, interest on various types of loans, on average, fluctuates within the following limits:
-
Car loan.
7-14% per annum.
-
Mortgage.
6-10% per annum.
-
Consumer loan.
10-15% per annum.
-
Credit cards.
from 0% during the grace period and up to 80% after it.
-
Microloans and microloans.
0, 2-2, 5% per day. These types of loans are dangerous enough if you cannot repay them within 1-2 weeks. Interest rates and the maximum amount of interest charges on microloans and microloans are currently regulated by the state and cannot exceed a certain amount.
Step 3. Find out the frequency of accrual of interest on the loan
Technically speaking, the frequency of interest charges determines the amount of interest paid to the lender. The more often the interest is calculated, the more amount you need to pay in the end, since you will actually have less time to pay off interest and prevent the growth of their amount. For example, if you took out a loan in the amount of 100,000 rubles at a preferential rate of 4% per annum, then the total amount of payments on the loan may be different with different frequency of interest accrual:
- when charged annually - 110412, 17 rubles;
- when charged monthly - 110512, 24 rubles;
- at daily accrual - 110521, 28 rubles.
Step 4. Use longer loan terms to pay smaller monthly payments, taking into account the fact that in the end you will have to pay more
The loan term determines the period during which you commit to repay the loan. Again, loan terms depend on the specific types of loans, and you should choose a loan option for yourself where the maturity will suit your needs. If you are not sure that you will be able to repay a shorter-term loan with a higher amount of monthly payments, you can always pick up a longer loan with lower monthly payments, but a larger total amount. A longer loan term usually means an increase in the total amount of interest paid, but allows for lower monthly loan payments. Let's say that you take out a car loan in the amount of 200,000 rubles at 5% per annum. The amount of monthly annuity payments for different loan terms will be as follows:
- with a loan for a period of 24 months you will pay only 10,583 rubles of interest, but at the same time you will pay the bank a monthly amount of 8,744 rubles;
- with a loan for a period of 30 months you will pay 13176 rubles of interest, but the amount of monthly payments will be 7106 rubles.
- with a loan for a period of 36 months you will pay 15790 rubles, and monthly payments will be 5994 rubles.
Method 2 of 3: Calculate Interest Payments Manually
Step 1. Learn the formula for calculating compound interest
Despite the huge number of online calculators for calculating payments and interest on loans, understanding the principle of these calculations plays an important role in making an informed decision on the implementation of a loan. To calculate the payments and interest on a loan, you need to use a mathematical formula that looks like this: Payment = Loan principal ∗ i (1 + i) n (1 + i) n − 1 { displaystyle { text {Payment}} = { text {Loan principal}} * { frac {i (1 + i) ^ {n}} {(1 + i) ^ {n} -1}}}
,
- Like most other financial formulas, this formula looks so intimidating only outwardly, but in fact the calculations for it are not so difficult. Once you understand how to fit your data into a formula, actually calculating your monthly payments is easy.
- Let's say you managed to get a loan at 4.5% per annum, and the lending terms assume monthly loan payments.
- Since you make payments on the loan on a monthly basis, you need to divide the annual interest rate by 12. If 4.5% (or 0, 045 in decimal) is divided by 12, you get 0, 00375. This value must be substituted in the formula instead of "i".
- Continuing the above example, let's say that the initial loan amount is $ 100,000. In this case, the formula with the substituted data will look like this: 100000 ∗ 0, 00375 (1 + 0. 00375) 360 (1 + 0. 00375) 360−1 { displaystyle 100000 * { frac {0, 00375 (1+ 0, 00375) ^ {3} 60} {(1 + 0, 00375) ^ {3} 60-1}}}
- 100000 ∗ 0.0375 (1.00375) 360 (1.00375) 360−1 { displaystyle 100000 * { frac {0.00375 (1.00375) ^ {3} 60} {(1 +0, 00375) ^ {3} 60-1}}}
-
<figure class =" image" />
- 100000 ∗ 0.00375 (3.84769 ….) (1 + 0.0375) 360−1 { displaystyle 100000 * { frac {0.0375 (3.84769 ….)} {(1 + 0, 00375) ^ {3} 60-1}}}
-
<figure class =" image" />
- 100000 ∗ 0.01442….. (1 + 0. 00375) 360−1 { displaystyle 100000 * { frac {0. 01442…..} {(1 + 0. 00375) ^ {3} 60 -1}}}
-
<figure class =" image" />
- 100000 ∗ 0.01442 ….. (1.00375) 360−1 { displaystyle 100000 * { frac {0.01442 …..} {(1.00375) ^ {3} 60-1}} }
-
<figure class =" image" />
- 100000 ∗ 0, 01442…..3, 84769…..− 1 { displaystyle 100000 * { frac {0, 01442…..} {3, 84769…..- 1}}} </ li >
-
<figure class =" image" />
- 100000 ∗ 0, 01442…..2, 84769….. { displaystyle 100000 * { frac {0, 01442…..} {2, 84769…..}}}
-
<figure class =" image" />
- 100000 ∗ 0.0506685….. = 506.69 { displaystyle 100000 * 0.00506685….. = 506.69}
-
<figure class =" image" />
- 506, 69 rubles
is your monthly loan payment.
-
Using the above example, you should multiply 506, 69 by 360, and you get 182,408 rubles.
This is the total amount of payments on the loan for its entire term.
-
Subtract the initial 100,000 rubles of the loan from this amount, and you will have 82408 rubles.
The latter value reflects the amount of interest that you will need to pay for using the loan.
- - 100000 - the principal amount of the loan.
- - 100000 - the principal amount of the loan.
- 360 - the number of loan payments.
- - 100000 - the principal amount of the loan.
- 360 - the number of loan payments.
- 4.5% 12 = 0.775% = { displaystyle { frac {4.5 \%} {12}} = 0.375 \% =}
-
<figure class =" image" />
- monthly interest rate.
- When manually entering a function into a string, omit the quotes.
- If you are a confident user of Excel, you can make the program automatically perform calculations using references to cells with source data.
-
For the above example, the complete entry in the cell should look like this: "= PMT (0, 00375; 360; -100000; 0)".
- The last argument must be zero. He says that by the end of all payments, the balance of payments should be zero.
- Remember to close the parentheses after entering the arguments.
-
In this example, this will be the sum RUB 506.69
This is exactly the size of the monthly payment on this loan.
- If you see the "#NUM!" Error in a cell or some other incorrect result, it means that you entered the function or its arguments incorrectly. Double-check the input line and try to fix the shortcomings.
-
In this example, multiplying 506, 69 by 360 gives 182,408 rubles.
This is the total amount of payments on the loan for the entire loan term.
-
In this example, you need to subtract 100,000 rubles from 182,408 rubles. As a result, you will get RUB 82408
This is the total amount of interest for the entire loan term.
- Understanding the principle of calculating loan payments allows you to weed out inappropriate loan options and choose exactly those conditions that really suit you.
- If you have a variable income, it is likely that the best choice may be a loan, not necessarily with the lowest rate, but with a longer loan term and less frequent and not so large payments, despite the fact that in total you will have to pay more interest on it. …
- If you have a good permanent income that leaves you with a lot of free funds, it is probably wiser to use a loan with a favorable rate, with a shorter term and higher monthly payments, as this will ensure a decrease in the total amount of interest over the entire term of the loan.
Step 2. Adjust the interest rate to the frequency of your loan payments
Before substituting numbers in the formula, adjust the "i" interest rate to the frequency of the loan payments made.
Step 3. Determine the total number of loan payments
To find out what number to substitute in the formula instead of "n", you need to calculate the total number of loan payments that you will need to make for the entire loan period.
Let's say that you have to repay the loan every month for 30 years. To find the total number of loan payments, simply multiply 30 by 12. You have 360 payments
Step 4. Calculate the amount of the monthly annuity payment
To find out the amount of the monthly loan payment, you just have to substitute the data into the formula. The upcoming calculations may seem complicated, but if you proceed step by step, you will quickly cope with the calculations and find out the result. Below are the step-by-step calculations of the monthly payment amount for the above example.
<figure class =" image" />
Step 5. Calculate the total amount of interest on the loan
Now that you know the amount of monthly payments, you can find out the amount of interest that you will need to pay for the entire period of using the loan. Multiply the total number of payments by the monthly payments. Then subtract the original loan amount from the result.
Method 3 of 3: Calculate Interest Payments Using Excel
Step 1. Enter the loan principal, term and interest rate in one column of the Excel spreadsheet
If you enter information on the loan amount, loan term and interest rate in separate cells of the Excel spreadsheet, the program will help you to make further calculations of monthly payments. Further in the text, for convenience, the example below will be considered.
The loan amount is 100,000 rubles. The loan term is 30 years, and the annual interest rate is 4.5%
Step 2. Enter the value of the initial loan amount minus
Excel should treat this figure as your debt. To do this, it should be made negative and, apart from the minus and the numbers themselves, no more signs denoting the currency should be entered.
Step 3. Specify the number of loan payments
If you wish, you can specify the loan term in years without translating it into the number of months, but then the calculation will be made on an annual basis, and not monthly. And since loans are usually paid on a monthly basis, you need to multiply the loan term by 12 months to calculate the total number of monthly payments. Write the result in the cell below.
Step 4. Convert the interest rate according to the number of loan payments per year
In this example, we know the annual interest rate that applies to the whole year. However, the payments on the loan must be made on a monthly basis, so it is necessary to find out the monthly interest rate. Since the 4.5% rate corresponds to 12 months, just divide it by 12 to calculate the monthly interest rate. When you're done, don't forget to represent the percentages as a decimal.
<figure class =" image" />
Step 5. Use the "= PMT ()" function to calculate the annuity payments for the loan
Excel has a ready-made formula for calculating monthly interest payments on a loan. To make calculations, you only need to substitute your data into it. Click on an empty cell in the table, then find the formula bar to manually enter the formula into the cell. It is located in the toolbar directly above the table to the right of the button labeled "fx". Click on the line and start typing the function into it "= PMT ("
Step 6. Enter the function arguments in the correct order
In parentheses, indicate the data required to calculate the annuity payments, separating them with semicolons. In the above example, after the name of the function, you need to enter the following: "(interest rate; number of periods; initial loan amount; 0)".
Step 7. Press the "Enter" key to display the result of calculating annuity payments in the cell
If you entered all the arguments of the function correctly, the result of the calculation will appear in the corresponding cell of the table.
Step 8. Calculate the total amount of the loan payments by multiplying the monthly payment by the total number of payments
To find out the total amount of loan payments, you only need to multiply the monthly payment by the number of payments for the entire loan term.
Step 9. Calculate the total interest by reducing the total loan payments by the original loan amount
If you want to know how much interest you will need to pay for using the loan, you need to perform the simplest subtraction operation. Decrease the total amount of the loan payments by the original loan amount.
Calculation table for automatic calculation of interest payments
The table below explains how to calculate interest payments in Excel, in the Google Sheets application or in another spreadsheet program for any parameters. Just fill it in with your own data. Note that where Fx = { displaystyle Fx =}
, input should be made through the formulas input line above the table to the right of the button" "google=" />
| A | B | C | D | |
|---|---|---|---|---|
| 1 | [Loan principal] | [Total number of payments] | [Annual interest rate] | [Monthly interest rate] |
| 2 | Minus the loan amount (-100000) | Total number of payments in months (360) | Annual Percentage Rate as Decimal (0.05) | Monthly interest rate (divide the annual rate by 12) |
| 3 | Monthly payment | FX = PMT (D2; B2; A2; 0). NOTE: The last argument of the formula is zero. | ||
| 4 | Total payments | FX = PRODUCT (D3; B2) | ||
| 5 | The total amount of interest on the loan | FX = SUM (D4; A2) |