Simple formula maybe over thinking.

[dallash]

I am trying to build a Spreadsheet to track an investment between my father and I. The investment is in stock so I want to be able to just enter the current stock prices and have it calculate the rest. Basically he has given me $5000 to start which is a 0% interest loan, but it is the first money out. Everything over $5000 gets split 50/50. I know that this is not complicated but for the life of me I can't wrap my mind around structuring it. I want to leave room for further investments, maybe that where I am over thinking it. Does anyone have any suggestions?

Thank you,

Dallas

 

[etaf]

whats the results from the stock look like and what are you entering ?

5000
Stock Quantity
So if the stock was 500 each then you would have 10 shares

A2 = 5000 in one cell
B2 = Number of Shares
C2 = Share price
D2 =C2 * B2. (Current Value)
E2 = D2 - 5000 (Total Return)
F2 = E2 / 2. ( Return Each )

 

[dallash]

Hi Etaf,

I really think I was over thinking it. I started by creating a transaction ledger but it really doesn't require that, this is more than enough. Thank you so much.

 

[etaf]

you are welcome
you could just add new stock in rows 3,4,etc

And in say CELL A1 or say G1
you could just enter SUM(E2:E100) , which shows the value from all investments and the Return for EACH of you

 

[dallash]

Hi Etaf,

I really think I was over thinking it. I started by creating a transaction ledger but it really doesn't require that, this is more than enough. Thank you so much.

Hi Etaf,

So the way I have it now, It should show $0 for me and $5000 for my dad. Instead it is -$2500 of me and $2500 for him. Here is what I ended up using for him & I

Him =5000+(G4-5000)/2

Me =(G4-5000)/2

I would also like to show if the investment dips below $5000, his would remain, but I would go negative. Is there an easy way to do that?

 

[dallash]

you are welcome
you could just add new stock in rows 3,4,etc

And in say CELL A1 or say G1
you could just enter SUM(E2:E100) , which shows the value from all investments and the Return for EACH of you

Hi Etaf,

So the way I have it now, It should show $0 for me and $5000 for my dad. Instead it is -$2500 of me and $2500 for him. Here is what I ended up using for him & I

Him =5000+(G4-5000)/2

Me =(G4-5000)/2

I would also like to show if the investment dips below $5000, his would remain, but I would go negative. Is there an easy way to do that?

 

[etaf]

not 100% sure i follow exactly

5000
If PLUS return , then thats 5000 / 2 + return /2 each
SO that would show for each person
2500 + whatever return

BUT if the return is negative then you want your share to show zero
and for your Dad to instead of showing
2500 + whatever return
you want it to show
5000 minus the FULL negative Return

for example
lets say it increases by 100
then will show
DAD = 2500 + 50 = 2550
YOU = 2500 + 50 = 2550
for each person

If it goes down by 100 - so total now 5000 - 100 = 4900
you want it to show
DAD = 5000 - 100 = 4900
YOU = 0

is that correct ?

I suspect and IF() will do that

 

[dallash]

not 100% sure i follow exactly

5000
If PLUS return , then thats 5000 / 2 + return /2 each
SO that would show for each person
2500 + whatever return

BUT if the return is negative then you want your share to show zero
and for your Dad to instead of showing
2500 + whatever return
you want it to show
5000 minus the FULL negative Return

for example
lets say it increases by 100
then will show
DAD = 2500 + 50 = 2550
YOU = 2500 + 50 = 2550
for each person

If it goes down by 100 - so total now 5000 - 100 = 4900
you want it to show
DAD = 5000 - 100 = 4900
YOU = 0

is that correct ?

I suspect and IF() will do that

Its actually reverse of that. He gets the $5000 no matter what so if I lose money, its negative for me but a minimum of $5000 for him.

So to use the same example

lets say it increases by 100
then will show
DAD = 5000 + 50 = 5050
YOU = 50 = 50
for each person

If it goes down by 100 - so total now 5000 - 100 = 4900
you want it to show
DAD = 5000
YOU = -$100

Does that make sense?

 

[etaf]

A2 = 5000 in one cell
B2 = Number of Shares
C2 = Share price
D2 =C2 * B2. (Current Value) - 6000
E2 = D2 - A2 (Total Return) = -1000
F2 = YOU = IF( D2 <= A2 , E2 , E2/2 )
G2 = DAD = IF ( D2 <= A2 , A2 , (A2 + (E2/2) ) )

Heres a sample sheet - USING XL2BB Add-in
So you can copy into a spreadsheet

Just need an idea of how other investments would work , if this does what you need

I'll also add to dropbox, but only for a short while

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Book1
	A	B	C	D	E	F	G
1	Initial Loan	No. Shares	Current Price	Current Value	Return	DAD	YOU
2	5000	1000	5	5000	0	5000	0
3	5000	1000	10	10000	5000	7500	2500
4	5000	1000	5.5	5500	500	5250	250
5	5000	1000	5.1	5100	100	5050	50
6	5000	1000	4.9	4900	-100	5000	-100
7	5000	1000	1	1000	-4000	5000	-4000
Sheet2

Cell Formulas
Range	Formula
D2:D7	D2	=B2*C2
E2:E7	E2	=D2-A2
F2:F7	F2	=IF(D2<=A2,A2,(A2+(E2/2)))
G2:G7	G2	= IF( D2 <= A2, E2, E2/2 )

 

[etaf]

Ok, had a play with the spreadsheet, so now - just the ONE cell for DAD & YOU
and works out multiple investments
Sheet2 example will work out upto 10 investments

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Investment - ETAF.xlsx
	A	B	C	D	E	F	G	H	I
1	Investment	Initial Loan	Initial Shares Price	No. Shares	Current Price	Current Value	Return	DAD	YOU
2	InVest 1	5000	100	50	101	5050	50	8000	-130
3	InVest 2	2000	1	2000	1.01	2020	20
4	InVest 3	1000	10	100	8	800	-200
5	InVest 4			0		0	0
6	InVest 5			0		0	0
7	InVest 6			0		0	0
8	InVest 7			0		0	0
9	InVest 8			0		0	0
10	InVest 9			0		0	0
11	InVest 10			0		0	0
Sheet2

Cell Formulas
Range	Formula
F2:F11	F2	=D2*E2
G2:G11	G2	=F2-B2
H2	H2	=IF(SUM(F2:F10)<=SUM(B2:B10),SUM(B2:B10),(SUM(B2:B10)+(SUM(G2:G10)/2)))
I2	I2	= IF( SUM(F2:F10)<=SUM(B2:B10), SUM(G2:G10), SUM(G2:G10)/2 )
D2:D11	D2	=IF(B2=0,0,B2/C2)