Serwis Finansowo Księgowy (F-K) 34/2005 z 23.08.2005, str. 5

Komputer w pracy: Wykorzystanie arkuszy kalkulacyjnych w pracach finansowo-księgowych staje się coraz

powszechniejsze. Przykładem takiego arkusza jest Excel – dobrze znane i wygodne narzędzie, stosowane w

procesie analizy finansowej, przetwarzania danych, tworzenia raportów, jednym słowem – wspomagające

proces podejmowania decyzji.

Funkcje finansowo-księgowe w Excelu – poradnik dla
początkujących

Excel oferuje wiele możliwości przeprowadzania złożonych operacji na zbiorach danych umieszczonych zarówno

w poszczególnych jego arkuszach, jak i w źródłach zewnętrznych. Do jego podstawowych zalet należy przyjazna

obsługa, łatwość operowania danymi i ich przetwarzania, elastyczność oraz graficzna prezentacja wyników

pracy. Okazuje się, że arkusze kalkulacyjne są także pomocne przy przeprowadzaniu symulacji, to znaczy

wielokrotnym rozwiązywaniu tego samego problemu z różnymi danymi wejściowymi. Wszystkie te cechy

sprawiają, że Excel staje się niezastąpiony w codziennych pracach finansowo-księgowych i zdobywa coraz

więcej zwolenników.

Niniejszy artykuł pomaga zapoznać się z najważniejszymi funkcjami finansowymi Excela, takimi jak: FV,

FVSCHEDULE, NPER, PV, PMT, EFFECT, NOMINAL, NPV, IRR, MIRR oraz narzędziem SZUKAJ WYNIKU.

Okazuje się, że umiejętność ich rozpoznania, prawidłowe zrozumienie mechanizmu działania oraz praktyczne

zastosowanie przyczyniają się do podniesienia efektywności pracy. Konstrukcja wymienionych funkcji ma swoje

podłoże w teorii finansów, dlatego w dalszych rozważaniach pomocne będzie przypomnienie podstawowych

informacji z tego obszaru, takich jak wartość pieniądza w czasie (w tym przyszła i obecna wartość pieniądza),

procent prosty, procent składany, wartość przyszła renty, nominalna i efektywna stopa procentowa, dynamiczne

metody oceny projektów inwestycyjnych.

Wartość pieniądza w czasie

Podstawowe przyczyny, dla których pieniądz zmienia swą wartość w czasie, to:

• inflacja – wcześniej otrzymane kwoty mają większą siłę nabywczą,

• możliwość reinwestycji – środki wcześniej postawione do dyspozycji mogą być przeznaczone na

inwestycję i stać się źródłem dodatkowych zysków i wpływów,

• ryzyko – każdej transakcji towarzyszy ryzyko związane z jej realizacją i każdej transakcji bardziej

oddalonej w czasie towarzyszy wzrost tego ryzyka.

W podejmowaniu decyzji finansowych pomaga sprowadzenie do jednego mianownika wartości kwot

przepływających w różnych okresach. Można to uzyskać przez wyznaczenie punktu odniesienia (teraźniejszość

lub dowolny moment w przyszłości) i przeprowadzenie operacji dyskontowania lub kapitalizacji. W przypadku

dyskontowania punktem odniesienia staje się wartość obecna (ang. present value – PV), gdyż jest to proces

wyznaczania wartości obecnej przyszłych przepływów pieniężnych. Natomiast w przypadku kapitalizacji punktem

odniesienia będzie wartość przyszła (ang. future value – FV), gdyż jest to proces wyznaczania wartości

końcowej przepływów pieniężnych.

Wartość przyszła pieniądza

Jak wspomniano, koncepcja zmiany wartości pieniądza w czasie jest wykorzystywana w obliczaniu przyszłej

wartości przepływu pieniężnego lub strumienia przepływów pieniężnych przy stałych okresach kapitalizacji. W

praktyce do obliczenia przyszłej wartości pieniądza w czasie wykorzystuje się kilka formuł matematycznych, z

których zaprezentujemy cztery podstawowe.

Procent prosty

INFORLEX.PL Księgowość

http://ksiegowosc3.inforlex.pl/szukaj/wyniki/drukuj,,I25.2005.034.000...

1 of 17

2013-06-12 20:18

Podstawowy wzór służący obliczeniu wartości przyszłej kapitału dla procentu prostego jest następujący:

gdzie:

FV

n

– wartość przyszła pieniądza na koniec okresu,

PV – wartość początkowa (obecna) pieniądza,

r – nominalna stopa procentowa, dla roku kalendarzowego równego 365 dni lub 366 dni w przypadku lat

przestępnych,

n – czas oprocentowania wyrażony liczbą dni.

Wzór jest wykorzystywany najczęściej w operacjach krótkoterminowych (poniżej roku), gdy odsetki od kapitału

początkowego nalicza się jednorazowo na koniec okresu inwestycji. Jeżeli znane są trzy z czterech zmiennych,

to przekształcając daną formułę zawsze możemy wyznaczyć pozostałą, czwartą niewiadomą i rozwiązać

równanie.

Procent składany

Technika procentu składanego zakłada reinwestycję otrzymywanych odsetek, czyli kapitalizację. Oznacza to

powiększenie wartości kapitału na koniec n-tego okresu o stały procent w stosunku do wartości kapitału na

początku tego okresu. W tym przypadku dla pojedynczej płatności wykorzystujemy następującą formułę, w

której kapitalizacja odsetek dokonywana jest na koniec każdego okresu:

gdzie:

FV – wartość przyszła na koniec n-tego okresu,

PV – wartość początkowa (obecna) pieniądza,

r – nominalna stopa procentowa,

n – okres, na którego koniec jest wyliczana FV.

W porównaniu z procentem prostym wzrost wartości kapitału na koniec n-tego okresu (FV) w dużym stopniu

będzie w tym przypadku zależeć od częstotliwości kapitalizacji oraz wysokości stopy procentowej, której

niewielka zmiana wywołuje istotne różnice w końcowym wyniku.

Wartość przyszła przy zmiennym oprocentowaniu

Natomiast wartość przyszłą kapitału FV, liczonego procentem składanym dla zmiennej stopy procentowej w

poszczególnych okresach, obliczamy rozwiązując następujące równanie:

gdzie:

PV – wartość początkowa (obecna) pieniądza,

r

1

, r

2

,..., r

n

– oczekiwane stopy procentowe w poszczególnych okresach,

n

1

, n

2

,..., n – okres, na jaki złożono kapitał przy danej stopie procentowej,

FV

n

– wartość przyszła na koniec n-tego okresu.

Wartość przyszła renty

INFORLEX.PL Księgowość

http://ksiegowosc3.inforlex.pl/szukaj/wyniki/drukuj,,I25.2005.034.000...

2 of 17

2013-06-12 20:18

W sytuacji gdy pod koniec okresu płacona jest stała suma pieniężna oznaczona symbolem PMT, przy czym po

dokonaniu wpłaty dochody są kapitalizowane, mamy do czynienia z wartością przyszłą renty zwykłej (ang.

ordinary annuity), do której określenia stosuje się wzór:

gdzie:

PMT – wartość jednej płatności okresowej,

n – liczba stałych płatności,

r – stopa oprocentowania,

FVA

n

– wartość przyszła renty zwykłej.

Przy płatnościach następujących na początku okresu mamy do czynienia z wartością przyszłą renty należnej

(ang. annuity due).

Funkcja Excela FV – wartość przyszła

Przedstawione formuły matematyczne (procent prosty, procent składany, wartość przyszła renty) mogą być

obliczone w arkuszu kalkulacyjnym Excel za pomocą funkcji FV.

Funkcja FV umożliwia obliczenie wartości przyszłej kapitału lub stałych płatności (annuitetowych) z

uwzględnieniem kapitalizacji odsetek stałą stopą oprocentowania.

FV (stopa;liczba_rat;rata;wa;typ)

gdzie:

stopa – stopa procentowa stała dla wszystkich okresów,

liczba_rat – liczba okresów płatności i równa jej liczba kapitalizacji,

rata – wysokość okresowej płatności (domyślnie zero); argument można pominąć, gdy podany jest argument

wa,

wa – początkowa wartość kapitału (domyślnie zero); argument można pominąć, gdy podany jest argument rata,

typ – określa sposób płatności,

0 – oznacza „na końcu okresu” (wartość domyślna),

1 – „na początku okresu”.

Zgodnie z przyjętą koncepcją kierunku przepływu środków pieniężnych znak minus (–) oznacza wydatki, a znak

plus (+) wpływy.

Rozpatrzmy dwa przykłady (przykład 1 i 2), w których będzie nas interesować wartość środków na rachunku

bankowym po upływie 12 miesięcy.

Przykład 1

Umieszczamy w banku środki w wysokości 10 000 PLN na okres 12 miesięcy, przy oprocentowaniu rocznym 8%

i miesięcznej kapitalizacji odsetek. Porównajmy wynik z wielkością kwoty, jaką otrzymalibyśmy przy jednokrotnej

kapitalizacji, przypadającej na koniec roku (procent prosty).

Przykład może być rozwiązany za pomocą kalkulatora (A) bądź z wykorzystaniem arkusza kalkulacyjnego (B,

C):

A. W pierwszym przypadku posługujemy się wzorami matematycznymi na FV dla procentu prostego i procentu

składanego:

FV = 10 000 (1 + 0,08) = 10 800 PLN (procent prosty)

INFORLEX.PL Księgowość

http://ksiegowosc3.inforlex.pl/szukaj/wyniki/drukuj,,I25.2005.034.000...

3 of 17

2013-06-12 20:18

FV = 10 000 (1 + 0,08/12)

12

= 10 830 PLN (procent składany)

B. Arkusz kalkulacyjny umożliwia dokonanie obliczeń arytmetycznych z wykorzystaniem podstawowych

operatorów matematycznych (+, -, *, /, ˆ). Wpisując do komórki o adresie B2 formułę równania na obliczenie

procentu składanego: =10000*(1+8%/12)ˆ12, otrzymamy taki sam wynik jak poprzednio (w Excelu formuła

zawsze poprzedzona jest znakiem „=”).

C. Możemy również skorzystać z gotowej funkcji FV Excela. Deklarując następujący zapis zgodny ze składnią

funkcji, oddzielamy poszczególne argumenty liczbowe średnikami i wpisujemy bezpośrednio do pustej komórki

arkusza:

=FV(8%/12;12;0;-10000;0)

W przypadku procentu prostego należy wpisać do komputera następującą funkcję, w której deklarujemy

pojedynczą płatność i pojedynczą kapitalizację na koniec roku:

=FV(8%;1;0;-10000;0)

W każdej z przedstawionych metod (A, B, C) uzyskujemy ten sam wynik końcowy obliczeń. Jednak

niewątpliwą przewagą dokonywania obliczeń za pomocą arkusza kalkulacyjnego Excel, w stosunku do

użycia kalkulatora, jest możliwość napisania i wyliczenia złożonych równań matematycznych, które

dodatkowo mogą być poszerzone o funkcje wbudowane w arkusz kalkulacyjny.

W przykładzie 2 przedstawiono zastosowanie funkcji FV do obliczenia przyszłej wartości kapitału z

uwzględnieniem okresowych wpłat (PMT).

Przykład 2

Umieszczamy w banku środki w wysokości 10 000 PLN na okres 12 miesięcy, przy oprocentowaniu

rocznym 8% i miesięcznej kapitalizacji odsetek. Jednocześnie na początku każdego kolejnego miesiąca

dopłacamy 100 PLN.

Jaką kwotę otrzymamy po upływie roku?

Szukana przyszła wartość kapitału FV równa się 12 074,99 PLN. Kwota ta została uzyskana przez rozwiązanie

następującego równania, które jest sumą procentu składanego i przyszłej wartości renty zwykłej:

FV = FV

n

+ FVA

n

= PV (1+r)

n

+ PMT [(1 + r)

n

– 1]/r

Natomiast liczbowo formuła, która umożliwi obliczenie powyższego równania w Excelu, to zapis następującego

ciągu operacji, umieszczony w komórce arkusza:

=10000*(1+8%/12)^12+100*((1+8%/12)ˆ12-1)/(8%/12)

Okazuje się, że znacznie prostszym rozwiązaniem będzie skorzystanie, analogicznie jak w przykładzie 1, z funkcji

FV Excela. Zapis jest prostszy i trudniej w nim o popełnienie błędu. W dowolnej komórce arkusza

kalkulacyjnego, zgodnie ze składnią funkcji FV Excela, deklarujemy do obliczenia następujący zapis:

=FV (8%/12;12;-100;-10000;0)

W praktyce często jednak się zdarza, że nie pamiętamy dokładnie składni interesującej nas funkcji. W takim

przypadku powinniśmy skorzystać z pomocy Kreatora funkcji, wykonując następujące czynności:

• na standardowym pasku narzędzi klikamy przycisk kreatora funkcji – f

x

lub na pasku Menu wybieramy

INFORLEX.PL Księgowość

http://ksiegowosc3.inforlex.pl/szukaj/wyniki/drukuj,,I25.2005.034.000...

4 of 17

2013-06-12 20:18

rozwijalne polecenie Wstaw,

• odszukujemy i klikamy pozycję Funkcja,

• w oknie dialogowym Kategorie funkcji wybieramy rodzaj funkcji: Finansowe,

• następnie w prawym oknie Nazwa funkcji wyszukujemy funkcję FV i wybieramy ją klikając dwukrotnie,

• pojawia się okno dialogowe funkcji FV, które należy wypełnić danymi. Dla przykładu 2 są one następujące:

• wreszcie klikamy przycisk OK,

• okno dialogowe Kreatora funkcji zostanie zamknięte, a wynik i treść funkcji zapisane w arkuszu.

Jak widać, treść formuły oraz uzyskany wynik, który otrzymaliśmy przy użyciu Kreatora funkcji, nie

różni się od wcześniejszych kalkulacji. Niewątpliwymi zaletami tej formy rozwiązywania problemu w

Excelu jest duża łatwość tworzenia funkcji, dostęp do podpowiedzi na temat poszczególnych

argumentów funkcji, jej składni, możliwość wprowadzania danych z zaznaczonego obszaru arkusza oraz

dowolne zmiany ich wartości, natychmiastowy wynik obliczeń, a także pojawiające się komunikaty o

błędach.

Funkcja FVSCHEDULE

W realiach gospodarki założenie stałej stopy procentowej w odniesieniu do długiego czasu rzadko jest spełnione,

dlatego przeprowadzone do tej pory rozważania warto rozszerzyć. Kolejnym analizowanym problemem będzie

więc sytuacja różnego oprocentowania w poszczególnych okresach inwestycji, którą można zobrazować za

pomocą przykładu 3.

Przykład 3

Umieszczamy na rachunku bankowym środki w kwocie 10 000 PLN na okres 3 lat. W pierwszych dwóch

latach n1 = 2 oprocentowanie wynosi r1 = 8%, a w trzecim roku wzrasta do r2 = 10%. Odsetki są

kapitalizowane co roku. Jaką kwotę otrzymamy po upływie trzech lat?

Ponownie dysponujemy kilkoma metodami rozwiązania:

A. Wartość przyszłą można wyznaczyć rozwiązując za pomocą kalkulatora następujące równanie:

FV

3

= PV (1 + r

1

)

n1

. (1 + r

2

)

n2

= 10 000. (1 + 0,08)

2

. (1 + 0,1)

1

= 12 830,40 PLN

B. Gdybyśmy chcieli skorzystać z arkusza kalkulacyjnego Excel i poznanej funkcji FV, wówczas uzyskamy taki

sam rezultat obliczeń:

=FV(10%;1;0;-FV(8%;2;0;-10000);0)

INFORLEX.PL Księgowość

http://ksiegowosc3.inforlex.pl/szukaj/wyniki/drukuj,,I25.2005.034.000...

5 of 17

2013-06-12 20:18

Wewnętrzna funkcja FV ze znakiem minus oznacza wartość przyszłą kapitału po upływie dwóch lat, którą

następnie inwestuje się na pozostały okres, tj. roku. Wewnętrzna funkcja FV ze znakiem minus staje się

argumentem zewnętrznej funkcji FV.

Innym alternatywnym sposobem rozwiązania przykładu 3 będzie skorzystanie z funkcji FVSCHEDULE Excela,

której opis i składnia przedstawiają się następująco:

Funkcja FVSCHEDULE umożliwia obliczenie wartości końcowej kapitału przy zmiennej stopie oprocentowania w

poszczególnych okresach z uwzględnieniem kapitalizacji odsetek.

FVSCHEDULE (kapitał;stopy)

gdzie:

kapitał – początkowa wartość kapitału,

stopy – tablica stóp oprocentowania w kolejnych okresach, gdzie puste komórki oznaczają brak

oprocentowania.

eśli któraś z funkcji Excela nie jest dostępna (np. FVSCHEDULE), należy ją zainstalować: wybrać z menu

Narzędzia, pozycję Dodatki i w oknie Dostępne dodatki wybrać pozycję Analysis ToolPark, a następnie

kliknąć przycisk OK.

Korzystając z Kreatora funkcji, w polu Kapitał należy wpisać wartość początkową kapitału równą w tym

przypadku 10 000 lub adres komórki B2, a w polu Stopy zaznaczony myszą obszar C3:E3.

Gdyby w omawianym przykładzie odsetki przez pierwsze dwa lata były kapitalizowane kwartalnie, a w trzecim –

co pół roku, to zapis funkcji FVSCHEDULE przy wykorzystaniu Kreatora funkcji wyglądałby następująco:

Możliwy jest też inny zapis funkcji FVSCHEDULE – w postaci wyszczególnienia kolejnych argumentów,

oddzielenia ich średnikami i umieszczenia między nawiasami klamrowymi:

INFORLEX.PL Księgowość

http://ksiegowosc3.inforlex.pl/szukaj/wyniki/drukuj,,I25.2005.034.000...

6 of 17

2013-06-12 20:18

=FVSCHEDULE(10000;{0,02;0,02;0,02;0,02;0,02;0,02;0,02;0,02;0,05;0,05})

Funkcja NPER

Przechodząc do kolejnego zagadnienia warto zwrócić uwagę, że w równaniu procentu składanego (podobnie jak

w przypadku procentu prostego) występują cztery zmienne. Oznacza to, że każdą z nich możemy wyznaczyć,

znając wartość trzech pozostałych. Jednak czasami pewną trudność może sprawić wyznaczenie i obliczenie

zmiennej n, czyli czasu oprocentowania. W jej wyliczeniu będzie pomocna następująca formuła matematyczna,

powstała z przekształcenia wzoru na procent składany:

Program Excel oferuje funkcję NPER, której zastosowanie sprawia, że problem wyliczenia czasu

oprocentowania staje się o wiele prostszy. Oto krótki jej opis (przykład 4):

Funkcja NPER umożliwia obliczenie liczby rat niezbędnych do zgromadzenia kapitału lub zmiany jego wysokości,

spłaty kredytu lub pokrycia pożyczki, przy założeniu stałego oprocentowania i stałych rat.

NPER(stopa;rata;wa;wp;typ)

gdzie:

stopa – stopa procentowa stała dla wszystkich okresów,

rata – wysokość stałej okresowej płatności (domyślnie zero); argument można pominąć, gdy podane są

argumenty wa i wp,

wa – początkowa wartość kapitału (domyślnie zero); wartość argumentu może być pominięta, gdy podany jest

argument rata,

wp – wartość przyszła kapitału (domyślnie zero), przyjęta na koniec całego okresu wpłat,

typ – określa sposób płatności,

0 – oznacza „na końcu okresu” (wartość domyślna),

1 – „na początku okresu”.

Zgodnie z przyjętą koncepcją kierunku przepływu środków pieniężnych znak minus (-) oznacza wydatki, a znak

plus (+) wpływy.

Przykład 4

Umieszczamy w banku środki w wysokości 10 000 PLN przy oprocentowaniu rocznym 8% i miesięcznej

kapitalizacji odsetek. Jednocześnie na początku każdego kolejnego miesiąca dopłacamy 100 PLN. Po

upływie jakiego okresu zgromadzimy kwotę równą 15 000 PLN?

Rozwiązanie, w formie wpisania do komórki B2 funkcji: =NPER(8%/12;-100;-10000;15000), wynosi 27,44

(domyślnie: liczba miesięcy). Oznacza to, że po upływie 2 lat, 3 miesięcy i 14 dni (= 365/12 * 0,44) lokata

przekroczy kwotę 15 000 PLN.

Funkcja PV – wartość obecna pieniądza

Odwrotnością procesu kapitalizacji i omówionego procentu składanego jest dyskontowanie, czyli wyznaczanie

wartości obecnej PV przepływu pieniężnego lub strumienia przepływów pieniężnych. Jej wartość w przypadku

pojedynczej płatności obliczamy za pomocą następującego wzoru:

INFORLEX.PL Księgowość

http://ksiegowosc3.inforlex.pl/szukaj/wyniki/drukuj,,I25.2005.034.000...

7 of 17

2013-06-12 20:18

gdzie:

FV – oczekiwana wartość przyszła kapitału,

PV – wartość początkowa (obecna) pieniądza,

r – nominalna stopa procentowa,

n – liczba kapitalizacji odsetek w roku.

Użycie arkusza kalkulacyjnego Excel umożliwia wyznaczenie wartości obecnej wymienionej formuły

matematycznej za pomocą funkcji PV (przykład 5):

Funkcja PV umożliwia obliczenie wartości bieżącej (zdyskontowanie) kapitału lub szeregu płatności.

PV(stopa;liczba_rat;rata;wp;typ)

gdzie:

stopa – stopa procentowa dla wszystkich okresów,

liczba_rat – liczba okresów płatności i równa jej liczba kapitalizacji,

rata – wysokość okresowej płatności, stała dla wszystkich okresów; może być pominięta, gdy podany jest

argument wp,

wp – wartość przyszła, przyjęta na koniec całego okresu wpłat; może być pominięta, gdy podany jest argument

rata,

typ – określa sposób płatności,

0 – oznacza „na końcu okresu” (wartość domyślna),

1 – „na początku okresu”.

Zgodnie z przyjętą koncepcją kierunku przepływu środków pieniężnych znak minus (-) oznacza wydatki, a znak

plus (+) wpływy.

Zademonstrujmy wykorzystanie funkcji PV na przykładzie (przykład 5).

Przykład 5

Jaki kapitał należy wpłacić na rachunek bankowy, aby otrzymać po roku kwotę 5000 PLN, przy rocznej

stopie oprocentowania równej 8% i miesięcznej kapitalizacji?

A. Pierwszy sposób rozwiązania, to użycie kalkulatora. Podstawiamy wartości do równania i wyliczamy PV:

PV = 5000( 1+ 0,08/12)

-12

= 4616,81 PLN

B. W arkuszu kalkulacyjnym Excel to samo równanie będzie miało następującą treść formuły:

=5000*(1+8%/12)ˆ(-12)

C. Z kolei wykorzystanie opisanej funkcji PV Excela będzie polegać na wpisaniu danych liczbowych zgodnie z jej

składnią PV(stopa;liczba_rat;rata;wp;typ) bezpośrednio do komórki arkusza lub użycie Kreatora funkcji:

INFORLEX.PL Księgowość

http://ksiegowosc3.inforlex.pl/szukaj/wyniki/drukuj,,I25.2005.034.000...

8 of 17

2013-06-12 20:18

Otrzymany wynik ze znakiem minus oznacza poniesienie wydatków. Tak więc, aby po roku otrzymać 5000 PLN,

należy wpłacić na rachunek bankowy kwotę 4616,81 PLN.

Funkcja PMT

Kolejnym z omawianych zagadnień jest wyznaczanie wysokości okresowych płatności niezbędnych do

zgromadzenia kapitału. Przydatną funkcją do rozwiązania tego problemu jest funkcja PMT. Jej opis i składnia są

następujące (przykład 6):

Funkcja PMT (ang. periodic constant payment) umożliwia obliczenie wysokości okresowych płatności

niezbędnych do zgromadzenia kapitału, spłaty kredytu lub pokrycia pożyczki, przy założeniu stałego

oprocentowania i stałych rat.

PMT(stopa;liczba_rat;wa;wp;typ)

gdzie:

stopa – stopa procentowa stała dla wszystkich okresów,

liczba_rat – całkowita liczba rat,

wa – kapitał początkowy; wartość argumentu może być pominięta, gdy podany jest argument wp,

wp – wartość przyszła kapitału (domyślnie zero), przyjęta na koniec całego okresu wpłat; może być pominięta,

gdy podany jest argument wa,

typ – określa sposób płatności,

0 – oznacza „na końcu okresu” (wartość domyślna),

1 – „na początku okresu”.

Zgodnie z przyjętą koncepcją kierunku przepływu środków pieniężnych znak minus (-) oznacza wydatki, a znak

plus (+) wpływy.

Przykład 6

Jaka powinna być wysokość miesięcznej raty spłaty kredytu (dokonywanej na koniec każdego miesiąca)

zaciągniętego na okres 2 lat w kwocie 20 000 PLN? Nominalna stopa procentowa jest równa 10%, a

odsetki są kapitalizowane co miesiąc.

Do obliczenia wysokości raty użyjemy formuły w postaci:

=PMT(10%/12;2*12;20000;0;0)

i uzyskamy wynik –922,90 PLN. Ujemny wynik oznacza wydatki ponoszone na spłacenie kredytu.

INFORLEX.PL Księgowość

http://ksiegowosc3.inforlex.pl/szukaj/wyniki/drukuj,,I25.2005.034.000...

9 of 17

2013-06-12 20:18

Funkcje EFFECT i NOMINAL

W celu porównania różnych stóp oprocentowania należy sprowadzić je do jednego mianownika. Innymi słowy,

aby porównać ze sobą oferowane na rynku oprocentowanie, wyrażone przeważnie za pomocą stóp

nominalnych, należy sprowadzić je do wspólnego mianownika, czyli posłużyć się stopami efektywnymi, które

odzwierciedlają rzeczywisty przyrost kapitału w skali roku. Wzory na efektywną i nominalną roczną stopę

procentową mają postać:

gdzie:

r

efektywna

– efektywne oprocentowanie roczne,

r

nominalna

– nominalne oprocentowanie roczne,

m – liczba okresów kapitalizacji w roku (np. miesięczna kapitalizacja m=12).

Excel oferuje dwie funkcje (EFFECT i NOMINAL) związane z przeliczaniem stóp procentowych na stopę

efektywną i nominalną (przykład 7 i 8).

Funkcja EFFECT umożliwia obliczenie efektywnej rocznej stopy procentowej na podstawie nominalnej stopy

procentowej i liczby kapitalizacji w roku.

EFFECT(stopa_nominalna;npery)

gdzie:

stopa_nominalna – nominalna stopa oprocentowania,

npery – liczba okresów składanych w roku, liczba kapitalizacji w okresie, zaokrąglana do liczby całkowitej.

Przykład 7

W Excelu można zbudować tabelę przeliczającą nominalne stopy procentowe na stopy efektywne, w zależności

od liczby kapitalizacji:

W wierszu drugim umieszczono nominalne stopy oprocentowania z zakresu od 5% do 8,0%. W kolumnie A

wpisano liczbę kapitalizacji w okresie (np. 4 oznacza kapitalizację kwartalną). Do komórki B4 wpisujemy formułę

funkcji =EFFECT(B$2;$A4). Ustawiamy wskaźnik myszy na jej prawym dolnym rogu. Gdy wskaźnik myszy

przyjmie kształt czarnego krzyżyka, wciskamy lewy przycisk myszy i po przeciągnięciu w prawo do komórki o

adresie N4, zwalniamy przycisk myszy. Następnie, ponownie wciskając przycisk myszy na prawym dolnym rogu

komórki N4, przeciągamy ją w dół do komórki N9. Cała tabela wypełni się wartościami. W zależności od

ustawionego formatu wyświetlania otrzymamy wyniki w formie wartości procentowych bądź liczbowych, które

zaokrąglamy do interesującej nas liczby cyfr po przecinku.

INFORLEX.PL Księgowość

http://ksiegowosc3.inforlex.pl/szukaj/wyniki/drukuj,,I25.2005.034.000...

10 of 17

2013-06-12 20:18

Przykład 8

Aby dowiedzieć się, jaką kwotę otrzymamy po roku oszczędzania wpłacając do banku 10 000 PLN, przy

dziennej kapitalizacji odsetek (365 dni w roku) i nominalnej miesięcznej stopie procentowej wynoszącej 0,65%,

wpisujemy do arkusza kalkulacyjnego następującą formułę, która wykorzystuje funkcję EFFECT:

=10000*(1+EFFECT(0,65%*12;365))

co zobrazowane zostało w komórce B2 arkusza:

Dla tak zapisanej formuły otrzymamy wartość równą 10 811,14 PLN. Wynik możemy sprawdzić, posługując się

kalkulatorem i obliczając wartość przyszłą kapitału FV przy założeniu, że kapitalizacja odsetek odbywa się

częściej niż raz w roku:

FV = 10 000(1 + (0,0065 × 12)/365)

365×1

= 10 811,14 PLN

W procesie przechodzenia z wartości stóp efektywnych na stopy nominalne możemy posłużyć się funkcją

NOMINAL (przykład 9):

Funkcja NOMINAL umożliwia obliczenie nominalnej stopy procentowej za dany okres, na podstawie efektywnej

stopy procentowej i określonej liczby okresów kapitalizacji.

NOMINAL(stopa_efektywna;npr)

gdzie:

stopa_efektywna – efektywna stopa oprocentowania,

npr – liczba składanych okresów rocznie.

Przykład 9

W celu obliczenia, jaka powinna być nominalna miesięczna stopa oprocentowania lokaty, przy miesięcznej

kapitalizacji odsetek i inflacji 4,5% w skali roku, która zapewni, że wartość powierzonych bankowi środków

wzrośnie w skali roku o 1,5% powyżej inflacji, powinniśmy użyć w Excelu następującej funkcji NOMINAL:

=NOMINAL((4,5%+1,5%);12)/12

Po wpisaniu tej funkcji w komórkę arkusza (ewentualnie korzystając z Kreatora funkcji) otrzymamy wynik

równy 0,4868%. Oznacza to, że uzyskanie wzrostu kapitału o 1,5% powyżej inflacji zapewni nominalna

miesięczna stopa procentowa równa 0,4868%. Jest to prostszy sposób niż podstawienie do wzoru i obliczenie

za pomocą kalkulatora:

r

nominalna

= ([((0,045 + 0,015) + 1)

1/12

– 1].12)/12 = 0,00486755, to znaczy 0,4868%

Ocena projektów inwestycyjnych

Do oceny efektywności projektów inwestycyjnych można wykorzystać jedną z metod opisanych w literaturze

finansowej. Do najbardziej popularnych należą metody dynamiczne, zwane także dyskontowymi, które

uwzględniają zmianę wartości pieniądza w czasie. Można wśród nich wymienić: wartość bieżącą netto NPV,

wewnętrzną stopę zwrotu IRR i zmodyfikowaną wewnętrzną stopę zwrotu MIRR. Wszystkie trzy metody mają

swoje odpowiedniki w funkcjach finansowych Excela, które zostaną zaprezentowane w dalszej części artykułu.

Przy podejmowaniu decyzji inwestycyjnych firmy uwzględniają dane, których dostarczają wszystkie wymienione

metody, ponieważ każda z nich przekazuje inny rodzaj informacji. W konsekwencji pomaga to w wyborze

najlepszego projektu, który maksymalizuje wartość przedsiębiorstwa.

Funkcja NPV – wartość bieżąca netto

INFORLEX.PL Księgowość

http://ksiegowosc3.inforlex.pl/szukaj/wyniki/drukuj,,I25.2005.034.000...

11 of 17

2013-06-12 20:18

Spośród metod oceny opłacalności projektów inwestycyjnych metoda NPV – wartości bieżącej netto (ang. net

present value) wydaje się najbardziej skuteczna i uniwersalna, ma przy tym najmniej mankamentów.

NPV to wartość otrzymywana przez zdyskontowanie różnicy między wpływami i wydatkami pieniężnymi z

przedsięwzięcia, generowanymi przez cały czas jego eksploatacji przy stałym poziomie oczekiwanej stopy

zwrotu (stopy dyskontowej), oddzielnie dla każdego okresu prognozy. Różnica ta jest dyskontowana na moment,

w którym przewiduje się rozpoczęcie budowy obiektu lub początek inwestycji. NPV stanowi zatem różnicę

między zdyskontowanymi przepływami pieniężnymi a nakładami początkowymi. Oblicza się ją według

następującego wzoru:

gdzie:

NCF

t

– strumień przepływów pieniężnych netto w okresie t, ustalony jako różnica pomiędzy wpływami i

nakładami,

r – stopa dyskontowa ustalana jako aktualna stopa oprocentowania występująca na rynku, skorygowana o

specyficzny dla każdego projektu stopień ryzyka,

n – okres równy okresowi użytkowania obiektów,

t – kolejne okresy (najczęściej lata) eksploatacji inwestycji.

Ustalana stopa dyskontowa określa minimalny zwrot nakładów wymagany przez inwestorów. Może być

wyrażona jako średnioważony koszt kapitału (WACC), uwzględniający zarówno kapitały własne, jak i obce.

Analiza efektywności inwestycji oparta na metodzie NPV zakłada odrzucenie inwestycji w przypadku, gdy

wartość bieżąca netto jest ujemna, oraz przyjęcie spośród potencjalnych projektów tego, który ma wyższą

wartość NPV.

Funkcja NPV umożliwia obliczenie zdyskontowanych wartości netto serii różnych przepływów pieniężnych dla

danej stopy dyskontowej.

NPV(stopa;wartość1;wartość2;...)

gdzie:

stopa – stopa dyskontowa jednakowa dla wszystkich okresów,

wartość1; wartość2;... – przepływy pieniężne w okresie 1, 2..., przypadające na koniec każdego okresu.

Przykład 10

Poniesione na początku nakłady inwestycyjne w wysokości 1000 PLN przyniosły w każdym kolejnym okresie

wpływy netto w wysokości 500 PLN, co można przedstawić graficznie. Przy stopie dyskontowej równej 11%

rocznie suma zdyskontowanych przepływów na bieżącą chwilę równa się 221,86 PLN.

Inwestycja charakteryzuje się następującymi przepływami pieniężnymi: nakłady początkowe mają wysokość 250

tys. PLN, a przewidywane przepływy netto z inwestycji w kolejnych latach wyniosą: 50 tys., 75 tys., 100 tys. i

125 tys. PLN. Przyjmując roczną stopę procentową w wysokości 12%, wartość netto inwestycji w Excelu

INFORLEX.PL Księgowość

http://ksiegowosc3.inforlex.pl/szukaj/wyniki/drukuj,,I25.2005.034.000...

12 of 17

2013-06-12 20:18

zastanie obliczona następująco:

Do komórki F4 wpisujemy formułę funkcji NPV albo korzystamy z Kreatora funkcji. Należy zwrócić uwagę na

to, że funkcja NPV Excela nie uwzględnia przepływów w okresie zerowym – komórka B2 (bieżące nakłady), i

oblicza przepływy od pierwszego do ostatniego okresu – komórki C2:F2. W związku z tym do wartości

zdyskontowanych przepływów, wyrażonych zapisem =NPV(B3;C2:F2), należy dodać przepływy okresu

zerowego – nakłady początkowe (komórka B2). Inwestycja okazuje się opłacalna z punktu widzenia

ekonomicznego, co wyraża dodatni wynik NPV uzyskany w komórce F4.

Elastyczność arkusza kalkulacyjnego Excel częstokroć umożliwia wykonanie tego samego zadania więcej niż

jedną metodą. Poznanie różnych procedur pozwala wybrać najbardziej odpowiednią. Inna droga rozwiązania

przykładu 10 w Excelu wygląda następująco:

W komórkach o adresach B4:F4 obliczamy wartość bieżącą (zdyskontowaną) przepływów pieniężnych netto

NCF. W tym celu w komórkę B4 wpisujemy następującą formułę: =B2/(1+$B$3)^B1, którą przeciągamy do

komórki F4. Komórki B4, C4, D4, E4 i F4 zostaną wypełnione zdyskontowanymi wartościami, które następnie

sumujemy w komórce F5, klikając przycisk Autosumy Σ i zaznaczamy interesujący nas obszar B4:F4.

Otrzymana wartość NPV równa się wartości uzyskanej w pierwszym wariancie rozwiązania:

Funkcja IRR – wewnętrzna stopa zwrotu

Drugą szeroko stosowaną metodą oceny efektywności inwestycji jest wewnętrzna stopa zwrotu IRR (ang.

internal rate of return). Jest to taka stopa dyskontowa, przy której wartość bieżąca nakładów pieniężnych równa

się wartości bieżącej wpływów pieniężnych w całym okresie przedsięwzięcia (PV nakładów = PV wpływów).

Wartość NPV jest w takim przypadku równa zeru. Zastosowanie tej metody obrazuje stopę zysku możliwą do

osiągnięcia z danego przedsięwzięcia. Wewnętrzna stopa zwrotu jest zwykle porównywana ze stopą

średnioważonego kosztu kapitału (WACC) i zakłada przyjęcie takiego wariantu projektu, w którym IRR > WACC.

W przypadku gdy IRR jest niższa od WACC, przyjęcie projektu powoduje obciążenie firmy kosztami i

zmniejszenie majątku. Z ekonomicznego punktu widzenia w wyborze najbardziej opłacalnego wariantu spośród

potencjalnych projektów logika nakazuje zaakceptować projekt o najwyższej wartości IRR. Formułę

matematyczną wewnętrznej stopy zwrotu można zapisać przy użyciu wzoru:

gdzie:

INFORLEX.PL Księgowość

http://ksiegowosc3.inforlex.pl/szukaj/wyniki/drukuj,,I25.2005.034.000...

13 of 17

2013-06-12 20:18

NCF

t

– wielkość przepływów pieniężnych netto w okresie t,

n – okres równy czasowi użytkowania obiektów,

t – kolejne okresy (najczęściej lata) eksploatacji inwestycji,

IRR – wewnętrzna stopa zwrotu.

Poniesione na początku nakłady inwestycyjne w wysokości 1000 PLN przyniosły w każdym kolejnym okresie

wpływy netto w wysokości 500 PLN, co można przedstawić graficznie. Szukamy stopy dyskontowej IRR

spełniającej równanie: PV nakładów = PV wpływów:

Jeśli nie dysponujemy kalkulatorem finansowym z funkcją IRR, możemy obliczyć wewnętrzną stopę zwrotu

danego projektu metodą kolejnych przybliżeń (prób i błędów), która polega na podstawianiu kolejnych wartości

stopy dyskontowej i sprawdzaniu poprawności równania. Jednak nawet w przypadku pojedynczego projektu jest

to bardzo czaso- i pracochłonne. Rozsądnym rozwiązaniem jest zatem skorzystanie z arkusza kalkulacyjnego i

taką możliwość daje gotowa funkcja IRR Excela (przykład 11):

Funkcja IRR umożliwia obliczenie wewnętrznej stopy zwrotu regularnego strumienia przepływów pieniężnych.

Wewnętrzna stopa zwrotu oznacza stopę dyskontową, która równoważy poniesione nakłady z przewidywanymi

wpływami.

IRR(wartości;wynik)

gdzie:

wartości – tablica przepływów pieniężnych dla kolejnych równych okresów, składająca się przynajmniej z jednej

wartości dodatniej i jednej ujemnej,

wynik – opcjonalnie przypuszczalna wartość wewnętrznej stopy zwrotu.

Przykład 11

Firma zamierza nabyć nieruchomość z przeznaczeniem na wynajem. Koszt inwestycji równa się 250 tys.

PLN, przy koszcie tego kapitału równym 12% rocznie. Oczekiwane wpływy netto w kolejnych 7 latach

wyniosą odpowiednio: 59 tys., 59 tys., 77 tys., 77 tys., 77 tys., 77 tys., 77 tys. PLN. Jaka jest

wewnętrzna stopa zwrotu z tej inwestycji?

W celu rozwiązania przykładu w komórkę I3 została zadeklarowana funkcja IRR: =IRR(B2:I2), gdzie wartości

podane są w formie zakresu komórek B2:I2. Jest to bardzo wygodny sposób określenia wewnętrznej stopy

zwrotu inwestycji:

Operując funkcją IRR możemy również podać konkretne wartości, wpisując je jako argumenty funkcji

przedzielone średnikami:

=IRR({-250000;59000;59000;77000;77000;77000;77000;77000})

Z obliczeń wynika, że projekt powinien być przyjęty do realizacji, gdyż wewnętrzna stopa zwrotu IRR jest wyższa

INFORLEX.PL Księgowość

http://ksiegowosc3.inforlex.pl/szukaj/wyniki/drukuj,,I25.2005.034.000...

14 of 17

2013-06-12 20:18

od kosztu kapitału.

Funkcja MIRR – zmodyfikowana wewnętrzna stopa zwrotu

MIRR (ang. modified internal rate on return) to stopa dyskontowa, która zrównuje wartość bieżącą ujemnych

przepływów pieniężnych ze zaktualizowaną wartością końcową TV dodatnich przepływów pieniężnych. Wartość

końcowa projektu TV (ang. terminal value), oznaczająca licznik prawej strony przedstawionego poniżej

równania, jest sumą wartości przyszłych dodatnich przepływów pieniężnych, kapitalizowanych według stopy

równej kosztowi kapitału. Lewa strona równania określa natomiast wartość bieżącą PV ujemnych przepływów

pieniężnych, przy stopie dyskontowej r równej kosztowi kapitału. MIRR można obliczyć przy użyciu

następującego równania:

gdzie:

COF

t

– nakłady inwestycyjne w roku t (ujemne przepływy pieniężne),

CIF

t

– wpływy środków pieniężnych w roku t (dodatnie przepływy pieniężne),

r – stopa dyskontowa (koszt kapitału),

n – okres inwestycji,

t – kolejne okresy (najczęściej lata) eksploatacji inwestycji,

MIRR – stopa zwrotu uwzględniająca przewidywaną stopę reinwestycji.

Różnica pomiędzy MIRR i IRR polega na tym, że funkcja MIRR uwzględnia reinwestycję uzyskanych

przychodów z inwestycji. Projekt jest opłacalny, gdy zmodyfikowana wewnętrzna stopa zwrotu

przewyższa koszt kapitału (MIRR > r). W przypadku rozpatrywania opłacalności kilku projektów za

najlepszy uznaje się ten, którego wartość MIRR jest najwyższa (przykład 12).

Funkcja MIRR umożliwia obliczenie zmodyfikowanej wewnętrznej stopy zwrotu regularnego strumienia

przepływów pieniężnych, z uwzględnieniem kosztu inwestycji oraz procentu uzyskanego z ponownej reinwestycji

osiągniętych przychodów.

MIRR(wartości;stopa_finansowa;stopa_reinwestycji)

gdzie:

wartości – tablica kolejnych przepływów pieniężnych w analizowanym okresie, zawierająca przynajmniej jedną

wartość ujemną; wartości ujemne odpowiadają wydatkom, dodatnie – reinwestowanym przychodom,

stopa_finansowa – stopa procentowa, która określa koszt kapitału za jeden okres,

stopa_reinwestycji – stopa refinansowa za jeden okres, po jakiej są dokonywane reinwestycje zysków.

Zademonstrujmy użycie funkcji MIRR w przykładzie 12.

Przykład 12

Firma zaciągnęła kredyt inwestycyjny w wysokości 40 tys. PLN, którego oprocentowanie wynosi 11% w skali

roku. Wykorzystanie kredytu sprawiło, że przyniósł on w ciągu kolejnych pięciu lat zyski w wysokości 11 tys., 14

tys., 12 tys., 9 tys. i 8 tys. PLN, które reinwestowano, uzyskując stopę zwrotu w wysokości 12,5% rocznie.

Należy obliczyć zmodyfikowaną wewnętrzną stopę zwrotu.

Najprostszym rozwiązaniem jest zastosowanie funkcji MIRR, której formuła wygląda następująco:

=MIRR({-40000;11000;14000;12000;9000;8000};11%;12,5%)

Krótszy zapis tej samej formuły jest możliwy, jeżeli w Excelu umieścimy niezbędne dane. Do komórki B2

INFORLEX.PL Księgowość

http://ksiegowosc3.inforlex.pl/szukaj/wyniki/drukuj,,I25.2005.034.000...

15 of 17

2013-06-12 20:18

wpisujemy wartość zaciągniętego kredytu, a do komórek C2:G2 wprowadzamy dochody z kolejnych pięciu lat.

W komórce B3 wpisujemy stopę oprocentowania kredytu inwestycyjnego, a w komórce C3 (powstałej ze

scalenia komórek o zakresie C3:G3) wpisujemy stopę reinwestycji. W komórce G4 deklarujemy zapis funkcji

MIRR, odwołując się do adresów komórek:

=MIRR(B2:G2;B3;C3)

Zmodyfikowana wewnętrzna stopa zwrotu wynosi 12,12%. Uzyskany wynik jest wyższy od kosztu kredytu

(kosztu kapitału), a więc projekt może być zaakceptowany do realizacji.

Innym sposobem obliczenia zmodyfikowanej wewnętrznej stopy zwrotu MIRR przy użyciu arkusza kalkulacyjnego

Excel jest wykorzystanie narzędzia Szukaj wyniku.

Po wpisaniu do Excela wartości i formuł zgodnie z zaprezentowanym przykładem, w wierszu piątym, w

komórkach C5:G5 obliczamy przyszłe wartości przepływów CIF

t

, zgodnie z formułą zapisaną w komórce C5:

=C2*(1+$C$3)ˆ($G1-C1) i przeciągniętą w prawo na komórki D5:G5. W komórce G6 obliczamy sumę:

=SUMA(C5:G5), będącą wartością końcową – TV projektu. W komórce B8 wpisujemy formułę:

=B4+G6/(1+B7)ˆG1, w której komórka B7 jest szukaną stopą dyskontową MIRR spełniającą równanie

PV

wartości końcowej

= PV

kosztów

, czyli PV

TV

– PV

COF

= 0. Po czym wykonujemy następujące czynności:

• będąc w komórce B8 wybieramy na Pasku Menu polecenie Narzędzia, a następnie klikamy polecenie Szukaj

wyniku,

• otwiera się okienko dialogowe polecenia Szukaj wyniku,

• w polu Ustaw komórkę wpisujemy adres komórki, w której chcemy otrzymać wynik (lub klikamy na tę

komórkę na arkuszu B8 – adres zostanie wpisany w pole Ustaw komórkę),

• w polu Wartość wpisujemy 0 (zero),

• w polu Zmieniając komórkę klikamy na komórkę B7.

Naciskamy przycisk OK. Program Excel znajdzie szukaną wartość, równą zmodyfikowanej wewnętrznej stopie

zwrotu MIRR, i wstawi ją do komórki B7:

INFORLEX.PL Księgowość

http://ksiegowosc3.inforlex.pl/szukaj/wyniki/drukuj,,I25.2005.034.000...

16 of 17

2013-06-12 20:18

Źródło: INFORLEX.PL Księgowość publikacja z serii INFORLEX

Copyright © 2013 INFOR PL S.A. Wszelkie prawa zastrzeżone.

Niniejszy artykuł przedstawia tylko część z bardzo szerokiego zakresu funkcji programu Excel. Niemniej jednak

przedstawiony opis umożliwi uproszczenie złożonych obliczeń i przyczyni się do efektywnego wykorzystania tego

narzędzia finansowo-księgowego.

Jan Podobiński

odpowiadamy na pytania: www.infor.pl/pytaniasfk

INFORLEX.PL Księgowość

http://ksiegowosc3.inforlex.pl/szukaj/wyniki/drukuj,,I25.2005.034.000...

17 of 17

2013-06-12 20:18