Excel is a universal analytical and computing tool that is often used by lenders (banks, investors, etc.) and borrowers (entrepreneurs, companies, individuals, etc.).

The functions of the Microsoft Excel program allow you to quickly navigate complex formulas, calculate interest, payment amounts, and overpayments.

How to calculate loan payments in Excel

Monthly payments depend on the loan repayment scheme. There are annuity and differentiated payments:

1. An annuity assumes that the client pays the same amount every month.
2. With a differentiated scheme for repaying debt to a financial organization, interest is charged on the balance of the loan amount. Therefore, monthly payments will decrease.

An annuity is used more often: it is more profitable for the bank and more convenient for most clients.

Calculation of annuity payments on a loan in Excel

The monthly annuity payment amount is calculated using the formula:

A = K * S

• A – loan payment amount;
• K – annuity payment coefficient;
• S – loan amount.

Annuity coefficient formula:

К = (i * (1 + i)^n) / ((1+i)^n-1)

• where i is the monthly interest rate, the result of dividing the annual rate by 12;
• n – loan term in months.

Excel has a special function that calculates annuity payments. This is PLT:

The cells turned red and a minus sign appeared in front of the numbers, because We will give this money to the bank and lose it.



Calculation of payments in Excel according to a differentiated repayment scheme

A differentiated payment method assumes that:

• the amount of the principal debt is distributed over payment periods in equal shares;
• interest on the loan is calculated on the balance.

Formula for calculating differentiated payment:

DP = NEO / (PP + NEO * PS)

• DP – monthly loan payment;
• OBL – loan balance;
• PP – the number of periods remaining until the end of the repayment period;
• PS – monthly interest rate (annual rate divided by 12).

We will draw up a repayment schedule for the previous loan according to a differentiated scheme.

The input data is the same:

Let's create a loan repayment schedule:

Balance of loan debt: in the first month equals the entire amount: =$B$2. In the second and subsequent ones, it is calculated using the formula: =IF(D10>$B$4;0;E9-G9). Where D10 is the number of the current period, B4 is the loan term; E9 – loan balance in the previous period; G9 – the amount of the principal debt in the previous period.

Interest payment: multiply the loan balance in the current period by the monthly interest rate, which is divided by 12 months: =E9*($B$3/12).

Payment of principal: divide the amount of the entire loan by the term: =IF(D9

Final payment: the amount of “interest” and “principal” in the current period: =F8+G8.

Let's enter the formulas in the appropriate columns. Let's copy them to the entire table.

Let’s compare the overpayment for the annuity and differentiated loan repayment schemes:

The red number is an annuity (they took 100,000 rubles), the black number is a differentiated method.

Formula for calculating interest on a loan in Excel

Let's 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:

Let's calculate the monthly interest rate and loan payments:

Let's fill out a table like this:

The commission is taken monthly from the entire amount. The total loan payment is the annuity payment plus commission. The amount of principal and the amount of interest are components of the annuity payment.

Principal amount = annuity payment – ​​interest.

Interest amount = debt balance * monthly interest rate.

Principal balance = balance of the previous period – the amount of the principal debt in the previous period.

Based on the table of monthly payments, we calculate the effective interest rate:

• took out a loan of 500,000 rubles;
• returned to the bank - 684,881.67 rubles. (sum of all loan payments);
• the overpayment amounted to 184,881.67 rubles;
• interest rate – 184,881.67 / 500,000 * 100, or 37%.
• A harmless commission of 1% was very costly for the borrower.

The effective interest rate of the loan without commission will be 13%. The calculation is carried out according to the same scheme.

Calculation of the total cost of a loan in Excel

According to the Consumer Credit Act, a new formula is now used to calculate the total cost of credit (TCC). UCS is determined as a percentage accurate to the third decimal place using the following formula:

• PSK = i * NBP * 100;
• where i is the interest rate of the base period;
• NBP is the number of base periods in a calendar year.

Let's take the following loan data as an example:

To calculate the full cost of the loan, you need to draw up a payment schedule (see procedure above).

It is necessary 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 = 28 days.

Now you can find the base period interest rate:

We have all the necessary data - we substitute them into the UCS formula: =B9*B8

Note. To get percentages in Excel, you do not need to multiply by 100. It is enough to set the percentage format for the cell with the result.

The PSC 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, the differentiated repayment method is somewhat more complicated.

It’s difficult to surprise anyone with loans now. Every average Russian has or has had at least one or two loans in his life or is planning to take one. There are quite a lot of offers on the market; different banks offer their own conditions for lending. A preliminary calculation can be easily done using a loan calculator. You can also calculate your future payments in an Excel spreadsheet. Knowing the general formulas for calculating rates, you can easily calculate a mortgage and consumer loan for any amount and check the accuracy of the bank’s calculations.

What data is needed to calculate the loan?

To calculate the monthly payment in Excel tables, you need to decide on the loan amount, term and rate. As a result, you can create your own repayment schedule, estimate the overpayment and the total amount of payments. Why do you need to calculate all this data yourself when there are automatic calculation systems - loan calculators? The answer is simple - to simply check the correctness of calculations in a credit institution, to calculate the presence of additional services that may be included in the payment without the client’s knowledge. This, unfortunately, happens quite often. Let's explain the basic terms:

• The repayment schedule is a list of monthly loan payments, reflecting the annuity payment, the amount of interest paid, the amount of the principal debt, the balance of the debt, the total overpayment and the amount of payments. This document is an integral part of the loan agreement. The schedule also reflects additional services that may be included in the payment. They are included as part of the payment or highlighted in a separate column.
• The monthly payment is the minimum amount of loan payments, which includes the amount of principal, interest, insurance and additional services. Most often, the monthly payment is annuity, but some banks also offer their clients a differentiated payment system.

Calculation of monthly payment.

Mortgage and consumer loan payments are calculated using the formula:

• where n is the loan term
• i – loan rate.

Let's look at the calculations using an example:

• Loan term – 6 months.
• Amount – 100,000 rub.
• Rate – 18%.

Let's calculate the payment using a loan calculator:

Using the PMT formula:

Using the formula:

How to create a payment schedule?

To do this, create a table in Excel with the following columns: Date, Payment, Principal, Interest, Balance.

In order to fill out the Date fields, you need to enter the payment dates in the first two fields yourself, and then move the cursor over the lower right corner until the “cross” icon appears and drag it out for the required period (in our case, 6 months).

We get a filled column with dates.

How to calculate the amount of interest?

The amount of interest will depend on the number of days in the billing period and the remaining principal balance. It will be calculated according to the formula:

Let's calculate the interest on our loan for the first month:

The principal amount will be calculated as the annuity amount minus interest:

OA=Annuity – Interest

The amount of the principal balance will be calculated as the loan amount minus the repaid principal amount for 1 month.

OD balance = Loan amount – OD for 1 month.

For the second month, the indicators will be calculated in the same way, only in the formula, instead of the loan amount, you need to substitute the balance of the principal debt. The number of days in a period is also calculated by subtracting the current date from the previous one.

As you can see, we have a balance left after the 6th payment. To prevent this from happening, banks charge the final payment a little more or a little less than the rest.

Often, the calculations may not coincide with those offered by the bank. This is due to the fact that settlement principles may differ between banks. This is quite legal, so for fun you can ask about the calculation formula used in the selected bank and compare your calculations. Banks sometimes count the number of days between dates differently or take into account weekends when making calculations

Accrual of interest on a loan is a direct cost borne by the bank client, and any forced costs limit the borrower to something else, perhaps more necessary. Therefore, preliminary calculation of loan payments is so important - so that the monthly payment does not become burdensome for you and you can calmly pay off the loan on schedule.

Of course, the easiest way is to calculate the overpayment on a loan using an online calculator, and most will do just that without thinking about the accuracy of the calculations. But we are sure that among our readers there will be not only humanists and we suggest delving into mathematics to understand how the loan is calculated.

How to calculate interest on a loan?

The calculation of bank interest on loans directly depends on the selected option:

• Annuity
• Differentiated payment
• Direct interest

Annuity

In most cases, banks use the annuity scheme, as it is the easiest to understand. And really, where is it easier? Pay the same amount every month and never have a problem. This principle is good for those who do not think about the benefits of early repayment and will not even try to understand how to calculate the annual interest on a loan - after all, there is a graph in front of your eyes in which “something is written.”

However, most often the bank does not give the client a choice as to which scheme to use, and many clients become forced users of the annuity loan.

How to calculate the annuity payment on a loan?

An annuity is a formula for calculating a mortgage loan or consumer loan, which involves making payments in equal installments. What we are used to is paying the same amount every month. This makes it easier for a person to plan a budget.

An annuity includes 2 main components:

• Interest
• Main debt

These parts are awarded in unequal proportions. At the beginning of the term, the bank includes mostly its interest in the payment, in the middle of the term the figures approximately equalize, and at the end we already feel a reduction in the principal debt.

You can see how interest is calculated on a loan with an annuity scheme using a live example:

How is the interest rate calculated on a loan with an annuity scheme:

• SC – loan term.

This classic formula is used for a trade loan in a store, a consumer loan in cash at any bank.

Speaking of mortgages: AHML, which provides a significant portion of housing loans, uses a slightly different annuity scheme. In it, the first payment differs from the rest due to the first incomplete interest period (less than the standard 31 days).

How to calculate the amount of the monthly loan payment in AHML:

• RAP – the amount of the annuity payment,
• PSK – initial loan amount,
• PGS – annual interest rate,
• SC – loan term.

Sometimes, the last payment may not be equal. If the lender adjusts the amounts to whole units, at the end of the term there will be a “tail” that also does not lend itself to standard formulas. In this case, the following calculation of loan interest is used: formula (example with the same variables):

Differentiated payment

Differential payment involves uneven payments to decrease. Each payment includes:

• Fixed portion of the principal debt;
• Interest on the actual balance (since every month the debt balance decreases, the interest in the payment decreases accordingly).

Example: table for calculating a loan with differentiated payment:

• We take out the remaining principal amount on each payment date.
• The number of interest periods is the number of months until the end of the loan.
• Interest rate is the effective annual rate.

The numbers 100 and 12 in our formula are used to convert the annual rate into interest and determine the interest for 1 calendar month.

In the formula you see 2 parts: one of them is constant, for each month (the part of the principal debt that reduces the amount of debt every month). For example, if we take 12 thousand rubles for 12 months, then this constant will be 1 thousand rubles.

The variable part - interest, depends on the balance of the debt on a specific date. This amount is not constant; it decreases monthly.

Direct (simple) interest

This scheme is used by banks for deposits, and it is also actively used. If you are interested in how to calculate interest on a loan or deposit, the formula for simple interest on loans will definitely be useful to you.

Simple interest involves adding to each payment interest calculated from the original amount. Here you don’t even need a calculator, since you can calculate how much to pay on a loan with a simple interest on your fingers.

Calculation example:

Suppose you took 1000 rubles at 12% per annum. Every month you will give 12/12 = 1% of 1000, that is, 10 rubles. Add part of the principal debt 1000/12 = 83 rubles. The total payment per month will be 93 rubles. It’s even easier to calculate the overpayment: 93*12-1000 = 116 rubles.

Generally, loans are subject to daily interest due to their short-term basis. The microloan is calculated simply: if you are assigned a rate of 2% per day, then at the end of the term you will need to pay:

Principal amount + (principal amount * 2% * number of days).

Interest on deposit - this is a reward paid by the bank to the depositor for the temporary use of his funds. According to the requirement of the Central Bank of the Russian Federation, all credit organizations operating in Russia are required to accrue interest on deposits daily. Formally, this is what happens, but in fact the client receives interest under the terms of the agreement. To understand how to calculate interest on a deposit, it should be borne in mind that banks use two methods of calculating interest: simple And difficult (with a deposit from ).

In the first case, interest is not added to the body of the deposit (the deposited amount), but is transferred to another account of the depositor in accordance with the terms of the agreement. As a rule, income is accrued monthly, quarterly, once every 6 months, once a year or at the end of the deposit period. In the second case, the accrued income is added to the body of the deposit within the terms stipulated by the agreement (usually monthly or quarterly). Since the principal amount of the deposit increases periodically, the interest accrued on it also increases. Ultimately, the overall return on the deposit increases, quite noticeably.

It turns out that with the same nominal interest rate, identical deposit amount and validity period, it brings greater profitability. This must be taken into account when choosing the optimal offer.

Calculation of interest on deposits with simple accrual

S = (P x I x t / K) / 100, Where:

S - amount of accrued interest
P - deposited amount
I — annual interest rate on deposit
t — period for which interest will be calculated, in days
K - number of days in a year (there are also leap years)

Calculation example: Let’s assume that the client has opened a deposit with simple accrual in the amount of 100 thousand rubles for 1 year at 11.5% per annum. It turns out that when closing the deposit, the investor will receive income in the amount of: (100,000 x 11.5 x 365/365)/100 = 11,500 rubles.

Calculation of interest on deposits with capitalization

S = (P x I x j / K) / 100, Where:

S - amount of accrued interest
P - the deposited amount, as well as all subsequent amounts increased as a result of capitalization
I - annual interest on deposit
j is the number of days in the period for which capitalization is carried out,
K - number of days in a year

Calculation example: Let’s assume that the client has opened a deposit with capitalization in the amount of 100 thousand rubles for 3 months (June, July, August) at 11.5% per annum.
Income for June will be: (100,000 x 11.5 x 30 / 365) / 100 = 945 rubles.

We add this amount to 100,000 rubles of the deposit body to calculate the accrued interest for July: (100945 x 11.5 x 31 / 365) / 100 = 985 rubles.
We similarly calculate income for August: (101930 x 11.5 x 31 / 365) / 100 = 995.5 rubles.

As can be seen from the calculation, in August the return on the deposit is higher than in July, although each month has 31 days. This occurs due to the capitalization of interest.

Obtaining a loan from financial institutions has become a fairly common procedure for most Russians. But still not every potential borrower can adequately assess the entire financial burden that he is shouldering. Hence, the percentage of arrears is huge, by world standards - over 17% of debtors have delays in payments exceeding two months.

But in order to determine the actual size of monthly payments, you just need to take a calculator and spend a little time on calculations. This will make it possible to find out the real overpayments for a particular banking offer. However, not many Russians know how to correctly calculate bank interest on a loan.

From a financial point of view, a bank loan is divided into three main parts:

• Body of the credit amount.
• Commission fee.
• Interest rate.

Everyone knows that borrowing money from a bank is not at all the same as borrowing a certain amount from a relative or neighbor “before payday.” Banks are called commercial organizations because they require “remuneration” for their services in the form of commissions and interest. In this sense, money acts as a commodity that brings profit to its owner as a result of its turnover.

The term “body of the loan amount” refers to the size of the loan itself, which the client received in hand. Commissions and interest are calculated on this amount, and in case of delay - penalties.

Example. The borrower received a loan of 20 thousand on January 1. A month later, according to the payment schedule, he made a payment of 2 thousand. Of these, 1.5 thousand went to repay the principal amount, and 500 rubles - to service interest. Accordingly, by February 1 the amount was 18.5 thousand, and interest for the next month will be accrued on this amount. Commission is a part of payments made in favor of a banking organization in addition to accrued interest.

In this case, two types of charges are used:

• For the entire amount that the borrower originally borrowed.
• For the remaining balance of the loan.

In numbers, the financial difference is as follows. A fixed commission amount of half a percent on a loan of 20 thousand will in this case be 100 rubles: 20,000 x 0.5% = 100. If the commission fee is charged on the balance of the credit body, then its size will decrease every month. Of course, provided that the borrower repays his bank debt on time. Commissions are calculated on the last working day of the current month.

Recently, a number of banks, in the fight for clients, have been reducing, or even completely eliminating, charging commissions. In this case, the only overpayment for a punctual borrower will be the interest rate. Unlike commissions, interest is always calculated on the balance of the debt. Accordingly, the faster the debtor pays off the principal debt, the fewer overpayments he has to make.

Different commercial structures offer different rates - from 12% per annum for large federal banks (Sberbank, VTB, Rosselkhozbank) to 2 and even 3% per day for microfinance organizations. The calculation in this case is carried out as follows. Let’s say a client took out a loan of 20 thousand at 15% per annum. Consequently, in the first month his overpayment will be 246 rubles (20,000 rubles x (15%: 12 months)).

However, the example given is a standard or average formula for calculating annual interest on a loan. In fact, there may be several payment options.

The most common method of repaying a bank loan used by credit institutions today is through annuity payments. This method involves paying off the debt in equal shares, divided over the entire term of the loan agreement. Moreover, initially the majority of the payment structure consists of commissions and interest. By the middle of the payment period, they are compared, and at the final stage the debt body is already repaid.

With the annuity payment method, the size of the principal debt practically does not decrease throughout the first half of the lending period. This situation is very beneficial for the bank: if the client suddenly decides to repay the loan early, then even at the end of the contract, the amount of the principal debt will be very significant. And the interest paid in advance, for the entire payment period, in this case remains with the bank.

The annuity payment is calculated using the following, rather complex, formula:
MP = TK x (P + (P: (1+P) x (D – 1)),
Where MP is the monthly payment; TK – loan body, P – interest rate based on one month; D – length of the loan term.

You can calculate what part of the payments goes to paying off the debt, and what part goes to paying off the accrued interest, as follows:
PM = TK x Pg: 12,
Where PM is the interest accrued for the month; TK – the remaining loan body at a given time; Pg – annual interest rate. Next, to find out what part of the payments goes to repay the debt (Pt), we remove the monthly interest (Pm) from the total monthly payment (MP):
Fri = MP – PM.

For clarity, let's give an example. The loan amount is 10 thousand, the interest rate is 15% per annum, the term of the loan agreement is 12 months. That is, the monthly interest rate will be equal to 15%: 12 months: 100 = 0.0125. We substitute the available values ​​into the annuity payment formula:
10,000 (0.0125 + (0.0125: (1 + 0.0125) x (12 – 1) = 1,483 rubles. 3 kopecks monthly payment.

This payment method is used quite rarely by banks today. The first sign of this method of calculating payments is the monthly decreasing amount of monthly payments. With differentiated payments, the principal debt is reduced every month by the same share, and interest charges are made on the remaining amount. As a result, the largest payments are made in the first months of the payment schedule, and the smallest - at the end of the loan term.

This type of loan settlement is used less and less by commercial organizations. The reason is quite clear - such a scheme is less profitable for a commercial organization than an annuity payment. The faster it is repaid, the less interest is charged on it. How to calculate loan interest in this case?

In this case, payments are calculated using a simpler and more understandable formula, which also plays into the hands of bank clients. Interest is accrued monthly on the loan body, each time less due to a decrease in the amount of the principal debt. Many banks offer special online calculators on their official websites. With their help, you can calculate the size of the final overpayments, depending on the loan amount and the interest rate.

In addition to the interest rate and commission charges, penalties may also be the reason for overpayment on the loan. Fines are assessed by the bank, in accordance with the agreement, for late payment. The agreement must clearly state the amount of penalties and the form of their calculation. They can be accrued monthly either in the form of a clearly fixed amount, or in the form of an interest rate on the balance of the debt, or on the original size of the loan.

All details of charging a penalty for violating the payment schedule are specified in the loan agreement. Another thing is that such important details are printed in small print or hidden on the last pages of the document. This is done so as not to scare off the client in advance. But in case of delay in monthly payments, the bank immediately puts this clause of the agreement into effect, adding additional penalties to the total amount of debt.

If the fine is calculated as a fixed amount, then calculating overpayments is not difficult. It is enough to multiply the number of months of delay by the agreed amount of the fine. It is more difficult to calculate the amount of payments for penalties if they are calculated as a percentage of the remaining amount of the loan. To do this, you will have to calculate the balance of the principal debt, and only then find the penalty interest.

In this case, you can only receive an approximate amount; the final amount of payments may be influenced by a number of additional factors.

How to calculate the loan amount and monthly payment?

Post Views: 5