Nowadays, borrowing money from banks is a popular way for many people to realize their dreams, especially for wage earners with relatively stable incomes. They can own their own private cars and houses and have a high-quality life as long as they pay a down payment and a certain amount of funds to the bank regularly. Buyers pay the same amount every month, knowing that this amount includes part of the principal and interest. From the first month to the last month, this figure is the same, but for most people, it is not clear how the monthly repayment amount is obtained. If you want to repay the loan in advance, it is even more unclear. In fact, many of us are using excel in the office suite, which provides pmt, ipmt and ppmt functions to calculate bank repayment. With them, you can easily calculate your repayment amount, monthly interest and principal paid to the bank.

1. Calculate the equal monthly repayment amount. The pmt function provided in excel returns the installment amount of an investment or loan based on the fixed interest rate and the equal installment method. Pmt function can be used to calculate the monthly repayment amount. 1. 1pmt function pmt(rate, nper, pv, fv, type) The use format of interest rate is the interest rate of each period, which is a fixed value. Nper is the total investment (or loan) period, that is, the total payment period of investment (or loan). Pv is the present value, that is, the amount recorded when calculating the investment (or loan), or the cumulative sum of the present values of a series of future payments, also known as the principal. Fv is the future value or the expected cash balance after the last payment. If fv is omitted, it is assumed that its value is zero (for example, the future value of the loan is zero).

Type can only use the number 0 or 1 to specify whether the payment time of each period is at the beginning or the end. If type is omitted, its value is assumed to be zero. 1.2 indicates that the money returned by pmt function includes principal and interest, but does not include taxes, retained funds or some loan-related expenses. The consistency between the specified ratio and nper unit shall be confirmed. For example, for a loan with an annual interest rate of 12% for four years, if it is paid monthly, the interest rate should be12/12, and the nper should be 4 *12; If it is paid annually, the rate should be 12% and nper should be 4. 1.3. For example, if you buy a house with a price of 200,000 yuan and make a down payment of 30%, your loan principal will be140,000 yuan, and 10 year mortgage will be paid off (to zero), according to the current bank statement. In a cell of excel spreadsheet, enter = PMT (0.0042, 10 * 12, 140000, 0,0) and press Enter to confirm. The value you get is 1487.66 yuan, which is the figure of your equal monthly repayment, including principal and interest. 0.0042 in the input formula is the monthly interest rate. If it is annual interest rate, divide the annual interest rate by 12 and convert it into monthly interest rate. 10* 12 is the total repayment months; 140000 is the loan principal; The first 0 means that the final value is 0 after all the principal and interest are repaid; The second 0 indicates the final payment, which is usually set by the bank. In addition to loans, pmt function can also calculate other payments in the form of annuities. For example, you need to deposit 50,000 yuan in fixed monthly deposit in 10. Assuming the annual deposit interest rate is 6%, you can use the function pmt to calculate the monthly deposit amount: PMT (6%/ 12,10 *12,0,500,000).

2. The usage format of1ipmt function ipmt(rate, per, nper, pv, fv, type)rate is the interest rate of each period, which is a fixed value. The period per uses to calculate its interest amount must be between 1 and nper. Nper is the total investment (or loan) period, that is, the total payment period of investment (or loan). Pv is the present value, that is, the amount recorded when calculating the investment (or loan), or the cumulative sum of the present values of a series of future payments, also known as the principal. Fv is the future value or the expected cash balance after the last payment. If fv is omitted, it is assumed that its value is zero (for example, the future value of the loan is zero).

The type can only be the number 0 or 1, which is used to specify whether the payment time of each period is at the beginning or the end. If type is omitted, its value is assumed to be zero. 2.2 Explain that the consistency between the specified rate and nper units should be confirmed. For example, for a loan with an annual interest rate of 12% for four years, if it is paid monthly, the interest rate should be12/12, and the nper should be 4 *12; If it is paid annually, the interest rate should be 12% and nper should be 4.

In all parameters, the money spent, such as bank deposits, is negative; Income and expenses such as dividend income are represented by positive numbers. 2.3 Calculate the monthly interest according to the above example (that is, the loan140,000 yuan, paid off in 10 year, and the monthly interest rate is 4.2‰). Fill in "the nth month" in a 1 and "the interest repayment in the nth month" in b 1 in excel, and then fill in 1 ~12 in a2 to A1respectively. Enter: =ipmt(0.0042, a2, 10* 12, 140000, 0,0) in cell b2, and you can get -588.00, that is, the interest of 1 month is 588.00 yuan.