facing formula 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/t2274919

Assuming your data is in row 2 try this formula in Cell D2:
=((C2-B2)/B2)/((TODAY()-A2)/356)

Hope this helps
Rowan

PS you would be better off posting this type of question in
microsoft.public.excel.worksheet.functions

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?
>
>