deducting a monthly payment

[alanled]

Hi I would like to have a cell displaying a total outstanding loa
balance and another cell displaying the monthly payment and every mont
on a set day the payment cell would deduct the total outstanding balanc
and also tell me how many payments i have left. Any help would be muc
appreiciated

 

[Sandy Mann]

alanled

Witht the monthly payment amount in G1, for the total still owing try:

=IF(DAY(TODAY())>=15,1000-G1*DATEDIF(C1,TODAY(),"m"),1000)

This is with the original loan, (1,000), hard coded into the formula. If
you want replace the 1000 with the cell reference of the original total.

For the number of payment try:

=INT(F5/G1)&" Payments"&IF(INT(F5/G1)*G1<>F5," and $"&MOD(F5,G1),"")

--
HTH

Sandy
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk