Forum Discussion

NKrichmond's avatar
NKrichmond
Frequent Visitor
4 years ago

Need Help Calculating Book Value (Adjusted Cost Base) of Investments

Hi there. I am modernizing some Excel databases and am having some problems with DAX. I need a calculated column or a measure that will calculate the change in book value (adjusted cost base) for each transaction and one that will calculate the cumulative ending book value for a given account #. I have attached a link to a sample spreadsheet.

 

https://docs.google.com/spreadsheets/d/13DKQaMC55h0yg_wu-kDnNrI2-AzpyOK4/edit?usp=sharing&ouid=100706133115617609789&rtpof=true&sd=true 

 

I will try to explain my issue as clearly as possible:

- There are four types of transactions: Contribution, Redemption, Distribution - Reinvested, and Distribution - Paid Out.

 

- The change in book value for a "contribution" or "distribution - reinvested" is equal to the market value of the transaction. The change in book value of a "distribution - paid out" is 0. The change in book value for a "redemption" is equal to the number of units sold multiplied by the previous book value per unit. Previous book value per unit is simply the ending total book value divided by number of units owned on the last transaction date. This is where I am running into problems as I am getting circular reference errors trying to calculate the change in book value, where it depends on the sum of previous changes in book value.  

 

- There are three example accounts in this workbook and their book value calculation are independant of each other. 

 

The calculation is fairly straightforward in Excel but depends on the data being sorted properly. It uses an account instance formula to look up the previous transaction and grab the book value per unit. 

 

Any help with this would be much appreciated, Thanks! 

 

18 Replies

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, NKrichmond 

    Please try calculated columns as below:

    Account Instance = CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[Account #]=EARLIER('Table'[Account #])&&'Table'[Date]<=EARLIER('Table'[Date])))
    Change in Units = IF('Table'[Action]="Dstr'n - Paid out",0,'Table'[Market Value]/'Table'[Price])
    Ending Units = CALCULATE(SUM('Table'[Change in Units]),FILTER('Table','Table'[Account #]=EARLIER('Table'[Account #])&&'Table'[Account Instance]<=EARLIER('Table'[Account Instance])))
    Ending Book Value = CALCULATE(SUM('Table'[Market Value]),FILTER('Table','Table'[Account #]=EARLIER('Table'[Account #])&&'Table'[Account Instance]<=EARLIER('Table'[Account Instance])))
    Ending BVPS = IFERROR( 'Table'[Ending Book Value]/'Table'[Ending Units],0)
    Prev. BVPS = CALCULATE(SUM('Table'[Ending BVPS]),FILTER('Table','Table'[Account #]=EARLIER('Table'[Account #])&&'Table'[Account Instance]=EARLIER('Table'[Account Instance])-1))

    result:

     

    Best Regards,
    Community Support Team _ Eason

    • NKrichmond's avatar
      NKrichmond
      Frequent Visitor
      4 years ago

      Thanks for the reply. There are a few issues with that solution.

       

      - Book value is being calculated as the sum of market values by account, where it needs to be calculated based on Action (transaction type). If i add "&& [Action] <> "Dstr'n - Paid out"" to the filter then it ignores paid out distributions as it should. This formula then works correctly for everything except redemptions, which is where the issues come from. 

      - If i add a new column for change in book value from redemptions, calculated as =IF([Action] = "Redemption" ,[Change in Units]*[Prev. BVPS],0) and then try to add this to the Ending Book Value calculation I get a circular dependancy error.

      -  The account instance formula is almost what I was looking for. The issue is that there can be multiple transactions on the same day and the instance will sum both and return a value 2 higher than the previous. The Prev. BVPS column then tries to look up that value -1 and returns nothing:

       

      Thanks again for the help, any other info would be appreciated although I'm starting to wonder if what I am looking for is even possible within DAX or if should stick with Excel...

       

  • milanpasschier2's avatar
    milanpasschier2
    Icon for Helper I rankHelper I
    4 years ago

    Hey man,

    Try:

    Prev. BVPS = 

VAR _ACCOUNT = 'Table'[Account #]
VAR _ACCOUNT_INSTANCE = 'Table'[Account Instance]

VAR _PREV_ENDING_BVPS = MAXX(
    CALCULATETABLE('Table', FILTER('Table','Table'[Account #]=_ACCOUNT&&'Table'[Account Instance]<_ACCOUNT_INSTANCE)), 'Table'[Ending BVPS])

RETURN _PREV_ENDING_BVPS


    Please mark as solution if it fits your needs.

     

    Best,

    Milan

    • NKrichmond's avatar
      NKrichmond
      Frequent Visitor
      4 years ago

      Thanks Milan, that formula works well and solves the account instance issue I was having. However, the main issue I am having remians: the book value is not calculating properly and I am getting a circular dependancy error whenever I try to calculate it. This is because the book value calculation for redemptions depends on the previous BVPS, which depends on earlier ending book value calculations. Do you know if there is any way to get around this?