Jak obliczyć rentę (annuity) w Excelu: PMT, PV, FV

Wpisujesz formułę, a Excel pokazuje wynik ze znakiem minus albo kwotę zupełnie inną niż w kalkulatorze online. Najczęściej winna jest stopa roczna wpisana tam, gdzie powinna być miesięczna, albo pomieszane znaki przepływów pieniężnych. Ten poradnik pokazuje, której funkcji użyć do jakiego zadania i jak poprawnie wpisać argumenty.

Czym jest renta (annuity) i kiedy ma sens liczenie jej w Excelu

Renta (ang. annuity) to ciąg regularnych płatności o stałej wysokości, wypłacanych lub wpłacanych w równych odstępach czasu. Może to być miesięczna wpłata na dodatkowe oszczędzanie albo stała miesięczna wypłata ze zgromadzonego kapitału. Excel ma zestaw funkcji finansowych, które opisują taki schemat przy założeniu, że stopa procentowa i wysokość płatności się nie zmieniają.

Liczenie w arkuszu ma sens, gdy chcesz szybko porównać własne założenia: inną długość oszczędzania, inną stopę czy inną kwotę wypłaty. Pamiętaj jednak, że to model matematyczny. Nie jest to prognoza świadczenia z ZUS, renty dożywotniej ani kwoty gwarantowanej przez ubezpieczyciela.

Przygotowanie arkusza: stopa okresowa, liczba okresów i znaki przepływów

Zanim wpiszesz pierwszą funkcję, przygotuj komórki wejściowe. Dzięki temu zmienisz założenia w jednym miejscu, bez przepisywania formuł. Przykładowy układ:

B1 – kapitał (wartość bieżąca)
B2 – stopa roczna (np. 5%)
B3 – liczba lat
B4 – liczba płatności w roku (12)
B5 – stopa okresowa:  =B2/B4
B6 – liczba okresów:  =B3*B4

Zasada jest prosta: jednostka stopy musi odpowiadać jednostce okresu. Przy płatnościach miesięcznych stopa okresowa to stopa roczna podzielona przez 12, a liczba okresów to liczba lat pomnożona przez 12. Uwaga: dzielenie przez 12 jest poprawne, gdy przyjmujemy kapitalizację miesięczną. Jeśli stopa roczna jest podana przy innej kapitalizacji, najpierw sprawdź, jak ją rozumieć.

Druga zasada dotyczy znaków. Excel stosuje konwencję przepływów pieniężnych: pieniądze, które wydajesz lub wpłacasz, zapisujesz jako liczby ujemne, a pieniądze, które otrzymujesz, jako dodatnie. Stąd bierze się wiele „dziwnych” minusów w wynikach.

Jeśli używasz polskiej wersji Excela, pamiętaj, że argumenty w formułach oddziela się zwykle średnikiem zamiast przecinka, a nazwy funkcji mogą być zlokalizowane. W przykładach poniżej używamy zapisu z przecinkami.

PMT: ile wynosi regularna wypłata lub wpłata

Funkcja PMT wylicza wysokość stałej płatności okresowej przy stałej stopie. Składnia: PMT(rate, nper, pv, [fv], [type]). Używasz jej, gdy znasz kapitał (lub cel do zgromadzenia), stopę i liczbę okresów, a szukasz kwoty płatności.

=PMT(B2/12, B3*12, B1)

• rate – stopa okresowa, tu stopa roczna ÷ 12,
• nper – łączna liczba płatności, tu lata × 12,
• pv – wartość bieżąca, czyli kapitał,
• fv – opcjonalna wartość przyszła (pominięta oznacza zero),
• type – opcjonalny moment płatności (0 lub 1).

Przykład liczbowy. W jednym z opisów źródłowych (Spreadsheet Planet) przyjęto kapitał 100 000 USD, stopę roczną 5%, płatności miesięczne na koniec okresu i 20 lat. Przy tych założeniach wychodzi około 659,96 USD miesięcznie. To wartość z przykładu źródłowego, obowiązująca wyłącznie przy podanych założeniach. Gdy kapitał wpiszesz jako liczbę dodatnią, Excel zwróci wynik ze znakiem minus, bo płatność traktuje jako wypływ. Jeśli wpiszesz kapitał ze znakiem minus, wynik będzie dodatni.

Pamiętaj też, że sama funkcja PMT może nie obejmować podatków, rezerw ani opłat, które w rzeczywistości zmieniają kwotę płatności.

PV: jakiej kwoty jednorazowej potrzeba na stałą miesięczną wypłatę

Funkcja PV (wartość bieżąca) odpowiada na pytanie odwrotne do PMT: ile kapitału trzeba mieć dziś, aby przez określony czas otrzymywać stałą płatność przy danej stopie. Składnia: PV(rate, nper, pmt, [fv], [type]).

B1 – wysokość miesięcznej wypłaty

B2 – stopa roczna
B3 – liczba lat

=PV(B2/12, B3*12, -B1)

Wypłatę, którą chcesz otrzymywać, wpisujemy tu ze znakiem minus, aby wynik (kapitał do zgromadzenia) wyszedł dodatni. Gdybyś w tym schemacie wpisał wypłatę z poprzedniej sekcji, czyli około 659,96 USD miesięcznie przy 5% i 20 latach, wynikiem powinno być około 100 000 USD. To dobry sposób, by sprawdzić spójność arkusza: PMT i PV opisują ten sam układ z dwóch stron.

FV: ile urośnie regularne oszczędzanie po określonym czasie

Funkcja FV (wartość przyszła) pokazuje, ile wyniesie suma regularnych wpłat wraz z odsetkami na koniec okresu. Składnia: FV(rate, nper, pmt, [pv], [type]).

B1 – miesięczna wpłata
B2 – stopa roczna
B3 – liczba lat

=FV(B2/12, B3*12, -B1)

Wpłatę wpisujemy ze znakiem minus, bo to wydatek z Twojej perspektywy. Wynik jest wtedy dodatni i oznacza kwotę, którą otrzymasz na końcu. Jeśli masz już jakiś kapitał początkowy, dodaj go w argumencie pv, również ze znakiem minus.

NPER: przez ile okresów wystarczy kapitał lub jak długo trzeba oszczędzać

Funkcja NPER zwraca liczbę okresów potrzebnych do osiągnięcia celu przy stałej stopie i stałej płatności. Składnia: NPER(rate, pmt, pv, [fv], [type]).

B1 – miesięczna wypłata
B2 – stopa roczna
B3 – kapitał początkowy

=NPER(B2/12, -B1, B3)



    
🛒 Polecany Produkt
    
Amazon.com 🛒 Polecany Produkt



Wynik jest w miesiącach; liczbę lat uzyskasz dzieląc go przez 12:
=NPER(B2/12, -B1, B3)/12

Wynik jest wyrażony w okresach, czyli przy płatnościach miesięcznych w miesiącach. Jeśli wypłata jest zbyt niska w stosunku do odsetek albo zbyt wysoka względem kapitału, Excel może zwrócić błąd, bo cel nie jest osiągalny przy podanych założeniach.

RATE: jak wyliczyć stopę procentową przy znanych płatnościach

Funkcja RATE odwraca zadanie: znasz liczbę okresów, wysokość płatności i kapitał, a szukasz stopy okresowej. Składnia: RATE(nper, pmt, pv, [fv], [type], [guess]).

B1 – liczba lat
B2 – miesięczna wypłata
B3 – kapitał


Stopa miesięczna:  =RATE(B1*12, -B2, B3)
Stopa roczna (nominalna):  =RATE(B1*12, -B2, B3)*12

RATE zwraca stopę za jeden okres, więc przy płatnościach miesięcznych otrzymasz stopę miesięczną. Aby uzyskać nominalną stopę roczną, pomnóż wynik przez 12. Funkcja działa iteracyjnie, więc przy nietypowych danych może nie znaleźć rozwiązania. Wtedy pomaga podanie własnego przybliżenia w argumencie guess.

Opisane tu funkcje można zestawić w jednej tabeli, która ułatwia wybór właściwej z nich:

Funkcja	Na jakie pytanie odpowiada	Kluczowe argumenty	Przykład zastosowania
PMT	Ile wynosi regularna płatność?	rate, nper, pv, [fv], [type]	Miesięczna wypłata z danego kapitału
PV	Ile kapitału potrzeba dziś?	rate, nper, pmt, [fv], [type]	Kapitał na stałą miesięczną wypłatę
FV	Ile urośnie oszczędzanie?	rate, nper, pmt, [pv], [type]	Suma regularnych wpłat po wybranym czasie
NPER	Ile okresów potrzeba?	rate, pmt, pv, [fv], [type]	Jak długo wystarczy kapitał przy danej wypłacie
RATE	Jaka jest stopa okresowa?	nper, pmt, pv, [fv], [type], [guess]	Stopa wynikająca ze znanych płatności

Argument type: płatność na początku i na końcu okresu

Argument type określa, kiedy w danym okresie następuje płatność. Wartość 0 lub pominięcie argumentu oznacza płatność na koniec okresu (renta zwykła), a wartość 1 – płatność na początek okresu (renta należna, ang. annuity due).

Cecha	type 0 (lub pominięty)	type 1
Moment płatności	Na koniec każdego okresu	Na początek każdego okresu
Nazwa schematu	Renta zwykła	Renta należna (annuity due)
Zapis w formule	=PMT(B2/12, B3*12, B1) lub z końcowym zerem	=PMT(B2/12, B3*12, B1, 0, 1)
Wpływ na wynik	Ustawienie domyślne, jak w przykładzie 659,96 USD	Wynik inny, bo płatności przesuwają się na wcześniejszy moment

Przesunięcie płatności na początek okresu zmienia wynik obliczeń, dlatego zawsze sprawdzaj, czy Twój schemat zakłada płatność na początku, czy na końcu miesiąca, i ustaw type zgodnie z tym założeniem.

Najczęstsze błędy i dlaczego wynik jest ujemny

Ujemny wynik to nie błąd, tylko skutek konwencji przepływów. Jeśli kapitał wpiszesz jako liczbę dodatnią (otrzymujesz go), Excel pokaże płatność jako liczbę ujemną (wydatek). Aby uzyskać dodatni wynik, odwróć znak jednego z argumentów, np. wpisz -B1 zamiast B1.

Inne częste pomyłki to:

• wpisanie stopy rocznej przy płatnościach miesięcznych (brak dzielenia przez 12),
• podanie liczby lat zamiast liczby okresów (brak mnożenia przez 12),
• pominięcie argumentu type, gdy płatność przypada na początek okresu,
• zapisanie wszystkich kwot z tym samym znakiem, co prowadzi do błędu lub bezsensownego wyniku,
• pomylenie funkcji: użycie PMT zamiast PV, gdy szukasz kapitału.

Lista kontrolna przed zaufaniem wynikowi:

• Czy jednostka stopy odpowiada jednostce okresu (miesiąc – miesiąc)?
• Czy znaki wpływów i wydatków są przeciwne?
• Czy wartość type odpowiada momentowi płatności?
• Czy założenie o miesięcznej kapitalizacji pasuje do warunków, które modelujesz?
• Czy stopa i wysokość płatności są rzeczywiście stałe w całym okresie?
• Czy wynik został sprawdzony drugą funkcją, np. PMT przez PV?

Ograniczenia: stała stopa, stała wypłata, brak podatków i opłat

Funkcje finansowe Excela opisują schemat o stałej stopie i stałej płatności, co wynika z dokumentacji Microsoft. Jeśli stopa albo wysokość wypłaty zmienia się w czasie, pojedyncza formuła nie odda tej sytuacji. Trzeba wtedy rozpisać harmonogram okres po okresie w osobnych wierszach.

Wynik takiego modelu może nie uwzględniać podatków, opłat, rezerw ani innych kosztów, które w praktyce zmniejszają lub zmieniają wypłatę. Nie należy go więc traktować jako prognozy emerytury z ZUS, renty dożywotniej ani kwoty gwarantowanej przez ubezpieczyciela. To narzędzie do porównywania własnych założeń, a nie obietnica konkretnej kwoty.

Najczęściej zadawane pytania (FAQ)

Którą funkcją policzyć wypłatę z renty?

Użyj PMT, gdy znasz kapitał, stopę i liczbę okresów. Funkcja zwróci wysokość stałej płatności okresowej.

Jak obliczyć potrzebny kapitał?

Użyj funkcji PV. Podajesz stopę okresową, liczbę okresów i pożądaną wypłatę (ze znakiem minus), a Excel zwróci kapitał potrzebny na starcie.

Dlaczego wynik jest ujemny?

Excel stosuje konwencję przepływów: wydatki są ujemne, wpływy dodatnie. Zmień znak jednego z argumentów, aby otrzymać wynik dodatni.

Jak wpisać stopę przy płatnościach miesięcznych?

Przy miesięcznej kapitalizacji wpisz stopę roczną podzieloną przez 12, a liczbę okresów jako lata pomnożone przez 12. Najpierw upewnij się, jakiej kapitalizacji dotyczy podana stopa.

Czym różni się płatność na początku od płatności na końcu okresu?

Decyduje o tym argument type: 0 (lub brak) oznacza koniec okresu, a 1 początek okresu. Zmiana momentu płatności zmienia wynik obliczeń.

Czy mogę użyć tych funkcji przy zmiennej stopie lub wypłacie?

Nie wprost. Funkcje zakładają stałą stopę i stałą płatność. Przy zmiennych parametrach potrzebny jest osobny harmonogram w arkuszu, rozpisany okres po okresie.

Dołącz do naszej społeczności