JavaScript
// ── 계리 커스텀 함수 정의 ──
// 위험률 함수 (MORTALITY)
function MortalityRate() { this.name = 'MORTALITY'; this.minArgs = 1; this.maxArgs = 1; }
MortalityRate.prototype = new GC.Spread.CalcEngine.Functions.Function();
MortalityRate.prototype.evaluate = function(age) {
const table = {
30:0.00082, 31:0.00089, 32:0.00097, 33:0.00106, 34:0.00117,
35:0.00130, 36:0.00145, 37:0.00163, 38:0.00184, 39:0.00209,
40:0.00238, 41:0.00271, 42:0.00309, 43:0.00352, 44:0.00401,
45:0.00456, 46:0.00518, 47:0.00588, 48:0.00667, 49:0.00756
};
return table[Math.floor(age)] || 0.001;
};
// 생존율 함수 (SURVIVAL, nPx)
function SurvivalRate() { this.name = 'SURVIVAL'; this.minArgs = 2; this.maxArgs = 2; }
SurvivalRate.prototype = new GC.Spread.CalcEngine.Functions.Function();
SurvivalRate.prototype.evaluate = function(age, n) {
let px = 1;
for (let t = 0; t < n; t++) px *= (1 - MortalityRate.prototype.evaluate(age + t));
return px;
};
// 연금현가 함수 (ANNUITYPV, äx)
function AnnuityPV() { this.name = 'ANNUITYPV'; this.minArgs = 3; this.maxArgs = 3; }
AnnuityPV.prototype = new GC.Spread.CalcEngine.Functions.Function();
AnnuityPV.prototype.evaluate = function(age, n, i) {
let pv = 0;
const v = 1 / (1 + i);
for (let t = 0; t < n; t++) {
pv += Math.pow(v, t) * SurvivalRate.prototype.evaluate(age, t);
}
return pv;
};
// ── SpreadJS 워크북 구성 ──
const spread = new GC.Spread.Sheets.Workbook(document.getElementById('spreadContainer'));
spread.addCustomFunction(new MortalityRate());
spread.addCustomFunction(new SurvivalRate());
spread.addCustomFunction(new AnnuityPV());
spread.options.tabStripVisible = true;
spread.suspendPaint();
// 시트 1: 보험료 산출
const calc = spread.getActiveSheet();
calc.name('보험료 산출');
calc.setColumnWidth(0, 200);
calc.setColumnWidth(1, 150);
calc.setColumnWidth(2, 80);
// 타이틀
calc.addSpan(0, 0, 1, 3);
calc.setValue(0, 0, '보험료 산출 시스템 (계리 모델 기반)');
const titleStyle = new GC.Spread.Sheets.Style();
titleStyle.font = 'bold 15px sans-serif';
titleStyle.foreColor = '#1565C0';
titleStyle.backColor = '#E3F2FD';
titleStyle.hAlign = GC.Spread.Sheets.HorizontalAlign.center;
titleStyle.vAlign = GC.Spread.Sheets.VerticalAlign.center;
calc.setStyle(0, 0, titleStyle);
calc.setRowHeight(0, 42);
// 입력 섹션 헤더
calc.addSpan(2, 0, 1, 3);
calc.setValue(2, 0, '▶ 계약 조건 입력');
calc.getCell(2, 0).backColor('#1565C0').foreColor('#fff').font('bold 12px sans-serif');
calc.setRowHeight(2, 30);
const inputs = [
['피보험자 나이 (세)', 40, '세'],
['보험 기간 (년)', 10, '년'],
['납입 기간 (년)', 10, '년'],
['가입 금액 (만원)', 5000, '만원'],
['예정 이율 (%)', 3, '%'],
];
inputs.forEach(([label, val, unit], i) => {
const r = 3 + i;
const bg = i % 2 === 0 ? '#EDE7F6' : '#F3E5F5';
calc.setValue(r, 0, label);
calc.setValue(r, 1, val);
calc.setValue(r, 2, unit);
calc.getCell(r, 0).backColor(bg);
calc.getCell(r, 1).backColor(bg).hAlign(GC.Spread.Sheets.HorizontalAlign.center).font('bold 12px sans-serif');
calc.getCell(r, 2).backColor(bg).foreColor('#888');
calc.setRowHeight(r, 28);
});
// 결과 섹션 헤더
calc.addSpan(9, 0, 1, 3);
calc.setValue(9, 0, '▶ 보험료 산출 결과');
calc.getCell(9, 0).backColor('#E65100').foreColor('#fff').font('bold 12px sans-serif');
calc.setRowHeight(9, 30);
// 계리 함수로 보험료 계산 (사망보험금 현가 ÷ 연금현가 = 순보험료 — 표준 수지상등 산식)
function DeathBenefitPV(age, n, i) {
// 정기보험 사망급부 기말 현가 합계: Σ v^(t+1) · tPx · qx+t
const v = 1 / (1 + i);
let pv = 0;
for (let t = 0; t < n; t++) {
const tPx = SurvivalRate.prototype.evaluate(age, t);
const qxT = MortalityRate.prototype.evaluate(age + t);
pv += Math.pow(v, t + 1) * tPx * qxT;
}
return pv;
}
const age = 40, n = 10, rate = 0.03, face = 5000;
const qx = MortalityRate.prototype.evaluate(age);
const nPx = SurvivalRate.prototype.evaluate(age, n);
const ax = AnnuityPV.prototype.evaluate(age, n, rate);
const deathPV = DeathBenefitPV(age, n, rate);
const netM = Math.round((face * 10000 * deathPV) / ax / 12);
const loadM = Math.round(netM * 0.25);
const grossM = netM + loadM;
const results = [
['위험률 MORTALITY(40)', (qx * 1000).toFixed(3) + '‰', false],
['생존율 SURVIVAL(40, 10)', (nPx * 100).toFixed(4) + '%', false],
['연금현가 ANNUITYPV(40, 10, 3%)', ax.toFixed(4), false],
['순보험료 (월납)', netM.toLocaleString() + '원', false],
['부가보험료 (순보험료 × 25%)', loadM.toLocaleString() + '원', false],
['영업보험료 (월납)', grossM.toLocaleString() + '원', true],
];
results.forEach(([label, val, isTotal], i) => {
const r = 10 + i;
const bg = isTotal ? '#FFF3E0' : (i % 2 === 0 ? '#F5F5F5' : '#FAFAFA');
calc.setValue(r, 0, label);
calc.setValue(r, 1, val);
calc.addSpan(r, 1, 1, 2);
calc.getCell(r, 0).backColor(bg).font(isTotal ? 'bold 12px sans-serif' : '11px sans-serif');
calc.getCell(r, 1).backColor(bg)
.hAlign(GC.Spread.Sheets.HorizontalAlign.right)
.font(isTotal ? 'bold 13px sans-serif' : '12px sans-serif')
.foreColor(isTotal ? '#E65100' : '#222');
calc.setRowHeight(r, 28);
});
// 시트 2: 위험률 테이블
const riskSheet = new GC.Spread.Sheets.Worksheet('위험률 테이블');
spread.addSheet(1, riskSheet);
const rHeaders = ['나이 (세)', '위험률 qx (‰)', '생존율 px (%)', '사망자수 / 1만명'];
rHeaders.forEach((h, c) => {
riskSheet.setValue(0, c, h);
riskSheet.getCell(0, c).backColor('#1A237E').foreColor('#fff').font('bold 11px sans-serif').hAlign(GC.Spread.Sheets.HorizontalAlign.center);
riskSheet.setColumnWidth(c, 135);
});
riskSheet.setRowHeight(0, 34);
for (let idx = 0; idx < 20; idx++) {
const a = 30 + idx;
const qx = MortalityRate.prototype.evaluate(a);
const bg = idx % 2 === 0 ? '#E8EAF6' : '#FFFFFF';
riskSheet.setValue(idx + 1, 0, a);
riskSheet.setValue(idx + 1, 1, (qx * 1000).toFixed(3));
riskSheet.setValue(idx + 1, 2, ((1 - qx) * 100).toFixed(4));
riskSheet.setValue(idx + 1, 3, Math.round(qx * 10000));
for (let c = 0; c < 4; c++) riskSheet.getCell(idx + 1, c).backColor(bg).hAlign(GC.Spread.Sheets.HorizontalAlign.center);
if (a >= 45) riskSheet.getCell(idx + 1, 1).foreColor('#C62828').font('bold 11px sans-serif');
}
spread.setActiveSheetIndex(0);
spread.resumePaint();