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.

This is the Annual contract RevenueThis is the % Revenue Share that the Company gets to keepThis is the amount of Revenue that the company gets to keepThis is the amount of the Pre-payment that Company receives when it signs the contractPrepayment AmountDuration in YearsPayment FrequencyStart date (but not payment date)First payment AFTER the start dateCalcualted start date
Client 1 128,00050% 64,00025% 16,00052Feb-182Apr-18
Client 2 350,00025% 87,50025% 21,87534Jun-182Aug-18
Client 3 350,00050% 175,00050% 87,50034Jun-182Aug-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-18Feb-18Mar-18Apr-18May-18Jun-18Jul-18Aug-18Sep-18Oct-18Nov-18Dec-18Jan-19Feb-19Mar-19Apr-19May-19Jun-19Jul-19Aug-19Sep-19Oct-19Nov-19Dec-19Jan-20Feb-20Mar-20Apr-20May-20Jun-20Jul-20Aug-20Sep-20Oct-20Nov-20Dec-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>
 

Some videos you may like

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
Joined
Apr 30, 2009
Messages
29
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
Joined
Mar 28, 2018
Messages
59
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
AD
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
Joined
Apr 30, 2009
Messages
29
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
Joined
Mar 28, 2018
Messages
59

ADVERTISEMENT

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

iggy_

Board Regular
Joined
Mar 28, 2018
Messages
59

ADVERTISEMENT

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
Joined
Apr 30, 2009
Messages
29
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!!!
 

coppertop18

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

Watch MrExcel Video

Forum statistics

Threads
1,109,473
Messages
5,529,045
Members
409,849
Latest member
J7House1984
Top