Monthly repayment amount = principal * monthly interest rate *( 1+ monthly interest rate) n/[(1+monthly interest rate) n- 1]
Where: monthly interest rate = annual interest rate/12, n represents the number of loan months, and n represents the power of n, such as 180, which represents the power of 180 (loan 15, 180 months).
Note: When calculating (1+ monthly interest rate) n- 1, you must first calculate the contents in brackets, and then multiply them and subtract 1.
For example, now suppose a loan of 200,000 yuan, with a term of 15 years (180 months) and an annual interest rate of 9%.
Substitute the data into the formula: 200000 * (9%/12 (1+9%/12)180/[(1+9%12)]
Extended data:
I. Introduction of PMT repayment function:
PMT function is an annuity function. PMT returns the installment amount of the loan according to the fixed interest rate and equal installment payment. EXCEL functions have 1 1 classes, which are database functions, date and time functions, engineering functions, financial functions, information functions, logical functions, query and reference functions, mathematical and trigonometric functions, statistical functions, text functions and user-defined functions.
Second, PMT repayment function syntax parameters:
1. Loan interest rate (term interest rate).
2.Nper The total payment periods of the loan (total years or leaseback times).
3.Pv present value (lease principal), or the cumulative sum of the present value of a series of future payments, also known as principal.
4.Fv is the future value (residual value), or the cash balance you want to get after the last payment. If Fv is omitted, it is assumed that its value is zero, that is, the future value of the loan is zero.
5. Type number 0 or 1 is used to specify whether the payment time of each period is at the beginning or the end. 1 indicates the beginning (early payment: on the first day of each period), and none or 0 indicates the end (late payment: on the last day of each period).
Three. Payment and repayment function type:
PMT functions include 1 1, which are database functions, date and time functions, engineering functions, financial functions, information functions, logical functions, query and reference functions, mathematical and trigonometric functions, statistical functions, text functions and user-defined functions.
1. Database function:
When you need to analyze whether the values in the data list meet certain conditions, you can use the database worksheet function.
2. Date and time function:
Date and time functions can be used to analyze and process date and time values in formulas.
3. Engineering functions:
The engineering worksheet function is used for engineering analysis. Most of these functions can be divided into three types: functions for dealing with complex numbers, functions for numerical conversion between different digital systems and functions for numerical conversion in different measurement systems.
4. Logical function:
Logical functions can be used to judge whether the value is true or not, or to conduct composite tests. For example, you can use the IF function to determine whether a condition is true or false, thus returning different values.
5. Mathematics and trigonometric functions:
With math and trigonometric functions, you can handle simple calculations, such as rounding numbers, summing values in a cell range, or complex calculations.
6. User-defined functions:
If you want to use a particularly complicated calculation in a formula or calculation, but the worksheet function cannot meet your needs, you need to create a user-defined function. These functions are called user-defined functions and can be created using Visual Basic for Applications.
7. Query and reference functions:
When you need to find a specific value in a data list or table, or you need to find a reference to a cell, you can use the query and reference worksheet function.
8. Statistical function:
The statistical worksheet function is used for statistical analysis of data areas. For example, the statistical worksheet function can provide information about a straight line drawn by a set of given values, such as the slope and Y-axis intercept of the straight line, or the actual point values that make up the straight line.
9. Text function:
Text functions allow you to manipulate text strings in formulas. For example, you can change the case or determine the length of a text string. You can insert or connect a date to a text string.
Reference: PMT repayment function _ Baidu Encyclopedia