Harmonogram zadłużenia z PMT, IPMT i IF

Możemy użyć formuł programu Excel PMT, IPMT i IF do stworzenia harmonogramu zadłużenia. Najpierw musimy stworzyć model, wprowadzając pewne założenia dotyczące długu. W tym przykładzie zakładamy, że dług wynosi 5 000 000 USD, termin spłaty 5 lat, a stopa procentowa Stopa procentowa Stopa procentowa odnosi się do kwoty naliczonej pożyczkobiorcy przez pożyczkodawcę za jakąkolwiek formę zadłużenia, ogólnie wyrażoną procent kwoty głównej. na 4,5%.

1. Saldo otwarcia w naszym harmonogramie zadłużenia jest równe kwocie pożyczki w wysokości 5 milionów dolarów, więc w komórce E29 wpisujemy = B25, aby połączyć je z danymi wejściowymi założenia. Następnie możemy użyć formuły PMT do obliczenia całkowitej płatności za pierwszy okres = PMT (27 B $, 26 $ B $, 25 $ B $) . Formuła oblicza kwotę spłaty na podstawie kwoty pożyczki, okresu i stopy procentowej podanej w sekcji dotyczącej założeń.

Harmonogram zadłużenia

2. W komórce E28 wprowadź okres, w którym się znajdujemy, czyli 1. W komórce E29 wprowadź = E28 + 1 i wypełnij formułę po prawej stronie. Następnie użyj wzoru IPMT, aby znaleźć płatność odsetek za pierwszy okres = IPMT (27 B $, E28, 26 B $, 25 $ B $) .

3. Kwota główna to różnica między całkowitą płatnością a odsetkami, która wynosi = E30-E31 . Bilans zamknięcia to saldo otwarcia plus dokonana spłata główna, która wynosi = E29 + E32 . Bilans otwarcia dla okresu 2 to saldo zamknięcia okresu 1, które wynosi = E33 .

4. Skopiuj wszystkie formuły z komórki E29 do E33 do następnej kolumny, a następnie skopiuj wszystko w prawo. Sprawdź, czy bilans zamknięcia dla okresu 5 = 0, aby upewnić się, że są używane prawidłowe formuły i liczby.

5. Zauważ, że pojawiają się komunikaty o błędach począwszy od okresu 6, ponieważ bilans otwarcia wynosi 0. Tutaj możemy użyć funkcji JEŻELI do wyczyszczenia błędów. W komórce E30 wpisz = JEŻELI (E29> 0, PMT (27 B $, 26 $ B $, B $ 25 $), 0) . Formuła stwierdza, że ​​jeśli saldo otwarcia jest mniejsze niż 0, wówczas łączna wartość płatności zostanie wyświetlona jako 0.

6. W komórce 31 wpisz = JEŻELI (E29> 0, IPMT (27 $ B $, E28, $ B $ 26, $ B $ 25), 0) . Ta formuła jest podobna do poprzedniej, która stwierdza, że ​​jeśli saldo otwarcia jest mniejsze niż 0, to płatność odsetek zostanie wyświetlona jako 0.

7. Skopiuj komórkę E30 i E31, naciśnij SHIFT + strzałka w prawo, a następnie CTRL + R, aby wypełnić w prawo. Powinieneś zobaczyć, że wszystkie komunikaty o błędach są teraz wyświetlane jako 0.

XNPV XIRR z funkcjami DATA i JEŻELI

Możemy obliczyć NPV i IRR na podstawie określonych dat przy użyciu funkcji Excela XNPV i XIRR z funkcjami DATA i JEŻELI.

8. Przejdź do komórki E6 i wprowadź = DATA (E5,12,31), aby wyświetlić datę. Skopiuj w prawo. Zobaczysz #ARG! komunikat po 2021 roku. Możemy to naprawić, używając funkcji JEŻELI BŁĄD = JEŻELI BŁĄD (DATA (E5,12,31), ””) .

9. Teraz możemy zacząć obliczać NPV i IRR. Najpierw musimy wprowadzić kwoty wolnych przepływów pieniężnych. Zakładamy, że kwoty FCF w okresie od 1 do 5 wynoszą -1 000, 500, 600, 700, 900. W komórce C37 wprowadzimy stopę dyskontową 15%. W komórce B37 oblicz wartość NPV za pomocą wzoru XNPV = XNPV (C37, E35: I35, E6: I6) .

10. W komórce B38 oblicz IRR za pomocą wzoru XIRR = XIRR (E35: I35, E6: I6) .

Dodanie OFFSET do XNPV i XIRR

Możemy zmienić formuły na XNPV i XIRR, aby utworzyć bardziej dynamiczne formuły za pomocą funkcji PRZESUNIĘCIE.

11. W komórce B42 zmień formułę na = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) . Formuła jest bardziej dynamiczna, ponieważ wraz ze wzrostem liczby okresów zwiększą się również okresy wolnych przepływów pieniężnych. Nie musimy zmieniać formuły NPV, jeśli okres prognozy jest dłuższy. Dla funkcji IRR zmień ją na = XIRR (E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) .

12. Po dostosowaniu wzoru na liczbę okresów należy przesunąć daty. W komórce B42 zmień formułę na = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: OFFSET (E6,0, $ F $ 3-1)) . Dzięki temu formuły NPV i IRR mogą pobierać odpowiednią liczbę wolnych przepływów pieniężnych wraz ze zmianą liczby okresów.

Podsumowanie kluczowych wzorów harmonogramu zadłużenia

  • Wzór PMT do obliczenia kwoty spłaty zadłużenia: = PMT (oprocentowanie, liczba terminów, wartość bieżąca)
  • Wzór IPMT do obliczania płatności odsetek: = IPMT (oprocentowanie, okres, liczba terminów, wartość bieżąca)
  • Wzór na XNPV do znajdowania wartości bieżącej netto: = XNPV (stopa dyskontowa, wolne przepływy pieniężne, daty)
  • Wzór XIRR na znalezienie wewnętrznej stopy zwrotu: = XIRR (wolne przepływy pieniężne, daty)
  • Formuła OFFSET do obliczania dynamicznego NPV: = XNPV (stopa dyskontowa, 1. FCF: OFFSET (1. FCF, 0, # okresy - 1), 1. data: OFFSET (1. data, 0, # okresy - 1))
  • Formuła OFFSET do obliczania dynamicznej IRR: = XIRR (1. FCF: OFFSET (1. FCF, 0, # okresy - 1), 1. data: OFFSET (1. data, 0, # okresy - 1))

Inne zasoby

Dziękujemy za przeczytanie przewodnika Finance na temat harmonogramu zadłużenia z formułami PMT, IPMT i IF. Aby kontynuować naukę i rozwijać swoją karierę, pomocne będą następujące zasoby finansowe:

  • Podstawowe formuły programu Excel Podstawowe formuły programu Excel Opanowanie podstawowych formuł programu Excel ma kluczowe znaczenie dla początkujących, aby stać się biegli w analizie finansowej. Microsoft Excel jest uważany za branżowy standard oprogramowania do analizy danych. Arkusz kalkulacyjny Microsoftu jest również jednym z najbardziej preferowanych przez bankierów inwestycyjnych
  • Najlepsze praktyki modelowania finansowego Najlepsze praktyki modelowania finansowego Ten artykuł ma na celu dostarczenie czytelnikom informacji na temat najlepszych praktyk w zakresie modelowania finansowego oraz łatwy do naśladowania przewodnik krok po kroku dotyczący tworzenia modelu finansowego.
  • Lista funkcji programu Excel Lista najważniejszych funkcji programu Excel dla analityków finansowych. Ten ściągawka obejmuje setki funkcji, które są kluczowe dla analityka Excela
  • Przegląd skrótów programu Excel Skróty programu Excel Omówienie Skróty programu Excel to przeoczona metoda zwiększania produktywności i szybkości w programie Excel. Skróty w programie Excel oferują analitykowi finansowemu potężne narzędzie. Te skróty mogą pełnić wiele funkcji. tak proste, jak nawigacja w arkuszu kalkulacyjnym do wypełniania formuł lub grupowania danych.

Zalecane

Czy Crackstreams zostały zamknięte?
2022
Czy centrum dowodzenia MC jest bezpieczne?
2022
Czy Taliesin opuszcza kluczową rolę?
2022