Excel formula for calculating the Cost Value on FIFO basis

Hi,

I am at my wits end but getting stuck. Pl. refer to the attached excel file. I want a formula in Col H that will calculate the cost value of the respective stocks on FIFO basis automatically. In FIFO basis, the stock purchased first will be sold first and according to that the cost value of the respective stock has to be calculated after multiple buy and sell transactions for different stocks.

For eg. in the attached excel sheet, the sale value of 225 shares of CCC on 13-09-2017 is 39375 and the cost value on FIFO basis, as calculated manually is 33125 (200 shares purchased on 04-06-2017 @ 150 per share + 25 shares out of 50 shares purchased on 08-08-2017 @ 125).

I need the formula to find the cost value in Col H automatically. The formula should be scalable so that I can add more stocks and transactions and just copy paste the formula.

In Col J & K, I have shown the calculation and the cost value as obtained through manual calculation so that the Cost Value as obtained through a suggested formula in Col H can be checked.

I am happy to clarify further and look forward to the help.

Thanks and regards,

Rahul

P.S: The earlier attached excel file had some mistakes, so uploading the updated file
Question-v1.xlsx
Rahul ChauhanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
I cannot think of a way to do this without VBA. Here is a solution using VBA UDF. You will have to have macros enabled. I have also inserted a formula for the "Balance quantity". I have also added some random data for testing. You will have to test the working of the code thoroughly before using it.
Solution.xlsm
0
Rahul ChauhanAuthor Commented:
Thanks Saqib for the help.

I want to copy the vba code to my other file with data and test it out. Can you please guide how to copy the vba code from your solution file to other excel file and where to paste in the other file?

Also, for balance quantity, can I use different formula i.e.  =SUMIFS($D$3:$D3,$A$3:$A3,$A3,$B$3:$B3,"Buy")-SUMIFS($D$3:$D3,$A$3:$A3,$A3,$B$3:$B3,"Sell") and have updated the formula in the solution file you had sent. I hope it will not interfere with the vba code? The updated solution file is as attached.

Thanks and regards,

Rahul
Solution-v1.xlsm
0
Saqib Husain, SyedEngineerCommented:
To copy the code
In my file 
    Press Alt-F-11
    Select the code by highlighting it
    press Ctrl-c
In your other file
    Right-click on the sheet tab name
    Select View code
    From the menu      Insert > Module
    Paste the copied code in the VBA window (Ctrl-v)
    Close the VBA window

Open in new window


For balance quantity, whatever formula works for you is ok. The code does not use that entry.

The file you uploaded works fine. Do you want something on it from me?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Rahul ChauhanAuthor Commented:
Thanks Saqib.

It's not working in my excel file, maybe because I have many other columns in my sheet. Also, the actual data starts from row 6 & first 4 rows are Column headings etc. The data Columns are till AJ Column.  

I also tried...before copying the vba code...added more columns and brought columns in your solution file similar to my excel file. Pl. refer to the attached file. It shows zero for the cost value, even in your Solution file. It shows error in my file after i copied the code.

Does the formula in vba code also adjusts itself when a row or column is added in the sheet?

Thanks and regards,

Rahul
Solution-v2.xlsm
0
Saqib Husain, SyedEngineerCommented:
Ok, I did not consider columns could be changed.

Now you can change columns with this formula

=IF(F6="sell",fifoval(E6,F6,H6,J6),"")

and this code

Function fifoval(stk As Range, act As Range, q As Range, pr As Range, Optional details As String) As Variant
Application.Volatile (True)
    Dim i As Integer
    Dim qstr As String
    Dim pstr As String
    Dim cqty As Integer
    Dim prc As Double
    Dim qty As Integer
    Dim ctr As Integer
    Dim dstr As String
    Dim amt As Double
    'Stop
    For i = 2 To q.Row - 1
        If Cells(i, stk.Column) = Cells(q.Row, stk.Column) Then
            Select Case Cells(i, act.Column)
                Case "Buy"
                    qstr = qstr & Cells(i, q.Column) & ","
                    pstr = pstr & Cells(i, pr.Column) & ","
                Case "Sell"
                    qty = Cells(i, q.Column)
                    Do While qty > 0
                        
                        cqty = Val(qstr)
                        If cqty = 0 Then
                            fifoval = "Not enough balance"
                            Exit Function
                        End If
                        Select Case True
                            Case cqty = qty
                                qstr = Replace(qstr, cqty & ",", "", , 1)
                                pstr = Replace(pstr, Val(pstr) & ",", "", , 1)
                                qty = qty - cqty
                            Case cqty > qty
                                qstr = Replace(qstr, cqty, cqty - qty, , 1)
                                qty = 0
                            Case cqty < qty
                                qstr = Replace(qstr, cqty & ",", "", , 1)
                                pstr = Replace(pstr, Val(pstr) & ",", "", , 1)
                                qty = qty - cqty
                            Case cqty = 0
                                fifoval = "Not enough balance"
                                Exit Function
                        End Select
                        ctr = ctr + 1
                        If ctr > 1000 Then End: Stop
                    Loop
            End Select
        End If
    Next i
    qty = Cells(q.Row, q.Column)
    Do While qty > 0
        
        cqty = Val(qstr)
        If cqty = 0 Then
            fifoval = "Not enough balance"
            Exit Function
        End If
        prc = Val(pstr)
        Select Case True
            Case cqty = qty
                dstr = dstr & IIf(dstr = "", "", " + ") & qty & " * " & prc
                amt = amt + qty * prc
                qstr = Replace(qstr, cqty & ",", "", , 1)
                pstr = Replace(pstr, Val(pstr) & ",", "", , 1)
                qty = qty - cqty
            Case cqty > qty
                dstr = dstr & IIf(dstr = "", "", " + ") & qty & " * " & prc
                amt = amt + qty * prc
                qstr = Replace(qstr, cqty, cqty - qty, , 1)
                qty = 0
            Case cqty < qty
                dstr = dstr & IIf(dstr = "", "", " + ") & cqty & " * " & prc
                amt = amt + cqty * prc
                qstr = Replace(qstr, cqty & ",", "", , 1)
                pstr = Replace(pstr, Val(pstr) & ",", "", , 1)
                qty = qty - cqty
        End Select
        ctr = ctr + 1
        If ctr > 1000 Then End: Stop
    Loop
    If details = "" Then
        fifoval = amt
    Else
        fifoval = dstr
    End If
End Function

Open in new window

0
Rahul ChauhanAuthor Commented:
Thanks Saqib Bhai.

3 errors are coming:

1) Ambiguous name detected: fifoval

2) Circular reference error

3) The Qty & Price values in the Column T are incorrect for many stocks.

Thanks and regards,

Rahul
0
Saqib Husain, SyedEngineerCommented:
1) Ambiguous name detected: fifoval
You have to delete the previous version of the code

2) Circular reference error
Is this in the your file or the one you uploaded later?

3) The Qty & Price values in the Column T are incorrect for many stocks.
That could be possible. I need examples to test this.
0
Rahul ChauhanAuthor Commented:
Thanks for the revert Saqib Bhai.

I had deleted the previous code. Infact, I copied the new code to new file. The circular ref error comes after copying the formula to my file, otherwise this error doesn't come in my file. I am sending you the sample file.

Thanks and regards,

Rahul

P.S: The ambiguous name error is not coming anymore when I created the sample file, but other 2 problems r still coming.
1
Saqib Husain, SyedEngineerCommented:
Column T is causing the problem. What do you need it for?
0
Rahul ChauhanAuthor Commented:
The Column T formula, that is, =IF(SUMPRODUCT(MAX((E6=$E$5:E5)*ROW($E$5:E5)))>0,INDEX($T$5:$T$69,SUMPRODUCT(MAX((E6=$E$5:E5)*ROW($E$5:E5)))),"")&IF(IF(SUMPRODUCT(MAX((E6=$E$5:E5)*ROW($E$5:E5)))>0,INDEX($T$5:$T$69,SUMPRODUCT(MAX((E6=$E$5:E5)*ROW($E$5:E5)))),"")="","",";")&H6&","&J6

was in the hidden column in the first solution file you had sent. Pl. refer to the hidden Col H in the attached Solution file you had initially sent.

So, I thought you have put this formula to calculate fifoval in the code. Hence I also copied this formula along with the fifoval code and Value at Cost (FIFO) formula i.e. =IF(F12="sell",fifoval(E12,F12,H12,J12),"")

Now, just to reconfirm, can I delete Col T and this formula =IF(SUMPRODUCT(MAX((E6=$E$5:E5)*ROW($E$5:E5)))>0,INDEX($T$5:$T$69,SUMPRODUCT(MAX((E6=$E$5:E5)*ROW($E$5:E5)))),"")&IF(IF(SUMPRODUCT(MAX((E6=$E$5:E5)*ROW($E$5:E5)))>0,INDEX($T$5:$T$69,SUMPRODUCT(MAX((E6=$E$5:E5)*ROW($E$5:E5)))),"")="","",";")&H6&","&J6

is not needed??
Solution.xlsm
0
Rahul ChauhanAuthor Commented:
Hi Saqib bhai,

You are right. After deleting the Col T, circular ref error is not coming and fifoval code is working fine. But there is one issue which needs to be fixed, for which, I think the code needs to be updated.

Right now FIFO cost value is calculated if Col F = Sell.

I want, IF F6 = Sell or Empty Cell, the FIFO Cost Value value shd be calculated. So, I have updated the formula to =IF(OR(F6="Sell",F6=""),fifoval(E6,F6,H6,J6),""). But the formula is giving 0 in cells where F is an empty cell (for instance F63 to F69 in the attached sample v1 file). To fix this, I think the code also needs to be updated.

Can you plz guide me, where in the code such an updation needs to be done so that I can do it and next time also, if i need any other updation I will be able to do it.

Thanks and regards,

Rahul
Sample-File-v1.xlsm
0
Saqib Husain, SyedEngineerCommented:
I do not understand. If there is no quantity given (in column H) then what will the cost be based on?
0
Rahul ChauhanAuthor Commented:
I got your point Squib Bhai. Where Col F is empty, the Qty (Col H) is zero, so thats why fifoval is returning 0

The way I have organised the data and formulas...Action i.e. Col F is based on actual transactions  i.e. buy, sell, dividend, bonus, IPO etc. The blank space in Col F represents an imaginary transaction ( for eg: row 63 to 69 in the sample file v1) that provides me the latest portfolio returns, total gain/loss of the respective stock automatically.

What I mean by above is ...by using today date formula the transaction date of these imaginary transactions is automatically taken as the current date. The latest stock price I am fetching from net on real time basis using get data function and that is used as the transaction price (Col J). Now I have to use Total Qty (Col I) to multiply it with transaction price (Col J) to find out the total gain/loss, CAGR etc for the individual stocks in these imaginary transaction with blanks in Col F (Rows 63 to 69).

Is it possible, if the code can be updated to take qty in Col H if its a Sell in Col F or total qty in Col I if its blank in Col F?

Or

maybe, another code, lets say, fifoval_1, where it takes the Sell Qty as Total Qty in Col I? I will use this fifoval_1 for the imaginary transactions where F is blank.

Thanks and regards

Rahul
0
Saqib Husain, SyedEngineerCommented:
In the code change
    qty = Cells(q.Row, q.Column)

to

    If q2 Is Nothing Then qty = q Else qty = q2

Open in new window

and change the formula to

=IF(F6="Sell",fifoval(E6,F6,H6,J6),IF(F6="",fifoval(E6,F6,H6,J6,I6),""))

Another thing I forgot to mention is that if you add an optional argument at the end of fifoval function

fifoval(E6,F6,H6,J6,,1)  
or
fifoval(E6,F6,H6,J6,I6,1)

then it will show the calculation steps instead.
0
Rahul ChauhanAuthor Commented:
Thanks Saqib bhai.

It's not working and shows an error. Pl. refer to the attached file.

I couldn't understand what you mean by..... "Another thing I forgot to mention is that if you add an optional argument at the end of fifoval function
fifo[/i]val(E6,F6,H6,J6,,1)  
or
fifoval(E6,F6,H6,J6,I6,1)

then it will show the calculation steps instead."

Thanks and regards

Rahul
Sample-File-v2.xlsm
0
Saqib Husain, SyedEngineerCommented:
Sorry you also have to change the first line to

Function fifoval(stk As Range, act As Range, q As Range, pr As Range, Optional q2 As Range, Optional details As String) As Variant
0
Rahul ChauhanAuthor Commented:
Dear Saqib bhai,

It works fine now but it gives the error when data connection in excel is turned on.

Pl. refer to the attached file. In the SQ worksheet, I have added the data connection which fetches the stock quotes in real time and it is used by Cells J63 to J69 on Z worksheet. So as soon as I open the excel file, excel asks me if I want to turn on the data connection link. If I say No, then the fifoval works fine. But if I say yes, then fifoval gives error.

For your ready reference, I am attaching both the screen shots.

I have tested this error on different files and different computers too. I have checked all the data and macro settings in excel too. But still this issue is coming. Could there be something in the code that is interfering with the data connection?

I request one more help. Currently, the fifoval code takes the txn price in Col J to calculate the Value at Cost.

I was hoping to find the Net Value at Cost, for which I will have to use the net price (Col O). On a separate excel file, I tried to update the code myself but I am not able to do it. Can you please help me with this.

Thanks and regards,

Rahul
Sample-File-v3.xlsm
Screen-shot_without-error.jpg
Screen-shot_with-error.jpg
0
Saqib Husain, SyedEngineerCommented:
I am not sure what is causing the shown message. It is working fine when I load the file and switch on data connection. I have also waited for the connection to refresh a few times. But when I get back to work and again return to your file the error is there. Pressing F9 should take care of this.

For the price, you do not have to do anything with the code. You only have to modify the formula in excel by changing one or both instances of Jxx to Oxx or whatever.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rahul ChauhanAuthor Commented:
Dear Saqid bhai,

The fifoval code is working fine with gross and net cost values too and the F9 takes care of the error.

I have checked it in the master file too and its working fine. Thanks a lot.

I am closing the question and thanks again for your help all along.

Regards,

Rahul
0
Rahul ChauhanAuthor Commented:
Thanks Saqib bhai for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.