Calculating LIFO Returns on Investments :: MrExcel Message Board


 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

Calculating LIFO Returns on Investments
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

The Grabber
Board Regular


Joined: 20 Sep 2003
Posts: 21
Location: Texas
Flag: Usa

Status: Offline

 Reply with quote  

Calculating LIFO Returns on Investments

Hi everyone:

This is my first post / question. This site was recommended by Matt over on an investment BB we frequent.

My question is...

How can I create a set of formulae in Excel whereby I can calculate the LIFO (Last In, First Out) returns on a series of stock buys and sells? More specifically; I would like to subtract Units Sold, $ Sold from a running total of Units Bought, $ Bought. Of course since each Buy would likely be at a different price per unit, the subsequent reduction of 'x' units at a (hopefully) higher price presents issues.

I'm assembling a tracking worksheet for an investment discipline and I need this info to help in that effort.

Thanks and........
_________________
Regards, Steve

Post Sat Sep 20, 2003 2:06 am 
 View user's profile Send private message

Travis
Board Master


Joined: 27 Feb 2002
Posts: 439
Location: Phoenix, Arizona
Flag: Usa

Status: Offline

 Reply with quote  

Re: Calculating LIFO Returns on Investments

Welcome to the board. Your question is confusing, can you post some sample data using Colos HTML Maker found at the bottom of the page?

Post Sat Sep 20, 2003 5:34 am 
 View user's profile Send private message Send e-mail

The Grabber
Board Regular


Joined: 20 Sep 2003
Posts: 21
Location: Texas
Flag: Usa

Status: Offline

 Reply with quote  

Re: Calculating LIFO Returns on Investments

Hi Travis:

I did the download, and extracted it to the correct folder, but when I selected the add-in from the tools menu in Excel (BTW I have Office XP), I got some type of error.


Basically the data looks like this (in transaction sequence):

1) bgt 500 shares @ 5.75
2) sld 185 shares @ 6.40
3) bgt 170 shares @ 5.00
4) sld 185 shares @ 7.15
5) bgt 385 sgares @ 5.50
6) bgt 319 shares @ 4.75
7) sld 233 shares @ 6.26
8) sld 771 shares @ 8.00

The question is how can I relieve the shares and $ value of those shares on a Last in First out Basis (LIFO). For example, # 2 relieves 185 shares of #1, leaving 315 of those. Then I buy #3. Then # 4 relieves all of #3 and an additional 15 of #1, now leaving #1 with 300. I then buy # 5 and 6. The next Sell (#7) takes 233 of # 6; leaving 86. # 8 takes those 86 from #6, all 385 of #5, and the remaining 300 from #1. We are now relieved to 0.

It seems like some type of grid problem to me. It is easy enough to do conceptually, but I'm having a mental block as far as do this in Excel. It would be great if I could get some help on this.

Thanks and........
_________________
Regards, Steve

Post Sat Sep 20, 2003 6:29 pm 
 View user's profile Send private message

The Grabber
Board Regular


Joined: 20 Sep 2003
Posts: 21
Location: Texas
Flag: Usa

Status: Offline

 Reply with quote  

Re: Calculating LIFO Returns on Investments

Success!

Microsoft Excel - Lifo Dilemma.xls___Running: xl2002 XP : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
=

A
B
C
D
E
F
G
H
I
J
K
L
1
Transaction History Unit Flow Cash Flow
2
DateShares bgt (sld)Price per Share$ Value Units inUnits OutCumulative $ out$ inCumulative
3
6/1/03500 $ 5.75 $ 2,875.00 5000500 $2,875.00 $ - $(2,875.00)
4
6/15/03(185) $ 6.40 $ (1,184.00) 0185315 $ - $ 1,184.00 $(1,691.00)
5
6/29/03170 $ 5.00 $ 850.00 1700485 $ 850.00 $ - $(2,541.00)
6
7/13/03(185) $ 7.15 $ (1,322.75) 0185300 $ - $ 1,322.75 $(1,218.25)
7
7/27/03385 $ 5.50 $ 2,117.50 3850685 $2,117.50 $ - $(3,335.75)
8
8/10/03319 $ 4.75 $ 1,515.25 31901004 $1,515.25 $ - $(4,851.00)
9
8/24/03(233) $ 6.25 $ (1,456.25) 0233771 $ - $ 1,456.25 $(3,394.75)
10
9/7/03(771) $ 8.00 $ (6,168.00) 07710 $ - $ 6,168.00 $ 2,773.25
Sheet1

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

_________________
Regards, Steve

Post Sat Sep 20, 2003 6:35 pm 
 View user's profile Send private message

Yogi Anand
MrExcel MVP


Joined: 13 Mar 2002
Posts: 5749
Location: Michigan USA

Status: Offline

 Reply with quote  

Re: Calculating LIFO Returns on Investments

Hi there Steve:

Welcome to MrExcel Board!

Great Job at solving your own problem -- now we know that MrExcel Board is also a great sounding board -- or would that be bouncing board?. Bounce the problem off MrExcel Board and find a solution. Seriously, Keep EXCELing!
_________________
Regards!
Yogi Anand

Post Sat Sep 20, 2003 7:07 pm 
 View user's profile Send private message

The Grabber
Board Regular


Joined: 20 Sep 2003
Posts: 21
Location: Texas
Flag: Usa

Status: Offline

 Reply with quote  

Re: Calculating LIFO Returns on Investments

Hi Yogi:

I didn't solve my problem at all! The Success I mentioned related to getting the table to show!

The page I posted only shows the flow, but not the set of calcs needed to do the LIFO relief I described in the post previous to it..

I still need help (I think).

Thanks and...
_________________
Regards, Steve

Post Sat Sep 20, 2003 8:17 pm 
 View user's profile Send private message

George J
Board Master


Joined: 16 Feb 2002
Posts: 375
Location: Edinburgh, Bonnie Scotland
Flag: Scotland

Status: Offline

 Reply with quote  

Re: Calculating LIFO Returns on Investments

This last in first out is losing us a bit i think.

It looks as though these figures relate to just the 1 stock, so can you give an example of what you would like to see and we can then work through it from the data you have already given.

If it was a simple profit/loss calculation you would just add an extra column and
=sum(-d3)
=SUM(E3-D4) and carrying this down with E being the column with this new data.
_________________
George J

Post Sat Sep 20, 2003 9:29 pm 
 View user's profile Send private message Send e-mail

Travis
Board Master


Joined: 27 Feb 2002
Posts: 439
Location: Phoenix, Arizona
Flag: Usa

Status: Offline

 Reply with quote  

Re: Calculating LIFO Returns on Investments

see if this helps

http://www.mrexcel.com/board2/viewtopic.php?t=59711&highlight=lifo#javascript:void(0);

Post Sat Sep 20, 2003 10:10 pm 
 View user's profile Send private message Send e-mail

The Grabber
Board Regular


Joined: 20 Sep 2003
Posts: 21
Location: Texas
Flag: Usa

Status: Offline

 Reply with quote  

Re: Calculating LIFO Returns on Investments

Hi George:

so can you give an example of what you would like to see and we can then work through it from the data you have already given.


What I'd like to see is the Cost of Goods Removed (on a LIFO basis) after each sell. Then I would know what the average price is of the remaining shares.

Thanks and....
_________________
Regards, Steve

Post Sun Sep 21, 2003 12:53 pm 
 View user's profile Send private message

Yogi Anand
MrExcel MVP


Joined: 13 Mar 2002
Posts: 5749
Location: Michigan USA

Status: Offline

 Reply with quote  

Re: Calculating LIFO Returns on Investments

quote:
Originally posted by The Grabber:

....
What I'd like to see is the Cost of Goods Removed (on a LIFO basis) after each sell. Then I would know what the average price is of the remaining shares.

Thanks and....

Hi Steve:

Please look at the following simulation ...

Microsoft Excel - y030921h1.xls___Running: xl97 : OS = Windows 98
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
=

A
B
C
D
1
 
2
DateUnitsBought/SoldUnitPriceNet(Cumulative)
3
06/01/20035005.75-2875
4
06/15/2003-1856.4-1691
5
06/29/20031705-2541
6
07/13/2003-1857.15-1218.25
7
07/27/20033855.5-3335.75
8
08/10/20033194.75-4851
9
08/24/2003-2336.25-3394.75
10
09/07/2003-77182773.25
Sheet5a 

[HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


Is this what you are looking for? If I have misunderstood your question -- please explain a little further and then let us take it from there.
_________________
Regards!
Yogi Anand

Post Sun Sep 21, 2003 7:40 pm 
 View user's profile Send private message

The Grabber
Board Regular


Joined: 20 Sep 2003
Posts: 21
Location: Texas
Flag: Usa

Status: Offline

 Reply with quote  

Re: Calculating LIFO Returns on Investments

Hi Yogi:

Actually what you've shown is the cumulative $ as they are being sent out/in via Buys and Sells.

I need to calculate the Cost $ Sold (COGS) basis a Last In, First Out approach.

For example, the first 185 shares I sold on 6/15 for 6.40 each had a Cost of had a cost of 5.75 each (they were the 'last ones in'). So the cost was 185*5.75=1063.75. That was easy!

The next Sell on 7/13 for 7.15 each had a cost made up of 170*5.00=850.00 PLUS 15*5.75=86.25 (totalling 936.25).

You can see now that I've sold all of the 170 shares that were purchased on 6/29 (they were the Last ones in) and another 15 of the 315 remaining from the original 6/1 purchase (they were the next in line to be relieved).

And so on until the last 771 shares that were sold relieve cost $ from the 8/10, 7/27 and 6/1 purchases.

I hope this clarifies things. I'm not very good a describing it, I just know It's right!

Thanks and...


[/u]
_________________
Regards, Steve

Post Tue Sep 23, 2003 12:13 am 
 View user's profile Send private message

Yogi Anand
MrExcel MVP


Joined: 13 Mar 2002
Posts: 5749
Location: Michigan USA

Status: Offline

 Reply with quote  

Re: Calculating LIFO Returns on Investments

Hi Steve:

If you are having difficulty describing what you are trying to accomplish --then for the data I have presented in my post, how about if you add a column E and present in that column E, what you think are the right results that you are after (witout worryng about the logic)-- so we can see what you are after -- and then let us take it from there.
_________________
Regards!
Yogi Anand

Post Tue Sep 23, 2003 12:26 am 
 View user's profile Send private message

The Grabber
Board Regular


Joined: 20 Sep 2003
Posts: 21
Location: Texas
Flag: Usa

Status: Offline

 Reply with quote  

Re: Calculating LIFO Returns on Investments

OK Yogi:

Howz This?

Microsoft Excel - Lifo Dilemma.xls___Running: xl2002 XP : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
=

A
B
C
D
E
F
G
H
1
DateShares bgt/(sld)Price per Share$ ValueLIFO Cost $ SoldCost Components
2
6/1/03500  $     5.75  $ 2,875.00     
3
6/15/03(185) $     6.40  $(1,184.00) $1,063.75  $  1,063.75   
4
6/29/03170  $     5.00  $    850.00     
5
7/13/03(185) $     7.15  $(1,322.75) $   936.25  $    850.00  $     86.25  
6
7/27/03385  $     5.50  $ 2,117.50     
7
8/10/03319  $     4.75  $ 1,515.25     
8
8/24/03(233) $     6.25  $(1,456.25) $1,106.75  $  1,106.75   
9
9/7/03(771) $     8.00  $(6,168.00) $4,251.00  $    408.50  $2,117.50  $1,725.00 
Sheet1 

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

_________________
Regards, Steve

Post Tue Sep 23, 2003 1:37 am 
 View user's profile Send private message

George J
Board Master


Joined: 16 Feb 2002
Posts: 375
Location: Edinburgh, Bonnie Scotland
Flag: Scotland

Status: Offline

 Reply with quote  

Re: Calculating LIFO Returns on Investments

In column E could this formula be what you are looking for

=if(b2<0,sum(f2:h2),"")

put this formula in E2 and drag from the bottom right of the cell to copy down.
_________________
George J

Post Tue Sep 23, 2003 9:49 am 
 View user's profile Send private message Send e-mail

The Grabber
Board Regular


Joined: 20 Sep 2003
Posts: 21
Location: Texas
Flag: Usa

Status: Offline

 Reply with quote  

Re: Calculating LIFO Returns on Investments

Hi George:

I can see that one would sum the cost components only when you have a sell. However, what are the formulas for getting to the cost component numbers? I manually calc'd those (at Yogi's suggestion) so he could see the structure of what I'm looking for.

In each of those cells, I knew how many shares and at what cost would be relieved from each purchase (on a Last in, First out basis) at each point in time. EG: On 6/15, I sold 185 shares. The last shares in were on 6/1, and I bought those at 5.75. So I sold 185 shares on 6/15 that cost me 5.75 each. I relieve those, leaving 315 to sell on on some future date.

You will note that I didn't calculate that # in my example, I just knew that there were 315 left. My next sell occurred on 7/13. But by that time I had another purchase (Last in). But that purchase was only for 170 shares. So I relieve those (@5.00), and another 15 from my 6/1 balance of 315 (@5.75).

By the time I get to my last Sell (771 shares), I have to find the relief from the 8/10, 7/27 and 6/1 purchases (all shares are now sold) .

Bottom line is that I need the worksheet to keep track of all of these incrementals.

Thanks and...
_________________
Regards, Steve

Post Tue Sep 23, 2003 10:40 am 
 View user's profile Send private message

George J
Board Master


Joined: 16 Feb 2002
Posts: 375
Location: Edinburgh, Bonnie Scotland
Flag: Scotland

Status: Offline

 Reply with quote  

Re: Calculating LIFO Returns on Investments

I hope "the grabber" is still out there.

This is the most mind-boggling and therefore sloopy code i have ever done.
(that's my excuse antway icon_mrgreen.gif )

Slight modifications to the last "screenshot" you gave first

Insert a row at the top of the page - for some reason i put a title in!!!

Insert a column before cost components. This new column F i have the heading (in F2) "shares left" - just so you know what is going on.

Copy and paste this formula into cell F3 =IF(B3<0,0,IF(B3>=0,B3))
In the bottom right of the cell (if selected) click and drag down to copy the formula to the other cells in column F

Press ALT and F11 (not AltGr)

goto Insert - Module

Paste this macro in

Sub LIFO()
Dim shares As Range
Dim price As Range
Dim sold As Range
Dim i As Range
Dim xtra As String
Dim xtra2 As String

Set shares = Range(Range("b3"), Range("b3").End(xlDown))
Set price = Range(Range("c3"), Range("c3").End(xlDown))

For Each i In shares

cc = 7
ro = -1

Set sold = Cells(i.Row, cc)

If i > 0 Then
orig = i
origr = i
Else
origr = i.Offset(ro, 4)
orig = i.Offset(ro, 4) + i

If orig > 0 Then
sold.Value = 0 - (i * i.Offset(-1, 1))
Cells(i.Row + ro, 6).Value = orig

Else
Do
xtra = orig
xtra2 = origr
If origr > 0 Then
sold.Value = origr * i.Offset(ro, 1)
orig = origr + i
Cells(i.Row + ro, 6).Value = Cells(i.Row + ro, 6) - origr

If sold.Value <> "" And sold.Value <> "0" Then
cc = cc + 1
Set sold = Cells(i.Row, cc)
End If
xtra2 = orig
ro = ro - 1
orig = orig + i.Offset(ro - 1, 4)
origr = i.Offset(ro, 4)
Else
sold.Value = ((i.Offset(ro - 1, 4)) * i.Offset(ro - 1, 1))
orig = orig + i.Offset(ro - 1, 4)
Cells(i.Row + ro - 1, 6).Value = Cells(i.Row + ro - 1, 6) - i.Offset(ro - 1, 4)
If sold.Value <> "" And sold.Value <> "0" Then
cc = cc + 1
Set sold = Cells(i.Row, cc)
End If
xtra2 = orig
ro = ro - 1
orig = orig + i.Offset(ro - 1, 4)
origr = i.Offset(ro, 4)
End If

If orig > 0 And (xtra2 + origr) <> 0 Then
lft = lft + 2
sold.Value = 0 - (xtra * i.Offset(ro - 1, 1))
Cells(i.Row + ro - 1, 6).Value = Cells(i.Row + ro - 1, 6) + xtra
Else
If orig <= 0 And (xtra2 + origr) <> 0 Then
sold = sold.Offset(0, 1)
sold.Value = (i.Offset(ro - 1, 4) * i.Offset(ro - 1, 1))
Cells(i.Row + ro - 1, 6).Value = Cells(i.Row + ro - 1, 6) - i.Offset(ro - 1, 4)
If sold.Value <> "" And sold.Value <> "0" Then
cc = cc + 1
Set sold = Cells(i.Row, cc)
End If
End If
End If
If (xtra2 + origr) = 0 Then
Cells(i.Row + ro, 6).Value = Cells(i.Row + ro, 6) - i.Offset(ro, 4)
End If
Loop Until orig >= 0
End If

End If
Next i

End Sub



Close down the VB editor

In your excel workbook goto Tools - Macro... - Macros
Select the LIFO macro and select options

You can assign a shortcut that will run the macro eg CTRL and L

Close down back to your workbook and save as something completely different - so that if this does not work you don't lose any data.

I have tried a few checks to make sure it works but i was starting to lose it bigtime icon_iwantatten.gif

Try running the macro and get back to us if there is anything gaga going on.
_________________
George J

Post Mon Sep 29, 2003 12:35 am 
 View user's profile Send private message Send e-mail

The Grabber
Board Regular


Joined: 20 Sep 2003
Posts: 21
Location: Texas
Flag: Usa

Status: Offline

 Reply with quote  

Re: Calculating LIFO Returns on Investments

The Grabber is still here.

Wow! Thanks for your interest!

I've never really worked with VBA Macros before, but am willing to give it a try. I'll have to find some time to get that in there.

Can I copy and past from your post?

Thanks and...
_________________
Regards, Steve

Post Mon Sep 29, 2003 3:24 am 
 View user's profile Send private message

George J
Board Master


Joined: 16 Feb 2002
Posts: 375
Location: Edinburgh, Bonnie Scotland
Flag: Scotland

Status: Offline

 Reply with quote  

Re: Calculating LIFO Returns on Investments

Yup, you just highlight the macro copy and paste in the vb editor

But please do not save over the original workbook - just in case

I did notice that the share transaction charges were not there, but wasn't sure where this cost was to be placed on your spreadsheet.

also you will have to watch that there are enough columns in the cost components section
_________________
George J

Post Mon Sep 29, 2003 8:51 am 
 View user's profile Send private message Send e-mail

The Grabber
Board Regular


Joined: 20 Sep 2003
Posts: 21
Location: Texas
Flag: Usa

Status: Offline

 Reply with quote  

Re: Calculating LIFO Returns on Investments

Hi George:

Well I did everything and when I ran it, it zero'd out the new column F. Was there something else I was supposed to do? Like empty thecontents in the cost component section?

I guess what I'm asking is.....Where should the workbook be before beginning?

Do I simply run it after every Sell?

Thanks
_________________
Regards, Steve

Post Mon Sep 29, 2003 11:58 am 
 View user's profile Send private message

George J
Board Master


Joined: 16 Feb 2002
Posts: 375
Location: Edinburgh, Bonnie Scotland
Flag: Scotland

Status: Offline

 Reply with quote  

Re: Calculating LIFO Returns on Investments

Check the values in the cost compnents columns

If they are the formulas in your example - we have a problem.
If they are numerical values - this is what the macro has done.

In your example you sold all shares so that there were none left - try changing 771 to 770 to leave 1 share left in this portfolio.

if you are running the entire spreadsheet from scratch, select the last used cell in column F as this should still have the formula and copy this upwards

I think the reason you did not see any change was that the macro agreed with the values from your calculations. - Try blanking out the cost component values if running from scratch.

Each time you sell, you will have to copy up formula in column F and run macro - will see what i can do about automating this part.
_________________
George J

Post Mon Sep 29, 2003 12:09 pm 
 View user's profile Send private message Send e-mail

The Grabber
Board Regular


Joined: 20 Sep 2003
Posts: 21
Location: Texas
Flag: Usa

Status: Offline

 Reply with quote  

Re: Calculating LIFO Returns on Investments

Hi again George:

Well I got rid of all of the transactions except the initial buy and the first sell. I deleted the entries and formulae in rows 5 on up, and then ran the macro.

Got a run time error '13'
type mismatch.

Was I supposed to be in a certain cell before I begin?

Thanks and...
_________________
Regards, Steve

Post Mon Sep 29, 2003 12:23 pm 
 View user's profile Send private message

George J
Board Master


Joined: 16 Feb 2002
Posts: 375
Location: Edinburgh, Bonnie Scotland
Flag: Scotland

Status: Offline

 Reply with quote  

Re: Calculating LIFO Returns on Investments

sorry Steve but this is starting to confuse me again - can you give a snapshot of what you just said?
_________________
George J

Post Mon Sep 29, 2003 12:34 pm 
 View user's profile Send private message Send e-mail

The Grabber
Board Regular


Joined: 20 Sep 2003
Posts: 21
Location: Texas
Flag: Usa

Status: Offline

 Reply with quote  

Re: Calculating LIFO Returns on Investments

Microsoft Excel - Lifo with macro.xls___Running: xl2002 XP : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
=

A
B
C
D
E
F
G
H
I
1
LIFO Calcs        
2
DateShares bgt/(sld)Price per Share$ ValueLIFO Cost $ SoldShares LeftCost Components
3
6/1/03500  $     5.75  $ 2,875.00  $         -   0   
4
6/15/03(185) $     6.40  $(1,184.00) $         -   0   
Sheet1 

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

_________________
Regards, Steve

Post Mon Sep 29, 2003 12:37 pm 
 View user's profile Send private message

George J
Board Master


Joined: 16 Feb 2002
Posts: 375
Location: Edinburgh, Bonnie Scotland
Flag: Scotland

Status: Offline

 Reply with quote  

Re: Calculating LIFO Returns on Investments

At any time before running the macro, Column F must have the formula in it.

This is telling the program how many shares are available for sale, so if you are selling 186 but have no shares, it is tring to go up to the next level which is "Shares Left" while it is looking for a number.

If you get that message, basically you are selling more shares than you have bought.
_________________
George J

Post Mon Sep 29, 2003 12:42 pm 
 View user's profile Send private message Send e-mail

George J
Board Master


Joined: 16 Feb 2002
Posts: 375
Location: Edinburgh, Bonnie Scotland
Flag: Scotland

Status: Offline

 Reply with quote  

Re: Calculating LIFO Returns on Investments

Just noticed F4 has the formula F3 doesn't - that may be the problem.
_________________
George J

Post Mon Sep 29, 2003 12:45 pm 
 View user's profile Send private message Send e-mail

The Grabber
Board Regular


Joined: 20 Sep 2003
Posts: 21
Location: Texas
Flag: Usa

Status: Offline

 Reply with quote  

Re: Calculating LIFO Returns on Investments

OK that worked, but I suppose I don't need to run the macro on successive Buys.

When I did that, it ran, but changed the shares left from the first Buy.

Then I entered the second Sell, and copied the formula as directed.

I got the run time error again.

I think the macro doesn't account for additional Buys?

I gotta take the Son to school, and get to work, but I'll check back later.

Thanks again. I think we're close.
_________________
Regards, Steve

Post Mon Sep 29, 2003 12:50 pm 
 View user's profile Send private message

George J
Board Master


Joined: 16 Feb 2002
Posts: 375
Location: Edinburgh, Bonnie Scotland
Flag: Scotland

Status: Offline

 Reply with quote  

Re: Calculating LIFO Returns on Investments

Problem solved???? icon_pray.gif

Right click on the sheet with the data - view code

Copy and paste this macro

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 2 Or Target.Column = 3 Then
If Range("b65536").End(xlUp).Row = Range("C65536").End(xlUp).Row Then
If Range("b65536").End(xlUp) < 0 And Range("C65536").End(xlUp) > 0 Then
call Update
call LIFO
End If
End If
End If

End Sub


This will check each time a cell is changed if the change was in column B or C and given that B is negative and C is positive will reset column f and run the main macro

The macro to reset column F is here

Sub Update()
Dim Frmla As Range
Dim i As Range
Dim x As String

Set Frmla = Range(Range("f3"), Range("f3").End(xlDown))

Range("F3").Formula = "=IF(b3<0,0,IF(b3>=0,b3))"
Range("F3").Select
Selection.AutoFill Destination:=Frmla, Type:=xlFillDefault

End Sub

Put this in the same module as the LIFO macro - just copy and paste it below

There will be limitations on your spreadsheet now - columns B C and F cannot have any notes or other calculations placed below your data.

If you are making the spreadsheet for a new stock, column f will need the formula initially in column F to show that that row is to be used in the calculations (some other form of data may be ok eg "1" but might not work)

The Time for me right now is 16:57 so when you get a chance to see this, it might be a few hours before i can give a response.
_________________
George J

Post Mon Sep 29, 2003 3:51 pm 
 View user's profile Send private message Send e-mail

George J
Board Master


Joined: 16 Feb 2002
Posts: 375
Location: Edinburgh, Bonnie Scotland
Flag: Scotland

Status: Offline

 Reply with quote  

Re: Calculating LIFO Returns on Investments

oops - slight change to update macro - should be

Sub Update()
Dim Frmla As Range
Dim i As Range
Dim x As String

Set Frmla = Range(Range("f3"), Cells(Range("b3").End(xlDown).Row, 6))

Range("F3").Formula = "=IF(b3<0,0,IF(b3>=0,b3))"
Range("F3").Select
Selection.AutoFill Destination:=Frmla, Type:=xlFillDefault

End Sub

and you can put anything you want at the bottom of column f (maybe a total), but B and C are only for the share dealings

I noticed that after deleting some sells and puting them as a lump sell, the old cost component values stayed so you may want to delete all the cost components to ensure a correct result, but if you don't make any input mistakes, this is not necessary.
_________________
George J

Post Mon Sep 29, 2003 7:51 pm 
 View user's profile Send private message Send e-mail
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.