formula percentage problem

[thinkpic]

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 th
need for something way outside my ability and I was hoping someon
might be able to help.

I am developing a spreadsheet to monitor share trading. I want one o
the fields to represent the current imputed per annum percentage gai
or loss of a trade. In other words I have bought but not yet sold th
shares but I want to know what itheir p.a. percentage increase o
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 t
solve:

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

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

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

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

 

[Gary''s Student]

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%

 

[Cutter]

Maybe like this: (assuming your data is in row 2)

=((C2-B2)/B2)/((TODAY()-A2)/365)

and format the cell containing this formula as percentage.

 

[joeu2004]

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.

 

[thinkpic]

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