MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Spreadsheet Help

Posted by Joseph Anthony on December 17, 2001 1:11 PM

Formula help please!

What I am trying to accomplish is A2 is if starting capital is e.g., $25000 when it doubles to $50,000 in whatever cell then I would reduce or withdraw by 1/2 of original capital ($12,500 in this example) and would continue future calculations from that point or 2*A2- 1/2 of A2.
2*$25000=$50000- 1/2 of original capital =$12,500.
Then future calculations, next cell would calculate from capital of $50,000 -$12,500 = $37,500.

Thank you in advance,

Posted by Juan Pablo G. on December 17, 2001 1:22 PM

I read your earlier post (Same as this one) and still don't understand what you're trying to do. Care to post some examples ? what your data looks like, where is it, etc.

Juan Pablo G.

Posted by Joseph Anthony on December 17, 2001 1:46 PM

I have set up a spreadsheet that starts with a beginning capital amount,e.g.,
A1 Starting Capital, A2 $25000
B1 % gain, B2 1.01%
C1 price of shares, C2 $46.00
D1 # of shares A2/C2, D2 543
E1 new shares D2*B2 E2 549
F1 Balance Forward A2*B2 F2 $25,250
G1 % Gain =(F2-25000)/25000 G2 1.00%

Now I want to add a formula that specifies when A2 is doubled 2*A2 1/2 of A2 is withdrawn and remaining balance ($37,500) is used for balance of spreadsheet calculations.

Posted by Tom Dickinson on December 17, 2001 9:35 PM

I agree with Juan, this is confusing. First off, I believe you mean 101%, not 1.01%. You may want to enter it as 1% and in cell E2 use the formula D2/B2+D2, or (1+B2)*D2. Similarly for E2.

Is G2 supposed to be 25,000, or the value that is in A2. If there are to be future rows, do you want the gain to be based on the value in column A for that particular row, or always compare to the value in cell A2?

As for your question about doubling A2, will that be in cell A2, or is it further down (i.e., more rows will follow)? If it is in A2, why bother with a formula? If not, is it a manual entry into the next row in column A, or is it the value from column F of the previous row?

At the moment, my last question is: how many times do you want $12500 removed from column A?

Posted by JosephAnthony on December 18, 2001 8:42 AM

Everytime original capital,in this case $25000 doubles I want to withdraw one half.

Posted by Tom Dickinson on December 18, 2001 10:55 AM

Need some answers

CAN'T HELP YOU IF YOU DON'T ANSWER ALL THE QUESTIONS. (see below or my previous message)

Posted by JOSEPH ANTHONY on December 18, 2001 12:21 PM

Re: Need some answers

G2 is the cumulaive percentage gain it equals the original investment + 1% and then just shows the compunded percentge change. It is not relevant with regard to the dilemma. It is only a calculated notation.

Thee are more cells to follow.The amount depends on what you are looking for 52 entries = 52 trades or 52 weeks ---220 entries = 220 days or 220 trades. A2=$25000 A3=$25000 + 1% A4 =$25250 + 1% etc.

Posted by Tom Dickinson on December 18, 2001 1:58 PM

Ok, try this

Cell A3 is the amount in A2, increased by the percent gain listed in B2.
Cell A4 is the amount in A3, increased by the percent gain listed in B3.
Once the amount in a cell equals double the amount in A2, subtract half of the amount from A2 in the current cell.
Use this formula in A3 if B2 is listed as > 100%:
=A2 * B2 - If(A2 * B2 > A$2, A$2 / 2, 0)
Otherwise use this formula:
=A2 * (1 + B2) - If(A2 * (1 + B2) > A$2, A$2 / 2, 0)
Copy the formula down as many rows as you need.

Column G is the cumulative on the investment (which gets deleted now and then when it makes double the original) I would try the following:
=(F2 - A$2) / A$2
By referencing A2 instead of listing 25,000, you can change the original amount without having to change the formula. Also, by using the dollar signs in the formula, you keep the reference (in this case row 2), even when the formula is copied down several rows.

Hope this helps

Posted by JOSEPH ANTHONY on December 18, 2001 5:01 PM

Re: Ok, try this

Thank you for all of your help,patience and persistence.