Home » Quarterly Hike Compounding Table

Quarterly Hike Compounding Table

Transpose the problem table into result table. Total Periods – Number of quarters starting with quarter given in Quarter column Every year i.e. after 4 quarters, year on year hike will be applicable as given in %Hike Yearly.

📌 Challenge Details and Links
ExcelBI Power Query Challenge Number: 183
Challenge Difficulty: ⭐️⭐️⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Quarterly Hike Compounding Table with Power Query

Power Query solution 1 for Quarterly Hike Compounding Table, proposed by Bo Rydobon 🇹🇭: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Ans = Table.Combine(
    List.Transform(
      Table.ToRows(Source), 
      (i) =>
        let
          q = Number.From(Text.End(i{2}, 1)) - 1
        in
          Table.FromRows(
            List.Transform(
              {q .. q + i{3} - 1}, 
              (Q) => {
                i{0}, 
                i{1} + Number.IntegerDivide(Q, 4), 
                "Q" & Text.From(Number.Mod(Q, 4) + 1), 
                Number.Round(i{4} * Number.Power(1 + i{5} / 100, Number.IntegerDivide(Q - q, 4)))
              }
            ), 
            List.FirstN(Table.ColumnNames(Source), 3) & {"Rental"}
          )
    )
  )
in
  Ans
Power Query solution 2 for Quarterly Hike Compounding Table, proposed by Zoran Milokanović: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Input"]}[Content], 
  Q = {"Q1", "Q2", "Q3", "Q4"}, 
  H = Table.ColumnNames(Source), 
  S = Table.FromRows(
    List.TransformMany(
      Table.ToRows(Source), 
      each {0 .. _{3} - 1}, 
      (i, _) =>
        let
          c = Number.IntegerDivide(_, 4), 
          q = Q{Number.Mod(List.PositionOf(Q, i{2}) + _, 4)}
        in
          {i{0}, i{1} + c + Number.From(q < i{2}), q, Number.Power(1 + (i{5} / 100), c) * i{4}}
    ), 
    {H{0}, H{1}, H{2}, H{4}}
  )
in
  S
Power Query solution 3 for Quarterly Hike Compounding Table, proposed by Kris Jaganah: 
let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 Rental = Table.AddColumn(Source, "Ans", each let 
a = {1..[Total Periods]},
b =[Rental],
c =[#"% Hike Yearly"],
d = Date.EndOfQuarter( hashtag#date([Year],Number.From(Text.AfterDelimiter([Quarter],"Q"))*3,1)),

e = List.Transform(a, each let 
 p = Number.Round( Number.Power( (1+c/100),Number.IntegerDivide(_-1,4)) * b ,0), 
 q= Date.AddQuarters(d,_ -1 ),
 r = Text.From(Date.Year(q)), 
 s = "Q"& Text.From( Date.QuarterOfYear(q)) ,
 t = r&"-"&s&"-"& Text.From( p) in t  ),

f = Table.FromList(e,Splitter.SplitTextByDelimiter("-"), {"Year", "Quarter", "Rental"})

in f),
 Select = Table.SelectColumns(Rental,{"Vendor", "Ans"}),
 Xpand = Table.ExpandTableColumn(Select, "Ans", {"Year", "Quarter", "Rental"}),
 Changetype = Table.TransformColumnTypes(Xpand,{{"Year", Int64.Type}, {"Rental", Int64.Type}})
in
 Changetype
Power Query solution 4 for Quarterly Hike Compounding Table, proposed by Aditya Kumar Darak 🇮🇳: 
let
  Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content], 
  Qtrs = List.Transform({"1" .. "4", "1" .. "4"}, each "Q" & _), 
  Record = Table.AddColumn(
    Source, 
    "R", 
    each [
      P = List.PositionOf(Qtrs, [Quarter]), 
      G = List.Generate(
        () => [a = 0, Q = [Quarter], Y = [Year], R = [Rental]], 
        (f) => f[a] < [Total Periods], 
        (f) => [
          a = f[a] + 1, 
          M = Number.Mod(a, 4), 
          Q = Qtrs{M + P}, 
          Y = f[Y] + Number.From(Q = "Q1"), 
          R = f[R] * (1 + [#"% Hike Yearly"] * Number.From(M = 0) / 100)
        ], 
        (f) => [Vendor = [Vendor], Year = f[Y], Quarter = f[Q], Rental = Number.Round(f[R])]
      )
    ][G]
  ), 
  Return = Table.FromRecords(List.Combine(Record[R]))
in
  Return
Power Query solution 5 for Quarterly Hike Compounding Table, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Group = Table.Group(
    Source, 
    {"Vendor"}, 
    {
      {
        "A", 
        (x) =>
          let
            b = List.Repeat({1 .. 4}, Number.RoundUp(x[Total Periods]{0} / 4) + 1), 
            c = List.Transform(
              List.FirstN(
                List.Skip(b, Number.From(Text.End(x[Quarter]{0}, 1)) - 1), 
                x[Total Periods]{0}
              ), 
              each "Q" & Text.From(_)
            ), 
            d = List.Skip(
              List.Generate(
                () => [y = 0, z = x[Year]{0}], 
                each [y] <= List.Count(b), 
                each [
                  y = [y] + 1, 
                  z = if try b{[y]} = b{[y] - 1} + 1 otherwise true then [z] else [z] + 1
                ], 
                each [z]
              )
            ), 
            e = List.FirstN(
              List.Skip(d, Number.From(Text.End(x[Quarter]{0}, 1)) - 1), 
              x[Total Periods]{0}
            ), 
            f = List.Transform({0 .. x[Total Periods]{0} - 1}, each Number.IntegerDivide(_, 4) + 1), 
            g = List.FirstN(
              List.Skip(
                List.Generate(
                  () => [y = 0, z = x[Rental]{0}], 
                  each [y] <= List.Count(b), 
                  each [
                    y = [y] + 1, 
                    z = 
                      if try f{[y]} = f{[y] - 1} otherwise true then
                        [z]
                      else
                        [z] * (1 + x[#"% Hike Yearly"]{0} / 100)
                  ], 
                  each [z]
                )
              ), 
              x[Total Periods]{0}
            ), 
            h = Table.FromColumns({e, c, g}, {"Year", "Quarter", "Rental"})
          in
            h
      }
    }
  ), 
  Sol = Table.ExpandTableColumn(Group, "A", Table.ColumnNames(Group[A]{0}))
in
  Sol
Power Query solution 6 for Quarterly Hike Compounding Table, proposed by Hussein SATOUR: 
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
AddCust = Table.AddColumn(Source, "Custom", each let
 a = Number.From(Text.End([Quarter],1)) * 3,
 b = hashtag#date([Year], a, 1),
 c = List.Transform({0 .. [Total Periods]-1}, (x)=> Date.AddQuarters(b, x)),
 d = List.Transform(c, (x)=> Number.RoundDown(Duration.Days(x - b)/365))
 in List.Zip({c,d})),
ExpandCust = Table.ExpandListColumn(AddCust, "Custom"),
ExtractVals = Table.TransformColumns(ExpandCust, {"Custom", each Text.Combine(List.Transform(_, Text.From), " ")}),
Split = Table.SplitColumn(ExtractVals, "Custom", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Quarters", "Custom.2"}),
ChangType = Table.TransformColumnTypes(Split,{{"Quarters", type date}, {"Custom.2", Int64.Type}}),
AddRentals = Table.AddColumn(ChangType, "Rentals", each Number.Power(1 + ([#"% Hike Yearly"]/100), [Custom.2]) * [Rental]),
CalculatQuarter = Table.TransformColumns(AddRentals,{{"Quarters", each "Q" & Text.From(Date.QuarterOfYear(_))}}),
RemoveOthers = Table.SelectColumns(CalculatQuarter,{"Vendor", "Year", "Quarters", "Rentals"})
in RemoveOthers
Power Query solution 7 for Quarterly Hike Compounding Table, proposed by Alexis Olson: 
let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 AddCol = Table.AddColumn(Source, "Table", each
 [
 Periods = {1..[Total Periods]},
 YearQtr = Date.EndOfMonth(hashtag#date([Year], 3 * Number.From(Text.End([Quarter], 1)), 1)),
 Quarters = List.Transform(Periods, (i) => Date.AddQuarters(YearQtr, i - 1)),
 Rental = List.Transform(Periods, (i) => [Rental] * Number.Power(1 + [#"% Hike Yearly"] / 100, Number.IntegerDivide(i - 1, 4))),
 Table = Table.FromColumns({Quarters, Rental}, {"QtrEnd", "Rental"})
 ][Table]
 ),
 SelectCols = Table.SelectColumns(AddCol,{"Vendor", "Table"}),
 ExpandTable = Table.ExpandTableColumn(SelectCols, "Table", {"QtrEnd", "Rental"}),
 YearQtr = Table.ReplaceValue(ExpandTable, each [QtrEnd], each [Year = Date.Year([QtrEnd]), Quarter = "Q" & Text.From(Date.QuarterOfYear([QtrEnd]))], Replacer.ReplaceValue, {"QtrEnd"}),
 ExpandRecord = Table.ExpandRecordColumn(YearQtr, "QtrEnd", {"Year", "Quarter"}, {"Year", "Quarter"})
in
 ExpandRecord
Power Query solution 8 for Quarterly Hike Compounding Table, proposed by Ramiro Ayala Chávez: 
let
  S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  L = List.Transform, 
  A = Table.AddColumn, 
  T = Table.TransformColumns, 
  Y = Table.TransformColumnTypes, 
  R = Number.Round, 
  TS = Text.StartsWith, 
  N = Table.ColumnNames, 
  a = Table.SplitColumn(S, "Quarter", Splitter.SplitTextByRepeatedLengths(1), {"Q1", "Q2"}), 
  b = Y(a, {"Q2", Int64.Type}), 
  c = A(b, "L", each [Q2] + [Total Periods] - 1), 
  d = A(c, "Q", each {[Q2] .. [L]}), 
  e = Table.ExpandListColumn(d, "Q")[[Vendor], [Year], [Rental], [#"% Hike Yearly"], [Q]], 
  f = A(e, "Y1", each if [Q] > 4 then [Year] + 1 else [Year]), 
  g = A(f, "Y2", each if [Q] > 8 then [Y1] + 1 else [Y1])[
    [Vendor], 
    [Rental], 
    [#"% Hike Yearly"], 
    [Q], 
    [Y2]
  ], 
  h = L(Table.Group(g, {"Vendor"}, {"G", each _})[G], each Table.AddIndexColumn(_, "I")), 
  i = L(
    h, 
    each A(
      _, 
      "R1", 
      each if [I] > 3 then R([#"% Hike Yearly"] / 100 * [Rental] + [Rental]) else R([Rental])
    )
  ), 
  j = L(
    i, 
    each A(_, "R2", each if [I] > 7 then R([#"% Hike Yearly"] / 100 * [R1] + [R1]) else R([R1]))
  ), 
  k = Table.Combine(j)[[Vendor], [Y2], [R2], [Q]], 
  l = A(k, "Q1", each if [Q] > 4 then [Q] - 4 else [Q]), 
  m = A(l, "Q2", each if [Q1] > 4 then [Q1] - 4 else [Q1]), 
  n = T(Y(m, {{"Q2", type text}}), {"Q2", each "Q" & _})[[Vendor], [Y2], [Q2], [R2]], 
  o = List.Select(N(S), each not TS(_, "T") and not TS(_, "%")), 
  Sol = Table.RenameColumns(n, List.Zip({N(n), o}))
in
  Sol
Power Query solution 9 for Quarterly Hike Compounding Table, proposed by Eric Laforce: 
let
 Source = Excel.CurrentWorkbook(){[Name="tData183"]}[Content],
 TransformRows = List.Transform(Table.ToRecords(Source), (r)=> let
 _DStart = Date.StartOfQuarter(hashtag#date(r[Year],3*Number.From(Text.End(r[Quarter],1)),1)),
 _IncY = 1 + (r[#"% Hike Yearly"] / 100)
 in List.Transform({0..r[Total Periods]-1}, each let
 _Date = Date.AddQuarters(_DStart, _),
 _R = Number.Round(r[Rental] * Number.Power(_IncY, Number.IntegerDivide(_,4)))
 in {r[Vendor], Date.Year(_Date), "Q" & Text.From(Date.QuarterOfYear(_Date)), _R} ) ),
 Result = Table.FromRows(List.Combine(TransformRows), {"Vendor", "Year", "Quater", "Rental"}) 
in
 Result
Power Query solution 10 for Quarterly Hike Compounding Table, proposed by Peter Tholstrup: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  f = (t) =>
    List.Generate(
      () => [i = 1, Y = t[Year], Q = t[Quarter], R = t[Rental]], 
      each [i] <= t[Total Periods], 
      each [
        c      = Number.From(Text.End([Q], 1)), 
        y      = if c = 4 then [Y] + 1 else [Y], 
        q      = "Q" & Text.From(if c = 4 then 1 else c + 1), 
        m      = Number.Power(1 + t[#"% Hike Yearly"] / 100, Number.IntegerDivide([i], 4)), 
        result = [i = [i] + 1, Y = y, Q = q, R = Number.Round(t[Rental] * m, 0)]
      ][result], 
      each [Vendor = t[Vendor], Year = [Y], Quarter = [Q], Rental = [R]]
    ), 
  result = Table.FromRecords(List.Combine(Table.TransformRows(Source, f)))
in
  result

Solving the challenge of Quarterly Hike Compounding Table with Excel

Excel solution 1 for Quarterly Hike Compounding Table, proposed by Bo Rydobon 🇹🇭: 
=REDUCE(H1:K1,A2:A5,LAMBDA(a,v,LET(i,LAMBDA(x,INDEX(v:F5,1,x)),
q,SEQUENCE(i(4),,RIGHT(i(3))-1),IFNA(VSTACK(a,HSTACK(v,i(2)+INT(q/4),"Q"&MOD(q,4)+1,ROUND(i(5)*(1+i(6)%)^(INT((q-@q)/4)),))),v))))
Excel solution 2 for Quarterly Hike Compounding Table, proposed by محمد حلمي: 
=REDUCE(H1:K1,A2:A5,LAMBDA(a,v,VSTACK(a,LET(
j,LAMBDA(x,OFFSET(v,,x)),u,SEQUENCE(j(3))-1,
s,u+RIGHT(j(2))-1,HSTACK(IF(u+1,v),INT(s/4)+j(1),
"Q"&MOD(s,4)+1,ROUND(j(4)*(1+j(5)%)^INT(u/4),))))))
Excel solution 3 for Quarterly Hike Compounding Table, proposed by Julian Poeltl: 
=LET(T,A2:F5,V,TAKE(T,,1),Y,CHOOSECOLS(T,2),Q,CHOOSECOLS(T,3),TP,CHOOSECOLS(T,4),R,CHOOSECOLS(T,5),H,TAKE(T,,-1),RTP,SCAN(,TP,LAMBDA(A,B,A+B)),BT,RTP-TP+1,AR,MAKEARRAY(MAX(RTP),3,LAMBDA(A,B,IFS(B=1,XLOOKUP(A,RTP,V,,1),B=2,XLOOKUP(A,BT,Y,0),B=3,XLOOKUP(A,BT,Q,0)))),QQ,SCAN(,CHOOSECOLS(AR,3),LAMBDA(A,B,IF(B<>0,B,SWITCH(A,"Q1","Q2","Q2","Q3","Q3","Q4","Q4","Q1")))),YY,SCAN(,CHOOSECOLS(AR,2),LAMBDA(A,B,IF(B=0,A,B))),YC,IF(TAKE(AR,,1)=VSTACK(TAKE(V,1),DROP(TAKE(AR,,1),-1)),IF(QQ="Q1",1,0),0),YYY,YY+LEFT(SCAN(,YC&TAKE(AR,,1),LAMBDA(A,B,IF(RIGHT(B)=RIGHT(A),(LEFT(B)+LEFT(A))&RIGHT(B),B)))),RH,MAKEARRAY(MAX(RTP),1,LAMBDA(A,B,XLOOKUP(A,RTP,H,,1))),RT,ROUNDDOWN((SCAN(,--(DROP(VSTACK(TAKE(V,1),TAKE(AR,,1)),-1)=TAKE(AR,,1)),LAMBDA(A,B,IF(B=1,A+1,1)))-1)/4,0),Rent,XLOOKUP(SEQUENCE(MAX(RTP)),RTP,R,,1)*(1+XLOOKUP(SEQUENCE(MAX(RTP)),RTP,H,,1)/100)^RT,VSTACK(HSTACK("Vendor","Year","Quarter","Rental"),HSTACK(TAKE(AR,,1),YYY,QQ,Rent)))
Excel solution 4 for Quarterly Hike Compounding Table, proposed by Sunny Baggu: 
=LET(
 z, A2:F5,
 REDUCE(
 HSTACK(A1:C1, E1),
 SEQUENCE(ROWS(z)),
 LAMBDA(x, y,
 VSTACK(
 x,
 LET(
 _s, SEQUENCE(INDEX(z, y, 4)),
 _n, --RIGHT(INDEX(z, y, 3), 1),
 _s1, _n + _s - 1,
 _y1, ROUNDUP(_s1 / 4, 0) - 1,
 _v, IF(_s, INDEX(z, y, 1)),
 _y2, _y1 + INDEX(z, y, 2),
 _q, "Q" & (_s1 - 4 * _y1),
 _r, INDEX(z, y, 5) *
 SCAN(
 1,
 DROP(VSTACK(0, N(MOD(_s, 4) = 0)), -1),
 LAMBDA(a, v, IF(v = 1, (INDEX(z, y, 6) / 100 + 1) * a, a))
 ),
 HSTACK(_v, _y2, _q, _r)
 )
 )
 )
 )
)
Excel solution 5 for Quarterly Hike Compounding Table, proposed by LEONARD OCHEA 🇷🇴: 
=REDUCE(H1:K1,A2:A5,LAMBDA(a,v,LET(d,LAMBDA(x,OFFSET(v,,x)),s,SEQUENCE(d(3)),n,s+RIGHT(d(2))-2,VSTACK(a,HSTACK(IF(s,v),d(1)+INT(n/4),"Q"&MOD(n,4)+1,d(4)*(1+d(5)%)^INT((s-1)/4))))))
Excel solution 6 for Quarterly Hike Compounding Table, proposed by Abdallah Ally: 
=REDUCE({"Vendor","Year","Quarter","Rental"},D2:D5,LAMBDA(x,y, VSTACK(x,REDUCE(HSTACK(OFFSET(y,,-3,,3),OFFSET(y,,1)),SEQUENCE(y-1),LAMBDA(u,v,LET(r,--RIGHT(OFFSET(y,,-1)),s,r+v,VSTACK(u, HSTACK(OFFSET(y,,-3),OFFSET(y,,-2)+IF(MOD(s,4)=0,INT(s/4)-1,INT(s/4)),IF(MOD(s,4)=0,"Q4","Q"&MOD(s,4)),ROUND(IFS(v=1,OFFSET(y,,1),MOD(v,4)=0,(1+OFFSET(y,,2)/100)*TAKE(u,-1,-1),1,TAKE(u,-1,-1)),0)))))))))
Excel solution 7 for Quarterly Hike Compounding Table, proposed by Burhan Cesur: 
=REDUCE(H1:K1,A2:A5,LAMBDA(s,v,
VSTACK(s,LET(f,LAMBDA(x,OFFSET(v,,x)),z,--RIGHT(f(2),1),y,INT(SEQUENCE(f(3),,z-1)/4),e,IF(y+f(1),v),t,1+MOD(SEQUENCE(f(3),,z-1),4),q,SCAN(f(4),VSTACK(0,DROP("Q"&t,1)),LAMBDA(acc,val,IF(val=f(2),acc*(1+f(5)%),acc))),
HSTACK(e,f(1)+y,"Q"&t,q)))))

Solving the challenge of Quarterly Hike Compounding Table with Python in Excel

Python in Exce&l solution 1 for Quarterly Hike Compounding Table, proposed by Abdallah Ally: 
import pandas as pd
file_path = 'PQ_Challenge_183.xlsx'
df = pd.read_excel(file_path, usecols='A:F', nrows=4)
df1 = pd.read_excel(file_path, usecols='H:K') # Expected
df1.iloc[:, 3] = df1.iloc[:, 3].map(lambda x: round(x))
# Perform data wrangling
items = []
for i in df.index:
 v, h, r = df.iloc[i, [0, 5, 4]]
 for j in range(df.iat[i, 3]):
 y, q = df.iloc[i, [1, 2]]
 n = j + int(q[1])
 if n <= 4:
 items.append([v, y, 'Q' + str(n), r])
 else:
 y = df.iat[i, 1] + (n // 4 - 1 if n % 4 == 0 else n // 4)
 q = 'Q4' if n % 4 == 0 else 'Q' + str(n % 4)
 r = r * (1 + h / 100) if j % 4 == 0 else r
 items.append([v, y, q, r])
df = pd.DataFrame(items, columns=df.columns[[0, 1, 2, 4]]) # Computed
df['Rental'] = df['Rental'].map(lambda x: round(x))
print(f'nMy Results vs Expected Results! Equal? {(df.values == df1.values).all()}')
# Display sample results 
df.sample(10)

Solving the challenge of Quarterly Hike Compounding Table with R

R solution 1 for Quarterly Hike Compounding Table, proposed by Konrad Gryczan, PhD: 
library(tidyverse)
library(readxl)
input = read_excel("Power Query/PQ_Challenge_183.xlsx", range = "A1:F5")
test = read_excel("Power Query/PQ_Challenge_183.xlsx", range = "H1:K24") %>%
 mutate(Rental = as.integer(Rental))
result = input %>%
 unite("OYQ", Year, Quarter, sep = " ") %>%
 mutate(OYQ = yq(OYQ)) %>%
 rowwise() %>%
 mutate(quarters = list(seq.Date(from = as.Date(OYQ), by = "quarter", length.out = `Total Periods`))) %>%
 ungroup() %>%
 unnest(quarters) %>%
 mutate(Year = year(quarters), 
 Quarter = paste0("Q",quarter(quarters)),
 rn = row_number(),
 roll_year = (rn - 1) %/% 4 ,
 .by = Vendor) %>%
 mutate(Rental = round(Rental * (1 + `% Hike Yearly`/100)^roll_year) %>% as.integer()) %>%
 select(Vendor, Year, Quarter, Rental) 
identical(result, test)
# [1] TRUE

&&

Leave a Reply