Wcześniej wyjaśniłem, jak można użyć funkcji arkusza w Excelu, aby określić płatność dla kredytu, a także jak obliczyć całkowite odsetki w jednej komórce arkusza. Tym razem wykorzystam funkcję NPER w Excelu, aby pokazać Ci, jak możesz określić, ile czasu zajmie Ci spłata rachunku za kartę kredytową, na którą dokonujesz comiesięcznych płatności.
Możesz odświeżyć sobie funkcje PMT i CUMIPMT, ale każdy typowy kredyt ma cztery kluczowe wartości:
- Oprocentowanie
- Okres kredytowania
- Kwota pożyczki
- Miesięczna płatność
Jak już wspomniałem w poprzednim artykule, jeśli masz stopę procentową, okres spłaty i kwotę pożyczki, możesz użyć funkcji PMT, aby rozwiązać problem płatności. Czasami, na przykład w przypadku salda karty kredytowej, znasz stopę procentową, płatność i kwotę pożyczki, ale nie znasz jej terminu. W takim przypadku funkcja NPER programu Excel (skrót od liczby okresów) umożliwia obliczenie czwartej wartości.
NPER ma 3 wymagane i 2 opcjonalne argumenty:
rate - Oprocentowanie kredytu wyrażone jako stawka miesięczna.
pmt - Miesięczna płatność, która zawsze powinna być pokazywana jako kwota ujemna.
pv - Bieżące saldo kredytu
fv - Ten opcjonalny argument pozwala na określenie przyszłej wartości, jeśli na koniec kredytu należna jest kwota balonowa. Pominięcie tego argumentu oznacza, że pożyczka ma być spłacona do 0.
type - 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żna również podać 0 w tej pozycji, aby jednoznacznie wskazać, że płatności są dokonywane na koniec każdego okresu.
Jak pokazano na rysunku 1, spłata 20 000 dolarów z miesięczną płatnością 586,04 dolarów przy stopie procentowej 3,5 procent zajmie 36 miesięcy. Zawsze upewnij się, że płatność jest pokazana jako liczba ujemna, w przeciwnym razie NPER może pokazać nieco dłuższy okres spłaty. Pominąłem 2 opcjonalne argumenty, więc w tym przypadku funkcja PMT zakłada, że pożyczka jest wypłacona do 0, a płatności są dokonywane na koniec każdego okresu.
Rysunek 1: Użyj funkcji NPER w Excelu, aby obliczyć termin spłaty pożyczki.
W przypadku pożyczek o dłuższym terminie spłaty, funkcja NPER może zwrócić dużą liczbę miesięcy, np. 94, co może być trudne do przeliczenia w głowie na miesiące. Rozszerzmy nasze obliczenia, aby dane wyjściowe były bardziej przyjazne dla użytkownika.
Po pierwsze, jak pokazano na rysunku 2, dodamy funkcję ROUNDUP do naszej formuły NPER. Okresy kredytowe będą zazwyczaj obejmować 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 ROUND, która może zaokrąglać liczby w górę lub w dół.
ROUNDUP ma dwa argumenty:
number - W tym przypadku wynikiem NPER będzie nasza liczba
num_digits - w tym przypadku użyjemy zera, gdyż chcemy zaokrąglić do kolejnego całego miesiąca. Gdybyś chciał zaokrąglić liczbę do powiedzmy najbliższego tysiąca, użyłbyś zamiast tego -3.
Rysunek 2: Użyj funkcji ROUNDUP z formułą NPER do konwersji na miesiące.
Następnie musimy przekonwertować wynik zwrócony przez ROUNDUP/NPER na liczbę lat i miesięcy. Aby to zrobić, możemy użyć funkcji TRUNC. Funkcja ta konwertuje liczbę na liczbę całkowitą poprzez usunięcie części dziesiętnej lub ułamkowej. Możesz również użyć funkcji ROUNDDOWN i podać zero jako liczbę cyfr, aby osiągnąć ten sam efekt.
Aby obliczyć liczbę pełnych lat w kredycie, możemy użyć tej formuły:
=TRUNC(B4/12)
Równie dobrze zadziała też to:
=ROUNDDOWN(B4/12,0)
W obu przypadkach bierzemy liczbę okresów zwróconych przez NPER, dzielimy ją przez 12, a następnie obcinamy miejsca po przecinku.
Użyj tego wzoru, aby obliczyć liczbę miesięcy pozostałych po całych latach:
=ROUNDUP(B4-TRUNC(B4/12)*12,0)
Następnie możemy to wszystko połączyć w schludny format, jak pokazano na rysunku 3:
="miesiące, czyli "&TRUNC(B4/12)& " lata",&ROUNDUP(B4-TRUNC(B4/12)*12,0)&" miesiące"
W tym używamy ampersandu do konkatenacji, czyli łączenia tekstu i obliczeń w zrozumiałe zdanie. Ja zdecydowanie wolę używać ampersandu do łączenia tekstu, ale jeśli jesteś fanem funkcji CONCATENATE w Excelu, formuła miałaby taką postać:
=CONCATENATE("miesiące, czyli ",TRUNC(B4/12)", Lata",,ROUNDUP(B4-TRUNC(B4/12)*12,0)", Miesiące").
Rysunek 3: Użyj znaku ampersand do konkatenacji tekstu i obliczeń w logiczne wyrażenie.
