Many times, we need to find the amount to be paid after applying a certain rate of interest for a given period of time compound. PPMT formula is used to find out the principle at any time period. This tutorial will show you every step of calculating the principle even for several years in a jiffy.
Syntax:
=PPMT(rate,period,periods, -loan)
Explanation
This formula is used to find out a portion of the principle of a loan in a given time period. However, you need to take care of the conversion of the interest rate from yearly to monthly according to the given payments.
Let’s understand this through an example:
Example
Figure 1: Application of PPMT formula
We take the values as shown in the figure.
Cells C5 to C9 contains all the given values including the loan amount, rate of interest, the total number of months and the compounding period. In the example, there are total 5 years on which the interest is calculated. Hence, the monthly period results to (5*12=60)
In the active cell C11, we apply the following formula to get the portion of the principal
=PPMT(C6/12,1,C8,-C5)
In the given formula, C6 refers to the interest rate per annum which is further divided by 12 to get the interest rate per month. C8 contains the value of the total months in the given years, while C5 contains the loan amount.
This PPMT in-built function in MS-Excel will calculate the principle and return the value as shown in Figure 2.
Figure 2: Result of PPMT function
Leave a Comment