Forum Discussion
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!
| A | B | C | D | E |
| Loan ID | Period | Beginning Debt | Amortization | Ending Debt |
| X | 0 | $9,450 | $9,450 | |
| X | 1 | $9,450 | ($53) | $9,398 |
| X | 2 | $9,398 | ($53) | $9,345 |
| X | 3 | $9,345 | ($53) | $9,293 |
| X | 4 | $9,293 | ($53) | $9,240 |
| X | 5 | $9,240 | ($53) | $9,188 |
| X | 6 | $9,188 | ($53) | $9,135 |
| X | 7 | $9,135 | ($53) | $9,083 |
| X | 8 | $9,083 | ($53) | $9,030 |
| X | 9 | $9,030 | ($9,030) | $0 |
| Y | 0 | $7,500 | $7,500 | |
| Y | 1 | $7,500 | ($45) | $7,455 |
| Y | 2 | $7,455 | ($45) | $7,410 |
| Y | 3 | $7,410 | ($45) | $7,365 |
| Y | 4 | $7,365 | ($45) | $7,320 |
| Y | 5 | $7,320 | ($45) | $7,275 |
| Y | 6 | $7,275 | ($7,275) | $0 |
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 ID Period Starting Loan Amount Beginning Debt Amortization Ending Debt Cumulative Amortization Cumulative Amortization (lagged) Beginning Debt (calculated) Ending Debt (calculated) X 0 $9,450 $9,450 $9,450 $9,450 $9,450 X 1 $9,450 $9,450 ($53) $9,397 ($53) $9,450 $9,397 X 2 $9,450 $9,398 ($53) $9,345 ($106) ($53) $9,397 $9,344 X 3 $9,450 $9,345 ($53) $9,292 ($159) ($106) $9,344 $9,291 X 4 $9,450 $9,293 ($53) $9,240 ($212) ($159) $9,291 $9,238 X 5 $9,450 $9,240 ($53) $9,187 ($265) ($212) $9,238 $9,185 X 6 $9,450 $9,188 ($53) $9,135 ($318) ($265) $9,185 $9,132 X 7 $9,450 $9,135 ($53) $9,082 ($371) ($318) $9,132 $9,079 X 8 $9,450 $9,083 ($53) $9,030 ($424) ($371) $9,079 $9,026 X 9 $9,450 $9,030 ($9,030) $0 ($9,454) ($424) $9,026 ($4) Y 0 $7,500 $7,500 $7,500 $7,500 $7,500 Y 1 $7,500 $7,500 ($45) $7,455 ($45) $7,500 $7,455 Y 2 $7,500 $7,455 ($45) $7,410 ($90) ($45) $7,455 $7,410 Y 3 $7,500 $7,410 ($45) $7,365 ($135) ($90) $7,410 $7,365 Y 4 $7,500 $7,365 ($45) $7,320 ($180) ($135) $7,365 $7,320 Y 5 $7,500 $7,320 ($45) $7,275 ($225) ($180) $7,320 $7,275 Y 6 $7,500 $7,275 ($7,275) $0 ($7,500) ($225) $7,275 $0
6 Replies
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
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 ID Period Starting Loan Amount Beginning Debt Amortization Ending Debt Cumulative Amortization Cumulative Amortization (lagged) Beginning Debt (calculated) Ending Debt (calculated) X 0 $9,450 $9,450 $9,450 $9,450 $9,450 X 1 $9,450 $9,450 ($53) $9,397 ($53) $9,450 $9,397 X 2 $9,450 $9,398 ($53) $9,345 ($106) ($53) $9,397 $9,344 X 3 $9,450 $9,345 ($53) $9,292 ($159) ($106) $9,344 $9,291 X 4 $9,450 $9,293 ($53) $9,240 ($212) ($159) $9,291 $9,238 X 5 $9,450 $9,240 ($53) $9,187 ($265) ($212) $9,238 $9,185 X 6 $9,450 $9,188 ($53) $9,135 ($318) ($265) $9,185 $9,132 X 7 $9,450 $9,135 ($53) $9,082 ($371) ($318) $9,132 $9,079 X 8 $9,450 $9,083 ($53) $9,030 ($424) ($371) $9,079 $9,026 X 9 $9,450 $9,030 ($9,030) $0 ($9,454) ($424) $9,026 ($4) Y 0 $7,500 $7,500 $7,500 $7,500 $7,500 Y 1 $7,500 $7,500 ($45) $7,455 ($45) $7,500 $7,455 Y 2 $7,500 $7,455 ($45) $7,410 ($90) ($45) $7,455 $7,410 Y 3 $7,500 $7,410 ($45) $7,365 ($135) ($90) $7,410 $7,365 Y 4 $7,500 $7,365 ($45) $7,320 ($180) ($135) $7,365 $7,320 Y 5 $7,500 $7,320 ($45) $7,275 ($225) ($180) $7,320 $7,275 Y 6 $7,500 $7,275 ($7,275) $0 ($7,500) ($225) $7,275 $0
mrothschild Can you please reupload? This is very useful thanks!
