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:
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?
Solved! Go to Solution.
Not at my PC right now - but do you need to use previous rows to calculate current row?
I did something similar for an amortization schedule where I built a macro to loop through rows and use previous row for current row calculations.
Check out the macro here:
Hi Wonka. Unfortunately this doesn't solve my problem.
I've created an iterative macro for this type of problem before, see attached (solution to Solved: Inventory Tracking - Alteryx Community) This calculates the values row by row based on previous data. Can you adapt this method to your data?
Thanks @rarrt for pushing this. I found the solution (an iterative macro) in one of the answers for @Christina_H's topic:
Solved: Best way to calculate row by row as next row requi... - Alteryx Community
(the post by CharlieS with the attachment 20180906-FinancialGeneration.yxzp)