Il rimborso di un prestito può avvenire con differenti modalità che dipendono, principalmente, dalla composizione della “quota capitale” e “quota interessi” delle singole rate. In questo tutorial proponiamo un modello per calcolare il piano di ammortamento di un finanziamento a “rate costanti“.
Attraverso tale metodo, che richiede l’applicazione di alcune funzioni tipicamente finanziarie, la rata di rimborso è composta da una quota capitale che cresce con il numero delle rate e una quota interessi che diminuisce nel tempo.
Come utilizzare il modello
Per generare automaticamente il piano di ammortamento è sufficiente inserire nel modello solo pochi dati; in particolare, l’importo del prestito nella cella C3, il tasso di interesse annuale (che per ipotesi rimane costante per tutta la durata del prestito) nella cella C4, la durata in anni del finanziamento nella cella E3, il numero di rate da rimborsare in un anno nella cella E4 ed, infine, la data dalla quale vengono conteggiati gli interessi (la data iniziale del prestito) nella cella G3.
Come costruire il modello
Nella cella E4 è stato creato un elenco a discesa per evitare l’inserimento di valori numerici non consentiti; si è, pertanto, utilizzato il comando “Convalida” del menu “Dati” e nella casella di testo “Origine”, presente all’interno della scheda “Impostazioni” della finestra “Convalida dati”, sono stati inseriti i numeri 1, 2, 3, 4, 6 e 12 separati da un punto e virgola.
Per creare un piano di ammortamento che si adatti al numero di rate del prestito, è stata inserita una serie numerica, da 1 a 50 nella colonna A a partire dalla cella A7 mentre nella cella B7 è stata utilizzata la formula
=SE(E($C$3<>””;$C$4<>””;$E$3<>””;$E$4<>””;$G$3<>””;$E$3*$E$4>=A7);A7;””)
La funzione SE, attraverso l’operatore logico E, verifica simultaneamente se sono stati inseriti i dati iniziali e se il numero di rate complessive del prestito, dato dalla moltiplicazione della durata in anni per il numero di rate in un anno (E3*E4) è maggiore o uguale al numero contenuto nella cella A7 e restituisce quest’ultimo valore; la formula, ricopiata verso il basso fino alla cella B56 visualizza, pertanto, una “cella vuota” dal momento in cui il numero progressivo delle rate del prestito supera il valore inserito nella corrispondente cella della colonna A (quest’ultima colonna verrà nascosta in fase di utilizzazione del modello).
Per determinare le scadenze delle singole rate si è utilizzata nella cella C7 la formula:
=SE(E($C$3<>””;$C$4<>””;$E$3<>””;$E$4<>””;$G$3<>””;B7<>””);DATA(ANNO($G$3);MESE($G$3)+B7*12/$E$4;GIORNO($G$3));””)
Attraverso la funzione SE, ricopiata successivamente fino alla cella C56, si controlla che tutte le celle di input e la cella B7 contengano un valore (questa struttura verrà ripetuta anche per le formule successive e, pertanto, eviteremo di soffermarci nuovamente) e si calcolano, per mezzo della funzione DATA, le scadenze delle singole rate. (Per maggiori approfondimenti sull’utilizzo della funzione DATA consultare l’editoriale sul piano di ammortamento di un prestito a quote di capitale costanti).
Per determinare l’importo costante delle singole rate (colonna D del prospetto) è necessario ricorrere alla funzione finanziaria RATA.
La funzione è composta da cinque argomenti ma, quando viene utilizzata per calcolare una rata di un prestito a pagamento posticipato (le ipotesi del nostro modello), sono sufficienti tre; la sintassi “semplificata” della funzione è, pertanto:
=RATA(tasso_int;periodi;val_attuale)
L’argomento tasso_int indica il tasso di interesse, espresso in forma percentuale, praticato per il prestito, periodi indica il numero complessivo delle rate mentre valore attuale indica l’importo del prestito.
Nota: i primi due argomenti della funzione devono fare riferimento ad uno stesso “arco temporale”: così, ad esempio, se le rate sono semestrali, anche il tasso deve essere “convertito” da annuale in semestrale (è sufficiente dividere il tasso per due).
Esempio pratico
Facciamo un esempio pratico per illustrare meglio quest’ultimo concetto.
Supponiamo di voler calcolare la rata costante di un prestito di 10.000 euro concesso, ad un tasso del 10% annuo, per un periodo di 5 anni e da rimborsare a rate quadrimestrali. Poiché in un anno ci sono tre quadrimestri il tasso di interesse annuale va diviso per 3 mentre le rate complessive, espresse sempre in periodi quadrimestrali, sono 15 (tre rate all’anno per 5 anni); pertanto la funzione da utilizzare sarà:
=RATA(10%/3;5*3;10000)
Determinare la rata
Torniamo al modello. Per determinare la rata costante del prestito nella cella D7 è stata utilizzata la funzione:
=SE(E($C$3<>””;$C$4<>””;$E$3<>””;$E$4<>””;$G$3<>””;B7<>””);-RATA($C$4/$E$4;$E$3*$E$4;$C$3);””)
L’argomento tasso_int della funzione RATA è dato dalla divisione del tasso annuale (cella C4) per il numero di rate in un anno (E4); l’argomento periodi è dato dalla moltiplicazione tra gli anni del prestito (E3) e il numero di rate annuali (E4), mentre l’argomento valore_attuale è dato dall’importo del prestito (C3); da notare che poiché il risultato della funzione RATA viene restituito con un segno negativo (in quanto si tratta di un debito) è stato necessario anteporre ad essa il trattino del meno in maniera che, nel prospetto, venga riproposto un valore positivo. La formula è stata successivamente copiata fino alla cella D56.
Nella colonna E e F sono state scisse le singole rate nella quota capitale e nella quota interessi.
Quota capitale
Per determinare la quota capitale di una rata costante si deve ricorrere alla funzione finanziaria P.RATA la cui sintassi “semplificata” è la seguente:
=P.RATA(tasso_int;periodo;periodi;val_attuale)
Gli argomenti della funzione sono simili a quelli della funzione RATA (valgono, pertanto, le stesse considerazioni fatte in precedenza); nella funzione P.RATA viene aggiunto (come secondo in ordine di argomenti) l’argomento “periodo” che indica la rata della quale si intende calcolare la quota capitale (si ricorda che attraverso questo metodo finanziario la quota capitale cresce nel tempo).
Nella cella E7 si è utilizzata quindi la formula:
=SE(E($C$3<>””;$C$4<>””;$E$3<>””;$E$4<>””;$G$3<>””;B7<>””);-P.RATA($C$4/$E$4;B7;$E$3*$E$4;$C$3);””)
=> Piano di ammortamento prestiti con Excel
Come secondo argomento della funzione P.RATA è stato richiamato il valore della cella B7 che contiene, appunto, la rata di riferimento del modello (copiando verso il basso la formula, fino alla cella E56, la funzione P.RATA restituirà dei valori differenti in quanto l’argomento “periodo” farà riferimento alle rate successive alla prima) mentre, gli altri argomenti della funzione fanno riferimento alle stesse celle utilizzate nella funzione RATA.
Quota interessi
La “quota interessi” della rata costante può essere calcolata semplicemente per differenza tra il valore complessivo della rata e il valore della “quota capitale” oppure, in alternativa, utilizzando la funzione finanziaria INTERESSI.
La sintassi della funzione INTERESSI è, sempre nella forma semplificata, la seguente:
=INTERESSI(tasso_int;periodo;periodi;val_attuale)
Come si può osservare, gli argomenti della funzione sono identici a quelli di P.RATA e, non necessitano, quindi, di alcun commento.
Ne segue che nella cella F7 è stata utilizzata (e successivamente copiata fino alla cella F56) una formula simile a quella inserita nella cella E7, e precisamente:
=SE(E($C$3<>””;$C$4<>””;$E$3<>””;$E$4<>””;$G$3<>””;B7<>””);-INTERESSI($C$4/$E$4;B7;$E$3*$E$4;$C$3);””)
Per determinare il debito residuo in corrispondenza della prima rata, nella cella G7 è stata utilizzata la formula:
=SE(E($C$3<>””;$C$4<>””;$E$3<>””;$E$4<>””;$G$3<>””;B7<>””);C3-E7;””)
che calcola la differenza tra l’importo del prestito (C3) e la “quota capitale” versata con la prima rata (E7).
Per calcolare il debito residuo in corrispondenza della seconda rata è stata utilizzata nella cella G8 la formula:
=SE(E($C$3<>””;$C$4<>””;$E$3<>””;$E$4<>””;$G$3<>””;B8<>””);G7-E8;””)
che sottrae al debito precedente (G7) e la quota capitale della seconda rata (E8); la funzione è stata copiata, infine, fino alla cella G56.
Il modello è stato completato, infine, con alcune operazioni di formattazione. È stata nascosta la colonna A e si è utilizzata la formattazione condizionale per assegnare dei bordi soltanto le celle l’intervallo B7:G56 che mostrano, di volta in volta, i valori numerici (quest’ultimo accorgimento consente di visualizzare il prospetto in maniera compatta).
Come personalizzare il modello
Il modello è stato costruito prevedendo un massimo di cinquanta rate; nel caso in cui si deve procedere alla creazione di un piano di ammortamento con rate maggiori è sufficiente selezionare l’intervallo A56:G56 (dopo aver visualizzato la colonna nascosta A) e copiare verso il basso con il quadratino di riempimento.
