Help with a macro....

[rchoi1203]

okay, i'm not really well versed in vba and have been trying to figure this out for a while. any help would be greatly appreciated

what i am trying to do:

-i have certain values let's say from D2:P2 (in a row), but, not ALL the cells will be filled. (i.e. D2=2, E2=4, F2=6, and the rest are blank or can be filled in depending on the amount of payments made)

-dependent on how many of the cells have been filled in, there needs to be a specific formula (present value formula), (i.e. if there are only three cells that are filled, then, the formula would be = .5*e^((D2/12)*T)+.5*e^((E2/12)*T)+.5*e^((F2/12)*T), but if four cells were filled then it would add that one in)

-then, it has to return the result of that formula into a certain cell, say A2.

i'm sure there's a way to do this, but i'm not well versed in vba, so, i'm not sure how to get this done (i'm stuck on trying to see if cell.value=true or something?) i'm not sure.. any help would be appreciated... thanks.

 

[Fat Cat]

Perhaps something along these lines, read across the row for any values, when you find one do the calc on the cell contents and add the result to whatever is already in the myData variable.

Dim myData as variant
Dim myRange as Range

Set myRange = Range("D2:P2")

For Each c in myRange
  If c.Value >0 Then      
      myData = myData + (0.5 * e^((c.Value /12)*T) )      
  End If
Next c

Range("A2").Value = myData

 

[rchoi1203]

hey, i just tried out the macro, but it doesn't seem to be working. or well, it works, but doesn't seem to return the results to the cell value that i want. here is what i have:

D2=Time to maturity (in months)
F2=Dividend payment
G2:P2=Dividend payment time periods (in months)
e=2.7182818285904
E2=Present Value of dividends (where the results are to go)

so, the formula would be:

(F2*(2.7182818285904^(-1*(c.value/12)*(D2/12))))

and of course this is to repeat for any other cell past G2 that's been filled in, changing the "G2" in the formula. i've tried the above method and it doesn't seem to work... am i writing the formula wrong in the macro? or, do i need to fill in E2 with a formula?

okay, i made a boneheaded mistake, and now i get an error message for the line with mydata+.... i used the formula above, is that wrong? (and instead of "G2", i put in c.value

 

[rchoi1203]

only need help with the formula now...

okay,
so, i finally decided to come back to this and try to finish this up... i think i've nailed the problem down to using the macro with references to values within my spreadsheet.

so far, i have tested that this much of the formula does return the results that i need:

2.712818285904^(-1*(c.value/12))

but, once i start to incorporate other cell values into it (but, that are variable to the input of the user), it screws up the results, i.e., when i reference (from my last post) values from F2 and D2 into the formula... is there some condition that i must meet first in regards to those cells for the formula to work properly???

also, thanks Fat Cat for your help with this code, it's works great as long as i can get the formula to work properly...

 

[Fat Cat]

Can you post a couple of example cell contents and the results that you would expect to see for each cell and I'll have a look at the formula for you. In my example I didn't know what T was so left it just as T

Dim myData As Variant
Dim myRange As Range
Dim T As Variant
Dim Div As Variant
Dim e As Variant
Dim curVal As Variant

Set myRange = Range("G2:P2")

T = Range("D2").Value / 12
Div = Range("F2").Value
e = 2.7182818285904

For Each c In myRange
  If c.Value > 0 Then
      myData = myData + 0.5 * e ^ ((c.Value / 12) * T)
  End If
Next c

Range("E2").Value = myData

 

[rchoi1203]

fat cat,
here is the data that i need to make work and the answer is 0.974153179

the T= a time period which is the cells of G2:IV2 (where we're using the c.value for) and it's in months, so it has to be divided by 12.

i need to use the constant "e"=2.7182818285904

the constant in front is .5

and the interest rate is 9% or .09 (in my spreadsheet i have it locked in as a percentage under cell format, so i just type in 9, maybe this might be a problem?)

so, let's say that the cells are:
B2=9% (interest rate)
F2=.5 (dividend payment)
and G2:IV2 are different time periods of the payment.

so, the formula would be (theoretically, and it's utterly failed for me so far):

mydata=mydata+ (F2*(2.7182818285904^(-1*(c.value/12)*B2)))

since i'm trying to calculate out present value, but i've only gotten the:

(2.7182818285904^(-1*(c.value/12))) to work, and once i put in B2, it comes back with a 0.00... which seems to me like it might be a problem with the referenced cells? i hope this is clear... and thanks again for your help...

 

[Fat Cat]

Using the following values;

B2 = 0.09
G2 = 0
F2 = 0.5

and the formula;

=F2*EXP(-1*(G2/12)*B2)

I get a value of 0.5 (ie: the constant term in F2)

If G2 = 100 then the answer is 0.236183276

Therefore, can the answer ever be greater than the value in F2

 

[rchoi1203]

fat cat,
omg, you're a genius... thank you so much... that worked like a charm...

and yes, it will never be higher than .5 or whatever else the dividend is because i was trying to find the present value of the dividend, so i have to take out the value over time of the dividend to calculate how much a dividend of .5 in 2 months will be worth today given a certain risk free interest rate...

once again, thank you so much....