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
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