Formula per il calcolo di una rendita in Excel

I pagamenti dei prestiti sono più facili e veloci da calcolare con Microsoft Office Excel. Molto più tempo viene dedicato al calcolo manuale. Questo articolo si concentrerà sui pagamenti delle rendite, sulle caratteristiche del loro calcolo, sui vantaggi e gli svantaggi.

Che cos'è un pagamento di rendita

Un metodo di rimborso mensile di un prestito, in cui l'importo depositato non cambia durante l'intero periodo del prestito. Quelli. in determinate date di ogni mese, una persona deposita una determinata somma di denaro fino a quando il prestito non viene completamente rimborsato.

Inoltre, gli interessi sul prestito sono già compresi nell'importo totale pagato alla banca.

Rendita di classificazione

I pagamenti delle rendite possono essere suddivisi nelle seguenti tipologie:

1. Fisso. I pagamenti che non cambiano hanno un tasso fisso indipendentemente dalle condizioni esterne.
2. Moneta. La possibilità di modificare l'importo del pagamento in caso di calo o aumento del tasso di cambio.
3. indicizzato. Pagamenti a seconda del livello, indicatore di inflazione. Durante il periodo del prestito, la loro dimensione cambia spesso.
4. Variabili. Rendita, che può variare a seconda dello stato del sistema finanziario, degli strumenti.

Fai attenzione! I pagamenti fissi sono preferibili per tutti i mutuatari, perché hanno pochi rischi.

Vantaggi e svantaggi del pagamento delle rendite

Per comprendere meglio l'argomento, è necessario approfondire le caratteristiche salienti di questa tipologia di pagamenti di prestiti. Presenta i seguenti vantaggi:

• Stabilire un importo specifico di pagamento e la data del suo pagamento.
• Alta disponibilità per i mutuatari. Quasi tutti possono richiedere una rendita, indipendentemente dalla loro situazione finanziaria.
• La possibilità di abbassare l'importo della rata mensile con un aumento dell'inflazione.

Non senza inconvenienti:

• Alta percentuale. Il mutuatario pagherà in eccesso una quantità di denaro maggiore rispetto al pagamento differenziale.
• Problemi derivanti dal desiderio di estinguere il debito prima del previsto.
• Nessun ricalcolo per pagamenti anticipati.

Qual è il pagamento del prestito?

Il pagamento della rendita ha le seguenti componenti:

• Interessi pagati da una persona quando paga un prestito.
• Parte dell'importo principale.

Di conseguenza, l'importo totale degli interessi supera quasi sempre l'importo contribuito dal mutuatario per ridurre il debito.

Formula di pagamento di rendita di base in Excel

Come accennato in precedenza, in Microsoft Office Excel puoi lavorare con vari tipi di pagamenti per prestiti e anticipi. La rendita non fa eccezione. In generale, la formula con cui è possibile calcolare velocemente i contributi di rendita è la seguente:  

Importante! È impossibile aprire le parentesi nel denominatore di questa espressione per semplificarla.

I valori principali della formula sono così decifrati:

• AP – pagamento rendita (il nome è abbreviato).
• O – la dimensione del debito principale del mutuatario.
• PS – il tasso di interesse proposto mensilmente da una determinata banca.
• C è il numero di mesi di durata del prestito.

Per assimilare le informazioni, è sufficiente fornire alcuni esempi dell'uso di questa formula. Saranno discussi ulteriormente.

Esempi di utilizzo della funzione PMT in Excel

Diamo una semplice condizione del problema. È necessario calcolare il pagamento mensile del prestito se la banca presenta un interesse del 23% e l'importo totale è di 25000 rubli. Il prestito durerà 3 anni. Il problema è risolto secondo l'algoritmo:

1. Crea un foglio di calcolo generale in Excel basato sui dati di origine.

2. Attiva la funzione PMT e inserisci gli argomenti nella casella appropriata.
3. Nel campo “Scommessa”, inserisci la formula “B3/B5”. Questo sarà il tasso di interesse sul prestito.
4. Nella riga “Nper” scrivere il valore nella forma “B4*B5”. Questo sarà il numero totale di pagamenti per l'intera durata del prestito.
5. Compila il campo "PS". Qui è necessario indicare l'importo iniziale prelevato dalla banca, scrivendo il valore “B2”.

6. Assicurati che dopo aver fatto clic su "OK" nella tabella di origine, sia stato calcolato il valore "Pagamento mensile".

Informazioni aggiuntive! Un numero negativo indica che il mutuatario sta spendendo denaro.

Un esempio di calcolo dell'importo del pagamento in eccesso su un prestito in Excel

In questo problema, è necessario calcolare l'importo che una persona che ha preso un prestito di 50000 rubli a un tasso di interesse del 27% per 5 anni pagherà più del dovuto. In totale, il mutuatario effettua 12 pagamenti all'anno. Soluzione:

1. Compila la tabella dati originale.

2. Dall'importo totale dei pagamenti, sottrarre l'importo iniziale secondo la formula «=ABS(ПЛТ(B3/B5;B4*B5;B2)*B4*B5)-B2». Deve essere inserito nella barra delle formule nella parte superiore del menu principale del programma.
3. Di conseguenza, l'importo dei pagamenti in eccesso apparirà nell'ultima riga del piatto creato. Il mutuatario pagherà oltre 41606 rubli.

La formula per calcolare la rata mensile ottimale del prestito in Excel

Un'attività con la seguente condizione: il cliente ha registrato un conto bancario per 200000 rubli con possibilità di rifornimento mensile. È necessario calcolare l'importo del pagamento che una persona deve effettuare ogni mese, in modo che dopo 4 anni abbia 2000000 rubli nel suo conto. Il tasso è dell'11%. Soluzione:

1. Crea un foglio di calcolo basato sui dati originali.

2. Immettere la formula nella riga di input di Excel «=ПЛТ(B3/B5;B6*B5;-B2;B4)» e premere "Invio" dalla tastiera. Le lettere differiranno a seconda delle celle in cui è posizionata la tabella.
3. Verificare che l'importo del contributo venga calcolato automaticamente nell'ultima riga della tabella.

Fai attenzione! Pertanto, affinché il cliente possa accumulare 4 rubli a un tasso del 2000000% in 11 anni, deve depositare 28188 rubli ogni mese. Il meno nell'importo indica che il cliente subisce perdite dando denaro alla banca.

Caratteristiche dell'utilizzo della funzione PMT in Excel

In generale, questa formula è scritta come segue: =PMT(tasso; nper; ps; [bs]; [tipo]). La funzione ha le seguenti caratteristiche:

1. Quando vengono calcolati i contributi mensili, viene presa in considerazione solo la tariffa annuale.
2. Quando si specifica il tasso di interesse, è importante ricalcolare in base al numero di rate all'anno.
3. Al posto dell'argomento "Nper" nella formula, viene indicato un numero specifico. Questo è il periodo di pagamento.

Calcolo del pagamento

In generale, il pagamento della rendita è calcolato in due fasi. Per comprendere l'argomento, ciascuna delle fasi deve essere considerata separatamente. Questo sarà discusso ulteriormente.

Fase 1: calcolo della rata mensile

Per calcolare in Excel l'importo che devi pagare ogni mese per un prestito a tasso fisso, devi:

1. Compila la tabella di origine e seleziona la cella in cui vuoi visualizzare il risultato e fai clic sul pulsante "Inserisci funzione" in alto.

2. Nell'elenco delle funzioni, selezionare "PLT" e fare clic su "OK".

3. Nella finestra successiva, impostare gli argomenti per la funzione, indicando le righe corrispondenti nella tabella compilata. Alla fine di ogni riga, è necessario fare clic sull'icona, quindi selezionare la cella desiderata nell'array.

4. Quando tutti gli argomenti sono stati compilati, nella riga per l'inserimento dei valori verrà scritta la formula appropriata e nel campo della tabella “Pagamento mensile” comparirà il risultato del calcolo con il segno meno.

Importante! Dopo aver calcolato la rata, sarà possibile calcolare l'importo che il mutuatario pagherà in eccesso per l'intero periodo del prestito.

Fase 2: dettagli di pagamento

L'importo del pagamento in eccesso può essere calcolato mensilmente. Di conseguenza, una persona capirà quanti soldi spenderà per un prestito ogni mese. Il calcolo di dettaglio viene eseguito come segue:

1. Crea un foglio di calcolo per 24 mesi.

2. Posizionare il cursore nella prima cella della tabella e inserire la funzione “OSPLT”.

3. Compila gli argomenti della funzione allo stesso modo.

4. Quando si compila il campo "Periodo", è necessario fare riferimento al primo mese della tabella, indicando la cella 1.

5. Verificare che la prima cella della colonna “Pagamento da parte dell'ente del prestito” sia riempita.
6. Per riempire tutte le righe della prima colonna, devi allungare la cella fino alla fine della tabella

7. Selezionare la funzione “PRPLT” per compilare la seconda colonna della tabella.
8. Compila tutti gli argomenti nella finestra aperta secondo lo screenshot qui sotto.

9. Calcola la rata mensile totale sommando i valori nelle due colonne precedenti.

10. Per calcolare il “Saldo da pagare”, è necessario sommare il tasso di interesse alla rata sul corpo del prestito e allungarlo fino alla fine del piatto per riempire tutti i mesi del prestito.

Informazioni aggiuntive! Quando si calcola il resto, i segni del dollaro devono essere appesi alla formula in modo che non si muova fuori quando viene allungata.

Calcolo delle rendite vitalizie su un prestito in Excel

La funzione PMT è responsabile del calcolo della rendita in Excel. Il principio di calcolo in generale consiste nell'eseguire i seguenti passaggi:

1. Compila la tabella dati originale.
2. Costruisci un piano di rimborso del debito per ogni mese.
3. Seleziona la prima cella della colonna “Pagamenti sul prestito” e inserisci la formula di calcolo “PLT ($B3/12;$B$4;$B$2)”.
4. Il valore risultante viene allungato per tutte le colonne del piatto.

Calcolo in MS Excel rimborso dell'importo capitale del debito

I pagamenti delle rendite devono essere effettuati mensilmente in importi fissi. E il tasso di interesse non cambia.

Calcolo del saldo dell'importo capitale (con BS=0, tipo=0)

Supponiamo che un prestito di 100000 rubli venga preso per 10 anni al 9%. È necessario calcolare l'importo del debito principale nel 1° mese del 3° anno. Soluzione:

1. Compila un foglio dati e calcola la rata mensile utilizzando la formula PV sopra.
2. Calcola la quota del pagamento necessaria per estinguere parte del debito utilizzando la formula «=-PMT-(PS-PS1)*articolo=-PMT-(PS +PMT+PS*articolo)».
3. Calcola l'importo del debito principale per 120 periodi utilizzando una formula ben nota.
4. Utilizzando l'operatore HPMT trovare l'importo degli interessi pagati per il 25° mese.
5. Controlla il risultato.

Calcolo dell'importo del capitale che è stato pagato tra due periodi

È meglio eseguire questo calcolo in modo semplice. È necessario utilizzare le seguenti formule per calcolare l'importo nell'intervallo per due periodi:

• =«-BS(elemento; con_period; plt; [ps]; [tipo]) /(1+tipo *elemento)».
• = “+ BS(tasso; inizio_periodo-1; plt; [ps]; [tipo]) /IF(inizio_periodo =1; 1; 1+tipo *tasso)”.

Fai attenzione! Le lettere tra parentesi vengono sostituite con valori specifici.

Rimborso anticipato con termine o pagamento ridotto

Se hai bisogno di ridurre la durata del prestito, dovrai eseguire calcoli aggiuntivi utilizzando l'operatore IF. Sarà quindi possibile controllare il saldo zero, che non dovrebbe essere raggiunto prima della fine del periodo di pagamento.

Per ridurre i pagamenti, è necessario ricalcolare il contributo per ogni mese precedente.

Calcolatore prestito con pagamenti irregolari

Esistono diverse opzioni di rendita in cui il mutuatario può depositare importi variabili in qualsiasi giorno del mese. In una situazione del genere, il saldo del debito e degli interessi viene calcolato per ogni giorno. Allo stesso tempo in Excel hai bisogno di:

1. Immettere i giorni del mese per i quali vengono effettuati i pagamenti e indicarne il numero.
2. Controlla gli importi negativi e positivi. Sono preferiti quelli negativi.
3. Conta i giorni tra due date in cui il denaro è stato depositato.

Calcolo del pagamento periodico in MS Excel. Deposito a termine

In Excel puoi calcolare rapidamente l'importo dei pagamenti regolari, a condizione che sia già stato accumulato un importo fisso. Questa azione viene eseguita utilizzando la funzione PMT dopo la compilazione della tabella iniziale.

Conclusione

Pertanto, i pagamenti delle rendite sono più facili, veloci ed efficienti da calcolare in Excel. L'operatore PMT è responsabile del loro calcolo. Esempi più dettagliati possono essere trovati sopra.