如何在Excel/Google Sheets中为滚动夏普比率计算获取自动更新的无风险收益率

前提：你已完成TNX（10年期美债收益率）自动更新插件的配置，可在表格固定单元格获取最新的TNX年化数值

注意事项：TNX默认返回年化收益率，若使用日度收益计算夏普比率，需先转换为日度无风险利率，转换逻辑为：日无风险利率 = 年化TNX / 252，252为美股年平均交易日天数，可根据你统计的市场实际交易日调整。

以下方案默认将最新TNX年化数值存放在A1单元格，日度无风险利率计算结果存放在A2单元格。

Google Sheets 实现步骤 #

• 配置原始数据区：B列放交易日期，C列及之后列存放对应单只股票的日收盘价，单独列（示例用AA列）计算每日投资组合收益率，可根据你的持仓权重自定义计算逻辑，等权组合可直接用=AVERAGE(C2:Z2)，自定义权重组合用=SUMPRODUCT(权重单元格区域, 单只股票日涨跌幅区域)
• 计算日度无风险利率：在A2单元格输入公式=A1/100/252，若你的插件返回的TNX自带百分比格式（如显示为3.8%），则公式改为=A1/252
• 计算区间年化夏普比率：以过去1年（252个交易日）的收益数据为例，完整公式为=(AVERAGE(AA2:AA253)-A2)/STDEV.P(AA2:AA253)*SQRT(252)，若需计算非年化夏普比率，去掉最后的*SQRT(252)即可
• 批量多组合计算：所有组合的夏普比率计算统一引用A2的日度无风险利率即可，TNX每日更新后所有计算结果会自动同步刷新

Excel 实现步骤 #

• 配置原始数据区：和Google Sheets逻辑一致，先整理好交易日期、单只股票收盘价、每日组合收益率的对应列
• 计算日度无风险利率：在A2单元格输入公式=A1/100/252，插件返回百分比格式TNX的话公式改为=A1/252
• 计算区间年化夏普比率：过去1年数据的完整公式为=(AVERAGE(AA2:AA253)-A2)/STDEV.P(AA2:AA253)*SQRT(252)，使用样本标准差的话将STDEV.P改为STDEV.S即可
• 自动刷新配置：点击顶部「数据」选项卡，选择「全部刷新」-「刷新属性」，设置刷新频率为每日1次，可指定开盘后时间段刷新，确保TNX和股价数据为最新值
• 批量多组合计算：统一引用A2的无风险利率参数即可，无需重复计算无风险利率

内容的提问来源于stack exchange，提问作者Hearno