calculating defered payments on a given date

clare_voiant · Mar 21st, 2002, 09:53 AM

this is what i have

column1 - no of installments i.e 10
column2 - amount per installment i.e. £100
column3 - payment start date i.e. 15th march 2002
column4 - last payment date i.e. 15th Dec 2002
column5 - now()
column6 - paid to date ????????

i would like to know how to calculate how much money we have now for account reconcilliation, and (even on a future date) the paid to date column should not read more than £1000

can anyone help?

IML · Mar 21st, 2002, 10:05 AM

assuming you are in row 1, try

=MIN(A1*B1,(DATEDIF(C1,E1,"M")+1)*B1)

you probably had a feeling I was going to say that.
good luck

[ This Message was edited by: IML on 2002-03-21 09:06 ]

clare_voiant · Mar 21st, 2002, 10:10 AM

thanks, funnily enough i had a datedif statement, but it wasnt stopping.... i'll give it a go

clare_voiant · Mar 21st, 2002, 10:15 AM

thankyou, thankyou, thankyou.....it works.... i wish id come here earlier cos ive spent all afternoon on this and now its time to go home

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 21st, 2002, 09:53 AM	#1
clare_voiant Board Regular Join Date: Mar 2002 Posts: 76	this is what i have column1 - no of installments i.e 10 column2 - amount per installment i.e. £100 column3 - payment start date i.e. 15th march 2002 column4 - last payment date i.e. 15th Dec 2002 column5 - now() column6 - paid to date ???????? i would like to know how to calculate how much money we have now for account reconcilliation, and (even on a future date) the paid to date column should not read more than £1000 can anyone help?

Mar 21st, 2002, 10:05 AM	#2
IML MrExcel MVP Join Date: Feb 2002 Location: Denver, CO Posts: 1,744	assuming you are in row 1, try =MIN(A1B1,(DATEDIF(C1,E1,"M")+1)B1) you probably had a feeling I was going to say that. good luck [ This Message was edited by: IML on 2002-03-21 09:06 ]

Mar 21st, 2002, 10:10 AM	#3
clare_voiant Board Regular Join Date: Mar 2002 Posts: 76	thanks, funnily enough i had a datedif statement, but it wasnt stopping.... i'll give it a go

Mar 21st, 2002, 10:15 AM	#4
clare_voiant Board Regular Join Date: Mar 2002 Posts: 76	thankyou, thankyou, thankyou.....it works.... i wish id come here earlier cos ive spent all afternoon on this and now its time to go home