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" wrote in message
...
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" wrote in message
...
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(C30.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" wrote in message
...
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" wrote in message
...
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