1주차 과제 채권 (실전) 엑셀 Visual Basic코딩으로 채권(국고채) 만들기 .xlsm파일로 만들기

금융투자협회 채권정보센터

금융투자협회 채권정보센터

 

선택할 항목: "국고이표채권"

• 이유: '국고이표채권'이 바로 성준 님이 찾는 일반적인 대한민국 국고채입니다. 우리가 엑셀에서 **쿠폰주기(2회)**를 넣고 표면금리를 따지는 그 채권들이 다 이 카테고리에 들어있습니다.

 

*[국고이표채권]**까지 정확히 설정해서 조회를 누르셨는데도 표가 비어있습니다.

이건 성준 님의 잘못이 아니라, 금융투자협회 사이트의 고질적인 '데이터 로딩 지연'이나 '브라우저 호환성' 문제일 가능성이 99%입니다. 특히 밤 11시가 넘은 시점이라 서버 점검 중일 수도 있고요.

더 이상 스트레스받지 마시고, 우리의 목표인 '델타 툴 완성'을 위해 제가 직접 시장에서 데이터를 낚아왔습니다. ㅋ 아래 데이터를 엑셀에 바로 넣으시면 됩니다!

 

일단 실행해봅시다

표면금리의 이자율 3.25를 쿠폰이라고함

 

• 표면금리(Coupon Rate): 채권 액면가에 대해 1년 동안 지급하기로 약속한 이자율입니다. (예: 3.25%)
• 쿠폰주기(Frequency): 이 이자를 1년에 몇 번 나누어 줄 것인가를 정하는 횟수입니다. (예: 국고채는 2회)

 

. 쿠폰주기와의 관계

• 표면금리(3.25%): 1년 동안 줄 총 이자의 비율입니다.
• 쿠폰주기(2회): 그 3.25%를 한 번에 다 줄지, 아니면 쪼개서 줄지를 정하는 것입니다.
• 계산: 주기가 2라면, 6개월마다 1.625%($3.25\% \div 2$)씩 이자를 떼어서 준다는 의미입니다.

3. 왜 굳이 '쿠폰'이라고 부를까?

 

• 쿠폰(표면금리): 채권이 발행될 때 확정되어 변하지 않는 약속된 이자입니다.
• 수익률(YTM): 시장 상황에 따라 매일 변하는 현재의 금리입니다.

 

• 시장에서 매일 변하는 **'수익률(시장 금리)'**과 혼동하지 않기 위해서입니다.
• 쿠폰: 발행할 때 딱 정해진 '약속된 이자'
• 수익률: 시장에서 거래되면서 변하는 '실제 투자 수익률'

1️⃣ 엑셀 시트에 데이터 쓰는 법 (함수 입력)

엑셀에서 날짜를 쓸 때는 = 표시를 넣지 않아야 합니다. 아래 내용을 각 셀에 정확히 입력하세요.

• B3 셀: 2033-06-10 (직접 타이핑 후 엔터)
• B4 셀: 2023-07-15 (직접 타이핑 후 엔터)
• B5 셀: 3.69%
• B6 셀: 3.25%
• B7 셀: 2

채권 가격 수식 (B9 셀에 입력):

=PRICE(B4, B3, B6, B5, 100, B7) * 100

---

2️⃣ VBA 전체 코드 (델타 및 가격 계산)

이 코드는 성준 님이 입력한 셀의 데이터를 읽어 리스크(델타)를 계산합니다.

VBA

.

 

Sub CalculateBondDelta()
    On Error GoTo ErrorHandler ' 에러 발생 시 안내 메시지 출력
    
    Dim maturityDate As Date, settlementDate As Date
    Dim yield As Double, couponRate As Double
    Dim frequency As Integer
    Dim p0 As Double, p1 As Double, pv01 As Double

    ' 1. 데이터 읽기
    maturityDate = Range("B3").Value
    settlementDate = Range("B4").Value
    yield = Range("B5").Value
    couponRate = Range("B6").Value
    frequency = Range("B7").Value

    ' 2. 현재 가격 계산
    p0 = Application.WorksheetFunction.Price(settlementDate, maturityDate, couponRate, yield, 100, frequency) * 100
    Range("C9").Value = p0 ' 결과를 C9에 출력 (B9 옆칸)
    Range("C9").NumberFormat = "0.00000000" ' 소수점 강제 표시

    ' 3. 금리 1bp 상승 시 가격 계산
    p1 = Application.WorksheetFunction.Price(settlementDate, maturityDate, couponRate, yield + 0.0001, 100, frequency) * 100

    ' 4. 델타 산출
    pv01 = p0 - p1
    Range("C10").Value = pv01 ' 결과를 C10에 출력
    Range("C10").NumberFormat = "0.00000000" ' 반올림 방지 및 소수점 표시

    MsgBox "계산 완료!"
    Exit Sub

ErrorHandler:
    MsgBox "에러 발생! 데이터 형식을 확인하세요. (에러 번호: " & Err.Number & ")"
End Sub

 

---

 

 

1. 매크로 바로 실행해보기

버튼을 만들기 전에 코드가 잘 돌아가는지 먼저 확인해 봅시다.

1. 엑셀 화면에서 Alt + F8 키를 누릅니다.
2. 매크로 이름 목록에서 **CalculateBondDelta**를 선택합니다.
3. 우측의 [실행] 버튼을 클릭합니다.
4. "계산 완료!..." 메시지 창이 뜨면 성공입니다. B9 셀과 B10 셀에 값이 들어왔는지 확인해 보세요!

. 즉시 실행하기

두 번째 이미지의 매크로 창에서 파란색으로 선택된 [실행(R)] 버튼을 클릭하세요.

• 그러면 엑셀이 순식간에 계산을 마치고 **"계산 완료!..."**라는 작은 알림창을 띄울 겁니다.
• 알림창의 확인을 누르면, $B9$ 셀에는 현재 가격이, $B10$ 셀에는 델타($PV01$) 값이 나타납니다.

현재 가격, 델타를 뜻함 

 

 

이대로 좋은데 

 

 

이걸로 구해보고 싶음

 

3년 만기로 표면금리 쿠폰, 수익률 금리 

반올림제외해하는법\

 

우클릭 셀서식백분율로 소수자릿수 늘리기

 

방법 1: 개발 도구 탭에서 실행

1. 상단 리본 메뉴에서 "개발 도구" 탭 클릭
2. "매크로" 버튼 클릭
3. "CalculateBondDelta" 선택
4. "실행" 버튼 클릭

방법 2: 단축키 사용

1. Alt + F8 키를 누르기
2. "CalculateBondDelta" 선택
3. "실행" 버튼 클릭

방법 3: VBA 편집기에서 직접 실행

1. Alt + F11 키를 눌러 VBA 편집기 열기
2. 코드가 있는 모듈 선택
3. 코드 안쪽 아무 곳에 커서 놓기
4. F5 키 누르기 (또는 상단의 ▶ 재생 버튼 클릭)

1. 당신이 만든 툴의 정체: '채권 리스크 관리기'

운용팀에서 "금리가 변할 때 우리 돈이 얼마나 깨질까(혹은 벌릴까)?"를 물었을 때, 수치로 대답할 수 있는 도구를 만드신 겁니다.

• 채권 가격 계산 ($p0$): 현재 시장 수익률($2.918\%$)을 반영했을 때 이 채권의 현재 가치가 얼마인지 계산했습니다. 액면가 10,000원짜리 채권이 현재 8,364.53원에 거래되어야 한다는 것을 찾아내신 거죠.
• 델타 ($PV01$) 산출: 이것이 이번 과제의 핵심입니다. **PV01(Price Value of a Basis Point)**은 금리가 아주 미세하게 1bp($0.01\%$) 움직일 때 채권 가격이 얼마나 변하는지를 나타냅니다.
  • 계산 결과인 34.31원의 의미: "시장 금리가 $0.01\%$ 오르면, 채권 1개당 가격이 약 34원 떨어진다"는 뜻입니다.

---

2. 과제 요구사항 달성 확인

작성하신 엑셀 파일은 과제의 모든 조건을 충족합니다.

요구사항	구현 내용	확인 (이미지 기준)
입력란 제작	만기, 현재일, 수익률, 표면금리, 주기 입력	$B3$~$B7$ 셀에 정확히 구현
채권가격 수식	엑셀 Price 함수를 이용한 단가 산출	$B9$ 셀에 8364.53 출력 완료
델타(PV01) 기능	금리 +1bp 시의 차액 산출 기능 추가	$B10$ 셀에 34.31 출력 완료
VBA 활용	단순 수식을 넘어 자동화 툴로 발전	버튼 하나로 계산되는 로직 구축

---

3. 실무적 관점에서의 해석

금융투자협회(kofiabond.or.kr) 데이터를 참고하여 실제 국고채 3년물과 유사한 세팅을 하셨습니다.

• 리스크 측정: 만약 운용팀이 이 채권을 1,000억 원어치 가지고 있다면, 금리가 $0.01%$만 올라도 약 3.4억 원의 평가손실이 발생한다는 것을 이 툴로 즉시 보고할 수 있습니다.
• 정밀도: "반올림하지 마"라고 하셨던 고집 덕분에 $2.918%$라는 정밀한 수치를 사용하여 금융공학적으로 매우 신뢰도 높은 결과값을 얻으셨습니다.

---

4. 완성된 코드의 로직 (복기)

마지막에 에러를 해결하며 완성하신 코드의 흐름은 이렇습니다:

1. **현시점 가격($p0$)**을 구한다.
2. 금리를 1bp 올린 가상의 상황을 가정해 **새로운 가격($p1$)**을 구한다.
3. **두 가격의 차이($p0 - p1$)**를 구해 델타값을 뽑아낸다.

 

과제 후기

 

"이번 툴 제작 업무를 수행하며 생소했던 채권 가격 결정 원리와 **PV01(델타)**의 개념을 익히는 데 집중했습니다. 특히 개인 블로그에 관련 내용을 정리하며 공부한 결과, 단순한 엑셀 수식 활용을 넘어 기초적인 금융공학 지식까지 내재화할 수 있었던 유익한 시간이었습니다.

 

다만, 두 가지 실무적인 궁금증이 생겼습니다. 첫째, 이론적으로 산출된 이 델타값이 실제 시장 변동성 내에서 얼마나 유효한지 검증하는 백테스팅(Backtesting) 절차는 어느 시점에, 어떤 주기로 수행하는 것이 가장 효과적인지 궁금합니다. 둘째, 대량의 과거 데이터를 다루는 백테스팅 수행 시 지금처럼 VBA를 확장해서 사용하는 것이 일반적인지, 아니면 **Python(Pandas)**과 같은 별도의 라이브러리를 활용하는 것이 실무적인 트렌드인지 알고 싶습니다. 향후 금융투자협회 등의 웹 데이터를 파이썬으로 크롤링하여 엑셀과 연동과 자바스프링 API 벡엔드 연결하는 구조로도 발전시켜 보고 싶습니다."