Jak obliczyć efektywną stopę procentową obligacji za pomocą Excela
w tym poście na blogu omówię, jak obliczyć efektywną stopę procentową obligacji za pomocą programu Excel.
jaka jest efektywna stopa procentowa?
jeśli wiesz, co to jest efektywna stopa procentowa, możesz pominąć tę sekcję. Przejdź do następnej sekcji.
efektywna stopa procentowa to stopa procentowa, którą otrzymujesz lub musisz zapłacić faktycznie z inwestycji lub pożyczki.
powiedzmy, że wziąłeś pożyczkę w wysokości 10 000 USD z lokalnego banku z roczną stopą procentową 12%. Bank powiedział, że Twoje odsetki będą powiększane co miesiąc.
nie wiesz co to znaczy. Przeczytaj uważnie, ważne jest, aby zrozumieć tę koncepcję.
roczna stopa procentowa / nominalna stopa procentowa: 12%
tak, miesięczna stopa procentowa: 12%/12 = 1%
na koniec miesiąca 1: kwota główna + odsetki będą: $10,000 + $10,000 x 1% = $10,000 (1 + 0.01) = $10100
tutaj zakładam, że nie zapłaciłeś odsetek ($100 za ten przypadek) za pierwszy miesiąc. Więc, przez drugi miesiąc, Twoja główna kwota wyniesie $10,100.
na koniec miesiąca 2: Twoja kwota główna + odsetki wyniesie = $10100 + $10100 x 1% = $10201
jak, chcę zrobić wzór z powyższego stwierdzenia, więc, mam zamiar ponownie powyższą linię w następujący sposób:
= $10100 + $10100 x 1%
= $10100 (1 + 1%)
= $10,000 x (1 + 1%) x (1 + 1%); As $10100 = $10,000 x (1 + 1%)
= $10,000 x (1 + 1%) ^2; As (1 + 1%) x (1 + 1%) = (1 + 1%) ^2
= $10,000 x (1 + 0.01) ^ 2
w ten sposób na koniec 3 miesiąca Twoja kwota główna + odsetki wyniesie: $10,000 x (1 + 0.01) ^ 3
… … …
… … …
… … …
w ten sposób, po 12 miesiącach, Twoja kwota główna i odsetki będą wynosić: $10,000 x (1 + 0.01) ^12 = $11268.25
więc zapłacisz całkowite odsetki: ($11268.25 – $10,000) / $10,000 = 12.68%. Ale bank powiedział, że Twoja roczna stopa procentowa wynosi 12%.
to, co Bank powiedział, To nominalna stopa procentowa, ale z powodu miesięcznych odsetek złożonych, płacisz więcej niż nominalna stopa procentowa. Ta stopa procentowa nazywana jest efektywną stopą procentową.
w tym artykule dowiesz się więcej o nominalnej i efektywnej stopie procentowej: nominalna stopa procentowa wzór Excel (Oblicz z efektywnej stopy procentowej).
obliczmy teraz efektywną stopę procentową obligacji.
przykład 1: Obliczanie odsetek efektywnych od obligacji dyskontowych
masz dodatkowe pieniądze i chcesz zainwestować swoje pieniądze w obligację.
oto szczegóły obligacji:
- wartość nominalna: $100,000
- podana Stopa / nominalna stopa / kupon / kwiecień: 5%
- okres zapadalności: 5 lat
- częstotliwość płatności odsetek: roczna
- cena emisyjna: $95000 (obligacja jest sprzedawana z dyskontem)
jeśli chcesz dowiedzieć się więcej o Bondzie, przejdź przez ten link.
Obliczanie efektywnej stopy procentowej i obliczanie wewnętrznej stopy zwrotu w rzeczywistości to samo. Pokażę Ci więc, jak obliczyć wewnętrzną stopę zwrotu powyższej obligacji.
spójrz na poniższy obrazek.
obraz jest oczywisty. Ale wyjaśnię to krok po kroku.
w roku 0 inwestujesz 95 000 USD (cena emisyjna obligacji), aby kupić obligację. Płacisz $95,000, ponieważ obligacja jest sprzedawana z dyskontem.
pod koniec pierwszego roku otrzymujesz 5000 USD jako wypłatę odsetek od emitenta obligacji. Jak oblicza się te $ 5,000?
wartość nominalna obligacji wynosi 100 000 USD, a nominalna roczna stopa procentowa wynosi 5%. Tak więc otrzymasz wypłatę odsetek w wysokości: $100,000 x 5% = $100,000 x 0,05 = $5,000.
w ten sam sposób, po 2, 3 i 4 latach, otrzymasz wypłatę odsetek w wysokości 5000 USD.
ale po zakończeniu 5 roku otrzymasz $105,000. Ponieważ twoja Obligacja dojrzała i odzyskasz wartość nominalną obligacji $100,000 + roczne odsetki $ 5,000 = $105,000.
tak więc są to przepływy pieniężne, które otrzymujesz w okresie posiadania powyższej obligacji.
teraz obliczyliśmy IRR (wewnętrzna stopa zwrotu) lub efektywną stopę procentową za pomocą funkcji IRR Excela w komórce C17: =IRR (C11: C16)
funkcja IRR Zwraca wewnętrzną stopę zwrotu dla serii przepływów pieniężnych.
składnia funkcji IRR: IRR (values, )
jako wartości funkcji IRR, wprowadziliśmy okresowe przepływy pieniężne i jako odgadnięcie, nie przekazaliśmy żadnej wartości, ponieważ nie jest ona wymagana.
i otrzymujemy 6,19% jako efektywną stopę tych przepływów pieniężnych.
więc widzisz, że nie jest trudno obliczyć efektywną stopę procentową obligacji sprzedawanych z dyskontem.
Czytaj więcej: Jak obliczyć IRR (wewnętrzną stopę zwrotu) w Excelu (9 prostych sposobów).
przykład 2: Obliczanie efektywnego udziału w obligacjach premium
w tym przykładzie nasza Obligacja sprzedaje się z premią.
oto szczegóły obligacji:
- wartość nominalna: $100,000
- podana Stopa / nominalna stopa / kupon / kwiecień: 5%
- okres zapadalności: 5 lat
- częstotliwość płatności odsetek: roczna
- cena emisyjna: $105,000 (Obligacja sprzedaje się z premią)
za tę obligację cena emisyjna wynosi 105 000 USD. Więc, w roku 0, nasza inwestycja pokazuje – $105,000.
po 1, 2, 3 i 4 roku Obligatariusz otrzyma odsetki w wysokości 5000 USD. Ponieważ wartość nominalna obligacji wynosi 100 000 USD, a jej roczna nominalna stopa procentowa wynosi 5%. Tak więc, co roku wypłata odsetek będzie wynosić $100,000 x 5% = $5,000.
pod koniec 5 roku Obligacja dojrzeje. Tak więc Obligatariusz otrzyma kwotę nominalną i roczną wypłatę odsetek= $100,000 + $5,000 = $105,000.
Tak więc dla tej obligacji są to przepływy pieniężne, które posiadacz obligacji otrzyma przez następne 5 lat.
wykorzystaliśmy funkcję IRR Excela do obliczenia wewnętrznej stopy zwrotu lub efektywnej stopy procentowej dla tych przepływów pieniężnych w komórce C35: = IRR (C29: C34).
i otrzymujemy wewnętrzną stopę zwrotu lub efektywną stopę procentową w wysokości 3,88%.
więc widzisz ogromną różnicę w uzyskaniu obligacji w Rabacie i w premii.
ale przed zakupem obligacji, nie tylko obliczysz efektywną stopę procentową, ale także sprawdzisz rating obligacji (przez niezależną firmę ratingową). Im wyższa ocena obligacji, Twoje pieniądze są bezpieczniejsze w firmie.
przykład 3: Obliczanie odsetek efektywnych, gdy płatności nie są roczne
w tym przykładzie pokażę Ci obligację, która płaci płatności odsetek półrocznie (dwa razy w roku), a nie rocznie. Niektórzy emitenci obligacji mogą nawet płacić miesięcznie, kwartalnie lub z dowolną częstotliwością.
oto szczegóły obligacji:
- wartość nominalna: $100,000
- podana Stopa/nominalna stopa/kupon/kwiecień: 5%
- okres zapadalności: 5 lat
- częstotliwość płatności odsetek: półrocznie (dwa razy w roku)
- cena emisyjna: 95.000 dolarów (obligacja jest sprzedawana z dyskontem)
powiedzmy, że kupiłeś obligację 1 stycznia 2018 r. i tutaj będą twoje przepływy pieniężne przez następne 5 lat.
ponieważ oprocentowanie będzie realizowane półrocznie (dwa razy w roku), musimy podzielić podaną stopę procentową / nominalną stopę procentową w wysokości 5% przez 2. To zwróci 2,5%.
tak więc nasze półroczne wypłaty odsetek będą wynosić 100 000 $x 2,5% = 2500$.
a kiedy Obligacja dojrzeje, otrzymasz $102,500 (wartość nominalna + odsetki z ostatnich 6 miesięcy).
aby uzyskać wewnętrzną stopę zwrotu lub efektywną stopę tych przepływów pieniężnych, musimy użyć funkcji XIRR programu Excel. Ponieważ przepływy pieniężne nie są roczne. Gdy przepływy pieniężne są odbierane w dyskretny sposób, musisz użyć funkcji XIRR, aby uzyskać wewnętrzną stopę zwrotu przepływów pieniężnych.
funkcja XIRR Zwraca wewnętrzną stopę zwrotu dla harmonogramu przepływów pieniężnych.
składnia funkcji XIRR: XIRR (values, dates, ).
w komórce G22 użyłem tego wzoru: =XIRR (G11: G21, F11: F21)
G11: G21 to otrzymane przepływy pieniężne, a F11: F21 To daty otrzymania przepływów pieniężnych.
i otrzymujemy 6,274% efektywnej stopy procentowej dla tych przepływów pieniężnych.
Pobierz skoroszyt programu Excel
oto moja metoda obliczania efektywnej stopy procentowej obligacji za pomocą programu Excel. Wiesz, jak to zrobić? Chętnie się dowiem. Podziel się swoimi przemyśleniami na temat tego artykułu w polu komentarzy.
podobne odczyty
- efektywna metoda oprocentowania w Excelu
- Oblicz rentowność do terminu zapadalności (YTM) w Excelu (prosta formuła + szablon)
- efektywna metoda Oprocentowania szablon Excela (bezpłatny)
- efektywna formuła Oprocentowania Excel + bezpłatny kalkulator