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

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### 2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors