monte carlo output: statistics in Excel (How?)

[choubix]

hello,

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...

thanks!

[Bob Phillips]

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.

[choubix]

hi Xld,

I intend to use this add in www.hoadley.net
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.

[choubix]

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...

+++

[joseph-anand]

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
{(Profit*Demand)/Investment}