Pianificare i rimborsi di prestiti con formule di Excel

Il rimborso del prestito è l'atto di rimborsare 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 i rimborsi dei prestiti?

Questo articolo è una guida dettagliata per l'impostazione dei calcoli del prestito.

Key Takeaways

• Usa Excel per ottenere una gestione del tuo mutuo determinando il tuo pagamento mensile, il tuo tasso di interesse e il tuo programma di prestiti.
• Puoi dare uno sguardo più approfondito alla ripartizione di un prestito con Excel e creare un programma di rimborso adatto a te.
• Sono disponibili calcoli per ogni passaggio che è possibile modificare per soddisfare le esigenze specifiche.
• Abbattere ed esaminare il prestito passo dopo passo può rendere il processo di rimborso meno travolgente e più gestibile.

Comprensione del mutuo

Usando Excel, puoi ottenere una migliore comprensione del tuo mutuo in tre semplici passaggi. Il primo passo determina il pagamento mensile. Il secondo passo calcola il tasso di interesse e il terzo passo determina il programma di prestito.

Puoi creare una tabella in Excel che ti dirà il tasso di interesse, il calcolo del prestito per la durata del prestito, la decomposizione del prestito, l'ammortamento e il pagamento mensile.

Calcola il pagamento mensile

Innanzitutto, ecco come calcolare il pagamento mensile per 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 sopra, è scritta come segue:

= -PMT (tasso; lunghezza; valore_attuale; [valore_futuro]; [tipo])

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 viene impostato automaticamente su zero; pagabile in anticipo (per uno) o alla fine (per zero), è anche facoltativo.

La formula di Excel utilizzata per calcolare il pagamento 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 di un pagamento mensile per un mutuo. Ma potremmo voler impostare un pagamento mensile massimo che possiamo permetterci che mostri anche il numero di anni durante i quali dovremmo rimborsare il prestito. Per tale motivo, vorremmo conoscere il tasso di interesse annuale corrispondente.

Come mostrato nello screenshot sopra, prima calcoliamo la tariffa del periodo (mensile, nel nostro caso), quindi la tariffa annuale. La formula utilizzata sarà RATE, come mostrato nello screenshot sopra. È scritto come segue:

= RATE (Nper; PMT; valore_attuale; [valore_futuro]; [tipo])

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 viene impostato automaticamente su zero; anche il termine argomento per gestire la scadenza in anticipo (per uno) o alla fine (per zero) è facoltativo. Infine, l'argomento della stima è facoltativo ma può fornire una stima iniziale della tariffa.

La formula di Excel utilizzata per calcolare il tasso di prestito è:

= TASSO (12 * B4; -B2; B3) = TASSO (12 * 13; -960; 120000)

Nota: i dati corrispondenti nel pagamento mensile devono essere contrassegnati con 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 annuale del nostro prestito, che è del 3, 58%. In altre parole, per prendere in prestito $ 120.000 in 13 anni per pagare $ 960 al mese, dovremmo negoziare un prestito ad un tasso massimo annuale del 3, 58%.

L'utilizzo di Excel è un ottimo modo per tenere traccia di ciò che devi e elaborare una pianificazione per il 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 si conoscono il tasso annuale, il capitale preso in prestito e il pagamento mensile che deve essere rimborsato. In altre parole, per quanto tempo dovremo 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 è scritto come segue:

= NUM.RATE (tasso; PMT; valore_attuale; [valore_futuro]; [tipo])

I primi tre argomenti sono il tasso annuale 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 viene impostato automaticamente su zero. Anche il termine argomento pagabile in anticipo (per uno) o alla fine (per zero) è facoltativo.

= NPER ((1 + B2) ^ (1/12) -1; -B4; B3) = NPER ((1 + 3, 10%) ^ (1/12) -1; -1100; 120000)

Nota: i dati corrispondenti nel pagamento mensile devono essere contrassegnati con 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 al mese, dovremmo rimborsare le scadenze per 128 mesi o 10 anni e 8 mesi.

Decomposizione del prestito

Un pagamento del prestito è composto da capitale e interessi. L'interesse viene calcolato per ciascun periodo, ad esempio i rimborsi mensili nell'arco di 10 anni ci daranno 120 periodi.

La tabella sopra mostra la suddivisione 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 (tasso; num_period; lunghezza; principale; [residua]; [termine])

Gli argomenti sono gli stessi della formula PMT già vista, ad eccezione di "num_period", che viene aggiunto per mostrare il periodo in cui suddividere il prestito in base al capitale e agli 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 nello screenshot sopra "Decomposizione prestito" nel periodo analizzato, che è "uno;" cioè, il primo periodo o il primo mese. Paghiamo $ 1.161, 88 suddivisi in $ 856, 20 capitale e $ 305, 68 interessi.

Calcolo del prestito in Excel

È anche possibile calcolare il rimborso del capitale e degli interessi per diversi periodi, come i primi 12 mesi o i primi 15 mesi.

= -CUMPRINC (tasso; lunghezza; principale; data_inizio; data_finale; tipo)

Troviamo gli argomenti, la velocità, 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". "Start_date" indica l'inizio del periodo da analizzare e "end_date" indica la fine del periodo da analizzare.

Ecco un esempio:

= -CUMPRINC ((1 + B2) ^ (1/12) -1; B4 * 12; B3; 1; 12; 0)

Il risultato è mostrato nello screenshot "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 in 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 prestiti

Per creare un programma di prestiti, 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 equivale a 120.

La seconda colonna è l'importo mensile che dobbiamo pagare ogni mese, che è costante per l'intero programma di prestito. Per calcolare l'importo, inserisci la seguente formula nella cella del nostro primo periodo:

= -PMT (TP-1; 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 linea di massima sul nostro importo totale mensile di $ 1.161, 88.

Per calcolare l'importo principale 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 utilizziamo 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 rimasto 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 alla fine di 120 periodi viene rimborsato il nostro prestito.