Formula to calculate monthly contract value between date
Closed
Bozztcs
Posts
1
Registration date
Sunday 23 June 2013
Status
Member
Last seen
23 June 2013
-
23 Jun 2013 à 05:41
bozztcs - 23 Jun 2013 à 19:27
bozztcs - 23 Jun 2013 à 19:27
Related:
- Formula to calculate monthly contract value between date
- Formula to calculate vat in excel - Guide
- Logitech formula vibration feedback wheel driver - Download - Drivers
- T mobile balance check contract - Guide
- Date formula in excel dd/mm/yyyy - Guide
- Spreadsheet formula - Guide
2 responses
Mazzaropi
Posts
1983
Registration date
Monday 16 August 2010
Status
Contributor
Last seen
24 May 2023
147
23 Jun 2013 à 14:17
23 Jun 2013 à 14:17
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.
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).
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).
