# 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
###### 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.

EngineerCommented:
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
Author 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
EngineerCommented:
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
``````

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
Author 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
EngineerCommented:
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)
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
``````
0
Author 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
EngineerCommented:
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
Author 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
EngineerCommented:
Column T is causing the problem. What do you need it for?
0
Author 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
Author 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
EngineerCommented:
I do not understand. If there is no quantity given (in column H) then what will the cost be based on?
0
Author 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
EngineerCommented:
In the code change
``````    qty = Cells(q.Row, q.Column)

to

If q2 Is Nothing Then qty = q Else qty = q2
``````
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
Author 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
EngineerCommented:
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
Author 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
EngineerCommented:
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.

Author 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
Author 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.