Montly payment with interest paid and balance of loan

D

da

What is the formula for calculating monthly loan payment, interest paid each
month, total monthly payment (including principal) and the remaining loan
balance?
thank you
 
S

ShaneDevenshire

Hi,

In order: PMT, PPMT, IPMT, subtract the PMT from the prior outstanding
balance.
 
D

da

Thanks
for loan amount 12,000, interest rate 2%, loan term 3 years (36 payments),
how would you set up the columns and what would be the result in each cell?
Thank you
 
X

Xhawk57

in A1:A3 list your numbers
12,000
..02
36

in A4: =Pmt(A2,A3,-A1)
in B4: =A4*A2
in C4: =A4-B4
in D4: =A1-C4

Repeat in row 5 using D4 as the current loan amount then copy all columns
down 36 rows until column D = 0. this is a fully amortizing loan table.
 
X

Xhawk57

I should have clarified that D5=D4-C5 and B5(interest) = D4*(.02/12) ... C is
the prin. pmt and is deducted from the total due. pmt (A) does not change

12000
0.02
36
$470.79 $20.00 $450.79 $11,549.21
$470.79 $19.25 $451.55 $11,097.66
$470.79 $18.50 $452.30 $10,645.36
$470.79 $17.74 $453.05 $10,192.31
$470.79 $16.99 $453.81 $9,738.50
$470.79 $16.23 $454.56 $9,283.94
$470.79 $15.47 $455.32 $8,828.62
$470.79 $14.71 $456.08 $8,372.54
$470.79 $13.95 $456.84 $7,915.70
$470.79 $13.19 $457.60 $7,458.10
$470.79 $12.43 $458.36 $6,999.73
$470.79 $11.67 $459.13 $6,540.61
 
D

da

Thank you.
Xhawk57 said:
I should have clarified that D5=D4-C5 and B5(interest) = D4*(.02/12) ... C is
the prin. pmt and is deducted from the total due. pmt (A) does not change

12000
0.02
36
$470.79 $20.00 $450.79 $11,549.21
$470.79 $19.25 $451.55 $11,097.66
$470.79 $18.50 $452.30 $10,645.36
$470.79 $17.74 $453.05 $10,192.31
$470.79 $16.99 $453.81 $9,738.50
$470.79 $16.23 $454.56 $9,283.94
$470.79 $15.47 $455.32 $8,828.62
$470.79 $14.71 $456.08 $8,372.54
$470.79 $13.95 $456.84 $7,915.70
$470.79 $13.19 $457.60 $7,458.10
$470.79 $12.43 $458.36 $6,999.73
$470.79 $11.67 $459.13 $6,540.61
 
D

da

Good Morning
Why my numbers are different from yours? I do not get a zero principal
balance at the end of 36 payments.
Thanks
interest No. of Years Payments Loan Amt PMT PPMT IPMT Balance
2.00% 3 36 $12,000.00 -$470.79 ($450.79) $20.00 $11,529.21
2.00% 3 36 $11,529.21 -$470.79 ($451.57) $19.22 $11,058.42
2.00% 3 36 $11,058.42 -$470.79 ($452.36) $18.43 $10,587.63
2.00% 3 36 $10,587.63 -$470.79 ($453.14) $17.65 $10,116.84
2.00% 3 36 $10,116.84 -$470.79 ($453.93) $16.86 $9,646.05
2.00% 3 36 $9,646.05 -$470.79 ($454.71) $16.08 $9,175.26
2.00% 3 36 $9,175.26 -$470.79 ($455.50) $15.29 $8,704.47
2.00% 3 36 $8,704.47 -$470.79 ($456.28) $14.51 $8,233.68
2.00% 3 36 $8,233.68 -$470.79 ($457.07) $13.72 $7,762.89
2.00% 3 36 $7,762.89 -$470.79 ($457.85) $12.94 $7,292.10
2.00% 3 36 $7,292.10 -$470.79 ($458.64) $12.15 $6,821.31
2.00% 3 36 $6,821.31 -$470.79 ($459.42) $11.37 $6,350.52
2.00% 3 36 $6,350.52 -$470.79 ($460.21) $10.58 $5,879.73
2.00% 3 36 $5,879.73 -$470.79 ($460.99) $9.80 $5,408.94
2.00% 3 36 $5,408.94 -$470.79 ($461.78) $9.01 $4,938.15
2.00% 3 36 $4,938.15 -$470.79 ($462.56) $8.23 $4,467.36
2.00% 3 36 $4,467.36 -$470.79 ($463.34) $7.45 $3,996.57
2.00% 3 36 $3,996.57 -$470.79 ($464.13) $6.66 $3,525.78
2.00% 3 36 $3,525.78 -$470.79 ($464.91) $5.88 $3,054.99
2.00% 3 36 $3,054.99 -$470.79 ($465.70) $5.09 $2,584.20
2.00% 3 36 $2,584.20 -$470.79 ($466.48) $4.31 $2,113.41
2.00% 3 36 $2,113.41 -$470.79 ($467.27) $3.52 $1,642.62
2.00% 3 36 $1,642.62 -$470.79 ($468.05) $2.74 $1,171.83
2.00% 3 36 $1,171.83 -$470.79 ($468.84) $1.95 $701.04
2.00% 3 36 $701.04 -$470.79 ($469.62) $1.17 $230.25
2.00% 3 36 $230.25 -$470.79 ($470.41) $0.38 $240.54
2.00% 3 36 $240.54 -$470.79 ($471.19) -$0.40 $711.33
2.00% 3 36 $711.33 -$470.79 ($471.98) -$1.19 $1,182.12
2.00% 3 36 $1,182.12 -$470.79 ($472.76) -$1.97 $1,652.91
2.00% 3 36 $1,652.91 -$470.79 ($473.54) -$2.75 $2,123.70
2.00% 3 36 $2,123.70 -$470.79 ($474.33) -$3.54 $2,594.49
2.00% 3 36 $2,594.49 -$470.79 ($475.11) -$4.32 $3,065.28
2.00% 3 36 $3,065.28 -$470.79 ($475.90) -$5.11 $3,536.07
2.00% 3 36 $3,536.07 -$470.79 ($476.68) -$5.89 $4,006.86
2.00% 3 36 $4,006.86 -$470.79 ($477.47) -$6.68 $4,477.65
2.00% 3 36 $4,477.65 -$470.79 ($478.25) -$7.46 $4,948.44
2.00% 3 36 $4,948.44 -$470.79 ($479.04) -$8.25 $5,419.23
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top