Sapevate che è possibile utilizzare Excel per calcolare il rimborso dei prestiti? Questo articolo ti accompagnerà attraverso tutti i passaggi necessari per farlo. (Vedi anche: Calcolatori ipotecari: come funzionano .)

Utilizzando Excel, è possibile ottenere una migliore comprensione della tua ipoteca in tre semplici passaggi. Il primo passo è quello di determinare il pagamento mensile. Il secondo è quello di scoprire il tasso di interesse, e il terzo è trovare il programma di prestito. A tal fine, è possibile creare una tabella in Excel che ti dirà: le tariffe più elevate; il calcolo del prestito per la durata; decomposizione di un prestito, nonché ammortamenti e calcoli per affitto mensile.

Calcolo prestiti per affitto mensile

In primo luogo, vediamo come implementare il calcolo di un pagamento mensile per un mutuo. In altre parole, usando il tasso annuo di interesse, il capitale e la durata, possiamo determinare l'importo da rimborsare mensilmente.

La formula, come mostrato nella schermata precedente, viene 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 prestatore preso in prestito. Gli ultimi due argomenti sono facoltativi, il valore residuo di default è 0, pagabile in anticipo (per 1) o alla fine (per 0), è anche facoltativo.

La formula 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 che usiamo il periodo del tasso, che è il tasso mensile, il numero di periodi (mesi qui 120 per 10 anni moltiplicato per 12 mesi) e infine indichiamo il prestito preso in prestito. Il nostro pagamento mensile sarà $ 1, 161. 88 in 10 anni.

Calcolo ipoteca per tassi d'interesse

Abbiamo visto come impostare il calcolo di un pagamento mensile per un mutuo. Ma possiamo voler impostare un pagamento mensile massimo che possiamo permetterci che mostra anche il numero di anni in cui dovremmo rimborsarlo. Per questo motivo, vorremmo sapere il corrispondente tasso di interesse annuo.

Calcolare il tasso di interesse per un prestito

Come mostrato nella schermata precedente, calcoliamo la tariffa periodica (mensile nel nostro caso) e quindi il tasso annuo. La formula utilizzata sarà RATE, come mostrato in schermata sopra, è scritto come segue:

= RATE (Nper; pmt; present_value; [future_value]; [tipo])

I primi tre argomenti sono la durata di il prestito (numero di periodi) e il pagamento mensile per rimborsare il prestito preso in prestito. Gli ultimi tre argomenti sono facoltativi e il valore residuo di default è 0, l'argomento termine per la gestione della scadenza in anticipo (per 1) o alla fine (per 0) è anche facoltativo e infine l'argomento di stima è opzionale, ma può dare una stima iniziale del tasso.

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

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

Nota: il pagamento mensile deve essere dato un segno negativo. Ecco perché un segno meno prima della formula. Il nostro periodo di tasso è 0. 294%.

Utilizziamo la formula

= (1 + B5) è 12-1 ^ = (1 +0, 294%) ^ 12-1 per ottenere il tasso annuo del nostro prestito essere 3. 58 %. In altre parole, per prendere in prestito 120.000 $ oltre 13 anni per pagare mensilmente 960 $ dovremmo negoziare un prestito ad un tasso annuo massimo del 3,58%. Calcolo ipoteca per la durata di un prestito

Ora vediamo come ottenere la durata di un prestito quando conosci il tasso annuo, il debito principale e il pagamento mensile da rimborsare. In altre parole, quanto tempo dobbiamo rimborsare un'ipoteca di 120.000 dollari con un tasso del 3, 10% e un pagamento mensile di $ 1, 100?

Numero di rimborsi per un prestito

La formula utilizzata è NPER, come mostrato nella schermata precedente e viene scritta come segue:

= NPER (tasso; pmt; present_value; [future_value]; [tipo])

I primi tre argomenti sono il tasso annuo del prestito, il pagamento mensile necessario per rimborsare il prestito e il prestito preso in prestito. Gli ultimi due argomenti sono facoltativi, il valore residuo è impostato a 0, l'argomento termine pagabile in anticipo (per 1) o alla fine (per 0) è anche facoltativo. (1 + 3, 10%) ^ (1/12) -1, -1100, 120000) = NPER ((1 + B2) ^ (1/12) -1, -B4; B3)

Nota: i dati corrispondenti nel pagamento mensile devono essere segnalati come segno negativo. Ecco perché abbiamo un segno meno prima della formula. La durata del rimborso è 127. 97 periodi (mesi nel nostro caso).

Utilizzeremo 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, dobbiamo ripagare scadenze per 128 mesi o 10 anni e 8 mesi.

Decomposizione del prestito

Il pagamento di un prestito consiste in due cose, il principale e l'interesse. L'interesse viene calcolato per ogni periodo, ad esempio i rimborsi mensili di oltre 10 anni, ci darà 120 periodi.

Lo screenshot sopra mostra la ripartizione 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 ripartiti come segue:

= - PPMT (tasso; num_period; lunghezza; principal; [residua]; [terme])

= - INTPER (velocità; num_period; [argomento]

Gli argomenti sono gli stessi per la formula PMT vista nella prima parte, ad eccezione di num_period che viene aggiunto per mostrare il periodo in cui decidere il prestito, dando il principale e l'interesse per esso. Prendiamo un esempio:

= - PPMT ((1 + 3, 10%) ^ (1/12) -1; 1; B4 * 12; B3) = PPMT (1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = INTPER ((1 + 3, 10, 10, 12, 120000) Il risultato è quello mostrato nella schermata "Decomposizione del prestito", nel periodo analizzato che è "1", in modo che il primo periodo (1/12) -1, 1, 10 * 12, 120000 , o il primo mese.Per questo paghiamo $ 1161. 88, suddivisi in $ 856, 20 principal e $ 305. 68 interesse.

Excel Compensation Loan

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

= - CUMPRINC (rate, lunghezza, principal, start_date, end_date; type)

= - CUMIPMT (rate, lunghezza, principal; start_date; end_date; principio e 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. Il primo indica l'inizio del periodo da analizzare e la seconda la fine. Prendiamo un esempio:

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

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

= - CUMIPMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000; 1; 12; 0)

uno mostrato nella schermata "Cumul 1st year", quindi i periodi analizzati variano da 1 a 12, dal primo periodo (primo mese) al dodicesimo (dodicesimo mese). Più di un anno, avremmo pagato $ 10 419, 55 Principal e $ 3 522. 99 Interessi.

Ammortamento del prestito

Le formule precedenti ci permettono di creare il nostro periodo di pianificazione per periodo, quanto paghiamo mensilmente in capitale e interesse e quanto rimane a pagare.

Crea un programma di prestiti in Excel

Per creare un programma di prestiti, utilizzeremo formule diverse discusse sopra e li espanderemo sul numero di periodi.

Nella prima colonna di periodo, basta inserire "1" come primo periodo, quindi trascinare la cella verso il basso. Nel nostro caso, abbiamo bisogno di 120 periodi a partire da un pagamento di 10 anni moltiplicato per 12 mesi = 120.

La seconda colonna è l'importo mensile che dobbiamo pagare ogni mese, che è costante su tutto il programma di prestiti. Per calcolarlo, inserire la formula seguente 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 principale che verrà rimborsato mensilmente. Ad esempio, per il 40 ° periodo, rimborseremo $ 945. Per calcolare l'importo principale rimborsato utilizziamo la seguente formula:

= - PPMT (TP; A18; $ B $ 4 * 12; $ B $ 3) = La quarta colonna è l'interesse per il quale calcoliamo il rimborso del rimborso sul nostro importo mensile per scoprire come molto interesse deve essere pagato, utilizzando la formula:

= - INTPER (TP; A18; $ B $ 4 * 12; $ B $ 3) = -INTPER ((1 + 3, 10%) ^ (1/12) ; 1; 10 * 12; 120000)

La quinta colonna contiene l'importo 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)

= 120000 + CUMPRINC ((1 + 3, 10%) ^ (1/12); 10 * 12; 120000; 1; 1; 0)

La formula usa una combinazione di principale in un periodo in avanti con la cella contenente il prestito preso in prestito. Questo periodo inizia a cambiare quando si copia e trascina la cella in giù.Lo screenshot seguente mostra che alla fine di 120 periodi il nostro prestito è rimborsato.