• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Instalment / payment plan creation

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

71932
 

Attachments

  • payment plan.xlsx
    11.8 KB · Views: 3
  • 1604664761332.png
    1604664761332.png
    22.2 KB · Views: 0
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?

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

Attachments

  • payment plan.xlsx
    12 KB · Views: 7
Back
Top