cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
mrothschild Member
Member

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

Accepted Solutions
mrothschild Member
Member

Re: loan 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
3 REPLIES 3
Community Support Team
Community Support Team

Re: loan amortization?

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 Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
mrothschild Member
Member

Re: loan amortization?

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]

mrothschild Member
Member

Re: loan 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