채권의 가격과 수익률을 이해하는 것은 생각보다 복잡하다. 시장 금리의 변동, 채권의 만기, 그리고 지급되는 이자 등이 모두 영향을 미치기 때문인데, 이런 다양한 요소를 고려하여 채권의 가치를 평가하기 위해서는 여러 계산이 필요하다.
프로그래밍언어로도 계산할 수 있긴하지만, 엑셀은 복잡한 금융 계산을 손쉽게 처리할 수 있는 기능을 제공하고, 실 업무담당자들과 긴밀히 계산결과를 소통하기에 좋다. 채권의 현재 가치 계산부터 만기수익률(YTM)까지, 엑셀을 통해 간편하게 계산할 수 있다.
문제예시
아래와 같이 액면금액 1,000,000원, 표면금리 12%, 이자기급주기1년, 만기3년의 채권 예시가 있다고 하자. 할인율을 매년 달라져 아래와 같이 현재가치까지 구해놓은 상태이다. 해당 채권의 만기수익률을 구해볼 것이다.
| 기간 | 현금흐름 | 비고 | 할인율 | 현재가치(PV) |
|---|---|---|---|---|
| 1 | 120,000 | 이자 | 10% | 109,091 |
| 2 | 120,000 | 이자 | 11% | 97,395 |
| 3 | 1,120,000 | 원금+이자 | 13% | 776,216 |
| 합계 | 1,360,000 | 982,702 |
엑셀 NPV 함수
값을 찾기전에 알아야할 엑셀 함수가 하나 있어 먼저 알아본다. 채권의 현금흐름을 아래그림 왼편과같이 만들어 놓으면, 시장이자율만 가지고 NPV함수를 이용하여 현재가치 계산을 바로 할 수 있다. 모든 기간에 같은 할인율을 적용할 때만 가능한 함수이다.
= NPV(할인율, 현금흐름데이터범위선택)
목표값찾기 기능
현재가치금액을 기준으로 시장이자율(할인율)을 시행착오법으로 찾아야 하는데, “목표값찾기” 기능부터 알아보겠다. 엑셀 데이터탭에는 “가상분석” 메뉴가 있다.
목표값 찾기 메뉴를 선택.
아래와 같은 창이 나올텐데 매핑하는 값을 잘 알아야 한다.
현재가치 982702 를 만족하는 만기수익률을 구하려고 하는거니, 아래와 같이 982702가 결과로 출력되는 셀을 “수식 셀"에 매핑하고, 변수인 만기수익률 필드를 “값을 바꿀 셀"로 선택한다.
확인을 누르면 실행되며, 엑셀에 변수로 지정했던 셀을 보니 12.729425% 가 산출되었다.
해찾기 기능
목표값 찾기보다 조금 더 정밀하게 값을 산출할 수 있는 해찾기 기능에 대해 알아본다. 데이터 탭에보면 분석도구가 있다.
해찾기 추가기능을 활성화한다.
그러면 아래와 같이 해찾기가 나타나게되는데, 현재 이 엑셀은 맥환경의 엑셀이다보니 윈도우 엑셀과 메뉴나 활성화 위치가 다를 수 있으니 참고한다.
어쨋든 해찾기 기능을 누르면 아래와 같이 팝업창이 나온다.
아까와 동일하다. 변수셀을 만기수익률 셀로 매핑하고, 목표설정 셀은 계산하여 산출되는 채권의 현재가치금액 셀을 매핑하면 된다. 조금 다른건 아까는 그냥 현재채권가격을 그대로 지정값으로 대입했는데, 차이가 0이되도록 수식을 하나 더 걸고 그 셀을 가지고 0 이 되도록 해를 찾아보게 시켰다.
정밀도를 높여야 제대로 나오니, 본인에 맞는 셋팅을 여러번 실행해가며 셋팅해보시길 바란다.
아래와 같이 성공했다는 메시지가 나오면 된 것이다. 변수로 설정한 셀에 가보면 만기수익률이 제대로 산출되어 있을 것이다.
