calculus and spreadsheets?
Alvin M. Foreman
There doesn't appear to be any @function that does this. Do other
spreadsheets have such @functions?
E. J. Billo
Foreman) wrote:
======================
"Derivatives of a Function
The first derivative of a formula in a worksheet cell can be obtained
with a high degree of accuracy by evaluating the formula at x0 and at
x0+Dx. Since Excel carries 15 significant figures, Dx can be made very
small. Under these conditions DF/Dx approximates dF/dx very well. "
--
Joe Billo
bi...@hermes.bc.edu
Alvin M. Foreman
actually implement this? You lost me with the "x0 Dx" stuff... What I want
to use calculus for is to calculate the first and second derivatives of an
option's value with respect to changes in stock price, time, interest rates,
etc.
jrw
>Path: ra.nrl.navy.mil!hookup!newsfeed.internetmci.com!howland.reston.ans.net!math.ohio-state.edu!newsfeed.acns.nwu.edu!news.acns.nwu.edu!lucky126
>From: alv...@nwu.edu (Alvin M. Foreman)
>Newsgroups: comp.apps.spreadsheets
>Subject: calculus and spreadsheets?
>Date: Mon, 01 Apr 96 02:16:33 GMT
>Organization: Northwestern University, Evanston, IL. USA
>Lines: 3
>Message-ID: <4jnea1$ph8...@nuts.nwu.edu>
>NNTP-Posting-Host: lucky126.nuts.nwu.edu
>X-Newsreader: News Xpress Version 1.0 Beta #4
>I have QPW 6.01. Is there a way to use it to calculate calculus derivatives?
> There doesn't appear to be any @function that does this. Do other
>spreadsheets have such @functions?
The derivative at a point can be approximated by taking one half the
difference of the point before and the point after (for equally spaced points)
ie if column A is the function then to put the derivative in col. B for
example, use B2=0.5*(A3-A1),etc if you need the end points, then B1=A2-A1.
The second deriv. can be obtained same way from col B to col C. You may want
to smooth the data first.
Dr. Jeffrey R. Wyatt
Code 6110
Naval Research Laboratory
Washington, DC 20375
(202) 767-3244
(202) 404-8119 FAX
Vandenberg P
: I have QPW 6.01. Is there a way to use it to calculate calculus derivatives?
: spreadsheets have such @functions?
Since you are frequently interested not in the derivative itself but in
what the derivative is used for ie. finding minimums or maximums you can
use the optimization routines in the spreadsheet. Enter the formula in one cell
and have it's "x" refer to a blank cell. Then choose optimization, max
the formula cell by varying the blank cell. Works very well.
And by the way:
There is another answer which is form an Excel user in this thread. That's
why I don't think a separate spreadsheet group would be useful.
Pieter Vandenberg
pieter.v...@sdsu.edu
Rodney Beard
>I have QPW 6.01. Is there a way to use it to calculate calculus derivatives?
> There doesn't appear to be any @function that does this. Do other
>spreadsheets have such @functions?
In general spreadsheets do not possess such functions. You can do numerical
differentiation and integration in a spreadsheet as one of posting indicated.
to do symbolic calculus you should preferably use a symbolic algebra package
such as Maple or mathematica. I've also heard of a package that combines
features of spreadsheets with those of symbolic algebra packages but I can't
recall it's name.
It is however possible to do symbolic calculus in a spreadsheet with
considerable effort. At least I've managed to get Quattro-Pro to do some
limited symbolic differentiation. I'm still working on the macro for this
though. I wouldn't advise anyone to try this but it is interesting that it can
be done.
My first attempts simply involved using lookup tables of common derivatives,
but although this might be useful for integrals I found that the table size
tended to gety rather large for differentiation. So, I changed tack instead you
type in a string, such as X^2 which is then read by the macro which parses the
string simultaneously from both the left and right. The string is then treated
as a hexadecimal number (which of course it's not), I then convert this to
decimal carry out the differentiation operation on the exponent and then
convert it back to a string by putting all the pieces back together.
I've got it working for simple differentiation, but have yet to start on the
chain rule, or product rule.
Regards,
Rodney Beard
University of Queensland
Mohammed Reza Jahanbin
Alvin M. Foreman <alv...@nwu.edu> wrote:
>In article <billo-01049...@ch62.bc.edu>,
> bi...@hermes.bc.edu (E. J. Billo) wrote:
>>Foreman) wrote:
>>
>>> I have QPW 6.01. Is there a way to use it to calculate calculus
>derivatives?
>>> There doesn't appear to be any @function that does this. Do other
>>> spreadsheets have such @functions?
>>"Derivatives of a Function
>> The first derivative of a formula in a worksheet cell can be obtained
>>with a high degree of accuracy by evaluating the formula at x0 and at
>>x0+Dx. Since Excel carries 15 significant figures, Dx can be made very
>>small. Under these conditions DF/Dx approximates dF/dx very well. "
>>
>E.J. - thanks for the reply, but could you give me an example of how you would
>actually implement this? You lost me with the "x0 Dx" stuff... What I want
>to use calculus for is to calculate the first and second derivatives of an
>option's value with respect to changes in stock price, time, interest rates,
>etc.
You need to look at Black-Scholes formula to workout the greeks
(delta, gamma, theta, rho, etc). If your option is American with divis
or you have some kind of exotic option then look at a tree approach
solution to get the greeks. In either case a generic calculus
capability will not give you very accurate answers.
Alvin M. Foreman
m...@eng.cam.ac.uk (Mohammed Reza Jahanbin) wrote:
>
>You need to look at Black-Scholes formula to workout the greeks
>(delta, gamma, theta, rho, etc). If your option is American with divis
>or you have some kind of exotic option then look at a tree approach
>solution to get the greeks. In either case a generic calculus
>capability will not give you very accurate answers.
>
When using Black-Scholes, the greeks are defined as the first and second
derivatives of the option value. If I enter the formula for the Black-Scholes
model into my spreadsheet to get theoretical option values, why wouldn't a
calculus capability enable me to calculate accurate values for the greeks
based on those option values? I thought that by using calculus (instead of an
approximation formula found in options books) I'd get an *exact* answer.