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

## Help in building cashflow table with interest computed on diminishing balance

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
Super Contributor

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

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

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

Proud to be a Datanaut!

Highlighted
Super User

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

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

`letSource = 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] inList.Accumulate(mylist, myfirstCashflow, (state, current) => state + state*.05/12 + current))inPL`

8 REPLIES 8
Super Contributor

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

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

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

Proud to be a Datanaut!

Highlighted
Super User

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

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

`letSource = 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] inList.Accumulate(mylist, myfirstCashflow, (state, current) => state + state*.05/12 + current))inPL`

Super Contributor

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

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!
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

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

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)

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!
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

```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!

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

Engage and empower students with Power BI!

#### Watch Sessions On Demand!

Continue your learning in our online communities.

#### Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Top Kudoed Authors
Users Online
Currently online: 254 members 2,640 guests
Recent signins:
Please welcome our newest community members: