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

Hi,

In order: PMT, PPMT, IPMT, subtract the PMT from the prior outstanding
balance.
--
Cheers,
Shane Devenshire


"da" wrote:

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

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

"ShaneDevenshire" wrote:

Hi,

In order: PMT, PPMT, IPMT, subtract the PMT from the prior outstanding
balance.
--
Cheers,
Shane Devenshire


"da" wrote:

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

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.

"da" wrote:

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

"ShaneDevenshire" wrote:

Hi,

In order: PMT, PPMT, IPMT, subtract the PMT from the prior outstanding
balance.
--
Cheers,
Shane Devenshire


"da" wrote:

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

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


"da" wrote:

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

"ShaneDevenshire" wrote:

Hi,

In order: PMT, PPMT, IPMT, subtract the PMT from the prior outstanding
balance.
--
Cheers,
Shane Devenshire


"da" wrote:

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

Thank you.
"Xhawk57" wrote:

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


"da" wrote:

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

"ShaneDevenshire" wrote:

Hi,

In order: PMT, PPMT, IPMT, subtract the PMT from the prior outstanding
balance.
--
Cheers,
Shane Devenshire


"da" wrote:

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

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



"Xhawk57" wrote:

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


"da" wrote:

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

"ShaneDevenshire" wrote:

Hi,

In order: PMT, PPMT, IPMT, subtract the PMT from the prior outstanding
balance.
--
Cheers,
Shane Devenshire


"da" wrote:

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