PMT PPMT IPMT Functions of Excel 

The PMT, PPMT, and IPMT functions in Excel are interconnected financial formulas that assist users in evaluating loans. They address key considerations such as determining the best loan option, interest rates (weekly, monthly, or annual), and the total interest payable upon loan completion. By utilizing these formulas, individuals can effectively answer critical questions regarding their loan decisions.

When to Use PMT, PPMT, IPMT Function of Excel

The PMT function in Excel calculates the total payment required for each period in a loan or investment scenario, encompassing both the principal amount and interest. The term "PMT" stands for "payment."

The word PPMT stands for “Principal Payment” for each period. The PPMT function of Excel gives the principal amount of money from the total payment transacted at the end or beginning of the period. If we took a loan, then the Interest amount will start decreasing with the coming periods whereas the principal amount is paid more to get the loan settled. This concept is explained in more details in the upcoming examples.

The word IPMT represents “Interest Payment”. The IPMT function of excel returns the amount that will be paid as interest. When we take a loan, we return the principal amount with the interest money in the coming periods. The interest lowers down as the money we took for a loan is getting returned in installments, reducing the amount on which we are paying the interest money.

Formulas of this part

The following are the syntax for PMT, PPMT, IPMT formula in Excel.

=PMT(rate,nper,pv,[fv],[type])

=PPMT(rate,per,nper,pv,[fv],[type])

=IPMT(rate,per,nper,pv,[fv],[type])

Below are the function arguments Explained.

• rate – This is the interest rate for each period.

• per – This is the number of period for which we want to get the principal payment (PPMT) or interest payment (IPMT)

• nper – This represents the total number of periods.

• pv – It is the present value of the loan or investment.

• fv – This is an optional argument representing the future value.

• type – When we are making payments at the starting of each period, then the type is 1. This leads to less interest and principal amount as you are returning the loan amount earlier when compared with the payment being done at the end of each period (type = 0).

Excel PMT, PPMT, IPMT Function

The following points must be kept in mind before actually using these functions.

• The value of per argument for PPMT and IPMT function must lie between 1 and nper (total number of periods).

• The money is negative for cash outflow whereas it is positive when money received.

• Default value of type is 0.

• We might need to change the annual interest rate into quarterly, monthly or weekly interest rates.

• nper can never be zero or negative value.

• The default value of fv is 0.