Piano di ammortamento di un mutuo

Stampa
( 2 Votes ) 
Valutazione attuale:  / 2
ScarsoOttimo 
Categoria: Informatica
Data pubblicazione
Scritto da Magellano Visite: 3280

Piano di ammortamento di un mutuo

In questo primo esempio di un problema risolto con Excel si fa ricorso alla Ricerca obiettivo, strumento molto potente ed utile in molte applicazioni.
Quando si contrae un mutuo con una banca, la sua restituzione avviene col pagamento di rate annuali o semestrali di importo costante. Inizialmente il debito che abbiamo con la banca è alto ma va diminuendo col passare degli anni grazie ai pagamenti effettuati. Contemporaneamente però stiamo pagando anche gli interessi sul debito residuo, che sono tanto più alti quanto più alto è questo debito. Come mai allora la rata è costante?



La banca calcola con delle formule di matematica finanziaria una quota di capitale che stiamo restituendo ed una quota di interessi; la somma di queste due quote è costante.

Senza conoscere queste formule di matematica finanziaria, che peraltro sono già comprese tra le funzioni di Excel, proviamo a ricavarci il piano di ammortamento, cioè la tabella che ci dà per ogni anno la quota capitale e la quota interessi, con un semplice ragionamento. Per semplicità immagineremo di pagare delle rate annuali; il caso delle rate semestrali verrà ricavato alla fine.

Apriamo quindi Excel e cominciamo a scrivere il testo seguente nelle celle:

A1:  ANNO
B1: QUOTA INTERESSE
C1: QUOTA CAPITALE
D1: CAPITALE RESIDUO

- Selezioniamo la cella A1 col mouse e, tenendo premuto il pulsante, trasciniamo fino alla cella D1

oppure da tastiera:

- Con i tasti freccia andiamo sulla cella A1 e poi con shift premuto ci spostiamo sulla D1

Ora clicchiamo sui pulsanti per la centratura del testo e per il grassetto. Poi inseriamo il testo seguente nelle celle:

F1: CAPITALE=
F2: TASSO=
F3: PERIODI=
F5: RATA=

Selezioniamo le quattro celle e mettiamo come colore di sfondo il giallo; si noti che le celle non sono contigue, quindi occorre premere ctrl, trascinare prima il puntatore sulle tre celle adiacenti e poi cliccare sulla quarta cella per ottenere una selezione non contigua.

Inseriamo dei valori di prova in:

G1: 70000, formattato con separatore delle migliaia (pulsante 000, a fianco a quello %)
G2: 0,05, formattato con pulsante %, o si scrive direttamente 5%
G3: 20
G5: 3500, formattato come G1

Scriviamo ora il valore 0 in A3, 1 in A4 e selezioniamo le due celle: questo rettangolo di selezione ha un quadratino in basso a destra; trasciniamo il quadratino verso il basso per ottenere automaticamente il riempimento della prima colonna con i valori in progressione aritmetica stabilita dalla differenza dei valori selezionati, quindi intervallati di una unità. Riempiamo fino a 30.
(se il quadratino di riempimento manca, controllare in Strumenti/Opzioni e spuntare Trascina cella)
La riga 2 è riservata ai totali.

A questo punto possiamo inserire le formule.

L’anno 0 corrisponde all’istante di contrazione del mutuo, quindi dobbiamo inserire come capitale residuo l’importo del prestito.
La formula da inserire nella D3 è:
=G1
Ciò può essere fatto in vari modi:

1. sulla barra della formula (se non è visibile si veda il menu Visualizza) si inserisce la formula =G1
2. doppio clic sulla cella D3 e si inserisce la formula
3. doppio clic sulla cella D3, si scrive =  e si fa clic sulla cella G1, infine tasto Invio

Nelle celle relative alla fine del primo anno inseriremo le formule seguenti:

B4: =D3*$G$2 (interesse = debito residuo x tasso di interesse)
C4: =$G$5-B4 (quota capitale = rata - quota interesse)
D4: =D3-C4 (debito residuo (n) = debito residuo (n-1) - quota capitale)

Queste tre formule andranno copiate e incollate nelle analoghe celle delle righe successive.

Si noti il simbolo $: esso serve per fare in modo che quando copieremo le formule nelle righe successive, esse facciano riferimento sempre alla stessa cella; se il simbolo è davanti alla coordinata di riga, solo questa rimarrà inalterata; analogamente per la coordinata di colonna; se è davanti a tutte e due le coordinate, la formula farà riferimento sempre alla stessa cella.

Non c’è bisogno di inserire a mano il simbolo $: una volta inserita la formula, ad esempio cliccando sulla cella a cui ci riferiamo (terza modalità di inserimento citata precedentemente), si preme più volte il tasto F4; in questo modo vengono passate in rassegna le quattro possibilità.

Per incollare le tre formule nelle righe successive possiamo:

1. selezionare le tre celle B3, C3, e D3 della riga 3 e copiare la riga
(per copiare la riga si può fare clic sul pulsante Copia, si può scegliere Copia dal menu Modifica, si può da tastiera premere ctrl+c)

poi selezionare tutte le celle delle altre righe e incollare
(per copiare la riga si può fare clic sul pulsante Incolla, si può scegliere Incolla dal menu Modifica, si può da tastiera premere ctrl+v)

oppure

2. selezionare le tre celle B3, C3, e D3 e trascinare il quadratino di riempimento in basso a destra del rettangolo di selezione fino all’ultima riga, relativa all’anno 30.

Si noti come i riferimenti assoluti rimangono invariati (quelli col simbolo $), mentre i riferimenti relativi vengono aggiornati; in pratica i riferimenti relativi guardano le celle che hanno una stessa posizione rispetto alla cella che contiene la formula.

Se una cella a cui si riferisce una formula viene trascinata in altra posizione, la formula viene automaticamente corretta per mantenersi agganciata a tale cella.

Selezioniamo infine le celle da A3 ad A33 e mettiamo un bordo spesso (pulsante bordi, aprire il menu cliccando sulla freccetta a fianco); analogamente per le celle da B2 a D2, per le quali metteremo anche uno sfondo giallo, e per le celle da B3 a D33.

A questo punto il foglio di calcolo dovrebbe presentarsi come in figura:





Click this bar to view the full image.

 

 

 

Si noti che il debito residuo non cala mai! Questo è ovvio in quanto si sta pagando una rata che è esattamente pari a quanto dobbiamo pagare di interessi, quindi non stiamo restituendo un bel niente di capitale.

Proviamo allora ad aumentare la rata.
Con 4000 euro avremmo ancora da restituire quasi 37 mila euro dopo 30 anni.
Con 5000 euro avremmo quasi estinto il debito al 24-esimo anno, per poi andare a credito!
Per tentativi potremmo ricavare l’importo della rata che rende nullo il debito residuo al 20-esimo anno.

C’è però un modo molto efficiente e rapido per ottenere ciò: far fare questa ricerca ad Excel. In sostanza dobbiamo trovare lo zero di una funzione, come se si risolvesse un’equazione.

Si va su Strumenti/Ricerca obiettivo…
I puntini indicano che si aprirà una finestra di dialogo: in essa occorre inserire alcune informazioni:

- Imposta la cella: quale cella contiene il valore obiettivo
- Al valore: quale valore si vuole ottenere
- Cambiando le celle: quale cella occorre cambiare per ottenere ciò

Nella prima si mette D23, nella seconda 0, nella terza G5.
Non occorre inserire i riferimenti alle celle, basta cliccare su di esse quando si è posizionati sulla casella di testo.
Magicamente Excel porterà la cella G5 al valore di 5617 euro, in modo tale da avere in D23 il valore zero. Prima di chiudere la finestra di dialogo, ricordarsi di cliccare su Ok per accettare la soluzione.

Infine inseriamo la sommatoria dei valori contenuti nella seconda e nella terza colonna:

Posizioniamoci sulla cella B2, clicchiamo sul pulsante col simbolo della sommatoria (la lettera graca sigma); nella cella compare la scritta: =SOMMA(), senza alcun riferimento all’intervallo di celle. Si clicca allora sulla cella B3 e, tenendo premuto il pulsante sinistro, si trascina fino alla cella B23; automaticamente nella cella verrà scritta la formula:

=SOMMA(B3:B23)

Non c’è bisogno di ripetere la procedura per la cella C2: basta posizionarsi in B3 e trascinare il quadratino di riempimento.


Si noti che il capitale complessivamente restituito è proprio 70000 euro ma abbiamo anche pagato ben 42340 euro di interessi, per un totale di 112340 euro che coincidono con 20 rate da 5617 euro l’una.

Il lavoro a questo punto è terminato; possiamo renderlo più presentabile eliminando le righe che non ci servono e provare con importi e tassi diversi.

 


Nella seconda parte renderemo il foglio di calcolo più flessibile, in modo da poter visualizzare automaticamente solo le righe corrispondenti al numero di anni impostati, visualizzeremo un istogramma dell’andamento degli importi pagati, ricaveremo il piano di ammortamento con rate semestrali e verificheremo i risultati trovati con le formule di matematica finanziaria, comprese tra le funzioni standard di Excel.

Joomla 1.7 Templates designed by College Jacke