Thread: formula percentage problem

Hi everyone. This is my first post. I am a relatively new user of excel.
I am able to devise simple formula {eg. =sum(A1+B1)] but I now have the
need for something way outside my ability and I was hoping someone
might be able to help.

I am developing a spreadsheet to monitor share trading. I want one of
the fields to represent the current imputed per annum percentage gain
or loss of a trade. In other words I have bought but not yet sold the
shares but I want to know what itheir p.a. percentage increase or
decrease would be if I sold today at their current price.

In simple terms here is an example of the problem I want a formula to
solve:

I buy a stock for $200 on Sept 1. On Oct1 it is worth $210. Obviously
it has gone up by 5% over 30 days but what would its current per annum
percentage rise be. The answere would be 5 divided by 30/365ths or
aprox 60%.

This is even getting complicated for my math but I have no idea how to
express this problem as a formula - particularly when it comes to
calculating the number of days. I assume that the computer clock/date
can be used in the equation but I have no idea how. Even when I
calculate the day/year fraction manually I have no idea how to write
the formula to calculate the percentage.

The only fields in the spreadsheet that I am hoping to use are: Column
A = Purchase date; B = purchase price; C = current price. (Hopefully
the computer knows the current date).

Can this be done?If so could anyone please please please give me the
formula?


--
thinkpic
------------------------------------------------------------------------
thinkpic's Profile: http://www.hightechtalks.com/m175
View this thread: http://www.hightechtalks.com/t2274784

First the concept:

You have two dates and the values at these dates. BY subtracting the dates
and the values you can calculate the average amount of gain EACH DAY.
Multiply by the number of days in a year to get the gain in a year and then
figure the percentage.

Starting in A1:

9/1/2005 200
10/1/2005 210

subtracting (and making sure A3 is formatted as General) A3 and B3:

30 10 thirty day and 10 dollars

In B4 put =B3/A3 and you'll see .3333333333333
This is the increase in a single day
In B5 put =B4*356 and you'll see 121.66666666
This is the increase in a year
In B6 put =B5/B1 format as percentage and you'll see 60.83%


--
Gary's Student


"thinkpic" wrote:

>
> Hi everyone. This is my first post. I am a relatively new user of excel.
> I am able to devise simple formula {eg. =sum(A1+B1)] but I now have the
> need for something way outside my ability and I was hoping someone
> might be able to help.
>
> I am developing a spreadsheet to monitor share trading. I want one of
> the fields to represent the current imputed per annum percentage gain
> or loss of a trade. In other words I have bought but not yet sold the
> shares but I want to know what itheir p.a. percentage increase or
> decrease would be if I sold today at their current price.
>
> In simple terms here is an example of the problem I want a formula to
> solve:
>
> I buy a stock for $200 on Sept 1. On Oct1 it is worth $210. Obviously
> it has gone up by 5% over 30 days but what would its current per annum
> percentage rise be. The answere would be 5 divided by 30/365ths or
> aprox 60%.
>
> This is even getting complicated for my math but I have no idea how to
> express this problem as a formula - particularly when it comes to
> calculating the number of days. I assume that the computer clock/date
> can be used in the equation but I have no idea how. Even when I
> calculate the day/year fraction manually I have no idea how to write
> the formula to calculate the percentage.
>
> The only fields in the spreadsheet that I am hoping to use are: Column
> A = Purchase date; B = purchase price; C = current price. (Hopefully
> the computer knows the current date).
>
> Can this be done?If so could anyone please please please give me the
> formula?
>
>
> --
> thinkpic
> ------------------------------------------------------------------------
> thinkpic's Profile: http://www.hightechtalks.com/m175
> View this thread: http://www.hightechtalks.com/t2274784
>
>

thinkpic wrote:
> I buy a stock for $200 on Sept 1. On Oct1 it is worth
> $210. Obviously it has gone up by 5% over 30 days but
> what would its current per annum percentage rise be.
> The answere would be 5 divided by 30/365ths or aprox 60%.

That might not be the correct way to annualize stock
returns. Nonetheless, I can show you how to express
the formula that you have in mind.

> This is even getting complicated for my math but I
> have no idea how to express this problem as a formula
> [....]
> The only fields in the spreadsheet that I am hoping to
> use are: Column A = Purchase date; B = purchase price;
> C = current price. (Hopefully the computer knows the
> current date). Can this be done?

To answer your last question: yes, the TODAY() function
yields the current date.

However, I suggest that you put the date associated with
the "current" price into column D (e.g). Since you are
putting the "current" price into a cell -- not acquiring
it dynamically -- the computed growth rate will be
misleading if you use TODAY() and look at the spreadsheet
on another day.

Then the formula that you express above would be:

=(C1/B1 - 1)*(365/(D1-A1))

That yields 60.83% for your example, as you computed
manually.

That annualizes the amount of change. Some people might
argue that you should annualize the growth rate. The
formula for that is:

=(C1/B1)^(365/(D1-A1)) - 1

That yields 81.05% for your example.

Arguably, there are reasons why neither is the correct
answer.

1. Should you use the number trade days instead of the
number of calendar days?

2. Should you use the "square root of time" rule instead
of either a geometric or linear proportional change?

3. Is it misleading to annualize growth rates of smaller
time periods of a stochastic process?

I believe you will find champions of arguments on both
sides of each of those questions. I suggest that you
do a google search to decide which side you want to take.

Thanks for being so helpful! These communities are just great. I tried
what has been suggested and it worked. Again thanks (I will look into
the various philosophies but at the moment I am happy with this.


--
thinkpic
------------------------------------------------------------------------
thinkpic's Profile: http://www.hightechtalks.com/m175
View this thread: http://www.hightechtalks.com/t2274784

First the concept:

You have two dates and the values at these dates. BY subtracting the dates
and the values you can calculate the average amount of gain EACH DAY.
Multiply by the number of days in a year to get the gain in a year and then
figure the percentage.

Starting in A1:

9/1/2005 200
10/1/2005 210

subtracting (and making sure A3 is formatted as General) A3 and B3:

30 10 thirty day and 10 dollars

In B4 put =B3/A3 and you'll see .3333333333333
This is the increase in a single day
In B5 put =B4*356 and you'll see 121.66666666
This is the increase in a year
In B6 put =B5/B1 format as percentage and you'll see 60.83%


--
Gary's Student


"thinkpic" wrote:

>
> Hi everyone. This is my first post. I am a relatively new user of excel.
> I am able to devise simple formula {eg. =sum(A1+B1)] but I now have the
> need for something way outside my ability and I was hoping someone
> might be able to help.
>
> I am developing a spreadsheet to monitor share trading. I want one of
> the fields to represent the current imputed per annum percentage gain
> or loss of a trade. In other words I have bought but not yet sold the
> shares but I want to know what itheir p.a. percentage increase or
> decrease would be if I sold today at their current price.
>
> In simple terms here is an example of the problem I want a formula to
> solve:
>
> I buy a stock for $200 on Sept 1. On Oct1 it is worth $210. Obviously
> it has gone up by 5% over 30 days but what would its current per annum
> percentage rise be. The answere would be 5 divided by 30/365ths or
> aprox 60%.
>
> This is even getting complicated for my math but I have no idea how to
> express this problem as a formula - particularly when it comes to
> calculating the number of days. I assume that the computer clock/date
> can be used in the equation but I have no idea how. Even when I
> calculate the day/year fraction manually I have no idea how to write
> the formula to calculate the percentage.
>
> The only fields in the spreadsheet that I am hoping to use are: Column
> A = Purchase date; B = purchase price; C = current price. (Hopefully
> the computer knows the current date).
>
> Can this be done?If so could anyone please please please give me the
> formula?
>
>
> --
> thinkpic
> ------------------------------------------------------------------------
> thinkpic's Profile: http://www.hightechtalks.com/m175
> View this thread: http://www.hightechtalks.com/t2274784
>
>

thinkpic wrote:
> I buy a stock for $200 on Sept 1. On Oct1 it is worth
> $210. Obviously it has gone up by 5% over 30 days but
> what would its current per annum percentage rise be.
> The answere would be 5 divided by 30/365ths or aprox 60%.

That might not be the correct way to annualize stock
returns. Nonetheless, I can show you how to express
the formula that you have in mind.

> This is even getting complicated for my math but I
> have no idea how to express this problem as a formula
> [....]
> The only fields in the spreadsheet that I am hoping to
> use are: Column A = Purchase date; B = purchase price;
> C = current price. (Hopefully the computer knows the
> current date). Can this be done?

To answer your last question: yes, the TODAY() function
yields the current date.

However, I suggest that you put the date associated with
the "current" price into column D (e.g). Since you are
putting the "current" price into a cell -- not acquiring
it dynamically -- the computed growth rate will be
misleading if you use TODAY() and look at the spreadsheet
on another day.

Then the formula that you express above would be:

=(C1/B1 - 1)*(365/(D1-A1))

That yields 60.83% for your example, as you computed
manually.

That annualizes the amount of change. Some people might
argue that you should annualize the growth rate. The
formula for that is:

=(C1/B1)^(365/(D1-A1)) - 1

That yields 81.05% for your example.

Arguably, there are reasons why neither is the correct
answer.

1. Should you use the number trade days instead of the
number of calendar days?

2. Should you use the "square root of time" rule instead
of either a geometric or linear proportional change?

3. Is it misleading to annualize growth rates of smaller
time periods of a stochastic process?

I believe you will find champions of arguments on both
sides of each of those questions. I suggest that you
do a google search to decide which side you want to take.

Thanks for being so helpful! These communities are just great. I tried
what has been suggested and it worked. Again thanks (I will look into
the various philosophies but at the moment I am happy with this.


--
thinkpic
------------------------------------------------------------------------
thinkpic's Profile: http://www.hightechtalks.com/m175
View this thread: http://www.hightechtalks.com/t2274784