Spread Prepayments over specified period (Start & End Date)

[Roggy_Swiss]

Hi all. I am trying to automate a Prepayment file. Basically I have the following data given in a row (entered via UserForm: -Amount of Invoice (+currency etc but not relevant in this context), start date, end date. In the columns there are months starting in July 2013 to June 2014. As I am setting a VBA controlled file with userforms, I need to automate the calculation of the amount in each specific month. So if an invoice is EUR 15000 for lets say Insurance for October, November, December 2013, I want the formula to look at start date, end date and the enter the values in each month concerned, in this case in the colums Oct, Nov and Dec 12 of EUR 5000 in each (Month approach). But as an invoice is not always for exact whole months, but sometimes starting on the 15th and ending on the 5th I would very much prefer a Day approach. Currently I have the following formula: =IFERROR(($M5/(DATEDIF($N5;$O5;"d"))*IF(ISERROR(DATEDIF(MAX($N5;P$4);MIN($O5;Q$4);"d"))=TRUE;"";DATEDIF(MAX($N5;P$4);MIN($O5;Q$4);"d")));"") ---- M5=Invoice amount, N5=Start Date, O5=End Date, P4=Jul 13,Q4=Aug 13 etc. This works quite good but the last month is alway a bit odd, meaning if the last month has 31 days the formula gives the same value as for a month with 30 days. Could anyone help me get the days right? I would be quite happy to have shorter formula than the one above. THANKS ALOT ALREADY!!! Regards, Roggy

 

[cambell]

Thank you very much, David. Is there a way to do this formula in an array? Or is it impossible?

Also if there is no start and end date, but an extra column with the date it is approved, is there a way to spread it over that date instead?

Thank you very much for the effort.

Cheers,

 

[Dave Patton]

"do this formula in an array? Or is it impossible? "

I do not use Google's Sheets, you will need to work out the Array solution,

In Excel,
- the formula will copy or fill down
- you could use a Data Table

"Also if there is no start and end date, but an extra column with the date it is approved, is there a way to spread it over that date instead?"

Did you try the suggested solution?

 

[Dave Patton]

"do this formula in an array? Or is it impossible? "

I do not use Google's Sheets, you will need to work out the Array solution,

In Excel,
- the formula will copy or fill down
- you could use a Data Table

"Also if there is no start and end date, but an extra column with the date it is approved, is there a way to spread it over that date instead?"

Did you try the suggested solution?

 

[Dave Patton]

You can edit and combine the following to address your requirements.
You know your data and your requirements.

Excel 2010
	A	B	C	D	E	F	G	H	I
2	Approved	Contract	Current ?	Current	Starting Date	Ending Date	Contract Amount	Jun-13	Jul-13
3	Y	A_222-2013	TRUE	120,000.00	16-Jun-13		120,000.00	120,000.00	0.00
4	16-Jun-13	A_222-2013					120,000.00	120,000.00	0.00
5
3aa

Cell Formulas
Range	Formula
C3	=AND(A3="Y",OR(E3="",F3=""))
D3	=C3*G3
H3	=($C3=TRUE)*($E3>=H$2)*($E3)*$D3
H4	=(A4>=$K$7)*(A4<=$L$7)*($A4>=H$2)*($A4)*$G4

 

[nihad]

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L
1
2	Approved	Contract	Current	Starting Date	Ending Date	Contract Amount	Jun-13	Jul-13	Aug-13	Sep-13	Oct-13	Nov-13
3	Y		0.00	15-Jun-13	30-Sep-13	100,000.00	14,814.81	28,703.70	28,703.70	27,777.78	0.00	0.00
4	Y		100,000.00	16-Jun-13		100,000.00	0.00	0.00	0.00	0.00	0.00	0.00
5
3a

Cell Formulas
Range	Formula
G3	=(($A3="Y")*(ISNUMBER($D3))*(ISNUMBER($E3)))*MAX(0,MIN(H$2,$E3+1)-MAX(G$2,$D3))/($E3-$D3+1)*$F3
G4	=(($A4="Y")*(ISNUMBER($D4))*(ISNUMBER($E4)))*MAX(0,MIN(H$2,$E4+1)-MAX(G$2,$D4))/($E4-$D4+1)*$F4
H3	=(($A3="Y")*(ISNUMBER($D3))*(ISNUMBER($E3)))*MAX(0,MIN(I$2,$E3+1)-MAX(H$2,$D3))/($E3-$D3+1)*$F3
H4	=(($A4="Y")*(ISNUMBER($D4))*(ISNUMBER($E4)))*MAX(0,MIN(I$2,$E4+1)-MAX(H$2,$D4))/($E4-$D4+1)*$F4
I3	=(($A3="Y")*(ISNUMBER($D3))*(ISNUMBER($E3)))*MAX(0,MIN(J$2,$E3+1)-MAX(I$2,$D3))/($E3-$D3+1)*$F3
I4	=(($A4="Y")*(ISNUMBER($D4))*(ISNUMBER($E4)))*MAX(0,MIN(J$2,$E4+1)-MAX(I$2,$D4))/($E4-$D4+1)*$F4
J3	=(($A3="Y")*(ISNUMBER($D3))*(ISNUMBER($E3)))*MAX(0,MIN(K$2,$E3+1)-MAX(J$2,$D3))/($E3-$D3+1)*$F3
J4	=(($A4="Y")*(ISNUMBER($D4))*(ISNUMBER($E4)))*MAX(0,MIN(K$2,$E4+1)-MAX(J$2,$D4))/($E4-$D4+1)*$F4
K3	=(($A3="Y")*(ISNUMBER($D3))*(ISNUMBER($E3)))*MAX(0,MIN(L$2,$E3+1)-MAX(K$2,$D3))/($E3-$D3+1)*$F3
K4	=(($A4="Y")*(ISNUMBER($D4))*(ISNUMBER($E4)))*MAX(0,MIN(L$2,$E4+1)-MAX(K$2,$D4))/($E4-$D4+1)*$F4
L3	=(($A3="Y")*(ISNUMBER($D3))*(ISNUMBER($E3)))*MAX(0,MIN(M$2,$E3+1)-MAX(L$2,$D3))/($E3-$D3+1)*$F3
L4	=(($A4="Y")*(ISNUMBER($D4))*(ISNUMBER($E4)))*MAX(0,MIN(M$2,$E4+1)-MAX(L$2,$D4))/($E4-$D4+1)*$F4
C3	=AND(A3="Y",OR(D3="",E3=""))*F3
C4	=AND(A4="Y",OR(D4="",E4=""))*F4

N.B. This is not an array formula.
G2 Jun-13 is a date June 1 2013 and the other dates are also 1st of the applicable months.
Hopefully this will translate to Google

Hi Dave

Thank you so much for this, is there away to change this to calculate flat monthly amount rather than based on days in the month?

 

[Dave Patton]

You can start a new thread for your question.
try searching for threads that meet your requirements

N.B. You can post an extract of your sheet with the forum's tool named XL2BB.

 

[nihad]

You can start a new thread for your question.
try searching for threads that meet your requirements

N.B. You can post an extract of your sheet with the forum's tool named XL2BB.

Done

Thank you David