Table financial function: Use IPMT function to calculate loan interest

The main function of the IPMT function is based on fixed interest rates and equal installment payments. Returns the interest repayment on an investment for a given period.

One ​​thing to note when using this function is that for all parameters, expenditures, such as bank deposits, are expressed as negative numbers. Income, such as dividend income, is expressed as a positive number.

Suppose we know the annual interest rate, the number of periods used to calculate its interest amount, the life of the loan, the present value of the loan, and now require the first month's interest on the loan.

￭ Select cell B5, click Insert function, find and select the IPMT function in the search box, and click OK.

A dialog box will pop up with a total of five parameters.

Interest rate: interest rate for each period

Number of periods: the period used to calculate interest

Total number of payment periods: that is, the total number of payment periods for the investment (or loan)

Present value: the amount of money that has been recorded from the beginning of the investment (or loan)

Future value: the cash balance obtained after the last payment

Whether initial payment: You can fill in 0 or 1, 0 is the end of the period, 1 is the beginning of the period, ignore it and default to the end

Because what we want to calculate is monthly interest, when the unit of interest rate is year, we need to divide it by 12 to calculate the monthly interest rate.

When the total number of payment periods is in years, it must be multiplied by 12 to calculate the total number of monthly periods.

We fill in B1/12 for the interest rate, 1 for the number of periods, B3*12 for the total number of payment periods, and B4 for the present value. Whether the initial payment is required is not filled in by default.

Click OK to calculate the interest for the first month of the loan.