Possiamo utilizzare le formule PMT, IPMT e IF di Excel per creare una pianificazione debiti. In primo luogo, dobbiamo impostare il modello inserendo alcune ipotesi sul debito. In questo esempio, assumiamo che il debito sia di $ 5.000.000, il termine di pagamento sia di 5 anni e il tasso di interesse Tasso di interesse Un tasso di interesse si riferisce all'importo addebitato da un prestatore a un mutuatario per qualsiasi forma di debito data, generalmente espresso come una percentuale del capitale. essere del 4,5%.
1. Il saldo di apertura nella nostra pianificazione debiti è uguale all'importo del prestito di $ 5 milioni, quindi nella cella E29 inseriamo = B25 per collegarlo all'input ipotetico. Quindi, possiamo utilizzare la formula PMT per calcolare il pagamento totale per il primo periodo = PMT ($ B $ 27, $ B $ 26, $ B $ 25) . La formula calcola l'importo del pagamento utilizzando l'importo del prestito, la durata e il tasso di interesse indicati nella sezione ipotesi.
2. Nella cella E28, immettere il periodo in cui ci troviamo, che è 1. Nella cella E29, immettere = E28 + 1 e riempire la formula a destra. Successivamente, utilizza la formula IPMT per scoprire il pagamento degli interessi per il primo periodo = IPMT ($ B $ 27, E28, $ B $ 26, $ B $ 25) .
3. Il pagamento principale è la differenza tra il pagamento totale e il pagamento degli interessi, che è = E30-E31 . Il saldo di chiusura è il saldo di apertura più il pagamento principale effettuato, che è = E29 + E32 . Il saldo di apertura per il periodo 2 è il saldo di chiusura per il periodo 1, che è = E33 .
4. Copia tutte le formule dalla cella E29 a E33 nella colonna successiva, quindi copia tutto a destra. Verificare se il saldo di chiusura per il periodo 5 = 0 per assicurarsi che vengano utilizzate formule e numeri corretti.
5. Notare che ci sono alcuni messaggi di errore a partire dal periodo 6 perché il saldo iniziale è 0. Qui possiamo usare la funzione IF per pulire gli errori. Nella cella E30, digita = IF (E29> 0, PMT ($ B $ 27, $ B $ 26, $ B $ 25), 0) . La formula afferma che se il saldo di apertura è inferiore a 0, il valore del pagamento totale verrà visualizzato come 0.
6. Nella cella 31, digitare = IF (E29> 0, IPMT ($ B $ 27, E28, $ B $ 26, $ B $ 25), 0) . Questa formula è simile alla precedente, che afferma che se il saldo di apertura è inferiore a 0, il pagamento degli interessi verrà visualizzato come 0.
7. Copiare le celle E30 ed E31, premere MAIUSC + freccia destra quindi CTRL + R per riempire a destra. Dovresti vedere che tutti i messaggi di errore ora vengono visualizzati come 0.
XNPV e XIRR con funzioni DATE e IF
Possiamo calcolare NPV e IRR in base a date specifiche utilizzando le funzioni Excel XNPV e XIRR con le funzioni DATE e IF.
8. Vai alla cella E6 e inserisci = DATE (E5,12,31) per visualizzare la data. Copia a destra. Vedrai il #VALORE! messaggio dopo il 2021. Possiamo risolvere questo problema utilizzando la funzione SE.ERRORE = SE.ERRORE (DATA (E5,12,31), ””) .
9. Ora possiamo iniziare a calcolare NPV e IRR. Innanzitutto, dobbiamo inserire gli importi del flusso di cassa libero. Supponiamo che gli importi FCF dal periodo 1 al 5 siano -1.000, 500, 600, 700, 900. Nella cella C37, inseriremo un tasso di sconto del 15%. Nella cella B37, calcola il VAN utilizzando la formula XNPV = XNPV (C37, E35: I35, E6: I6) .
10. Nella cella B38, calcolare l'IRR utilizzando la formula XIRR = XIRR (E35: I35, E6: I6) .
Aggiunta di OFFSET a XNPV e XIRR
Possiamo passare alle formule XNPV e XIRR per rendere più dinamiche le formule utilizzando la funzione OFFSET.
11. Nella cella B42, modificare la formula in = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) . La formula è più dinamica perché se il numero di periodi aumenta, aumenteranno anche i periodi di free cash flow. Non è necessario modificare la formula NPV se il periodo di previsione è più lungo. Per la funzione IRR, modificala in = XIRR (E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) .
12. Dopo aver modificato la formula per il numero di periodi, dovremmo compensare le date. Nella cella B42, modifica la formula in = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: OFFSET (E6,0, $ F $ 3-1)) . Ciò consente alle formule NPV e IRR di raccogliere il giusto numero di flussi di cassa liberi con la variazione del numero di periodi.
Riepilogo delle formule chiave della pianificazione del debito
- Formula PMT per il calcolo dell'importo del pagamento del debito: = PMT (tasso di interesse, numero di termini, valore attuale)
- Formula IPMT per il calcolo del pagamento degli interessi: = IPMT (tasso di interesse, periodo, numero di termini, valore attuale)
- Formula XNPV per trovare il valore attuale netto: = XNPV (tasso di sconto, flussi di cassa liberi, date)
- Formula XIRR per trovare il tasso di rendimento interno: = XIRR (flussi di cassa liberi, date)
- Formula OFFSET per il calcolo del NPV dinamico: = XNPV (tasso di sconto, 1 ° FCF: OFFSET (1 ° FCF, 0, # periodi - 1), 1 ° data: OFFSET (1 ° data, 0, # periodi - 1))
- Formula OFFSET per il calcolo dell'IRR dinamico: = XIRR (1 ° FCF: OFFSET (1 ° FCF, 0, # periodi - 1), 1a data: OFFSET (1 ° data, 0, # periodi - 1))
Altre risorse
Grazie per aver letto la guida di Finance su Debt Schedule con formule PMT, IPMT e IF. Per continuare ad apprendere e far progredire la tua carriera, saranno utili le seguenti risorse finanziarie:
- Formule di base di Excel Formule di base di Excel La padronanza delle formule di base di Excel è fondamentale per i principianti per acquisire competenze nell'analisi finanziaria. Microsoft Excel è considerato il software standard del settore per l'analisi dei dati. Il programma per fogli di calcolo di Microsoft è anche uno dei software preferiti dai banchieri di investimento
- Migliori pratiche di modellazione finanziaria Migliori pratiche di modellazione finanziaria Questo articolo fornisce ai lettori informazioni sulle migliori pratiche di modellazione finanziaria e una guida passo passo facile da seguire per la costruzione di un modello finanziario.
- Elenco delle funzioni delle funzioni di Excel Elenco delle funzioni di Excel più importanti per gli analisti finanziari. Questo cheat sheet copre centinaia di funzioni che sono fondamentali da conoscere come analista di Excel
- Panoramica delle scorciatoie di Excel Panoramica delle scorciatoie di Excel Le scorciatoie di Excel sono un metodo trascurato per aumentare la produttività e la velocità in Excel. Le scorciatoie di Excel offrono all'analista finanziario uno strumento potente. Queste scorciatoie possono eseguire molte funzioni. semplice come la navigazione all'interno del foglio di calcolo per compilare formule o raggruppare dati.
