Pytanie z EXCELA

[Gość: Terra]

Kto wie, z czego korzystamy licząc oprocentowanie od danego wkładu przez
jakiś okres ? Słyszałem, że podobno korzysta się z funkcji finansowych ale
jeśli tak, to z której konkretnie i jak po kolei to się robi ? A może jest
jakaś inna formuła ?

[Gość: gearbox]

Jedna z metod to policzenie oprocentowania efektywnego za pomocą funkcji

EFFECT(stopa nominalna;liczba okresów odsetkowych w roku)

jeżeli kapitalizacja dokonywana jest kwartalnie liczba okresów odsetkowych
wynosi 4, jeżeli miesięcznie - 12, itd.

Posiadając oprocentowanie efektywne wartość odsetek można policzyć formułą:

=oprocentowanie efektywne*(okres, za który liczymy odsetki/rok)*kwota lokaty

okresy zwykle okresla się w dniach, rok bankowy ma 360 dni.

Zatem cała formuła może mieć kształt:

=EFFECT(stopa nominalna; liczba okresów odsetkowych)*(liczba dni/360)*kwota

Jeżeli interesuje nas kwota odsetek po pół roku od lokaty w wysokości 22000,
oprocentowanej w interesującym nas okresie na 7% rocznie, z kapitalizacją
kwartalną (odsetki są dopisywane do wartości lokaty co kwartał) formuła będzie
miec postać:

=EFFECT(7%;4)*(180/360)*22000

W celu użycia funkcji EFFECT i wielu innych funkcji finansowych konieczne jest
jednak zainstalowanie Analysis ToolPack z dysku instalacyjnego pakietu MS
Office.

Prościej jest zatem użyć wzoru na procent składany, tworząc odpowiednią formułę
(odsyłam do podręcznika matematyki, ekonomii lub rachunkowości - bo przyznam
szczerze nie pamiętam). Ewentualny brak wzoru mozna obejść licząc wszystkie
okresy kapitalizacji osobno jak w przypadku braku kapitalizacji (patrz niżej).
Tzn. policzyć odsetki w pierwszym okresie kapitalizacji (załóżmy, że wynik mamy
w komórce A1), następnie jako kwoty lokaty w drugim okresie kapitalizacyjnym
użyć sumy kwoty początkowej i komórki A1, itd.

W przypadku, gdy nie mamy do czynienia z kapitalizacją (wiele banków przelewa
odsetki na osobny rachunek, zamiast dołączać je do wartości lokaty) sprawa jest
zupełnie prosta:

=stopa niminalna*(liczba dni/360)*kwota lokaty

Od wspomnianej wyżej lokaty bez kapitalizacji odsetek uzyskamy:

=7%*(180/360)*22000

czyli 770 zł w okresie pół roku.


W przypadku niejasności pytaj, albo podaj szczegóły do wyliczenia, to
dostaniesz formułę.

Pozdro

[Gość: merien]

Mozna wyprowadzic sobie taki wzor
Xn=Wp(sⁿ+ ns^(n-1) +ns^(n-2)
...+1)
robisz to tak dopoki ns<supɭ</sup>
Xn - Stan na dany okres kapitalizacji
n - okres kapitalizacji
Wp - wartosc poczatkowa wkladu
s- stopa procentowa

[Gość: Terra]

Dzięki za rady ale te sposoby nie opierają się na Excelu, o który prosiłem. Czy
naprawdę się nie da zrobić tego za pomocą jakiejś formuły w Excel-u? A co z
funkcjami finansowymi ?

[Gość: gearbox]

To jednak jest w Excelu. Ale dobra. Podaj więcej szczegółów to dostaniesz
gotową formułę: kwota lokaty, oprocentownie nominalne (ewentualne zmiany w
okresie lokaty), okres, za który chcesz obliczyć odsetki, info o kapitalizacji
(lub jej braku).

[Gość: Terra]

Dzięki Gearbox! Dla przykładu, jakie jest oprocentowanie rachunku
oszczędnościowego, który ma miesięczną kapitalizację. Oprocentowanie roczne
wynosi 7%. Kwota początkowa - 10000 zł. Chodzi mi o formułę, która umożliwi mi
wyświetlenie odsetek dla wszystkich 12 miesięcy jednocześnie, ponieważ
chciałbym uzyskać odsetki na przestrzeni całego roku.
Terra

[Gość: merien]

to proste dodawnie i mnozenie cos w rodzaju ciagu geometrycznego ale nie do konca, na pewno sobie poaradzisz ja otrzymalem, ten wzor ktory napisalem, wlanie z mnozenia i dodawania niestey nie wyszly potegi jak trzeba na tej stronie ale teraz za n wystarczy podstawic okres i masz stan faktyczny

[Gość: gearbox]

merien ma 100% racji podając wzór na procent składany. Właśnie tak to należy
zrobić.

Ale spróbujemy by the numbers, żeby było przejrzyście (żebyś załapał skąd to
się bierze):

1. w komórce B1 (kolumnę A zostawimy na nagłówki, żebyś wiedział co jest grane)
wpisz =0,07/12, w A1 nazwę tej wartości - oprocentowanie miesięczne

3. w komórce B2 wpisz 10000, w A2 - wartość początkowa

4. w kom B3 wpisz formułę =B2*(1+$B$1), w A3 wartość lokaty po 1 mies.

5. zaznacz zakres B3:B14 i naciśnij CRTL+D - formuła zostanie skopiowana

6. w kom. B4 będzie wartość lokaty po 2 m-cach, w B5 po 3 m-cach, w B14 po 12 m-
cach. Takie też nagłówki możesz dać w kolumnie A.

Wartość odsetek po X m-cach to oczywiście wartość lokaty minus kwota
początkowa. Jeżeli kwota początkowa jest okrągła (łatwa do odejmowania) to
wartośc odsetek jest widoczna gołym okiem bez żadnych rachunków (jak w
przypadku lokaty 10000 - wszystko co powyżej to odsetki). Jeżeli jest inaczej,
w kolumnie C możesz sobie wpisać formułę odejmującą wspomniane 2 wartości.

Dla zrozumienia (trzeba się uczyć! ;):
Tak będzie to wyglądać w przypadku parametrów zadanych przez Ciebie. Ale co,
jeżeli kapitalizacja będzie np. kwartalna? Ano tak samo, tylko okresem stanie
się kwartał, zatem będziesz miał w B5 wartość lokaty po 3 kwartałach, zamiast
po 3 m-cach. Oczywiście trzeba wtedy zmienić(!) wartość oprocentowania w B1 z
=0,07/12 (oprocentowanie miesięczne) na =0,07/4 (oprocentowanie kwartalne,
liczba okresów odsetkowych w roku=4, poprzednio 12). Jeżeli zmieni się
oprocentowanie zmieniasz 0,07 w B1 na np. 0,05 (5% to 5/100) itd. (Celowo nie
stosuję formatowania komórki w procentach, żeby Ci na tym etapie nie mącić.)

Teraz najważniejsze: dlaczego formuła licząca wartość lokaty to =B2*(1+$B$1)
itd.? Wartość lokaty to jej wartość początkowa + odsetki. W danym okresie
kapitalizacji wartością początkową jest wartość lokaty po poprzednim okresie
kapitalizacji. Zatem na początku jest to 10000, po 1 mies.(kwartale, roku,
itp.) - 10000 + odsetki za poprzedni mies. (kwartał, itd.), itd. marien
słusznie zauważył, że mamy tu ciąg, jakkolwiek nie geometryczny, to dość
podobny na oko. Z punktu widzenia formuły wartość początkową zapiszemy jako:

w B3 =B2, w B4 =B3, w B5 =B4 (jasne, prawda?).

O.K. mamy wartość początkową, trzeba dodać odsetki. Odsetki wyniosą
WARTOŚĆ_POCZĄTKOWA*STOPA_ODSETEK(w zadanym okresie) czyli u nas B2*B1.

Zatem wartość lokaty dana jest formułą =B2+B2*B1, czyli =B2*(1+B1), jak
napisaliśmy dla przejrzystości. Dlaczego w naszej formule napisaliśmy $B$1
zamiast B1? W pkt. 5 dokonujemy skopiowania formuły (CTRL+D to
komenda "wypełnij w dół", wykonuje seryjne kopiowanie z pierwszej od góry
zaznaczonej komórki w zakresie do pozostałych). Przy kopiowaniu formuły
zmieniane są adresy komórek w formule (poeksperymentuj! kopiując dowolną
formułę w inne miejsce - zobaczysz jak się zmieni, szybko załapiesz, dlaczego
tak). Zmiana ta nie następuje, jeżeli dodamy $ w adresie. Jeżeli mamy adres
$B1, to formuła skopiowana w dowolne inne miejsce zachowa w adresie B, będzie
zmieniać 1 na 2, 23, 56786, itd. Jeżeli będzie B$1 to zmieniane będzie B na C,
D, AH, itd., niezmienne pozostanie 1. W naszym przypadku odwołanie do adresu
$B$1 nie zostanie zmienione przy kopiowaniu. W każdym okresie kapitalizacji
zakładamy przecież tę samą wysokość odsetek.

O.K., myślę, że tyle wyjaśnień wystarczy. Napisałem jak dla newbie, ale tak mi
wyglądasz (żadnej obrazy, każdy zaczyna od 0). W razie problemów - odzywaj się.

Oczywiście to jest najprostsze możliwe rozwiązanie z punktu widzenia złożoności
wykonania w Excelu (taki był mój cel), żebyś się nie zakręcił. Jeżeli chcesz to
w wersji bardziej profesjonalnej, możesz podać adres e-mail.

Pozdro

Ps. ufff! ale się nastukałem w keyboarda...

[Gość: Terra]

Cześć GEARBOX !

Bardzo dziękuję za szczegóły, o które prosiłem. Nie wiem, czy masz
wykształcenie pedagogiczne ale wytłumaczyłeś naprawdę bardzo przystępnie. Teraz
to wszystko wydaje się bardzo proste. Szczerze mówiąc okazało się, że samemu
byłem krok od rozwiązania, ponieważ zanim do Ciebie napisałem, utworzyłem sobie
formułę w rodzaju kwota początkowa * oprocentowanie + kwota początkowa ale
liczyłem odsetki „na piechotę” (o czym Ci zresztą pisałem), bo nie wiedziałem
jak przy okazji kopiowania formuły na resztę pól, rubrykę oprocentowanie
zostawić np. jako a2 we wszystkich innych rubrykach (po kopiowaniu zamieniało
mi się w b2,c2,d2 itd. ) Dopiero od Ciebie dowiedziałem się, że wystarczyło
przed tym wpisać $. Wyprowadzenie przed nawias też czyni wzór bardziej
przejrzystym. Skąd wiedziałeś, że CTRL+D to kopiowanie akurat formuły a nie
formatów albo wartości ? Chciałem znaleźć to w pomocy EXCEL-a ale nie
znalazłem. Wiem, że zwykłe kopiowanie to CTRL+C bo ta informacja wyświetla się
na pasku przy otwieraniu Edycji. Byłoby dużo łatwiej gdyby znać te wszystkie
skróty. Gdzie jest jakaś informacja na ten temat ? Trochę znalazłem w pomocy
Excela w klawiszach skrótów ale np. kopiowania formuły jako CTRL+D nie było.
Mój e-mail [e-mail]

[Gość: gearbox]

Nie zajmuję się nauczaniem w zasadzie, ha, ha, ha... Chociaż prowadzę od czasu
do czasu różne szkolenia (jednakze nie z informatyki) ;).

Szczerze powiedziawszy, nie pamiętam skąd wziąłem do CTRL+D, inna metoda to
ciągnięcie myszą dolnej prawej krawędzi obramowania zaznaczonej komórki (przy
naciśnięciu CTRL dodatkowo zachodzi wypełnianie ciągiem arytmetycznym z krokiem
1 - użyteczne np. przy numerowaniu pozycji).

Jeżeli zamierzasz więcej pracować z Excelem może warto zaopatrzyć się w jakąś
literaturę?

Czy adres e-mail oznacza, że pożądasz jeszcze czegoś ode mnie?

Pozdro,

[Gość: Terra]

Dzięki za wiadomość. E-mail podałem na wypadek, gdybyś swą wiadomość chciał
przesłać bezpośrednio do mnie a nie na forum, ale skoro napisałeś na forum,
sprawa jest nieaktualna. Jeszcze raz dzięki za wszystko i pozdrawiam. Terra