Fairly Complex IF Statement

Hello,
For a Finance class I have to program a realtively complex if statement, and
have been working on it for quite some time, to no avail. The statement must
state that, IF the total % return on an index goes up by 1.5% or more then
BUY if it goes down by 1.5% or more the SELL. Everything in between you are
to hold. Now, in the event that you are not between a buy and a sell your
money is to be invested at a T-Bill rate of 4%. Any transaction costs will
cost .25%. I have the BUY/SELL function
programmed=IF(C857<=-0.015,"Buy",IF(C857>=0.015,"Sell","")). It now tells me
when to buy or sell. Now I need to figure the Holding period return for when
the money is invested in the T-Bill and when it is invested in the market.
Any pointers? Thanks!

Patrick

Patrick,

Do you have the dates when your cell C857 triggered you to Sell or to Buy.?
What happens when you the index goes up consecutive times up by 1.5% that is
to buy and buy again ? Is there money left for the second buy?
Can you short the market?
Where are the returns of the period when you are at buy mode and then decide
to sell? So your investment really is principal + return?

You will have to provide with more details before a formula can found of how
you data is structured and preferablly with some example.


Otherwise holding period returns are simply your
=Principal * (1-.25%) * (4%* duration you held the T-bill) + any coupon if
any...




"Patrick" <[email protected]> wrote in message
news:[email protected]...
> Hello,
> For a Finance class I have to program a realtively complex if statement,
> and
> have been working on it for quite some time, to no avail. The statement
> must
> state that, IF the total % return on an index goes up by 1.5% or more
> then
> BUY if it goes down by 1.5% or more the SELL. Everything in between you
> are
> to hold. Now, in the event that you are not between a buy and a sell your
> money is to be invested at a T-Bill rate of 4%. Any transaction costs
> will
> cost .25%. I have the BUY/SELL function
> programmed=IF(C857<=-0.015,"Buy",IF(C857>=0.015,"Sell","")). It now tells
> me
> when to buy or sell. Now I need to figure the Holding period return for
> when
> the money is invested in the T-Bill and when it is invested in the market.
> Any pointers? Thanks!
>
> Patrick

Starting Amount $10,000 T-Bill Rate 4%
Date Nasdaq %Chng
5/14/01 2,081.92
5/15/01 2,085.58 0.18%
5/16/01 2,166.44 3.88% Buy
5/17/01 2,193.68 1.26%
5/18/01 2,198.88 0.24%
5/21/01 2,305.59 4.85%
5/22/01 2,313.85 0.36%
5/23/01 2,243.48 -3.04% Sell

For instance, from 5/14 to 5/15 we will hold our $10,000 at the T-Bill Rate
of 4%. When we hit the 16th we buy, and will recieve the return on the
market between 5/16 and 5/23, each transaction costs is .25%. I'm trying to
create an IF function that looks at the BUY/SELL column and says if it has
nothing hold @ 4% if it says BUY you get the Holding Period Return up until
the point where it says SELL. After the SELL you hold it at 4% until a buy
opportunity arises.

I'm doing a 3 year period, and trying to avoid manual calculations of each
period of BUY/SELL or HOLD as there are quite a few of them. Thanks for the
attention!

Patrick


"N Harkawat" wrote:

> Patrick,
>
> Do you have the dates when your cell C857 triggered you to Sell or to Buy.?
> What happens when you the index goes up consecutive times up by 1.5% that is
> to buy and buy again ? Is there money left for the second buy?
> Can you short the market?
> Where are the returns of the period when you are at buy mode and then decide
> to sell? So your investment really is principal + return?
>
> You will have to provide with more details before a formula can found of how
> you data is structured and preferablly with some example.
>
>
> Otherwise holding period returns are simply your
> =Principal * (1-.25%) * (4%* duration you held the T-bill) + any coupon if
> any...
>
>
>
>
> "Patrick" <[email protected]> wrote in message
> news:[email protected]...
> > Hello,
> > For a Finance class I have to program a realtively complex if statement,
> > and
> > have been working on it for quite some time, to no avail. The statement
> > must
> > state that, IF the total % return on an index goes up by 1.5% or more
> > then
> > BUY if it goes down by 1.5% or more the SELL. Everything in between you
> > are
> > to hold. Now, in the event that you are not between a buy and a sell your
> > money is to be invested at a T-Bill rate of 4%. Any transaction costs
> > will
> > cost .25%. I have the BUY/SELL function
> > programmed=IF(C857<=-0.015,"Buy",IF(C857>=0.015,"Sell","")). It now tells
> > me
> > when to buy or sell. Now I need to figure the Holding period return for
> > when
> > the money is invested in the T-Bill and when it is invested in the market.
> > Any pointers? Thanks!
> >
> > Patrick
>
>
>

Patrick
Based on your data I came up with the following :

Date Nasd % change Decision Value
5/14/2001 2,081.92 T 9,975
5/15/2001 2,085.58 0.18% T 9,976
5/16/2001 2,166.44 3.88% B 9,952
5/17/2001 2,193.68 1.26% B 10,078
5/18/2001 2,198.88 0.24% B 10,102
5/21/2001 2,305.59 4.85% B 10,592
5/22/2001 2,313.85 0.36% B 10,630
5/23/2001 2,243.48 -3.04% T 10,281


The decison column is either you are in treasury (T) or in the market (B)
The above data is from cell A1 thru E9,

On cell D2 type = T and on E2 type =10000 *(1-.25%)
Then on cell D3 type
=IF(AND(C3>0.015,D2="T"),"B",IF(AND(C3<-0.015,D2="B"),"T",IF(D2="B","B","T")))

and on cell E3 type
=IF(D2="T",E2*(1+4%/360),E2*(1+C3))*IF(D3<>D2,(1-0.25%),1)

Now copy D3 and E3 all the way down

Please double check the calculations manually to see that is what you get as
at 5/23/01.(10,281)
Also since the decision to buy or sell happens at the end of the day you do
not get that days return. For instance on 5/16 when the market went up by
3.88% you were still invested in T-bill and only after observing that the
market has gone up beyond 2.5% that you decided to sell off T-bills and
invest it in Nasdaq.
Also try and incorporate all the days of the year in the data. For weekends
you can simply put a 0 for the return, this will capture the t-bills
interest for those 2 days

Hope it helps


"Patrick" <[email protected]> wrote in message
news:[email protected]...
> Starting Amount $10,000 T-Bill Rate 4%
> Date Nasdaq %Chng
> 5/14/01 2,081.92
> 5/15/01 2,085.58 0.18%
> 5/16/01 2,166.44 3.88% Buy
> 5/17/01 2,193.68 1.26%
> 5/18/01 2,198.88 0.24%
> 5/21/01 2,305.59 4.85%
> 5/22/01 2,313.85 0.36%
> 5/23/01 2,243.48 -3.04% Sell
>
> For instance, from 5/14 to 5/15 we will hold our $10,000 at the T-Bill
> Rate
> of 4%. When we hit the 16th we buy, and will recieve the return on the
> market between 5/16 and 5/23, each transaction costs is .25%. I'm trying
> to
> create an IF function that looks at the BUY/SELL column and says if it has
> nothing hold @ 4% if it says BUY you get the Holding Period Return up
> until
> the point where it says SELL. After the SELL you hold it at 4% until a
> buy
> opportunity arises.
>
> I'm doing a 3 year period, and trying to avoid manual calculations of each
> period of BUY/SELL or HOLD as there are quite a few of them. Thanks for
> the
> attention!
>
> Patrick
>
>
> "N Harkawat" wrote:
>
>> Patrick,
>>
>> Do you have the dates when your cell C857 triggered you to Sell or to
>> Buy.?
>> What happens when you the index goes up consecutive times up by 1.5% that
>> is
>> to buy and buy again ? Is there money left for the second buy?
>> Can you short the market?
>> Where are the returns of the period when you are at buy mode and then
>> decide
>> to sell? So your investment really is principal + return?
>>
>> You will have to provide with more details before a formula can found of
>> how
>> you data is structured and preferablly with some example.
>>
>>
>> Otherwise holding period returns are simply your
>> =Principal * (1-.25%) * (4%* duration you held the T-bill) + any coupon
>> if
>> any...
>>
>>
>>
>>
>> "Patrick" <[email protected]> wrote in message
>> news:[email protected]...
>> > Hello,
>> > For a Finance class I have to program a realtively complex if
>> > statement,
>> > and
>> > have been working on it for quite some time, to no avail. The
>> > statement
>> > must
>> > state that, IF the total % return on an index goes up by 1.5% or more
>> > then
>> > BUY if it goes down by 1.5% or more the SELL. Everything in between
>> > you
>> > are
>> > to hold. Now, in the event that you are not between a buy and a sell
>> > your
>> > money is to be invested at a T-Bill rate of 4%. Any transaction costs
>> > will
>> > cost .25%. I have the BUY/SELL function
>> > programmed=IF(C857<=-0.015,"Buy",IF(C857>=0.015,"Sell","")). It now
>> > tells
>> > me
>> > when to buy or sell. Now I need to figure the Holding period return
>> > for
>> > when
>> > the money is invested in the T-Bill and when it is invested in the
>> > market.
>> > Any pointers? Thanks!
>> >
>> > Patrick
>>
>>
>>