How to Calculate a Principal Payment Using Microsoft Excel
The PPMT function in Excel calculates the amount of a loan payment that is applied to the principal based on the interest rate, period of the payment, number of payments and the value of the loan. The resulting value is also referred to as the principal payment. The following steps explain how to use PPMT in Excel.
Other People Are Reading
Instructions
-
-
1
Grasp how the principal payment is calculated. PPMT first calculates the total payment based on the financial formula pmt = pv * rate /(1-1/(1 + rate)^nper) where pmt is the total payment, pv is the value of the loan, rate is the interest rate and nper is the number of payment periods in the loan.
-
2
Calculate the interest payment as pv * rate and subtract this amount from the total payment to get the principal payment on the first payment. The principal payment is then subtracted from the value of the loan to get the new principal. This process is repeated until the desired period is reached and the principal payment for that period can be calculated.
-
-
3
Enter "=ppmt(" into the cell. A tip will appear that reads "PPMT(rate,per,nper,pv,[fv],[type])." This indicates the syntax for the PPMT function. The parameter that Excel is expecting you to input next will be in bold. Parameters in square brackets are optional.
-
4
Provide the interest rate for the period, the period you wish to know the principal payment for, the total number of payments and the value of the loan.
-
5
Input the desired balance after the last payment and loan type ("0" for payments due at the end of the period and "1" for payments due at the beginning of the period) if non-zero values are needed.
-
1
