Create Amortization table

[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