Formula to calculate monthly contract value between date

Closed
Bozztcs Posts 1 Registration date Sunday June 23, 2013 Status Member Last seen June 23, 2013 - Jun 23, 2013 at 05:41 AM
 bozztcs - Jun 23, 2013 at 07:27 PM
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

Mazzaropi Posts 1978 Registration date Monday August 16, 2010 Status Contributor Last seen November 18, 2022 146
Jun 23, 2013 at 02:17 PM
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.
2
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).
1