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
bozztcs - Jun 23, 2013 at 07:27 PM
Related:
- Formula to calculate monthly contract value between date
- Date formula in excel dd/mm/yyyy - Guide
- How to calculate 90 days from date in excel - Guide
- Logitech formula vibration feedback wheel driver - Download - Drivers
- Number to words in excel formula - Guide
- Excel grade formula - Guide
2 responses
Mazzaropi
Posts
1985
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
May 24, 2023
147
Jun 23, 2013 at 02:17 PM
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.
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).
