Function Explained
PPMT and IPMT are both financial functions that can help us find the Principal and Interest amount of a certain payment.
PPMT function helps to calculate the Principal amount to be paid for a certain period on a loan or other financial instrument, such as bonds.
IPMT function is used to find out the Interest portion of a certain payment.
Syntax
The syntax for both functions is exactly the same!
PPMT:
=PPMT(rate,per,nper,pv,[fv],[type])
rate – The periodic interest rate
per – The number of payment for which we would like to find the principal amount. For example – Payment no. 3.
nper – The number of periods for payment
pv – The Present Value (PV) of the loan, or any other instrument.
[fv] – The Future Value (FV) – the amount left after all payments are done. This argument is optional, meaning that if left empty, the default FV will be 0 (meaning no balance is left) [type] – Are payments being made at the beginning or at the end of each period. 1 is for beginning, 0 is for the end of the period. As this argument is optional, if omitted, the default is 0 (End of period)IPMT:
=IPMT(rate,per,nper,pv,[fv],[type])
rate – The periodic interest rate
per – The number of payment for which we would like to find the interest amount. For example – Payment no. 3.
nper – The number of periods for payment
pv – The Present Value (PV) of the loan, or any other instrument.
[fv] – The Future Value (FV) – the amount left after all payments are done. This argument is optional, meaning that if left empty, the default FV will be 0 (meaning no balance is left) [type] – Are payments being made at the beginning or at the end of each period. 1 is for beginning, 0 is for the end of the period. As this argument is optional, if omitted, the default is 0 (End of period)Example
Let’s assume we have taken a loan in the of 150,000$ to buy ourselves a brand new sports car 🙂
The loan is for 10 years, bearing a yearly interest rate of 5%. We already know, using the PMT function, that the total amount to be paid each period is 19,425.69.
Can we find out the principal amount and the interest amount which will be paid in the 3rd payment?
In this example, we did not type any value in [fv] as the Future Value of the loan is 0. We kept [type] empty as well, meaning that payments are made at the end of each period.