loan amortization?

[mrothschild]

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

[Anonymous]

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

[v-shex-msft]

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

[mrothschild]

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

[csinha]

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