I am trying to create a worksheet that will track payments made on an
account. I do not have the use of EDATE()

GIVEN:

E3 = Payment made each month. 25.00 until bal < $25 on 5th pmt
G3 = Starting Balance 114.90
H3 = Amount of Total Pmts made (calculated)
I3 = Current Balance (=G3-H3)
J3 = # of Pmts Made
Calculated -- ROUND((TODAY()-Q3)/30.417,0)
K3 = # of Pmts Remaining
Calculated -- ROUND(G3/E3,0)-J3
N3 = Due Date: 30 -- Not given as a date value but numeric value, ie
15th, 20th.
Q3 = Start Date 6/30/08 (entered by user)
R3 = Stop Date 10/16/08 -- calculated: Q3+(INT((G3/E3)*30.417)-30.417)

In this example, there are 4 equal pmts of $25, which leaves a 5th and final
payment of $14.90.

Need help calculating H3. It is easy to calculate cumulative total of pmts
as long as they are equal pmts, ie $25. However, the last pmt will be $14.90
in this example.
I need help calculating this.

Also need help with formula in Stop Date (R3). Note that the "Due Date" is
the 30th, but the "Stop Date" is calculated to be the 16th of Oct. Need it
to also be the 30th.

I appreciate any help with this!

Thanks,
Les

From what I can see, your spreadsheets assume that payments are actually
made on the day required. In most industries, this is an unsafe assumption,
but it's up to you. If it's valid, then try the following changes:

H3: I assume your calculation is =E3*J3. Change this to =min(g3,e3*j3)
J3: Change to =Ceiling((today()-q3)/30.417,0) as your formula will fail when
the final payment is less than 50% of the monthly.
K3: Change to =Ceiling(g3/e3,0)-j3 for same reaon.
N3: Could be calculated as =day(q3)
R3: Change to =date(year(q3),month(q3)+j3+k3,day(q3))

Regards,
Fred.

"WLMPilot" wrote in message
...
I am trying to create a worksheet that will track payments made on an
account. I do not have the use of EDATE()

GIVEN:

E3 = Payment made each month. 25.00 until bal < $25 on 5th pmt
G3 = Starting Balance 114.90
H3 = Amount of Total Pmts made (calculated)
I3 = Current Balance (=G3-H3)
J3 = # of Pmts Made
Calculated -- ROUND((TODAY()-Q3)/30.417,0)
K3 = # of Pmts Remaining
Calculated -- ROUND(G3/E3,0)-J3
N3 = Due Date: 30 -- Not given as a date value but numeric value, ie
15th, 20th.
Q3 = Start Date 6/30/08 (entered by user)
R3 = Stop Date 10/16/08 -- calculated: Q3+(INT((G3/E3)*30.417)-30.417)

In this example, there are 4 equal pmts of $25, which leaves a 5th and
final
payment of $14.90.

Need help calculating H3. It is easy to calculate cumulative total of
pmts
as long as they are equal pmts, ie $25. However, the last pmt will be
$14.90
in this example.
I need help calculating this.

Also need help with formula in Stop Date (R3). Note that the "Due Date" is
the 30th, but the "Stop Date" is calculated to be the 16th of Oct. Need
it
to also be the 30th.

I appreciate any help with this!

Thanks,
Les

Thanks for your help. I have only had a chance to insert the formulas you
suggested. Having problem with CEILING formulas returning a zero, but I will
figure it out. Thanks for you help.

Les

"Fred Smith" wrote:

From what I can see, your spreadsheets assume that payments are actually
made on the day required. In most industries, this is an unsafe assumption,
but it's up to you. If it's valid, then try the following changes:

H3: I assume your calculation is =E3*J3. Change this to =min(g3,e3*j3)
J3: Change to =Ceiling((today()-q3)/30.417,0) as your formula will fail when
the final payment is less than 50% of the monthly.
K3: Change to =Ceiling(g3/e3,0)-j3 for same reaon.
N3: Could be calculated as =day(q3)
R3: Change to =date(year(q3),month(q3)+j3+k3,day(q3))

Regards,
Fred.

"WLMPilot" wrote in message
...
I am trying to create a worksheet that will track payments made on an
account. I do not have the use of EDATE()

GIVEN:

E3 = Payment made each month. 25.00 until bal < $25 on 5th pmt
G3 = Starting Balance 114.90
H3 = Amount of Total Pmts made (calculated)
I3 = Current Balance (=G3-H3)
J3 = # of Pmts Made
Calculated -- ROUND((TODAY()-Q3)/30.417,0)
K3 = # of Pmts Remaining
Calculated -- ROUND(G3/E3,0)-J3
N3 = Due Date: 30 -- Not given as a date value but numeric value, ie
15th, 20th.
Q3 = Start Date 6/30/08 (entered by user)
R3 = Stop Date 10/16/08 -- calculated: Q3+(INT((G3/E3)*30.417)-30.417)

In this example, there are 4 equal pmts of $25, which leaves a 5th and
final
payment of $14.90.

Need help calculating H3. It is easy to calculate cumulative total of
pmts
as long as they are equal pmts, ie $25. However, the last pmt will be
$14.90
in this example.
I need help calculating this.

Also need help with formula in Stop Date (R3). Note that the "Due Date" is
the 30th, but the "Stop Date" is calculated to be the 16th of Oct. Need
it
to also be the 30th.

I appreciate any help with this!

Thanks,
Les

Sorry, my mistake. Change Ceiling to Roundup, and it should work.

Fred.

"WLMPilot" wrote in message
...
Thanks for your help. I have only had a chance to insert the formulas you
suggested. Having problem with CEILING formulas returning a zero, but I
will
figure it out. Thanks for you help.

Les

"Fred Smith" wrote:

From what I can see, your spreadsheets assume that payments are actually
made on the day required. In most industries, this is an unsafe
assumption,
but it's up to you. If it's valid, then try the following changes:

H3: I assume your calculation is =E3*J3. Change this to =min(g3,e3*j3)
J3: Change to =Ceiling((today()-q3)/30.417,0) as your formula will fail
when
the final payment is less than 50% of the monthly.
K3: Change to =Ceiling(g3/e3,0)-j3 for same reaon.
N3: Could be calculated as =day(q3)
R3: Change to =date(year(q3),month(q3)+j3+k3,day(q3))

Regards,
Fred.

"WLMPilot" wrote in message
...
I am trying to create a worksheet that will track payments made on an
account. I do not have the use of EDATE()

GIVEN:

E3 = Payment made each month. 25.00 until bal < $25 on 5th pmt
G3 = Starting Balance 114.90
H3 = Amount of Total Pmts made (calculated)
I3 = Current Balance (=G3-H3)
J3 = # of Pmts Made
Calculated -- ROUND((TODAY()-Q3)/30.417,0)
K3 = # of Pmts Remaining
Calculated -- ROUND(G3/E3,0)-J3
N3 = Due Date: 30 -- Not given as a date value but numeric value, ie
15th, 20th.
Q3 = Start Date 6/30/08 (entered by user)
R3 = Stop Date 10/16/08 -- calculated:
Q3+(INT((G3/E3)*30.417)-30.417)

In this example, there are 4 equal pmts of $25, which leaves a 5th and
final
payment of $14.90.

Need help calculating H3. It is easy to calculate cumulative total of
pmts
as long as they are equal pmts, ie $25. However, the last pmt will be
$14.90
in this example.
I need help calculating this.

Also need help with formula in Stop Date (R3). Note that the "Due Date"
is
the 30th, but the "Stop Date" is calculated to be the 16th of Oct.
Need
it
to also be the 30th.

I appreciate any help with this!

Thanks,
Les

Thanks for your help. Got me past my brain block I was having. I have
another concern dealing with R3 below. I am going to work out some
calculations to see what I can get to work. Have not tried your formula for
R3 yet. Just looking at it, I don't think it takes into account having to
make payments into the next year(s).

Thanks again,
Les


"Fred Smith" wrote:

Sorry, my mistake. Change Ceiling to Roundup, and it should work.

Fred.

"WLMPilot" wrote in message
...
Thanks for your help. I have only had a chance to insert the formulas you
suggested. Having problem with CEILING formulas returning a zero, but I
will
figure it out. Thanks for you help.

Les

"Fred Smith" wrote:

From what I can see, your spreadsheets assume that payments are actually
made on the day required. In most industries, this is an unsafe
assumption,
but it's up to you. If it's valid, then try the following changes:

H3: I assume your calculation is =E3*J3. Change this to =min(g3,e3*j3)
J3: Change to =Ceiling((today()-q3)/30.417,0) as your formula will fail
when
the final payment is less than 50% of the monthly.
K3: Change to =Ceiling(g3/e3,0)-j3 for same reaon.
N3: Could be calculated as =day(q3)
R3: Change to =date(year(q3),month(q3)+j3+k3,day(q3))

Regards,
Fred.

"WLMPilot" wrote in message
...
I am trying to create a worksheet that will track payments made on an
account. I do not have the use of EDATE()

GIVEN:

E3 = Payment made each month. 25.00 until bal < $25 on 5th pmt
G3 = Starting Balance 114.90
H3 = Amount of Total Pmts made (calculated)
I3 = Current Balance (=G3-H3)
J3 = # of Pmts Made
Calculated -- ROUND((TODAY()-Q3)/30.417,0)
K3 = # of Pmts Remaining
Calculated -- ROUND(G3/E3,0)-J3
N3 = Due Date: 30 -- Not given as a date value but numeric value, ie
15th, 20th.
Q3 = Start Date 6/30/08 (entered by user)
R3 = Stop Date 10/16/08 -- calculated:
Q3+(INT((G3/E3)*30.417)-30.417)

In this example, there are 4 equal pmts of $25, which leaves a 5th and
final
payment of $14.90.

Need help calculating H3. It is easy to calculate cumulative total of
pmts
as long as they are equal pmts, ie $25. However, the last pmt will be
$14.90
in this example.
I need help calculating this.

Also need help with formula in Stop Date (R3). Note that the "Due Date"
is
the 30th, but the "Stop Date" is calculated to be the 16th of Oct.
Need
it
to also be the 30th.

I appreciate any help with this!

Thanks,
Les

Well, I stand corrected. Your DATE formula for R3 works. With the way the
month, year, and day were listed and the calculation only taking place with
the month, I did not think it would change the year. Live and learn.

Thanks again,
Les

"Fred Smith" wrote:

From what I can see, your spreadsheets assume that payments are actually
made on the day required. In most industries, this is an unsafe assumption,
but it's up to you. If it's valid, then try the following changes:

H3: I assume your calculation is =E3*J3. Change this to =min(g3,e3*j3)
J3: Change to =Ceiling((today()-q3)/30.417,0) as your formula will fail when
the final payment is less than 50% of the monthly.
K3: Change to =Ceiling(g3/e3,0)-j3 for same reaon.
N3: Could be calculated as =day(q3)
R3: Change to =date(year(q3),month(q3)+j3+k3,day(q3))

Regards,
Fred.

"WLMPilot" wrote in message
...
I am trying to create a worksheet that will track payments made on an
account. I do not have the use of EDATE()

GIVEN:

E3 = Payment made each month. 25.00 until bal < $25 on 5th pmt
G3 = Starting Balance 114.90
H3 = Amount of Total Pmts made (calculated)
I3 = Current Balance (=G3-H3)
J3 = # of Pmts Made
Calculated -- ROUND((TODAY()-Q3)/30.417,0)
K3 = # of Pmts Remaining
Calculated -- ROUND(G3/E3,0)-J3
N3 = Due Date: 30 -- Not given as a date value but numeric value, ie
15th, 20th.
Q3 = Start Date 6/30/08 (entered by user)
R3 = Stop Date 10/16/08 -- calculated: Q3+(INT((G3/E3)*30.417)-30.417)

In this example, there are 4 equal pmts of $25, which leaves a 5th and
final
payment of $14.90.

Need help calculating H3. It is easy to calculate cumulative total of
pmts
as long as they are equal pmts, ie $25. However, the last pmt will be
$14.90
in this example.
I need help calculating this.

Also need help with formula in Stop Date (R3). Note that the "Due Date" is
the 30th, but the "Stop Date" is calculated to be the 16th of Oct. Need
it
to also be the 30th.

I appreciate any help with this!

Thanks,
Les

As you've found out, it's one of the great things about the Date function.

Regards,
Fred.

"WLMPilot" wrote in message
...
Well, I stand corrected. Your DATE formula for R3 works. With the way
the
month, year, and day were listed and the calculation only taking place
with
the month, I did not think it would change the year. Live and learn.

Thanks again,
Les

"Fred Smith" wrote:

From what I can see, your spreadsheets assume that payments are actually
made on the day required. In most industries, this is an unsafe
assumption,
but it's up to you. If it's valid, then try the following changes:

H3: I assume your calculation is =E3*J3. Change this to =min(g3,e3*j3)
J3: Change to =Ceiling((today()-q3)/30.417,0) as your formula will fail
when
the final payment is less than 50% of the monthly.
K3: Change to =Ceiling(g3/e3,0)-j3 for same reaon.
N3: Could be calculated as =day(q3)
R3: Change to =date(year(q3),month(q3)+j3+k3,day(q3))

Regards,
Fred.

"WLMPilot" wrote in message
...
I am trying to create a worksheet that will track payments made on an
account. I do not have the use of EDATE()

GIVEN:

E3 = Payment made each month. 25.00 until bal < $25 on 5th pmt
G3 = Starting Balance 114.90
H3 = Amount of Total Pmts made (calculated)
I3 = Current Balance (=G3-H3)
J3 = # of Pmts Made
Calculated -- ROUND((TODAY()-Q3)/30.417,0)
K3 = # of Pmts Remaining
Calculated -- ROUND(G3/E3,0)-J3
N3 = Due Date: 30 -- Not given as a date value but numeric value, ie
15th, 20th.
Q3 = Start Date 6/30/08 (entered by user)
R3 = Stop Date 10/16/08 -- calculated:
Q3+(INT((G3/E3)*30.417)-30.417)

In this example, there are 4 equal pmts of $25, which leaves a 5th and
final
payment of $14.90.

Need help calculating H3. It is easy to calculate cumulative total of
pmts
as long as they are equal pmts, ie $25. However, the last pmt will be
$14.90
in this example.
I need help calculating this.

Also need help with formula in Stop Date (R3). Note that the "Due Date"
is
the 30th, but the "Stop Date" is calculated to be the 16th of Oct.
Need
it
to also be the 30th.

I appreciate any help with this!

Thanks,
Les