cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mrothschild
Responsive Resident
Responsive Resident

loan amortization?

This seems like it should be much easier than I've found it to be.

I have a table that has Columns A, B, and D below and I'm trying to calculate C & E.  

 

C = prior row's E and E = current row C + current row amortization. 

 

I'm less worried about the last period Amortization [Column D], as I think I've already figured that one out, but Power BI is throwing circular reference errors in a way that I wouldn't get in Excel.

 

Thanks in advance!

 

 

 

ABCDE
Loan IDPeriodBeginning DebtAmortizationEnding Debt
X0$9,450 $9,450
X1$9,450($53)$9,398
X2$9,398($53)$9,345
X3$9,345($53)$9,293
X4$9,293($53)$9,240
X5$9,240($53)$9,188
X6$9,188($53)$9,135
X7$9,135($53)$9,083
X8$9,083($53)$9,030
X9$9,030($9,030)$0
Y0$7,500 $7,500
Y1$7,500($45)$7,455
Y2$7,455($45)$7,410
Y3$7,410($45)$7,365
Y4$7,365($45)$7,320
Y5$7,320($45)$7,275
Y6$7,275($7,275)$0
1 ACCEPTED SOLUTION

Wow, well that was way more difficult than it should be for such a simple financial concept, but given my search of the forums, this is a significant shortfall in BI. 

 

The link here: https://www.dropbox.com/s/hryw5ezenojphzn/LoanAmortization.pbix?dl=0

 

has the PBIX file that does the job (though for those more sophisticated, it uses straight line debt amortization rather than PMT amortization).

 

The process, as I suspected, uses the workaround of calculating a running total cumulative amortization.  That DAX is as follows:

Cumulative Amortization = 
CALCULATE(
        SUM(LoanAmortization[Amortization]),
        FILTER(ALL(LoanAmortization),LoanAmortization[Period]<=EARLIER(LoanAmortization[Period])),
        FILTER(ALL(LoanAmortization),LoanAmortization[Loan ID]=EARLIER(LoanAmortization[Loan ID])

))

One also has to calculate the lagged cumulative amortization, which looks like above, but replaces " <= " with just " < ". as shown: 

Cumulative Amortization (lagged) = 
CALCULATE(
        SUM(LoanAmortization[Amortization]),
        FILTER(ALL(LoanAmortization),LoanAmortization[Period]<EARLIER(LoanAmortization[Period])),
        FILTER(ALL(LoanAmortization),LoanAmortization[Loan ID]=EARLIER(LoanAmortization[Loan ID])

))

Once those are calculated, and one has the Starting Loan Amount (which I created by using a related table, called "Starting Loan Amount"), then the Beginning Debt (calculated) is as follows: 

Beginning Debt (calculated) = 
    LoanAmortization[Starting Loan Amount] + LoanAmortization[Cumulative Amortization (lagged)]

 

and the Ending Debt (calculated) is as follows:

 

Ending Debt (calculated) = 
    LoanAmortization[Starting Loan Amount] + LoanAmortization[Cumulative Amortization]

I'm still working through the last period amortization to avoid circular references, but I'm less concerend about that.

 

Hopefully this helps for all those who were looking for a solution.

 

 

Here's how the data looks for comparisons (there's some rounding that leads to imprecise comparisons as I moved from Excel to PBIX and back again, but the process works):

 

Loan IDPeriodStarting Loan AmountBeginning DebtAmortizationEnding DebtCumulative AmortizationCumulative Amortization (lagged)Beginning Debt (calculated)Ending Debt (calculated)
X0$9,450$9,450 $9,450  $9,450$9,450
X1$9,450$9,450($53)$9,397($53) $9,450$9,397
X2$9,450$9,398($53)$9,345($106)($53)$9,397$9,344
X3$9,450$9,345($53)$9,292($159)($106)$9,344$9,291
X4$9,450$9,293($53)$9,240($212)($159)$9,291$9,238
X5$9,450$9,240($53)$9,187($265)($212)$9,238$9,185
X6$9,450$9,188($53)$9,135($318)($265)$9,185$9,132
X7$9,450$9,135($53)$9,082($371)($318)$9,132$9,079
X8$9,450$9,083($53)$9,030($424)($371)$9,079$9,026
X9$9,450$9,030($9,030)$0($9,454)($424)$9,026($4)
Y0$7,500$7,500 $7,500  $7,500$7,500
Y1$7,500$7,500($45)$7,455($45) $7,500$7,455
Y2$7,500$7,455($45)$7,410($90)($45)$7,455$7,410
Y3$7,500$7,410($45)$7,365($135)($90)$7,410$7,365
Y4$7,500$7,365($45)$7,320($180)($135)$7,365$7,320
Y5$7,500$7,320($45)$7,275($225)($180)$7,320$7,275
Y6$7,500$7,275($7,275)$0($7,500)($225)$7,275$0

View solution in original post

5 REPLIES 5
Phogon
Helper II
Helper II

@mrothschild Can you please reupload? This is very useful thanks!

 

v-shex-msft
Community Support
Community Support

Hi @mrothschild ,

 

Power bi not allow you to do recursive calculation or circular reference between two fields.

 

Can you please share some sample data for test?

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Here's a link to a PBIX with the same data posted in my original post. https://www.dropbox.com/s/hryw5ezenojphzn/LoanAmortization.pbix?dl=0 

 

There is another way I think could work around to the right answer, which is to have a column that shows the cumulative amortization for a given period and loan id, such that beginning debt at period (t) = beginning debt at period (0) - cumulative amortization at period (t-1)

 

cumulative period amortization sum of amortization from periods 0 --> t-1

or as [period] * [amortization]

Wow, well that was way more difficult than it should be for such a simple financial concept, but given my search of the forums, this is a significant shortfall in BI. 

 

The link here: https://www.dropbox.com/s/hryw5ezenojphzn/LoanAmortization.pbix?dl=0

 

has the PBIX file that does the job (though for those more sophisticated, it uses straight line debt amortization rather than PMT amortization).

 

The process, as I suspected, uses the workaround of calculating a running total cumulative amortization.  That DAX is as follows:

Cumulative Amortization = 
CALCULATE(
        SUM(LoanAmortization[Amortization]),
        FILTER(ALL(LoanAmortization),LoanAmortization[Period]<=EARLIER(LoanAmortization[Period])),
        FILTER(ALL(LoanAmortization),LoanAmortization[Loan ID]=EARLIER(LoanAmortization[Loan ID])

))

One also has to calculate the lagged cumulative amortization, which looks like above, but replaces " <= " with just " < ". as shown: 

Cumulative Amortization (lagged) = 
CALCULATE(
        SUM(LoanAmortization[Amortization]),
        FILTER(ALL(LoanAmortization),LoanAmortization[Period]<EARLIER(LoanAmortization[Period])),
        FILTER(ALL(LoanAmortization),LoanAmortization[Loan ID]=EARLIER(LoanAmortization[Loan ID])

))

Once those are calculated, and one has the Starting Loan Amount (which I created by using a related table, called "Starting Loan Amount"), then the Beginning Debt (calculated) is as follows: 

Beginning Debt (calculated) = 
    LoanAmortization[Starting Loan Amount] + LoanAmortization[Cumulative Amortization (lagged)]

 

and the Ending Debt (calculated) is as follows:

 

Ending Debt (calculated) = 
    LoanAmortization[Starting Loan Amount] + LoanAmortization[Cumulative Amortization]

I'm still working through the last period amortization to avoid circular references, but I'm less concerend about that.

 

Hopefully this helps for all those who were looking for a solution.

 

 

Here's how the data looks for comparisons (there's some rounding that leads to imprecise comparisons as I moved from Excel to PBIX and back again, but the process works):

 

Loan IDPeriodStarting Loan AmountBeginning DebtAmortizationEnding DebtCumulative AmortizationCumulative Amortization (lagged)Beginning Debt (calculated)Ending Debt (calculated)
X0$9,450$9,450 $9,450  $9,450$9,450
X1$9,450$9,450($53)$9,397($53) $9,450$9,397
X2$9,450$9,398($53)$9,345($106)($53)$9,397$9,344
X3$9,450$9,345($53)$9,292($159)($106)$9,344$9,291
X4$9,450$9,293($53)$9,240($212)($159)$9,291$9,238
X5$9,450$9,240($53)$9,187($265)($212)$9,238$9,185
X6$9,450$9,188($53)$9,135($318)($265)$9,185$9,132
X7$9,450$9,135($53)$9,082($371)($318)$9,132$9,079
X8$9,450$9,083($53)$9,030($424)($371)$9,079$9,026
X9$9,450$9,030($9,030)$0($9,454)($424)$9,026($4)
Y0$7,500$7,500 $7,500  $7,500$7,500
Y1$7,500$7,500($45)$7,455($45) $7,500$7,455
Y2$7,500$7,455($45)$7,410($90)($45)$7,455$7,410
Y3$7,500$7,410($45)$7,365($135)($90)$7,410$7,365
Y4$7,500$7,365($45)$7,320($180)($135)$7,365$7,320
Y5$7,500$7,320($45)$7,275($225)($180)$7,320$7,275
Y6$7,500$7,275($7,275)$0($7,500)($225)$7,275$0

@mrothschild 
The dropbox link for the PBIX file says that the file has been deleted. Can you upload the PBIX again?

Helpful resources

Announcements
Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.