Università degli studi di Foggia
SSIS D.M.85 2005
Laboratorio di didattica della
matematica finanziaria
Classe 17/A
Appunti sull’utilizzo di Excel per la soluzione di problemi di
matematica finanziaria. Ad uso della SSIS sede di Foggia:
lezioni ed esercitazioni di didattica della matematica finanziaria.
Classe 17/A.
Prof. Italo M. Scrocchia.
1
Appunti sull’utilizzo di Excel per la soluzione di problemi di matematica
finanziaria. Ad uso della SSIS sede di Foggia: lezioni ed esercitazioni di
didattica della matematica finanziaria. Classe 17/A.
Prof. Italo M. Scrocchia.
Prefazione:
Questi appunti sono stati appositamente stilati ad uso dei corsisti SSIS
sessione abilitante D.M. 85 del 2005 di matematica finanziaria e di
laboratorio di didattica della matematica finanziaria.
Essi hanno essenzialmente uno scopo didattico e non costituiscono un
esauriente compendio della materia, al cui libro di testo adottato si
rimanda per un efficace preparazione.
1) Utilizzo della RICERCA OBIETTIVO e della funzione TIR.COST
per la soluzione della ricerca del tasso interno di rendimento di un
progetto di investimento.
Proviamo a risolvere con l’ausilio del componente aggiuntivo ricerca
obiettivo di Microsoft Excel® il problema di determinare il Tasso interno
di rendimento.
Come sappiamo il T.I.R. ( tasso interno di rendimento) è quel tasso che
rende uguale a zero il valore attuale netto cioè la differenza ( nel caso qui
descritto, di un progetto di puro investimento1) tra il valore attuale di una
serie di redditi futuri attesi ed il costo iniziale dell’investimento che li ha
prodotti.
Risolviamo il seguente problema tratto dal testo “Elementi di matematica
finanziaria” F. Pressacco, P. Stucchi, Cedam 2007 pag. 146- 147.
Sia un progetto di investimento così descritto sinteticamente:
Somme
Tempi
1
-100
0
+60
1
+72
2
nel quale ad un valore iniziale di esborso seguono uno o più valori di reddito positivi.
2
In formule il problema consiste nel risolvere l’equazione seguente2:
−100+ 60(1 + i ) + 72(1 + i ) = 0
−1
−2
In definitiva attualizzando 60 e 72 ad un opportuno tasso ( il T.I.R.)
dovremmo ottenere 100 che sottratto a -100 produce appunto il valore
attuale netto di 0.
Prepariamo il foglio di lavoro di Excel:
In esso come vediamo abbiamo inserito la formula partendo da un tasso di
interesse 0,32 da noi scelto “a naso” che evidentemente non risolve il
problema in questione.
in verità avremo potuto cavarcela benissimo con “carta e penna” ponendo x = (1 + i ) e risolvendo l’equazione di
secondo grado risultante, ma il pretesto è buono per dire che nel caso di equazioni di grado superiore al quarto non
esistono formule generali di soluzione e quindi occorre far ricorso al calcolo numerico.
2
−1
3
Per farlo chiamiamo in funzione il componente aggiuntivo ricerca
obiettivo: dal menù Strumenti/Ricerca obiettivo.
Impostiamo la cella D9, dove è presente la formula dei valori attuali, come
la cella che dovrà assumere il valore 100, valore attuale che desideriamo
ottenere, ed imponiamo alla ricerca obiettivo di modificare il valore
presente nella cella F4.
4
Come notiamo nei passaggi successivi, delle due schermate del foglio, una
volta impostata la maschera della ricerca obiettivo otteniamo la soluzione
cercata di 0.20, (20%, l’incognita della nostra equazione) che rende uguale
a 100 il valore attuale dei flussi futuri, come richiesto dal problema.
5
6
Ovviamente possiamo in maniera più spedita utilizzare la funzione di Excel
predisposta per il calcolo del T.I.R..
A tal scopo dopo aver impostato come prima il foglio di calcolo con i valori disposti
in colonna (è una nostra scelta dato che si possono disporre anche in riga), chiamiamo
in causa la funzione in questione selezionando nella barra apposita il simbolo f x .
Si aprirà una finestra di dialogo nella quali si chiede di inserire il nome della funzione
specifica, nel nostro caso TIR.COST.
Cliccando su OK otteniamo la finestra che ci invita ad inserire il Val ossia il listato
che contiene tutti i valori del flusso di valori di cui si intende ottenere il T.I.R..
Inserendo la stringa D7:D9 in Val ed omettendo di formulare l’ipotesi di un valore
iniziale ipotizzato di tasso, in questo caso opzionabile, ( notare la logica del calcolo
numerico con la formulazione di una soluzione base da iterare fino al raggiungimento
della soluzione “giusta”) e cliccando su OK otteniamo, come vediamo nella videata
finale la soluzione 20% (0.20) che ovviamente corrisponde a quella precedentemente
ottenuta con l’uso della ricerca obiettivo.
7
8
Va da sé che nel caso esaminato, volutamente semplice, al fine di permettere un
riscontro con carta e penna, con i calcoli effettuati abbiamo ottenuto un risultato,
questo perché con operazioni di puro investimento ciò è sempre possibile; non
9
sempre capita con operazioni non di puro investimento ossia con flussi di cassa che
alternano valori positivi a valori negativi dove può benissimo capitare di avere più
risultati o di non averne affatto.
NOTA: Come riportare le videate ( così come appaiono sullo schermo) da
excel a word:
dal programma excel si attiva la funzione stamp (stampa schermo) che
cattura la videata così come compare sullo schermo del computer e
successivamente si incolla sul foglio di word con il comando incolla.
Per catturare l’immagine di una finestra aperta ( in questo caso solo
l’immagine della maschera del risolutore) si usano i tasti ALT +STAMP-
10
2) Soluzione con l’ausilio di Risolutore di excel di problemi di
ottimizzazione del portafoglio.
Proviamo a risolvere, con l’ausilio del foglio di calcolo Excel, il seguente problema di gestione del
portafoglio ottimo tratto da F. Cacciafesta “Lezioni di matematica finanziaria classica e moderna”,
pag. 335, ed Giappichelli :
Si considerino tre titoli rischiosi A1 , A2 , A3 , aventi rendimenti medi, rispettivamente, da m1 = 12,
m2 = 14, m3 = 16, e matrice varianze/covarianze:
100 50 − 20
50 200 10 ;
− 20 10 300
Occorre individuare,dato i tre pesi in titoli x1 , x 2 , x3 , il portafoglio di varianza totale minima con il
3
vincolo di
∑x
i
=1;
1
Per risolvere il problema dato impostiamo il foglio di calcolo così come compare:
11
Dal menù strumenti chiamiamo il componente aggiuntivo risolutore:
Comparirà una maschera di dialogo:
12
Cliccando su Risolvi dopo aver impostato come appare, si ottiene la soluzione cercata:
13
Vale a dire: X1=0,6029; X2=0,1520; X3=0,2451. Rendimento del portafoglio 13,2843 e varianza
minima 62,9901.
Le soluzioni sono le stesse indicate dal libro da cui è tratto l’esempio.
14
Il portafoglio composto con i pesi così determinati rappresenta dunque quello che si dice un
portafoglio efficiente ossia presenta varianza minima ad un dato rendimento e la determinazione di
qualunque altro portafoglio efficiente deve avere fissato un rendimento atteso , sempre varianza
minima.
Ad esempio fissando un rendimento atteso di m=15 si tratta di cercare il minimo della
200 100 − 40 x1
2
2
σ = σ ( x1, x2 , x3 ) ossia di ( x1 , x2 , x3 ) 100 400 20 x2 con i vincoli di ( x1 , x 2 , x 3 ) =1;
− 40 20 600 x3
∑ mi xi = 12 x1 + 14 x2 + 16 x3 = 15 ; e non essendo ammesse vendite allo scoperto di x1, x2 , x3 ≥ 0 .
A tal fine riprendiamo l’ultimo foglio di excel e chiamando in causa ancora una volta il componente
risolutore otteniamo aggiungendo il vincolo di rendimento fissato al valore di 15:
15
Otteniamo i valori di x1 = 0,0435; x2 = 0,4130; x3 = 0,5435. con un valore di varianza di 128,26.
16
Va da sé che continuando in questo modo potremo ottenere altre combinazioni media (rendimento
atteso) varianza (rischio).
E’ molto semplice ottenerli: basta, sempre nella maschera del risolutore, sostituire al vincolo del
rendimento atteso altri valori ai quali verranno automaticamente, dal programma di ottimizzazione,
associati le corrispettive varianze ( e quindi s.q.m. σ ).
Proviamo a dare altri valori al rendimento atteso di portafoglio ad esempio, 16 (valore maggiore
del portafoglio di varianza minima di rendimento atteso 15) e 13 (valore minore del portafoglio di
varianza minima di rendimento atteso 15).
Nota: nel caso del valore atteso di rendimento 16 per mantenere efficiente il portafoglio dobbiamo
ammettere vendite allo scoperto e perciò nella maschera del risolutore non imponiamo il vincolo di
positività ai pesi dei titoli.
17
18
19
appendice
Come creare una matrice varianza/covarianza con excel.
Si abbia una coppia di vettori V1 e V2 cosi composta:
1
3
V1 = 3 ;
5
4
3
3
V2 = 5 .
5
6
Vogliamo, partendo da tali vettori, creare una matrice varianza/covarianza.
Per far ciò introduciamo nel foglio di calcolo i vettori.
Chiamiamo adesso in causa con il comando f x la funzione covarianza.
20
Nella finestra di dialogo inseriamo i vettori in questione bloccando le celle
del riquadro “Matrice1” C5:C9 ( con f4 della tastiera del computer) e
cliccando su ok otterremo il valore 1,76 della prima covarianza (in questo
caso varianza tra il vettore V1 e se stesso).
Trasciniamo ora nella cella accanto la formula contenuta in C12 otteniamo
la covarianza tra il vettore V1 e V2.
21
Ripetiamo la stessa operazione partendo dal vettore V2 ottenendo prima la
covarianza tra V2 e V1.
22
e successivamente trascinando nella cella accanto otteniamo la varianza
tra V2 e se stesso, giungendo alla fine alla matrice varianza / covarianza.
23
Come diceva un famoso personaggio, una domanda sorge spontanea: esiste
un procedimento con excel, diciamo più immediato, per il calcolo della
matrice varianza /covarianza?
In effetti da strumenti selezioniamo il componente aggiuntivo analisi dati
dove v’è una funzione chiamata covarianza che potrebbe risultare utile
specie per il calcolo di matrici varianza/covarianza di una certa
dimensione( basti pensare che per calcolare tale matrice per n vettori
occorre una matrice di n*n elementi).
Calcoliamo con tale metodo la matrice in precedenza considerata.
24
25
impostata la maschera di dialogo così come compare e cliccando su ok
otteniamo la matrice di varianza/ covarianza desiderata.
Si evidenzia subito però un problema: dov’è l’elemento in alto a destra
della matrice?
Diciamo subito che una matrice varianza/covarianza è una matrice
simmetrica e pertanto il programma di excel per una sorta di risparmio non
riporta l’elemento in questione che data la simmetria in questione deve
essere 1,12.
Per risolvere il problema dopo avere selezionato la matrice incompleta,
chiamiamo in causa dal menù modifica, nell’ordine, copia e dopo aver
individuato la cella dove comparirà la nuova matrice trasposta,
selezioniamo incolla speciale spuntando come compare nella maschera le
opzioni salta celle vuote e trasponi (la cui logica per la simmetria anzidetta
è chiara)
26
cliccando su ok ed incollando nell’apposito spazio selezionato otteniamo
la matrice trasposta.
27
Ora si selezionino i dati trasposti e scegliere modifica, copia.
Selezionate la matrice originale e scegliere di nuovo modifica , incolla
speciale. Nella maschera di dialogo selezionare salta celle vuote e ciccare
su ok. Otteniamo in tal modo la matrice completa.
28
29
30
