Problema di ottimizzazione con Risolutore di Excel
I titoli più amati dai trader italiani: a tutta FAANG, ma a svettare è un’altra super big
Microsoft, Facebook, Apple. Ma anche Disney, Mastercard e Netflix. Sono solo alcuni dei titoli su cui gli investitori italiani e globali hanno puntato a giugno secondo eToro. Il portale di …
UBI boccia l’OPS di Intesa: non è conveniente per 6 motivi. Messina medita sul rilancio
L’ops lanciata da Intesa Sanpaolo non è conveniente. E’ il responso del cda odierno di UBI Banca, che conferma le attese della vigilia e apre le strade a due possibili …
BTP Futura, Mef alza velo su tassi cedolari minimi. Tesoro chiama investitori retail, la tentazione del premio fedeltà
ll Mef ha finalmente comunicato i tassi cedolari minimi garantiti della prima emissione del BTP Futura, il titolo di stato italiano offerto interamente agli investitori retail. L'emissione partirà lunedì 6 …
Tutti gli articoli
Tutti gli articoli Tutte le notizie

  1. #1

    Data Registrazione
    Nov 2017
    Messaggi
    47
    Mentioned
    0 Post(s)
    Quoted
    14 Post(s)
    Potenza rep
    3

    Problema di ottimizzazione con Risolutore di Excel

    Buonasera a tutti,

    è la prima volta che mi trovo a dover usare questo plug-in di Excel e ho difficoltà a capire come definire le celle del Risolutore. In particolare, vi propongo questo problema di ottimizzazione.

    Si consideri un'azienda che per garantire (nei prossimi 8 anni) il pre-pensionamento di un certo numero di impiegati deve sostenere ogni anno i seguenti costi:
    Per far fronte a queste uscite, l'azienda investe una certa somma F da ripartire nell’acquisto di titoli di Stato annuali con tasso di rendimento fisso del 4% annuo e tre obbligazioni B1, B2 e B3 con valore facciale di 1.000€ e i seguenti prezzi correnti, scadenze e rendimenti annui:
    1) Impostare il problema di ottimizzazione. 2) Calcolare il valore minimo di F che consente all'azienda di soddisfare tutte le sue obbligazioni di cassa.

    Dunque… Tutto ciò che l’azienda non investe nell’acquisto dei bond sarà investito in titoli di Stato, fermo restando che dovranno rimanere in cassa le somme necessarie a garantire il pre-pensionamento e che il suo obiettivo è minimizzare l’investimento da effettuare. Inizio impostando le variabili decisionali, che sono:
    - F = somma totale investita a copertura delle spese di prepensionamento per gli 8 anni successivi;
    - B1 = quantità di titoli del tipo B1;
    - B2 = quantità di titoli del tipo B2;
    - B3 = quantità di titoli del tipo B3;
    Chiamo poi Sx la quota di F investita nell’acquisto dei titoli di Stato nell’anno x=1,2,…,8. Bene. Io so che all’inizio del 1° anno l’azienda dispone di una certa somma F dalla quale, sottraendo il costo di acquisto dei tre bond (cioè la somma dei prodotti tra i prezzi unitari dei 3 titoli e le diverse quantità acquistate) e dei titoli di Stato S1, dovrà permanere una quota in cassa sufficiente a garantire piena copertura ai pre-pensionamenti previsti per l’anno corrente (che per il primo anno sono 430€). Operando similmente per gli anni successivi (ma tenendo presente che i ricavi derivanti dagli interessi sui titoli obbligazionari e sui bond acquistati l’anno precedente dovranno essere stornati dei costi di acquisto dei nuovi titoli di Stato acquistati l’anno in corso), il problema può essere così formalizzato:
    minF
    F-(1.150€*B1+1.000€*B2+1.350€*B3)-S1=430€
    (0,08875*B1+0,055*B2+0,1175*B3)+1,04*S1-S2=210€
    (0,08875*B1+0,055*B2+0,1175*B3)+1,04*S2-S3=222€
    (0,08875*B1+0,055*B2+0,1175*B3)+1,04*S3-S4=231€
    (0,08875*B1+0,055*B2+0,1175*B3)+1,04*S4-S5=240€
    (1,08875*B1+0,055*B2+0,1175*B3)+1,04*S5-S6=195€
    (1,055*B2+0,1175*B3)+1,04*S6-S7=225€
    (1,1175*B3)+1,04*S7-S8=255€

    E fin qui mi ritrovo con la soluzione (che come ogni buon esercizio preparatorio in vista dell'esame non ha alcun passaggio, né logico né tantomeno formale).
    Il problema è nel secondo punto. Questo è ciò che ho scritto sul foglio Excel…

    ...e questo è solo l'ultimo delle decine di tentativi fatti col Risolutore per ottenere uno straccio di risultato:

    Ho provato a scrivere i vincoli da C14 a C21 in termini di formula (e lasciando quindi #VALORE! al momento di inserire le Celle di Riferimento), ho provato a scrivere (come si vede dal fogli Excel) la colonna dei coefficienti (quasi certamente sballati) perchè su un esempio trovato in un manuale di Excel la risoluzione di un sistema di n equazioni in n incognite imponeva come Celle di Riferimento i valori dei coefficienti delle incognite e come vincoli il valore della funzione, ho provato a lasciare le Celle di Riferimento in bianco… Niente da fare: errore, errore, errore. Non se nemmeno se l'obiettivo impostato e l'elenco delle Celle variabili sia corretto.

    Vi pregherei quindi di aiutarmi a capire bene, una volta per tutte, come risolvere in Excel questo tipo di esercizi. O meglio, cosa inserire nelle varie voci del Risolutore e perché (magari facendo riferimento proprio all'esempio che ho scritto). Ringrazio tutti in anticipo!

  2. #2
    L'avatar di Paolo1956
    Data Registrazione
    Jun 2010
    Messaggi
    5,574
    Mentioned
    1 Post(s)
    Quoted
    1448 Post(s)
    Potenza rep
    42949683
    Hai fatto un pastrocchio, per capire il risolutore devi partire da qualcosa di più semplice. In rete trovi molti esempi semplici

  3. #3

    Data Registrazione
    Nov 2017
    Messaggi
    47
    Mentioned
    0 Post(s)
    Quoted
    14 Post(s)
    Potenza rep
    3
    Citazione Originariamente Scritto da Paolo1956 Visualizza Messaggio
    Hai fatto un pastrocchio, per capire il risolutore devi partire da qualcosa di più semplice. In rete trovi molti esempi semplici
    Ahimè ne ero consapevole ancor prima di postare il messaggio, sono ancora agli inizi. Ho provato a modificare il file Excel e questo è il risultato:Problema di ottimizzazione con Risolutore di Excel-immagine6.pngProblema di ottimizzazione con Risolutore di Excel-immagine7.pngProblema di ottimizzazione con Risolutore di Excel-immagine8.png

    Purtroppo però continua ad essere sbagliato perchè il risultato corretto è il seguente:

    Problema di ottimizzazione con Risolutore di Excel-immagine4.pngProblema di ottimizzazione con Risolutore di Excel-immagine5.png

    Cosa mi sfugge? Temo che l'errore sia nel definire l'investimento variabile nei titoli di Stato (Sx). Se infatti assumiamo quantità inizializzate ad 1 avremo Qx=1 con x={1,2,3,Sx}, e quindi per il primo anno:
    - B1=1*1000€=1000€;
    - B2=1*1000€=1000€;
    - B3=1*1000€=1000€;
    - S1=1*1000€=1000€
    da cui segue
    - 0,08875*B1=88,75€;
    - 0,0555*B2=55,5€;
    - 0,1175*B3=117,5€;
    - 1,04*S1=1040€ (con gli interessi sui titoli di Stato calcolati in regime di capitalizzazione composta: (1+0,04)^(1)).
    Tuttavia, mentre banalmente la quantità acquistata dei tre titoli obbligazionari B1, B2, B3 rimarrà costante per tutta la durata del piano d'investimento (permettendomi così di replicare in ogni vincolo le formule di cui sopra), la quantità acquistata di titoli di Stato Sx varia di anno in anno in maniera apparentemente incognita.
    La sola cosa certa (come potete vedere dalla formalizzazione del problema) è che dagli interessi maturati sui tre titoli obbligazionari relativi al 1° anno, maggiorati degli interessi sui titoli di Stato maturati nello medesimo anno (1,04), devono essere detratti i costi aleatori sostenuti nell'anno successivo per l'acquisto di una "nuova" quantità di titolo di Stato (da cui se ne deduce maturity annuale).
    Il tutto dovrà poi essere (come giustamente hai detto) >= del vincolo di cassa.

    Dunque per il secondo anno avremo
    (0,08875*B1+0,0555*B2+0,1175*B3)+1,04*S1-S2 >=210€

    dove S2 è incognito.

    Ritieni sia corretto porre {S2, S3, S4, S5, S6, S7, S8}=S1 per ogni vincolo? Ovvero a dire lasciare in ogni vincolo:

    …+1,04*1*1000€-(1*1000€)>=…


    Il Risolutore ricalcolerà automaticamente per ogni anno la nuova quantità di titoli di Stato acquistata?

  4. #4
    L'avatar di Paolo1956
    Data Registrazione
    Jun 2010
    Messaggi
    5,574
    Mentioned
    1 Post(s)
    Quoted
    1448 Post(s)
    Potenza rep
    42949683
    Direi di sì…


    Cioè per seguirti dovrei rifare il foglio; però ad occhio essendo i rendimenti crescenti con la duration, ad ogni anno il residuo dovrebbe essere zero, cioè il problema mi sembra in effetti lineare. Così a naso eh….

  5. #5

    Data Registrazione
    Nov 2017
    Messaggi
    47
    Mentioned
    0 Post(s)
    Quoted
    14 Post(s)
    Potenza rep
    3
    Citazione Originariamente Scritto da Paolo1956 Visualizza Messaggio
    Direi di sì…


    Cioè per seguirti dovrei rifare il foglio; però ad occhio essendo i rendimenti crescenti con la duration, ad ogni anno il residuo dovrebbe essere zero, cioè il problema mi sembra in effetti lineare. Così a naso eh….
    Grazie ancora per il tuo interessamento Paolo. Considera che sono 5 giorni che sono assolutamente bloccato su questo, e non riesco in alcun modo ad andare avanti col lavoro. Tuttavia come vedi il risultato non viene: dovrebbe essere F=1708,66€. So disperato, non capisco dove cavolo sia l'errore Secondo te cosa dovrei modificare?

  6. #6
    L'avatar di Paolo1956
    Data Registrazione
    Jun 2010
    Messaggi
    5,574
    Mentioned
    1 Post(s)
    Quoted
    1448 Post(s)
    Potenza rep
    42949683
    Ma nel tuo modello il prezzo delle obbligazioni dove va a finire?

    Prendiamo la Obblig. 1: rende l'8,875 nominale e costa 1150 (ogni 1000 € nominali) presumo…….
    Quindi se tu investi 1150 hai l'8,875 *1000/1150 …. o no?

  7. #7
    L'avatar di Paolo1956
    Data Registrazione
    Jun 2010
    Messaggi
    5,574
    Mentioned
    1 Post(s)
    Quoted
    1448 Post(s)
    Potenza rep
    42949683
    Poi:

    al termine dell'ottavo anno tu paghi 255. Se l'obbligazione più lunga dura 7 anni dal 7 all'8 compri un titolo a un anno. E allora come fa S8 a essere 0 ?

  8. #8

    Data Registrazione
    Nov 2017
    Messaggi
    47
    Mentioned
    0 Post(s)
    Quoted
    14 Post(s)
    Potenza rep
    3
    Citazione Originariamente Scritto da Paolo1956 Visualizza Messaggio
    Ma nel tuo modello il prezzo delle obbligazioni dove va a finire?

    Prendiamo la Obblig. 1: rende l'8,875 nominale e costa 1150 (ogni 1000 € nominali) presumo…….
    Quindi se tu investi 1150 hai l'8,875 *1000/1150 …. o no?
    Paolo, grazie ancora per le tue risposte. Magari da questo confronto si può accendere la famosa lampadina
    Esatto, il taglio delle obbligazioni non defaultabili è di 1000€ ognuna. Ma se assumiamo che sia le obbligazioni che i titoli vengano acquistati in quantità intere Qx, l'acquisto di 10 Obbligazioni di tipo 1 costerà 11500€ ma l'interesse restituito sarà 10*1000*0.08875=887.5€ secondo valore facciale. Ergo, nella prima condizione del problema si storna dalla somma iniziale investita F (incognita) il costo delle 10 obbligazioni (vale a dire F - 1.150€ * B1, con B1 = Q1 * VN), mentre nella seconda si considerano quali flussi di cassa positivi gli interessi maturati da tali obbligazioni (vale a dire 0,08875 * B1). Ne segue che:

    F-(1.150€*B1+1.000€*B2+1.350€*B3)-S1=430€
    (0,08875*B1+0,055*B2+0,1175*B3)+1,04*S1-S2=210€


    La formalizzazione del problema di minimizzazione ne esce corretta (dai uno sguardo al mio post precedente).
    Mi mette in crisi la quantità di bond di Stato acquistati anno dopo anno (S1, S2, …, S8), ognuno di essi incognito, e di conseguenza l'intera traduzione in linguaggio Excel sia della Cella obiettivo che dei vincoli a cui tale obiettivo è soggetto. Non saprei davvero, c'è qualcosa che mi sfugge

  9. #9

    Data Registrazione
    Nov 2017
    Messaggi
    47
    Mentioned
    0 Post(s)
    Quoted
    14 Post(s)
    Potenza rep
    3
    Citazione Originariamente Scritto da Paolo1956 Visualizza Messaggio
    Poi:

    al termine dell'ottavo anno tu paghi 255. Se l'obbligazione più lunga dura 7 anni dal 7 all'8 compri un titolo a un anno. E allora come fa S8 a essere 0 ?
    Ma infatti la costruzione è senza dubbio errata: non può essere zero.

  10. #10

    Data Registrazione
    Sep 2015
    Messaggi
    6
    Mentioned
    0 Post(s)
    Quoted
    0 Post(s)
    Potenza rep
    0

    Problema correlazione

    Ciao a tutti, mi accodo a questo thread perché mi sembra quello più adatto al mio problema.

    Con Excel ho fatto una classica simulazione di massimizzazione dell'indice di Sharpe di un portafoglio con tre ETF, selezionati in funzione della correlazione "il più negativa possibile".
    Ho scaricato i NAV storici giornalieri dei tre ETF dai rispettivi siti ufficiali; siccome voglio tutti gli stimatori annualizzati (se utilizzo termini impropriamente, vi prego di essere un po' elastici e cercare di capire i concetti che cerco di esprimere ahah), ho calcolato i rispettivi rendimenti medi annui (se al 31/12/2019 il prezzo è 120€ e al 31/12/2014 il prezzo era 100€, ho fatto "(120/100)^(1/5)-1=3.71%"), le rispettive varianze e deviazioni standard dei rendimenti medi annui e le varie covarianze e correlazioni sempre dei rendimenti medi annui.
    Ho costruito la matrice varianza-covarianza, impostato il risolutore affinché mi restituisse i rispettivi pesi che massimizzassero l'indice di Sharpe e quindi ottenuto rendimento atteso e deviazione standard del portafoglio, tutto in maniera molto "scolastica" e basilare; correggetemi se ho fatto qualche castroneria (e ne ho fatte di sicuro!).
    Una volta eseguiti questi passaggi e completato il modello, ho pensato che, così facendo, ho tralasciato praticamente tutti i dati giornalieri scaricati, utilizzando soltanto i prezzi al 31/12 di ogni anno e buttando al vento tutte le informazioni contenute nei dati giornalieri! In questo modo, ad esempio, se al 31/12/2019 il titolo risulti essere cresciuto del 10% rispetto al 31/12 dell'anno precedente, non posso avere idea di COME ci sia arrivato a quel +10%: c'è arrivato in maniera costante e regolare? C'è arrivato dopo mesi di picchi alti e bassi??
    Il mio problema è proprio questo e ho pensato ad un modo di proseguire che però non mi torna e sul quale nutro parecchi dubbi e infatti sono bloccato!
    La mia idea è quella di inserire le varianze medie annue (calcolate utilizzando i rendimenti giornalieri e annualizzate; non più, quindi, utilizzando la varianza dei rendimenti annui) e le "covarianze medie annue" (stesso discorso della parentesi precedente sulle varianze) nella matrice varianza-covarianza ed utilizzare questa nuova matrice var-cov per calcolare la varianza attesa del portafoglio tramite gli stessi procedimenti utilizzati con la vecchia matrice var-cov. È una cosa matematicamente possibile? Vi spiego nei dettagli cosa mi è venuto in mente di fare.
    Innanzitutto calcolo tutti i rendimenti giornalieri (es. "(Pt/Pt-1)-1", dove Pt rappresenta il prezzo al tempo t e Pt-1 il prezzo al tempo t-1), poi calcolo le deviazioni standard in questo modo: prendo i rendimenti giornalieri di un anno, ad esempio quelli del 2016, calcolo la deviazione standard di questi rendimenti e la moltiplico per la radice quadrata del numero delle osservazioni giornaliere considerate nel calcolo (circa 252), stessa cosa per l'anno 2017, 2018 etc.; in questo modo trovo le deviazioni standard dei rendimenti di ogni anno.

    DOMANDA 1: Se voglio ottenere la media annua delle deviazioni standard, posso sommare le deviazioni standard di ogni anno e dividerle per il numero di anni? È una castroneria matematica o è corretto?

    DOMANDA 2: Come posso fare ad ottenere lo stesso risultato per la covarianza e la correlazione lineare sulla base della stessa logica utilizzata per annualizzare le deviazioni standard degli anni in analisi e calcolarne la media annua?
    In altri termini: se ho a disposizione rendimenti giornalieri, che formula posso usare (se esiste) per trasformare covarianza e correlazione ottenute con questi rendimenti giornalieri, in covarianza e correlazione annualizzate allo stesso modo in cui si può tranquillamente fare coi rendimenti e come credo di poter fare con la deviazione standard? Posso moltiplicarle per la radice quadrata del numero delle osservazioni giornaliere considerate (circa 252) come ho fatto con la deviazione standard (sarebbe un po' come un discorso sulle equivalenze tra unità di misura della stessa categoria: come faccio a trasformare i centimetri in metri?)?
    Se tutto questo fosse possibile, otterrei covarianza e correlazione del 2016, quelle del 2017, del 2018, etc.; se sommassi rispettivamente queste covarianze e correlazioni e le dividessi per il numero di anni, otterrei una "covarianza media annua" e una "correlazione media annua" esattamente come ho fatto per la deviazione standard? O è l'ennesima castroneria impensabile?

    Ho tutti questi dubbi perché, non volendo rinunciare ad analizzare i dati giornalieri, nella matrice varianza-covarianza non posso inserire le varianze medie annue insieme alle covarianze espresse senza il fattore di annualizzazione; mi spiego meglio: se inserisco le varianze medie annue, poi non posso utilizzare le covarianze calcolate sui rendimenti giornalieri dell'INTERO periodo di analisi come ad esempio i NAV storici di dieci anni; ma anche se il periodo di analisi fosse un solo anno e continuassi a voler utilizzare gli stimatori espressi in anni, come potrei costruire la matrice var-cov con le varianze (calcolate utilizzando il quadrato delle deviazioni standard dei rendimenti giornalieri moltiplicate per la radice quadrata del numero dei rendimenti utilizzati) e le covarianze calcolate utilizzando i rendimenti giornalieri senza poterle "annualizzare" come ho fatto sia coi rendimenti che con le deviazioni standard??

    Spero di essere riuscito a spiegarmi nonostante il mio linguaggio poco tecnico e ripetitivo.

    DOMANDA BONUS: Visto che i rendimenti medi si calcolano con la media geometrica, perché mai si dice che uno degli indicatori più comuni di volatilità utilizzato sia la deviazione standard? La deviazione standard calcola la media degli scarti DALLA MEDIA ARITMETICA dei rendimenti, quindi la media degli scarti da un numero che non corrisponde al rendimento medio che si calcola, appunto, con la media geometrica!
    Detto questo, esiste una sorta di "deviazione standard geometrica"?? Ahahah mi viene da ridere per tutte le cavolate che ho detto ma spero davvero di essere riuscito a far capire quello che intendo dire.

    Un immenso grazie a chi mi risponderà e a chi riuscirà a schiarirmi un po' le idee.

Accedi