Online overpayment loan calculator. How to independently calculate the interest on the loan - the formula. Annual interest rate

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:

1. Annuity assumes that the customer pays the same amount every month.
2. With a differentiated scheme of debt repayment to 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

Monthly amount 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 interest rate per 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:

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 payment on credit;
• NEO - loan balance;
• PP - the number of periods remaining until the end of 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:

The balance of the loan debt: in the first month is equal to 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: loan balance in current period multiply by the monthly interest rate, which is divided by 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.

Compare the overpayment for 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:

Calculate the monthly interest rate and loan payments:

Fill in the table of the form:

The commission is taken monthly from the entire amount. The total loan payment is 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);
• the 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 accurate to the third decimal place using 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:

To calculate the full cost of the loan, you need to make a payment schedule (order see above).

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:

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.

UCS by new formula coincided with the annual interest rate on the loan.

Thus, to calculate annuity loan payments, the simplest PMT function is used. As you can see, a differentiated repayment method is somewhat more complicated.

Credit calculator Vyberu.ru - your faithful assistant in the selection of lending programs in Moscow. With the help of our service, you can quickly pick up a loan online and assess the likely costs. Preparing for a loan will allow you not to be in a situation where delays put too much pressure on your budget and threaten to ruin your relationship with the bank.

How does the calculator work?

The easiest way to calculate a loan is to use a loan calculator online. It applies the parameters you set to all programs, creates a schedule of monthly installments, and also places it on the page best offersthat fit your requirements.

To use the service:

• Enter the amount you need and select the loan currency;
• Set a convenient maturity;
• Enter the interest rate you are counting on;
• And the estimated date of issue;
• Choose a payment scheme - annuity (that is, in equal shares) or differentiated payments;
• Are you going to close the loan as quickly as possible? Add early repayments.

When you click on the button “Calculate expenses”, the loan calculator will calculate the loan according to your parameters and present a summary of it. It indicates the amount of monthly payments, total amount and the amount of accrued interest. And in the graph also presented on the page, you will see which part of the installment will go to the main debt, and which part - to the cost of the loan, as well as the remaining balance after each payment.

Annuity or differential payment?

What are the repayment schemes and which one is more profitable? The size of your monthly installments, the total overpayment on the loan and what the payment consists of depends on what scheme is used.

The annuity scheme implies that payments are calculated in equal shares for the entire duration of the contract. Thus, if you took a loan for 12 months, then every month you will pay the same amount. However, there is an important point: at first, most of the contribution consists of interest accrued to you. That is, first you pay the bank for the service provided, and only then the cost of the loan.

Differentiated payment is calculated differently. Its size will change every day, and the shares of the main debt and interest in it are the same. The calculation is carried out so that the debt body (that is, the amount you received) is divided by the number of months, and the cost of the service is calculated on the balance and recounted after each installment.

This scheme is considered the most beneficial for the borrower, because it turns out that the overpayment will be negligible. But there is also a minus - at the beginning of the term payments will be the largest, which means they can become a serious burden on the budget of the borrower.

Which payment scheme to choose? It depends only on how you evaluate your financial abilities. If your monthly income allows you to give back enough large amounts to pay off debt, and you want to save on interest, feel free to choose a bank with a differentiated calculation scheme. If your income does not allow this, it is better to overpay - so you reduce the risk of delinquency and interest.

A loan calculator is one of the most convenient and fastest ways to calculate the main indicators of a consumer loan, knowing which, it is easy to evaluate the conditions of several banks and choose the most favorable. Using it, you can see current offers 2020, pre-calculate the loan payment and evaluate your financial capabilities even before contacting the bank.

Loan calculation

• the availability of guarantors and certificates confirming income;
• form of issuing money (cash or card);
• experience;
• age;
• availability of collateral;
• the term for considering an online loan application.

Additionally, you can specify a specific bank in which you would like to take a loan. In addition, advanced settings allow you to find financial institutions that give money to people with poor credit history, beneficiaries and citizens with temporary registration. All presented options can be sorted by size. annual interest and loan amount.

How to calculate the monthly loan payment

To see detailed information about a particular loan product, you must click on its name. A page opens indicating the conditions for obtaining and repaying a loan, as well as its main parameters, such as:

• amount;
• type of payment;
• currency;
• amount of monthly payments;
• interest rate;
• total cost;
• payment schedule;
• list of required documents;
• borrower age.

Using the loan repayment calculator, you can see the preliminary monthly calculation with the date of payments, their size and the balance of the debt. Here you can calculate the overpayment on the loan.

Calculator consumer loan makes calculations according to the annuity payment formula. This method assumes that the loan amount and interest on it are repaid in equal installments over the entire loan term. The monthly amount will not change even if the client pays amounts in excess of the amount established by the contract. The calculation of interest on the loan according to the annuity scheme is used in most banks in Russia.

Everyone faced the problem of lack of money for the purchase of household appliances or furniture. Many have to borrow to pay. Some prefer not to go to friends or relatives with their financial problems, but to contact the bank right away. Moreover, a huge amount is offered loan programsthat allow you to solve the problem of buying expensive goods on favorable conditions.

This system economic relationsproviding for the transfer of values \u200b\u200bfrom one owner to another for temporary use on special conditions. In the case of banks, money is such a value. A person needs a certain amount, an economist evaluates the solvency of the client and makes a decision. If everything is in order, provided necessary funds for a certain period. For this, the client pays the bank interest.

To buy goods or need cash? It’s worth taking a loan. A low percentage always attracts customers. Therefore, popular financial institutions provide credit cards and cash loans on favorable terms. A loan formula) will help you figure out how much the bank will have to pay for the service.

Overpayment

In the case of a loan in a bank, money acts as a commodity. For the provision of services, the client must pay a fee to the financial institution. To understand how the amount of overpayment is calculated, it is worth understanding the following concepts:

• loan body;
• commission;
• annual interest rate.

The repayment system, as well as the loan term, are important. This will be described below.

What is a loan body?

The amount that a person borrowed from the bank, this is the body of the loan. As payments are made, this amount decreases. It is on the loan body that interest and, in most cases, commissions are accrued.

Consider an example. The client executed a loan agreement on May 1 in the amount of 20,000 rubles. A month later he contributed minimum payment in the amount of 2000 rubles. Of this amount, 500 rubles were spent to repay the interest on the loan, and 1,500 rubles to repay the body. Thus, on June 1, the loan body decreased to 18,500 rubles. In the future, all interest will be accrued on exactly this amount.

Commission

The percentage that the client gives to the bank in excess of this is the commission. Different financial institutions may offer different loan conditions. The commission can be charged both on the loan body and on the amount that the client borrowed initially. Recently, many banks refuse to commission at all and set only the annual interest rate.

Consider an example with a fixed commission of 0.5%. The client took a loan in the amount of 10,000 rubles. The monthly commission in this case will be Formula (calculation of interest on the loan) looks like this: 10 000: 100 X 0.5.

If the commission is not fixed, it is charged on the balance of the debt (loan body). This option is more beneficial for the client, since the amount of interest is constantly decreasing. As a rule, a commission is charged on the balance of the debt as of the last business day of the month. That is, if the client paid the full amount on the 28th day, and the last business day falls on the 30th, you will not have to pay a commission.

Annual interest rate

In the absence of a commission for loan agreement The annual rate will be the basis for calculating the overpayment. Interest is always charged on the balance of the debt. The faster the client repays the loan, the less he will have to overpay.

How much interest does the loan provide? Different banks offer their terms. It is possible to take money at a rate of 12% to 25%. Next, it will be described how the calculation of interest on the loan (formula) is carried out. Example: a client took a loan in the amount of 10,000 rubles. The annual rate under the contract is 15%. On the day the client will overpay 0.041% (15: 365). Thus, in the first month you will have to pay the amount of interest in the amount of 123 rubles.

10 000: 100 x 0,041 \u003d 4 rubles 10 kopecks - the amount of overpayment per day.

4.1 x 30 \u003d 123 rubles / month. (provided that the month is 30 days).

Let's consider further. The client made the first payment in the amount of 500 rubles. There is no commission under the contract. 123 rubles will go to interest, 377 rubles - repayment of the body. The rest of the debt will amount to 9623 rubles (10,000 - 377). This is the body of the loan, on which interest will be charged in the future.

How to quickly calculate overpayment on a loan?

It is difficult for a person who is far from the financial sphere to make any calculations. Many banks offer a loan calculator for customers, which allows you to quickly calculate the overpayment under the contract. All you need to do is enter the amount of debt, the expected payment period and the annual interest rate on the institution’s website. After a few seconds, you will be able to find out the amount of the overpayment.

The loan calculator is an auxiliary tool that allows you to tentatively calculate the amount of the expected overpayment. The data is not accurate. The amount of overpayment depends on the amount of funds that the client will deposit, as well as on the loan repayment term.

What are the loan repayment systems?

There are two options for repaying a loan. Classic provides for the payment of a certain part of the loan body and interest rate. Example: a client decided to take a loan for a year in the amount of 5000 rubles. According to the conditions, the annual rate is 15%. A monthly loan body will have to be paid in the amount of 417 rubles (5000: 12). The formula (calculation of interest on the loan) will look like this:

5000: 100 x 0.041 \u003d 2 rubles 05 kopecks - the amount of overpayment per day.

2.05 x 30 \u003d 61 rubles 50 kopecks (provided that there are 30 days in a month) - the amount of overpayment per month.

417 + 61.5 \u003d 478 rubles 50 kopecks - the amount of the required minimum payment.

In the classical repayment system, the amount of payments decreases every month, since interest is accrued on the remaining debt.

The annuity system provides for the repayment of the loan in equal installments. Initially, a fixed minimum payment amount is set. As the debt is paid, most of the money goes to repay the loan body, since the interest overpayment decreases.

Consider an example. The client decided to take a loan for 10 years in the amount of 100,000 rubles. The annual rate is 12%. Overpayment per day 0.033% (12: 365). The formula (calculation of interest on the loan) will look like this:

100 000: 100 x 0.033 \u003d 33 rubles - the amount of overpayment per day.

33 x 30 \u003d 990 rubles - the amount of overpayment per month.

The minimum payment can be set at 2,000 rubles. At the same time, in the first month, 1,100 rubles will go to repay the loan body, then this amount will decrease.

Penalties

If a bank client does not fulfill its debentures, a financial institution has the right to charge a fine. The conditions must be described in the contract. A fine can be presented either in the form of a fixed amount or in the form of an interest rate. If according to the agreement penalties are provided in the amount of 100 rubles, for example, the amount of the next minimum payment will not be difficult to calculate. It is only necessary to add 100 rubles.

Things are more complicated if penalties are charged in the form of an interest rate. As a rule, the calculation is based on the amount of debt for a certain period. For example, a client had to make a minimum payment of 500 rubles before May 5, but did not. According to the contract, the penalty is 5% of the amount owed. The next payment will be calculated as follows:

500: 100 x 5 \u003d 25 rubles - the amount of the fine.

Until June 5, the client will need to pay 1025 rubles (two minimum payments of 500 rubles and 25 rubles fine).

Summarize

It is not difficult to calculate interest on a loan yourself. One has only to carefully study the terms of the contract and use the formulas described above. Special credit calculators, which are presented on official websites, make the task easier financial institutions. It is worth remembering that only an approximate calculation is made. The exact amount may depend on many factors, such as the loan term, the amount of payments, etc. The shorter the loan term, the less the overpayment.

Through loan calculator You can independently, online, calculate regular loan payments and determine which repayment system will be optimal. Simple formulas help to understand how much money you spend on paying off debt, and how much you give back for using borrowed funds as interest. You can check the results using a conventional calculator.

Credit online calculator allows you to calculate annuity and differentiated payments. Annuity payments are made every month in equal installments, consisting of the loan amount and interest on it. With differentiated payments, monthly payments are consistently reduced, since interest is accrued only on the outstanding part of the loan. Most commercial banks practice annuity, and Sberbank of Russia offers a differentiated form.

Differentiated payment

With a differentiated scheme, the size of payments at the beginning is much larger than the final. The difference is due to the fact that payments consist of two parts:

• fixed - debt repayment amount;
• decreasing - percent of the remaining amount.

A constantly decreasing second part reduces the size monthly payments. The formula by which you can determine the size of the fixed part is extremely simple: you need to divide the loan amount by the number of loan months:

OD \u003d SK / KP

(OD - the main debt; SK - the amount of the loan; KP - the number of periods)

Further calculations are somewhat more complicated, since two approaches are used. Some banks proceed from the fact that there are 12 months in a year and calculate the loan interest according to the formula:

NP \u003d OK × PS / 12

(NP - accrued interest, OK - loan balance, PS - annual interest rate)

Other banks prefer, given that there are 365 days in a year, considering this approach more accurate. Calculation formula:

NP \u003d OK × PS × ChDM / 365

(NP - accrued interest; OK - loan balance; PS - interest rate for the year; ChDM - the number of days in a month (ranges from 28 to 31).

Calculation Example

With a loan of 100,000 rubles, taken for a year, the payment schedule according to the formula that takes into account 12 months and 20% per annum, is as follows:

Annuity payment

Payments under the classical system are repeated monthly, and can only be changed upon early repayment of the loan or by agreement with the bank. As in the previous case, the contributions consist of the principal repayment amount and interest on the loan. The ratio of these components changes over time: the percentage decreases, and the loan repayment amount increases. Thus, interest on annuity payments is higher than on differentiated payments. This is explained by the fact that interest is accrued on the remainder of the amount, and it decreases slowly. The difference is especially noticeable if the loan is repaid ahead of schedule, because in the first installments a significant part of the amount falls on interest.

Formula for calculating the payment:

AP \u003d SK × PS / 1 - (1 + PS) - KP \u003d SK × PS / 1 / (1 + PS) KP \u003d SK (PS × (PS + PS / (1 + PS) KP - 1

(AP - annuity payment; PS - interest rate; SK - loan amount; KP - the number of periods).

For monthly payments of KP in this formula - the number of months for which the loan is scheduled, PS - 1/12 of the annual interest rate.

This formula is classic, most banks use this particular scheme.

Calculation Example

Consider a repayment schedule for a loan taken for 12 months in the amount of 1,000 rubles. In some banks, the first payment on a loan is not annuity, in this case, the calculation formula looks like this:

AP \u003d SK × PS / 1 - (1 + PS) 1 - KP \u003d SK × PS / 1 - 1 / (1 + PS) KP-1 \u003d SK × (PS + PS / (1 + PS) KP-1 - 1)

(AP - annuity payment; PS - interest rate; SK - initial loan amount; KP - the number of periods).

The first loan payment period may be full or incomplete, and in this case he is not intuitive. If the period is incomplete, the first installment may be less than the annuity, but at high interest rates, a full period of 31 days and a long-term loan, it is possible that it will exceed the established amount.

Sometimes banks apply the formula with the first and last non-annuity payments:

AP \u003d SK × PS / 1 - (1 + PS) 2 - KP \u003d SK × PS / 1 - 1 / (1 + PS) KP-2 \u003d SK (PS + PS / (1 + PS) KP-2 - 1 )

In the calculations using this formula, the first and last installments are not annuity, that is, in the first month only interest must be paid, and in the last month, the balances. Thus, banks are trying to adjust the amount of payments to an integer, as a result, there remains a “tail” that goes to the last payment. In case of early repayment, the reduced amount of the balance also changes the size of the “tail”, which can increase or decrease.

According to the last formula, the payment is the largest, and according to the classical first formula, the smallest. The difference becomes especially noticeable if, by the final settlement, the payment amount remains minimal. This is important for early repayment of the loan.

Which scheme is more profitable?

• With an annuity, the size of payments does not change, and with a differentiated scheme, it constantly decreases.
• A differentiated system involves larger payments at the beginning of loan repayment.
• An annuity is usually more convenient for borrowers, as the amount of repayments is clear and determined for the entire loan term.
• With a differentiated scheme, income should be 25% higher than with an annuity.
• The main debt under the annuity decreases slowly, and the interest on the loan is high. Prepayment loan leads to the loss of interest already paid.
• A differentiated system is not associated with loss of interest, even if the loan is repaid ahead of schedule.
• It is much more difficult to get a loan according to the differentiated scheme, as banks try to make sure that the credit is payable. Big income the borrower needs to be able to make higher payments at the beginning of loan repayment.