Generating Correlated Random Values in Excel

Hi Mike,

Thanks so much for your advice.  I think that I have it figured out,
but can you confirm this for me?  I can't afford to screw this up.

If  mean(inflation) = .031
stdev(inflation) = .047
mean(stock return) = .106
stdev(stock return) = .204

Column Headers:
A     B       C          D
1      Z1    Z2   Inflation   Stock Return

Data Generation Formulas

A2 =NORMINV(RAND(),0,1)
B2 =NORMINV(RAND(),0,1)
C2 =0.031+0.047*A2
D2 =0.106+0.204*(A2*rho+B2*(1-rho^2)^0.5)

Are these formulas correct to generate the random data?  Particularly
cell D2.  Does this give me a complete value that takes into
consideration both the correlated part of the value and the
uncorrelated part?  It looks like it does.  I copied this down many
rows and ran a histogram and it looks good to me, but I just wanted to
confirm before I ran with it.

Thank you again, Mike.

Randy Eastland

+++++++++++++++++++++++++

Randy Eastland  -

I don't know where data is available, but you can get correlated random
values as follows:

If Z1 and Z2 are each independent standard normal random variables,
i.e., each is NORMINV(RAND(),0,1), then to get X and Y with correlation
rho, use

X = MeanX + StDevX*Z1

Y = MeanY + StDevY*(Z1*rho + Z2*(1-rho^2)^0.5)

Alternatively, search google groups for "excel correlated normal" or
similar keywords, without the quotes.

Or, download risk230e.xla from the "Decision Modeling Using Excel" page
of my university web site, and use its RandBiNormal function, which is
described in RiskSim's built-in Help.

-  Mike Middleton

+++++++++++++++++++++++++

At 14:41 2005-01-05, you wrote:

Hello Mike,

A few weeks ago, you responded to a post that I had placed on an Excel
VBA message board when I was struggling with the ATPVBAEN.XLA!Random
function. Your advice was excellent and has help me considerably.  I
wondered it I could ask you another question.

Actually, 2 questions:

1.  I'm looking for correlation coefficients between the general rate
of inflation, overall stock market returns, and overall bond market
returns. Do you know of a resource where I might find these values?

2.  In building my table of randomly generated inflation rates, stock
market returns, and bond market returns, I am currently using the
formula that you suggested on the message board
(=NORMINV(RAND(),Mean,StDev)).  This formula works excellently, but it
does not consider the fact that these values might be correlated (thus
the first question).  Assuming that I can find r-squared values for a)
inflation with stock returns, and b) inflation with bond
returns, can you offer any advice as to how I might alter this formula
to take correlation into consideration?

Boiled down, my table is basically three columns.  A = inflation rate,
B = stock market returns, and C = bond market returns.  My thought is
to keep your formula intact for inflation, letting this value be
randomly generated around a mean and standard deviation.  B and C then
need to take into consideration that there is at least some correlation
with the inflation rate.  My knowledge of stats began and pretty much
ended back in college.  I have poured through some stats books that I
was able to gather, but I can't
figure out how to do this reliably.  Can you help?

Thank you very much, Mike.  I'm sorry to bother you.  If this is too
much to ask, I understand.

Randy Eastland

0
1/12/2005 10:57:51 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
1065 Views

Similar Articles

[PageSpeed] 53

Randy  -

Two things to check:

(1) Your formulas seem OK. But you'll need to either (a) replace "rho" with 
a numerical value (between -1 and +1) or a reference to a cell containing a 
value or (b) define a name "rho" similarly.

(2) A histogram is useful for checking each variable separately. But you 
should also use an XY (Scatter) chart to check the results for a hundred or 
so pairs of Inflation and Stock Return.

-  Mike

www.mikemiddleton.com

++++++++++++++++++++++++++

"Randy" <john.public1@comcast.net> wrote in message 
news:1105570671.586697.8010@f14g2000cwb.googlegroups.com...
> Hi Mike,
>
> Thanks so much for your advice.  I think that I have it figured out,
> but can you confirm this for me?  I can't afford to screw this up.
>
> If  mean(inflation) = .031
> stdev(inflation) = .047
> mean(stock return) = .106
> stdev(stock return) = .204
>
> Column Headers:
> A     B       C          D
> 1      Z1    Z2   Inflation   Stock Return
>
> Data Generation Formulas
>
> A2 =NORMINV(RAND(),0,1)
> B2 =NORMINV(RAND(),0,1)
> C2 =0.031+0.047*A2
> D2 =0.106+0.204*(A2*rho+B2*(1-rho^2)^0.5)
>
> Are these formulas correct to generate the random data?  Particularly
> cell D2.  Does this give me a complete value that takes into
> consideration both the correlated part of the value and the
> uncorrelated part?  It looks like it does.  I copied this down many
> rows and ran a histogram and it looks good to me, but I just wanted to
> confirm before I ran with it.
>
> Thank you again, Mike.
>
> Randy Eastland
>
> +++++++++++++++++++++++++
>
> Randy Eastland  -
>
> I don't know where data is available, but you can get correlated random
> values as follows:
>
> If Z1 and Z2 are each independent standard normal random variables,
> i.e., each is NORMINV(RAND(),0,1), then to get X and Y with correlation
> rho, use
>
> X = MeanX + StDevX*Z1
>
> Y = MeanY + StDevY*(Z1*rho + Z2*(1-rho^2)^0.5)
>
> Alternatively, search google groups for "excel correlated normal" or
> similar keywords, without the quotes.
>
> Or, download risk230e.xla from the "Decision Modeling Using Excel" page
> of my university web site, and use its RandBiNormal function, which is
> described in RiskSim's built-in Help.
>
> -  Mike Middleton
>
> +++++++++++++++++++++++++
>
> At 14:41 2005-01-05, you wrote:
>
> Hello Mike,
>
> A few weeks ago, you responded to a post that I had placed on an Excel
> VBA message board when I was struggling with the ATPVBAEN.XLA!Random
> function. Your advice was excellent and has help me considerably.  I
> wondered it I could ask you another question.
>
> Actually, 2 questions:
>
> 1.  I'm looking for correlation coefficients between the general rate
> of inflation, overall stock market returns, and overall bond market
> returns. Do you know of a resource where I might find these values?
>
> 2.  In building my table of randomly generated inflation rates, stock
> market returns, and bond market returns, I am currently using the
> formula that you suggested on the message board
> (=NORMINV(RAND(),Mean,StDev)).  This formula works excellently, but it
> does not consider the fact that these values might be correlated (thus
> the first question).  Assuming that I can find r-squared values for a)
> inflation with stock returns, and b) inflation with bond
> returns, can you offer any advice as to how I might alter this formula
> to take correlation into consideration?
>
> Boiled down, my table is basically three columns.  A = inflation rate,
> B = stock market returns, and C = bond market returns.  My thought is
> to keep your formula intact for inflation, letting this value be
> randomly generated around a mean and standard deviation.  B and C then
> need to take into consideration that there is at least some correlation
> with the inflation rate.  My knowledge of stats began and pretty much
> ended back in college.  I have poured through some stats books that I
> was able to gather, but I can't
> figure out how to do this reliably.  Can you help?
>
> Thank you very much, Mike.  I'm sorry to bother you.  If this is too
> much to ask, I understand.
>
> Randy Eastland
> 


0
middleton (61)
1/15/2005 12:44:25 AM
Thanks, Mike.  I appreciate all of your help with this.

Randy

Michael R Middleton wrote:
> Randy  -
>
> Two things to check:
>
> (1) Your formulas seem OK. But you'll need to either (a) replace
"rho" with
> a numerical value (between -1 and +1) or a reference to a cell
containing a
> value or (b) define a name "rho" similarly.
>
> (2) A histogram is useful for checking each variable separately. But
you
> should also use an XY (Scatter) chart to check the results for a
hundred or
> so pairs of Inflation and Stock Return.
>
> -  Mike
>
> www.mikemiddleton.com
>
> ++++++++++++++++++++++++++
>
> "Randy" <john.public1@comcast.net> wrote in message
> news:1105570671.586697.8010@f14g2000cwb.googlegroups.com...
> > Hi Mike,
> >
> > Thanks so much for your advice.  I think that I have it figured
out,
> > but can you confirm this for me?  I can't afford to screw this up.
> >
> > If  mean(inflation) = .031
> > stdev(inflation) = .047
> > mean(stock return) = .106
> > stdev(stock return) = .204
> >
> > Column Headers:
> > A     B       C          D
> > 1      Z1    Z2   Inflation   Stock Return
> >
> > Data Generation Formulas
> >
> > A2 =NORMINV(RAND(),0,1)
> > B2 =NORMINV(RAND(),0,1)
> > C2 =0.031+0.047*A2
> > D2 =0.106+0.204*(A2*rho+B2*(1-rho^2)^0.5)
> >
> > Are these formulas correct to generate the random data?
Particularly
> > cell D2.  Does this give me a complete value that takes into
> > consideration both the correlated part of the value and the
> > uncorrelated part?  It looks like it does.  I copied this down many
> > rows and ran a histogram and it looks good to me, but I just wanted
to
> > confirm before I ran with it.
> >
> > Thank you again, Mike.
> >
> > Randy Eastland
> >
> > +++++++++++++++++++++++++
> >
> > Randy Eastland  -
> >
> > I don't know where data is available, but you can get correlated
random
> > values as follows:
> >
> > If Z1 and Z2 are each independent standard normal random variables,
> > i.e., each is NORMINV(RAND(),0,1), then to get X and Y with
correlation
> > rho, use
> >
> > X = MeanX + StDevX*Z1
> >
> > Y = MeanY + StDevY*(Z1*rho + Z2*(1-rho^2)^0.5)
> >
> > Alternatively, search google groups for "excel correlated normal"
or
> > similar keywords, without the quotes.
> >
> > Or, download risk230e.xla from the "Decision Modeling Using Excel"
page
> > of my university web site, and use its RandBiNormal function, which
is
> > described in RiskSim's built-in Help.
> >
> > -  Mike Middleton
> >
> > +++++++++++++++++++++++++
> >
> > At 14:41 2005-01-05, you wrote:
> >
> > Hello Mike,
> >
> > A few weeks ago, you responded to a post that I had placed on an
Excel
> > VBA message board when I was struggling with the
ATPVBAEN.XLA!Random
> > function. Your advice was excellent and has help me considerably.
I
> > wondered it I could ask you another question.
> >
> > Actually, 2 questions:
> >
> > 1.  I'm looking for correlation coefficients between the general
rate
> > of inflation, overall stock market returns, and overall bond market
> > returns. Do you know of a resource where I might find these values?
> >
> > 2.  In building my table of randomly generated inflation rates,
stock
> > market returns, and bond market returns, I am currently using the
> > formula that you suggested on the message board
> > (=NORMINV(RAND(),Mean,StDev)).  This formula works excellently, but
it
> > does not consider the fact that these values might be correlated
(thus
> > the first question).  Assuming that I can find r-squared values for
a)
> > inflation with stock returns, and b) inflation with bond
> > returns, can you offer any advice as to how I might alter this
formula
> > to take correlation into consideration?
> >
> > Boiled down, my table is basically three columns.  A = inflation
rate,
> > B = stock market returns, and C = bond market returns.  My thought
is
> > to keep your formula intact for inflation, letting this value be
> > randomly generated around a mean and standard deviation.  B and C
then
> > need to take into consideration that there is at least some
correlation
> > with the inflation rate.  My knowledge of stats began and pretty
much
> > ended back in college.  I have poured through some stats books that
I
> > was able to gather, but I can't
> > figure out how to do this reliably.  Can you help?
> >
> > Thank you very much, Mike.  I'm sorry to bother you.  If this is
too
> > much to ask, I understand.
> >
> > Randy Eastland
> >

0
1/16/2005 9:50:02 PM
Reply:

Similar Artilces:

ExcelReport
Please access http://www.ljzsoft.com ExcelReport is a report generator to generate reports in Microsoft Excel format. If you know how to use Excel and write SQL statements, you can use ExcelReport to create all kinds of reports as you need. Furthermore, with one time configuration, you can easily build periodic reports such as daily, weekly, monthly and annual reports. ...

Dynamically generating email as html from a template
Would anyone know of an article/information detailing the creation of templates in word that can be used to create word documents by merging data to be saved as html to be included as the body of an email in outlook. .... or another method of dynamically creating emails in outlook based on a template. Thanx for the Advice Have you looked at Word's mail merge feature? That's exactly what it = does. See http://www.slipstick.com/contacts/startletter.htm=20 --=20 Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Us...

Is it possible to make Labels from Excel?
I'm not sure if it can be done, our database is strickly Excel and I'd like to make labels from the Names & addresses we have listed on there. Can it be done stricktly from excel? Hi Julia, Not an easy task without a lot of programming. But you can use an Excel database as the source file for Word 'mail-merge' and hence make labels. Bernard "Julia" <anonymous@discussions.microsoft.com> wrote in message news:001501c3a3b8$08937fa0$a301280a@phx.gbl... > I'm not sure if it can be done, our database is strickly > Excel and I'd like to make la...

Import excel into publisher
It would be beneficial, if Publisher had the capibilities to import Excel spreadsheets to keep data up-to-date. I read that it is NOT possible, or is there something I missed? Import excel into publisher wrote: > It would be beneficial, if Publisher had the capibilities to import Excel > spreadsheets to keep data up-to-date. I read that it is NOT possible, or is > there something I missed? You can Mail Merge from Excel spreadsheets, and you can insert an Excel sheet as an OLE object. Does that not cut the mustard? -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org ...

Extremely complex problem: showing a value as an 'axis' on a circle
The value represents the orientation in degrees from horizontal So, imagine a circle where 3 o'clock is 0 degrees 12 o'clock is 90 degrees 9 o'clock is 180 degrees and a value of 180 is represented by: a picture of a circle with a horizontal line (going straigh across the diameter) and a value of 90 is represented by: a picture of a circle with a vertial line Is there any way that Excel can translate a value into this pictorally? BACKGROUND IF YOU ARE INTERESTED: taken from http://www.medem.com/MedLB/article_detaillb_for_printer.cfm?article_ID=ZZZG1...

Error Generating the Offlice Address Book
I have a mixed site with 3 5.5 server and 4 2003 servers. I installed 2003 SP1 a few weeks back and since then I'm having an issue generating my Offline Address Book. Here the event log messages I'm getting. Event ID 9331: OALGen encountered error 80040107 (internal ID 501023d) accessing the public folder store while generating the offline address list for address list '/'. - Default Offline Address List For more information, click http://www.microsoft.com/contentredirect.asp. Event ID 9335: OALGen encountered error 80040107 while cleaning the offline address list public ...

Preventive Maintenance, multiple call generation 1 generate to pr.
need to be able to have preventive maintenance generate a call for each day in a month when running the generate routine. Rather than having to create each one individually. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/...

Random assignment
I have three tables CREATE TABLE [dbo].[STAFF]( [StaffKEY] [int] NOT NULL, [StaffName] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, ) ON [PRIMARY] CREATE TABLE [dbo].[Clients]( [ClientKEY] [int] not NULL, [ClientName] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, ) ON [PRIMARY] CREATE TABLE [dbo].[STAFF_Collections]( [CLIENTKEY] [int] NULL, [StaffName] [varchar](17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Staffkey] [int] NULL ) ON [PRIMARY] Sample data Staff: 303, 'Paul Newman' 405, 'Jane Fonda' 605,&#...

Starting Excel #2
How can make sure that a workbook with multiple sheets always starts on a specific sheet. Hi put the following type of code into your workbook module ('Thisworkbook'): sub workbook_open() me.worksheets("sheet1").activate end sub "Gerrym" wrote: > How can make sure that a workbook with multiple sheets > always starts on a specific sheet. > ...

ComboBox Values to TextBox
Hi, On my form "frmDetails", I have a ComboBox "cboProblem" and a field [RATE] The ComboBox looks up the values from the table tblProblem with 3 fields [ID] [TASK] [RATE] The 3 fields are formatted as AutoNumber, Text and Currency The ComboBox RowSource is SELECT [tblProblem].[ID], [tblProblem].[Task], [tblProblem].[Rate] FROM tblProblem; I need to select a Problem from the ComboBox and the [Rate] field automatically populate. I am using Access 2007. I have tried a few variants of the following but cannot get get the field to populate. "Me!Rate = Me!Problem.Colu...

Excel 2003 versus Excel 2007 calculation speed
I'm running an interesting test on a PC of Excel 2003 and Excel 2007. I ran a Monte Carlo simulation in Excel 2003. The workbook has 50 input cells (cells where random values per distributions are input) and 200 calculation (formula) cells. So, it is basically a small workbook. No conditional formatting. No charts. The VBA code calculates random values per distributions and puts into the input cells. The workbook is then recalculated and the values of the input and output cells (20 of the formula cells) are recorded. Only cell values are recorded - formatting is not set on ...

Where are auto-complete values stored in browser forms
My browsers (IE and Firefox) keep values of auto-complete for form fields (eg what I've entered for Google searches in the past). Where is this kept? How do I find all the lists for the different kinds of fields that kept in my PC? Not an Office's Outlook question. Ask in a Windows forum. "Hall" wrote: > My browsers (IE and Firefox) keep values of auto-complete for form fields > (eg what I've entered for Google searches in the past). > > Where is this kept? How do I find all the lists for the different kinds of > fields that kept in my PC? &g...

When entering formula it does not show the value but the formula
We received a file and are trying to sum columns but when the person enters the formula the formula stays visible but not the actual value. I've never run across this before. You've probably got the cell formatted as text. Format as General (or Number) & re-enter the formula. -- David Biddulph "SBongiov" <SBongiov@discussions.microsoft.com> wrote in message news:85781AA1-FE61-4351-A221-A01B63172585@microsoft.com... > We received a file and are trying to sum columns but when the person > enters > the formula the formula stays visible but ...

PROVEN SYSTEM TO GENERATE CASH
Hi friends, I think it be a very promising program .... the great thing about it is they market it for you! Easy, once you join all of this happens without you lifting a finger! You automatically received your replicated Unique URL (personal web site) You automatically have 150,000+ emails sent to promote your web site You automatically have people (just like you) visit your web site You automatically have welcome emails sent to your new prospects You automatically have 10 days of follow-up (drip) emails sent to your new prospects The System automatically processes the new member when they...

Finding a value in several different columns
Sorry if this is a really easy question, but I'm still learning when it comes to Access 2003's capabilities. I have a table from which I want to create either a report or a query to show the record number (primary key) of all instances where the value "1" occurs in field A OR field B OR field C, followed by all instances of "2" then "3" and so on. I'd imagine it's an easy thing to do for an experienced user, but I'm a novice, so any help would be appreciated. Hi Nick, Interesting question. Here is one way to do it. Create a ta...

sumif remove high/low values
Hello, I have a list of ten values. I would like to sum this list with the top 2 and bottom 2 values removed in the sum calculation. Any help would be great, ben -- Big Ben ------------------------------------------------------------------------ Big Ben's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6431 View this thread: http://www.excelforum.com/showthread.php?threadid=395038 Hi Ben, Try this =SUM(D1:D10)-SUMPRODUCT(--(LARGE(D1:D10,{1;2}))+(SMALL(D1:D10,{1;2}))) or if there will be 10 then you could use =SUMPRODUCT(--(LARGE(D1:D10,ROW(INDIRECT(&qu...

vs2005 xsd.exe: Can it generate different files?
Is there an option to get the vs2005 xsd.exe tool to generate seperate files for each class that it generates? It would be much easier from a maintenance standpoint than one big giant file for my classes. Thanks for any help Matt MattBell wrote: > Is there an option to get the vs2005 xsd.exe tool to generate seperate files > for each class that it generates? I had a look at the output of xsd.exe /help but there is no option/setting mentioned. -- Martin Honnen --- MVP XML http://JavaScript.FAQTs.com/ ...

How can I combine multiple cells in Excel?
I am trying to combine column A(last name) and column B(first name). I have tried to use Excel help, but am not getting any results. Please explain how I can do this. Thank you. Hi in an adjacent column =B1 & " " & A1 or =CONCATENATE(B1," ",A1) or reverse the A1/B1 depending on how you want the combined name displayed. You can then copy this formula down the list of names and select the column copy it and then edit / paste special - values to change the column from being a formula into the actual names for use later on. Cheers JulieD "jallbright24...

to generate only table names but not their schema in the database
My replication on multiple servers failed. So i want to know which articles are published from the daabase. i wish to generate only articles that are there in the database, but not their schema or column names. how to do this through ms visio ...

Auto-generate product ID
Is there a way to auto-generate product ID? Or is there any third party addons that does that? Better yet would be a way to auto-generate a barcode for each item "Raymond" <Raymond@discussions.microsoft.com> wrote in message news:F1EF7ACB-8D15-4527-A8C7-53C9F8FD6906@microsoft.com... > Is there a way to auto-generate product ID? Or is there any third party > addons that does that? Raymond, We offer AutoGen for this purpose. Barcode 128A is the default for new item adds. For more information contact your RMS reseller or visit: http://www.digitalretailer.com/rmsaddin...

mouse-over value
My secondary axis value is say 10% and the mouse-over value displays .10 which is incorrect, can you advise how to fix this please 0.10 = 10%. Perhaps your data range has different formatting than the axis. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Gill" <Gill@discussions.microsoft.com> wrote in message news:1F9B56E5-FDA9-44E8-A64D-EC10D9ECF354@microsoft.com... > My secondary axis value is say 10% and the mouse-over value displays .10 > which is incorrect, can you ...

Export Only Values
Below is a code that works great and what it does is that it exports a worksheet from the current workbook to a new workbook. The problem I'm having is that I only want to copy the values and leave the formulas and references behind. Any help would be appreciated. Thank you for your help! Dim message As String Set MyComputer = CreateObject("Scripting.FileSystemObject") FolderName1 = Range("F6") FileName1 = Range("M6") If MyComputer.FolderExists(FolderName1) = False Then message = MsgBox("The directory: " &am...

Auto Generate Serial Numbers for Bill of Materials
Hi! We have a partner in Australia who has some issues with the Serial Number Masks for Finished Goods in Inventory. Currently Bill of Materials functions this way: If the customer creates 20 finished products which tracks Serial Numbers. The user has to manually enter the 20 serial numbers into Assembly Entry. Even though the Finished product has been setup with a Serial Mask, there is no option to Auto Generate Serial Numbers. He would like to suggest that there should be an auto generate serial number function also for the finished goods in Bill of Materials. Thanks, ---------...

Subtotal of Hidden Values over a certain amount of days
Good afternoon, I want to subtotal only numbers over 30 that are not hidden and don't know the formula. Can anyone assist me: A 1 35 2 65 3 08 4 12 12 38 15 68 16 = SUBTOTAL(?, +30 ) Hopefully that makes sense. Thanks again for everyones posting on this. This website has saved me from tons of work many times. -- Message posted via http://www.officekb.com The rows are hidden and not filtered, right? What version of Excel are you using? Excel 2003 extended the SUBTOTAL function to work ...

Excel's inbuilt functions on task bar
Hi, I would like to know if there's a way to access the task Bar functions of Average, Count, Count Nums, Max, Min, Sum, as they respond so well when using Filtering by not including cells that have been "hidden" by the filter. I know the Subtotal function also does this, but I can't seem to get the Subtotal function to respond to Count when the value is text. Rob What "function number" are you using in the Subtotal function? Don't forget, "2" represents Count, While "3" represents CountA, which is what you would want to use to co...