Results 1 to 5 of 5

Thread: monte carlo output: statistics in Excel (How?)

  1. #1

    monte carlo output: statistics in Excel (How?)


    I was coding a backtesting spreadsheet for some structured products until I realized the add in I wanted to use was nice yet not 100% accurate.

    I 2x checked the volatility and expcted returns of the stock to the input and they are deviating too much from a "normal" result.

    I found an add in that seems much better and that I undersntad. yet, even if it provides a monte carlo class to simulate the returns of the udnerlying stocks their is a gap: their is no analysis of the ouputs...

    I understand hwo to do these things:
    get the source data
    generate random prices for X days/Months/Years
    build my strategy depending on the random prices generated
    calculate my ouputs
    loop this (part of the monte carlo process)

    yet: how do I analyse the ouputs I get during each iteration at the end of the whole monte carlo process? (like Internal Rate of return, Average payoff etcetc)

    in the addins I tried before, in the cell that returns the output you had to add "+output()" so that the value of that cell waht being analyzed at the end of the process. how can i do something like that please?

    I guess the analysis toolpack can be helpful but I really don't know where to start from...


  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Apr 2005
    Surely Monte Carlo simulations are so complex and require so many calculations that these must be done in XLLs or DLLs at the leats. Crafting your own seems OTT to me as there are already so many out there.

    If, big IF, you are going to craft your own, you will need to work hand in hand with a business person who can give you those business ruls.
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    hi Xld,

    I intend to use this add in
    it provides oretty much everything (the sample spreadsheets are useful it's the least I can say) yet : there is no analysis "module" for the output of the simulation... this is why I though analysis toolpack would have been a good choice.

  4. #4
    no idea?

    the add in helps me generate the log return of the underlying stock(s)
    from that I can define a strategy
    crete a function taht returns the payoff of the strategy and the IRR at each iteration of the simulation

    now what I need is to understand how to analyse the 2 outputs: payoff and IRR... if anyone here can give me a lead to follow...


  5. #5
    Can someone help me to solve this problem

    A market survey company wants to study the investment in a detergent factory based
    on three factors. These factors are
    Independent of each other. The probability distribution of each are

    Annual Demand Profit/Unit Required Investment
    Units Probability Rs. Probability Rs. Probability
    20 0.05 3.00 0.10 1,750.00 0.25
    25 0.10 5.00 0.20 2,000.00 0.50
    30 0.20 7.00 0.40 2,500.00 0.25
    35 0.30 9.00 0.20
    40 0.15 10.00 0.10
    45 0.15
    50 0.05

    Use Monte-Carlo simulation for 1000 iterations and estimate the ROI percentage based on profit

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts