cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
CheenuSing Super Contributor
Super Contributor

Help in building cashflow table with interest computed on diminishing balance

 

Hi @Zubair_Muhammad ,  @OwenAuger , @MattAllington 

 

My table has only the columns MonthName, Month, Cashflow

 

                      Month    Cashflow      Interest    liability    liability c/f

 
Aug-18 0      6,838.17                        6,838.17
Sep-18 1 -300 28.49 271.51                  6,566.66
Oct-18 2 -300 27.36 272.64                    6,294.02
Nov-18 3 -300 26.23 273.77                  6,020.25
Dec-18 4 -300 25.08 274.92                    5,745.33
Jan-19 5 -300 23.94 276.06                  5,469.27
Feb-19 6 -300 22.79 277.21                    5,192.06
Mar-19 7 -300 21.63 278.37                  4,913.69
Apr-19 8 -300 20.47 279.53                    4,634.17
May-19 9 -300 19.31 280.69                  4,353.48
Jun-19 10 -300 18.14 281.86                    4,071.62
Jul-19 11 -300 16.97 283.03                  3,788.58
Aug-19 12 -300 15.79 284.21                    3,504.37
Sep-19 13 -300 14.60 285.40                  3,218.97
Oct-19 14 -300 13.41 286.59                    2,932.38
Nov-19 15 -300 12.22 287.78                  2,644.60
Dec-19 16 -300 11.02 288.98                    2,355.62
Jan-20 17 -300 9.82 290.18                  2,065.43
Feb-20 18 -300 8.61 291.39                    1,774.04
Mar-20 19 -300 7.39 292.61                  1,481.43
Apr-20 20 -300 6.17 293.83                    1,187.60
May-20 21 -300 4.95 295.05                      892.55
Jun-20 22 -300 3.72 296.28                        596.27
Jul-20 23 -300 2.48 297.52                      298.76
Aug-20 24 -300 1.24 298.76                          0.00

 

Need help to compute interest, Liablilty,Liability C/F

 

The formula for Interest = ( Previous Month Liability C/F) * (0.05/12) 

The formula for  Liability  =  -Cashflow-Interest

Liability C/F = PreviousMonthLiability C/F - Liability

 

Note that for the Month = 0, the interest, Liability is Zero.

 

How this can be achieved in PowerQuery or DAX when want to show the final output as a table visual.

 

Appreciate any help on this

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
2 ACCEPTED SOLUTIONS

Accepted Solutions
OwenAuger Super Contributor
Super Contributor

Re: Help in building cashflow table with interest computed on diminishing balance

Hi @CheenuSing 

 

These sort of iterative calculations can be done with either Power Query or DAX. I have attached an example showing both.

 

  1. Using Power Query, you can use List.Generate to create a list containing the Interest and other values, in a similar fashion to a While-loop. The M code I used is below, with the List.Generate portion in red. The list is then converted to a table and joined with the original table.
    Possibly this could be converted into a more general function:
    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdC9CsIwFIbhW5HMjeQk/UnHgjgI6uBYOtRSXESLWMG7tz1fweQ0kOVJIDlvXatqvGnyKlFm2pt55Yl3fkuFapJaXfoBxzRt7YxhPXdvqA319PxAXai7voOmoR7ah6ZykizUfX+F5qEe2xe0CLUaFvXx3S+0jF4bl9fIxHxfOJqOkzBH43EKZrdqwZyuYjBnqxrMucxh579RIXuAvQwCLmURZmtkEjDJJmArm4CdbAL+T9n8AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [MonthName = _t, Month = _t, Cashflow = _t]),
        ChangeType1 = Table.TransformColumnTypes(Source,{{"MonthName", type date}, {"Month", Int64.Type}, {"Cashflow", type number}}),
        SortedRows = Table.Sort(ChangeType1,{{"Month", Order.Ascending}}),
        Cashflow = SortedRows[Cashflow],
        CashflowCount = List.Count(Cashflow),
        CalcInterest =
            List.Generate(
                ()=>[Index = 0,Interest = null, Liability = null, LiabilityCF = Cashflow{0}],
                each [Index]<CashflowCount,
                each [
                    Index = [Index]+1,
                    Interest = [LiabilityCF]*0.05/12,
                    Liability = -Cashflow{Index}-Interest,
                    LiabilityCF = [LiabilityCF]-Liability
                ]
            ),
        ConvertedToTable = Table.FromList(CalcInterest, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        ExpandedColumns = Table.ExpandRecordColumn(ConvertedToTable, "Column1", {"Index", "Interest", "Liability", "LiabilityCF"}, {"Index", "Interest", "Liability", "Liability c/f"}),
        JoinTables = Table.NestedJoin(SortedRows,{"Month"},ExpandedColumns,{"Index"},"Interest"),
        #"Expanded Interest" = Table.ExpandTableColumn(JoinTables, "Interest", {"Interest", "Liability", "Liability c/f"}, {"Interest", "Liability", "Liability c/f"}),
        ChangeType2 = Table.TransformColumnTypes(#"Expanded Interest",{{"Interest", type number}, {"Liability", type number}, {"Liability c/f", type number}})
    in
        ChangeType2
  2. Using DAX, you can create calculation columns as follows:
    Liability c/f is calculated by compounding each cashflow value (positive or negative) at the monthly interest rate, by the number of months between the cashflow and the current row's month. The other columns can then be derived from this:
    Liability c/f DAX = 
    VAR InterestRatePerMonth =
        0.05/12
    VAR CurrentMonth =
        Data[Month]
    VAR CashflowToDate =
        CALCULATETABLE ( 
            ADDCOLUMNS (
                SUMMARIZE( Data, Data[Month] ),
                "CashflowSum", CALCULATE ( SUM ( Data[Cashflow] ) )
            ),
            Data[Month] <= CurrentMonth,
            ALL ( Data )
        )
    VAR LiabilityCF =
        SUMX ( 
            CashflowToDate,
            [CashflowSum] * ( 1 + InterestRatePerMonth ) ^ ( CurrentMonth - Data[Month] )
        )
    RETURN
        LiabilityCF
    
    Interest DAX = 
    VAR InterestRatePerMonth =
        0.05/12
    VAR CurrentMonth =
        Data[Month]
    VAR Interest =
        CALCULATE (
            SUM ( Data[Liability c/f DAX] ),
            ALL ( Data ),
            Data[Month] = CurrentMonth - 1
        ) * InterestRatePerMonth
    RETURN
        Interest
    
    Liability DAX = 
    IF ( 
        Data[Month] > 0,
        - Data[Cashflow] - Data[Interest DAX]
    )

Hopefully that's a useful illustration of how these sorts of calculations can be done. 

 

Cheers 
Owen Smiley Happy



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
Super User

Re: Help in building cashflow table with interest computed on diminishing balance

@CheenuSing 

 

Similar to List.Generate used by @OwenAuger  we can also use List.Accumulate

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdBJCgMxEEPRqwSv26HkuXOVpu9/jdhksFAtBIK3+9cVDNFGxHjYyywcYa2NPJ7o4T4+fpJjLuZ5vwgjTIogzIqJsAguP39YFRNhU8yEXbEQDsVKeCo2QphqZ9VGK/BWjbTybnWVjFUzgTNBO4E7wYVCTPZXVyqxulSZ1bUqpMm1qqyuVWN1rTqrtpqdSXer+w0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Month Name" = _t, Month = _t, Cashflow = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Month Name", type datetime}, {"Month", Int64.Type}, {"Cashflow", type number}}),
PL=Table.AddColumn(ChangedType,"Liability CF",
each
let mymonth=[Month],
myfirstCashflow=Table.SelectRows(ChangedType,each [Month]=0)[Cashflow]{0},
mylist=Table.SelectRows(ChangedType, each [Month]<=mymonth and [Month]>0)[Cashflow] in
List.Accumulate(mylist, myfirstCashflow, (state, current) => state + state*.05/12 + current))
in
PL

CS.png

 

8 REPLIES 8
OwenAuger Super Contributor
Super Contributor

Re: Help in building cashflow table with interest computed on diminishing balance

Hi @CheenuSing 

 

These sort of iterative calculations can be done with either Power Query or DAX. I have attached an example showing both.

 

  1. Using Power Query, you can use List.Generate to create a list containing the Interest and other values, in a similar fashion to a While-loop. The M code I used is below, with the List.Generate portion in red. The list is then converted to a table and joined with the original table.
    Possibly this could be converted into a more general function:
    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdC9CsIwFIbhW5HMjeQk/UnHgjgI6uBYOtRSXESLWMG7tz1fweQ0kOVJIDlvXatqvGnyKlFm2pt55Yl3fkuFapJaXfoBxzRt7YxhPXdvqA319PxAXai7voOmoR7ah6ZykizUfX+F5qEe2xe0CLUaFvXx3S+0jF4bl9fIxHxfOJqOkzBH43EKZrdqwZyuYjBnqxrMucxh579RIXuAvQwCLmURZmtkEjDJJmArm4CdbAL+T9n8AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [MonthName = _t, Month = _t, Cashflow = _t]),
        ChangeType1 = Table.TransformColumnTypes(Source,{{"MonthName", type date}, {"Month", Int64.Type}, {"Cashflow", type number}}),
        SortedRows = Table.Sort(ChangeType1,{{"Month", Order.Ascending}}),
        Cashflow = SortedRows[Cashflow],
        CashflowCount = List.Count(Cashflow),
        CalcInterest =
            List.Generate(
                ()=>[Index = 0,Interest = null, Liability = null, LiabilityCF = Cashflow{0}],
                each [Index]<CashflowCount,
                each [
                    Index = [Index]+1,
                    Interest = [LiabilityCF]*0.05/12,
                    Liability = -Cashflow{Index}-Interest,
                    LiabilityCF = [LiabilityCF]-Liability
                ]
            ),
        ConvertedToTable = Table.FromList(CalcInterest, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        ExpandedColumns = Table.ExpandRecordColumn(ConvertedToTable, "Column1", {"Index", "Interest", "Liability", "LiabilityCF"}, {"Index", "Interest", "Liability", "Liability c/f"}),
        JoinTables = Table.NestedJoin(SortedRows,{"Month"},ExpandedColumns,{"Index"},"Interest"),
        #"Expanded Interest" = Table.ExpandTableColumn(JoinTables, "Interest", {"Interest", "Liability", "Liability c/f"}, {"Interest", "Liability", "Liability c/f"}),
        ChangeType2 = Table.TransformColumnTypes(#"Expanded Interest",{{"Interest", type number}, {"Liability", type number}, {"Liability c/f", type number}})
    in
        ChangeType2
  2. Using DAX, you can create calculation columns as follows:
    Liability c/f is calculated by compounding each cashflow value (positive or negative) at the monthly interest rate, by the number of months between the cashflow and the current row's month. The other columns can then be derived from this:
    Liability c/f DAX = 
    VAR InterestRatePerMonth =
        0.05/12
    VAR CurrentMonth =
        Data[Month]
    VAR CashflowToDate =
        CALCULATETABLE ( 
            ADDCOLUMNS (
                SUMMARIZE( Data, Data[Month] ),
                "CashflowSum", CALCULATE ( SUM ( Data[Cashflow] ) )
            ),
            Data[Month] <= CurrentMonth,
            ALL ( Data )
        )
    VAR LiabilityCF =
        SUMX ( 
            CashflowToDate,
            [CashflowSum] * ( 1 + InterestRatePerMonth ) ^ ( CurrentMonth - Data[Month] )
        )
    RETURN
        LiabilityCF
    
    Interest DAX = 
    VAR InterestRatePerMonth =
        0.05/12
    VAR CurrentMonth =
        Data[Month]
    VAR Interest =
        CALCULATE (
            SUM ( Data[Liability c/f DAX] ),
            ALL ( Data ),
            Data[Month] = CurrentMonth - 1
        ) * InterestRatePerMonth
    RETURN
        Interest
    
    Liability DAX = 
    IF ( 
        Data[Month] > 0,
        - Data[Cashflow] - Data[Interest DAX]
    )

Hopefully that's a useful illustration of how these sorts of calculations can be done. 

 

Cheers 
Owen Smiley Happy



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Super User
Super User

Re: Help in building cashflow table with interest computed on diminishing balance

@CheenuSing 

 

Similar to List.Generate used by @OwenAuger  we can also use List.Accumulate

 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdBJCgMxEEPRqwSv26HkuXOVpu9/jdhksFAtBIK3+9cVDNFGxHjYyywcYa2NPJ7o4T4+fpJjLuZ5vwgjTIogzIqJsAguP39YFRNhU8yEXbEQDsVKeCo2QphqZ9VGK/BWjbTybnWVjFUzgTNBO4E7wYVCTPZXVyqxulSZ1bUqpMm1qqyuVWN1rTqrtpqdSXer+w0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Month Name" = _t, Month = _t, Cashflow = _t]),
ChangedType = Table.TransformColumnTypes(Source,{{"Month Name", type datetime}, {"Month", Int64.Type}, {"Cashflow", type number}}),
PL=Table.AddColumn(ChangedType,"Liability CF",
each
let mymonth=[Month],
myfirstCashflow=Table.SelectRows(ChangedType,each [Month]=0)[Cashflow]{0},
mylist=Table.SelectRows(ChangedType, each [Month]<=mymonth and [Month]>0)[Cashflow] in
List.Accumulate(mylist, myfirstCashflow, (state, current) => state + state*.05/12 + current))
in
PL

CS.png

 

CheenuSing Super Contributor
Super Contributor

Re: Help in building cashflow table with interest computed on diminishing balance

Hi @Zubair_Muhammad , @OwenAuger 

 

Thanks to you both for giving a solution. 

 

It will be for the benefit of  every one in the community , if you can explain what each step does in PowerQuery and in DAX.

 

 

Thanks again

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
CheenuSing Super Contributor
Super Contributor

Re: Help in building cashflow table with interest computed on diminishing balance

Hi  @OwenAuger ,

 

I have the table with   ID ,MonthName, Month,Cashflow . 

Thatis for different IDs I have similar data as posted earlier.

 

What changes should be made to dax when user filter a particular ID through slicer.

 

And also to Power Query.

 

Sorry I forgot to mmention this in my earler post.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Super User
Super User

Re: Help in building cashflow table with interest computed on diminishing balance

Hi @CheenuSing 

 

Here is my effort to explain List.Accumulate

 

List.Accumualte is one of the ways of implementing a loop in Power Query (others being List.Generate, recursive function).

 

This loop is similar to For -Next loop in VBA

 

Formula:
List.Accumulate(list as list, seed as any, accumulator as function)as any 


Example given in https://docs.microsoft.com/en-us/powerquery-m/list-accumulate
List.Accumulate({1, 2, 3, 4, 5}, 0, (state, current) => state + current) equals 15 

Sample illustration with your data (Row 3 calculation)

cs1.png

 

 

CheenuSing Super Contributor
Super Contributor

Re: Help in building cashflow table with interest computed on diminishing balance

Hi @OwenAuger ,

 

I got it working. Thanks for the help.

 

If you can put down in simple sentences what the calculated columns  and power query does in each step it will help everyone understand the logic better.

 

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
OwenAuger Super Contributor
Super Contributor

Re: Help in building cashflow table with interest computed on diminishing balance

Hi again @CheenuSing 

Took me a while to come back to this.

I have added some comments to the code describing the meaning of the important steps.

Pasted below and reattached my PBIX.

 

It sounded like you were fine with handling the calculation for different "project IDs" or something similar? Post back if neeeded Smiley Happy

 

Liability c/f DAX = 
VAR InterestRatePerMonth =
    0.05/12
VAR CurrentMonth =
    Data[Month]
// CashflowToDate is a table of Cashflow by Month, for all months up to and including the current row's month
VAR CashflowToDate =
    CALCULATETABLE ( 
        ADDCOLUMNS (
            SUMMARIZE( Data, Data[Month] ),
            "CashflowSum", CALCULATE ( SUM ( Data[Cashflow] ) )
        ),
        // Filter to all months <= current row's month
        Data[Month] <= CurrentMonth, 
        // Clear other filters present in the current row.
        // Note: If you want to do this calculation "per Project" or similar, 
        // ALL could be changed to ALLEXCEPT to keep filters on specific columns, such as Project ID
        ALL ( Data ) 
                     
    )
// LiabilityCF is the result we want
VAR LiabilityCF =
    // Iterate over the rows of CashflowToDate
    SUMX ( 
        CashflowToDate,
        // For each row, take the cashflow and compound it by the interest rate,
        // from the month of the cashflow up to the month of the current row in the Data table
        [CashflowSum] * ( 1 + InterestRatePerMonth ) ^ ( CurrentMonth - Data[Month] )
    )
RETURN
    LiabilityCF

Interest DAX = 
VAR InterestRatePerMonth =
    0.05/12
VAR CurrentMonth =
    Data[Month]
// Interest is equal to the balance from the previous month multiplied by the interest rate
VAR Interest =
    // Get balance from current row's month minus 1
    CALCULATE (
        SUM ( Data[Liability c/f DAX] ),
        ALL ( Data ),
        Data[Month] = CurrentMonth - 1
    ) * InterestRatePerMonth
RETURN
    Interest

Liability DAX = 
// This is effectively the movement in Liability c/f (the monthly balance)
// which is (-Cashflow)+(-Interest).
// This could also be simply calculated as the change in Liability c/f
IF ( 
    // Only calculate for Month > 0
    Data[Month] > 0,
    - Data[Cashflow] - Data[Interest DAX]
)

Also here's the M code again with comments:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdC9CsIwFIbhW5HMjeQk/UnHgjgI6uBYOtRSXESLWMG7tz1fweQ0kOVJIDlvXatqvGnyKlFm2pt55Yl3fkuFapJaXfoBxzRt7YxhPXdvqA319PxAXai7voOmoR7ah6ZykizUfX+F5qEe2xe0CLUaFvXx3S+0jF4bl9fIxHxfOJqOkzBH43EKZrdqwZyuYjBnqxrMucxh579RIXuAvQwCLmURZmtkEjDJJmArm4CdbAL+T9n8AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [MonthName = _t, Month = _t, Cashflow = _t]),
    ChangeType1 = Table.TransformColumnTypes(Source,{{"MonthName", type date}, {"Month", Int64.Type}, {"Cashflow", type number}}),
    SortedRows = Table.Sort(ChangeType1,{{"Month", Order.Ascending}}),
    Cashflow = SortedRows[Cashflow],
    CashflowCount = List.Count(Cashflow),
    // List.Generate is used to create a list containing the required values for each time index
    // Each element of the list is a record containing fields Index, Interest, Liability, LiabilityCF
    // Each record is calculated by taking the previous record's values plus the cashflow of the current index
    CalcInterest =
        List.Generate(
            // Initialise variables
            // Index (month ) = 0, Interest = null (i.e. zero), LiabilityCF = first cashflow
            ()=>[Index = 0,Interest = null, Liability = null, LiabilityCF = Cashflow{0}],
            
            // This is the loop condition: continue as long as Index < the count of cashflows
            each [Index]<CashflowCount,
            
            // Each time through the loop, add the following record to the list of results.
            // This record contains:
            // Index = Previous Index + 1
            // Interest = Previous Liability c/f * interest rate
            // Liability = - Cashflow{Index} - Interest (above)
            // Liability c/f = Previous Liability c/f  less Liability (above)
            each [
                Index = [Index]+1,
                Interest = [LiabilityCF]*0.05/12,
                Liability = -Cashflow{Index}-Interest,
                LiabilityCF = [LiabilityCF]-Liability
            ]
        ),
    // Convert CalcInterst to a table & expand the records into columns
    ConvertedToTable = Table.FromList(CalcInterest, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    ExpandedColumns = Table.ExpandRecordColumn(ConvertedToTable, "Column1", {"Index", "Interest", "Liability", "LiabilityCF"}, {"Index", "Interest", "Liability", "Liability c/f"}),
    // Join CalcInterest to the original table
    JoinTables = Table.NestedJoin(SortedRows,{"Month"},ExpandedColumns,{"Index"},"Interest"),
    #"Expanded Interest" = Table.ExpandTableColumn(JoinTables, "Interest", {"Interest", "Liability", "Liability c/f"}, {"Interest", "Liability", "Liability c/f"}),
    ChangeType2 = Table.TransformColumnTypes(#"Expanded Interest",{{"Interest", type number}, {"Liability", type number}, {"Liability c/f", type number}})
in
    ChangeType2


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




CheenuSing Super Contributor
Super Contributor

Re: Help in building cashflow table with interest computed on diminishing balance

Thanks Owen

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 22 members 926 guests
Please welcome our newest community members: