FINANCIAL MACRO or FORMULA help needed

coppertop18

New Member
Joined
Apr 30, 2009
Messages
29
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]
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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?
 
Upvote 0
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]
 
Upvote 0
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?
 
Upvote 0
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)))))
 
Upvote 0
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.
 
Upvote 0
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!!!
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,271
Messages
6,177,616
Members
452,786
Latest member
k3calloway

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top