Need help with calculation

  • Thread starter Thread starter WLMPilot
  • Start date Start date
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
G3 = Starting Balance 114.90
H3 = Total Pmts made (calculated)
I3 = Current Balance (=G3-H3)
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. 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
 
I guess I did not clarify myself. The "Total Pmts" is a dollar amount and I
guess it should be titled something different, ie "Total Pmts so far". I
also added two more cells below that I left off, J3 & K3.

What I want H3 to show is after the first pmt, H3 = $25, 2nd pmt H3 =
$50......4th Pmt H3 = $100, 5th Pmt H3 = 114.90.

E3 = Payment made each month. 25.00
G3 = Starting Balance 114.90
H3 = 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)

Sorry for any confusion and thanks for your help.

Les
 
Back
Top