Excel Tip: Określanie pozostałej długości kredytu przy użyciu NPER

Wcześniej wyjaśniłem, jak można korzystać z funkcji arkusza kalkulacyjnego w programie Excel w celu określenia płatności za pożyczkę, a także jak obliczyć całkowite odsetki w jednej komórce arkusza. Tym razem około zamierzam użyć funkcji NPER w Excelu, aby pokazać, jak można określić, jak długo zajmie ci spłatę tego rachunku karty kredytowej, na którym dokonujesz płatności miesięcznych.

Można uzyskać odświeżenie na funkcje PMT i CUMIPMT, ale każdy typowy kredyt ma cztery kluczowe wartości:

  • Stopa procentowa
  • Okres kredytowania
  • Kwota pożyczona
  • Płatność miesięczna

Jak wspomniano w moim poprzednim artykule, jeśli masz stopę procentową, termin i kwotę kredytu, można użyć funkcji PMT, aby rozwiązać dla płatności. Czasami, na przykład w przypadku salda karty kredytowej, będziesz znał stopę procentową, płatność i kwotę pożyczki, ale nie termin. W tym przypadku, funkcja NPER Excela (skrót od liczby okresów) pozwala na obliczenie czwartej wartości.

NPER posiada 3 wymagane i 2 opcjonalne argumenty:

stopa – Stopa procentowa pożyczki wyrażona jako stopa miesięczna.

pmt – Płatność miesięczna, która powinna być zawsze wykazywana jako kwota ujemna.

pv – Bieżące saldo pożyczki

fv – Ten opcjonalny argument pozwala określić przyszłą wartość, jeśli kwota balonu jest należna na koniec pożyczki. Pominięcie tego argumentu oznacza domyślnie, że pożyczka ma być spłacona do 0.

typ – Ten opcjonalny argument pozwala określić, czy płatności są dokonywane na początku każdego okresu, czy też można pominąć ten argument, aby wskazać, że płatności są dokonywane na końcu każdego okresu. Możesz również określić 0 w tej pozycji, aby wyraźnie wskazać, że płatności są dokonywane na koniec każdego okresu.

Jak pokazano na rysunku 1, spłata 20.000 dolarów przy miesięcznej płatności 586,04 dolarów przy stopie procentowej 3,5% zajmie 36 miesięcy. Zawsze upewnij się, że płatność jest pokazana jako liczba ujemna, w przeciwnym razie NPER może wykazywać nieco dłuższy termin płatności. Pominąłem 2 opcjonalne argumenty, więc w tym przypadku, funkcja PMT zakłada, że pożyczka jest wypłacana do 0 i płatności są dokonywane na koniec każdego okresu.

Rysunek 1: Użyj funkcji NPER Excela, aby obliczyć termin spłaty kredytu.

:

W przypadku kredytów długoterminowych, NPER może zwrócić dużą liczbę miesięcy, np. 94, które mogą być trudne do przekształcenia w miesiące w głowie. Rozszerzmy nasze kalkulacje, aby uczynić wyjście bardziej przyjaznym dla użytkownika.

Najpierw, jak pokazano na rysunku 2, dodamy funkcję ROUNDUP do naszego wzoru NPER. Okresy kredytowania obejmują zazwyczaj pewien ułamek ostatniego miesiąca, który dla naszych celów chcemy traktować jako cały miesiąc. Funkcja ROUNDUP zaokrągla liczbę w górę, w przeciwieństwie do powszechnie stosowanej funkcji ROUNDUP, która może zaokrąglać liczby w górę lub w dół.

ROUNDUP ma dwa argumenty:

liczba – W tym przypadku wynikiem NPER będzie nasza liczba

num_digits – w tym przypadku użyjemy zera, ponieważ chcemy zaokrąglić w górę do następnego całego miesiąca. Jeśli chcielibyśmy zaokrąglić liczbę do najbliższego tysiąca, zamiast tego użylibyśmy -3.

Rysunek 2: Użyj funkcji ROUNDUP z formułą NPER do przeliczenia na miesiące.

Następnie musimy przeliczyć wynik ROUNDUP/NPER na kilka lat i miesięcy. Aby to zrobić, możemy użyć funkcji TRUNC. Funkcja ta zamienia liczbę na liczbę całkowitą, usuwając część dziesiętną lub ułamkową. Można również użyć funkcji ROUNDDOWN i podać zero jako liczbę cyfr, aby uzyskać ten sam efekt.

Aby obliczyć liczbę pełnych lat w kredycie, możemy skorzystać z tego wzoru:

=TRUNC(B4/12)

Albo to też by zadziałało:

=(B4/12,0)

W obu przypadkach bierzemy liczbę okresów zwróconą przez NPER, dzieląc ją przez 12, a następnie obcinamy je po przecinku.

Użyj tego wzoru do obliczenia liczby miesięcy pozostałych po całych latach:

=powłoka (B4-TRUNC(B4/12)*12,0)

Możemy to wszystko połączyć w uporządkowany format, jak pokazano na rysunku 3:

=»miesiące, lub «&TRUNC(B4/12)&»Lata»,&ROUNDUP(B4-TRUNC(B4/12)*12,0)&»Miesiące»

Używamy w tym celu wzmacniaczy i łączymy ze sobą tekst i obliczenia w zrozumiałą frazę. Bardzo wolę używać ampersand do łączenia tekstu razem, ale jeśli jesteś fanem Excel funkcji CONCATENATE, formuła będzie mieć taką formę:

=CONCATENATE(«miesiące, lub «,TRUNC(B4/12)», Lata», ROUNDUP(B4-TRUNC(B4/12)*12,0)», Miesiące»)

Rysunek 3: Za pomocą amperów i znaku łączyć tekst i obliczenia w logiczną frazę.

: