deducting a monthly payment
 

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


--
alanled
------------------------------------------------------------------------
alanled's Profile: http://www.excelforum.com/member.php...o&userid=30949
View this thread: http://www.excelforum.com/showthread...hreadid=506197

deducting a monthly payment
 

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

with @tiscali.co.uk


"alanled" wrote in
message ...

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


--
alanled
------------------------------------------------------------------------
alanled's Profile:
http://www.excelforum.com/member.php...o&userid=30949
View this thread: http://www.excelforum.com/showthread...hreadid=506197