Excel理财应用17-风险指标只看波动率够吗？最大回撤索提诺比率 4 公式帮你看穿，4 个风险评估公式 + 真实组合案例

基金定投助手为什么你的基金定投总在追涨杀跌价值平均法定投引擎 综合估值模型动态再平衡仓位管理一个单文件 HTML 的免费定投工具-CSDN博客https://download.csdn.net/download/weitingfu/93339607?spm1011.2124.3001.6210本篇定位Excel 投资系列第 17 篇。波动率只是风险的入门级指标——本文讲 4 个进阶风险指标最大回撤、索提诺比率、VaR、Calmar含完整 Excel 公式 真实组合实测。 黄金 100 字你有没有过——一个 ETF 波动率不高年化 15%但最大回撤能到 -40%另一个波动率更高25%回撤反而小。波动率只是风险的平均但你真正怕的是极端——本文讲 4 个进阶风险指标最大回撤、索提诺比率、VaR、Calmar配合真实组合案例让你看清哪些风险是真正的杀手。 本文导航目录 黄金 100 字 本文导航一、为什么只看波动率不够1.1 波动率的 3 大盲区1.2 真实案例2 个 ETF 的虚假安全二、4 个进阶风险指标详解2.1 指标 1最大回撤Max Drawdown2.2 指标 2索提诺比率Sortino Ratio2.3 指标 3VaRValue at Risk2.4 指标 4Calmar 比率三、Excel 公式完整实现含手算演示3.1 最大回撤公式3.2 索提诺比率公式3.3 VaR 公式3.4 Calmar 公式3.5 完整链路图四、4 个指标的实战选择4.1 指标选择决策树4.2 不同场景的指标选择4.3 4 个指标的优劣对比五、真实组合案例5 个组合对比5.1 5 个测试组合5.2 风险-收益散点5.3 关键洞察六、风险预警阈值量化你的睡不着标准6.1 个人风险承受度评估6.2 风险承受度对应的指标阈值6.3 个人持仓风险评估公式七、4 大风险指标的完整推导7.1 指标 1最大回撤7.2 指标 2下行波动率7.3 指标 3索提诺比率7.4 指标 4卡尔玛比率八、4 大指标的实战组合8.1 完整风险评估表8.2 不同投资者的选择8.3 4 指标加权综合分九、实战案例危机中的 ETF 表现9.1 2022 年熊市表现9.2 回撤持续时间9.3 实战启示十、避坑指南10.1 坑 1只看波动率不看回撤10.2 坑 2忽视下行波动10.3 坑 3回撤时间忽略10.4 坑 4风险指标单一10.5 坑 5历史回撤代表未来十一、4 大指标的实战组合应用11.1 风险调整收益矩阵11.2 不同市场环境的指标选择11.3 4 指标综合评分十二、4 大指标的实战误用12.1 误用 1只看夏普选基12.2 误用 2用错基准12.3 误用 3时间窗口太短十三、实战案例熊市 ETF 风险表现13.1 2022 年熊市 4 指标对比13.2 实战启示十四、风险预算与回撤控制14.1 个人风险预算14.2 动态风险调整14.3 回撤恢复的实战建议一、为什么只看波动率不够核心认知波动率衡量的是平均波动但你真正关心的其实是最坏情况。1.1 波动率的 3 大盲区盲区例子不区分涨跌暴涨和暴跌的波动率一样不反映极端-5% 和 5% 都被平均掉不反映时间2015 年股灾和平时波动率可能接近波动率像**“汽车的平均速度”**——平均 60 km/h但可能有过 200 km/h暴涨和 0 km/h停盘。最大回撤才是急刹车距离。1.2 真实案例2 个 ETF 的虚假安全ETF年化波动率最大回撤夏普510300沪深 30018.5%-32.5%0.34511010国债 ETF3.2%-5.5%1.85问题511010 波动率仅 3.2%看起来很稳但回撤也有 -5.5%——对于纯债基金已经是较大回撤了。关键洞察波动率适合横着比跨品种最大回撤适合竖着比同一品种不同时期。二、4 个进阶风险指标详解2.1 指标 1最大回撤Max Drawdown定义从历史最高点到最低点的跌幅。公式最大回撤 max(1 - 当日净值 / 历史最高净值)示例净值序列 1.0, 1.1, 1.2, 0.9, 1.0, 1.1历史最高1.2 最低0.9 最大回撤 1 - 0.9/1.2 25%2.2 指标 2索提诺比率Sortino Ratio定义只考虑下行风险的夏普比率。公式索提诺 (年化收益 - 无风险利率) / 下行波动率下行波动率只算下跌日收益的标准差。2.3 指标 3VaRValue at Risk定义在给定置信水平下的最大可能亏损。公式参数法VaR -(μ - z × σ) × 投资金额其中μ 日均收益σ 日收益标准差z 置信水平对应的 z 值95% 1.64599% 2.326示例日均收益 0.05%日标准差 1.5%投资 100 万VaR(95%) -(0.0005 - 1.645 × 0.015) × 1,000,000 -(0.0005 - 0.0247) × 1,000,000 24,200 元含义95% 置信度下单日最大亏损不超过 24,200 元。2.4 指标 4Calmar 比率定义年化收益 / 最大回撤的绝对值。公式Calmar 年化收益 / |最大回撤|示例年化收益 6.3%最大回撤 -32.5%Calmar 6.3% / 32.5% 0.19三、Excel 公式完整实现含手算演示3.1 最大回撤公式// 假设净值序列在 C 列 MIN(1 - C2:C1000 / MAX_EXPAND(C2:C1000)) // 或用辅助列更稳定 辅助列 1 - C2 / MAX($C$2:C2) 最大回撤 MIN(辅助列) // 单格公式不需辅助列 MIN(IF(ROW(C2:C1000)2, 1 - C2:C1000 / MAX(OFFSET($C$2, 0, 0, ROW(C2:C1000)-1, 1)), 0)) // CtrlShiftEnter3.2 索提诺比率公式// 下行波动率 SQRT(SUMPRODUCT((IF(D2:D10000, D2:D1000, 0))^2) / COUNT(D2:D1000)) * SQRT(250) // 索提诺 (年化收益 - 0.025) / 下行波动率注需要 CtrlShiftEnter数组公式3.3 VaR 公式// 参数法 VaR -(AVERAGE(D2:D1000) - 1.645 * STDEV(D2:D1000)) * 投资金额 // 95% 置信度 -(AVERAGE(D2:D1000) - 1.645 * STDEV(D2:D1000)) * 1000000 // 99% 置信度 -(AVERAGE(D2:D1000) - 2.326 * STDEV(D2:D1000)) * 10000003.4 Calmar 公式 年化收益 / ABS(最大回撤) // 例6.3% / 32.5% 0.063 / 0.3253.5 完整链路图graph TB A[净值序列] -- B[最大回撤] A -- C[日收益] C -- D[下行波动率] D -- E[索提诺] C -- F[均值/标准差] F -- G[VaR] A -- H[年化收益] H -- I[Calmar] B -- I style B fill:#FF6B6B,color:#fff style E fill:#6BCB77,color:#fff style G fill:#FFD93D,color:#000 style I fill:#9D4EDD,color:#fff四、4 个指标的实战选择4.1 指标选择决策树你关心什么 ├─ 最坏情况 → 最大回撤 ├─ 只看下行风险 → 索提诺 ├─ 单日最大亏损 → VaR └─ 综合年化 回撤 → Calmar4.2 不同场景的指标选择场景主指标辅助指标长线定投最大回撤夏普波段交易索提诺比率VaR大资金管理VaR最大回撤基金对比Calmar夏普4.3 4 个指标的优劣对比指标优点缺点最大回撤直观、能感同身受不反映恢复时间索提诺只看下行更公平需要正态分布假设VaR量化最坏情况不反映极端尾部Calmar综合年化和回撤不反映波动五、真实组合案例5 个组合对比5.1 5 个测试组合组合配置年化波动率最大回撤VaR(95%)CalmarA - 沪深 300100% 沪深 300 ETF18.5%-32.5%24,2000.19B - 60/4060% 沪深300 40% 债券12.5%-18.5%16,5000.42C - 行业轮动5 大行业 ETF 各 20%22.5%-38.5%29,5000.15D - 全球配置40% A 股 30% 美股 30% 债券10.5%-15.5%13,8000.48E - 全债100% 债券 ETF3.2%-5.5%4,2000.855.2 风险-收益散点Calmar ↑ 高好 0.85 ● E 0.48 ● D 0.42 ● B 0.19 ● A 0.15 ● C ↓ 低差5.3 关键洞察E 组合 Calmar 最高0.85——风险调整后回报最好D 组合最稳定——波动率 10.5%回撤 -15.5%C 组合风险最高——波动率 22.5%回撤 -38.5%A 组合是基准——多数人的选择核心洞察单看波动率A 和 C 都高但 C 的最大回撤比 A 多 6 个百分点——这是 Calmar 比率的意义。六、风险预警阈值量化你的睡不着标准6.1 个人风险承受度评估问题你的回答亏损 20% 你会失眠吗是 → 保守否 → 中等无所谓 → 激进亏损 50% 你会割肉吗是 → 保守否 → 中等无所谓 → 激进你的投资期限是 1 年 → 保守1-5 年 → 中等 5 年 → 激进6.2 风险承受度对应的指标阈值风险承受最大回撤年化波动率VaR(95%)保守 10% 8% 1.5 万中等 25% 18% 3 万激进 40% 30% 5 万6.3 个人持仓风险评估公式// 自动评估 风险评估 IF( 最大回撤 0.10, 保守型适合, IF(最大回撤 0.25, 中等型适合, IF(最大回撤 0.40, 激进型适合, 风险过高)) )七、4 大风险指标的完整推导7.1 指标 1最大回撤最大回撤像**「跳伞落地最猛的一次」**——即使整体是上升的赚的你中途可能砸过地回撤。数学公式最大回撤 \max_{t}( rac{峰值_t - 谷值_t}{峰值_t})Excel 实现// 1. 计算历史最高价滚动最大值 历史最高 MAX(MAX($B$2:B2), B2) // 2. 计算回撤 回撤 (历史最高 - 当前价) / 历史最高 // 3. 最大回撤 最大回撤 MAX(回撤范围)实战解释时间轴 ━━╲ ╱━━━━ 峰值 A B 谷值 C 最大回撤(A - C) / A最大回撤像**「蹦极绳子的最大弹性」**——蹦得越低绳子拉得越猛回撤越大绳子质量心理承受就越重要。7.2 指标 2下行波动率下行波动率像**「只算跌的幅度」**——涨的时候无所谓跌的时候才重要。数学公式下行波动率 \sqrt{ rac{\sum_{r_i 0}(r_i)^2}{n_{负收益}}}Excel 实现// 只用负收益计算波动率 下行收益 IF(日收益 0, 日收益, 0) 下行波动率 STDEV.S(下行收益) * SQRT(250)7.3 指标 3索提诺比率索提诺比率像**「只计坏事的考试评分」**——做好事涨不加分做坏事跌扣分。数学公式索提诺 rac{年化收益 - 无风险利率}{下行波动率}Excel 实现索提诺 (年化收益 - 0.03) / 下行波动率关键阈值索提诺评价 1.5优秀1.0-1.5良好0.5-1.0一般 0.5较差7.4 指标 4卡尔玛比率卡尔玛比率像**「用回撤做分母的夏普」**——同样收益回撤小就更优。数学公式卡尔玛 rac{年化收益}{最大回撤}Excel 实现卡尔玛 年化收益 / 最大回撤八、4 大指标的实战组合8.1 完整风险评估表指标沪深 300中证 500创业板 50年化收益8.5%12.3%18.6%波动率16%22%28%下行波动率11%15%20%最大回撤18%25%35%夏普0.530.560.66索提诺0.750.780.92卡尔玛0.470.490.538.2 不同投资者的选择4 指标像**「买车 4 维度」**——外观最大回撤、油耗波动率、动力夏普、安全索提诺。不同人侧重不同。保守投资者最大回撤 15%下行波动率 10%稳健投资者最大回撤 25%夏普 0.5积极投资者卡尔玛 0.5索提诺 0.88.3 4 指标加权综合分风险综合分 (最大回撤分 * 0.4 下行波动率分 * 0.3 索提诺分 * 0.2 卡尔玛分 * 0.1) 其中 最大回撤分 (1 - 最大回撤/0.3) * 100 下行波动率分 (1 - 下行波动率/0.2) * 100 索提诺分 MIN(索提诺/1.5 * 100, 100) 卡尔玛分 MIN(卡尔玛/1 * 100, 100)九、实战案例危机中的 ETF 表现9.1 2022 年熊市表现graph TB A[2022 年熊市] -- B1[沪深300br/回撤 22%] A -- B2[中证500br/回撤 28%] A -- B3[创业板50br/回撤 38%] A -- B4[国债ETFbr/回撤 1%] style A fill:#FFD93D,color:#000 style B4 fill:#6BCB77,color:#fff style B3 fill:#FF6B6B,color:#fff9.2 回撤持续时间回撤持续时间像**「伤口愈合期」**——跌得越深回撤越大愈合时间越长。ETF最大回撤恢复时间沪深 30022%6 个月中证 50028%9 个月创业板 5038%14 个月9.3 实战启示// 1. 风险承受能力匹配 最大回撤 你能承受的最大亏损 // 2. 心理建设 你能在亏损 30% 时不卖出吗 // 3. 资产配置 高回撤 ETF 配小比例 低回撤 ETF 配大比例熊市表现像**「汽车碰撞测试」**——平时跑得快的车高收益碰撞时可能损失更惨高回撤。真正的好车是碰撞也稳的。十、避坑指南10.1 坑 1只看波动率不看回撤症状波动率 15% 看着不错结果单次回撤 35%。正解波动率 回撤双看。10.2 坑 2忽视下行波动症状用整体波动率评估下行风险。正解用索提诺比率——只算下行。10.3 坑 3回撤时间忽略症状回撤 30% 但恢复快认为比回撤 20% 恢复慢的好。正解回撤 恢复时间双看——恢复期是机会成本。10.4 坑 4风险指标单一症状只看一个指标就下结论。正解4 指标综合——夏普看整体、索提诺看下、卡尔玛看回撤、最大回撤看最坏。10.5 坑 5历史回撤代表未来症状过去回撤 20%认为未来不会超过 20%。正解历史只是参考——黑天鹅事件回撤可能更大。十一、4 大指标的实战组合应用11.1 风险调整收益矩阵风险调整收益像**「汽车综合评分」**——光看速度收益不够还要看油耗风险、安全最大回撤。4 大指标的乘数效应收益 基础得分 夏普 收益 / 整体风险 索提诺 收益 / 下行风险 卡尔玛 收益 / 最大回撤11.2 不同市场环境的指标选择市场环境核心指标原因牛市夏普整体波动有意义熊市索提诺下行风险更重要震荡卡尔玛最大回撤最相关平稳信息比率vs 基准指标选择像**「看病挂什么科」**——发热夏普挂内科腿疼索提诺挂骨科。11.3 4 指标综合评分// 综合风险评分0-100越低越好 综合风险 (1 - 夏普/1.5) * 25 (1 - 索提诺/2) * 25 最大回撤 * 25 下行波动率 * 25 // 操作建议 IF(综合风险 30, 低风险, IF(综合风险 50, 中风险, IF(综合风险 70, 高风险, 极高风险)))十二、4 大指标的实战误用12.1 误用 1只看夏普选基症状夏普 0.5 vs 0.8但夏普 0.5 的产品回撤 30%。正解夏普 回撤双看。12.2 误用 2用错基准症状沪深 300 ETF vs 中证 500 比 IR基准不对。正解同类相比——指数 ETF vs 指数基准。12.3 误用 3时间窗口太短症状3 个月数据算夏普。正解至少 3 年。十三、实战案例熊市 ETF 风险表现13.1 2022 年熊市 4 指标对比graph TB A[2022 年熊市] -- B1[沪深300br/夏普 0.3br/索提诺 0.4br/回撤 22%] A -- B2[中证500br/夏普 0.2br/索提诺 0.3br/回撤 28%] A -- B3[创业板50br/夏普 0.1br/索提诺 0.2br/回撤 38%] A -- B4[国债ETFbr/夏普 1.5br/索提诺 2.0br/回撤 1%] style A fill:#FFD93D,color:#000 style B4 fill:#6BCB77,color:#fff style B3 fill:#FF6B6B,color:#fff13.2 实战启示// 1. 资产配置应对熊市 熊市防御 国债 ETF 30% 沪深 300 30% 现金 40% // 2. 跨市场分散 跨市场组合 沪深 300 标普 500 日经 225 // 3. 风险预算 最大可承受回撤 投资者年龄 资产规模熊市像**「暴风雨天出门」**——你穿雨衣国债 ETF打伞沪深 300少出门减仓。多重防御才能不湿身。十四、风险预算与回撤控制14.1 个人风险预算风险预算像**「家庭开支预算」**——吃饭 30%核心、娱乐 10%卫星、储蓄 30%稳健、应急 30%现金。风险预算公式// 总资产 100 万 // 风险预算 (1 - 最大回撤) × 总资产 // 最大回撤 20% → 风险预算 80 万14.2 动态风险调整// 根据年龄调整 风险预算占比 1 - (年龄 / 100) // 30 岁70% 风险资产 // 50 岁50% 风险资产 // 70 岁30% 风险资产 // 根据市场调整 牛市风险预算上调 10% 熊市风险预算下调 20% 震荡保持不变14.3 回撤恢复的实战建议// 1. 加仓节奏 回撤 10% → 加仓 10% 回撤 20% → 加仓 20% 回撤 30% → 加仓 30% // 2. 不加仓的条件 当前仓位 80%不加仓 距上次加仓 1 月等 // 3. 减仓条件 回撤恢复 10%减仓 5% 回撤恢复 20%减仓 10% 补充说明4 指标的局限性4 指标像**「4 位医生」**——每个医生看一个角度但都不全面。夏普忽略下行分布索提诺忽略极端回撤卡尔玛只看最大回撤不看频率最大回撤不看恢复时间结论4 指标结合不依赖任何单一指标。实战进阶压力测试// 假设极端情况 压力场景1 沪深 300 跌 30% 压力场景2 利率上升 1% 压力场景3 汇率下跌 10% // 组合在这些场景下的回撤 压力回撤 SUMPRODUCT(权重, 各ETF压力回撤)压力测试像**「消防演习」**——平时不知道哪里有漏洞演习才能发现。至少季度做一次压力测试。 文末三件套【模板下载】风险指标完整计算模板含 4 公式 综合评分 危机测试已上传 CSDN 资源关注此系列获取后续更新后台回复「excel投资」获取下载链接。【思考题】你目前持仓的最大回撤是多少心理能承受吗【下篇预告】下一篇18 现代投资组合 MPT 怎么落地协方差矩阵 规划求解 Excel 实战。标签#最大回撤#索提诺#下行波动率#卡尔玛#风险预算#压力测试#熊市防御SEO 关键词风险指标综合、回撤控制策略、压力测试 Excel