Hey all,

I've searched this site and can't find the answer I'm looking for so I need help. I'm building a spreadsheet that shows loan balances. I 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 sheet and then have the cells reflect the results

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

Dave

On Dec 18, 11:56 am, Keyrookie
wrote:
Hey all,

I've searched this site and can't find the answer I'm looking for so I
need help. I'm building a spreadsheet that shows loan balances. I
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 sheet
and then have the cells reflect the results

--
Keyrookie

On Dec 18, 8:56 am, Keyrookie
wrote:
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)