Formula to calculate monthly contract value between date

Closed
Posts
1
Registration date
Sunday June 23, 2013
Status
Member
Last seen
June 23, 2013
-
 bozztcs -
Hi,

Would appreciate helps here.
I need to calculate monthly contract value based on contract start date and finish date.

Contract value: 100,000; start date:5/5/2013, finish date :25/8/2016.

Thanks,

2 replies

Posts
1950
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
January 9, 2022
148
Bozztcs, Good afternoon.

Suppose your data are:

4---------------D------------E-----------
5----------------Value:|------100,000
6---------------- Start:|--05/05/2013
7----------------Finish:|--25/08/2016

Try to do this:

10------ Total Months:| =DATEDIF(E6;E7;"M")
11--Monthly Payment:| =E5/E10

Result:

10------ Total Months:| 39
11--Monthly Payment:| 2564.10

Tell us if it worked for you.

I hope it helps.
Thanks, Mazzaropi for your reply.
However, I need more conditions of calculation than that. =IFERROR((IF(AND('Pipeline Input'!$M7>='Pipeline Calculations'!D$6,'Pipeline Input'!$L7<=DATE(YEAR('Pipeline Calculations'!D$6),MONTH('Pipeline Calculations'!D$6)+1,0)),MIN('Pipeline Input'!$M7,DATE(YEAR('Pipeline Calculations'!D$6),MONTH('Pipeline Calculations'!D$6)+1,1))-MAX('Pipeline Input'!$L7,'Pipeline Calculations'!D$6),0)/('Pipeline Input'!$M7-'Pipeline Input'!$L7))*'Pipeline Calculations'!$C7,0)

This formula shown calculation day of months, it is good when start date is not 1st of month, however, it calculate day of month for subsequent full months, Feb/ Mar/APr have different figures (due to different day of month).