Payment Schedules

[AffanAlvi]

Hi guys, 

I am struggling in plotting monthly receipts which are proportioned based on contract start month. 12% of my contracts (signed in let's say Jan 22) start at month 0 and receipts starts from 3 months after contract start month. Similarly 33% of my contracts (signed in Jan 22) starts in month 1 and hence receipts starts from month 4. 

This receipts proportion is same for all contracts signed irrespective of month of signing.

Hence in Mar 22, I'll be receiving 12% of contracts signed in Mar 22 (which is month 0) and 33% of contracts signed in Jan 22 (i.e. month 3 from Jan 22) and so on.

Attached is a sample sheet for your reference.  

Please let me know how to plot receipts for each month. 

[#2 · Accepted solution]

Hi@AffanAlvi 

As per my understanding you need cumulative reciepts for each month and at the same time each month contract must follow the collection ratio. Please use below formula to achieve your requirement:

=IF(L$1<$A3+2,0,IF(L$1-$A3+1<=15,$C3*IFERROR(INDEX($G$3:$G$15,(L$1-1-$A3)),0),0))

A sample file is also attached for your reference. Please let me know if it works as desired.

Thanks

Tauqeer

[#3]

Thanks @tauqeeracma 

this is exactly what I needed.