W
WLMPilot
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
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