Instalment / payment plan creation

[ferocious12]

Hi All,

I have to create instalment plan for apartment units sold The payment plan for each apartment sold is different as instalment plan differs for each buyer.
Is there a way I can recreate the instalment plan based on inputs from row 2 to 6 to get output at row 12-19 ( I have attached a sample file as well). Thanks

 

[Hui]

C3: 1%
C5: 2%
C12: =IF(VLOOKUP(B12,$B$2:$C$6,2)="",1-SUM($C$11:C11),VLOOKUP(B12,$B$2:$C$6,2))
Copy C12 down

 

[ferocious12]

C3: 1%
C5: 2%
C12: =IF(VLOOKUP(B12,$B$2:$C$6,2)="",1-SUM($C$11:C11),VLOOKUP(B12,$B$2:$C$6,2))
Copy C12 down

Mant thanks Hui. It partially works. The problem arises at the end of 1% block it is doing 100%-sum(....) calculation, instead after the end of last instalment Mar 21 (screenshot below). Also, is there a way dates/months in column B are generated based on values in B2-B6?

 

[Excel Wizard]

Please try at
B12
=IF(EDATE(B$2,ROWS(B$12:B12)-1)>B$6,"",EDATE(B$2,ROWS(B$12:B12)-1))

C12
=IF(B13="",IF(B12="","",1-SUM(C$11:C11)),LOOKUP(B12,B$2:B$6/(C$2:C$6<>""),C$2:C$6))

 

[ferocious12]

Please try at
B12
=IF(EDATE(B$2,ROWS(B$12:B12)-1)>B$6,"",EDATE(B$2,ROWS(B$12:B12)-1))

C12
=IF(B13="",IF(B12="","",1-SUM(C$11:C11)),LOOKUP(B12,B$2:B$6/(C$2:C$6<>""),C$2:C$6))

Superb. Thanks