• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Newbee looking for help with formula for adjusted cost

R

Robican02

New Member
Hi

I am new to this and trying to do calculation on cumulative running adjusted cost for the units in Column K.
Have multiple products on same sheet at different dates which is not shown on the sheet. This sheet will be used to calculate adjusted cost for each units as transaction take place. Column K values are all manually calculated, separate for each symbol, This sheet has showing 4 different symbols . Column A33 to A34 (and few more can be added) are some of the transactions that can taken place.
 

Attachments

  • Adjusted cost sheet.xlsx
    14.4 KB · Views: 7
I'm not sure I can help, it looks complex at first glance.
1. What version of Excel are you using? (Important to know what functions I can use; LET, LAMBDA, FILTER etc.)
2. Are the transactions in date order, earliest at the top? (Dates could be useful in case list isn't in date order)
3. Can you take us through each and every step to calculate cell K30? (I've chosen this one because it has a wide variety of different kinds of transaction so we'll get a better idea of how to handle each type.)
 
p45cal said:
I'm not sure I can help, it looks complex at first glance.
1. What version of Excel are you using? (Important to know what functions I can use; LET, LAMBDA, FILTER etc.)
2. Are the transactions in date order, earliest at the top? (Dates could be useful in case list isn't in date order)
3. Can you take us through each and every step to calculate cell K30? (I've chosen this one because it has a wide variety of different kinds of transaction so we'll get a better idea of how to handle each type.)
Click to expand...
Thankyou for looking in. It is bit complex i agree..

1. I am using EXCEL 2016. Not sure which function to use with FILTER
2. All transactions are in order, earliest starting from top.
3. To understand K30 you have to look at prior transitions (if any), in K26 all units for MFC650 transferred out to some other account so balance is 0 and then on some other date in K28 units (700) are brought in this account so this will we starting point and the price at which they where brought in will also be the starting point. Any transition which is buy (or bought or dividend reinvested) will increase the total number of units and calculated total amount with 2 criteria Column B and C, performed SUMPRODUCT of column D and Column E to get total cost and then divided with total number of units.

When any sell or sold or transfer out, Cost remains same as it provide capital gain or loss only, but the invested cost per unit stay same from last previous buy (or Bought or dividend or transfer in)
 
You must log in or register to reply here.
Back
Top