Hey there and welcome to my first post 🙂
The Story
Provided is a dataset of transactions (purchases and sales of stocks) that covers multiple years (example data reduced to one asset over 3 years). These assets need to be valuated at the end of each year t and the valuation at the end of t0 is the basis for the subsequent calculations in year t+1.
Valuation is as follows:
- Quantities of the asset are purchased and sold throughout the year
- Ongoing valuation is based on the average purchasing price per piece
- At year end, ongoing valuation will be higher or lower than actual stock exchange price and depreciation or appreciation needs to be considered
- Depreciation/appreciation affects valuation of subsequent year
- I calculate the necessary fields linearly with formulas, multirow-formulas and running totals
Problem
Above approach leads to a circular reference as of t+1 because the valuation per piece in t+1 is affected by the depreciation in t0. So my column “AvgPricePerPiece” at the start of t+1 needs to incorporate the depreciation from the end of t0, but I cannot take it into account, because the depreciation calculation comes after the valuation per piece calculation. The average price per piece at the start of 2021 (cell Q30 in output-file) needs to incorporate the depreciation of 2020 (cell U29).
How can I solve the problem of not being able to run this calculation block by block (1 year = 1 block) without duplicating my existing workflow for each occurring year?
