Pianifica i rimborsi del prestito con le formule Excel
Il rimborso del prestito è l’atto di rimborsare il denaro precedentemente preso in prestito da un prestatore, in genere attraverso una serie di pagamenti periodici che includono capitale più interessi. Sapevi che puoi utilizzare il programma software Excel per calcolare il rimborso del tuo prestito?
Questo articolo è una guida passo passo per impostare i calcoli del prestito.
Considerazioni chiave:
- Usa Excel per gestire il tuo mutuo determinando il pagamento mensile, il tasso di interesse e la pianificazione del prestito.
- Puoi dare uno sguardo più approfondito alla ripartizione di un prestito con Excel e creare un piano di rimborso che funzioni per te.
- Sono disponibili calcoli per ogni passaggio che puoi modificare per soddisfare le tue esigenze specifiche.
- Analizzare ed esaminare il tuo prestito passo dopo passo può rendere il processo di rimborso meno opprimente e più gestibile.
Capire il tuo mutuo
Utilizzando Excel, puoi ottenere una migliore comprensione del tuo mutuo in tre semplici passaggi. Il primo passaggio determina il pagamento mensile. Il secondo passaggio calcola il tasso di interesse e il terzo passaggio determina la pianificazione del prestito.
Puoi costruire una tabella in Excel che ti dirà il tasso di interesse, il calcolo del prestito per la durata del prestito, la scomposizione del prestito, l’ammortamento e la rata mensile.
Calcola il pagamento mensile
Innanzitutto, ecco come calcolare la rata mensile di un mutuo. Utilizzando il tasso di interesse annuale, il capitale e la durata, possiamo determinare l’importo da rimborsare mensilmente.
La formula, come mostrato nello screenshot qui sopra, è scritta come segue:
= -PMT (rate; length; present_value; [future_value]; [type])
Il segno meno davanti a PMT è necessario poiché la formula restituisce un numero negativo. I primi tre argomenti sono il tasso del prestito, la durata del prestito (numero di periodi) e il capitale preso in prestito. Gli ultimi due argomenti sono facoltativi, il valore residuo predefinito è zero; è facoltativo anche il pagamento anticipato (per uno) o alla fine (per zero).
La formula Excel utilizzata per calcolare la rata mensile del prestito è:
= PMT ((1 + B2) ^ (1/12) -1; B4 * 12; B3) = PMT ((1 + 3,10%) ^ (1/12) -1; 10 * 12; 120000)
Spiegazione: Per il tasso, utilizziamo il tasso mensile (periodo del tasso), quindi calcoliamo il numero di periodi (120 per 10 anni moltiplicato per 12 mesi) e, infine, indichiamo il capitale preso in prestito. Il nostro pagamento mensile sarà di $ 1.161,88 in 10 anni.
Calcola il tasso di interesse annuale
Abbiamo visto come impostare il calcolo della rata mensile di un mutuo. Ma potremmo voler impostare un pagamento mensile massimo che possiamo permetterci che mostri anche il numero di anni in cui dovremmo rimborsare il prestito. Per questo motivo, vorremmo conoscere il tasso di interesse annuo corrispondente.
Come mostrato nello screenshot qui sopra, calcoliamo prima il tasso del periodo (mensile, nel nostro caso), quindi il tasso annuale. La formula utilizzata sarà RATE, come mostrato nello screenshot qui sopra. È scritto come segue:
= RATE (Nper; pmt; present_value; [future_value]; [type])
I primi tre argomenti sono la durata del prestito (numero di periodi), il pagamento mensile per rimborsare il prestito e il capitale preso in prestito. Gli ultimi tre argomenti sono facoltativi e il valore residuo predefinito è zero; anche il termine argomento per gestire la scadenza in anticipo (per uno) o alla fine (per zero) è facoltativo. Infine, l’argomento stima è facoltativo ma può fornire una stima iniziale del tasso.
La formula Excel utilizzata per calcolare il tasso di prestito è:
= TARIFFA (12 * B4; -B2; B3) = TARIFFA (12 * 13; -960; 120000)
Nota: i dati corrispondenti nella rata mensile devono essere contrassegnati da un segno negativo. Questo è il motivo per cui c’è un segno meno prima della formula. Il periodo di tasso è dello 0,294%.
Usiamo la formula = (1 + B5) è 12-1 ^ = (1 + 0,294%) ^ 12-1 per ottenere il tasso annuo del nostro prestito, che è 3,58%. In altre parole, per prendere in prestito $ 120.000 in 13 anni per pagare $ 960 mensilmente, dovremmo negoziare un prestito a un tasso massimo annuo del 3,58%.
L’utilizzo di Excel è un ottimo modo per tenere traccia di ciò che devi e elaborare un programma di rimborso che riduca al minimo le commissioni che potresti dover pagare.
Determinazione della durata di un prestito
Vedremo ora come determinare la durata di un prestito quando conoscerai il tasso annuo, il capitale preso in prestito e il pagamento mensile che deve essere rimborsato. In altre parole, per quanto tempo avremo bisogno di rimborsare un mutuo di $ 120.000 con un tasso del 3,10% e un pagamento mensile di $ 1.100?
La formula che useremo è NPER, come mostrato nello screenshot sopra, ed è scritta come segue:
= NPER (rate; pmt; present_value; [future_value]; [type])
I primi tre argomenti sono il tasso annuo del prestito, il pagamento mensile necessario per rimborsare il prestito e il capitale preso in prestito. Gli ultimi due argomenti sono facoltativi, il valore residuo predefinito è zero. Anche il termine argomento pagabile in anticipo (per uno) o alla fine (per zero) è facoltativo.
= NUMERO NUMERO ((1 + B2) ^ (1/12) -1; -B4; B3) = NUMERO NUMERO ((1 + 3,10%) ^ (1/12) -1; -1100; 120000)
Segno meno prima della formula
I dati corrispondenti nel pagamento mensile devono essere contrassegnati da un segno negativo. Questo è il motivo per cui abbiamo un segno meno prima della formula. La durata del rimborso è di 127,97 periodi (mesi nel nostro caso).
Useremo la formula = B5 / 12 = 127,97 / 12 per il numero di anni per completare il rimborso del prestito. In altre parole, per prendere in prestito $ 120.000, con un tasso annuo del 3,10% e pagare $ 1.100 mensilmente, dovremmo rimborsare le scadenze per 128 mesi o 10 anni e otto mesi.
Scomporre il prestito
Il pagamento di un prestito è composto da capitale e interessi. L’interesse viene calcolato per ogni periodo: ad esempio, i rimborsi mensili su 10 anni ci daranno 120 periodi.
La tabella sopra mostra la scomposizione di un prestito (un periodo totale pari a 120) utilizzando le formule PPMT e IPMT. Gli argomenti delle due formule sono gli stessi e sono suddivisi come segue:
= -PPMT (rate; num_period; length; principal; [residual]; [term])
Gli argomenti sono gli stessi della formula PMT già vista, ad eccezione di “num_period”, che viene aggiunto per indicare il periodo su cui scomporre il prestito dato capitale e interessi. Ecco un esempio:
= -PPMT ((1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = PPMT ((1 + 3,10%) ^ (1/12) -1; 1; 10 * 12; 120000)
Il risultato è mostrato nella schermata sopra “Scomposizione del prestito” nel periodo analizzato, che è “uno”; cioè il primo periodo o il primo mese. Paghiamo $ 1.161,88 suddivisi in $ 856,20 di capitale e $ 305,68 di interessi.
Calcolo del prestito in Excel
È anche possibile calcolare il rimborso del capitale e degli interessi per più periodi, come i primi 12 mesi oi primi 15 mesi.
= -CUMPRINC (rate; length; principal; start_date; end_date; type)
Troviamo gli argomenti, il tasso, la lunghezza, il capitale e il termine (che sono obbligatori) che abbiamo già visto nella prima parte con la formula PMT. Ma qui, abbiamo bisogno anche degli argomenti “start_date” e “end_date”. “Data_inizio” indica l’inizio del periodo da analizzare e “data_fine” indica la fine del periodo da analizzare.
Ecco un esempio:
= -COMPRINC ((1 + B2) ^ (1/12) -1; B4 * 12; B3; 1; 12; 0)
Il risultato è mostrato nella schermata “Cumul 1 ° anno”, quindi i periodi analizzati vanno da uno a 12 del primo periodo (primo mese) al dodicesimo (12 ° mese). In un anno, pagheremmo $ 10.419,55 in capitale e $ 3.522,99 di interessi.
Ammortamento del prestito
Le formule precedenti ci consentono di creare il nostro programma periodo per periodo, di sapere quanto pagheremo mensilmente in capitale e interessi e di sapere quanto resta da pagare.
Creazione di un programma di prestito
Per creare un programma di prestito, utilizzeremo le diverse formule discusse sopra e le espanderemo nel numero di periodi.
Nella prima colonna del periodo, inserisci “1” come primo periodo, quindi trascina la cella verso il basso. Nel nostro caso, abbiamo bisogno di 120 periodi poiché un pagamento del prestito di 10 anni moltiplicato per 12 mesi è uguale a 120.
La seconda colonna è l’importo mensile che dobbiamo pagare ogni mese, che è costante per l’intera durata del prestito. Per calcolare l’importo, inserisci la seguente formula nella cella del nostro primo periodo:
= -PMT (TP; B4 * 12; B3) = -PMT ((1 + 3,10%) ^ (1/12) -1; 10 * 12; 120000)
La terza colonna è il capitale che verrà rimborsato mensilmente. Ad esempio, per il 40 ° periodo, rimborseremo $ 945,51 in capitale sul nostro importo totale mensile di $ 1.161,88.
Per calcolare l’importo del capitale rimborsato, utilizziamo la seguente formula:
= -PPMT (TP; A18; $ B $ 4 * 12; $ B $ 3) = -PPMT ((1 + 3,10%) ^ (1/12); 1; 10 * 12; 120000)
La quarta colonna è l’interesse, per il quale usiamo la formula per calcolare il capitale rimborsato sul nostro importo mensile per scoprire quanti interessi devono essere pagati:
= -INTPER (TP; A18; $ B $ 4 * 12; $ B $ 3) = -INTPER ((1 + 3,10%) ^ (1/12); 1; 10 * 12; 120000)
La quinta colonna contiene l’importo rimanente da pagare. Ad esempio, dopo il 40 ° pagamento, dovremo pagare $ 83.994,69 su $ 120.000.
La formula è la seguente:
= $ B $ 3 + CUMPRINC (TP; $ B $ 4 * 12; $ B $ 3; 1; A18; 0)
La formula utilizza una combinazione di capitale in un periodo precedente alla cella contenente il capitale preso in prestito. Questo periodo inizia a cambiare quando copiamo e trasciniamo la cella verso il basso. La tabella seguente mostra che al termine di 120 periodi, il nostro prestito viene rimborsato.