Annuity calculator. What does an annuity payment on a loan mean - a breakdown of the concept and calculation formula How interest is calculated on annuity payments
Updated: March 3, 2020
Example fileCalculate inMSEXCELthe amount of the regular annuity payment when repaying the loan. We will do this both using the PMT() function, and directly using the annuity formula. We will also compile a table of monthly payments with a breakdown of the remaining part of the debt and accrued interest.
When lending, banks often use along with. The annuity scheme provides for repayment of the loan by periodic equal payments (usually monthly), which include both the repayment of the principal debt and the interest payment for using the loan. This equal payment is called an annuity. The annuity repayment scheme assumes immutability interest rate loan throughout the entire repayment period.
Task1
Determine the amount of monthly equal payments on a loan, the amount of which is 100,000 rubles, and the interest rate is 10% per annum. The loan was taken for a period of 5 years.
We understand what information is contained in the task:
- The borrower must make monthly payments to the bank. This payment includes: amount to repay part of the loan and the amount to pay interest accrued over the past period on the balance of the loan ;
- The amount of the monthly payment (annuity) constant and does not change throughout the entire period, as well as the interest rate. Also, the order of payments does not change - 1 time per month;
- Amount to pay interest accrued for the past period decreases each period, because interest is charged only on the outstanding part of the loan;
- As a consequence of paragraphs 3 and 1, the amount paid to repay the principal amount of the loan, increases from month to month.
- The borrower must make 60 equal payments (12 months in a year * 5 years), i.e. 60 periods in total (Nper);
- Interest accrues in the end of each period (unless otherwise stated, this is exactly what is meant), i.e. argument Type=0. Payment must also be made at the end of each period;
- The interest for the use of borrowed funds per month (for the period) is 10%/12 (rate);
- At the end of the term, the debt should be equal to 0 (BS=0).
The calculation of the amount of payment on the loan for one period, we will first make using financial function MS EXCEL PMT() .
Note. An overview of all annuity functions in the article.
This function has the following syntax: PMT(rate; nper; ps; [bs]; [type]) PMT(rate, nper, pv, , ) is the English version.
Note: The PMT() function is included in the Analysis ToolPak add-in. If this function is not available or returns the #NAME? error, then enable or install and download this add-in (in MS EXCEL 2007/2010, the "Analysis Pack" add-in is enabled by default).
The first argument is Rate. This is the interest rate for the period, i.e. in our case for a month. Rate \u003d 10% / 12 (12 months in a year). Nper is the total number of annuity payment periods, i.e. 60 (12 months a year * 5 years) Ps - all cash flows of the annuity. In our case, this is the loan amount, i.e. 100,000. Bs - all cash flows of the annuity at the end of the term (after the expiration of the number of periods Nper). In our case, Bc = 0, because the loan must be fully repaid at the end of the term. If this parameter is omitted, then it is considered =0. Type is a number 0 or 1 indicating when the payout should be made. 0 - at the end of the period, 1 - at the beginning. If this parameter is omitted, then it is considered = 0 (our case).
Note: In our case, interest is calculated at the end of the period. For example, after the first month, interest is charged for using the loan in the amount of (100,000 * 10% / 12), until this moment the first monthly payment must be made. In the case of interest accrual at the beginning of the period, no interest is accrued in the first month, because there was no real use of the loan funds (roughly speaking,% should be accrued for 0 days of using the loan), and the entire first monthly payment goes to repay the loan (principal amount).
Solution1 So, the monthly payment can be calculated by the formula =PMT(10%/12; 5*12; 100,000; 0; 0), the result is -2 107.14 rubles. The minus sign shows that we have multidirectional cash flows: +100000 is the money that bankgave us, -2107.14 is the money that we we return to the bank .
Alternative formula for calculating the payment (general case): =-(Ps*rate*(1+ rate)^ Nper /((1+ rate)^ Nper -1)+rate /((1+ rate)^ Nper -1)* Bs)*IF(Type;1/(rate +1);1)
If the interest rate = 0, then the formula will be simplified to = (Ps + Bs) / Nper If Type = 0 (payment at the end of the period) and BS = 0, then Formula 2 is also simplified:
The above formula is often referred to as the annuity (annuity payment) formula and is written as A=K*S, where A is the annuity payment (i.e. PMT), K is the annuity ratio, and S is the loan amount (i.e. .PS). K=-i/(1-(1+i)^(-n)) or K=(-i*(1+i)^n)/(((1+i)^n)-1), where i=rate per period (i.e. Rate), n - number of periods (i.e. Nper). We remind you that the expression for K is valid only when BS=0 (full repayment of the loan for the number of periods Nper) and Type=0 (interest accrual at the end of the period).
Table of monthly payments
Let's make a table of monthly payments for the above problem.
To calculate the monthly amounts going to repay the principal amount of the debt, the function OSPLT(rate; period; nper; ps; [bs]; [type]) is used with almost the same arguments as PMT() (for more details, see the article). Because the amount going to repay the principal amount of the debt changes from period to period, then one more argument is needed period, which determines which period the amount belongs to.
Note. To determine the amount of overpayment on a loan (the total amount of interest paid), use the TOTAL PAYMENT() function, see .
Of course, you can use either MPMT() or OSMT() to compile the monthly payment table, because these functions are connected and in any period: PMT = OSPLT + MPPT
The ratio of payments of the principal amount of the debt and accrued interest is well demonstrated by the graph given in the example file.
Note. The article shows how to calculate the amount of the regular deposit replenishment amount in order to accumulate the desired amount.
The payment schedule can be calculated without using annuity formulas. Graph shown in columns K:P Annuity sheet sample file (PLT), as well as on Annuity sheet (no PMT). Also, the body of the loan at the beginning and end of the period can be calculated using the function PS and BS (see. sample file Annuity sheet (PMT), columns H:I).
Task2
Loan of 100,000 rubles. taken for a period of 5 years. Determine the amount of quarterly equal payments on the loan so that after 5 years the unpaid balance is 10% of the loan. The interest rate is 15% per annum.
Solution2 The quarterly payment can be calculated using the formula =PMT(15%/12; 5*4; 100,000; -100,000*10%; 0), the result is -6 851.59 rubles. All parameters of the PMT() function are selected similarly to the previous task, except for the value of BS, which = -100000 * 10% = -10000 rubles, and requires explanation. To do this, we return to the previous problem, where PS = 100000, and BS = 0. The found value of the regular payment has the property that the sum of the values \u200b\u200bgoing to repay the loan body for all periods of payments is equal to the value of the loan with the opposite sign. Those. the equality is true: PS + SUM (share of PMT going to repay the loan body) + BS \u003d 0: 100,000 rubles + (-100,000 rubles) + 0 \u003d 0. The same for the second task: 100,000 rubles + (-90,000 rubles) + BS \u003d 0, i.e. BS=-10000r.
Annuity (annuity payments)- a method of repaying a loan in equal periodic payments (usually monthly). At the same time, the part of the annuity payment amount going to repay the principal amount of the loan is gradually growing, and the part of the amount going to repay the interest is decreasing. Alternative to annuity payments - differentiated payments, in which a fixed amount is paid to repay the loan plus interest on the balance of the principal amount of the loan. At the same time, the total monthly payment amount is gradually reduced.
The amount of annuity payments is calculated based on the loan amount, loan term and interest rate using annuity ratio.
See also:
Annuity ratio
A = P * (1+P) N / ((1+P) N -1), where
A - annuity ratio;
P is the interest rate expressed in hundredths per period. For example, for the case of 12 percent per annum and a monthly payment, this would be 0.12/12 = 0.01;
N is the number of loan repayment periods.
The formula for calculating the loan. The formula for calculating the amount of an annuity payment
Sa = A * K, where
A - annuity ratio;
K is the loan amount.
The formula for calculating the loan. The total amount of payments for the annuity method of loan repayment
S = N * Sa = N * A * K, where
A - annuity ratio;
K is the loan amount.
The amount of interest (overpayment) for the annuity method of repayment
Sp=S-K=N*A*K-K=
(N*A - 1) * K , where
N is the number of loan repayment periods;
A - annuity ratio;
K is the loan amount.
The formula for calculating the loan. Example.
Mortgage loan for 10 years in the amount of 1,000,000 rubles at 12 percent per annum with monthly payments.
In this case, the number of repayment periods N = 10 * 12 = 120, the interest rate per period P = 0.12 / 12 = 0.01.
Annuity ratio:
A = 0.01 * (1+0.01) 120 / ((1+0.01) 120 -1) =
0.01 * 1.01 120 / (1.01 120 -1) =
0.01*3.3003867/2.3003867 = 0.0143471
Annuity payment amount:
Sa \u003d 0.0143471 * 1,000,000 \u003d 14347.1 rubles.
Total repayment amount (loan calculation formula):
S \u003d 120 * 14347.1 \u003d 1,721,652 rubles.
Interest amount (overpayment):
Sp \u003d 1,721,652 - 1,000,000 \u003d 721,652 rubles.
Many borrowers, reading the terms of lending on the bank's website, have no idea how to calculate the monthly loan payment, overpayment and other loan parameters. However, everything is quite simple, it is enough to know the formulas for calculating the loan.
The vast majority of banks offer loans on equal (annuity) payments. This means that the amount of the monthly payment will not change during the entire payment period, which is very convenient for the borrower. The monthly payment on the loan consists of the cost of interest and part of the principal repayment. At the same time, at first, most of the payment is interest, the share of which decreases every month, increasing the amount of repayment of the principal debt.
Loan calculation formula
The basis of all formulas for calculating a loan with annuity payments is the so-called annuity ratio. On its basis, in the future, all other parameters of the loan are considered. The formula for calculating the annuity ratio:A = P * (1+P) N / ((1+P) N -1)
A - annuity ratio;
P - interest rate coefficient, calculated according to the formula P = C/1200 , where C - interest rate in annual terms, specified by the bank.
N is the loan repayment term in months.
Formula for calculating loan payments
Calculate the monthly loan payment can be quite easy, knowing the annuity ratio. For this, the formula is applied:Sa = A*K
Sa - monthly loan payment;
A - annuity ratio;
K - loan amount.
To calculate the full cost of the loan (calculate the total amount of debt), you must use the formula:
S = N * Sa
N - term of payments on the loan in months;
Sa - monthly loan payment.
Then you can easily calculate the overpayment for using the loan (calculation of the amount of interest on the loan):
Sp = S - K
Sp - overpayment on the loan;
S - the sum of all payments on the loan;
K - loan amount.
Here, in fact, are the basic formulas for calculating a loan. If you know the allowable monthly payment and the maximum loan amount, then from the above formulas you can derive a formula for calculating the loan interest rate in order to select suitable bank offers for this parameter.
To quickly calculate the overpayment on a loan and see the detailed structure of payments, you can use ours. You can also, where, by substituting the values of the loan amount, interest rate and repayment period, you will find out the monthly payment, the full cost of the loan and the overpayment.
Let's give an example of applying formulas. For example, Vasya wants to take a loan in the amount of 120 thousand rubles at 24% per annum for a year. The interest rate coefficient will be P = 24/1200 = 0.02. The annuity ratio is A = 0.02 * (1 + 0.02) 12 / ((1 + 0.02) 12 - 1) = ~0.094571. Thus, the monthly loan payment is: Sa = 0.094571 * 120000 = 11,348.52. Based on this, you can calculate the total amount of debt: S = 11348.52 * 12 = 136,182.24, as well as overpayment on the loan: Sp = 136,182.24 - 120,000 = 16,182.24. Of course, these data have a small error, because in the calculation we rounded the annuity coefficient. To get more accurate results, you need to use a calculator.
Loan calculator with early repayment
In the early repayment section, you can draw up a plan for such repayments. Some banks often impose penalties associated with the payment of such a payment. In the commission section, you can set the appropriate parameters and thereby determine exactly how profitable early repayment will actually be.
Loan Calculator Report in Excel
The loan calculator will calculate the total cost of the loan - a value calculated as a percentage, which takes into account commissions, related payments and the time of their payment. This makes it possible to compare loans with a variety of fees.
Accounting for inflation in loan payments
By setting the expected inflation parameters of the loan calculator, you can estimate the costs, taking into account the real purchasing power of money over time.
Dependence of the overpayment, the amount of the monthly payment on the parameters of the loan
Analysis of graphs of dependence of loan parameters allows you to choose the most comfortable conditions for a loan. By clicking on the point of interest on the diagram, you can start a more detailed calculation for the parameter selected on the graph.
Annuity or differentiated payment
With annuity payments during the entire repayment period, the amount of monthly payments is the same, while in the initial period, debt repayment is slower, since accrued interest on the loan has to be paid. This type of loans is most common in Russia. The scheme with differentiated payments involves, at the initial stage, the payment of large monthly amounts, which will become smaller with each subsequent time. The debt is repaid in equal installments over the entire period, but the amount of accrued interest changes. The total amount of overpayments in absolute terms is greater with an annuity scheme, however, it is important not to forget about inflation, especially for long-term loans. In conditions of high inflation, this scheme becomes significantly more profitable, in the context of the purchasing power of money. Those. You will be able to purchase more goods and services for the entire period of loan repayments.