FINANCIAL MACRO or FORMULA help needed

[coppertop18]

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

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

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]this is what the resulting formula needs to show
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][TABLE="width: 2387"]
<colgroup><col><col span="2"><col><col span="32"></colgroup><tbody>[TR]
[TD]Jan-18[/TD]
[TD]Feb-18[/TD]
[TD]Mar-18[/TD]
[TD]Apr-18[/TD]
[TD]May-18[/TD]
[TD]Jun-18[/TD]
[TD]Jul-18[/TD]
[TD]Aug-18[/TD]
[TD]Sep-18[/TD]
[TD]Oct-18[/TD]
[TD]Nov-18[/TD]
[TD]Dec-18[/TD]
[TD]Jan-19[/TD]
[TD]Feb-19[/TD]
[TD]Mar-19[/TD]
[TD]Apr-19[/TD]
[TD]May-19[/TD]
[TD]Jun-19[/TD]
[TD]Jul-19[/TD]
[TD]Aug-19[/TD]
[TD]Sep-19[/TD]
[TD]Oct-19[/TD]
[TD]Nov-19[/TD]
[TD]Dec-19[/TD]
[TD]Jan-20[/TD]
[TD]Feb-20[/TD]
[TD]Mar-20[/TD]
[TD]Apr-20[/TD]
[TD]May-20[/TD]
[TD]Jun-20[/TD]
[TD]Jul-20[/TD]
[TD]Aug-20[/TD]
[TD]Sep-20[/TD]
[TD]Oct-20[/TD]
[TD]Nov-20[/TD]
[TD]Dec-20[/TD]
[/TR]
[TR]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 16,000[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 24,000[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 40,000[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 24,000[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 40,000[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 24,000[/TD]
[TD] - [/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 21,875[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 16,406[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 16,406[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 16,406[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 38,281[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 16,406[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 16,406[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 16,406[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 38,281[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 16,406[/TD]
[TD] - [/TD]
[/TR]
[TR]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 87,500[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 21,875[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 21,875[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 21,875[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 109,375[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 21,875[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 21,875[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 21,875[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 109,375[/TD]
[TD] - [/TD]
[TD] - [/TD]
[TD] 21,875[/TD]
[TD] - [/TD]
[/TR]
</tbody>[/TABLE]

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

 

[coppertop18]

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_]

Can try something like this.

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][th]

J​

[/th][th]

K​

[/th][th]

L​

[/th][th]

M​

[/th][th]

N​

[/th][th]

O​

[/th][th]

P​

[/th][th]

Q​

[/th][th]

R​

[/th][th]

S​

[/th][th]

T​

[/th][th]

U​

[/th][th]

V​

[/th][th]

W​

[/th][th]

X​

[/th][th]

Y​

[/th][th]

Z​

[/th][th]

AA​

[/th][th]

AB​

[/th][th]

AC​

[/th][th]

AD​

[/th][th]

AE​

[/th][th]

AF​

[/th][th]

AG​

[/th][th]

AH​

[/th][th]

AI​

[/th][th]

AJ​

[/th][th]

AK​

[/th][th]

AL​

[/th][th]

AM​

[/th][th]

AN​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td=bgcolor:#FAFAFA]Client 1[/td][td=bgcolor:#FAFAFA]

128,000​

[/td][td=bgcolor:#FAFAFA]

50%​

[/td][td=bgcolor:#FAFAFA]

64,000​

[/td][td=bgcolor:#FAFAFA]

25%​

[/td][td=bgcolor:#FAFAFA]

16,000​

[/td][td=bgcolor:#FAFAFA]

5​

[/td][td=bgcolor:#FAFAFA]

2​

[/td][td=bgcolor:#FAFAFA]

18-Feb​

[/td][td=bgcolor:#FAFAFA]

2​

[/td][td=bgcolor:#FAFAFA]

18-Apr​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td=bgcolor:#FAFAFA]Client 2[/td][td=bgcolor:#FAFAFA]

350,000​

[/td][td=bgcolor:#FAFAFA]

25%​

[/td][td=bgcolor:#FAFAFA]

87,500​

[/td][td=bgcolor:#FAFAFA]

25%​

[/td][td=bgcolor:#FAFAFA]

21,875​

[/td][td=bgcolor:#FAFAFA]

3​

[/td][td=bgcolor:#FAFAFA]

4​

[/td][td=bgcolor:#FAFAFA]

18-Jun​

[/td][td=bgcolor:#FAFAFA]

2​

[/td][td=bgcolor:#FAFAFA]

18-Aug​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td=bgcolor:#FAFAFA]Client 3[/td][td=bgcolor:#FAFAFA]

350,000​

[/td][td=bgcolor:#FAFAFA]

50%​

[/td][td=bgcolor:#FAFAFA]

175,000​

[/td][td=bgcolor:#FAFAFA]

50%​

[/td][td=bgcolor:#FAFAFA]

87,500​

[/td][td=bgcolor:#FAFAFA]

3​

[/td][td=bgcolor:#FAFAFA]

4​

[/td][td=bgcolor:#FAFAFA]

18-Jun​

[/td][td=bgcolor:#FAFAFA]

2​

[/td][td=bgcolor:#FAFAFA]

18-Aug​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td=bgcolor:#FAFAFA]

18-Jan​

[/td][td=bgcolor:#FAFAFA]

18-Feb​

[/td][td=bgcolor:#FAFAFA]

18-Mar​

[/td][td=bgcolor:#FAFAFA]

18-Apr​

[/td][td=bgcolor:#FAFAFA]

18-May​

[/td][td=bgcolor:#FAFAFA]

18-Jun​

[/td][td=bgcolor:#FAFAFA]

18-Jul​

[/td][td=bgcolor:#FAFAFA]

18-Aug​

[/td][td=bgcolor:#FAFAFA]

18-Sep​

[/td][td=bgcolor:#FAFAFA]

18-Oct​

[/td][td=bgcolor:#FAFAFA]

18-Nov​

[/td][td=bgcolor:#FAFAFA]

18-Dec​

[/td][td=bgcolor:#FAFAFA]

18-Jan​

[/td][td=bgcolor:#FAFAFA]

18-Feb​

[/td][td=bgcolor:#FAFAFA]

18-Mar​

[/td][td=bgcolor:#FAFAFA]

18-Apr​

[/td][td=bgcolor:#FAFAFA]

18-May​

[/td][td=bgcolor:#FAFAFA]

18-Jun​

[/td][td=bgcolor:#FAFAFA]

18-Jul​

[/td][td=bgcolor:#FAFAFA]

18-Aug​

[/td][td=bgcolor:#FAFAFA]

18-Sep​

[/td][td=bgcolor:#FAFAFA]

18-Oct​

[/td][td=bgcolor:#FAFAFA]

18-Nov​

[/td][td=bgcolor:#FAFAFA]

18-Dec​

[/td][td=bgcolor:#FAFAFA]

18-Jan​

[/td][td=bgcolor:#FAFAFA]

18-Feb​

[/td][td=bgcolor:#FAFAFA]

18-Mar​

[/td][td=bgcolor:#FAFAFA]

18-Apr​

[/td][td=bgcolor:#FAFAFA]

18-May​

[/td][td=bgcolor:#FAFAFA]

18-Jun​

[/td][td=bgcolor:#FAFAFA]

18-Jul​

[/td][td=bgcolor:#FAFAFA]

18-Aug​

[/td][td=bgcolor:#FAFAFA]

18-Sep​

[/td][td=bgcolor:#FAFAFA]

18-Oct​

[/td][td=bgcolor:#FAFAFA]

18-Nov​

[/td][td=bgcolor:#FAFAFA]

18-Dec​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]

-​

[/td][td]

-​

[/td][td]

-​

[/td][td]

16,000​

[/td][td]

-​

[/td][td]

-​

[/td][td]

-​

[/td][td]

-​

[/td][td]

-​

[/td][td]

24,000​

[/td][td]

-​

[/td][td]

-​

[/td][td]

-​

[/td][td]

-​

[/td][td]

-​

[/td][td]

40,000​

[/td][td]

-​

[/td][td]

-​

[/td][td]

-​

[/td][td]

-​

[/td][td]

-​

[/td][td]

24,000​

[/td][td]

-​

[/td][td]

-​

[/td][td]

-​

[/td][td]

-​

[/td][td]

-​

[/td][td]

40,000​

[/td][td]

-​

[/td][td]

-​

[/td][td]

-​

[/td][td]

-​

[/td][td]

-​

[/td][td]

24,000​

[/td][td]

-​

[/td][td]

-​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

9​

[/td][td]

-​

[/td][td]

-​

[/td][td]

-​

[/td][td]

-​

[/td][td]

-​

[/td][td]

-​

[/td][td]

-​

[/td][td]

21,875​

[/td][td]

-​

[/td][td]

-​

[/td][td]

16,406​

[/td][td]

-​

[/td][td]

-​

[/td][td]

16,406​

[/td][td]

-​

[/td][td]

-​

[/td][td]

16,406​

[/td][td]

-​

[/td][td]

-​

[/td][td]

38,281​

[/td][td]

-​

[/td][td]

-​

[/td][td]

16,406​

[/td][td]

-​

[/td][td]

-​

[/td][td]

16,406​

[/td][td]

-​

[/td][td]

-​

[/td][td]

16,406​

[/td][td]

-​

[/td][td]

-​

[/td][td]

38,281​

[/td][td]

-​

[/td][td]

-​

[/td][td]

16,406​

[/td][td]

-​

[/td][/tr]


[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

10​

[/td][td]

-​

[/td][td]

-​

[/td][td]

-​

[/td][td]

-​

[/td][td]

-​

[/td][td]

-​

[/td][td]

-​

[/td][td]

87,500​

[/td][td]

-​

[/td][td]

-​

[/td][td]

21,875​

[/td][td]

-​

[/td][td]

-​

[/td][td]

21,875​

[/td][td]

-​

[/td][td]

-​

[/td][td]

21,875​

[/td][td]

-​

[/td][td]

-​

[/td][td]

109,375​

[/td][td]

-​

[/td][td]

-​

[/td][td]

21,875​

[/td][td]

-​

[/td][td]

-​

[/td][td]

21,875​

[/td][td]

-​

[/td][td]

-​

[/td][td]

21,875​

[/td][td]

-​

[/td][td]

-​

[/td][td]

109,375​

[/td][td]

-​

[/td][td]

-​

[/td][td]

21,875​

[/td][td]

-​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet4[/td][/tr][/table]

Paste into E8 and copy across

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

E​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td]

=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)))))​

[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet4[/td][/tr][/table]

 

[coppertop18]

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_]

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]

nope. still not working...

 

[iggy_]

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]

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!!!

 

[iggy_]

Np!

 

[coppertop18]

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.