Appunti di Excel per risolvere
alcuni problemi di matematica
(II parte)
a.a. 2001-2002
Daniela Favaretto*
[email protected]
Stefania Funari*
[email protected]
*Dipartimento di Matematica Applicata
Università Ca’Foscari di Venezia
Indice
UN BREVE RIPASSO
3
IL MONTANTE E IL VALORE ATTUALE
14
AMMORTAMENTO A QUOTE COSTANTI DI CAPITALE
19
REGRESSIONE LINEARE
23
FILTRARE DATI DA UN ELENCO
30
I PROBLEMI DI PROGRAMMAZIONE LINEARE
37
13
IL MONTANTE E IL VALORE ATTUALE
1.
Il montante
Data una successione di importi monetari R1,…,Rk,…., Rn, che si verificano alle
epoche 1,..k,…,n, il montante alla scadenza n si calcola come:
M = R1 (1 + i )
n −1
+ R2 (1 + i )
n−2
+ .... + Rn .
ESEMPIO
Un’azienda prevede i seguenti ricavi annuali espressi in Euro:
anno
importo
1
3200
2
3890
3
4200
4
2500
5
4020
Assumendo un tasso di valutazione annuale effettivo del 8%, calcolare i valori dei
flussi di cassa a scadenza (montante).
Dobbiamo ottenere un foglio come quello delle figure 6 e 6a.
I valori relativi alle variabili del problema sono contenuti nelle seguenti celle.
I dati e il risultato
Nelle celle A3, A4, A5, A6 e A7 digitiamo i numeri 1, 2, 3, 4, 5 rispettivamente.
Nelle celle B3, B4, B5, B6 e B7 digitiamo i flussi di cassa corrispondenti.
Selezioniamo la cella B9. Nella barra dei menu scegliamo Formato e poi Celle….
Nella finestra di dialogo che compare, scegliamo Percentuale e in Posizioni decimali
scriviamo 2. Confermiamo con OK. Digitiamo 8 e poi battiamo INVIO.
Selezioniamo la cella C11. Digitiamo la formula
=B7+B6*(1+B9)+B5*(1+B9)^2+B4*(1+B9)^3+B3*(1+B9)^4
e poi battiamo INVIO.
Osservazione
Si usa il simbolo * per la moltiplicazione, ^ per l’elevamento a potenza, / per la divisione.
Nella casella C11 della figura 3a leggiamo la soluzione: il montante cercato è 20872.72
Euro.
14
Il problema iniziale è risolto, ma con il foglio ottenuto possiamo risolvere tutti i problemi
dello stesso tipo.
Figura 6-6a. Il foglio per il calcolo del montante dei flussi di cassa (formula-risultati).
15
Osservazione
Nella cella B9 abbiamo scritto il tasso in forma percentuale e non ci siamo poi
preoccupati di trasformarlo nel tasso unitario i presente nella formula del montante.
Questo perché, quando si sceglie il formato Percentuale, Excel scrive il dato in forma
percentuale ma lo usa automaticamente in forma unitaria. Per esempio, se vediamo scritto
8% nella cella B9, per applicare la formula precedente, Excel al posto del valore scritto in
B9 mette 0.08.
2.
Il valore attuale
Data una successione di importi monetari R1,…,Rk,…., Rn, che si verificano alle
epoche 1,..k,…,n, il valore attuale in t = 0 si calcola come:
V = R1 (1 + i ) + R2 (1 + i ) + .... + Rn (1 + i ) .
−1
−2
−n
ESEMPIO
Utilizzando Excel calcolare il valore attuale dei seguenti 6 flussi di cassa al tasso
annuo effettivo del 6.5%:
anno
importo
1
3200
2
3890
3
4200
4
2500
5
4020
6
5000
.
Dobbiamo ottenere un foglio come quello della figura 7.
Nella casella C12 (figura 4a) leggiamo la soluzione: il montante cercato è 18215.41
Euro.
Il problema iniziale è risolto, ma con il foglio ottenuto possiamo risolvere tutti i
problemi dello stesso tipo.
16
17
Figura 7-7a.
Il foglio per il calcolo del valore attuale (formula-risultati).
Per calcolare il valore attuale netto di un progetto si può utilizzare la funzione finanziaria
VAN di Excel. La funzione si presenta nella forma
VAN(tasso_int; valore1; valore2;…,valoren)
dove gli argomenti della funzione hanno il seguente significato:
tasso_int
valore1,…,valoren
tasso di interesse relativo all’unità temporale considerata,
importi periodici R1, …., Rn riscossi o pagati nelle diverse epoche.
Per risolvere l’esercizio utilizzando la funzione finanziaria VAN basta selezionare la cella
C12, digitare la formula
=VAN(B10;B3:B8)
e premere INVIO. Si ottiene come risultato 18215.41.
Figura 7b. Uso della funzione VAN per il calcolo del valore attuale netto
18
AMMORTAMENTO A QUOTE COSTANTI DI CAPITALE
Si consideri il problema di redigere il piano di ammortamento di un debito di
ammontare S che deve essere restituito in n anni, secondo un ammortamento a quote
costanti di capitale. Si utilizza il regime di capitalizzazione composta al tasso di interesse
annuo i. Indicato con:
k
Ck
Ik
Rk
Dk
Ek
generico anno (k = 0,…,n),
quota capitale (costante) in k,
quota interesse in k,
rata di ammortamento in k,
debito residuo in k,
debito estinto in k,
ricordiamo che, nel caso in cui i pagamenti vengono corrisposti posticipatamente al
termine di ciascuno degli n anni, fra le grandezze del piano di ammortamento valgono le
seguenti relazioni:
Rk = Ck + Ik
Ik = iDk-1
Dk = Dk-1 - Ck
Ek = S - Dk
(1)
(2)
(3)
(4)
Inoltre la quota (costante) di capitale può calcolarsi come
Ck =
S
n
k = 1,2,..., n
ESEMPIO
Vogliamo ammortizzare un prestito di 10000 Euro al 6% annuo in 8 anni con il
metodo a quote costanti di capitale (ammortamento italiano). Utilizzando Excel
costruisci il piano di ammortamento completo
Dobbiamo ottenere un foglio come quello della figura 8.
19
Figura 8-8a. Il foglio per il calcolo del piano di ammortamento a quote costanti di
capitale (formule-risultati).
20
I valori relativi alle variabili del problema sono contenuti nelle seguenti celle:
Somma ottenuta in prestito (debito)
Numero delle rate
Tasso di interesse
Quota capitale
B2
B3
B4
B6
I dati e il calcolo della quota capitale
Selezioniamo la cella B2: digitiamo 10000 e poi battiamo il tasto INVIO.
Selezioniamo la cella B3: digitiamo 8 e poi battiamo INVIO.
Selezioniamo la cella B4: digitiamo 6 (formato Percentuale) e poi battiamo
INVIO.
Selezioniamo la cella B6: digitiamo la formula
=B3/B2
e poi battiamo INVIO.
Nella cella B6 leggiamo l’importo della quota capitale: 1250 Euro.
Il problema di determinare la quota capitale è risolto; si tratta ora di redigere il piano
di ammortamento.
I dati e i risultati
Compiliamo la prima colonna (quella degli anni): selezioniamo la cella A9 e
digitiamo il numero 0; selezioniamo la cella A10 e digitiamo =A9+1 e
premiamo INVIO; selezioniamo la cella A10, posizioniamo il cursore in basso
a destra della cella selezionata e trasciniamo il cursore fino alla cella A17. In
questo modo si copierà =A10+1 sulla cella A11, =A11+1 sulla cella A12, … e
=A16+1 sulla cella A17.
Compiliamo la terza colonna (quella delle quote capitale): selezioniamo le
celle C10, C11, …, C17 e in ognuna digitiamo =B6. Dopo ogni digitazione
battiamo il tasto INVIO. Anziché digitare =B6 in ogni cella dalla C10 alla
C17, è possibile scrivere =B$6 nella cella C10 e battere invio; poi selezionare
la cella C10, posizionare il cursore in basso a destra della cella selezionata e
trascinare il cursore fino alla cella C17. In questo modo si copierà =B$6 su
tutte le celle dalla C11 alla C17. Il “$” tra B e 6 serve a non fare incrementare
l’indice di riga nel trascinamento.
Compiliamo la quinta colonna (quella del debito estinto): dopo aver scritto in
E9 10000, selezioniamo la cella E10 e digitiamo =E9-C10; selezioniamo poi la
cella E11 e digitiamo =E10-C11; procediamo così fino alla cella E17, in cui
digitiamo =E19-C17. Dopo ogni digitazione battiamo INVIO. Anziché
scrivere la formula in ogni cella dalla E10 alla E17, basta selezionare la cella
21
E10, digitare =E9-C10 e battere INVIO. Successivamente si tratta di
selezionare E10 e trascinare il valore con il cursore fino alla cella E17.
Compiliamo la sesta colonna (quella del debito residuo): selezioniamo la cella
F10 e digitiamo =F9-E10; selezioniamo poi la cella F11 e digitiamo =F9-E11;
continuiamo così fino alla cella F17 in cui digitiamo =F11-E17. Dopo ogni
digitazione battiamo INVIO. Anziché scrivere la formula in ogni cella dalla
F10 alla F17, basta selezionare la cella F10, digitare =F$9-E10 e battere
INVIO. Successivamente si tratta di selezionare F10 e trascinare il valore con
il cursore fino alla cella F17. In questo modo l’indice di riga di F9 resterà
immutato, mentre l’indice di riga di E10 aumenterà di una unità ad ogni cella.
Compiliamo la quarta colonna (quella della quota interessi): selezioniamo la
cella D10 e digitiamo =E9*B4; selezioniamo poi la cella D11 e digitiamo
=E10*B4; continuiamo così fino alla cella D17 in cui digitiamo =E16*B4.
Dopo ogni digitazione battiamo INVIO. Anziché scrivere la formula in ogni
cella dalla D10 alla D17, basta selezionare la cella D10, digitare =E9*B$4 e
battere INVIO. Successivamente si tratta di selezionare D10 e trascinare il
valore con il cursore fino alla cella D17.
Compiliamo infine la seconda colonna (quella della rata): selezioniamo la cella
B10 e digitiamo =C10+D10; selezioniamo poi la cella B11 e digitiamo
=C11+D11; procediamo così fino alla cella B17, in cui digitiamo =C17+D17.
Dopo ogni digitazione battiamo INVIO. Anziché scrivere la formula in ogni
cella dalla B10 alla B17, basta selezionare la cella B10, digitare =C10+D10 e
battere INVIO. Successivamente si tratta di selezionare B10 e trascinare il
valore con il cursore fino alla cella B17.
Attenzione che tutte le celle, tranne quelle della prima colonna, devono essere nel
formato Valuta.
Osservazione
Ricordiamo che per ottenere il formato Valuta bisogna selezionare la cella e nella
barra dei menu scegliere Formato e poi Celle… e, nella finestra di dialogo che
compare, scegliere Valuta; per confermare scegliere OK.
A questo punto il piano di ammortamento è completato e risulta esattamente quello
in figura 8a.
22
