deducting a monthly payment

[alanled]

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.

[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
[email protected]
[email protected] with @tiscali.co.uk


"alanled" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>