Calcolo della rendita annuale in Excel

Prima di prendere il prestito, sarebbe bello calcolare tutti i pagamenti su di esso. Salverà il mutuatario in futuro da vari problemi e delusioni inaspettati quando si scopre che il pagamento eccessivo è troppo grande. Aiuto su questo calcolo può gli strumenti del programma Excel. Scopriamo come calcolare i pagamenti della rendita sul prestito in questo programma.

Calcolo del pagamento

Prima di tutto, si deve dire che ci sono due tipi di pagamenti di credito:

• Differenziato;
• Annualità rendita.

Con uno schema differenziato, il client offre una quota mensile pari di pagamenti sul corpo di un prestito più pagamenti a interessi. I pagamenti di grande titolo di interesse crescono ogni mese, poiché il corpo del prestito è ridotto da cui sono calcolati. Pertanto, anche il pagamento mensile complessivo è ridotto.

Ann Zunt Scheme utilizza un approccio leggermente diverso. Il cliente fa un singolo importo del pagamento totale mensile, che consiste in pagamenti sul corpo di un prestito e al pagamento degli interessi. Inizialmente, i contributi di interessi sono numerati sull'intero importo del prestito, ma poiché il corpo diminuisce, l'interesse è ridotto e interessante accumulabile. Ma la quantità totale di pagamento rimane invariata a causa di un aumento mensile della quantità di pagamenti da parte del corpo del prestito. Pertanto, nel tempo, la percentuale di interesse per il pagamento mensile totale cade e il peso della proporzione del corpo cresce. Allo stesso tempo, il pagamento mensile generale non è cambiata durante il periodo di credito.

Proprio sul calcolo del pagamento della rendita, ci fermeremo. Soprattutto, questo è rilevante, dal momento che ora la maggior parte delle banche utilizza questo particolare schema. È conveniente per i clienti, perché in questo caso la quantità totale di pagamento non cambia, rimanendo fisso. I clienti sanno sempre quanto devi pagare.

Passaggio 1: calcolo del contributo mensile

Per calcolare un contributo mensile quando si utilizza un circuito di rendita in EXCELLE, c'è una funzione speciale - PPT. Si riferisce alla categoria degli operatori finanziari. La formula di questa funzione è la seguente:

= PPT (tasso; Kper; PS; BS; TIPO)

Come vediamo, la funzione specificata ha un numero abbastanza elevato di argomenti. È vero, gli ultimi due di loro non sono obbligatori.

L'argomento "tasso" indica un tasso percentuale per un periodo specifico. Se, ad esempio, viene utilizzato un tasso annuo, ma il pagamento del prestito viene effettuato mensilmente, quindi il tasso annuale deve essere diviso in 12 e il risultato è usato come argomento. Se viene applicato un tipo di pagamento trimestrale, in questo caso la scommessa annuale deve essere divisa in 4, ecc.

"CPER" indica il numero totale di periodi di pagamento dei prestiti. Cioè, se il prestito è preso per un anno con il pagamento mensile, il numero di periodi è considerato 12, se due anni, il numero di periodi - 24. Se il prestito è preso per due anni con un pagamento trimestrale, quindi Il numero di periodi è 8.

"PS" indica il valore attuale al momento. Parlando con parole semplici, questa è l'importo totale del prestito all'inizio del prestito, cioè l'importo che è stato preso in prestito, escluso interesse e altri pagamenti aggiuntivi.

"BS" è un costo futuro. Questo valore sarà un organismo di prestito al momento del completamento dell'accordo di prestito. Nella maggior parte dei casi, questo argomento è "0", poiché il mutuatario alla fine del periodo di credito dovrebbe sistemarsi completamente con il creditore. L'argomento specificato non è obbligatorio. Pertanto, se è sceso, è considerato zero.

L'argomento "Type" determina il tempo di calcolo: alla fine o all'inizio del periodo. Nel primo caso, prende il valore "0" e nel secondo - "1". La maggior parte delle istituzioni bancarie utilizzano esattamente l'opzione con il pagamento alla fine del periodo. Questo argomento è anche facoltativo, e se viene omesso, si ritiene che sia zero.

Ora è il momento di passare a un esempio specifico di calcolare un contributo mensile usando la funzione PL. Per calcolare, utilizziamo una tabella con i dati di origine, in cui è indicato il tasso di interesse sul prestito (12%), il valore del prestito (500.000 rubli) e il periodo di prestito (24 mesi). Allo stesso tempo, il pagamento viene effettuato mensilmente alla fine di ogni periodo.

1. Selezionare l'elemento sul foglio in cui verrà visualizzato il risultato dei risultati e fare clic sull'icona "Inserisci funzione", posizionata vicino alla riga della formula.



2. La finestra del wizard della finestra viene lanciata. Nella categoria "Financial" alloca il nome "PLT" e fare clic sul pulsante "OK".



3. Successivamente, apre la finestra Argomenti dell'operatore PL.

   Nel campo "VELOCE", è necessario inserire il valore percentuale per il periodo. Questo può essere fatto manualmente, basta mettere una percentuale, ma è indicato in una cella separata sul foglio, quindi daremo un legame ad esso. Installare il cursore nel campo, quindi fare clic sulla cella corrispondente. Ma, come ricordiamo, abbiamo il tasso di interesse annuale nel nostro tavolo e il periodo di pagamento è uguale al mese. Pertanto, dividiamo la scommessa annuale e piuttosto un collegamento alla cella in cui è contenuto dal numero 12, corrispondente al numero di mesi nell'anno. Divisione Esegui direttamente nel campo della finestra degli argomenti.

   Nel campo CPER, il prestito è impostato. È uguale a 24 mesi. È possibile applicare nel campo Manuale numero 24, ma, come nel caso precedente, specificare un collegamento alla posizione di questo indicatore nella tabella di origine.

   Nel campo "PS" indica il valore del prestito iniziale. È uguale a 500.000 rubli. Come nei casi precedenti, specifichiamo un collegamento all'elemento foglia, che contiene questo indicatore.

   Nel campo "BS" indica la grandezza del prestito, dopo il suo pagamento completo. Come ti ricordi, questo valore è quasi sempre zero. Installa in questo campo il numero "0". Sebbene questo argomento possa generalmente essere omesso.

   Nel campo "Tipo", specifichiamo all'inizio o alla fine del mese di pagamento. Noi, come nella maggior parte dei casi, è prodotto alla fine del mese. Pertanto, impostiamo il numero "0". Come nel caso dell'argomento precedente, è possibile inserire qualsiasi cosa a questo campo, il programma predefinito si suppone che sia pari a zero.

   Dopo aver inserito tutti i dati, premere il pulsante "OK".



4. Successivamente, nella cella che abbiamo assegnato nel primo paragrafo di questo manuale, viene visualizzato il risultato del calcolo. Come puoi vedere, l'entità del pagamento generale mensile del prestito è di 23536.74 rubli. Non consentono di confondere il segno "-" prima di questa quantità. Così esilio indica che questo è il flusso di denaro, cioè una perdita.



5. Al fine di calcolare l'importo totale del pagamento per l'intero periodo di prestito, tenendo conto del rimborso del corpo di un prestito e degli interessi mensili, piuttosto moltiplicare l'importo del pagamento mensile (23536.74 rubli) per il numero di mesi (24 mesi ). Come puoi vedere, l'importo totale dei pagamenti per l'intero periodo di prestito nel nostro caso era 564881.67 rubli.



6. Ora puoi calcolare la quantità di pagamento anticipato del prestito. Per fare ciò, è necessario togliere dall'importo totale dei pagamenti sul prestito, compreso gli interessi e il corpo del prestito, l'importo iniziale sostenuto. Ma ricordiamo che il primo di questi valori già con il segno "-". Pertanto, in particolare, il nostro caso risulta che devono essere piegati. Come vediamo, il pagamento in eccesso totale del prestito sull'intero periodo era 64881.67 rubli.

Lezione: Master di funzioni in Excel

Fase 2: dettagli di pagamento

E ora, con l'aiuto di altri operatori Excel, facciamo un dettaglio mensile dei pagamenti per vedere quanto in un mese specifico paghiamo attraverso il corpo del prestito, e quanto è la quantità di interesse. Per questi scopi, il fabbro nella tavola di esilio, che compileremo i dati. Le linee di questo tavolo saranno responsabili nel corrispondente periodo, cioè il mese. Dato che il periodo di prestito è di 24 mesi, sarà anche appropriato il numero di righe. Le colonne hanno indicato un corpo di prestito, pagamenti di interessi, un pagamento mensile totale, che è la somma delle precedenti due colonne, nonché la quantità rimanente da pagare.

1. Per determinare la quantità di pagamento da parte del corpo del prestito, utilizzare la funzione OSP, che è solo destinata a questi scopi. Stabiliamo il cursore nella cella, che si trova nella riga "1" e nella colonna "Pagamento con il corpo del prestito". Fare clic sul pulsante "Pasta funzione".



2. Vai al master delle funzioni. Nella categoria "Finanziamento", notiamo il nome "OSPLT" e fare clic sul pulsante "OK".



3. Gli argomenti degli argomenti dell'operatore OSP iniziano. Ha la seguente sintassi:

   = OSPUST (tasso; periodo; Kper; PS; BS)

   Come possiamo vedere, gli argomenti di questa funzione coincidono quasi completamente con gli argomenti dell'operatore PLT, solo invece dell'argomento opzionale "Tipo" aggiunto un argomento obbligatorio "Periodo". Indica il numero del periodo di pagamento e nel nostro caso particolare sul numero del mese.

   Compila gli argomenti degli argomenti della funzione OSR già familiari a noi con gli stessi dati, che è stato utilizzato per la funzione PL. Appena dato il fatto che in futuro, la copia di una formula verrà utilizzata attraverso un marker di riempimento, è necessario eseguire tutti i collegamenti nei campi Assoluto in modo che non cambino. Ciò richiede di mettere un segno di dollaro prima di ogni valore delle coordinate verticali e orizzontali. Ma è più facile farlo, semplicemente selezionando le coordinate e facendo clic sul tasto funzione F4. Il segno del dollaro verrà inserito automaticamente nei posti giusti. Inoltre, non dimentichiamo che la scommessa annuale deve essere divisa in 12.



4. Ma abbiamo un altro nuovo argomento, che non era dalla funzione PL. Questo argomento "periodo". Nel campo appropriato, impostare un riferimento alla prima cella della colonna "Periodo". Questo elemento del foglio contiene il numero "1", che denota il numero del primo mese di prestito. Ma a differenza dei campi precedenti, lasciamo il collegamento relativo nel campo specificato e non è assoluto da esso.

   Dopo che tutti i dati su cui abbiamo parlato sopra sono introdotti, premere il pulsante "OK".



5. Successivamente, nella cella, che abbiamo precedentemente assegnato, la quantità di pagamento da parte del corpo del prestito per il primo mese apparirà. Sarà 18536.74 rubli.



6. Quindi, come accennato sopra, dovremmo copiare questa formula per le celle di colonna rimanenti utilizzando un indicatore di riempimento. Per fare ciò, impostare il cursore nell'angolo in basso a destra della cella, che contiene la formula. Il cursore viene convertito in una croce, che è chiamato un indicatore di riempimento. Fai clic sul pulsante sinistro del mouse e tiralo giù fino alla fine della tabella.



7. Di conseguenza, tutte le colonne cellulari sono riempite. Ora abbiamo un grafico di pagare un prestito mensile. Come menzionato sopra, la quantità di pagamento su questo articolo aumenta con ogni nuovo periodo.



8. Ora dobbiamo fare un calcolo del pagamento mensile da interessi. Per questi scopi, useremo l'operatore PRT. Assegniamo la prima cella vuota nella colonna "Percentuale dei pagamenti". Fare clic sul pulsante "Pasta funzione".



9. Nelle funzioni del Master of Funcys nella categoria "finanziaria", produciamo i nomi del NAMP. Eseguire un clic sul pulsante "OK".



10. La finestra Argomenti della funzione TRP inizia. La sua sintassi è simile a questo:

    = PRT (tasso; periodo; CPU; PS; BS)

    Come possiamo vedere, gli argomenti di questa funzione sono assolutamente identici a elementi simili dell'operatore OSP. Pertanto, inserire solo gli stessi dati nella finestra che abbiamo inserito nella finestra precedente degli argomenti. Non dimentichiamo che il riferimento nel campo "Periodo" dovrebbe essere relativo, e in tutti gli altri campi le coordinate dovrebbero essere portate alla forma assoluta. Dopodiché, fai clic sul pulsante "OK".



11. Quindi il risultato del calcolo della quantità di pagamento per interessi per un prestito per il primo mese viene visualizzato nella cella corrispondente.



12. Applicazione del marker di riempimento, effettuare la copia della formula negli elementi rimanenti della colonna, in questo modo ricevendo un programma mensile per percentuali per il prestito. Come possiamo vedere, come è stato detto prima, dal mese al mese il valore di questo tipo di pagamento diminuisce.



13. Ora dobbiamo calcolare il pagamento mensile complessivo. Per questo calcolo, non si dovrebbe ricorrere a qualsiasi operatore, poiché è possibile utilizzare la semplice formula aritmetica. Piegheremo il contenuto delle cellule del primo mese della colonna "Pagamento con il corpo del prestito" e "interesse pieno". Per fare ciò, impostare il segno "=" nella prima cella vuota della colonna "Pagamento mensile totale". Quindi fare clic sui due elementi sopra impostando il segno "+" tra di loro. Fare clic sul tasto Invio.



14. Successivamente, utilizzando il marker di riempimento, come nei casi precedenti, compilare la colonna Dati. Come possiamo vedere, per tutta l'intera azione del contratto, l'ammontare del pagamento mensile totale, che include il pagamento da parte del corpo del prestito e del pagamento degli interessi, saranno 23536.74 rubli. In realtà, abbiamo già calcolato questo indicatore prima di usare PPT. Ma in questo caso è presentato più chiaramente, precisamente come la quantità di pagamento da parte del corpo del prestito e degli interessi.



15. Ora è necessario aggiungere dati alla colonna, dove viene visualizzato il saldo dell'importo del prestito mensile, il che è ancora necessario per pagare. Nella prima cella della colonna "Equilibrio per pagare" Il calcolo sarà il più semplice. Dobbiamo essere portati via dalla grandezza del prestito iniziale, che è specificato nella tabella con dati primari, il pagamento da parte del corpo del prestito per il primo mese nella tabella calcolata. Ma, dato il fatto che uno dei numeri che andiamo già con il segno "-", quindi non dovrebbero essere portati via, ma piegare. Facciamo e clicca sul pulsante ENTER.



16. Ma il calcolo del saldo da pagare dopo il secondo e i mesi successivi sarà alquanto più complicato. Per fare ciò, dobbiamo portare via dal corpo del prestito all'inizio del prestito la quantità totale di pagamenti da parte del corpo del prestito per il periodo precedente. Installa il segno "=" nella seconda cella della colonna "Palace to Pay". Quindi, specificare un collegamento alla cella, che contiene l'importo del prestito iniziale. Lo rendiamo assoluto, evidenziando e premendo il tasto F4. Poi mettiamo il segno "+", dal momento che abbiamo il secondo significato e così negativo. Dopodiché, fare clic sul pulsante "Inserisci funzione".



17. Il master delle funzioni è stato lanciato, in cui è necessario passare alla categoria "Matematica". Lì allociamo la scritta "somme" e premette il pulsante "OK".



18. La finestra Argomenti avvia gli argomenti della funzione. L'operatore specificato serve a riassumere i dati nelle celle che dobbiamo eseguire nella colonna "Pagamento tramite il corpo del corpo". Ha la seguente sintassi:

    = Somme (numero1; numero2; ...)

    Come argomenti, riferimenti a cellule in cui i numeri sono contenuti. Impostiamo il cursore nel campo "Number1". Quindi Pin il tasto sinistro del mouse e seleziona le prime due celle della colonna del corpo del credito sul foglio. Nel campo, come vediamo, è apparso un collegamento alla gamma. Consiste di due parti separate dal colon: i riferimenti alla prima gamma della gamma e sull'ultimo. Per poter copiare la formula specificata in futuro da un marker di riempimento, facciamo il primo collegamento all'intervallo assoluto. Li evidenziamo e clicciamo sul tasto funzione F4. La seconda parte del riferimento e lasciare relativo. Ora, quando si utilizza un marker di riempimento, verrà risolto il primo intervallo dell'intervallo e quest'ultimo si estenderà mentre si muove verso il basso. Questo è necessario per noi per soddisfare gli obiettivi. Quindi, fai clic sul pulsante "OK".



19. Quindi, il risultato del bilanciamento del debito del credito dopo il secondo mese viene scaricato nella cella. Ora, a partire da questa cella, rendiamo copiando la formula in elementi di colonna vuoti utilizzando un indicatore di riempimento.



20. Il calcolo mensile dei resti per la retribuzione del prestito è effettuato per l'intero periodo di credito. Come dovrebbe essere, alla fine della scadenza, questa quantità è zero.

Quindi, non abbiamo semplicemente calcolato il pagamento del prestito, ma ha organizzato un tipo di calcolatore di credito. Che agirà su uno schema di rendita. Se nel tavolo di origine, ad esempio, modificare l'importo del prestito e il tasso di interesse annuale, quindi nella tabella finale ci sarà il ricalcolo automatico dei dati. Pertanto, può essere utilizzato non solo una sola volta per un caso particolare, ma di applicare in varie situazioni per calcolare le opzioni di credito su uno schema di rendita annua.

Lezione: Funzioni finanziarie in Excel

Come puoi vedere, utilizzando il programma Excel a casa, è possibile calcolare facilmente il pagamento complessivo del prestito mensile sullo schema di rendita, utilizzando l'operatore PL per questi scopi. Inoltre, con l'aiuto delle funzioni OSR e del PRT, è possibile calcolare la quantità di pagamenti da parte del corpo del prestito e percentuali per il periodo specificato. Applicazione di tutte queste funzioni di bagagli insieme, è possibile creare un potente calcolatore di credito che può essere utilizzato più di una volta per calcolare il pagamento della rendita.