Finance
Split a loan payment into interest and principal
=IPMT(B1/12,1,B2*12,-B3)How it works in Excel
- B1/12 is the monthly rate.
- 1 means the first payment.
- B2*12 is the number of payments.
- -B3 is the loan amount.
- PPMT uses the same items and gives the part that reduces the loan.
- Change the 1 to look at a later payment.
- Early payments are mostly interest.
The idea in general
Each payment first covers the interest owed, and the rest shrinks the loan.
What I assumed
- The yearly rate is in B1, the years are in B2 and the loan is in B3.
Check the result on your own data before relying on it.