Loan Payments P/Yr.

[Keyrookie]

Hey all,

I've searched this site and can't find the answer I'm looking for so
need help. I'm building a spreadsheet that shows loan balances.
need a formula(s) that will return what I'm looking for.

Ex.

C4 Loan Amount ($30,000)
D4 Interest (3%)
E4 Total # of Payments (60)
F4 January (amount of payment)
G4 February
Etc. ... (thru December)
R4 Balance This cell always reflects the current balance!

I'm thinking I might need to have formulas somewhere else on the shee
and then have the cells reflect the result

 

[Dave F]

In R4 enter = SUM([range of cells showing payments])

Dave

 

[joeu2004]

C4 Loan Amount ($30,000)
D4 Interest (3%)
E4 Total # of Payments (60)
F4 January (amount of payment)
G4 February
Etc. ... (thru December)
R4 Balance This cell always reflects the current balance!

I'm thinking I might need to have formulas somewhere else on the
sheet and then have the cells reflect the results

If the amount of the payment is constant and on time, then you can
compute the balance in R4 with the following, assuming monthly
payments:

=fv(3%/12, 12, payment, -30000)

Note that using -30000 works only for the first 12 months. If you
compute the 2nd year balance in R5, replace -30000 with -R4.

If you want to compute the payment that reduces the balance of the
loan to zero at the end of the loan term (60 months), you can compute
"payment" as follows:

=round(pmt(3%/12, 60, -30000), 2)

Note: If you round PMT(), as you should, the last (60th) payment will
likely not be the same amount as the other payments. The last payment
can be computed by:

=roundup(fv(3%/12, 59, payment, -30000)*(1+3%/12), 2)