PeetsDrinker
New Member
- Joined
- Nov 14, 2007
- Messages
- 25
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,
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,