Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
This Excel Lambda namespace allows for parameterized creation of a depreciation schedule
author = "Owen Price";
author_url = "https://www.flexyourdata.com";
namespace_description="This namespace provides functionality for building depreciation schedules";
/*
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".
*/
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,[show_metadata],
LET(
_show_metadata,IF(ISOMITTED(show_metadata),FALSE,show_metadata),
_metadata,HSTACK(
{"name";"purpose";"author"},
VSTACK({"depn.bymonth";"Return period labels to depn.schedule"},depn.author)
),
_return,DATE(YEAR(start),periods+1,1),
IF(_show_metadata,_metadata,_return)
)
);
byyear = LAMBDA(periods,start,[show_metadata],
LET(
_show_metadata,IF(ISOMITTED(show_metadata),FALSE,show_metadata),
_metadata,HSTACK(
{"name";"purpose";"author"},
{"depn.byyear";"Return period labels to depn.schedule";"Owen Price"}
),
_return,start+periods,
IF(_show_metadata,_metadata,_return)
)
);
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,{"Period labels","Periods","Depreciation","Accumulated Depreciation","Depreciated Asset Value"},
_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))
)
);
waterfall = LAMBDA(
asset_purchases_table,
start,
end,
depn_fn,
[period_fn],
LET(
_pfn, IF( ISOMITTED( period_fn ), depn.byyear , period_fn ),
_periodcount, SWITCH(
_pfn,
depn.byyear, end-start+1,
depn.bymonth, DATEDIF(start,end,"m")+1,
-1
),
_periodcount2, IFS(
depn.byyear = depn.byyear, end-start+1,
depn.byyear = depn.bymonth, DATEDIF(start,end,"m")+1,
TRUE, -1
),
_periods, SEQUENCE(_periodcount,,0),
_periodlabels,_pfn(_periods,start),
_periodcount2
)
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment