Forum Discussion

ooramirez's avatar
ooramirez
New Member
6 years ago

Avoid Circular Dependency when evaluating previous rows.

Hello All,   I am trying to create a power bi to analyse loan options. I have the following measures: Opening Balance, Monthly Instalments, Capital Repaid and Closing Balance Interest Charged. I am...
Circular dependency
Filter
DataNinja777's avatar
DataNinja777
Super User
1 year ago

Hi ooramirez ,

 

You can calculate the implicit interest rate (IRR) in your morgage loan from the information of 

  • Loan #
  • Loan balance
  • Monthly repayments
  • Number of loan repayments
  • Loan start dates

From these key information for preparing loan amortization schedule, you can write a table dax formula below:

LoanAmortization = 
ADDCOLUMNS(
    GENERATE(
        Loans,
        GENERATESERIES(1, Loans[Monthly repayments], 1)  -- Generate periods for each loan
    ),
    "Payment Date", EDATE(Loans[Loan start date], [Value] - 1)  -- Calculate payment date

)

Then write a calculated column like below in the calculated table above.

Cash flow = if([Value]=1,[Loan balance]+[Monthly payments],[Monthly payments])

You can then use the XIRR DAX formula to calculate the implicit interest rate. 

Loan IRR = 
XIRR(LoanAmortization,
    LoanAmortization[Cash Flow], 
    LoanAmortization[Payment Date]
)

This will produce the IRR of your morgage in a measure. 

 

I have attached an example pbix file for your reference. 

 

Best regards,