On 2 Nov 2003 at 5:56, Kristian Moe wrote:
I don't know how to do this in excel (never using excel), but for
finance you need something better than excel anyhow.
In R I did:
> test
[,1] [,2] [,3]
[1,] 4735 0.0080 0.0080
[2,] 2522 0.0530 0.0610
[3,] 1343 0.1537 0.2147
[4,] 715 0.2548 0.4695
[5,] 381 0.2640 0.7335
[6,] 203 0.1750 0.9085
[7,] 108 0.0725 0.9810
[8,] 58 0.0172 0.9982
[9,] 31 0.0018 1.0000
test is a matrix with your data. Then:
> fun <- approxfun(x=test[,3], y=test[,1])
> fun(0.05)
[1] 2981.302
> fun(0.85)
[1] 262.5029
does linear interpolation.
Kjetil Halvorsen
> Let me start off by saying that I am not a statistics wiz; I've taken
> an introductory college stat class, and that is about it. So if I mix
> up terms here, bear with me. I'm working on a finance thesis, focusing
> on options and value at risk.
>
> My problem is as follows: I am using a discrete pricing model
> (binomial tree), and I have a number of possible outcomes with
> corresponding probabilities. I.e.
>
> Result Prob Cum
> 4735 0,0080 0,0080
> 2522 0,0530 0,0610
> 1343 0,1537 0,2147
> 715 0,2548 0,4695
> 381 0,2640 0,7335
> 203 0,1750 0,9085
> 108 0,0725 0,9810
> 58 0,0172 0,9982
> 31 0,0018 1,0000
>
> From these values it is easy to draw a nice and smooth curve in Excel,
> showing the distribution of the outcomes fairly exact (using scatter
> plot with lines). Now I want an estimate of, say, the 5th percentile
> of the outcomes. It is of course possible to use the cumulative
> probability and get a "linear" eastimate. I am wondering whether
> anyone can think of a more exact and elegant way of doing this? Is
> there any statistics plug-ins that could be helpful? Any help would be
> greatly appreciated.
> .
> .
> =================================================================
> Instructions for joining and leaving this list, remarks about the
> problem of INAPPROPRIATE MESSAGES, and archives are available at:
> . http://jse.stat.ncsu.edu/ .
> =================================================================
.
.
=================================================================
Instructions for joining and leaving this list, remarks about the
problem of INAPPROPRIATE MESSAGES, and archives are available at:
. http://jse.stat.ncsu.edu/ .
=================================================================
