Calculating Yield in VBA

[JohnAndersen]

All,

I am importing raw trade data into excel where I then need to run yield calculations. This is where I am getting hung up. Although there is a yield function in the worksheet there doesn't appear to be anything equivalent when I try to write it up in VBA. The Analysis Toolpak for VBA is loaded and everything but it still won't work.

Does anyone know anything about this? Has anyone done tried to calculate anything similar where they've gotten around this?

 

[JohnAndersen]

Seems it's missing:

http://www.pcreview.co.uk/forums/yield-vba-function-missing-excel-2007-a-t3763031.html

Good find, thanks! Frustrating but I guess I'll have to go back to 2003 for this one.

 

[Richard Schollar]

You can *probably* still use evaluate via VBA to calculate utilising the actual worksheet function Yield:

Assume

settlement=A1
maturity=A2
rate=A3
pr=A4
redemption=A5
frequency=A6

MsgBox Evaulate("Yield(A1,A2,A3,A4,A5,A6)")

I haven't tested this though (don't have anything higher than 2002 at work).