Create Amortization table

[Zizo]

Hello,

I want to create amortization table based on start and end date. I was able to do so using the dax function generate series but I struggle to add another column that will give the month as per below:

[Zizo]

Is it possible to generate amorization schedules in power bi based on a table that contains initial loan balance loan life and interest rate?

Thanks.

[Nick_M]

@Zizo,
It’s possible to that in DAX, but I went the route of using Power Query. I went on the assumption that this would be monthly payments made at the 1st of the month.

1. load the data:
   

   

2. Create a custom column to give me the total years
   

   

3. Another custom column that will give a list of dates in between
   

   

4. Expand that column out, create a new column that references that expanded column if that date is the start of the month or not
   

   

5. Filter on TRUE, and remove misc columns
   

   

also need a Calendar table. Load both of those and relate the Calendar Table to this new table on Date to Days. There’s a function in PQ that created the calendar.

With dates from the Calendar table on rows, you can use the following function to get the monthly payment. DAX does not have a PMT function built in like excel, so have to do this manually:
Monthly Payment =
VAR __PresendtValue =
MAX( Table1[Initial Loan Balance])

Var __Rate=
   MAX( Table1[Interest Rate])

Var __RateBy12=
DIVIDE(__Rate, 12)

Var __NPER=
    MAX( Table1[Years])*12
Return
IF ( [Payment Number] <= __NPER,
(__PresendtValue * __RateBy12 *(1+__RateBy12)^__NPER) / ((1+__RateBy12)^__NPER-1))

But checked against excel and matches, so thats good. Of course, this will only work for monthly payments, but can modify pretty easily to get different duration.

Here’s the function for what payment number we are on, which is needed to cap the table at the right amount of periods:

Payment Number = 

VAR __CurrentPaymentNumber=
CALCULATE( 
    COUNTROWS( DimCalendar), 
    FILTER( ALL( DimCalendar[Date]), 
        MAX(DimCalendar[Date]) >= DimCalendar[Date]))

VAR __MaxPayments=
MAX( Table1[Years]) *12 

RETURN

IF( __CurrentPaymentNumber <= __MaxPayments,__CurrentPaymentNumber)

And the final table:

From there you could get the balance remaining, interest paid, principal paid, etc…

Amort Schedule Start.pbix (158.8 KB)

Nick

[Zizo]

Thanks a lot, it is really a good work and support done. I am trying to go through what is presented and the challenge is that the power query has a different language than DAX but I can try to live with that.

The other thing that doesn’t want to click is the payment number. Max payment is ok for me but the calculate count rows, I could not get the logic. Is there some videos that I can watch or you can illustrate it more simply.

Thanks again!

[sam.mckay]

Amazing support there from Nick…

For COUNTROWS, just image a virtual table being created by what’s in the FILTER area of the formula. The COUNTROWS is just counting up the rows that remain virtual after the filtering is being done (within the current context as well ofcourse)

This is the area you will want to focus on to understand more around this

[Nick_M]

@Zizo,
Yes, Power Query (M) is much different than DAX but they work in tandem. Power Query allows you to create a better data model which allows for easier (which means faster ) DAX. It’s all a learning process though. Just take one step at time and it will start to click. Just keep at it and ask any questions in this forum.

-Nick