rozwiń filtry »

Wyszukiwanie publikacji

Porady

maj
28

Praca z Excelem - 100 porad i tricków (4/5)

28.05.2015 16:05, dodał: Krzysztof Cyło
Ocena:

FORMUŁY FINANSOWE

#66 Szybkie porównywanie lokat - czyli efektywna stopa procentowa

W środowisku formuł finansowych prawdopodobnie najbardziej elementarnymi, a zarazem często wykorzystywanymi przez użytkowników, są formuły poświęcone prostemu obliczeniu efektywnej stopy procentowej. Efektywna stopa mówi nam o tym, jaką faktycznie stopę zwrotu uzyskujemy z danej inwestycji - lokaty, zważywszy na kapitalizację. Weźmy sobie za przykład właśnie lokatę.

Nasze oszczędności wynoszą 10 tys. zł. Pierwszym krokiem na drodze skutecznego ich ulokowania, będzie wybranie banku oferującego najlepsze oprocentowanie. Mamy w naszym przykładzie trzy warianty, z tym samym oprocentowaniem, ale różnymi okresami kapitalizacji.

Kapitalizacja to inaczej naliczanie odsetek. Jej częstotliwość oznacza więc, jak często do naszego rachunku dopisywane są odsetki. Gdy mamy do czynienia z kapitalizacją miesięczną, wówczas co miesiąc nasz kapitał powiększa się o dodatkowe środki. W kolejnym miesiącu oprocentowanie jest liczone od naszego kapitału powiększonego o dotychczasowe, narastające odsetki. Teoretycznie więc, najwyższe efektywne oprocentowanie, będzie tam, gdzie najczęstsze są kapitalizacje (w przypadku jednakowego oprocentowania). Aby się o tym przekonać, wystarczy zastosować formułę =EFEKTYWNA, dla której argumentami są: oprocentowanie oraz okresy kapitalizacji.

Wyniki są następujące:

#67 Zysk z lokaty

Wydawać by się mogło, że obliczenie zysku z lokaty to sprawa intuicyjna, nie przysparzająca żadnych kłopotów. Czy na pewno?

Do obliczenia realnych zysków z lokaty wykorzystamy jedną z podstawowych formuł finansowych - Future Value, tj. FV. W tym przykładzie założymy, że kapitalizacja jest miesięczna, a oprocentowanie jest skali roku. Później ten przykład będziemy nieco modyfikować.

Zacznijmy od prostego kalkulatora, w którym będziemy mogli operować dwiema zmiennymi: oprocentowaniem i okresem przechowywania środków.

Mając wypisane dane jak powyżej, zastosujmy formułę FV, której składnikami są:

  • Stopa - stopa procentowa dla okresu. U nas 3%, podzielone przez liczbę okresów kapitalizacji (czyli 12).

  • Liczba_okresów - całkowita liczba okresów płatności w okresie spłaty. U nas są to trzy miesiące. Wiemy, że kapitalizacja następuje co miesiąc, a nasza lokata trwa trzy miesiące.

  • Rata - płatność dokonywana w każdym okresie; nie może się zmienić w czasie trwania kredytu. U nas wyniesie ona 0, gdyż niczego nie dopłacamy do lokaty.

  • Wb - wartość bieżąca lub skumulowana wartość przyszłego strumienia płatności według wyceny na dzień obecny. Jeśli argument „wb” zostanie pominięty, przyjmuje się, że ma wartość 0 (zero) i należy określić argument „rata”. Kapitał należy w formule wpisać ze znakiem "-".

  • Typ - Argument opcjonalny. Liczba 0 albo 1. Określa, kiedy płatność jest należna. Jeśli zostanie pominięty, przyjmowana jest wartość 0.

Ostatecznie uzyskujemy taki wynik:

#68 Tworzymy uniwersalny kalkulator lokat

Powyższa wskazówka jest o tyle przydatna, że z pewnością jest najbardziej uniwersalnym rozwiązaniem służącym wyliczaniu odsetek od lokat. Co jednak, gdy mamy do czynienia z inną kapitalizacją lub gdy chcemy porównać różne opcje?

Pozwolę sobie zaznaczyć, że ta wskazówka należy do nieco bardziej zaawansowanych. Ostatecznie musimy uzyskać widok jak poniżej. Na niebieskim tle są pozycje, które będziemy każdorazowo definiować określając parametry lokaty. Okres to liczba miesięcy trwania lokaty. Następnie mamy oprocentowanie oraz kapitalizację. Kapitalizacja stanowi listę rozwijaną w komórce C7 (jak zrobić taką listę opisywałem w pierwszym artykule z cyklu). Jej źródłem są dane w kolumnie F. Zakładamy, że kapitalizacje mogą być tygodniowe, miesięczne, kwartalne i roczne. Z rozwijanej listy będziemy definiować tę wartość określając warunki lokaty.

Teraz przechodzimy już do pisania formuły FV. To będzie nasza podstawowa formuła, dlatego rozbiję ją na części składowe dla lepszej czytelności:

- stopa - jest to stopa procentowa, podzielona przez liczbę okresów kapitalizacji. W przypadku miesięcznej kapitalizacji jest to 12 okresów (3%/12), w przypadku kwartalnej - 4 (3%/4). Korzystając z funkcji WYSZUKAJ.PIONOWO, wyszukujemy ilość okresów, odpowiadających częstotliwości kapitalizacji, zdefiniowanych w tabeli obok. 3% będzie więc dzielone przez 12, gdy kapitalizacja jest miesięczna, przez 4, gdy będzie kwartalna, itd.

- liczba_rat - teraz musimy określić, ile razy w ciągu roku następnie kapitalizacja (w ciągu roku, bo zakładamy stopę oprocentowania wyrażoną w skali roku). Dla miesięcznej kapitalizacji, przykładowe 6 miesięcy będzie 6 razy kapitalizowane. Ale gdy kapitalizacja będzie kwartalna, wówczas będą tylko dwie kapitalizacje. Szóstkę będziemy zatem musimy przemnożyć przez wartość 0,333 (1/3). Te informacje zawarliśmy w kolumnie H na zrzucie ekranu. Excel wyszukuje je w punkcie liczba_rat korzystając z funkcji WYSZUKAJ.PIONOWO. W zależności od rodzaju kapitalizacji, ilość miesięcy jest mnożona przez odpowiedni współczynnik.

- parametr wa/wb (w zależności od wersji Office'a) - to po prostu wskazanie komórki z kapitałem, tyle że z minusem. Minus możemy też postawić przed całą formułą.

W ten sposób uzyskujemy uniwersalny kalkulator, w którym możemy zmieniać okresy kapitalizacji, oprocentowanie, wielkość kapitału. Pozwala to na łatwe i szybkie obliczanie odsetek od lokaty i porównywanie różnych ofert.

#69 Co miesiąc odkładam określoną kwotę. Ile uzbieram w okresie X, na oprocentowanym rachunku?

Korzystacie z pakietów systematycznego oszczędzenia? Lub - być może - sami opracowaliście system stałych przelewów na konto oszczędnościowe? Jeżeli tak, to być może interesuje Was, ile uzbieracie po określonej liczbie miesięcy lub lat. Procent składany jest wówczas waszym sprzymierzeńcem.

Ja w takich sytuacjach preferuję korzystanie z prostej tabeli. Zakładając, że wpłaty mają miejsce na początku miesiąca (z góry), co miesiąc dopłacamy po 200 zł, a oprocentowanie wynosi 3% w skali roku, możemy łatwo skonstruować przebieg kształtowania się kapitału. Zaczynamy od kapitału 200 zł. Następnie liczymy od niego odsetki, mnożąc przez 3%/12, a następnie odejmując podatek (odsetki*0,81). Odsetki + kapitał z początku miesiąca, daje kapitał na koniec miesiąca i początek następnego, itd.

Zastosowanie tabeli jest o tyle dobrym pomysłem, że w razie zmiany oprocentowania, można łatwo je zastąpić w tabeli - aplikując nową wartość do późniejszych wpłat. Jest ona również korzystna przy zmianie wysokości miesięcznych wpłat.

#70 Podwojenie kapitału - jak szybko je policzyć?

Ten ekspresowy life-hack nie jest szczególnie związany z Excelem, ale już na pewno z omawianą w tej części artykułu tematyką. Aby szybko policzyć, po ilu latach podwoi się nasz kapitał, przy danym oprocentowaniu, wystarczy zastosować Regułę 72. 72 dzielimy przez oprocentowaniu i wychodzi nam wynik w latach, przy założeniu oprocentowania składanego. Tak więc przy 8%, kapitał podwoi się po 9 latach. Sprawdźcie sami ;-)

#71 Chcę uzbierać kwotę X, przy oprocentowaniu Y, w ciągu Z lat. Ile muszę odkładać co miesiąc? Jaki wpływ ma inflacja?

Jest to dosyć powszechny dylemat w finansach osobistych. Mając w pamięci jakiś projekt, zadanie, które musimy sfinansować w przyszłości (np. zakup samochodu albo zorganizowanie wesela), zastanawiamy się, ile pieniędzy powinniśmy oszczędzać, chcąc regularnie odkładać, by dojść do danej kwoty, wykorzystując przy tym również narastające odsetki. Zakładamy więc, że jest to stosunkowo odległe zadanie (np. mieszkanie dla dziecka, które obecnie ma 2 latka).

Załóżmy, że współcześnie takie mieszkania kosztują ok. 350 tys. zł. Mając w perspektywie długoterminowe oszczędzanie (ok. 20 lat), należy wziąć pod uwagę ew. wzrost cen wynikający z inflacji. Ceny mieszkań nie zachowują się co prawda idealnie na wzór trendu inflacji, jednakże - dla uproszczenia - jakąś stopę przyrostu cen wypadałoby do obliczeń przyjąć. Załóżmy więc, że ceny średniorocznie będą rosły o 1%, a zatem musimy naszą wyjściową kwotę uaktualnić do tej wartości.

Takiej operacji dokonamy stosując wzór na procent składany. Kapitał bieżący należy przemnożyć razy 1,01 podniesione do potęgi odpowiadającej ilości lat (u nas 20).

Wiemy już, że powinniśmy odłożyć ponad 427 tys. zł. Ile w takim razie powinniśmy odkładać co miesiąc, by uzyskać tę kwotę? Nie wystarczy podzielić kapitału przez ilość miesięcy. Należy w końcu mieć na uwadze, że środki będą na siebie pracowały, osiągając określoną stopę zwrotu. Załóżmy, że środki będą inwestowane i stopa zwrotu przewyższy oprocentowanie lokat i wyniesie 7% rocznie.

Formuła służąca do takich wyliczeń będzie - powiedzmy - umiarkowanie skomplikowana, wynikająca z wprost z matematyki finansowej. Kwotę kapitału sprowadzonego do wartości przyszłej (nasze 427 tys.) dzielimy przez oprocentowanie w skali miesiące podniesione do potęgi odpowiadającej ilości miesięcy, uwzględniając przy tym opodatkowanie.

O wiele szybciej i prościej można by to policzyć stosując formułę PMT, jednakże ona nie uwzględnia opodatkowania.

#72 Ile kosztuje kredyt?

Excel znajduje również zastosowanie na etapie poszukiwania odpowiedniego kredytu. Co jednak ważne, daje on realny pogląd na to, ile kredyt w rzeczywistości kosztuje - jaka część spłacanej raty to odsetki, a jaka to kapitał.

W przypadku kredytów nie będzie interesowała nas przyszła wartość pieniądza, a właśnie rata kredytu. Użyjemy więc innej formuły - PMT. Przedstawmy sobie jej komponenty:

PMT (stopa;liczba_rat;wartość_bieżąca; wartość_przyszła; typ )

STOPA - stopa procentowa dla okresu. Na przykład w przypadku pożyczki na samochód oprocentowanej na 10 procent rocznie ze spłatami miesięcznymi miesięczna stopa procentowa to 10 procent podzielone przez 12, czyli 0,83 procent. Dlatego jako argument stopa należy wprowadzić w formule wartość 10%/12 albo 0,83% lub 0,0083.

LICZBA_RAT - całkowita liczba okresów płatności w okresie spłaty. Na przykład osoba otrzymująca czteroletnią pożyczkę na samochód, spłacająca tę pożyczkę w miesięcznych ratach, będzie ją spłacać przez 4*12 (czyli 48) okresów. Dlatego jako argument liczba_rat należy wprowadzić w formule liczbę 48.

WARTOŚĆ_BIEŻĄCA, czyli aktualna łączna wartość serii przyszłych płatności (nazywana także kapitałem). U nas będzie to kwota zaciągniętego kredytu.

WARTOŚĆ_PRZYSZŁA - wartość przyszła lub saldo gotówkowe, do którego zmierza się po dokonaniu ostatniej płatności. Z tego argumentu korzystamy zamiast powyższego, chcąc obliczyć miesięczną kwotę oszczędności.

TYP - mówi nam o tym, kiedy płatność jest należna (1 - koniec miesiąca, 0 - początek).

Zakładając więc, że - jak na zdjęciu poniżej - chcemy zaciągnąć kredyt na 25 tys. zł, przy oprocentowaniu wynoszącym 6% w skali roku, na okres 3 lat, możemy łatwo policzyć naszą ratę kredytu:

Nasza rata wynosi 1108,02 zł. Wiedząc to, możemy łatwo sprawdzić, czy oprocentowanie proponowane przez pożyczkodawcę jest faktycznie takie, jak twierdzi, czy też może w umowę zostały wkomponowane jakieś dodatkowe opcje.

#73 Tworzymy harmonogram spłat

Obliczenia doradcy kredytowego można skonfrontować przy użyciu własnoręcznie policzonego harmonogramu spłaty kredytu. Uwaga, w tym przypadku cały czas zakładamy, że mamy do czynienia ze stałym oprocentowaniem i tak zwanymi równymi ratami.

Zacznijmy od utworzenia tabeli, w której uzupełnimy potrzebne rubryki. Tabela posiadać będzie 24 wiersze, bo tyle jest okresów (plus dodatkowy na nagłówki i drugi na sumy), w jednej kolumnie określać będziemy ratę kredytu (policzyliśmy ją w poprzednim punkcie), a następnie, przy użyciu nowej formuły, rozbijemy ją na część kapitałową i odsetkową.

Formuła działa analogicznie do poprzedniej. Stopa procentowa to stopa roczna, podzielona na ilość okresów (u nas 12), okres to numer danego miesiąca, dla którego dokonywane są obliczenia. Liczba rat to ilość miesięcy, a kapitał początkowy to kwota zaciągniętego kredytu.

#74 Ile odsetek jest w jednej racie?

Ta porada będzie krótka, ale być może przydatna. Funkcja IPMT oblicza ile w danej płatności jest odsetek. Argumentami dla niej są: stopa procentowa (na wzór dotychczasowych - tj. stopa podzielona przez ilość kapitalizacji), okres dotyczy wybranego miesiąca, dla którego chcemy uzyskać kwotę odsetek.

#75 Tabela danych z dwiema zmiennymi - różne oprocentowania i czas trwania kredytu

Teraz wykorzystajmy trick, który omówiliśmy sobie w jednym z poprzednich artykułów - tabelę danych. Przypomnę, że przedstawia ona wyniki różnych kombinacji wykorzystywanych w równaniu. My ustalamy, że bierzemy kredyt na 100 tys. zł, ale chcemy sprawdzić, ile wynosić będzie rata przy różnym poziomie oprocentowania i różnym okresie spłaty. Jak to zrobić?

Przygotowałem - na razie jeszcze pustą - tabelę. W wierszu mamy poziom oprocentowania, w kolumnie - czas trwania kredytu (okresem jest dla nas miesiąc).

W rogu tabeli, jak na poniższym zdjęciu, wpisujemy formułę PMT, uwzględniając dane z wyjściowej tabeli. Następnie, po zaznaczeniu całej tabeli, którą chcemy uzupełnić wynikami różnych zmiennych tej formuły, klikamy na formatkę Dane -> Analiza warunkowa -> Tabela danych. Argumentem dla wierszy będzie u nas oprocentowanie (komórka C5 podzielona przez 12), a dla okresu C4. Tabel danych wykorzystywać będzie następnie różne kombinacje tych zmiennych, zdefiniowane w wierszach i kolumnach.

Na podobnej zasadzie można stworzyć tabelę danych dla lokat.

#76 Raty malejące

Pokrótce - celem dopełniania tematyki kredytów - przedstawimy sobie tabelę, w której można obliczać oprocentowanie kredytu w systemie rat malejących. Zasadniczo model ten polega na tym, że co miesiąc spłacamy tę samą, równą wartość zaciągniętego kapitału, a odsetki naliczane są od kapitału, który pozostaje do spłaty. Raty malejące wywodzą swą nazwę od tego, że z okresu na okres spada część odsetkowa.

Sposób obliczenia przedstawiłem na poniższym zdjęciu - mam nadzieję, że okaże się to dla Was wystarczająco czytelne. Pamiętajcie, że odsetki naliczają się od kapitału pozostałego na początek miesiąca. W rubryce K odejmujemy zatem od kapitału z zeszłego miesiąca, część raty kapitałowej. Odsetki obliczane są od tej wartości (jest to iloczyn salda na początek miesiąca i oprocentowania podzielonego przez ilość okresów odsetkowych).

#77 Jak obliczyć roczną stopę zwrotu z inwestycji?

No właśnie - powiedzmy, że w styczniu zainwestowaliście kapitał, którego przyrost [(kapitał końcowy/kapitał początkowy -1 )*100%] wyniósł ok. 3%. Czy to dużo? Porównujecie zapewne ten wynik do lokaty, która daje mniej więcej tyle samo, tyle że bez ryzyka i... w skali roku. Jak sprawić, by tę stopę zwrotu sprowadzić do tej samej skali? Służy temu metodą rocznej stopy zwrotu CAGR, funkcjonująca w Excelu pod formułą XIRR.

Celem wykorzystania formuły XIRR, musimy nieco zmienić wygląd naszej tabeli, gdyż formuła jest dość wymagająca jeżeli chodzi właśnie o układ danych, jak i ich zapis.

W pierwszej kolumnie możemy napisać komentarz. U nas jest to po prostu "Data inwestycji", aczkolwiek zaleca się, by był on bardziej szczegółowy, jak "Zakup jednostek funduszy inwestycyjnego XYZ". W drugiej kolumnie piszemy datę dokonania tejże operacji. W trzeciej natomiast - określamy wpłatę lub wypłatę kapitały. Wpłata jest - na przekór - ze znakiem minus, ponieważ niejako "wychodzi" ona z naszego konta, na rzecz dokonania inwestycji/lokaty. A zatem nasze 10 tys. wpisujemy ze znakiem minus.

U dołu dokonujemy wyceny kapitału. Komentarz to "Wycena", a data jest bieżąca (skrót CTRL + ;), tak byśmy mogli codziennie znać faktycznie osiąganą stopę zwrotu (jeżeli wartość inwestycji się nie zmieni, to stopa procentowa będzie z dnia na dzień spadać w skali roku!). Wartość wyceny traktujemy jako wpływ, czyli określamy ją bez minusa.

Formuła XIRR ma następujące argumenty:

wartość;data;wynik.

Nasza wartość to zakres uwzględniający zainwestowany kapitał (otwiera on zakres) i jego wycena (zamknięcie zakresu). Różnica w wycenie zostanie odniesiona do zainwestowanego kapitału i daty dokonania inwestycji. Data również stanowi zbiór rozpoczynający się w dniu dokonania inwestycji, zamykając na dniu wyceny. Wynik jest argumentem opcjonalnym.

Jak więc widzicie, nasze 3.2%, to blisko 9% w skali roku.

#78 Amortyzacja - Excel zna wiele metod liczenia

Nad tą wskazówką nie będę się szczególnie rozwodził. Uznałem jednak, że warto ją przytoczyć ze względu na małych przedsiębiorców, którzy zapewne nie korzystają z wyrafinowanych programów finansowo-księgowych, prowadząc księgowość na własną rękę. Amortyzacja w Excelu może być liczona według pięciu formuł:

SLN - metoda liniowa, wynik podany w skali roku. Excel podpowiada, by do jej wyliczenia uwzględnić tzw. wartość odzyskiwalną.

DB i DDB - metody degresywne, wykorzystujące stałe, współczynniki.

SYD - metoda sumy cyfr wszystkich lat amortyzacji

VDB - metoda podwójnie malejącego salda. Jest to też formuła, która potrafi na stosunkowo najbardziej zróżnicowanie określenie parametrów amortyzacji.

#79 Konwersja jednostek

Problem konwersji jednostek matematycznych, fizycznych lub innych, jest dość typowy. Zapewne każdy z Was wypracował już jakieś rozwiązanie dla tego typu zadania, ale pracując już w Excelu, można spokojnie dokonać odpowiednich obliczeń przy użyciu jednej formuły. Chodzi o formułę KONWERTUJ, która składa się zasadniczo z trzech argumentów: konwertowanej wartości, jej dotychczasowej jednostki, jednostki docelowej. Nazwę jednostki określamy stosownym skrótem - cm, kg, mil, lbm, m3, itp. Pełną listę skrótów i obsługiwanych wielkości znajdziemy pod linkiem:

https://support.office.com/pl-pl/article/KONWERTUJ-funkcja-d785bef1-808e-4aac-bdcd-666c810f9af2

Konwersje wartości zapisanych w systemach dwójkowym, ósemkowym, dziesiętnym i szesnastkowym dokonuje się poprzez dedykowane formuły, rozpoczynające się od nazwy systemu który konwertujemy.

#80 Zaokrąglenia

Dla Excela nic nie jest oczywiste i na każde zadanie znajduje on wiele rozwiązań - naprawdę wszystko jest przemyślane. Nawet tak - pozornie - prosta operacja jak zaokrąglanie wartości, może być przeprowadzona przy użyciu kilku formuł.

ZAOKR - zaokrągla uwzględniając ilość miejsc po przecinku

ZAOKR.DO.PARZ / ZAOKR.NPARZ / ZAOKR.DO.CAŁK - do najbliższych parzystych, nieparzystych, całkowitych

ZAOKR.W.GÓRĘ / ZAOKR.W.DÓŁ - przydatne zwłaszcza przy dokonywaniu obliczeń finansowych - banki i urzędy skarbowe zazwyczaj zaokrąglają na niekorzyść klienta/podatnika

ZAOKR.DO.WIELOKR - zaokrągla do najbliższej wielokrotności wskazanej liczby. (ZAOKR.DO.WIELOKR(11,23;5) zostanie zaokrąglone do wielokrotności piątki, czyli w tym przykładzie do 10.

ZAOKR.DO.TEKST - zaokrągla, zmieniając format komórki na tekstowy

LICZB.CAŁK - sprowadza liczbę do wartości całkowitej bez zaokrąglania (8,9 będzie zapisane jako 8).

 

To by było na tyle jeżeli chodzi o czwarty artykuł z cyklu poświęconego programowi Excel. Przed nami jeszcze jedna odsłona. W niej przejdziemy sobie przez tabele przestawne, a także inne, nieprzypisane do tematyki poszczególnych odsłon lub - nie oszukując - pominięte tricki, które przyszły mi do głowy w trakcie pisania cyklu. W tym misz-miszu będzie więc mowa o zabezpieczaniu danych, komórek, arkuszy, pobieraniu aktualizujących się danych z internetu i jeszcze wielu innych, przydatnych rzeczach. W piątym artykule będziecie mogli też pobrać ściągawkę z przydatnymi skrótami!


Strona:






Tagi

 android   apple   ciekawostki   gaming   google   gry   internet   komputery   kosmos   microsoft   mobilne   nauka   polska   samsung   smartfon   smartfony   tablet   technologia   telefony   windows 

Korzystamy z plików cookies i umożliwiamy zamieszczanie ich osobom trzecim. Pliki cookie pozwalają na poznanie twoich preferencji na podstawie zachowań w serwisie.
Uznajemy, że jeżeli kontynuujesz korzystanie z serwisu, wyrażasz na to zgodę. Poznaj szczegóły i możliwości zmiany ustawień w Polityce Cookies.