Formula for calculating an annuity payment in Excel

Loan payments are easier and faster to calculate with Microsoft Office Excel. Much more time is spent on manual calculation. This article will focus on annuity payments, the features of their calculation, advantages and disadvantages.

What is an annuity payment

A method of monthly repayment of a loan, in which the deposited amount does not change during the entire period of the loan. Those. on certain dates of each month, a person deposits a specific amount of money until the loan is fully repaid.

Moreover, the interest on the loan is already included in the total amount paid to the bank.

Classification annuity

Annuity payments can be divided into the following types:

1. Fixed. Payments that do not change have a fixed rate regardless of external conditions.
2. Currency. The ability to change the amount of payment in case of a fall or rise in the exchange rate.
3. indexed. Payments depending on the level, inflation indicator. During the loan period, their size often changes.
4. Variables. Annuity, which may change depending on the state of the financial system, instruments.

Pay attention! Fixed payments are preferable for all borrowers, because have little risk.

Advantages and disadvantages of annuity payments

To better understand the topic, it is necessary to study the key features of this type of loan payments. It has the following advantages:

• Establishing a specific amount of payment and the date of its payment.
• High availability for borrowers. Almost anyone can apply for an annuity, regardless of their financial situation.
• The possibility of lowering the amount of the monthly installment with an increase in inflation.

Not without drawbacks:

• High rate. The borrower will overpay a larger amount of money compared to the differential payment.
• Problems arising from the desire to pay off the debt ahead of schedule.
• No recalculations for early payments.

What is the loan payment?

Annuity payment has the following components:

• Interest paid by a person when paying off a loan.
• Part of the principal amount.

As a result, the total amount of interest almost always exceeds the amount contributed by the borrower to reduce the debt.

Basic Annuity Payment Formula in Excel

As mentioned above, in Microsoft Office Excel you can work with various types of payments for loans and advances. Annuity is no exception. In general, the formula with which you can quickly calculate annuity contributions is as follows:  

Important! It is impossible to open brackets in the denominator of this expression to simplify it.

The main values ​​​​of the formula are deciphered as follows:

• AP – annuity payment (the name is abbreviated).
• O – the size of the principal debt of the borrower.
• PS – the interest rate put forward on a monthly basis by a particular bank.
• C is the number of months the loan lasts.

To assimilate the information, it is enough to give a few examples of the use of this formula. They will be discussed further.

Examples of using the PMT function in Excel

We give a simple condition of the problem. It is necessary to calculate the monthly loan payment if the bank puts forward an interest of 23%, and the total amount is 25000 rubles. Lending will last for 3 years. The problem is solved according to the algorithm:

1. Make a general spreadsheet in Excel based on the source data.

2. Activate the PMT function and enter arguments for it in the appropriate box.
3. In the “Bet” field, enter the formula “B3/B5”. This will be the interest rate on the loan.
4. In the line “Nper” write the value in the form “B4*B5”. This will be the total number of payments for the entire term of the loan.
5. Fill in the “PS” field. Here you need to indicate the initial amount taken from the bank, writing the value “B2”.

6. Make sure that after clicking “OK” in the source table, the value “Monthly payment” was calculated.

Additional Information! A negative number indicates that the borrower is spending money.

An example of calculating the amount of overpayment on a loan in Excel

In this problem, you need to calculate the amount that a person who has taken a loan of 50000 rubles at an interest rate of 27% for 5 years will overpay. In total, the borrower makes 12 payments per year. Solution:

1. Compile the original data table.

2. From the total amount of payments, subtract the initial amount according to the formula «=ABS(ПЛТ(B3/B5;B4*B5;B2)*B4*B5)-B2». It must be inserted into the formula bar at the top of the main menu of the program.
3. As a result, the amount of overpayments will appear in the last line of the created plate. The borrower will overpay 41606 rubles on top.

The formula for calculating the optimal monthly loan payment in Excel

A task with the following condition: the client has registered a bank account for 200000 rubles with the possibility of monthly replenishment. It is necessary to calculate the amount of payment that a person must make every month, so that after 4 years he has 2000000 rubles in his account. The rate is 11%. Solution:

1. Create a spreadsheet based on the original data.

2. Enter the formula in the Excel input line «=ПЛТ(B3/B5;B6*B5;-B2;B4)» and press “Enter” from the keyboard. The letters will differ depending on the cells in which the table is placed.
3. Check that the contribution amount is automatically calculated in the last line of the table.

Pay attention! Thus, in order for the client to accumulate 4 rubles at a rate of 2000000% in 11 years, he needs to deposit 28188 rubles every month. The minus in the amount indicates that the client incurs losses by giving money to the bank.

Features of using the PMT function in Excel

In general, this formula is written as follows: =PMT(rate; nper; ps; [bs]; [type]). The function has the following features:

1. When monthly contributions are calculated, only the annual rate is taken into consideration.
2. When specifying the interest rate, it is important to recalculate based on the number of installments per year.
3. Instead of the argument “Nper” in the formula, a specific number is indicated. This is the payment period.

Payment calculation

In general, annuity payment is calculated in two stages. To understand the topic, each of the stages must be considered separately. This will be discussed further.

Stage 1: calculation of the monthly installment

To calculate in Excel the amount that you need to pay each month on a loan with a fixed rate, you must:

1. Compile the source table and select the cell in which you want to display the result and click on the “Insert function” button on top.

2. In the list of functions, select “PLT” and click “OK”.

3. In the next window, set the arguments for the function, indicating the corresponding lines in the compiled table. At the end of each line, you need to click on the icon, and then select the desired cell in the array.

4. When all the arguments are filled in, the appropriate formula will be written in the line for entering values, and the calculation result with a minus sign will appear in the field of the “Monthly payment” table.

Important! After calculating the installment, it will be possible to calculate the amount that the borrower will overpay for the entire loan period.

Stage 2: payment details

The amount of overpayment can be calculated monthly. As a result, a person will understand how much money he will spend on a loan every month. Detail calculation is performed as follows:

1. Create a spreadsheet for 24 months.

2. Place the cursor in the first cell of the table and insert the “OSPLT” function.

3. Fill in the function arguments in the same way.

4. When filling in the “Period” field, you need to refer to the first month in the table, indicating cell 1.

5. Check that the first cell in the column “Payment by the body of the loan” is filled.
6. To fill all the rows of the first column, you need to stretch the cell to the end of the table

7. Select the “PRPLT” function to fill in the second column of the table.
8. Fill in all the arguments in the opened window in accordance with the screenshot below.

9. Calculate the total monthly payment by adding the values ​​in the previous two columns.

10. To calculate the “Balance payable”, you need to add the interest rate to the payment on the body of the loan and stretch it to the end of the plate to fill all the months of the loan.

Additional Information! When calculating the remainder, dollar signs must be hung on the formula so that it does not move out when stretched.

Calculation of annuity payments on a loan in Excel

The PMT function is responsible for calculating the annuity in Excel. The principle of calculation in general is to perform the following steps:

1. Compile the original data table.
2. Build a debt repayment schedule for each month.
3. Select the first cell in the column “Payments on the loan” and enter the calculation formula “PLT ($B3/12;$B$4;$B$2)”.
4. The resulting value is stretched for all columns of the plate.

Calculation in MS Excel repayment of the principal amount of the debt

Annuity payments must be made monthly in fixed amounts. And the interest rate does not change.

Calculation of the balance of the principal amount (with BS=0, type=0)

Suppose that a loan of 100000 rubles is taken for 10 years at 9%. It is necessary to calculate the amount of the principal debt in the 1st month of the 3rd year. Solution:

1. Compile a datasheet and calculate the monthly payment using the above PV formula.
2. Calculate the share of the payment required to pay off part of the debt using the formula «=-PMT-(PS-PS1)*item=-PMT-(PS +PMT+PS*item)».
3. Calculate the amount of the principal debt for 120 periods using a well-known formula.
4. Using the HPMT operator find the amount of interest paid for the 25th month.
5. Check result.

Calculating the amount of principal that was paid between two periods

This calculation is best done in a simple way. You need to use the following formulas to calculate the amount in the interval for two periods:

• =«-BS(item; con_period; plt; [ps]; [type]) /(1+type *item)».
• = “+ BS(rate; start_period-1; plt; [ps]; [type]) /IF(start_period =1; 1; 1+type *rate)”.

Pay attention! Letters in parentheses are replaced with specific values.

Early repayment with reduced term or payment

If you need to reduce the loan term, you will have to perform additional calculations using the IF operator. So it will be possible to control the zero balance, which should not be reached before the end of the payment period.

To reduce payments, you need to recalculate the contribution for each previous month.

Loan calculator with irregular payments

There are several annuity options where the borrower can deposit variable amounts on any day of the month. In such a situation, the balance of the debt and interest are calculated for each day. At the same time in Excel you need:

1. Enter the days of the month for which payments are made, and indicate their number.
2. Check for negative and positive amounts. Negative ones are preferred.
3. Count the days between two dates on which money was deposited.

Calculation of the periodic payment in MS Excel. Term deposit

In Excel, you can quickly calculate the amount of regular payments, provided that a fixed amount has already been accumulated. This action is performed using the PMT function after the initial table has been compiled.

Conclusion

Thus, annuity payments are easier, faster and more efficient to calculate in Excel. The PMT operator is responsible for their calculation. More detailed examples can be found above.