Implementing Excel table in Power Bi where a column value depends on another column earlier value

Using Power Query M Code, it is possible to create the table you show from the source data you supply:

• In this code, the number of years is hard coded
• The growth rate is calculated from the source data
• List.Generate is used to generate the required columns
• I assumed the $2700 contribution in year 38 was a typo

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjZQ0lEyMjUAUaYQNpCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Age = _t, Contribution = _t, #"Investment Growth" = _t, #"Fund Value" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Age", Int64.Type}, 
        {"Contribution", Currency.Type}, {"Investment Growth", Int64.Type}, {"Fund Value", Currency.Type}}),
 
    //calculate new columns
    yrs = 11,
    rate= #"Changed Type"[Investment Growth]{0}/#"Changed Type"[Contribution]{0},
    
    newTblCols = List.Generate(
        ()=>[yr=#"Changed Type"[Age]{0},
             contr=#"Changed Type"[Contribution]{0},
             gr=#"Changed Type"[Investment Growth]{0},
             fv=#"Changed Type"[Fund Value]{0}, 
             idx=0],
        each [idx] < yrs,
        each [yr=[yr]+1,
              contr=[contr],
              gr = ([fv] + [contr]) * rate,
              fv = [fv] + [contr] + ([fv] + [contr]) * rate,
              idx = [idx]+1],
        each {[yr],[contr],[gr],[fv]}
    ),

//create table from columns and prepend with a column for the Row labels
    #"New Table" = Table.FromColumns({{"Year","Contribution","Growth","Fund Value"}} & newTblCols),

//Promote first row to the column Headers
    #"Promoted Headers" = Table.PromoteHeaders(#"New Table", [PromoteAllScalars=true]),

//Set the data types for new table
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Year", type text}} & 
        List.Transform(List.RemoveFirstN(Table.ColumnNames(#"Promoted Headers"),1), each {_, Currency.Type}))
in
    #"Changed Type1"

But, as you show in your example, this is three rows of results for a single entry row.

How would you want to display the results when you have multiple entry rows?

Hi,

Share the source data (not the expected result which you have pasted in the original message) in a format that can be pasted in an MS Excel file.