Funkcje finansowe w arkuszach kalkulacyjnych

As-Easy-As, Excel, Lotus 1-2-3, Quattro Pro... to tylko najbardziej znane arkusze kalkulacyjne. Kto wie, ile jeszcze będzie? Wszystkie umożliwiają korzystanie z pewnej liczby funkcji.

As-Easy-As, Excel, Lotus 1-2-3, Quattro Pro... to tylko najbardziej znane arkusze kalkulacyjne. Kto wie, ile jeszcze będzie? Wszystkie umożliwiają korzystanie z pewnej liczby funkcji.

Wiele z nich jest oczywistych. Inne przydać się mogą tylko niektórym użytkownikom, chociaż czasami nawet oni zapominają o ich istnieniu, programując je od początku. Do tej grupy - jak pokazuje praktyka - należą funkcje finansowe. Ich przydatność będzie wzrastać w miarę wchodzenia w życie nowych przepisów finansowych, zasad przygotowywania bilansów, rachunków zysków i strat, analiz określających kondycję finansową, reguł naliczania podatków i metod zarządzania przedsiębiorstwami, a wszystko to zgodnie ze standardem obowiązującym w zjednoczonej od przyszłego roku Europie.

Zapraszamy więc do lektury omówienia wspomnianych funkcji finansowych, występujących w czterech wymienionych wyżej arkuszach. Znajomość któregoś z nich będzie oczywiście w lekturze pomocna, choć nie jest ani wymagana, ani niezbędna.

Słowniczek finansowy

Funkcje finansowe z reguły posługują się następującymi wskażnikami i danymi ekonomicznymi:

koszt (ang.cost) - pierwotny koszt nabycia rzeczy ulegającej zużyciu;

okres (period) - ustalona jednostka czasu lub jej wielokrotność;

płatności (payment) - kwota, którą należy wpłacać z reguły w stałych okresach;

termin (term) - liczba okresów płatności, termin całkowitej spłaty zobowiązania lub uzyskania należności.

rodzaj płatności (annuity) - czy płaci się z góry (=1) czy z dołu (=0);

stopa procentowa (interest rate) - wskaźnik oprocentowania zarówno kredytu, zobowiązania jak i lokaty finansowej lub należności, wyrażony w procentach, oprocentowanie;

wartość aktualna - (present value) obecna wartość środków finansowych (papiery wartościowe, depozyty bankowe) a także inwestycji, których koszt mógł zostać poniesiony wcześniej (bez uwzględniania amortyzacji)

wartość odzyskana - (salvage) wartość odsprzedaży zużytej rzeczy;

wartość pierwotna (principal value) - wartość początkowa inwestycji, również w papiery wartościowe i lokaty.

wartość przyszła (future value) - wartość obecnej inwestycji (również w papiery wartościowe i udzielone pożyczki) w konkretnym momencie w przyszłości.

Zasady korzystania z funkcji

Funkcje ekonomiczne mogą opierać się na pojedynczych danych bądź całych ich seriach. W niektórych bowiem przypadkach dopiero dla dłuższego ciągu danych (bloku: komórka1..komórka2) obliczanie pewnych wartości nabiera sensu.

W większości arkuszy kalkulacyjnych funkcje na ogół są do siebie podobne. Czasem którejś brakuje, ale częściej mają tylko nieco odmienne nazwy ewentualnie inaczej połączoną kolejność parametrów. W kilku przypadkach funkcje z nowszych arkuszy są ogólniejszymi wersjami funkcji z arkuszy dawniejszych. Wtedy wszystkie nowe parametry są opcjonalne, co znaczy, że ich używanie jest nieobowiązkowe. Zaznaczono to w tekście nawiasami kwadratowymi.

Funkcje przydatne dla inwestora

@CTERM(stopa, wart_przyszła, wart_pierwotna) - oblicza okres, po którym przy danej stopie procentowej, z danej wartości pierwotnej osiągnie się podaną wartość przyszłą. Podobnie działają @NPER i @TERM.

@FV(płatność, stopa, termin) - oblicza przyszłą wartość sumy regularnych płatności przy założonej stopie procentowej po upływie danego terminu. Wersja ulepszona nosi nazwę @FVAL.

Przykład: książeczka systematycznego oszczędzania. Jeśli co miesiąc wpłacamy 100.000 zł, to przy miesięcznej stopie procentowej 5% po 12 miesiącach będziemy mieli @FV(100000,5%,12) = 1.591.712 złotych.

@FVAL(stopa, termin, płatność [,wart_pierwotna] [,typ_płatności]) - ulepszona wersja FV, oblicza wartość przyszłą sumy regularnych płatności przy założonej stopie procentowej, terminie spłaty. Opcjonalnie można uwzględnić wartość pierwotną sumy, o ile jest ona różna od wartości wpłat regularnych oraz typ płatności. Zachodzi tożsamość @FVAL(stopa,termin,płatność,0,0)=@FV(płatność,stopa,termin).

@IPAYMT(stopa, okres, termin, wart_pierwotna [,wart_przyszła] [,typ_płatności]), (ang. interest payment) - oblicza tę część płatności, która przypada w danym okresie z tytułu odsetek od kumulowanego w poprzednich okresach kapitału, przy założonej stopie procentowej, terminie spłaty, wartości pierwotnej i wartości przyszłej.

@IRATE(termin, płatność, wart_pierwotna [,wart_przyszła] [,typ_płatności]). Oblicza stopę procentową, przy której dana wartość pierwotna urośnie przy określonych wpłatach terminowych w określonym okresie do żądanej wartości przyszłej.

@PAYMT(stopa, okres, termin, wart_pierwotna [,wart_przyszła] [,typ_płatności]) - oblicza wartość stałych okresowych płatności wymaganych dla pełnej spłaty pożyczki w danym terminie, przy założonej stałej stopie procentowej (int), ewentualnej przyszłej wartości oszczędności (fv) przy danym typie płatności. PAYMT wylicza się z wzoru na sumę szeregu geometrycznego: PAYMT*((1-(1+int)^(-n))/int)=prin - fv

@PPAYMT(stopa, okres, termin, wart_pierwotna [,wart_przyszła] [,typ_płatności]) - (ang. principal payment) oblicza odsetki uzyskane w danym okresie z już spłaconego kapitału, powiększone o wysokość kolejnej spłacanej raty, przy założonej stopie procentowej, terminie spłaty, wartości pierwotnej i docelowej wartości przyszłej, przy danym typie płatności. Ostatnie dwa parametry są, jak wyżej, opcjonalne.

TAB.I.

TABELA PŁATNOŚCI (PAYMT) DANE LICZBOWE: STOPA PROCENTOWA: PRZY ZAŁOŻONEJ 0.1 WARTOSCI POCZĄTKOWEJ INWESTYCJI, STAŁEJ STOPIE PROCENTOWEJ, PRZY DANYM SPOSOBIE TERMIN SPŁATY: PŁATNOŚCI "Z DOŁU" KAŻDEGO OKRESU; 12 Z ROZKŁADEM NA CZĘŚĆ GŁÓWNĄ PŁATNOŚCI POCZĄTKOWA WARTOŚĆ: (PPAYMT) POMNIEJSZONĄ O ZYSK (IPAYMT) -5000 WYNIKAJĄCY ZE STOPY PROCENTOWEJ PRZYSZŁA WARTOŚĆ: 30000 SPOSÓB PŁATNOŚCI: 0 OKRES PPAYMT IPAYMT PAYMT=PPAYMT+IPAYMT

1 -1169.083 500 -669.083 2 -1285.991 616.9083 -669.083 3 -1414.59 745.5074 -669.083 4 -1556.049 886.9664 -669.083 5 -1711.654 1042.571 -669.083 6 -1882.82 1213.737 -669.083 7 -2071.102 1402.019 -669.083 8 -2278.212 1609.129 -669.083 9 -2506.033 1836.95 -669.083 10 -2756.636 2087.553 -669.083 11 -3032.3 2363.217 -669.083 12 -3335.53 2666.447 -669.083

SUMA: -25000 16971.01 -8028.995

@PMT(wart_pierwotna, stopa_zysku, termin) - oblicza stałe okresowe płatności (pmt) wymagane dla pełnej spłaty w danym terminie wartości pierwotnej pożyczki (prin), przy założonej stałej stopie oprocentowania kredytu. Jest to więc zwykła stopa procentowa, z tym że zakłada się spłatę z dołu. @PMT można obliczyć za pomocą @PAYMT, ustalając jej dwa ostatnie parametry jako zera lub pomijając je.

Przykład: pożyczamy 100 mln zł spłacane miesięcznie przez trzy lata. Przy rocznej stopie procentowej 20%, miesięczna rata spłaty wyniesie: @PMT(100, 20/12, 36)= 3,716 mln zł. UWAGA. Zwracamy uwagę na wyrażenie wszystkich parametrów w jednostkach odpowiadających odstępom spłaty.

@PV(płatność, stopa, termin) - oblicza obecną wartość sumy równych płatności (pmt) rozłożonych w określonym terminie (n), przy założonej stopie procentowej (int). Wartość obecną oblicza się z wzoru:

pmt*((1-(1+int)^(-n))/int)=PV

Przykład: funkcja oblicza, że obecna wartość 100 mln zł, jaką w równych ratach zgromadzilibyśmy przez 10 lat lokując owe raty na rachunku bankowym przy założonym oprocentowaniu 8 proc., wynosi @PV(100/10, 8%, 10) = 67,1 mln złotych.

@PVAL(stopa, okres, termin, wart_pierwotna [,wart_przyszła] [,typ_płatności]) - jest ulepszoną wersją @PV, uwzględniającą dwa dodatkowe parametry.

@RATE(wart_przyszła, wart_aktualna, termin) - oblicza stopę procentową, przy której zadana wartość aktualna urośnie w zadanym terminie do żądanej wartośći przyszłej. Istnieje ulepszona wersja funkcji: @IRATE.

@TERM(płatność, stopa, wart_przyszła) - oblicza liczbę płatności, które przy danej stopie procentowej doprowadzą do osiągnięcia zakładanej wartości przyszłej.

Funkcje zależne od przepływów gotówki

Wartości tych funkcji zależą od serii liczb, uporządkowanych w kolumnie lub wierszu liczb charakteryzujących przepływy gotówki w kolejnych okresach.

@IRR(wart_początkowa, seria) - (ang. internal rate of return) oblicza stopę zysku przy założonej wielkości inwestycji, na podstawie uzyskanych z jej tytułu wpływów gotówkowych w poszczególnych okresach. Wartość początkowa stanowi pierwsze przybliżenie @IRR, a seria jest ciągiem liczb odpowiadających wpływom gotówki (cash flow) w kolejnych okresach rozliczeniowych z tytułu inwestycji. Pierwsza liczba w serii oznaczającej wpływy powinna być liczbą ujemną, co do wartości bezwzględnej równą początkowej inwestycji.

TAB.II. OBLICZENIA STOPY ZYSKU IRR. EKRAN ARKUSZA KALKULACYJNEGO.

D98: (P2) [W9] @IRR(D88,B85..B97) A B C D E F G 80 81 82 OBLICZENIA WEWNĘTRZNEJ STOPY ZYSKU (IRR) DLA PRZEPLYWÓW GOTÓWKI 83 84 85 INWESTYCJA= -1000 86 1 110 87 2 120 PIERWSZE PRZYBLIŻENIE: 88 3 125 5.00% 89 4 111 90 5 50 91 6 100 92 7 100 93 8 100 94 9 100 95 10 130 96 11 130 97 12 135 WEWNĘTRZNA STOPA ZYSKU: 98-------------------------------------- 4.31% 99

@NPV(stopa, seria [,typ]) - (ang. net present value) oblicza wartość bieżącą netto przyszłych przepływów gotówki (dochodów lub wydatków), przy danej stopie procentowej. Typ, czyli sposób płatności (z góry, z dołu) nie w każdym arkuszu jest uwzględniany.

Odpisy amortyzacyjne

Te funkcje finansowe obliczają o ile w danym okresie obniży się rzeczywista wartość eksploatowanych środków trwałych w stosunku do ich kosztu. Co więcej, zakładana jest konkretna - po iluś tam latach - wartość odzyskana w wyniku sprzedaży posiadanych środków trwałych po ich całkowitym lub częściowym umorzeniu.

Arkusze kalkulacyjne rozpatrują cztery modele odpisów amortyzacyjnych:

I. Spadek proporcjonalny (ang. straight line depreciation). Opisuje go funkcja:

@SLN(koszt, wart_odzyskana, czas_umorzenia) - w każdym okresie odpis amortyzacyjny @SLN jest stały i zależy jedynie od kosztu zakupu środka trwałego, wartości odzyskanej i ilości okresów, na które podzielony jest czas umorzenia środka trwałego.

II. Spadek arytmetycznie malejący. Opisywany przez funkcję @SYD(koszt, wart_odzyskana, czas_umorzenia, okres) - (ang. sum-of-the-years'-digits) oblicza się przy założeniu, że odpisy stanowią ciąg arytmetyczny malejący, a ich suma daje różnicę pomiędzy kosztem a wartością odzyskaną środka trwałego. Odpis jest największy w pierwszym okresie eksploatacji, a najmniejszy w ostatnim. Obliczany jest z wzoru: (n-1)*n*SYD/2=(koszt-wart_odzyskana)*(n-okres+1), gdzie n=czas_umorzenia środka trwałego.

III. Spadek połówkowy (ang. double-declining balance). Obliczany przez funkcję:

@DDB(koszt, wart_odzyskana, czas_ekspoloatacji, okres). Metoda połówkowego spadku zakłada, że odpis jest proporcjonalny do wartości księgowej z poprzedniego okresu rozliczeniowego (wk) podzielonej przez połowę czasu_eksploatacji przedmiotu (n/2). Wzór na odpis ma więc postać: @DDB=(wk*2)/n,lub @DDB=0, gdy suma odpisów z poprzednich okresów dała już różnicę kosztu i wartości odzyskanej. Odpisy @DDB są z początku mniejsze niż @SYD, ale potem zwykle przewyższają je. Dociekliwi zauważą, że odpisy @DDB spadają wykładniczo.

Przykład: załóżmy, że zakupiliśmy pewien środek trwały, powiedzmy duży komputer, za 10000 mln złotych i podpisaliśmy umowę z bardzo rzetelnym partnerem na to, że po 12 okresach rozrachunkowych (latach?, kwartałach?) odsprzedamy mu sprzęt za 1700 mln zł. Przyjrzyjmy się, jakich odpisów amortyzacyjnych trzeba będzie dokonywać w zależności od oceny sposobu dekapitalizacji naszego urządzenia. Ilustruje to tabela i sąsiadujący z nią wykres. Warto zauważyć, że w przypadku metody DDB urządzenie całkowicie zamortyzowało się przed końcem eksploatacji

TAB.III. TABELA ODPISÓW AMORTYZACYJNYCH.

ODPISY AMORTYZACYJNE INWESTYCJI LICZONE TRZEMA SPOSOBAMI W KOLEJNYCH OKRESACH ROZRACHUNKOWYCH: w mln zł

OKRES @DDB @SYD @SLN KOSZT INWESTYCJI: 1 1666.667 1276.923 691.667 10000 2 1388.889 1170.513 691.667 WARTOSC ODSPRZEDAZY: 3 1157.407 1064.103 691.667 1700 4 964.506 957.692 691.667 CZAS EKSPLOATACJI: 5 803.755 851.282 691.667 12 6 669.796 744.872 691.667 7 558.163 638.462 691.667 8 465.136 532.051 691.667 9 387.613 425.641 691.667 10 238.067 319.231 691.667 11 0.000 212.821 691.667 12 0.000 106.410 691.667

SUMA: 8300 8300 8300

IV. Odpis liczony mieszaną metodą DDB:

@VDB(koszt, wart_odzyskana, czas_życia, okres_poczatkowy, okres_końcowy [,stopa] [,przełącznik]) - funkcja pozwala na obliczenia odpisu metodą DDB z przełączeniem do metody proporcjonalnej. Dostępna tylko w 1-2-3 Lotus wer.3.0 i w Excelu.

W celu komercyjnej reprodukcji treści Computerworld należy zakupić licencję. Skontaktuj się z naszym partnerem, YGS Group, pod adresem [email protected]

TOP 200