Arkusz

• Microsoft Excel udostępnia przeszło pięćdziesiąt funkcji finansowych. Mogą one być stosowane

indywidualnie lub w kombinacjach z innymi funkcjami.

• Funkcje te, tak jak pozostałe funkcje opisane są przez odpowiednią dla każdej z nich składnię. Należy

zwrócić uwagę na wyjątkowe znaczenie formatu daty.

! Istnieje poręczny skrót klawiszowy pozwalający wyświetlić składnię funkcji. Po wpisaniu nazwy funkcji po
znaku równości wystarczy wybrać [Ctrl ] + [Shift] + [Alt]. Możesz następnie potraktować podpowiedź
Excela jako szablon funkcji.

• Podając wartości argumentów, musisz pamiętać, aby zawsze wyrażone były w tych samych jednostkach.

• Kwoty uzyskiwane ( przychody ) należy podawać jako wartości dodatnie, natomiast kwoty wpłacane

( rozchody ) jako ujemne. Funkcje finansowe automatycznie formatują komórki, w wyniku czego wartości
ujemne wyświetlane są w czerwonym kolorze.

• Należy również wiedzieć, że wiele funkcji należących do tej kategorii wymaga uaktywnienia dodatku

Analysis ToolPak. W tym celu należy zaznaczyć pole wyboru Analysis ToolPak i kliknąć przycisk [OK].

Funkcje finansowe

Ćwiczenie:

Przyjmijmy, że otrzymujemy kredyt 24 000 PLN, który oprocentowany jest według stopy 12 % rocznie, a
przewidywany okres spłaty wynosi 3 lata ( tj. 36 miesięcy ). Naszym zadaniem jest obliczyć wielkość raty,
a następnie opracować harmonogram spłaty kredytu.

Zadanie rozwiążemy w dwóch etapach.

Etap I – wyznaczenie wielkości raty kapitałowej i odsetek (łącznie)

Rozwiązując ten problem możemy skorzystać z funkcji PMT – obliczającej ratę spłaty pożyczki opartej na
stałych ratach (czyli z miesięczna kapitalizacją odsetek ) i stałym oprocentowaniu. Ogólna postać tej
funkcji jest następująca:

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

Gdzie:

• stopa – stopa procentowa dla okresu

• liczba_rat – całkowita liczba okresów płatności w okresie spłaty

• wa – (ang. Pv) kapitał, czyli całkowita wartość bieżącej serii przyszłych płatności

• wp – ( ang. FV) przyszła wartość, czyli poziom finansowy, do którego zmierza się po dokonaniu ostatniej

płatności; jeżeli argument ten zostanie pominięty, to jako przyszłą wartość przyjmuje się 0

• typ – liczba wskazująca kiedy płatność ma miejsce ( 0 lub gdy jest pominięty - koniec okresu, 1- początek

okresu )

Spłata kredytu w stałych ratach 1/4

Ćwiczenie cd.:

! Należy zwrócić uwagę, że obliczana wartość nie obejmuje podatków ani opłat związanych z pożyczką.

• Dla naszych potrzeb wykorzystamy komórki arkusza, jak poniżej

Formuła wyliczająca wartość raty miesięcznej ma ostatecznie postać =PMT(B4/12;B5;-B3). Zapytania
może budzić fakt podzielenia zawartości komórki B4 przez 12 oraz znak - dopisany do komórki B3.

Podana w B4 stopa procentowa to stopa roczna, a raty płacone są w cyklu miesięcznym – stąd konieczne
podzielenie jej przez 12

Znak – powoduje, że wartość wyświetlona w komórce ma wartość dodatnią.

Spłata kredytu w stałych ratach 2/4

Ćwiczenie cd.:

Etap II – opracowanie harmonogramu spłaty kredytu

Tworzymy arkusz w następującej postaci

Bazujemy na następujących
zależnościach

saldo początkowe kapitału =
kredyt do spłacenia w danym miesiącu.

spłata odsetek = saldo p.k * roczna stopa procentowa / 12

spłata kapitału = rata kredytu – spłata odsetek

pozostało do spłaty = saldo początkowe kapitału - spłata kapitału

Spłata kredytu w stałych ratach 3/4

Ćwiczenie cd.:

Formuły jakie należy
wpisać odnajdziesz
na tym obrazku

• Dodaj podsumowania zapłaconych odsetek dla 36 rat - niech wynik skłoni Ciebie do myślenia, zanim
zaciągniesz kredyt .

Spłata kredytu w stałych ratach 4/4

Ćwiczenie.:

Pod koniec każdego miesiąca następuje wpłata 100 zł na rachunek oszczędnościowy w banku z rocznym
oprocentowaniem 8%. Jaka będzie wysokość wkładu po pięciu latach, jeśli odsetki kapitalizowane są co
miesiąc ?

Oczywiście jest to pytanie o przyszłą wartość – korzystamy więc z funkcji Przyszła wartość FV. Jej składnia
ma postać

Odpowiedź: 7347,69 zł

Obliczanie wartości wkładu na rachunku

oszczędnościowym ze stałym oprocentowaniem

Obliczanie wartości wkładu na rachunku

oszczędnościowym ze stałym oprocentowaniem

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

Argument

Znaczenie

W naszym przypadku

Stopa

Oprocentowanie miesięczne

8% / 12

liczba_rat

Liczba miesięcznych wpłat

5*12

Rata

Miesięczna wpłata; liczba ujemna,

gdyż jest to wpłata gotówki

- 100

Bieżąca wartość

0 ( na rachunku nie było

wcześniej pieniędzy)

Typ

Określa, czy płatność ma miejsce na początku,

czy na końcu miesiąca.

0 – wpłaty są dokonywane

pod koniec miesiąca

Ćwiczenie.:

Jaka będzie wysokość wkładu w wysokości 1000 zł po pięciu latach, oprocentowanego w skali 6 % rocznie ?

Jest to pytanie o przyszłą wartość – korzystamy więc ponownie z funkcji Przyszła wartość FV.

Odpowiedź: 1348,85 zł

! Mogliśmy opuścić zerową wartość parametru rata i użyć formuły = FV(6%/12;5*12; ;-1000), gdyż
opuszczone argumenty otrzymują wartość zero.

Obliczanie wartości lokaty ze stałym

oprocentowaniem

Obliczanie wartości lokaty ze stałym

oprocentowaniem

Argument

Znaczenie

W naszym przypadku

Stopa

Oprocentowanie miesięczne

6% / 12

liczba_rat

Liczba miesięcznych wpłat

5*12

Rata

Miesięczna wpłata; liczba ujemna,

gdyż jest to wpłata gotówki

0 ( nie wpłacamy przecież

rat )

Bieżąca wartość

-1000 ( kwota wpłacona na

rachunek )

Typ

Określa, czy płatność ma miejsce na początku,

czy na końcu miesiąca.

Ćwiczenie.:

Muszę pożyczyć 4000 zł. Roczne oprocentowanie pożyczki wynosi 9% przy miesięcznej kapitalizacji. Mogę
sobie pozwolić na miesięczne spłaty w wysokości 300 zł. Jak długo będzie trwała spłata pożyczki ?

Jest to pytanie o przyszłą wartość – korzystamy więc z funkcji Liczba rat NPER o składni

Odpowiedź: 14,1 raty – pożyczka powinna być spłacana w czasie trochę dłuższym od 14 miesięcy. Użyj
teraz funkcji PMT, aby obliczyć wysokość miesięczne raty, która pozwoli spłacić pożyczkę w 15 miesięcy.

Obliczanie liczby rat pożyczki

Argument

Znaczenie

W naszym przypadku

Stopa

Oprocentowanie miesięczne

9% / 12

Rata

Miesięczna wpłata; liczba ujemna,

gdyż jest to wpłata gotówki

-300

Bieżąca wartość

4000

Przyszła wartość

0 ( zamierzamy spłacić całą

pożyczkę)

Typ

Określa, czy płatność ma miejsce na początku,

czy na końcu miesiąca.

0 ( wpłaty z końcem

miesiąca )

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

Wartość bieżąca jest całkowitą sumą bieżącej wartości szeregu przyszłych płatności. Na przykład, kiedy
pożyczasz pieniądze, kwota pożyczki jest wartością bieżącą dla pożyczkobiorcy
Ćwiczenie:

Masz na sprzedaż jakąś posiadłość. Kupiec A oferuje 100 000 zł, natomiast kupiec B proponuje pięć rocznych
wpłat w wysokości 30 000 zł. Która oferta jest lepsza, jeśli roczna stopa dyskontowa wynosi 8 % ?

Bieżąca wartość oferty A to dokładnie 100 000 zł. Wartość bieżącą oferty B można znaleźć korzystając z
funkcji Bieżąca Wartość PV o składni

Odpowiedź: Funkcja zwraca wartość 129 364 zł, więc oferta B jest lepsza niż A. Czasami mówi się, że kwota
129 364 zł to zdyskontowana wartość czterech przyszłych płatności po 30 000 zł.

Obliczanie bieżącej wartości

–

sprzedaży ratalna

Obliczanie bieżącej wartości

–

sprzedaży ratalna

Argument

Znaczenie

W naszym przypadku

Stopa

Oprocentowanie miesięczne

Liczba rat

Liczba rocznych wpłat

Rata

Roczna wpłata; liczba ujemna,

gdyż jest to wpłata gotówki

-30000

Przyszła wartość

Typ

Określa, czy płatność ma miejsce na początku,

czy na końcu okresu rozliczeniowego.

1 ( porównujemy płatność

w danym momencie )

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

Ćwiczenie:

Planujesz inwestycję emerytalną. Aktualnie masz 25 000 zł i możesz pozwolić sobie na zainwestowanie 5000
zł rocznie. Za 25 lat chcesz przejść na emeryturę jako milioner. Jakie musiałbyś uzyskać oprocentowanie ?

Należy skorzystać z funkcji Stopa RATE o składni

Odpowiedź: Korzystając z funkcji =RATE(25;-5000;-25000;1000000;1) obliczysz, że roczne oprocentowanie
musiałoby wynieść 11 %

Obliczanie korzystnej stopy procentowej

–

inwestycja emerytalna 1/2

Obliczanie korzystnej stopy procentowej

–

inwestycja emerytalna 1/2

Argument

Znaczenie

W naszym przypadku

Liczba rat

Liczba rocznych wpłat

Rata

Roczna wpłata; liczba ujemna,

gdyż jest to wpłata gotówki

-5000

Bieżąca wartość

-25000

Przyszła wartość

1000000

Typ

Określa, czy płatność ma miejsce na początku,

czy na końcu okresu rozliczeniowego.

1( każda wpłata będzie mieć

miejsce na początku roku)

Wynik

Przypuszczalny wynik

Jeśli pominie się ten
argument, to za jego
wartość przyjmuje się 10%

=RATE(liczba_rat;rata;wa; wp;typ;wynik)

• Objaśnienia wymaga argument wynik ( w Excelu 2002 nosi on nazwę przypuszczenia ). Podczas gdy inne

funkcje finansowe używają prostych podstawień w czasie obliczania wartości, funkcja RATE stosuje
metodę iteracyjną.

• Jeśli pominiesz argument wynik , wówczas otrzymuje on wartość 10 %. Jeżeli po 20 iteracjach wyniki

kolejnych iteracji różnią się o więcej niż 0,0000001, funkcja RATE generuje wartość błędu #LICZBA!.
Podając wartość startową (argument wynik ), możemy wspomóc proces obliczeń, ale w tym przypadku nie
było to konieczne.

Obliczanie korzystnej stopy procentowej

–

inwestycja emerytalna 2/2

Obliczanie korzystnej stopy procentowej

–

inwestycja emerytalna 2/2

iteracja

(wielokrotne)

powtarzanie

(zwł.

każdorazowym

przekształceniem w analogiczny sposób); mat. kolejne powtórzenie
danej operacji odniesione do rezultatu poprzedniego jej wypełnienia.

iteracja

(wielokrotne)

powtarzanie

(zwł.

każdorazowym

przekształceniem w analogiczny sposób); mat. kolejne powtórzenie
danej operacji odniesione do rezultatu poprzedniego jej wypełnienia.

• Często w życiu znajdujemy się w sytuacji, kiedy wiemy, co chcemy osiągnąć, ale nie wiemy jak tego

dokonać. Z analogiczną sytuacją możemy spotkać się w trakcie rozwiązywania różnych problemów za
pomocą Excela.

• Często istnieje jakaś formuła, jeden lub kilka zbiorów wejściowych i oczekiwany wynik obliczeń. Zadaniem

jest określenie, która wartość wejściowa da pożądany wynik.

• W celu ułatwienia rozwiązywania tego typu problemów, Excel umożliwia szukanie wyniku metodą, która

zwalnia nas z konieczności rozwiązywania ( często) złożonych zadań optymalizacyjnych, sprowadzając
nasze działania do szukania odpowiedzi na pytania, co chcemy uzyskać, a nie - jak to zrobić.

• Rozwiązywanie tego typu zadań umożliwia polecenie Narzędzia / Szukaj wyniku.

• Może zdarzyć się, że mimo zastosowania powyższej metody nie będziemy mogli osiągnąć założonego

wyniku. W tej sytuacji może pomóc zmiana domyślnych ustawień związanych z przeliczaniem. Aby
dokonać zmian, należy wybrać Narzędzia / Opcje i następnie uaktywnić kartę Przeliczanie.

Szukanie wyniku

Ćwiczenie:

Mamy przez 10 lat uzbierać na jakiś cel 20 000 zł. Pieniądze chcemy wpłacać do banku w 10 rocznych
jednakowych ratach. Roczna stopa procentowa wynosi 5 %.
Jaka będzie wysokość jednej raty. ?

• Przygotuj arkusz, wpisując funkcję FV

do komórki B5

• Przejdź do komórki B5 i wydaj polecenie

Narzędzie / Szukaj wyniku . Wypełnij
pola jak na obrazie.

• W komórce B3 wyliczona jest liczba ujemna.

Jest to związane ze standardem funkcji
finansowych, wg którego wpłaty i wynik
( kapitał) muszą być liczbami odmiennego
znaku. Możesz zapamiętać, że
Znak minus bo wydajemy
Znak plus, bo nam przybywa

Wyznaczanie wysokości raty

z wykorzystaniem funkcji Szukaj

Wyznaczanie wysokości raty

z wykorzystaniem funkcji Szukaj

Ćwiczenie:

Firma rozpoczyna produkcję nowego towaru. Założenia są następujące:

•W ciągu roku zostanie sprzedane 10 000 sztuk

•Koszt produkcji jednej sztuki: 7,50 zł

•Koszty stałe tej produkcji: 45 000 zł

Jaka powinna być jednostkowa cena detaliczna, aby marża wynosiła 20 % ? Przy czym marże

definiujemy jako stosunek przychodu do dochodu i wyrażamy w procentach.

Przygotuj następujący
arkusz ( na lewym obrazie
widać formuły). Do komórki B1
wpisana jest prowizoryczna
wartość, aby uniknąć zerowego
przychodu w B3i błędu dzielenia
przez zero w komórce B10.

Wyznaczanie wysokości marży

z wykorzystaniem funkcji Szukaj

1/2

Wyznaczanie wysokości marży

z wykorzystaniem funkcji Szukaj

1/2

Ćwiczenie cd. :

• Przejdź do komórki B10 i wydaj polecenie Narzędzie / Szukaj Wyniku.
Wypełnij okienko dialogowe jak na rysunku. Naciśnij OK.

• Na podstawie wielkości całkowitego
przychodu w komórce B3 wnioskujemy,
że wyliczona cena jednostkowa jest
niższa od 15 zł pokazanych w
komórce B1. Jest to wynik
narzuconego przez formatowanie
zaokrąglenia.

• Jak widać, sprzedaż po cenie dwukrotnie
wyższej od kosztów produkcji zapewnia
marżę zaledwie 20-procentową.
W rzeczywistości bywa gorzej 8-(

Wyznaczanie wysokości marży

z wykorzystaniem funkcji Szukaj

2/2

Wyznaczanie wysokości marży

z wykorzystaniem funkcji Szukaj

2/2

• Mimo wielu zalet narzędzie ( metoda ) Szukaj wyniku obarczona jest istotnym ograniczeniem – przy jego

wykorzystaniu można obliczać tylko zadania o jednej zmiennej. A w praktyce zmuszeni jesteśmy
poszukiwać rozwiązań problemów bardziej złożonych. Należą do tej grupy zarówno procesy biznesowe
( np.. Jak zarządzać zasobami przypisanymi do harmonogramu ), zadania optymalizacyjne ( np.. Jak dobrać
wielkość produkcji, aby przy ustalonych ograniczeniach osiągnąć maksymalny zysk ).

• Narzędziem, które umożliwią rozwiązywanie wyżej wymienionych zadań jest Solver. Należy pamiętać, że

nie jest on jak szereg innych narzędzi domyślnie zainstalowany i aby móc go zastosować, należy
załadować go, korzystając z Narzędzia / Dodatki.

• Uogólniając można stwierdzić, iż Solver umożliwia znalezienie optymalnego rozwiązania danej formuły

przez zmianę wielu wartości wejściowych i określenie warunków ograniczających.

SOLVER

Ćwiczenie:

Załóżmy, że przez 10 kolejnych lat planujemy co roku wpłacać raty. Odsetki będą doliczane z dołu po
upływie każdego roku. Co należy zrobić aby uzbierać 14000 zł ? Dopuszczamy zmiany oprocentowania i
wysokości wpłacanej raty.

• Do komórki B2 wpisz formułę =FV($C$2;A2;$D$2)
Zwróć uwagę na odpowiednie użycie adresów względnych
i bezwzględnych.

• Skopiuj formułę w dół kolumny B.

• Przejdź do komórki B11 i wydaj polecenie
Narzędzia / Solver.

• W okienku Solver - Parametry wypełnij
pola zgodnie z rysunkiem i wybierz Rozwiąż.

• Po obejrzeniu wyników wybierz Anuluj, aby
ułatwić wykonanie następnego ćwiczenia.

! Jak widać została zmieniona jedynie wartość
oprocentowania, lecz nie musiało tak być. Po
prostu to rozwiązanie zostało znalezione
najszybciej.

Planowanie lokaty z użyciem Solvera

Ćwiczenie:

Załóżmy, że przez 10 kolejnych lat planujemy co roku wpłacać raty. Odsetki będą doliczane z dołu po

upływie każdego roku. Co należy zrobić aby uzbierać 14000 zł ? Dopuszczamy zmiany wysokości wpłacanej
raty, ale nieuprzejmy bank nie chce się zgodzić na oprocentowanie wyższe niż 6,2 % . Sprawdź, o ile w
takiej sytuacji trzeba zwiększyć roczną ratę.

•Przejdź do ponownie komórki B11 i wydaj

polecenie Narzędzia / Solver.

• Pola w oknie dialogowym Dodaj warunek ograniczający

wypełnij jak na rys. i zatwierdź.

• Nastąpi powrót do okna Solver – Parametry, które

umożliwia dodanie kolejnych ograniczeń. Ponieważ w tym ćwiczeniu następne ograniczenia są już
niepotrzebne, wybierz Rozwiąż.

! W komórce C2 zobaczysz pewnie wartość 6 %, zamiast założonego 6,2 %. Jest to wynik formatowania -

komórce został narzucony format procentowy bez wyświetlania części ułamkowej ( jest oczywiście możliwa
zmiana )

Można również skorzystać z raportów generowanych przez narzędzie Solver. Przeglądając Raport wyników

zobaczymy informacje służące do bezpośrednich porównań oraz umożliwiające stwierdzenie, które
ograniczenia odegrały role, a które nie. Ograniczenia Niewiążące mają po prostu niezerową wartość Luzu.
Pod pojęciem luzu rozumiemy przedział dopuszczalnych zmian ograniczenia, który nie będzie wpływał na
ostateczną wartość funkcji.

Planowanie lokaty z użyciem Solvera

przy nałożonym ograniczeniu

Planowanie lokaty z użyciem Solvera

przy nałożonym ograniczeniu

• Załóżmy, że mamy arkusz wykonujący dość skomplikowane obliczenia: formuły wpisane w różnych

miejscach wyliczają różne wartości na podstawie wielu danych wejściowych. Wpisywanie co rusz innych
danych to niszczenie potrzebnego arkusza, wpisywanie innych danych do kopii arkusza to zbędne
mnożenie plików. Rozwiązaniem, które warto w takich przypadkach stosować, są scenariusze.

• Scenariuszem nazywamy pojedynczy zestaw danych, zapamiętany pod dowolną nazwą.

• Narzędziem umożliwiającym zestawianie i porównywanie scenariuszy jest Menedżer Scenariuszy.

Scenariusz

Ćwiczenie:

Utwórz przykładowy ( mocno uproszczony ) arkusz bilansowy
pewnej firmy. Wg powyższego wzoru.

• Zakresowi A3:B4 nadaj nazwę Obroty, a zakresowi A7:B11 Koszty.
Służy do tego polecenie Wstaw / Nazwa / Definiuj. Komórki
nie zawierające funkcji wypełnij wartościami liczbowymi.

• Możemy już przejść do tworzenia scenariuszy. W tym celu wybierz
Narzędzia / Scenariusze.

Arkusz bilansowy z wykorzystaniem scenariuszy

1/3

Arkusz bilansowy z wykorzystaniem scenariuszy

1/3

Ćwiczenie c.d.:

• W pierwszym oknie Menedżera scenariuszy

kliknij przycisk Dodaj.

• W otwartym oknie Dodaj scenariusz w polu

Nazwa scenariusza wpisz Dochód – propozycje.

• W polu Komórki zmieniane wprowadź zakresy komórek,

które mogą być zmieniane podczas opracowywania kolejnych
wariantów scenariusza. Zatwierdź wpisy.

•Aby dodać nowy scenariusz, wystarczy kliknąć Dodaj.

•W oknie Dodaj scenariusz wpisz nazwę nowego scenariusz

Prognoza zmian sprzedaży. Klikając OK. spowodujesz
wyświetlenie okna Wartości scenariusza

•Przyjmujemy, że wartość sprzedanego towaru wzrośnie o 10%.

Aby wprowadzić zmianę, należy w polu odpowiadającym
komórce B4 wpisać odpowiednią formułę. Otrzymamy komunikat
o zamianie formuł na odpowiadające im wartości.

Arkusz bilansowy z wykorzystaniem scenariuszy

2/3

Arkusz bilansowy z wykorzystaniem scenariuszy

2/3

Ćwiczenie c.d.:

• Dodajmy jeszcze scenariusz przewidujący

wzrost czynszów o 40%, a podatków i ZUS
o 20%.

•Kliknięcie przycisku OK. otwiera ponownie

okno Menedżera Scenariuszy. Możemy dodawać
kolejne, edytować lub usuwać. Należy przy tym pamiętać,
że program nie żąda potwierdzenia decyzji usunięcia
konkretnego scenariusza.

•Po wskazaniu konkretnego scenariusza Excel wyświetla żądane

zestawy danych ( zmiany następują w komórkach zawierających
zmienne modyfikowane w poszczególnych scenariuszach).

Arkusz bilansowy z wykorzystaniem scenariuszy

3/3

Arkusz bilansowy z wykorzystaniem scenariuszy

3/3

Ćwiczenie:

Tworzymy raport dotyczący scenariuszy.

• Skorzystaj z arkusz z niedawno utworzonym bilansem firmy.
Wybierz polecenie Narzędzia / Scenariusze

• W oknie Menedżer scenariuszy wybierz przycisk Podsumowanie.
Otwarte zostanie okno dialogowe Podsumowanie scenariuszy.

• Domyślnie wybrana jest opcja Podsumowanie scenariuszy
( nie należy jej zmieniać ) . Kliknięcie
OK. powoduje wyświetlenie okna
Podsumowanie scenariuszy.

Tworzenie raportu ze scenariuszy.

Bibliografia

• [1] „Excel w praktyce” - B.Zieliński Wyd. Translator s.c.
• [2] „Excel 2002/XP PL Ćwiczenia zaawansowane” K.Masłowski Wyd. Helion
2003

• [3] „ Microsoft Excel w biznesie i zarządzaniu” - B.V.Liengme Wyd. RM 2002
• [4] „Excel w biurze i nie tylko” S.Flanczewski - Wyd.Helion 2003
• [5] „Excel 2000 PL” G.Kowalczyk - Wyd.Helion 2003

Document Outline