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