Partial prepayment loan calculator. Loan calculator online
This consumer credit calculator Home Credit Bank, Gazprombank, Ugra Bank is designed to calculate new and existing loans at the bank. The calculator considers not only the loans of this bank. It is universal and will be able to calculate the loan of any other bank. If you need a loan, you can.
Calculator Operation Procedure
To calculate an existing consumer loan, you need to enter the amount, rate, period in months, date of the first payment on the loan and type of payments. Home Bank mainly annuity payments. Differentiated payments include Sberbank and Gazprombank.
In the additional parameters for the calculation, you can set the weekend accounting - if you set this flag, the calculation will be carried out with the transfer of the monthly payment from the weekend to the first working day.
The second flag - the first payment is only interest. Usually it is relevant for mortgages. This is when the issue date and the first payment date do not match. You have to pay a percentage for using the bank’s money from the entire amount for several days.
Features of calculating early repayment of a consumer loan.
The calculator allows you to calculate the loan, taking into account the early repayment of your loan. If you pay about the amount that includes the next payment, you need to subtract the value of the monthly payment from this amount. This will be the amount for net prepayment. It will be taken into account and displayed on the chart.
The calculator has the option of calculating with setting the amount of prepayment of the monthly payment to reduce the amount. This is a reference function. Those. Let's say every month you pay in addition to the next payment a certain amount from above. What will be your debt and your payment in a year? - this calculator will help to answer this question.
Currency selection
The calculator calculates without currency. Those. This is a universal calculator for any of the currencies. You just need to enter your details and click calculation. Data can be taken from a loan agreement or from Home Credit Internet Banking. Everything is simple - we take, enter and count. Calculations can be exported to MS Excel format. If you are unable to make a calculation, I recommend leaving your questions in the comments and they will certainly help you.
Banks and microcredit financial organizations offer more and more credit programs that have hidden advantages and disadvantages. In this article we will look at how to calculate the monthly loan payment using a loan calculator and choose the best offer on the loan market. Having received a profitable loan, a person has the opportunity to translate his plans into reality, which require large financial costs: the acquisition of real estate, a vehicle, household appliances, education, and much more.
Most often, people in Russian realities refuse loans, believing that this is monetary slavery, which is very difficult to get out of. The loan calculator allows you to find out what the loan repayment will be and calculate the monthly payment. Our site is financially independent from banks, does not have a percentage from interested potential customers, and does not provide questionable microcredit services with crazy interest.
Some banks give only beautiful advertising on the issuance of a loan, and nobody will be interested in how the loan will be paid. We recommend using a loan calculator to determine the amount of payment for each month, given the type of loan: annuity or differential. Our calculator is based on the official algorithms of the Central Bank, which will allow you to calculate the payment as accurately as possible and make the right, informed choice.
Credit calculator
Monthly payment is calculated on the basis of three irreplaceable data: loan amount, interest rate and loan term. Knowing these data, you can already calculate the monthly installment for a loan proposal and pre-plan your loan.
If you are just planning a loan - this data will be enough for an approximate calculation of the monthly payment. You can enter them both in the advanced version of the calculator on this page, or use the simple version of the loan calculator consisting of 3 steps. This is the best way to avoid the eternal expectation of a telephone support specialist and settle online.
How to calculate the monthly loan payment?
Enter the loan amount, specify the loan interest rate for the year or month, enter the loan term. Consider your financial capabilities, do not strive to plan a large loan amount for a short period, it is profitable for a bank to issue loans for a long period. This allows you to maintain the current activity of banking activities, as well as to receive the greatest profit, having minimal risks.
You take 1 000 000 rubles for 12 months at 20 percent per annum and annuity type of loan. We write obvious figures for a simple understanding of the overpayment, which for this loan offer will amount to 111.614 rubles. And this is with the annuity type of lending, which is more beneficial to the bank than to you. Overpayment just some 11%
You took 1,000,000 rubles in cash for 60 months or five years at 20 percent per annum. Type of lending annuity and differentiated. The overpayment on this loan will amount to 589,633 rubles or almost 59% for the annuity type of loan and 508,333 rubles for the differentiated one. Why so much? The bank will surely say that it carries great risks with long-term lending, but will never say that it is only profitable.
You took a mortgage, the amount of 2,000,000 rubles, for a 240 month or 20 year loan at a low interest rate of 9.9% per annum. The overpayment for such a loan will amount to 2 628 924 rubles. Sberbank began to approve the mortgage for 30 years. Not only because it is convenient for you, but it is convenient especially for the bank.
Add one-time and monthly commissions, select the payment date. Refer to the one-time commissions life insurance, which you will be obligated to impose. This trend persists at the end of 2017, the whole of 2018 and the beginning of 2019. The size of the one-time commission can be different, from 3 to 11% of the loan amount. Monthly commissions used to include early repayment commissions and a lot of junk commissions that remain today.
A striking example of a monthly commission is the account maintenance fee. Some little-known banks abused this and considered it from the amount of the principal debt, and not from the amount of the monthly payment. So it turned out 70-80% per annum.
Use the tabs early repayment and variable interest rateif relevant to you. For example, Raiffeisen Bank offered to reduce interest rates by 4% for the second year of lending. Early repayment of the loan remains an urgent problem today. We recommend that you review partial prepayment in this article.
By pressing the “ Calculate”, You will get full monthly payment schedule on a loan, taking into account early repayment and a variable interest rate. Print, save or compare the payment schedule with the schedule received at the bank. This is the best way to avoid financial risks and save the family budget.
Pros of a loan calculator
The loan calculator is able to calculate the monthly fee for a loan or a credit card thanks to powerful algorithms and updated statistics. Using the calculator, you can easily make calculations for several years or months, analyze the overpayment amount, calculate the residual payment for early repayment of the loan and much more. An advanced free tool will calculate not only the loan payment for each month, but also will calculate the mortgage, deposit, car loans.
In the article, we examined how to calculate the monthly loan payment using the online loan calculator. The counting process will not take much time and will take no more than 5 minutes. Despite the fact that the final results may slightly differ from the actual calculations in the bank, a loan calculator can be a useful tool that allows you to find out at any time how much a loan will cost a potential borrower.
Excel is a universal analytical and computational tool that is often used by lenders (banks, investors, etc.) and borrowers (entrepreneurs, companies, individuals, etc.).
Quickly navigate in sophisticated formulas, calculate the interest, payment amounts, overpayment allows the functions of the Microsoft Excel program.
How to calculate loan payments in Excel
Monthly repayments depend on the loan repayment scheme. Distinguish between annuity and differentiated payments:
- Annuity assumes that the customer pays the same amount every month.
- With a differentiated scheme of debt repayment to a financial institution, interest is accrued on the balance of the loan amount. Therefore, monthly payments will decrease.
An annuity is more often used: more profitable for a bank and more convenient for most customers.
Calculation of annuity loan payments in Excel
The monthly annuity payment is calculated by the formula:
A \u003d K * S
- A - loan payment amount;
- K - annuity payment ratio;
- S - loan amount.
The formula for the annuity ratio:
K \u003d (i * (1 + i) ^ n) / ((1 + i) ^ n-1)
- where i is the interest rate for the month, the result of dividing the annual rate by 12;
- n - loan term in months.
Excel has a special function that counts annuity payments. This is a PMT:
![](https://i1.wp.com/exceltable.com/otchety/images/otchety17-3.png)
The cells turned red, a minus sign appeared in front of the numbers, because we will give this money to the bank, lose.
Payments calculation in Excel according to differentiated repayment scheme
A differentiated payment method assumes that:
- the amount of the main debt is distributed over the payment periods in equal shares;
- interest on the loan is accrued on the balance.
Differential Payment Calculation Formula:
DP \u003d NEO / (PP + NEO * PS)
- DP - monthly loan payment;
- NEO - loan balance;
- PP - the number of periods remaining until the maturity date;
- PS - interest rate for the month (annual rate is divided by 12).
We draw up a repayment schedule for the previous loan in a differentiated way.
The input is the same:
Draw up a loan repayment schedule:
![](https://i1.wp.com/exceltable.com/otchety/images/otchety17-4.png)
The balance of the loan debt: in the first month equals the whole amount: \u003d $ B $ 2. In the second and subsequent ones, it is calculated by the formula: \u003d IF (D10\u003e $ B $ 4; 0; E9-G9). Where D10 - current period number, B4 - loan term; E9 - loan balance in the previous period; G9 - the amount of the main debt in the previous period.
Interest payment: the loan balance in the current period is multiplied by the monthly interest rate, which is divided into 12 months: \u003d E9 * ($ B $ 3/12).
Repayment of the main debt: the total loan amount divided by the term: \u003d IF (D9
Final payment: the sum of “interest” and “principal” in the current period: \u003d F8 + G8.
We introduce the formulas in the corresponding columns. Copy them to the whole table.
![](https://i0.wp.com/exceltable.com/otchety/images/otchety17-5.png)
Compare the overpayment with an annuity and differentiated loan repayment scheme:
The red number is annuity (they took 100,000 rubles), the black one is the differentiated way.
Excel loan interest calculation formula
We will calculate the interest on the loan in Excel and calculate the effective interest rate, having the following information on the loan offered by the bank:
![](https://i1.wp.com/exceltable.com/otchety/images/otchety17-7.png)
Calculate the monthly interest rate and loan payments:
Fill in the table of the form:
![](https://i1.wp.com/exceltable.com/otchety/images/otchety17-9.png)
The commission is taken monthly from the entire amount. The total loan payment is an annuity payment plus commission. The amount of the principal debt and the amount of interest are components of the annuity payment.
Principal amount \u003d annuity payment - interest.
Interest amount \u003d debt balance * monthly interest rate.
The balance of the main debt \u003d the balance of the previous period - the amount of the main debt in the previous period.
Based on the table of monthly payments, we calculate the effective interest rate:
- took a loan of 500,000 rubles .;
- returned to the bank - 684 881.67 rubles. (the sum of all payments on the loan);
- overpayment amounted to 184 881, 67 rubles .;
- interest rate - 184,881, 67 / 500,000 * 100, or 37%.
- A harmless commission of 1% cost the borrower very expensive.
The effective interest rate of the loan without commission is 13%. Counting is carried out in the same way.
Calculation of the full cost of a loan in Excel
According to the Law on Consumer Credit, a new formula is now applied to calculate the total cost of a loan (CPM). UCS is determined as a percentage with an accuracy of the third decimal place according to the following formula:
- UCS \u003d i * NWP * 100;
- where i is the interest rate of the base period;
- NWP - the number of base periods in a calendar year.
Take for example the following loan data:
![](https://i0.wp.com/exceltable.com/otchety/images/otchety17-10.png)
To calculate the full cost of the loan, you need to make a payment schedule (see the procedure above).
![](https://i0.wp.com/exceltable.com/otchety/images/otchety17-11.png)
You need to determine the base period (BP). The law says that this is the standard time interval that occurs most often in the repayment schedule. In the example, BP \u003d 28 days.
Now you can find the interest rate of the base period:
![](https://i0.wp.com/exceltable.com/otchety/images/otchety17-12.png)
We have all the necessary data - we substitute them in the UCS formula: \u003d B9 * B8
Note. To get interest in Excel, you do not need to multiply by 100. It is enough to set the percentage format for the cell with the result.
CPM according to the new formula coincided with the annual interest rate on the loan.
Thus, to calculate annuity payments on a loan, the simplest PMT function is used. As you can see, a differentiated repayment method is somewhat more complicated.
Have you been dreaming about the house itself for a long time, want to go on a trip or get funds for a big purchase? Then a completely obvious decision would be to take a loan.
Banks offer a variety of lending programs. Choosing the right one among them helps a loan calculator. You need to enter only a few basic data, and you will see a table with information about the amount of overpayment, payment schedule, etc. Thanks to this convenient online calculator, choosing the best option is much easier.
Credit calculator
Many banks offer a loan in advance. This can be done using special calculators. For example, if you are going to take a loan at VTB 24 and want to find out how much you have to pay each month, you can make the necessary calculations directly on the bank's website. To do this, you just need to use the VTB 24 loan calculator.
Working with him is very simple. You only need to fill in the necessary fields, such as the loan amount, annual interest rate, payment period in months, etc. After clicking on the “Calculate” button, the loan calculator will calculate everything and you will see a table showing how much and in what month you have to pay the bank.
Such an online loan calculator may include a different number of windows for various information. For example, the loan calculator of Sberbank is somewhat different from that offered by VTB 24. In order to calculate the future loan size, Sberbank customers need to enter the following information:
- preferred type of currency;
- average monthly income;
- type of calculation;
- down payment amount and other data, which may vary depending on the selected loan program.
Sometimes it is more convenient to use a loan calculator on third-party resources that provide information about several banks at once. By using this feature, you become a more prepared borrower. After all, this is how you get more information about when and how much you will need to pay. This helps to choose the optimal loan program and calculate the amount that you can confidently repay on time.
Mortgage calculator
The calculation of mortgage payments can be carried out according to a differentiated or annuity scheme. With differentiated payments, the largest payments are expected in the first years, their amount is gradually decreasing every month. In turn, the annuity scheme is designed for equal payments over the entire loan term.
The benefits of each of these schemes are constantly debated. Some people think that annuity payments are more convenient, while others say that they lead to a greater overpayment than calculating by a differentiated scheme.
In the end, we can say that there is no universal option. Many families making a mortgage loan simply can not afford the calculation according to a differentiated scheme, but for someone, on the contrary, it is the best option that will allow you to have free funds in the middle of the loan term.
In any case, it is better to consult with specialists about choosing a suitable scheme for calculating mortgage payments. In addition, you can use the mortgage calculator, which will provide a clear payment scheme for each option. It is important to do so that the mortgage is a good chance to purchase your own home, and not a heavy financial obligation.
The loan calculator calculates monthly payments, interest on the loan, payments on commissions and insurance. A payment schedule is drawn up with an indication of the amounts to be considered for payments. The loan calculator can calculate payments by annuity or differentiated method. The results on the right show the amount of the monthly payment, interest overpayment, overpayment including commissions, total cost of the loan.
Pay particular attention to the effective interest rate, which, taking into account additional fees and insurance, can be significantly higher than that offered in the loan agreement.
Loan Calculator Settings
Calculation Method
It is possible to calculate the loan and payments, both by the loan amount, and by the cost of purchase and down payment. When calculating a loan at the cost of purchase, the amount of the loan is first calculated, with no interest and commission on the down payment.
Choosing a loan currency
A loan calculator can calculate a loan online in one of 3 currencies: rubles, dollars or euros.
Credit term
By default, the loan term must be entered in months. The term can be entered in years, but you must change the type of loan term.
Interest rate
Traditionally, the interest rate is calculated on the basis of interest / year. By changing the settings of the loan calculator, you can calculate payments based on the monthly interest rate.
Type of payments
Typically, banks use an annuity method for calculating loan payments (equal monthly payments) to calculate a loan. However, the second option is also possible - differentiated payments (interest accrual on the balance). Using the drop-down menu, select the type of payment calculation you need. For more information about the types and methods of calculation, see the sections annuity calculator or differential payments calculator.
Additional settings
Extradition Fee
One of the conditions for issuing a loan by many banks is to pay a fee when issuing or issuing a loan. The loan calculator can take such a commission into account in the total cost of the loan and, if necessary, break down the commission into monthly payments.
Monthly commission
It is taken into account in the total cost of the loan and in monthly payments.
Insurance
Credit insurance is an additional option of a monthly commission. As a rule, banks do not take insurance into the schedule of monthly payments and charge a similar commission on the basis of an additional contract. However, the total cost of the loan received may increase significantly. The online loan calculator takes into account monthly insurance in the total cost of the loan and in the amount of the monthly payment.
Last installment
One of the loan options is a loan with the last installment. When calculating such a loan, the monthly payment is lower due to a decrease in repayments on the principal debt. However, interest on the last installment is also charged and taken into account in monthly payments.
date of issue
By default, the current date is used, but you can choose any convenient one. The function is convenient when working with a payment schedule.
First payment date
Initially, the current date is used, for the convenience of working with the payment schedule, select the necessary one.