Forum Discussion
Need Help Calculating Book Value (Adjusted Cost Base) of Investments
That is correct, there are multiple accounts that may have transactions on the same date and one account may also have multiple transactions on the same date. The account instance is just a count of transactions in one account up until that point. It is important as we need to find the most recent (current instance -1) book value per share and the transactions are ordered (in my main database) so that distributions occur first, then redemptions, and lastly contributions to properly calculate book value.
Community ChampionHi NKrichmond
Actually milanpasschier2 has done a very great job. The thing which I don't understand is the meaning of each measure. Not sure if that was illustrated somewhere in the post as I didn't read it all. If not please provide detailed description of each measure. Thank you
Yes it is close, but still not calculating properly. Ill try to explain them all:
- Account Instance: The number of transactions in an account up until the current date.
- Change in units: The number of units bought, sold, or distributed in each transaction. = (Market Value / Price) for Contributions, Redemptipons and Dstr'n - Reinvested. = 0 for Dstr'n - Paid out. This measure is working well.
- Ending Units: The number of units an account holds on that specific date. = cumulative sum of the change in units for a specific account up until that date. This measure is working well.
Now this is where the problems start...
- Change in Book Value: The change in book value due to a transaction. = Market Value for Contributions and Dstr'n - Reinvested. = 0 for Dstr'n - Paid Out. = (Change in Units * Prev. BVPS). for Redemptions. This measure is set up correctly.
- Ending book value: The book value of an account on that date. Calculated the same way as ending units but is instead a cumulative sum of Change in Book Value. The "Ending Book Value Measure" is set up correctly. Note that the "Ending Book Value" calculated column is not calculating correctly.
- Ending BVPS (book value per share): = (Ending Book Value / Ending Units)
- Prev. BVPS: = Ending BVPS from the most recent transaction
The problem is that the Change in Book Value relies on the Prev. BVPS, which relies on the Ending Book Value, which relies on the Change in Book Value, resulting in a circular dependancy.
- tamerj1
Ok this is a recursive calculations which dax don't support. The workaround if possible is usually complex. I'll do my best to find a solution. I'll let you know if I was able to reach any solution. Good night for now.