Need help with formula

[WLMPilot]

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

 

[Fred Smith]

You say our help is GREATLY appreciated, but your walk doesn't match your
talk. In the spirit of Christmas, we'll try again.

As a small point, your formula in Column H, you don't need the If statement
at all, because you're using the same formula for both the true and false
parts.

To your main question, you state your fomula won't return 1 when today
equals Q30. But your formula is: (Today()-Q30)/30. Obviously, when Q30
equals today, then today-q30 will be zero. To rectify this problem, add one
to the result.

This will then get you to your true problem -- your formulas are assuming
there are 30 days in a month, which, of course, is not the case. Between
9/20/02 and 12/20/08 there are 75 months, and there are 2283 days. When you
divide 2283 by 30, you get 76, which makes you think the formula is working.
It's "working" only because there are 6 years between the two dates. Every
year, there are 5 more days in a year than 12 30-day months, so after 6
years, your formula will add a month.

Your choices are:
1. Properly calculate the number of months between two dates.
2. Use the Days360 function to calculate the number of days between two
dates.

Regards,
Fred.

 

[Shane Devenshire]

Hi,

The undocumented DATEDIF function has the following form:

DATEDIF(StartDate,EndDate,Unit)

Where Units are on the left in the table below and their results on the
right. Note that the Unit must be quoted - "y" for example.

y Whole years between two dates
m Whole months between two dates
d Whole days between two dates
md Number of days between two dates ignoring months
ym Number of months between two dates ignoring years
yd Number of days between two dates ignoring years

=DATEDIF(A1,A2,"Y") returns the number of whole years between the dates in
cell A1 and A2

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

 

[WLMPilot]

Hey Fred,

Somehow I lost the thread link. I was not able to link to it via the email
I get when you respond. Thanks for your help. I took your suggestions in
this response and researched, which pointed me to the answer I needed.

Sorry it has taken a while to respond, but I could not find the link and I
happen to run across it today.

Thanks again,
Les