cancel
Showing results for
Did you mean:
Responsive Resident

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.

 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
1 ACCEPTED SOLUTION
Responsive Resident

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.

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
5 REPLIES 5
Helper II

Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.
Responsive Resident

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]

Responsive Resident

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.

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

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

Announcements

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors