Skip to content

Instantly share code, notes, and snippets.

@ncalm
Last active January 19, 2024 05:33
Show Gist options
  • Star 3 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
  • Save ncalm/43d206ca615af98114efaa1d1d6346f4 to your computer and use it in GitHub Desktop.
Save ncalm/43d206ca615af98114efaa1d1d6346f4 to your computer and use it in GitHub Desktop.
This Excel Lambda namespace allows for parameterized creation of a depreciation schedule
namespace_author = "Owen Price";
namespace_author_url = "https://www.flexyourdata.com";
namespace_description="This namespace provides functionality for building depreciation schedules";
schedule_demo = depn.schedule(100000,10000,6,2015,depn.db,true,depn.byyear,true);
schedule_header = {"Period labels","Periods","Depreciation","Accumulated Depreciation","Depreciated Asset Value"};
/*
Author: Owen Price
https://www.flexyourdata.com
2022-08-15
Updated with bymonth and byyear functions on 2022-08-17
For explanation:
https://www.flexyourdata.com/blog/excel-lambda-depn-schedule-create-a-depreciation-schedule-in-excel-with-one-function/
Please be sure to import these functions into a namespace called "depn".
NOTE: waterfall function has a dependency on the META namespace:
https://gist.github.com/ncalm/643ebc018946542f8642c074043e510a
*/
sln = LAMBDA(cost,salvage,life,periods,
IF(periods=0,0,SLN(cost,salvage,life))
);
db = LAMBDA(cost,salvage,life,periods,
DB(cost,salvage,life,periods)
);
ddb = LAMBDA(cost,salvage,life,periods,
DDB(cost,salvage,life,periods)
);
syd = LAMBDA(cost,salvage,life,periods,
SYD(cost,salvage,life,periods)
);
bymonth = LAMBDA(periods,start,[return_metadata],
LET(
_return_metadata,IF(ISOMITTED(return_metadata),FALSE,return_metadata),
_metadata,META.DATA("depn.bymonth",
"Return a list of months as period labels to depn.schedule",
depn.namespace_author),
IF(_return_metadata,_metadata,DATE(YEAR(start),MONTH(start)+periods,DAY(start)))
)
);
byyear = LAMBDA(periods,start,[return_metadata],
LET(
_return_metadata,IF(ISOMITTED(return_metadata),FALSE,return_metadata),
_metadata,META.DATA("depn.byyear",
"Return a list of years as period labels to depn.schedule",
depn.namespace_author),
IF(_return_metadata,_metadata,start+periods)
)
);
schedule = LAMBDA(cost,salvage,periods,start,depn_fn,[vertical],[period_fn],[return_header],
LET(
_v,IF(ISOMITTED(vertical),FALSE,vertical),
_rh,IF(ISOMITTED(return_header),TRUE,return_header),
_pfn,IF(ISOMITTED(period_fn),depn.byyear,period_fn),
_periods,SEQUENCE(periods+1,,0),
_periodlabels,_pfn(_periods,start),
_depr,IFERROR(depn_fn(cost,salvage,periods,_periods),0),
_acc,SCAN(0,_depr,LAMBDA(a,b,a+b)),
_dav,cost-_acc,
_header,depn.schedule_header,
_array,CHOOSE({1,2,3,4,5},_periodlabels,_periods,_depr,_acc,_dav),
_array_with_header,MAKEARRAY(
periods+2,5,
LAMBDA(r,c,
IF(r=1,INDEX(_header,1,c),
INDEX(_array,r-1,c))
)
),
_output,IF(_rh,_array_with_header,_array),
IF(_v,_output,TRANSPOSE(_output))
)
);
schedule_stack = LAMBDA(asset_purchases_table,depn_fn,[period_fn],
LET(
t, asset_purchases_table ,
PeriodFn, IF( ISOMITTED( period_fn ), depn.byyear , period_fn ),
RowIndex, SEQUENCE( ROWS( t ) ),
//shorthand for getting a cell's value from _t
GetVal, LAMBDA(row, LAMBDA( col , INDEX( t , row , col) ) ),
//returns the schedule for row b of the asset table
Schedule, LAMBDA(b,
LET(
//get a lambda containing the row for this asset
args,GetVal(b),
//create the schedule
depn.schedule(
args(2) , //cost
args(3) , //salvage
args(4) , //life
args(5) , //start
depn_fn , //depreciation calculation function
TRUE , //vertical
PeriodFn , //period function
FALSE //return_header
)
)
),
//get the schedule for row 1 of the asset table
Schedule1, Schedule(1) ,
//Creates a 1-column array containing the asset name
AssetNameCol, LAMBDA( name , sch , EXPAND( name , ROWS(sch) , 1 , name )),
//prepare the column for the first asset
NameCol1 , AssetNameCol( GetVal(1)(1) , Schedule1 ),
//create the initial value for the REDUCE operation
Init , HSTACK( NameCol1 , Schedule1 ),
Schedules,
REDUCE(
Init , //initial array - the schedule for row 1
DROP( RowIndex , 1 ), //scan the rest of the rows
LAMBDA( a , b ,
LET(
//get the schedule for the current row/asset
Schedule_b, Schedule(b),
//make a column containing the asset name with same number of rows as schedule
NameCol_b, AssetNameCol( GetVal(b)(1) , Schedule_b ) ,
VSTACK(
a, //the previous schedules
HSTACK( NameCol_b , Schedule_b ) //the current schedule
)
)
)
),
Schedules
)
);
waterfall = LAMBDA(
asset_purchases_table,
depn_fn,
start,
end,
[period_fn],
[vertical],
LET(
omitted,ISOMITTED(asset_purchases_table)+ISOMITTED(depn_fn)+ISOMITTED(start)+ISOMITTED(end),
IF(omitted,"ERROR: asset_purchases_table, depn_fn, start or end is missing",
LET(
t, asset_purchases_table ,
PeriodFn, IF( ISOMITTED( period_fn ), depn.byyear , period_fn ) ,
ReturnVertical, IF( ISOMITTED( vertical ), FALSE , vertical ) ,
//third param returns metadata about the function when TRUE (includes function name)
PeriodFnMD, PeriodFn(1,1,TRUE) ,
//total count of periods to include is the difference between start and end
//according to the period function given
PeriodCount,
SWITCH(
INDEX(PeriodFnMD,1,2), //check the function name
"depn.byyear", end-start+1,
"depn.bymonth", DATEDIF(start,end,"m")+1,
-1
),
PeriodLabels, PeriodFn( SEQUENCE(PeriodCount,,0) , start ),
/*Returns a vertical table of stacked schedules for each asset
listed in the control table, with the asset name in the first
column.
*/
ScheduleStack, depn.schedule_stack( t , depn_fn , PeriodFn ),
/*
For each asset in the table, create an array _periodcount rows tall
and hstack them together, so we end with ROWS(_t) columns
*/
Assets, CHOOSECOLS( t , 1 ) ,
GetAssetDepn,
LAMBDA(x,
LET(
FilteredDepn, FILTER( DROP( ScheduleStack , 0 , 1 ) , CHOOSECOLS( ScheduleStack , 1 ) = x ) ,
XLOOKUP( PeriodLabels , CHOOSECOLS( FilteredDepn , 1 ) , CHOOSECOLS( FilteredDepn , 3 ) , 0 )
)
) ,
Schedules,
REDUCE(
GetAssetDepn( TAKE( Assets , 1 ) ),
DROP( Assets , 1 ),
LAMBDA(a,b,
HSTACK( a , GetAssetDepn(b) )
)
),
Totals, BYROW( Schedules , LAMBDA(r, SUM( r ) ) ),
Result,
VSTACK(
HSTACK( "" , TRANSPOSE( Assets ) , "Total" ),
HSTACK( PeriodLabels , Schedules , Totals )
),
IF( ReturnVertical , Result , TRANSPOSE( Result ) )
)
)
)
);
@luigi-fisys
Copy link

luigi-fisys commented Jan 3, 2024

Please could your code adapt to the following changes?
Captura2

taking into account that there could be more than one asset purchase with overlapping useful lives in a row.

The result I get is this.
Captura3
What is underlined in the image is wrong, since it should be 6000 since I have another asset in Machine that has already started to depreciate.

I adapted this code, but I can't get it to add monthly depreciations that have the same time period for the same asset.
`schedule = LAMBDA(cost,periods,start,
LET(
_periods,SEQUENCE( periods+1 ,1,0),
_depr,IFERROR(IF(_periods=0,0,SLN(cost,0,periods)),0),
_acc,SCAN(0,_depr,LAMBDA(a,b,a+b)),
_periodslabels,

        EOMONTH(start,_periods) 
        
    ,
    _dav,cost-_acc,
    _array,CHOOSE({1,2,3,4,5},_periodslabels,_periods,_depr,_acc,_dav),
    _output,IF(_array,_array),
    _output
)

);

array_create = LAMBDA(Rows,Columns,Defaults,
LET(D,IF(COLUMNS(Defaults)>1,
INDEX(Defaults,1,0),
LEFT(INDEX(Defaults,1,1),
SEQUENCE(1,Columns,
LEN(INDEX(Defaults,1,1)),0))),
X,SEQUENCE(1,Columns),
Y,SEQUENCE(Rows,1,1,0),
IFERROR(INDEX(D,1,X*Y),"")));

schedule_stack = LAMBDA(t,tv,tt, tc,

LET(
    f_asset, ROWS(t) ,
    f_vida, ROWS(tv) ,
    f_time, ROWS(tt) ,
    f_concepto, ROWS(tc),
    c_asset, COLUMNS(t) ,
    c_vida, COLUMNS(tv) ,
    c_time, COLUMNS(tt) ,
    indice_f_asset, SEQUENCE(f_asset,1,1),
    indice_f_vida, SEQUENCE(f_vida,1,1),
    indice_c_asset, SEQUENCE(1,c_asset,1),
    indice_c_vida, SEQUENCE(1,c_vida,1),
    tc_c, array_create(f_asset,f_concepto,transpose(tc)),

    info_table, LAMBDA(b,
        //VSTACK(
            HSTACK(INDEX(tc_c,,b) , INDEX(t, indice_f_asset, b), INDEX(tv, indice_f_vida,b ), INDEX(tt, , 1))
            //HSTACK(INDEX(t, , b), INDEX(tv, , b), INDEX(tt, , 1))
            
        //)
        
    ),
    //info_table(0),
    //GetVal, LAMBDA(row, LAMBDA( col , INDEX( info_table({1,2}) , row , col) ) ),
    resultado, VSTACK(
        info_table(1),info_table(2)
        
    ),
    GetVal, LAMBDA(row, LAMBDA( col , INDEX( resultado , row , col) ) ),
    Schedule, LAMBDA(b,
            LET(
                
                //get a lambda containing the row for this asset
                args,GetVal(b), 

                //create the schedule
                depre_n.schedule(
                    args(2) , //cost
                    args(3) , //life
                    args(4)  //start
                )
            )

    ),
    Schedule1, Schedule(1) ,

    //Creates a 1-column array containing the asset name
    AssetNameCol, LAMBDA( name , sch , EXPAND( name , ROWS(sch) , 1 , name )),
    RowIndex, SEQUENCE( ROWS( Schedule(0) ) ),
    //prepare the column for the first asset
    NameCol1 , AssetNameCol( GetVal(1)(1) , Schedule1 ),

    //create the initial value for the REDUCE operation
    Init , HSTACK( NameCol1 , Schedule1 ),

    Schedules,
        REDUCE(
            Init , //initial array - the schedule for row 1
            DROP( RowIndex , 1 ), //scan the rest of the rows
            LAMBDA( a , b ,
                LET(

                    //get the schedule for the current row/asset
                    Schedule_b, Schedule(b), 

                    //make a column containing the asset name with same number of rows as schedule
                    NameCol_b, AssetNameCol( GetVal(b)(1) , Schedule_b ) ,

                    VSTACK(
                        a, //the previous schedules
                        HSTACK( NameCol_b , Schedule_b ) //the current schedule
                    )
                )
            )
        ),

    Schedules
)

);

waterfall = LAMBDA(
asset_purchases_table,
vida_table,
time_table,
concepto_table,
//depn_fn,
start,
end,
//[period_fn],
[vertical],

LET(
    omitted,ISOMITTED(asset_purchases_table)+ISOMITTED(start)+ISOMITTED(end),
    IF(omitted,"ERROR: asset_purchases_table, depn_fn, start or end is missing",
    
        LET(
            t, transpose(asset_purchases_table) ,
            tv,transpose(vida_table) ,
            tt,transpose(time_table) ,
            tc,concepto_table , 
            ReturnVertical, IF( ISOMITTED( vertical ), FALSE , vertical ) ,

            //total count of periods to include is the difference between start and end
            //according to the period function given
            PeriodCount, ROWS(asset_purchases_table),

            PeriodLabels, tt,

            /*Returns a vertical table of stacked schedules for each asset
            listed in the control table, with the asset name in the first
            column. 
            */
            ScheduleStack, depre_n.schedule_stack( t, tv, tt, tc ),
            Assets, tc , 
            GetAssetDepn,
                LAMBDA(x, 
                    LET(
                        FilteredDepn, FILTER( DROP( ScheduleStack , 0 , 1 ) , CHOOSECOLS( ScheduleStack , 1 ) = x ) ,
                        XLOOKUP( PeriodLabels , CHOOSECOLS( FilteredDepn , 1 ) , CHOOSECOLS( FilteredDepn , 3 ) , 0 )
                        //SUMIFS(CHOOSECOLS( FilteredDepn , 3 ),CHOOSECOLS( FilteredDepn , 1 ),PeriodLabels)
                    )
                ) ,
            Schedules, 
                REDUCE(
                    GetAssetDepn( TAKE( Assets , 1 ) ),
                    DROP( Assets , 1 ),
                    LAMBDA(a,b,
                        HSTACK( a , GetAssetDepn(b) )
                    )
                ),
            Totals, BYROW( Schedules , LAMBDA(r, SUM( r ) ) ),
            
            Result,
            VSTACK(
                HSTACK( "" , TRANSPOSE( Assets ) , "Total" ),
                HSTACK( PeriodLabels , Schedules , Totals )
            ),
            IF( ReturnVertical , Result , TRANSPOSE( Result ) )
        )
    
    )

)

);`

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment