Need help with formula

[WLMPilot]

This may have gotten posted twice and I apologize if it did.

I have racked my brain to try and figure this out. I have a worksheet with
the following I am having trouble with the ROUNDDOWN part of formula in
Columna J.

Col E: Monthly Pmt
Col G: Total to be paid back
Col H: Amt Pd so far
=IF(TODAY()<=R30,MIN(G30,E30*J30),MIN(G30,E30*J30))
Col I: Balance
=IF(H30="","",G30-H30)
Col J: Pmts made
=IF(TODAY()<$Q30,0,IF(TODAY()>$R30,ROUND((($R30-$Q30)/30)
+1,0),ROUNDDOWN(((TODAY()-Q30)/30),0)))
Col K: Pmts remaining
=IF(M30="N",ROUNDUP(G30/E30,0)-J30,""))
Col Q: Start Date
Col R: End Date

The formula shown shows the correct value in Column J for example 1, but not
for example 2. I want Col J (pmts made) to equal 1 if TODAY() = Start Date
but Col J does not equal 1 until 30 days after the start date.

EXAMPLE 1:
Pmt = $250
Total To Be Paid: $23,227.19
Start Date: 9/20/02
Pmts Made (as of 12/20/08) = 76
Pmts Remaining (as of 12/20/08) = 17

Example 2: This is a dummy example I am using to try and fix the problem
Pmt = $500
Total to be Paid: $5,000
Start Date = today's date (whatever date that is)

I want the Pmts Made (Col J) to equal 1 on the start date and to increase by
one each month after that. I can't figure out why the formula works for
Example 1 and not Example 2. If I fix it so example 2 works, then the PMTS
MADE in Example one goes to 77, instead of staying at 76.

NOTE: These are not calculation that involved knowing interest rates, but
simply the TOTAL amount to be paid.

Your help is GREATLY appreciated.

Les

 

[barry houdini]

Hello Les,

You are using 30 days to represent a month but, of course, not all
months have 30 days. This is why you have a discrepancy in the count.
Try using DATEDIF to count the number of months, e.g.

=DATEDIF(TODAY,Q30,"m")+1

that will give 1 when Q30=TODAY and should also give the correct result
when Q30 is 20th Sept 2002.

Clearly you will need to change your other formulas accordingly

Be careful if the payment date is late in the month. If Q30 is 31st
January, for instance, then formula won't count 2 payments until 1st
March