Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Loan Payment Table

Hi Experts

 

See attached both pBIX and Excel file with sample data

I want to recreate the loan payment method in the excel file "tab" Result in Power BI. In the Excel file i have taken loan 

BrandLoanIDProductMonthsAmount takenMonth/yearTerm
Cap1AAA10% FOR 1M0           20201/01/2021              21

 

And applied a payment Curve over the life of the loan and shown the payment due in each period starting at M0 since the product is 0% for 1 (first month) no payment is due in month 1.

 

there after the payment plan see results tab.

 

Sample PBIX

https://www.dropbox.com/s/ylhimi1gwseqsbf/Test.pbix?dl=0 

 

Sample Excel

https://www.dropbox.com/scl/fi/szev5oqzhoq8n0m6ww873/LoanFile.xlsx?dl=0&rlkey=s0mewkyagrvpanmu4zlqbedbd 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    Please check the following calculated columns.

    Column = CALCULATE(SUM(Loan[Amount taken]),FILTER(Loan,Loan[Brand]='Payment Curve'[Brand]))*'Payment Curve'[Percentage]

Column 2 = CALCULATE(SUM('Payment Curve'[Column]),FILTER('Payment Curve','Payment Curve'[Period]=EARLIER('Payment Curve'[Period])-1))-'Payment Curve'[Column]

     

    Best Regards,

    Jay

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    Please check the following calculated columns.

    Column = CALCULATE(SUM(Loan[Amount taken]),FILTER(Loan,Loan[Brand]='Payment Curve'[Brand]))*'Payment Curve'[Percentage]

Column 2 = CALCULATE(SUM('Payment Curve'[Column]),FILTER('Payment Curve','Payment Curve'[Period]=EARLIER('Payment Curve'[Period])-1))-'Payment Curve'[Column]

     

    Best Regards,

    Jay