Come utilizzare Excel per simulare i prezzi delle azioni

Alcuni investitori attivi modellano le variazioni di un titolo o altro bene per simulare il suo prezzo e quello degli strumenti che si basano su di esso, come i derivati. La simulazione del valore di un bene su un foglio di calcolo Excel può fornire una rappresentazione più intuitiva della sua valutazione per un portafoglio.

Punti chiave

• I trader che desiderano testare un modello o una strategia possono utilizzare prezzi simulati per convalidarne l'efficacia.
• Excel può aiutarti con i tuoi test retrospettivi utilizzando una simulazione monte carlo per generare movimenti di prezzo casuali.
• Excel può essere utilizzato anche per calcolare la volatilità storica da collegare ai modelli per una maggiore precisione.

Creazione di una simulazione del modello di prezzo

Sia che stiamo valutando l'acquisto o la vendita di uno strumento finanziario, la decisione può essere aiutata studiandola sia numericamente che graficamente. Questi dati possono aiutarci a giudicare la prossima mossa probabile che l'asset potrebbe fare e le mosse meno probabili.

Prima di tutto, il modello richiede alcune ipotesi preliminari. Assumiamo, Per esempio, che il quotidiano torna, o "r(t), "di questi beni sono normalmente distribuiti con la media, "(μ), " e deviazione standard sigma, "(σ)." Queste sono le ipotesi standard che useremo qui, sebbene ce ne siano molti altri che potrebbero essere utilizzati per migliorare l'accuratezza del modello.

R ( T ) = S ( T ) − S ( T − 1 ) S ( T − 1 ) ? n ( μ , ? ) dove: S ( T ) = chiudere T S ( T − 1 ) = chiudere T − 1 \begin{allineato} &r ( t ) =\frac { S ( t ) - S ( t - 1 ) }{ S ( t - 1 ) } \sim N ( \mu, \sigma ) \\ &\textbf{dove:} \\ &S ( t ) =\text{close}_t \\ &S ( t - 1 ) =\text{close}_{t - 1} \\ \end{ allineato} ​r(t)=S(t−1)S(t)−S(t−1)​∼N(μ, σ)dove:S(t)=armadio​S(t−1)=armadio−1​​

Che dà:

R ( T ) = S ( T ) − S ( T − 1 ) S ( T − 1 ) = μ ? T + ? ? ? T dove: ? T = 1 giorno = 1 3 6 5 di un anno μ = Significare ? ? n ( 0 , 1 ) ? = volatilità annualizzata \begin{allineato} &r ( t ) =\frac { S ( t ) - S ( t - 1 ) }{ S ( t - 1 ) } =\mu \delta t + \sigma \phi \sqrt { \delta t } \\ &\textbf{dove:} \\ &\delta t =1 \ \text{giorno} =\frac { 1 }{ 365 } \ \text{di un anno} \\ &\mu =\text{ significa} \\ &\phi \cong N ( 0, 1 ) \\ &\sigma =\text{volatilità annualizzata} \\ \end{allineato} ​r(t)=S(t−1)S(t)−S(t−1)​=μδt+σϕδt​dove:δt=1 giorno=3651​ di un annoμ=mediaϕ≅N(0, 1)σ=volatilità annualizzata​

Che si traduce in:

S ( T ) − S ( T − 1 ) S ( T − 1 ) = μ ? T + ? ? ? T \begin{allineato} &\frac { S ( t ) - S ( t - 1 ) }{ S ( t - 1 ) } =\mu \delta t + \sigma \phi \sqrt { \delta t } \\ \ fine{allineato} ​S(t−1)S(t)−S(t−1)​=μδt+σϕδt​​

Finalmente:

S ( T ) − S ( T − 1 ) = S ( T − 1 ) μ ? T + S ( T − 1 ) ? ? ? T S ( T ) = S ( T − 1 ) + S ( T − 1 ) μ ? T + S ( T − 1 ) ? ? ? T S ( T ) = S ( T − 1 ) ( 1 + μ ? T + ? ? ? T ) \begin{allineato} S ( t ) - S ( t - 1 ) =&\ S ( t - 1 ) \mu \delta t + S ( t - 1 ) \sigma \phi \sqrt { \delta t } \\ S ( t ) =&\ S ( t - 1 ) + S ( t - 1 ) \mu \delta t \ + \\ &\ S ( t - 1 ) \sigma \phi \sqrt { \delta t } \\ S ( t ) =&\ S ( t - 1 ) ( 1 + \mu \delta t + \sigma \phi \sqrt { \delta t } ) \\ \end{allineato} S(t)−S(t−1)=S(t)=S(t)=​ S(t−1)μδt+S(t−1)σϕδt​ S(t−1)+S(t− 1)μδt + S(t−1)σϕδt​ S(t−1)(1+μδt+σϕδt​)​

E ora possiamo esprimere il valore del prezzo di chiusura di oggi utilizzando la chiusura del giorno precedente.

• Calcolo di μ:

Per calcolare μ, che è la media dei rendimenti giornalieri, prendiamo gli n prezzi di chiusura passati successivi e applichiamo, che è la media della somma degli n prezzi passati:

μ = 1 n ? T = 1 n R ( T ) \begin{allineato} &\mu =\frac { 1 }{ n } \sum_{ t =1 } ^ { n } r ( t ) \\ \end{allineato} ​μ=n1​t=1∑n​r(t)​

• Il calcolo della volatilità σ - volatilità

φ è una volatilità con una media di variabile casuale zero e deviazione standard uno.

Calcolo della volatilità storica in Excel

Per questo esempio, utilizzeremo la funzione di Excel "=INV.NORM.ST (RAND ())." Con una base dalla distribuzione normale, questa funzione calcola un numero casuale con media zero e deviazione standard uno. Per calcolare μ, semplicemente mediare i rendimenti usando la funzione Ln (.):la distribuzione log-normale.

Nella cella F4, inserire "Ln (P (t) / P (t-1)"

Nella cella F19 cerca "=MEDIA (F3:F17)"

Nella cella H20, inserire “=MEDIA(G4:G17)

Nella cella H22, inserisci "=365*H20" per calcolare la varianza annualizzata

Nella cella H22, inserire "=SQRT(H21) " per calcolare la deviazione standard annualizzata

Quindi ora abbiamo la "tendenza" dei rendimenti giornalieri passati e la deviazione standard (la volatilità). Possiamo applicare la nostra formula trovata sopra:

S ( T ) − S ( T − 1 ) = S ( T − 1 ) μ ? T + S ( T − 1 ) ? ? ? T S ( T ) = S ( T − 1 ) + S ( T − 1 ) μ ? T + S ( T − 1 ) ? ? ? T S ( T ) = S ( T − 1 ) ( 1 + μ ? T + ? ? ? T ) \begin{allineato} S ( t ) - S ( t - 1 ) =&\ S ( t - 1 ) \mu \delta t + S ( t - 1 ) \sigma \phi \sqrt { \delta t } \\ S ( t ) =&\ S ( t - 1 ) + S ( t - 1 ) \mu \delta t \ + \\ &\ S ( t - 1 ) \sigma \phi \sqrt { \delta t } \\ S ( t ) =&\ S ( t - 1 ) ( 1 + \mu \delta t + \sigma \phi \sqrt { \delta t } ) \\ \end{allineato} S(t)−S(t−1)=S(t)=S(t)=​ S(t−1)μδt+S(t−1)σϕδt​ S(t−1)+S(t− 1)μδt + S(t−1)σϕδt​ S(t−1)(1+μδt+σϕδt​)​

Faremo una simulazione in 29 giorni, quindi dt =1/29. Il nostro punto di partenza è l'ultimo prezzo di chiusura:95.

• Nella cella K2, inserire "0".
• Nella cella L2, inserisci "95".
• Nella cella K3, inserire "1".
• Nella cella L3, immettere "=L2 * (1 + $F$19 * (1/29) + $H$22 *SQRT(1/29)*INV.NORM. (RAND ()))."

Prossimo, trasciniamo la formula in basso nella colonna per completare l'intera serie di prezzi simulati.

Questo modello ci permette di trovare una simulazione degli asset fino a 29 date date, con la stessa volatilità dei primi 15 prezzi che abbiamo selezionato e con un andamento simile.

Infine, possiamo fare clic su "F9" per avviare un'altra simulazione poiché abbiamo la funzione rand come parte del modello.