You are offered a 10,000 loan and the bank only tells you the payment. Before signing you want to know how much extra you will pay and how much of the first payment is eaten by interest.
How to use Loan Payment Formula step by step
1. Choose what you want to know
The payment, the interest in one given payment, the principal you repay, the total interest, the term or the amount you can borrow.
2. Enter amount, rate and term
Type numbers or cells. The rate goes in as 6 for 6% a year and the tool spreads it according to the payment frequency you pick.
3. Copy the formula
Paste it into your sheet. The result comes out positive, without the minus sign Excel usually attaches to payments.
The Step Count: Traditional Friction vs. KorexTools
Working out a loan on a calculator (4 steps)
- Divide the annual rate by 12.
- Raise (1 + rate) to the power of the term.
- Apply the annuity formula by hand.
- Redo everything when the amount or rate changes.
With the KorexTools “Loan Payment Formula” builder (3 steps)
- You pick the question you want answered.
- You enter amount, rate and years.
- You copy the formula and change the data whenever you like.
A payment is principal plus interest, and Excel can pull them apart payment by payment
PMT works out the constant payment of a loan from the rate per period, the total number of payments and the amount. The unit is what trips people up: if the rate is annual and you pay monthly, divide it by 12 and multiply the years by 12. The builder makes that split for you according to the frequency you choose.
To see where the money goes there are IPMT and PPMT: the first gives the interest in one specific payment and the second the principal you repay in it. Early on almost the whole payment is interest and over time the proportion flips. Total interest comes from multiplying the payment by the number of payments and subtracting the amount borrowed.
Which formula answers which question
- •How much do I pay each month?: PMT.
- •How much of payment 12 is interest?: IPMT with the payment number.
- •What does the loan cost me overall?: payment times payments minus the amount.
A 10,000 loan at 6% a year over 5 years, monthly payments
| Question | Formula | Approximate result |
|---|---|---|
| Monthly payment | =PMT(6/100/12,5*12,-10000) | 193.33 |
| Interest in the first payment | =IPMT(6/100/12,1,5*12,-10000) | 50.00 |
| Total interest | =PMT(6/100/12,5*12,-10000)*5*12-10000 | 1,599.68 |
Common Mistakes and How to Avoid Them
- ⚠️Typing the rate as 0.06 and then dividing it by 100 again.
- ⚠️Using the years as the number of payments without multiplying by the frequency.
Frequently Asked Questions
Why is my payment negative in Excel?
Because PMT treats money going out as negative. This tool puts the amount with a negative sign inside the function so the result comes out positive.
Does it work for a mortgage or a car loan?
Yes, as long as the payment is constant and the rate fixed. It does not cover fees, insurance or variable rates, which you would add separately.
How do I build an amortization schedule?
Use IPMT and PPMT in two columns and change the payment number on each row. The sum of both should always equal the payment.
What I would do if I were in your shoes
«A low payment over a long term usually costs more in total interest: always compare the total, not just the payment.» — DataKorex Engineering Team.