Explanation
For this example, we want to calculate cumulative principal payments over the full term of a 5-year loan of $5,000 with an interest rate of 4.5%. To do this, we set up CUMPRINC like this:
rate - The interest rate per period. We divide the value in C6 by 12 since 4.5% represents annual interest:
=C6/12
nper - the total number of payment periods for the loan, 60, from cell C8.
pv - The present value, or total value of all payments now, 5000, from cell C5.
start_period - the first period of interest, 1 in this case, since we are calculating principal across the entire loan term.
end_period - the last period of interest, 60 in this case for the full loan term.
With these inputs, the CUMPRINC function returns 5,000, which matches the original loan value as expected.
Explanation
For this example, we want to calculate the interest portion for payment 1 of a 5-year loan of $5,000 with an interest rate of 4.5%. To do this, we set up PPMT like this:
rate - The interest rate per period. We divide the value in C6 by 12 since 4.5% represents annual interest:
=C6/12
per - the period we want to work with. Supplied as 1 since we are interested in the principal amount of the first payment.
pv - The present value, or total value of all payments now. In the case of a loan, this is input as a negative value by adding a negative sign in front of C5 to supply -5000.
With these inputs, the IPMT function returns 74.465, which is rounded to $74.47 when the Currency number format is applied.
