Bonus yield calculation

[tkraju via OfficeKB.com]

I have a range A2:B7 bonus shares fhistory of a company. I need a function
that calculates automatically how many shares I hold when I put input date in
D2
Col A ........Col B ........Col C........Col D
1. DATE ........BonusRatio..Buydate ......SaleDate
2.07-Jul-04 .....1:1 ...........10-Feb-05.....25-Aug-06
3.03-Mar-05.....1:5 ............. 100 ........... ?
4.19-Jan-06 .....1:1
5 25-Jan-07 ....1:2
6.
7.

C3= number shares bought on 10-Feb-05
C2= Buy date
I want a formula in D3 that calculates how many shares I hold on sale date(D2)
..The formula should calculates number of shares I hold on sale date whenever
I change (input) saledate or buy date or number of shares.
From the above saledate I must get the answer through formula 240.If I
change sale date to 01-Feb-2007 I must get the answer 360.If I change Buydate
to 15-May-2004 I must get the answer 720.The formula should take care of
whole range of Bonus history table(A2:B7),at present A6,A7 are blank.
I am very grateful If any body gives me a compact formula to achieve this.

 

[Guest]

Hi,

And the calculation is based on what exactly???? Where does the date fit in?
Where does the ratio fit in? You need to give more details.


Regards!
Jean-Guy

 

[Guest]

Let me explain in detail.col A date is record date bonus shares annoucement
by the conmpany.As on that date if anybody holds shares of that company they
will be given free shares as per ratio announced by the company.As per my
table,buy date of shares is 10-Feb-05 and the buyer holds 100 shares.His
holding period is from 10-Feb-05 to 25-Aug-06during thisperiod ,the company
announced two times bonus shares.On 03-Mar-05 buyers' holding was 100+20=120
shares as the ratio was 1 free share for every 5 shares held by share holder,
and again on 19-Jan-06 buyer's holding became 120+120=240 shares as the ratio
of bonus was 1 free share for every 1 share held by sahre holder.So,as on
25-Aug-06 he holds total 240 shares and he is not eligilble to get next bonus
shres announced by the company on 25-Jan-2007 as he sold off his shares on
25-Aug-2006.

 

[Guest]

Hi,

Try this in C4:

=IF(A4="","",IF(AND(A4>=$C$2,A4<=$D$2),C3+(C3/ABS(RIGHT(B4,FIND(":",B4)-1))),C3))
where C2 is the buy date, D2 is the sale date, A4 is the bonus date
(07-Jul-04 ), C3 is the number of shares (100) and B4 is the ratio (1:1)

Using that formula I got the following results:

C4 - 100
C5 - 120
C6 - 240
C7 - 240

HTH
Jean-Guy

 

[Guest]

Small revision in case some the ratio might 2:1 or something similar.

=IF(A4="","",IF(AND(A4>=$C$2,A4<=$D$2),C3+(C3/ABS(RIGHT(B4,FIND(":",B4)-1))*(ABS(LEFT(B4,FIND(":",B4)-1)))),C3))
copied down

in D3 use:

=MAX(C3:C7)


Cheers!
Jean-Guy