In excel, there are many financial functions. Today I will introduce to you a few financial functions related to the interest rate for a house loan. It may require some financial knowledge, but it doesn’t matter if you haven’t been exposed to it. As long as you learn these functions, it will be too simple to calculate the monthly payment and interest rate in the future.
1, fv functions. In excel, the FV function represents the calculation of future values based on interest rate, term, amount paid per issue or current value. The five parameters of the FV function are: rate, nper, pmt, [pv], [type]. The first parameter represents the interest rate, the second parameter represents the number of periods, the third parameter represents the amount paid for each period, the fourth parameter represents the current value, and the fifth parameter selects 1 to indicate that each payment is at the beginning of the period, and selects 0 or default means that each payment is at the end of the period. The first two parameters are required, choose one of the third and fourth ones, choose the third parameter to find the future value based on the value of each period, and choose the fourth parameter to find the future value based on the current value. The payment period below is all at the end of the year, and the fifth parameter does not need to be selected.
examples: Put 500,000 yuan into Yu'ebao at one time, with an annual interest rate of 2.722%, and a term of 10 years. How much money will the account be in 10 years? (Note: Yu'ebao's interest rate is the 7-day annualized interest rate, and the calculation results here are slightly different from the actual situation. The same below)
input = FV (C2, B2, A2), and it is calculated that the balance of Yu'ebao's account will be 654,040.54 yuan in 10 years.
Figure 1
Example: 20,000 yuan of Yu'ebao is deposited annually, with an annual interest rate of 2.7220%, and a term of 10 years. How much money will the account be in 10 years?
input =FV(C6,B6,A6), and calculate that the balance of Yu'ebao account will be RMB 226,363.76 in 10 years.
Figure 2
2. PV function. In excel, the PV function indicates that the current value is calculated based on the interest rate, term, payment amount per issue or future value. It can be understood that it is exactly the opposite of the FV function. The five parameters of the PV function are: rate, nper, pmt, [fv], [type]. The first parameter represents the interest rate, the second parameter represents the number of periods, the third parameter represents the amount paid for each period, the fourth parameter represents the future value, and the fifth parameter selects 1 to indicate that each payment is at the beginning of the period, and selects 0 or default means that each payment is at the end of the period. The first two parameters are required, choose one of the third and fourth ones, choose the third parameter to find the current value based on the fixed payment value for each period, and choose the fourth parameter to find the current value based on the future value.
examples: If the balance of Yu'ebao account is 500,000 yuan in 10 years, the average interest rate is 2.7220%. How much should you deposit it in one lump sum now?
input =PV (C10, B10, A10), the calculation appears when it should be saved to Yu'ebao 382,239.3 yuan.
Figure 3
Example: If you want to withdraw 20,000 yuan from Yu'ebao every year in the next 10 years, the interest rate is 2.7220%, how much does it need to deposit in one lump sum now?
input =PV (C14, B14, A14), and the calculation appears in one-time deposit of 173,050.25 yuan.
Figure 4
3. PMT function. In excel, the PMT function represents the amount paid for each period based on the interest rate, term, current value or future value. The five parameters of the PMT function are: rate, nper, pv, [fv], [type]. The first parameter represents the interest rate, the second parameter represents the years, the third parameter represents the present value, the fourth parameter represents the future value, and the fifth parameter selects 1 to indicate that each payment is at the beginning of the period, and selects 0 or default means that each payment is at the end of the period. The first two parameters are required, the third and fourth parameters are optional. If you do not select or select 0, it means that the current value or future value is 0. If you choose, it means a certain amount of the current or the amount you want to achieve in the future.
examples: Zhang San bought a provident fund loan of 500,000 yuan, the interest rate is 2.7220%, the term is 10 years, how much money does it need to pay back every year?
input =PMT(I2,H2,G2), and calculated that it is 577.8668 million yuan per year.
Figure 5
Example: I want to have 500,000 yuan in Yu'ebao in 10 years, and the average interest rate of Yu'ebao in 10 years is 2.7220%. How much money do you need to save every year?
input =PMT(I10,H10,G10), and it is calculated that 441.7668 million yuan is required to save each year.
Figure 6
4, rate function. In excel, the rate function indicates that the interest rate is calculated based on the term, the amount of payment per issue, the current value or the future value.The five parameters of the PMT function are: nper, pmt, pv, [fv], [type]. The first parameter represents the age, the second parameter represents the amount of payment per issue, the third parameter represents the present value, the fourth parameter represents the future value, and the fifth parameter selects 1 to indicate that each payment is at the beginning of the period, and selects 0 or default means that each payment is at the end of the period. The first two parameters are required, the third and fourth parameters are optional. If you do not select or select 0, it means that the current value or future value is 0. If you choose, it means a certain amount you want to achieve in the future.
examples: Zhang San bought a house with a loan of 250,000 yuan, and paid 20,000 yuan a year. The repayment period is 10 years. What is the annual interest rate of the loan?
input =RATE(H14,G14,I14), and the annual interest rate is calculated to be 3.86%.
Figure 7
Example: A fund pays 20,000 yuan every year, and can withdraw 250,000 yuan in 10 years. What is the annual interest rate?
input =RATE(H6,G6,I6), and the annual interest rate is calculated to be 4.87%.
Figure 8
5. NPER function. In excel, the nper function represents the calculation period based on the interest rate, the amount of payment per issue, the current value or the future value. The five parameters of the nper function are: rate, pmt, pv, [fv], [type]). The first parameter represents the interest rate, the second parameter represents the amount of payment per issue, the third parameter represents the present value, the fourth parameter represents the future value, and the fifth parameter selects 1 to indicate that each payment is at the beginning of the period, and selects 0 or default means that each payment is at the end of the period. The first two parameters are required, and the third and fourth parameters are optional. If you do not select or select 0, it means that the current value or future value is 0. If you choose, it means a current amount or a certain amount you want to achieve in the future.
examples: Now, 500,000 yuan of Yu'ebao is deposited in one lump sum, 20,000 yuan is withdrawn every year, with an average interest rate of 2.7220%. How many years can I get?
input =NPER(C17,A17,B17), and it is calculated that 42.49 years can be taken.
Figure 9
Example: If you deposit 20,000 yuan per year, the average interest rate is 2.7220%, how many years the account balance is 500,000 yuan?
input =NPER(I17,G17,H17), and it is calculated that the account required for 1933 years can be deposited to RMB 500,000.
Figure 10 How about
? Isn’t it a bit confusing? Below is a summary of the above calculations, you can compare and learn. (Comparison of the same color)
Figure 11
