Loan payment calculator

Work out the payment on any loan or mortgage, see how much of every payment is interest, and copy the Excel formula that gives the same number.

Your loan

Monthly payment: 483.32
Total paid: 28,999.20
Total interest: 3,999.20

The formula in Excel

Paste it into any cell of a sheet. It gives the payment above.

=PMT(6%/12, 5*12, -25000) → 483.32

Total interest: every payment, plus any balloon, less what you borrowed.

=PMT(6%/12, 5*12, -25000)*5*12-25000 → 3,999.20

How it works: the PMT function. Practise it: Monthly payment on a loan, a short lesson that grades your formula.

Repayment schedule

The first 12 of 60 payments, then the totals.

No.	Payment	Interest	Principal	Balance
1	483.32	125.00	358.32	24,641.68
2	483.32	123.21	360.11	24,281.57
3	483.32	121.41	361.91	23,919.66
4	483.32	119.60	363.72	23,555.93
5	483.32	117.78	365.54	23,190.39
6	483.32	115.95	367.37	22,823.03
7	483.32	114.12	369.20	22,453.82
8	483.32	112.27	371.05	22,082.77
9	483.32	110.41	372.91	21,709.86
10	483.32	108.55	374.77	21,335.09
11	483.32	106.68	376.64	20,958.45
12	483.32	104.79	378.53	20,579.92
Total	28,999.20	3,999.20	25,000.00

Show all 60 rows

No.	Payment	Interest	Principal	Balance
1	483.32	125.00	358.32	24,641.68
2	483.32	123.21	360.11	24,281.57
3	483.32	121.41	361.91	23,919.66
4	483.32	119.60	363.72	23,555.93
5	483.32	117.78	365.54	23,190.39
6	483.32	115.95	367.37	22,823.03
7	483.32	114.12	369.20	22,453.82
8	483.32	112.27	371.05	22,082.77
9	483.32	110.41	372.91	21,709.86
10	483.32	108.55	374.77	21,335.09
11	483.32	106.68	376.64	20,958.45
12	483.32	104.79	378.53	20,579.92
13	483.32	102.90	380.42	20,199.50
14	483.32	101.00	382.32	19,817.18
15	483.32	99.09	384.23	19,432.94
16	483.32	97.16	386.16	19,046.79
17	483.32	95.23	388.09	18,658.70
18	483.32	93.29	390.03	18,268.68
19	483.32	91.34	391.98	17,876.70
20	483.32	89.38	393.94	17,482.76
21	483.32	87.41	395.91	17,086.86
22	483.32	85.43	397.89	16,688.97
23	483.32	83.44	399.88	16,289.10
24	483.32	81.45	401.87	15,887.22
25	483.32	79.44	403.88	15,483.34
26	483.32	77.42	405.90	15,077.43
27	483.32	75.39	407.93	14,669.50
28	483.32	73.35	409.97	14,259.53
29	483.32	71.30	412.02	13,847.51
30	483.32	69.24	414.08	13,433.42
31	483.32	67.17	416.15	13,017.27
32	483.32	65.09	418.23	12,599.04
33	483.32	63.00	420.32	12,178.71
34	483.32	60.89	422.43	11,756.29
35	483.32	58.78	424.54	11,331.75
36	483.32	56.66	426.66	10,905.09
37	483.32	54.53	428.79	10,476.29
38	483.32	52.38	430.94	10,045.35
39	483.32	50.23	433.09	9,612.26
40	483.32	48.06	435.26	9,177.00
41	483.32	45.89	437.44	8,739.57
42	483.32	43.70	439.62	8,299.94
43	483.32	41.50	441.82	7,858.12
44	483.32	39.29	444.03	7,414.09
45	483.32	37.07	446.25	6,967.84
46	483.32	34.84	448.48	6,519.36
47	483.32	32.60	450.72	6,068.64
48	483.32	30.34	452.98	5,615.66
49	483.32	28.08	455.24	5,160.42
50	483.32	25.80	457.52	4,702.90
51	483.32	23.51	459.81	4,243.10
52	483.32	21.22	462.10	3,780.99
53	483.32	18.90	464.42	3,316.58
54	483.32	16.58	466.74	2,849.84
55	483.32	14.25	469.07	2,380.77
56	483.32	11.90	471.42	1,909.35
57	483.32	9.55	473.77	1,435.58
58	483.32	7.18	476.14	959.44
59	483.32	4.80	478.52	480.92
60	483.32	2.40	480.92	0.00

On this page

How the payment is worked out

A loan with a fixed payment is paid off in equal amounts. Each payment first covers the interest built up since the last one, and the rest goes towards the loan. Early on most of the payment is interest; by the end almost all of it is loan. Excel's PMT function finds the one payment that brings the balance to zero with the last one.

=PMT(rate, nper, pv, [fv], [type])

rate is the interest rate per payment: the yearly rate divided by the number of payments a year, so 6%/12 for monthly payments.
nper is the number of payments: years times payments a year, so 5*12.
pv is the amount borrowed. Put a minus before it and the payment comes out positive.
fv is a balloon still owed at the end, 0 if you pay the loan off in full.
type is 0 when payments fall at the end of each period, 1 at the start.

Build the schedule in Excel

Put the amount in B1, the yearly rate in B2 and the years in B3. Number the payments 1, 2, 3 and so on down column A from A6. IPMT gives the interest part of each payment, and PPMT the part that pays off the loan:

=IPMT($B$2/12,A6,$B$3*12,-$B$1)

=PPMT($B$2/12,A6,$B$3*12,-$B$1)

Fill both down to the last payment. Interest plus principal is the same payment on every row, and the principal column adds up to the amount borrowed. The calculator's schedule is built the same way, so for a monthly loan paid at the end of each month its rows match these formulas row by row. For another frequency, change both 12s; for a balloon or payments at the start, add the balloon and 1 as the last two arguments, as in PMT.

Balloons and payments at the start

A balloon is part of the loan left to pay in one lump sum at the end, common with car finance. It goes into PMT as the fourth argument, fv. Every regular payment gets smaller, but at any rate above 0% you pay more interest in total, because more of the loan stays unpaid for longer. The schedule shows the balloon as its own last row.

Payments at the start of each period, type 1, are how leases and some savings plans work. The first payment comes before any interest builds up, so at any rate above 0% every payment is a little lower than with payments at the end.

Want to know if an investment is worth it instead? The npv and irr calculator works out what a series of cash flows is worth today, and the return it earns.

FAQ

How do I calculate a loan payment in Excel?

Use PMT with the rate and the number of payments in the same unit. For monthly payments that is =PMT(yearly rate/12, years*12, -amount). The minus sign before the amount makes the payment come out positive.

Why does PMT give a negative number?

Excel gives money you receive and money you pay opposite signs. The loan comes to you, so the payments go out and come back negative. Put a minus before the amount, or before PMT, to show the payment as a positive number.

What is a balloon payment?

A lump sum still owed after the last regular payment, paid in one go at the end. PMT takes it as its fv argument: =PMT(rate/12, years*12, -amount, balloon). It lowers every regular payment, but at any rate above 0% you pay more interest overall, because more of the loan stays unpaid for longer.

What changes when payments are due at the start of each period?

The first payment is made on day one, before any interest has built up, so at any rate above 0% each payment is a little smaller. In Excel, set PMT's last argument, type, to 1. Most loans and mortgages are paid at the end of each period, type 0, which is the default.

Does the payment include taxes, insurance or fees?

No. PMT, and this calculator, work out principal and interest only. Add property tax, insurance and any fees on top.

Why is my lender's schedule a few cents different?

Lenders round every payment and every interest charge to the cent, and some count interest by the day. Excel, and this calculator, keep every decimal and only round what they show. The difference is usually a few cents, often in the last payment.

Know PMT? Prove it on a real sheet.

Practise PMT and the finance functions around it in a live grid that grades your result and explains your mistakes.

Practise PMT free See the lessons →

Runs in your browser · Graded on your result · No install