# FINANCIAL MACRO or FORMULA help needed

#### coppertop18

##### New Member
Please help create a formula or macro based on the information below. Prefer to stay away from VBA.

 This is the Annual contract Revenue This is the % Revenue Share that the Company gets to keep This is the amount of Revenue that the company gets to keep This is the amount of the Pre-payment that Company receives when it signs the contract Prepayment Amount Duration in Years Payment Frequency Start date (but not payment date) First payment AFTER the start date Calcualted start date Client 1 128,000 50% 64,000 25% 16,000 5 2 Feb-18 2 Apr-18 Client 2 350,000 25% 87,500 25% 21,875 3 4 Jun-18 2 Aug-18 Client 3 350,000 50% 175,000 50% 87,500 3 4 Jun-18 2 Aug-18

<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

this is what the resulting formula needs to show
 Jan-18 Feb-18 Mar-18 Apr-18 May-18 Jun-18 Jul-18 Aug-18 Sep-18 Oct-18 Nov-18 Dec-18 Jan-19 Feb-19 Mar-19 Apr-19 May-19 Jun-19 Jul-19 Aug-19 Sep-19 Oct-19 Nov-19 Dec-19 Jan-20 Feb-20 Mar-20 Apr-20 May-20 Jun-20 Jul-20 Aug-20 Sep-20 Oct-20 Nov-20 Dec-20 - - - 16,000 - - - - - 24,000 - - - - - 40,000 - - - - - 24,000 - - - - - 40,000 - - - - - 24,000 - - - - - - - - - 21,875 - - 16,406 - - 16,406 - - 16,406 - - 38,281 - - 16,406 - - 16,406 - - 16,406 - - 38,281 - - 16,406 - - - - - - - - 87,500 - - 21,875 - - 21,875 - - 21,875 - - 109,375 - - 21,875 - - 21,875 - - 21,875 - - 109,375 - - 21,875 -

<colgroup><col><col span="2"><col><col span="32"></colgroup><tbody>
</tbody>

<tbody>
</tbody>

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### coppertop18

##### New Member
Maybe a simpler ask is how to create a rolling EDATE function using a fixed interval based on recurring payments. What does that EDATE formula look like?

#### iggy_

##### Board Regular
Can try something like this.

Excel 2016 (Windows) 32 bit
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z
AA
AB
AC
AE
AF
AG
AH
AI
AJ
AK
AL
AM
AN
1
Client 1
128,000​
50%​
64,000​
25%​
16,000​
5​
2​
18-Feb​
2​
18-Apr​
2
Client 2
350,000​
25%​
87,500​
25%​
21,875​
3​
4​
18-Jun​
2​
18-Aug​
3
Client 3
350,000​
50%​
175,000​
50%​
87,500​
3​
4​
18-Jun​
2​
18-Aug​
4
5
6
7
18-Jan​
18-Feb​
18-Mar​
18-Apr​
18-May​
18-Jun​
18-Jul​
18-Aug​
18-Sep​
18-Oct​
18-Nov​
18-Dec​
18-Jan​
18-Feb​
18-Mar​
18-Apr​
18-May​
18-Jun​
18-Jul​
18-Aug​
18-Sep​
18-Oct​
18-Nov​
18-Dec​
18-Jan​
18-Feb​
18-Mar​
18-Apr​
18-May​
18-Jun​
18-Jul​
18-Aug​
18-Sep​
18-Oct​
18-Nov​
18-Dec​
8
-​
-​
-​
16,000​
-​
-​
-​
-​
-​
24,000​
-​
-​
-​
-​
-​
40,000​
-​
-​
-​
-​
-​
24,000​
-​
-​
-​
-​
-​
40,000​
-​
-​
-​
-​
-​
24,000​
-​
-​
9
-​
-​
-​
-​
-​
-​
-​
21,875​
-​
-​
16,406​
-​
-​
16,406​
-​
-​
16,406​
-​
-​
38,281​
-​
-​
16,406​
-​
-​
16,406​
-​
-​
16,406​
-​
-​
38,281​
-​
-​
16,406​
-​
10
-​
-​
-​
-​
-​
-​
-​
87,500​
-​
-​
21,875​
-​
-​
21,875​
-​
-​
21,875​
-​
-​
109,375​
-​
-​
21,875​
-​
-​
21,875​
-​
-​
21,875​
-​
-​
109,375​
-​
-​
21,875​
-​
 Sheet: Sheet4

Paste into E8 and copy across

Excel 2016 (Windows) 32 bit
E
8
=IF(E\$7<\$O1,0,IF(E\$7=\$O1,\$J1,IF(E\$7>=DATE(YEAR(\$O1)+\$K1,MONTH(\$O1),DAY(\$O1)),0,IF(MONTH(E\$7)=MONTH(\$O1),(\$H1-\$J1)/\$L1+\$J1,IF(MOD(ABS(MONTH(E\$7)-MONTH(\$O1)),12/\$L1)=0,(\$H1-\$J1)/\$L1,0)))))​
 Sheet: Sheet4

#### coppertop18

##### New Member
This works great for every payment but the pre-payment. The 1st prepayment isn't populating using this formula for me. Am I doing something wrong?

#### iggy_

##### Board Regular

I have it set so that the dates are equal so in my table start date would be 4/30/18 in the above table and the below range. Alternatively you can change that part of the formula to the one below. Change is bolded:

=IF(E\$7<\$O1,0,IF(AND(MONTH(E\$7)=MONTH(\$O1),YEAR(E\$7)=YEAR(\$O1)),\$J1,IF(E\$7>=DATE(YEAR(\$O1)+\$K1,MONTH(\$O1),DAY(\$O1)),0,IF(MONTH(E\$7)=MONTH(\$O1),(\$H1-\$J1)/\$L1+\$J1,IF(MOD(ABS(MONTH(E\$7)-MONTH(\$O1)),12/\$L1)=0,(\$H1-\$J1)/\$L1,0)))))

#### coppertop18

##### New Member
nope. still not working...

#### iggy_

##### Board Regular

If everything else is working then I would look in that part of the formula. It works in the sheet that I built but I don't have your exact data.

#### coppertop18

##### New Member
Never mind. I fixed it. It was a date mismatch. One started on the 1st of every month and the reference cells started 18th of the month. It's fixed. Thank you so much for the help!!!

Np!

#### coppertop18

##### New Member
Ok. one more issue found. The final payment on the last month of the contract needs to be reflected as total of the duration of the contract minus the sum of all the prior payments. Currently, the total of the monthly payments doesn't add up to the total of all contract years... Need to close the loop. Please help.

Replies
3
Views
45
Replies
10
Views
114
Replies
5
Views
59
Replies
3
Views
78
Replies
1
Views
69