PMT function( ) , English version of PMT(), allows you to calculate the monthly loan payment amount in the case of annuity payments (when the loan is paid in equal installments).
A block of articles devoted to the theory and calculations of annuity parameters. This article only covers the syntax and examples of using the PMT() function.
Function SyntaxPMT()
PMT(rate; nper; ps; [bs]; [type])
- Bid. Interest rate on a loan (loan).
- Nper. The total number of loan payments.
- ps. Amount of credit.
- Bs. Optional argument. The required loan balance after the last payment. If this argument is omitted, it is assumed to be 0 (the loan will be fully refunded).
- Type. Optional argument. Takes the value 0 (zero) or 1. If =0 (or omitted), then it is assumed that the regular payment is made at the end of the period, if 1, then at the beginning of the period (the amount of the regular payment will be slightly less).
The payments returned by the PMT() function include principal and interest payments, but do not include taxes, reserve payments, or fees sometimes associated with a loan.
Example 1
Suppose a person plans to take out a loan in the amount of 50,000 rubles. (cell AT 8 ) in the bank at 14% per annum ( B6 ) for 24 months ( AT 7 ) (see example file).
Calculation of the Monthly payment amount for such a loan using the PMT() function
PLT(B6/12;B7;B8)
ADVICE
:
Be sure to be consistent in your choice of time units for specifying the rate and nper arguments. In our case we calculate monthly payments on a two-year loan (24 months) at the rate of 14 percent per annum ( 14% / 12 months).
Calculation of the Monthly payment amount for such a loan using the WITHOUT function PMT()
B8*(B6/12*(1+B6/12)^B7)/((1+B6/12)^B7-1)
To find the amount of overpayment, multiply the value returned by the PLT() function by “nper” (you will get a number with a minus sign) and add the loan amount. In our case, the overpayment will be RUB 7,615.46. (for 2 years).
Example 2
Suppose a person plans to save money every month in order to save in 5 years (cell E7 ) 1 million rubles ( E8 ). He plans to take the money to the bank every month and replenish his deposit. The bank has an interest rate of 10% ( E6 ) and the person believes that it will remain in effect without changes for 5 years. How much should a person pay to the bank every month in order to save 1 million rubles in 5 years? (see example file).
The PMT function in Excel is included in the “Financial” category. It returns the amount of the periodic payment for an annuity, taking into account the constant payment amounts and interest rate. Let's take a closer look.
Syntax and features of the PMT function
Function syntax: rate; nper; ps; [bs]; [type].
Decoding the arguments:
- The rate is the interest on the loan.
- Nper – the total number of loan payments.
- Ps – present value, equal to a number of future payments (loan amount).
- Fs is the future value of the loan after the last payment (if the argument is omitted, the future value is assumed to be 0).
- Type – an optional argument that specifies whether the payment is made at the end of the period (value 0 or absent) or at the beginning (value 1).
Features of PMT operation:
- Only principal payments and interest payments are included in the calculation of the periodic payment. Taxes, commissions, additional contributions, and reserve payments sometimes associated with a loan are not taken into account.
- When specifying the “Rate” argument, it is necessary to take into account the frequency of interest calculation. For a 6% loan, the quarterly rate is 6%/4; for a monthly rate – 6%/12.
- The argument "Nper" indicates the total number of payments on the loan. If a person makes monthly payments on a three-year loan, then the value 3*12 is used to specify the argument.
Examples of the PMT function in Excel
For the function to work correctly, you must enter the initial data correctly:
The loan size is indicated with a minus sign, because The credit institution “gives” and “loses” this money. To record the interest rate value, you must use the percentage format. If written in numeric form, a decimal number (0.08) is used.
Click the fx button (“Insert function”). The Function Wizard window opens. In the “Financial” category, select the PMT function. Fill in the arguments:
When the cursor is in the field of one or another argument, a “hint” is shown below: what needs to be entered. Since the source data was entered into an Excel table, we used cell references with the corresponding values as arguments. But you can also enter numeric values.
Note! In the “Rate” field, the annual interest value is divided by 12: loan payments are made monthly.
Monthly loan payments in accordance with the conditions specified as arguments amount to RUB 1,037.03.
To find the total amount that needs to be paid for the entire period (principal plus interest), multiply the monthly loan payment by the “Nper” value:
PMT(Bet;Number_per;Ps;[Bs];[Type])
PS
Formula PS used to calculate the present value of an investment. This function is the inverse of the operator PLT. It has exactly the same arguments, but instead of the present value argument ( "PS"), which is actually calculated, the amount of the periodic payment is indicated ( "Plt"). The syntax is accordingly:
PS(Rate;Number_per;Plt;[Bs];[Type])
NPV
The following statement is used to calculate net present value or discounted value. This function has two arguments: the discount rate and the value of payments or receipts. True, the second of them can have up to 254 options representing cash flows. The syntax for this formula is:
NPV(Rate;Value1;Value2;…)
BID
Function BID calculates the interest rate on an annuity. The arguments to this operator are the number of periods ( "Kol_per"), the amount of regular payment ( "Plt") and payment amount ( "Ps"). In addition, there are additional optional arguments: future value ( "Bs") and an indication at the beginning or end of the period that payment will be made ( "Type"). The syntax looks like this:
BET(Number_per;Plt;Ps[Bs];[Type])
EFFECT
Operator EFFECT Calculates the actual (or effective) interest rate. This function has only two arguments: the number of periods in the year for which interest is calculated, as well as the nominal rate. Its syntax looks like this:
EFFECT(Number_rate,Number_per)
We considered only the most popular financial functions. In general, the number of operators from this group is several times larger. But these examples clearly demonstrate the effectiveness and ease of use of these tools, which greatly facilitate calculations for users.
The article discusses the financial functions PLT(), OSPLT(), PRPLT(), NPER(), RATE(), PS(), BS(), as well as GENERAL INCOME() and GENERAL PAYMENT(), which are used to calculate the parameters of the annuity scheme .
This article is part of a series of articles on calculating annuity parameters. List of all articles on our website about annuity.
This article contains a short section on annuity theory, a brief description of annuity functions and their arguments, and links to articles with examples of the use of these functions.
A little theory
An annuity (sometimes the terms “rent”, “financial annuity” are used) is unidirectional cash flow, the elements of which are the same in size and are produced through equal periods of time(for example, when payments are made annually in equal amounts).
Syntax PRPLT (rate; period; nper; ps; bs, type). (2.13)
The function arguments mean: bid
period- specifies the period for which interest payments must be found, the value must be in the range from 1 to “nper”;
nper- the total number of payment periods for the annuity;
ps- present value or total amount that is currently equivalent to a series of future payments, also called the principal amount;
bs- the required value of the future value or balance of funds after the last payment;
type- a number 0 or 1 indicating when the payment should be made. If this argument is omitted, it is set to 0.
If this function is not available or returns the error # NAME?, then install and load the “Analysis package” add-on. To do this, in the menu Service Select team Excel add-ins. From the list of add-ons, select Analysis package and press the button OK. Follow the installer instructions if necessary.
Solution: PRPLT (10% / 12, 1, 12 * 3; 800) = - 6.667 thousand UAH.
Example 2.28. Due to annual contributions over 6 years, a fund of 500 thousand UAH was formed. It is necessary to calculate how much income the investments brought to the owner over the last year, if the annual rate was 17.5%.
Solution: Income for the last year (6 periods) was:
PRPLT (17.5%; 6; 6;; 500) = 66.48110268 thousand UAH.
Annually PMT was due (17.5%; 6;; 500) = - 53.627 thousand UAH.
The amount of the principal loan payment (debt payment), which is repaid in equal payments at the end or at the beginning of each billing period, for the specified period is calculated using the Excel function OSPLT:
Syntax OSPLT (rate; period; nper; ps; bs; type) (2.14)
or is found as the difference between the fixed periodic payment and the interest on the outstanding portion of the loan. The function arguments mean: bid- interest rate for the period;
period- sets the period, the value must be in the range from 1 to "nper";
nper- the total number of periods of annual annuity payments;
ps- present value, that is, the total amount that is equivalent to a number of future payments;
type- a number 0 or 1 indicating when the payment should be made.
Example 2.29. Determine the amount of the principal payment for a two-year loan of 2000 UAH. for the first month at the rate of 10% per annum. Interest is calculated monthly.
Solution: Principal loan payment for the first month:
OSPLT (10% / 12, 1, 2 * 12; 2000) = - 75.62 UAH.
The accumulated loan income (the amount of interest payments), which is repaid in equal payments at the end or at the beginning of each billing period, is calculated in Excel by the GENERAL PAYMENT function between two payment periods.
Syntax GENERAL PAYMENT (rate; nper; ps;
start period; conperiod; type). (2.15)
The function arguments mean: bid- interest rate; nper ps start_period -
end_period -
type- this is the choice of payment time.
Example 2.30. Issued a loan for the purchase of real estate in the amount of 125 thousand UAH. for a period of 30 years at 9% per annum, interest is accrued monthly. Determine the amount of interest payments a) for the second year, b) for the first month.
Solution: The cumulative interest payment for the second year (from the 13th to the 24th period) will be:
GENERAL PAYMENT (9% / 12; 30 * 12; 125,000; 13; 24; 0) = - 11135.23 UAH. One payment for the first month will be:
GENERAL PAYMENT (9% / 12; 30 * 12; 125,000, 1, 1, 0) = - 937.50 UAH. The same value will be obtained when calculating using the formula:
PRPLT (9% / 12, 1, 30 * 12; 125,000) = - 937.50 UAH. In Excel, the TOTAL INCOME function calculates the cumulative (cumulative) amount paid to repay the principal amount of a loan between two periods:
Syntax TOTAL INCOME (rate; nper;
ps; start period; conperiod; type). (2.16)
The function arguments mean:
bid- interest rate;
nper- is the total number of payment periods;
ps- is the cost of the investment at the current moment;
start_period - this is the number of the first period included in the calculation. Payment periods are numbered starting from 1;
end_period - this is the number of the last period included in the calculation;
type- this is the choice of payment time.
Example 2.31. Issued a loan in the amount of 125 thousand UAH. for a period of 30 years at 9% per annum, interest is accrued monthly. Determine the amount of basic payments: a) for the first month; b) second year (payments from the 13th period to the 24th).
solution:
a) TOTAL INCOME (9% / 12; 30 * 12; 125000, 1, 1, 0) = - 68.27827118 UAH;
b) if the loan is repaid in equal payments at the end of each billing period, then the amount of debt payment for the second year will be:
TOTAL INCOME (9% / 12; 30 * 12; 125000; 13; 24; 0) = - +934.1071234 UAH. The periods from the 13th to the 24th constitute the second year.
There are hundreds of online financial planners. All of them are easy to use, but limited in functionality. Compared to them, MS Excel is a real combine. It contains 53 financial formulas for all occasions, and for budget control and planning it is useful to know three of them.
PMT function
One of the most relevant functions with which you can calculate the payment amount for a loan with annuity payments, that is, when the loan is paid in equal installments. Full description of the function.
PLT(rate;nper;ps;bs;type)
- Bid- interest rate on the loan.
- Nper- the total number of loan payments.
- Ps- present value, or the total amount that is currently equivalent to a series of future payments, also called the principal amount.
- Bs- the required value of the future value, or the balance of funds after the last payment. If the argument “bs” is omitted, then it is set to 0 (zero), i.e. for a loan, for example, the value “bs” is 0.
BET function
Calculates the interest rate on a loan or investment based on its future value. Full description of the function.
RATE(nper;plt;ps;bs;type;forecast)
- Nper- the total number of payment periods for the annual payment.
- Plt- payment made in each period; this value cannot change during the entire payment period. Typically, the "plt" argument consists of the principal payment and the interest payment, but does not include other taxes and fees. If omitted, the "ps" argument is required.
- Ps- present (current) value, i.e. the total amount that is currently equivalent to a number of future payments.
- BS (optional argument)- the value of the future value, i.e. the desired balance of funds after the last payment. If "bs" is omitted, it is assumed to be 0 (for example, the future value for a loan is 0).
- Type (optional)- number 0 (zero) if you need to pay at the end of the period, or 1 if you need to pay at the beginning of the period.
- Forecast (optional argument)- estimated bet amount. If the "prediction" argument is omitted, its value is assumed to be 10%. If the BET function does not converge, try changing the value of the "forecast" argument. The BET function usually converges if the value of this argument is between 0 and 1.
EFFECT function
Returns the effective (actual) annual interest rate, given the nominal annual interest rate and the number of periods per year for which compound interest is calculated. Full description of the function
Excel is a truly powerful tool due to its unique versatility and ability to solve problems for people from different professional fields. Excel is indispensable for managers and economists, entrepreneurs and financiers, accountants and analysts, mathematicians and engineers. Its versatility is given by specific built-in functions that certain specialists use in their calculations.
One of the largest and most popular feature categories is financial. The latest version of Excel has 55 functions that fall into this group. Many of them are specific and narrowly focused, but some can be useful to almost everyone. One of these basic functions is PMT.
As the official certificate says,The PMT function returns the periodic payment amount for an annuity based on constant payment amounts and a constant interest rate. If you are confused by the specific term "annuity" - do not be alarmed. In other words, using the PMT function, you can calculate the amount that will need to be paid every month, provided that the interest on the loan does not change and payments are made regularly in equal amounts.
Function Syntax
The function has the following syntax:
PMT(rate; nper; ps; [bs]; [type])
Let's look at all the arguments one by one:
- Bid.Required argument. Represents the interest rate for the period. The most important thing here is not to make a mistake in recalculating the size of the bet for the required period. If you plan to repay the loan in monthly payments, and the annual rate, then it must be converted into a monthly rate, dividing by 12. If, for example, the loan is repaid once a quarter, then the annual rate must be divided by 4 (and thus get the rate for 1 quarter ). The rate can be specified as a percentage or hundredths.
- Nper.Required. This argument represents the number of billing periods (how many times payments will be made to repay the loan). Like the rate, this argument depends on which settlement period is used for calculations. If the loan is received for 5 years with payments once a month, thenNper = 5*12 = 60 periods . If for 3 years, with payments once a quarter, thenNper = 3*4 = 12 periods .
- Ps. Required. The loan amount, that is, the amount of debt that will need to be repaid with future payments.
- [bs].Optional. The amount of debt that must remain unpaid after all billing periods have expired. Typically this argument is 0 (the loan must be repaid in full). Since the argument is optional, it can be omitted (in this case it will be taken equal to zero).
- [type].Optional. Indicates the moment of payment - at the beginning or at the end of the period. For the first case, you need to specify one, and for the second, zero (or skip this argument altogether). In most cases, the second option is used - payments at the end of the period, which means that most often this argument can be omitted.
A special feature of the function syntax is to indicate the direction of the cash flow. If the cash flow is incoming (for example, the amount of the loan received, specified in the argument Ps), then it must be indicated as a positive number. Outgoing flows, on the contrary, are indicated as negative numbers (for example, after calculation, the PMT function will return a negative result, since the amount of the loan payment is an outgoing cash flow).
Examples of using
Task 1. Calculating the amount of loan payments
Let's assume that a bank received a loan in the amount1 000 000 rub. under 17,5% per annum for a period 6 years. The loan will be repaid in equal monthly payments throughout the loan term. By the end of the term, the entire amount of the debt will be paid. The first payment will be made at the end of the first period. You need to find the monthly payment amount.
So, we know the annual rate, and the loan will be repaid monthly. This means that to calculate, we will need to convert the annual rate into a monthly rate, dividing 17.5% by 12 months.In the first argument we write 17,5%/12 .
The loan was received for 6 years. Paid monthly. This means the number of payment periods = 6*12.In the second argument we write 72 .
In the third argument we write the loan amount. It is equal to 1,000,000 rubles. (for the borrower this is an incoming cash flow, we indicate it as a positive number).
We will omit the fourth argument, since the amount will be fully repaid by the end of the term. We will also omit the fifth argument, since payments are made at the end of the period.
The formula will look like:
PLT(17.5%/12;72;1000000)
The result of the calculation is-22526.05 RUR. The number is negative because the loan payment is an outgoing cash flow for the borrower. This is the amount that will need to be paid every month to repay the loan described in the conditions.
To calculate the amount of the final overpayment, you need to multiply the monthly payment by the number of periods (Nper) and subtract the loan amount (Ps) from the result.
Task 2. Calculation of the deposit replenishment amount to accumulate a certain amount of funds
The bank has opened a replenishable deposit with a rate of 9% per annum. You plan to deposit the same amount of money every quarter (for example, part of the quarterly bonus received) with the goal of accumulating exactly 1,000,000 rubles in the account in 4 years. Question: How much should I top up my account every quarter?
We indicate the first argument as 9%/4 (since the annual rate must be converted to a quarterly rate), the second argument = 4*4 (4 years, 4 quarters - a total of 16 contributions). The third argument is the loan amount. We take it as 0, since we didn’t take anything. The fourth argument is future value. We indicate the amount we want to save (RUB 1,000,000). We omit the fifth argument again (payments at the end of the period, this is the most common situation).
We get the formula:
PMT(9%/4;4*4;0;1000000).
Calculation result:-52,616.63 rub.This amount must be deposited into the specified deposit every quarter in order to have a million rubles in the account after four years.
Total amount of deposited funds = 52616.63 * 16 = 841,866.08 rubles. The rest is accumulated through interest.
Features Features
When using the function, pay attention to the following points:
- the function is intended only for annuity payments (that is, equal payments at regular intervals);
- the function works according to the classic credit model, which does not always coincide with what modern credit organizations offer. In many cases, lending conditions will not allow you to successfully apply the PLT function to them and you will have to write a separate model and look for a solution usingParameter selection or Finding a solution(the creation of a similar model can be ordered on our website - tDots.ru);
- the function takes into account the payment of principal and accrued interest, but does not take into account various additional charges, commissions, taxes and fees, etc.;
- the sign of the number (positive or negative) specifies the direction of the cash flow. The flow from the creditor to the debtor (for example, the loan amount) will have one sign, and the flow from the debtor to the creditor (for example, the monthly repayment amount) will have the opposite sign (it doesn’t matter whether it’s plus or minus).
You can support our project and its further development .
You can ask your questions about the article through our feedback bot atTelegram:
Have you ever taken out a loan from a bank? Then this article is for you. When evaluating and analyzing loan options, it is necessary to obtain final values (how much will you have to pay?) for different sets of initial data (in this case, interest rates). One of the advantages of the MS Excel spreadsheet processor is the ability to quickly solve such problems and automatically recalculate the results when the source data changes. Let's say you are planning a project and for this you take out a loan from a bank. When is it better to repay the loan, what interest rates should I choose? To solve such problems, MS Excel uses Lookup table. The use of this tool occurs in this way.
Possible values for one or two function arguments must be presented in the form of a list or table. For one argument, the list of initial values is specified as a row or column of a table. MS Excel represents these values into a formula (function) specified by the user, and then substitutes the results into the appropriate row or column.
When using a table with two variables, the values of one of them are located in a column, the other in a column, and the result of the calculation is based on one or more formulas, and a table for two variables containing calculations for one formula.
In this article we will look at a substitution table for one variable. For a table with two variable values, see the following article.
Let's say you take out a loan of 100 thousand rubles for a period of 5 years and determine monthly payments at different interest rates.
To solve this problem it is used Lookup table MS Excel. First, we write down the initial data - loan amount, term, interest rate according to the figure.
In cell D7 we enter the formula for periodic constant payments on the loan, provided that the amount must be repaid during the loan term: = PMT (C4/12;C3*12;C2)
We divide the interest rate by 12 in the case of monthly payments and select the cell format as percentage - the interest rate in this case is written thus: 12% - 0.0125 - cell format - percentage.
Nper– number of payment periods. If the period is in years, then to calculate monthly payments we multiply by 12.
Ps– indicate the amount we are borrowing (in our case, it is 100,000).
Bs And Type– optional parameters. Bs– the future value or cash balance to be achieved after the last payment; is assumed to be 0 if no value is specified. Type– a boolean value (0 or 1) indicating whether the payment should be made at the end of the period or at the beginning of the period.
Select the range of cells containing the values of interest rates and formulas for calculation - C7:D18.
Run the command. A dialog box will appear on the screen Data table. (see figure). This window is used to specify the work cell that is referenced by the calculation formula. In our example this is the cell C4, which must be specified in the field Substitute values by row into:.
If the source data is located in a column, then the link to the work cell must be entered in the field Substitute values by columns into:. After pressing the button OK the program will fill the column with results. The resulting numbers have a “-” sign.
Let's say that you wanted to determine which part of the payment goes to repay the interest on the loan, and which part goes to the interest on the loan. To do this, in the next column, in the cell E7 you need to enter the formula: = PROCESS PAYMENT(C4/12;1;C3*12;C2) (see figure).
Then run the command again Data – What If Analysis – Data Table, having previously selected the required range of cells. After pressing the button OK The table Interest payment for 1 month appears. (see figure). If these numbers don’t scare you, then you can safely go to the bank for a loan.
Good luck calculating interest payments