Avoid Circular Dependency when evaluating previous rows.

[ooramirez]

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 having a circular dependency error because:

• Interest Charged is calculated using the opening balance (Interest Charged = Opening Balance * Interest Rate)
•  Capital Repaid is calculated using Monthly Instalments and Interest Charged (Capital Repaid = Monthly Instalments - Interest Charged )
• Opening Balance is calculated using the cumulative of Capital Repaid evaluated in all previous rows (Opening Balance = Original Loan - Sum(all previous Capital Repaid)

I do understand why that causes an error. I am just wondering how could I avoid the error. In my mind, there should be away since the circular dependency only occurs if Opening Balance uses the same row data when evaluating Capital Repaid. So far this is what I have:

_Interest Charged = [_OpeningBalance]*[x_piRate]/1200

_Capital Repaid = MonthAmort[_Monthly Installements] - MonthAmort[_Interest Charged]

_OpeningBalance =
_Loan[Loan Value]
    - CALCULATE (
        SUMX ( MonthAmort, [_Capital Repayment] ),
        FILTER (
            MonthAmort[paymentDate],
            MonthAmort[paymentDate] < MAX ( MonthAmort[paymentDate] )
        )
    )

I got this down easily in excel, since you can evaluate the previous row quite straight forward:

Repayment Number	Opening Balance	Monthly Installments	Interest Charged	Capital Repaid	Closing Balance
1	540,000.00	2,332.37	1,435.50	896.87	539,103.13
2	539,103.13	2,332.37	1,433.12	899.25	538,203.88
3	538,203.88	2,332.37	1,430.73	901.64	537,302.24
4	537,302.24	2,332.37	1,428.33	904.04	536,398.20
5	536,398.20	2,332.37	1,425.93	906.44	535,491.75
6	535,491.75	2,332.37	1,423.52	908.85	534,582.90
7	534,582.90	2,332.37	1,421.10	911.27	533,671.63
8	533,671.63	2,332.37	1,418.68	913.69	532,757.94
9	532,757.94	2,332.37	1,416.25	916.12	531,841.82
10	531,841.82	2,332.37	1,413.81	918.56	530,923.26

The reason I am using measures, in case it is relevant, is because I want to dynamically change the Loan, Rate and Loan Period base on "What If" sliders. In that way, I can dynamically explore different scenarios.

Hopefully, it is something that can be done. Any idea?

[DataNinja777]

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,

[AlejandroVazque]

I have exactly the same problem. I see answers said is not possible.. have you managed to get around the circular dependency?

[Anonymous]

This smells of recursive calculations... which are NOT POSSIBLE in DAX. You have to find a closed formula for your amounts to be able to calculate like this. I know there are closed formulas so you have to find them and implement in DAX.

Best
D

[harshnathani]

Hi @ooramirez ,

Please check if this interests you.

https://community.powerbi.com/t5/Desktop/Mortgage-Payment-Calculation/td-p/820036

https://forum.enterprisedna.co/t/create-amortization-table/2404/3

Regards,

Harsh Nathani