Forum Discussion
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 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
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
The dropbox link for the PBIX file says that the file has been deleted. Can you upload the PBIX again?I have the same issue (this time with PMT amortization with an added caveat that borrowers can do partial repayments, hence meaning I cannot know for certain the amortization schedule
