Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: loan amortization?

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

loan amortization?

02-26-2019
11:51 AM

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 |

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-27-2019
01:54 PM

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 |

5 REPLIES 5

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-27-2019
01:03 AM

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.

If this post

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-27-2019
05:12 AM

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]

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

02-27-2019
01:54 PM

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 |

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-08-2020
04:01 AM

@mrothschild

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

Announcements

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

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

Featured Topics

Top Solution Authors

User | Count |
---|---|

268 | |

83 | |

74 | |

67 | |

56 |

Top Kudoed Authors

User | Count |
---|---|

330 | |

84 | |

78 | |

77 | |

71 |