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

 

[dallash]

You Are AWESOME! Thank you so much!!

 

[dallash]

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)

Hi Etaf,

I added everything in, but I forgot about the currency exchange. I thought I could just add extra columns for the exchange but it did not work as expected. Here is what I entered, would you please tell me the smarter way to do it?

Feg Investment - Dad.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Investment	CAD Initial Loan	USD Initial Loan	Initial Shares Price	No. Shares	Current Price	Current Value	Return	DAD	Dallas	Dad	Dallas
2	InVest 1	$ 3,000.00	$ 2,427.68	$ 0.00000000748364737361626	324397941110.75	$ 0.00	2356.374741	-71.30505944	$ 4,046.13	-$ 118.84	$ 4,895.82	-$ 143.80
3	InVest 2	$ 2,000.00	$ 1,618.45	$ 0.00000000748364737361626	2.16265E+11	$ 0.00	1570.916494	-47.53670629
4	InVest 3				0		0	0
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
12
13
14
Sheet2

Cell Formulas
Range	Formula
G2:G11	G2	=E2*F2
H2:H11	H2	=G2-C2
I2	I2	=IF(SUM(G2:G10)<=SUM(C2:C10),SUM(C2:C10),(SUM(C2:C10)+(SUM(H2:H10)/2)))
J2	J2	= IF( SUM(G2:G10)<=SUM(C2:C10), SUM(H2:H10), SUM(H2:H10)/2 )
K2:L2	K2	=I2*1.21
C2:C3	C2	=B2*0.8092266
E2:E11	E2	=IF(C2=0,0,C2/D2)

 

[etaf]

do you want to add in an exchange rate column as over time the exchange rate will change
and where/When does the exchange rate apply ?

You have a loan with an FX rate
Then all worked out in USD
do you then work the returns for DAD & You Back into CAD
Bearing in mind the FX rate will be different , and different over time

Can get complicated with the prevailing FX Rate
so you may need to review when the Return is realised on each portfolio and the exchange rate prevailing for that time

I have just added some FX Rate columns as an idea

Dropbox - File Deleted - Simplify your life

www.dropbox.com

Share-ETAF.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Investment	CAD Initial Loan	USD FX Rate	USD Initial Loan	Initial Shares Price	No. Shares	Current Price	Current Value	Return USD	CAD FX Rate	Return CAD	DAD USD	Dallas USD	FX Rate	Dad CAD	Dallas CAD
2	InVest 1	$ 3,000.00	0.8092266	$ 2,427.68	$ 0.0000000074836	324397941110.75	$ 0.0000000072638	$ 2,356.37	($71.31)	1.235747812	($88.12)	4046.133	-118.8417657	1.235747812	5000	-146.8584519
3	InVest 2	$ 2,000.00	0.8092266	$ 1,618.45	$ 0.0000000074836	216265294073.84	$ 0.0000000072638	$ 1,570.92	($47.54)	1.235747812	($58.74)
4	InVest 3			$ -		0.00		$ -	$0.00		$0.00
5	InVest 4			$ -		0.00		$ -	$0.00		$0.00
6	InVest 5			$ -		0.00		$ -	$0.00		$0.00
7	InVest 6			$ -		0.00		$ -	$0.00		$0.00
8	InVest 7			$ -		0.00		$ -	$0.00		$0.00
9	InVest 8			$ -		0.00		$ -	$0.00		$0.00
10	InVest 9			$ -		0.00		$ -	$0.00		$0.00
11	InVest 10			$ -		0.00		$ -	$0.00		$0.00
Sheet1

Cell Formulas
Range	Formula
K2:K11,D2:D11,H2:H11	H2	=F2*G2
I2:I11	I2	=H2-D2
J2:J3	J2	=1/C2
L2	L2	=IF(SUM(H2:H10)<=SUM(D2:D10),SUM(D2:D10),(SUM(D2:D10)+(SUM(I2:I10)/2)))
M2	M2	= IF( SUM(H2:H10)<=SUM(D2:D10), SUM(I2:I10), SUM(I2:I10)/2 )
N2	N2	=1/C2
O2	O2	=L2*N2
P2	P2	=M2*N2
F2:F11	F2	=IF(D2=0,0,D2/E2)

 

[dallash]

Thank you again Etaf, that was awesome! I really appreciate your time and assistance.

Warm regards,

Dallas

 

[etaf]

you are welcome