Since no one seemed to have tried this circular do loop, I am going to see how it works and let everyone know. Simples and userque, I am happy with and appreciate both your recommendations and will try both. I actually knew how to program, in FORTRAN, years ago in undergraduate classes , but have never done any programming since other than learned VBA recently.
It's OK. Me and Userque just have two different approaches to answer this. His answer is that this is a possibility (which it is according to docs). My answer is that this will be impractical and not fit for purpose. If it doesn't cost too much, it's good to try something new though (and learn from it). I'll be happy to hear of your progress and your experience with this. If viable after all (proving me "wrong"), it could be useful for one offs in the future. I understand your pain of the VBA-formula division in Excel, however, VBA is general purpose, although slow, so you can make your own procedures and libraries if there's something you lack. If this is too complex to do, you may need to seek a trading/analysis platform or get assistance from someone who have the technical knowledge. It's not very important for me to be right or wrong, though I'm saying this with 30+ years of programming experience, it's good to get assumptions tested also (but I'm more willing to chat about it than spend more time in Excel ). (Am actually spending more time in LibreOffice nowadays, as this works under Linux, and works well enough when you learn it's quirks). We won't know for sure until someone has tested the waters!
Thanks, but just to be clear. I only answered the original question--regardless as to possibilities. There was nothing to be right or wrong 'about' at that point. You then chimed in that Excel was not 'meant' to do 'that.' I responded with a simple cite from MS to the contrary. Thousands of words later--here we are; right back where we started. That said; without knowing anything about the OP's project; of course it's possible. Possibility is not the same as something being 'easy' or 'probable' or 'likely' or 'the best course of action' or ... etc. I never addressed those things because they weren't the question. You did address those things, which is fine...expect you made an incorrect statement regarding my post--upon which I offered you correction. Some get offended and defensive and excited when you correct them after they've had 30+ years experience.
userque and Simples, I am using a version of Office for Mac, 2010 edition. So far I am unable to find the "options" to enable circular computation. If I am not getting any traction, I may have to purchase a newer version. In any case, I am not smart enough to write my own function library, just too complex for a senior citizen. My main objective is to find a simple way to do a Monte Carlo on expected returns. I want to use several different distribution functions (i.e., account for fat tail) and see how they affect the outcome. If I can perform a do loop within excel, it makes my life easy.
Thanks for asking. Maybe I am making things way too complicated, but this is what I like to do. Using Black Scholes, or other options pricing models to simulate the behavior of the option price as a function of the various variables. For example in Black Scholes: C = F(S, K, IV, t, r, d) C = call premium F = function, e.g. Black Scholes S = Underlying price K = Strike IV= implied volatility t = time to expiry r = risk free rate d = dividend rate I want to calculate a time series of C, changing the value of S(t), IV(t) as time unfolds, S(t) can be lognormal or historical or fat tail.., IV can be a constant or follow some empirical skew function... I can use a random number generator and the lognormal function to create S(t) as time unfolds. Since C is path dependent, to see how the most likely value of C unfolds as a function of time depends on S(t). For this time series calculation, a simple Excel will do. But I need to run enough cases to get the most likely C(t) and therefore a Monte Carlo. In my younger days using FORTRAN to run Monte Carlo, I typically ran ~100,000 cases. Not practical to do that with Excel without a do loop. I can do it with VBA but I don't know how to create functions for the Black Scholes equation within the VBA subroutine. I thought at first that the most likely C(t) would be to calculate S(t) as if it grows as square root of (IV*t) but could not prove that mathematically, so am resorting to a numerical simulation. Plus, with a numerical simulation, I can input any function for S. Am I making any sense? If not, please set me straight. Thanks again.
Maybe start your own ET thread for this project? I think I understand you. Been awhile since I've watched a PBS documentary explaining the origins of the Black-Scholes equation--a good watch btw. Are you sure you have Office for Mac 2010? And not 2011: "Microsoft Office for Mac 2011 is a version of the Microsoft Office productivity suite for Mac OS X. It is the successor to Microsoft Office 2008 for Mac and is comparable to Microsoft Office 2010 for Windows. Office 2011 was followed by Microsoft Office 2016 for Mac released on September 22, 2015, requiring a Mac with a 64-bit Intel processor and OS X Yosemite or later." https://en.wikipedia.org/wiki/Microsoft_Office_for_Mac_2011 A while back I introduced you to the Excel WORKSHEETFUNCTION. What happened with that? Maybe upload your workbook somewhere where I or others can take a look at it. If you only want me to look at it, PM me the link. You can try to strip any personal or proprietary data from it by replacing it with dummy data...as long at it still 'works' the 'same.' If I can't make sense of it; don't want to make sense of it; or you don't want to upload it; I may attempt to just code a simple version to do what you state above whereby, hopefully, you can learn from and use as a sample to apply to your project.
Yes, it is Microsoft Office for Mac 2011. As for this: No, I don't think it is that interesting a topic. As for this: I did try but could not make it work, so I still use Excel to calculate the function and send it over to VBA to continue the computation and then send it back to Excel... As I said, cumbersome but workable for what I wanted to do. If I cannot make the circular to work I will jus have to use a large array calculation and repeat it multiple times. Thanks. You helped me enough already, shouldn't waste more of your time. You have more important thing to do. Regards,
What if you used an Option Calculator Program as the heart of this program and then create data inputs for the Option Calculator for: C = call premium S = Underlying price K = Strike IV= implied volatility t = time to expiry r = risk free rate d = dividend rate The F = function, e.g. for Black Scholes is already built into the Option Calculator.
I talked to someone familiar with Microsoft Office for Mac 2011. I can now get the circular iteration loop going by: press command and comma simultaneously to get into the options table, then click calculation to get the circular iteration function. Let me try a simple Monte Carlo and let you folks know how it works. Thank you very much for your kindness. Regards,