formula problem

T

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
 
R

Rowan Drummond

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top