HotXLS로 Delphi에서 채권과 국채 수식 사용하기

HotXLS는 Excel의 채권 및 국채 금융 함수를 Delphi와 C++Builder에서 네이티브로 평가합니다: PRICE, YIELD, DURATION, MDURATION, 여섯 가지 쿠폰 날짜 기본 함수, 국채 3종 세트, 할인 계열, 프랑스식 감가상각 함수까지입니다. 이들은 라이브러리 자체의 수식 엔진 안에서 계산되므로, 채권 포트폴리오 가격을 매기는 워크북은 Excel도 Analysis ToolPak 추가 기능도 전혀 없는 서버에서 재계산됩니다

이 함수들은 예전에 Analysis ToolPak에 속해 있었고, 그래서 지금도 다루기 까다롭다는 평판이 남아 있습니다. Excel 2007부터는 표준 함수 집합의 일부가 되어, 실제로 더 새로운 함수들이 갖는 _xlfn. 접두사 없이 파일 형식에 그대로 기록됩니다. 이 함수들을 사용하는 워크북은 평범한 워크북이며, 유일한 질문은 그것을 재계산하는 쪽이 이 함수들을 알고 있는지 뿐입니다

쿠폰 날짜 엔진이 반드시 맞춰야 하는 것

모든 채권 계산은 동일한 기본 요소 위에 서 있습니다: 마지막 쿠폰 이후 며칠이 지났는지, 현재 쿠폰 기간이 며칠인지, 다음 쿠폰까지 며칠 남았는지, 그 쿠폰들이 언제 도래하는지, 그리고 몇 번이 남았는지입니다. 이것이 COUPDAYBS, COUPDAYS, COUPDAYSNC, COUPPCD, COUPNCD, COUPNUM이며, 모든 가격과 수익률 함수가 이들을 호출합니다

이를 단순하지 않게 만드는 것은 basis 인자입니다. 30/360 US, actual/actual, actual/360, actual/365, 30/360 European이라는 다섯 가지 일수 계산 관례가 관여하며, 이들은 결제일이 어느 달의 31일에 해당할 때 무슨 일이 일어나는지, 또는 윤년의 2월 쿠폰 날짜가 어떻게 동작하는지 같은 문제에서 서로 다른 답을 냅니다. 한 가지 관례만 정확히 맞추고 나머지는 근사하면, 그럴듯해 보이지만 몇 베이시스 포인트만큼 틀린 결과가 나오는데, 큰 포지션에서는 이것이 실제 돈입니다

HotXLS는 다섯 가지 모두를, 이를 필요로 하는 모든 함수가 공유하는 자체 완결적인 중첩 쿠폰 날짜 엔진 하나로 구현합니다. 그래서 PRICE와 DURATION은 쿠폰 날짜가 어디인지에 대해 서로 어긋날 수 없습니다

Delphi를 위한 HotXLS 쿠폰 날짜 엔진: 채권 입력값과 선택된 일수 계산 기준이 PRICE, YIELD, DURATION, MDURATION이 공유하는 여섯 개의 COUP 기본 함수로 흘러들어갑니다 — 다섯 가지 일수 계산 기준 모두가 HotXLS의 모든 금융 함수가 공유하는 하나의 중첩 쿠폰 날짜 엔진으로 흘러들어가므로, PRICE와 DURATION은 쿠폰 날짜가 어디인지에 대해 절대 어긋날 수 없습니다

워크북 안에서의 가격 계산과 수익률

호출하는 코드에는 특별할 것이 없습니다: 수식을 쓰고, 재계산하고, 값을 읽으면 됩니다. 바로 이것이 핵심입니다 — 워크북이 모델이고, Delphi 애플리케이션은 계산기일 뿐입니다:

uses
  lxHandleX;

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Bond');
    Sheet.Cells[1, 1].Value := EncodeDate(2026, 3, 15);   // 결제일
    Sheet.Cells[2, 1].Value := EncodeDate(2034, 11, 15);  // 만기일
    Sheet.Cells[3, 1].Value := 0.0425;                    // 쿠폰 이율
    Sheet.Cells[4, 1].Value := 0.0391;                    // 요구 수익률
    Sheet.Cells[5, 1].Value := 100;                       // 상환 가격
    Sheet.Cells[6, 1].Value := 2;                         // 이자 지급 빈도
    Sheet.Cells[7, 1].Value := 1;                         // 기준: actual/actual

    Sheet.Cells[9, 1].Formula  := '=PRICE(A1,A2,A3,A4,A5,A6,A7)';
    Sheet.Cells[10, 1].Formula := '=DURATION(A1,A2,A3,A4,A6,A7)';
    Sheet.Cells[11, 1].Formula := '=MDURATION(A1,A2,A3,A4,A6,A7)';
    Sheet.Cells[12, 1].Formula := '=COUPNUM(A1,A2,A6,A7)';

    Book.Recalculate;

    Writeln(Format('price %.6f  Macaulay %.6f  modified %.6f  coupons %d',
      [Double(Sheet.Cells[9, 1].Value), Double(Sheet.Cells[10, 1].Value),
       Double(Sheet.Cells[11, 1].Value),
       Integer(Sheet.Cells[12, 1].Value)]));
  finally
    Book.Free;
  end;
end;

날짜는 문자열이 아니라 실제 날짜 값으로 입력됩니다. 텍스트 날짜는 Excel이 이를 강제 변환해 주기 때문에 Excel에서는 동작하지만, 그 강제 변환은 로케일에 따라 달라지며, 이는 서버 사이드 재계산이 절대 가져서는 안 되는 종류의 의존성입니다. 그 밑바탕에 있는 날짜 시스템 규칙은 날짜 일련번호와 1904 시스템에서 다룹니다

수익률에는 닫힌 형태의 해가 없으며, 그 결과가 있다

PRICE는 직접 계산입니다: 현금 흐름을 할인하고 합산합니다. YIELD는 그 역함수이며, 남은 쿠폰이 하나보다 많은 채권에 대해서는 닫힌 형태의 해가 없으므로 수치적으로 풀어야 합니다. HotXLS는 이분법 솔버를 사용하는데, 이는 빠르기보다는 안정적으로 수렴합니다

실무적인 함의가 두 가지 따라옵니다. 수익률은 가격보다 눈에 띄게 계산 비용이 크므로, 만 개의 포지션에 대해 수익률을 계산하는 포트폴리오 재평가는 가격만 계산하는 것보다 눈에 띄게 더 많은 비용이 들며, 자신의 모델이 실제로 둘 중 무엇을 필요로 하는지 아는 것이 중요합니다. 그리고 — 주어진 값으로는 어떤 수익률도 그 채권의 가격을 맞출 수 없어서 — 구간을 좁힐 수 없는 수익률은 잘못된 숫자가 아니라 오류를 반환하는데, 이것이 올바른 동작이며 테스트해야 할 대상입니다

Delphi의 HotXLS에서 PRICE는 할인된 현금 흐름에 대한 한 번의 직접 계산인 반면, YIELD는 닫힌 형태의 해가 없어 구간을 잡을 수 없을 때 오류를 반환하는 이분법 루프로 풀립니다 — PRICE는 할인된 현금 흐름을 한 번 직접 훑는 계산이지만, YIELD는 닫힌 형태의 해가 없어 구간을 잡고 이분법으로 풀며, 입력값으로 가격을 매길 수 없을 때는 잘못된 숫자 대신 오류를 반환합니다

국채는 그 자체로 하나의 계열이다

국채는 쿠폰이 없는 할인 상품이므로, 채권 함수 대신 TBILLPRICE, TBILLYIELD, TBILLEQ를 사용합니다. TBILLEQ는 할인율을 채권 등가 수익률로 변환하는데, 알아 둘 만한 문서화된 특이점이 있습니다: 만기가 182일을 넘으면 그 증권이 반년 기간을 하나 이상 걸치기 때문에 변환이 선형이 아니라 이차식이 됩니다

쿠폰 없이 이자 지급 빈도만 주고 국채를 PRICE에 대신 넣어도 같은 숫자가 나오지 않습니다. 할인 상품 계열인 DISC, INTRATE, RECEIVED, PRICEDISC, YIELDDISC가 존재하는 것은 바로 이런 상품들이 다르게 고시되고 결제되기 때문입니다

나머지 함수 세트를 한자리에

채권과 국채를 넘어, 같은 묶음은 실제 모델에서 이들을 둘러싼 함수도 다룹니다. PRICEMAT과 YIELDMAT은 만기에 이자를 지급하는 증권을 다룹니다. ACCRINTM은 그런 증권의 발생 이자를 계산합니다. EFFECT와 NOMINAL은 실효 연이율과 명목 연이율을 서로 변환하는데, 이는 사람이 손으로 계산할 때 가장 흔히 틀리는 변환입니다. CUMIPMT와 CUMPRINC는 일정 기간 범위에 걸친 누적 이자와 원금을 제공합니다. FVSCHEDULE은 범위든 스칼라든 받아서 서로 다른 이율들을 순서대로 적용합니다

DOLLARDE와 DOLLARFR은 십진수 달러 시세와 분수 달러 시세를 서로 변환하는데, 아직 32분의 1 단위로 고시되는 상품이라면 어디서든 중요합니다. 그리고 AMORDEGRC와 AMORLINC는 프랑스식 감가상각 방법을 구현하는데, 이는 선택적인 편의 기능이 아니라 프랑스 회계 규정이 요구하는 것입니다

// 사람들이 손으로 계산할 때 늘 틀리는 이율 변환
Sheet.Cells[1, 3].Formula := '=EFFECT(0.0425,12)';   // 명목에서 실효로
Sheet.Cells[2, 3].Formula := '=NOMINAL(0.043338,12)'; // 그리고 그 반대로

// 25년 대출의 첫 2년간 누적 이자
Sheet.Cells[4, 3].Formula :=
  '=CUMIPMT(0.0389/12,300,340000,1,24,0)';

// 변동 이율 예측: 3퍼센트, 다음 3.5, 다음 4
Sheet.Cells[6, 3].Formula := '=FVSCHEDULE(10000,{0.03;0.035;0.04})';

직접 작성하지 않은 구현을 검증하기

금융 함수는 '숫자가 나온다'는 것이 아무것도 증명해 주지 않는 유일한 영역입니다. 그 숫자는 구조상 그럴듯해 보이며, 일수 계산 관례의 오류는 눈치채기엔 너무 작고 받아들이기엔 너무 큰 만큼 결과를 어긋나게 만듭니다

실제로 통하는 테스트는 차등 검증입니다. 대표적인 상품들로 이루어진 스프레드시트를 만드세요 — 일수 계산 기준별로 채권 하나씩, 월말에 결제되는 것 하나, 윤년 2월을 걸치는 것 하나, 182일 미만인 국채 하나와 그 이상인 것 하나 — 이를 Excel에서 한 번 계산해 예상 결과로 저장해 두고, 라이브러리를 업그레이드할 때마다 이를 대조하세요. 쿠폰 날짜에 걸쳐 있는 포지션과 31일에 결제되는 것은 구현이 서로 갈리는 지점이므로, 둘 다 의도적으로 포함하세요

Delphi용 HotXLS 채권 및 국채 함수에 대한 차등 검증 세트: 윤년과 월말 사례를 포함한 대표 상품들을 Excel에서 한 번 계산하고 라이브러리를 업그레이드할 때마다 대조합니다 — 차등 검증 스위트는 대표 상품들의 가격을 Excel에서 한 번 매겨 예상값으로 저장해 두고 HotXLS를 업그레이드할 때마다 이를 대조하므로, 베이시스 포인트 단위의 오차가 그럴듯한 출력 뒤에 숨을 수 없습니다

워크북 자체에 대해서는, 이 기능과 짝을 이루는 두 가지가 있습니다: 무엇을 재계산할지 결정하는 계산 엔진은 증분 재계산과 의존성 그래프에서, 통계 계열 함수는 통계 분포 함수에서 다룹니다. 이 둘이 합쳐져야 금융 모델이 서버에서 무인으로 돌아갈 수 있으며, 이것이 애초에 이 함수들을 네이티브로 구현한 이유였습니다

채권 금융, 통계, 공학 함수, 그리고 재계산 엔진은 Delphi와 C++Builder를 위한 하나의 라이브러리로 제공됩니다. 전체 기능 목록은 HotXLS Delphi 스프레드시트 컴포넌트 페이지에서 확인할 수 있습니다