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

 

[Dave Patton]

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
2	Contract	Starting Date	Ending Date	Contract Amount	Jun-13	Jul-13	Aug-13	Sep-13	Oct-13	Nov-13
3		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		16-Jun-13	31-Oct-13	100,000.00	10,869.57	22,463.77	22,463.77	21,739.13	22,463.77	0.00
5
1a

Excel 2003

Cell Formulas
Range	Formula
E3	=MAX(0,MIN(F$2,$C3+1)-MAX(E$2,$B3))/($C3-$B3+1)*$D3
F3	=MAX(0,MIN(G$2,$C3+1)-MAX(F$2,$B3))/($C3-$B3+1)*$D3
G3	=MAX(0,MIN(H$2,$C3+1)-MAX(G$2,$B3))/($C3-$B3+1)*$D3
H3	=MAX(0,MIN(I$2,$C3+1)-MAX(H$2,$B3))/($C3-$B3+1)*$D3
I3	=MAX(0,MIN(J$2,$C3+1)-MAX(I$2,$B3))/($C3-$B3+1)*$D3
J3	=MAX(0,MIN(K$2,$C3+1)-MAX(J$2,$B3))/($C3-$B3+1)*$D3

N.B. The dates such as Jun-13 are actually real dates first of month i.e. June 1, 2013

 

[Roggy_Swiss]

Thank you Dave. I managed it with my initial formula. It just needed +1 for the MIN number. =IFERROR(($M5/(DATEDIF($N5;$O5;"d"))*IF(ISERROR(DATEDIF(MAX($N5;P$4);MIN($O5+1;Q$4);"d"))=TRUE;"";DATEDIF(MAX($N5;P$4);MIN($O5+1;Q$4);"d")));"").

 

[Dave Patton]

Why are you using Datedif? Simple arithmetic would probably suffice.

 

[Roggy_Swiss]

That was a good point. I changed to arithmetic. I was someway used to Datedif. THANK YOU DAVE!

A question on VBA UserForms (I am posting it in here because it's related to the same workbook).
I have a UserForm with all sorts of TextBoxes, ComboBoxes, CommandButtons in it.

The question now is:
I have a ComboBox for the Profit&Loss (PL) account - cboPL
I have a ComboBox for the Department (~Profit Center) - cboDept
I have a ComboBox for the Product Code - cboPCode
I would like these ComboBoxes to interact with each other.

I am pulling the data from the same workbook, different sheet("AccountRules").
In column A in this Sheet "Account Rules" there all the P&L accounts, in column B the Departments and so on.
The issue now is, that some P&L account allow only 1 Department or 1 Product Code, whereas other P&L accounts allow many (up to 100)
So in column A i have MANY DUPLICATES.

Example:
A2: Operating Leasing ; B2: Dept 10 ; C2: PCode 10
A3: Operating Leasing ; B3: Dept 20 ; C3: PCode 10
A3: Operating Leasing ; B3: Dept 30 ; C3: PCode 10

What I would need is the ComboBoxes to show the following:
P&L account -> ingore Duplicates
Only allow Departments, as per the list ("AccountRules") and based on the Selected P&L account.
Only allow PCode 10, as per the list ("AccountRules") and based on the Selected P&L account.
So if the user adds an entry in the UserForm, she/he selects cboPL and would then only be able to select certain Departments and certain Product Codes based on the look up Sheet.

I tried with this code here: (This one is able to ignore Duplicates, but nothing else really)
Private Sub UserForm_Initialize()
Dim LastRow As Integer
Dim i As Integer
Dim check As Boolean
Dim k As Integer

LastRow = ThisWorkbook.Sheets("AccountRules").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To LastRow
check = True
For k = 0 To Me.cboPL.ListCount - 1
If ThisWorkbook.Sheets("AccountRules").Cells(i, 1).Text = Me.cboPL.List(k) Then
check = False
Exit For
End If
Next k

If check Then
Me.cboPL.AddItem ThisWorkbook.Sheets("AccountRules").Cells(i, 1).Text
End If
Next i
End sub

and I also tried this one: (This is supposed to add items based on the P&L account selection (cboPL)
Private Sub cboPL_Change()
Call checkForData
cboPL.Style = fmStyleDropDownList 'Only values from Dropdown list allowed
Dim myPLAccount As String

myPLAccount = Me.cboPL.Text
LastRow = ThisWorkbook.Sheets("AccountRules").Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To LastRow
If myPLAccount = ThisWorkbook.Sheets("AccountRules").Cells(i, 1) Then
'add to cboDept, cboPCode
Me.cboDept.AddItem ThisWorkbook.Sheets("AccountRules").Cells(i, 2)
Me.cboPCode.AddItem ThisWorkbook.Sheets("AccountRules").Cells(i, 3)
End If
Next i
End Sub

I might need to combine these two together.
But as i'm pretty new to VBA I have no clue where the problem is.
I got the above codes together from Internet and my own input.
Note: I left the RowSource in Properties empty

Hope this makes sense.

Thanks
BR,
Roger

 

[amoocole]

The formula above =MAX(0,MIN(F$2,$C3+1)-MAX(E$2,$B3))/($C3-$B3+1)*$D3 has one problem for me. I need to calculate the monthly amount on a flat total/number of months to evenly spread the cost each month, instead of this formula which varies by month (depending on the total number of days each month). Any suggestions on how to edit this formula to provide a flat monthly amount?

The fact that the formula starts and stops automatically in columns E through J above is exactly what I'm looking for, but I need to spread the cost evenly.

Thank you

 

[NPATEL27]

Did you get a formula for spreading the cost at a fixed cost evenly?

 

[cambell]

1a[TABLE="class: html-maker-worksheet"]
<tbody>[TR]
[TH][/TH]
[TH]A[/TH]
[TH]B[/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]E[/TH]
[TH]F[/TH]
[TH]G[/TH]
[TH]H[/TH]
[TH]I[/TH]
[TH]J[/TH]
[/TR]
[TR]
[TH]2[/TH]
[TD="bgcolor: #C0C0C0"]Contract[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Starting Date[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Ending Date[/TD]
[TD="bgcolor: #C0C0C0, align: center"]Contract Amount[/TD]
[TD="bgcolor: #C0C0C0, align: right"]Jun-13[/TD]
[TD="bgcolor: #C0C0C0, align: right"]Jul-13[/TD]
[TD="bgcolor: #C0C0C0, align: right"]Aug-13[/TD]
[TD="bgcolor: #C0C0C0, align: right"]Sep-13[/TD]
[TD="bgcolor: #C0C0C0, align: right"]Oct-13[/TD]
[TD="bgcolor: #C0C0C0, align: right"]Nov-13[/TD]
[/TR]
[TR]
[TH]3[/TH]
[TD][/TD]
[TD="bgcolor: #FFFFFF, align: right"]15-Jun-13[/TD]
[TD="bgcolor: #FFFFFF, align: right"]30-Sep-13[/TD]
[TD="bgcolor: #FFFFFF, align: right"]100,000.00[/TD]
[TD="bgcolor: #FFFFFF, align: right"]14,814.81[/TD]
[TD="bgcolor: #FFFFFF, align: right"]28,703.70[/TD]
[TD="bgcolor: #FFFFFF, align: right"]28,703.70[/TD]
[TD="bgcolor: #FFFFFF, align: right"]27,777.78[/TD]
[TD="bgcolor: #FFFFFF, align: right"]0.00[/TD]
[TD="bgcolor: #FFFFFF, align: right"]0.00[/TD]
[/TR]
[TR]
[TH]4[/TH]
[TD][/TD]
[TD="bgcolor: #FFFFFF, align: right"]16-Jun-13[/TD]
[TD="bgcolor: #FFFFFF, align: right"]31-Oct-13[/TD]
[TD="bgcolor: #FFFFFF, align: right"]100,000.00[/TD]
[TD="bgcolor: #FFFFFF, align: right"]10,869.57[/TD]
[TD="bgcolor: #FFFFFF, align: right"]22,463.77[/TD]
[TD="bgcolor: #FFFFFF, align: right"]22,463.77[/TD]
[TD="bgcolor: #FFFFFF, align: right"]21,739.13[/TD]
[TD="bgcolor: #FFFFFF, align: right"]22,463.77[/TD]
[TD="bgcolor: #FFFFFF, align: right"]0.00[/TD]
[/TR]
[TR]
[TH]5[/TH]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Excel 2003

Cell	Formula
E3	=MAX(0,MIN(F$2,$C3+1)-MAX(E$2,$B3))/($C3-$B3+1)*$D3
F3	=MAX(0,MIN(G$2,$C3+1)-MAX(F$2,$B3))/($C3-$B3+1)*$D3
G3	=MAX(0,MIN(H$2,$C3+1)-MAX(G$2,$B3))/($C3-$B3+1)*$D3
H3	=MAX(0,MIN(I$2,$C3+1)-MAX(H$2,$B3))/($C3-$B3+1)*$D3
I3	=MAX(0,MIN(J$2,$C3+1)-MAX(I$2,$B3))/($C3-$B3+1)*$D3
J3	=MAX(0,MIN(K$2,$C3+1)-MAX(J$2,$B3))/($C3-$B3+1)*$D3

<tbody>
[TD="bgcolor: #FFFFFF"] Worksheet Formulas [TABLE="class: html-maker-worksheet"]
<tbody>[TR]

</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]



N.B. The dates such as Jun-13 are actually real dates first of month i.e. June 1, 2013

Hey guys,

Sorry for bumping this thread, but currently I found this to assist me in spreading out payments over associated periods. My issue is that I use a google form and I would like it to be an array formula so I don't have to constantly update rows when new information is entered. I did slightly modify the formula so that if the A column is approved (showing a "Y") then spread the cost as per the quote above.

For cell E3 in this example I tried:
=ArrayFormula(IF(A3:A="Y",MAX(0,MIN($F$2,$C3:C+1)-MAX($E$2,$B3:B))/($C3:C-$B3:B+1)*D3:D,""))

Assuming everything is approved and showing a "Y" in the A col, I get zeros! But if I don't use the array formula, it is fine. (Please replace "=Arrayformula" that is used by google sheets with ctrl + shift + enter for {} brackets if that makes sense)

Further more, how would I go about adding an additional criterion. Currently the formula above checks if a cost is approved, if so then spreads it over the start and end dates as a portion of its monthly amount. I would like the same sequence but if it is approved but doesn't have a start or end date, it allocates its cost over the month that the date it was approved (which is an additional col).

As always thank you guys for your help. I look forward to seeing how the array formula of this actually works!

Cheers,

 

[Dave Patton]

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

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F	G	H
2	Approved	Contract	Current ?	Current	Starting Date	Ending Date	Contract Amount	Jun-13
3	Y		FALSE	0.00	15-Jun-13	30-Sep-13	100,000.00	14,814.81
3aa

Cell Formulas
Range	Formula
C3	=AND(A3="Y",OR(E3="",F3=""))
D3	=C3*G3
H3	=($C3=FALSE)*MAX(0,MIN(I$2,$F3+1)-MAX(H$2,$E3))/($F3-$E3+1)*$G3