If, then formula problem?

[PeetsDrinker]

I’m having a hard time with what I think may have a simple solution; however, I’m not sure if I’m using the right formula(s) for what I’m trying to accomplish.

I have the following columns:
C: Month (Dec 2007 – Dec 2008)
D: Month-end closing price
E: Change in price
F: Decision to hold “H” or sell “S”
G: Decision to hold “H” or buy “B”
H: Performance of Buy & Hold portfolio
I: Performance of tactical trading portfolio

My main formula is in column “I”. I am trying to determine how the value of a portfolio will change if an investor sells his holdings when the change in price (column E) declines by a given percentage and buy back into the stock when it increases by a given percentage. Columns “F” & “G” make those determinations. For example, the investor would sell the stock if it declines by 4 percent, sit on cash (annual cash rate is in $I$9), and go back into the market when it increases by 2 percent. I think I’ve figured out how to sell and sit on cash, but I can’t get the formula to buy back again after a percentage increase.

Here are the formulas I’m using for each column:
C: no formula, just names of the month
D: random month-end prices
E: =D22/D21-1
F: =IF(E22<-0.04,"S","H")
G: =IF($E22>=0.02,"B","H")
H: =H21*(1+E22)
I: =IF(AND(F21="S",G22="H"),I21*(1+$I$9/12),IF(AND(F21="H",G22="H"),I21*(1+$I$9/12),IF(AND(F21="H",G22="B"),I21*(1+E22),I21*(1+E22))))


Any advice????


Thanks,

 

[nbrcrunch]

not being in investment, many of us probably would have no clue whether or not your formula is good, bad or indifferent.

What are the underlying principles involved in this type of calculation? If you also are not sure, that would be the best place to start. Hopefully you have some resource, if not in the financial world, then maybe a school teacher specializing in financial analysis that can tell you what to consider. Thereafter, you can try formulating an English psuedo-math statement that can be converted to an Excel formula. It sounds like you need to first "wrap your head around" the concept and thereafter you should be home free. You may or may not even realize you're missing certain critical elements of the calculation--I really couldn't tell you.

 

[PeetsDrinker]

Thanks for your comments nbrcrunch. I guess I thought my question was pretty straightforward, but apparently it's not. I know exactly what I want to do, but am not explaining it clearly. I may have also given too much info and confused people.

 

[nbrcrunch]

my apology. I misunderstood what you were saying. What is the problem with the formula you are using now? What isn't it doing?

 

[PeetsDrinker]

ok, I think I figured out what I’m having difficulty with and how to ask it in a non-financial way.
I’m working with these 4 cells:
A30 (value is either “Y” or “N”)
B30 (value is either “H” or “S”)
C30 (value is either “H” or “B”)
D30

I want to put a logical statement in D30 that states:
if B30 and C30 both = “H”, then D30 = A30
if B30 = “S” and C30 = “H”, then D30 = “N”
if B30 = “H” and C30 = “B”, then D30 = “Y”

Is it possible to put all these conditions into one cell??

Thanks

 

[nbrcrunch]

=if(and(b30="H",c30="H"),a30,if(and(b30="S",c30="H"),"n",if(and(b30="H",c30="B"),"Y")))

 

[PeetsDrinker]

great, that worked. Thanks a lot.