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.

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>

 

[coppertop18]

Solved it using offset formula. Thanks for everything.

 

[iggy_]

In case you still need it

=if(e$7<$o1,0,if(and(month(e$7)=month($o1),year(e$7)=year($o1)),$j1,if(date(year($o1)+$k1,month($o1)-12/$l1,day($o1))=e$7,($h1-$j1)/$l1*2,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]

My offset formula isn't working for every scenario and your formula above seems to double the last payment of the cycle instead of adding another payment to close out the revenue loop. Need to modify it. Thank you anyway.