本篇定位Excel 投资系列第 05 篇。本篇把持仓成本、市值、浮动盈亏这三个最常用、也最容易算错的指标讲透——含 3 种成本法对照、4 个真实踩坑案例、5 段可直接复用的 Excel 公式。你有没有过——赚了钱却算不清到底赚多少因为成本没摊匀、分红没算、手续费漏了这种糊涂盈亏会让你误判该不该卖。其实用几个关键公式Excel 就能实时算出持仓成本、当前市值和浮动盈亏。本文把公式逻辑拆开讲并点出最容易算错的地方让你的账本一清二楚。 目录一、持仓管理的核心指标黄金三角二、3 个核心公式含完整 Excel 实现2.1 持仓成本移动加权平均法2.2 当前市值2.3 浮动盈亏2.4 完整公式链路图三、3 种成本法深度对比含手算演示3.1 案例背景贯穿三节3.2 方法 A移动加权平均法推荐最常用3.3 方法 B先进先出法FIFO适合税务核算3.4 方法 C后进先出法LIFO激进税收筹划3.5 三种方法对比表四、4 个真实踩坑案例附修复方案坑 1忽略手续费 → 盈亏算偏 0.05%坑 2分红没调整 → 持仓虚假盈利坑 3送股没调整 → 数量对不上坑 4部分卖出算错成本 → 已实现盈亏失真五、条件格式给盈亏上色5.1 三色规则设置5.2 进阶自定义阈值六、避坑指南8 条血泪经验七、3 个公式的深度数学推导7.1 公式 1 推导移动加权平均成本7.2 公式 2 推导当前市值7.3 公式 3 推导浮动盈亏八、3 个公式的实战组合应用8.1 持仓仪表盘架构8.2 持仓盈亏率计算8.3 多 ETF 持仓汇总8.4 实战李先生的实时盯盘表九、5 大边界情况与应对9.1 情况 1分红除权9.2 情况 2拆分/合并9.3 情况 3现金分红9.4 情况 4红利再投9.5 情况 5跨市场 ETFQDII一、持仓管理的核心指标黄金三角核心认知持仓管理有三个黄金三角指标——持仓成本、当前市值、浮动盈亏。这三个不盯牢就像开车不看仪表盘。graph LR A[交易台账] -- B[持仓成本br/加权平均] A -- C[当前市值br/最新价×数量] B -- D[浮动盈亏br/市值−成本] C -- D style A fill:#FFD93D,color:#000 style D fill:#6BCB77,color:#fff幽默点 #1这三个指标像**“开车三件套”**——成本是出发地油表市值是当前位置盈亏是到目的地的距离。三个都不看那你开的是自动驾驶失控。指标公式本质你能从什么看持仓成本加权平均的地基决定你盈亏计算的基准当前市值最新价 × 数量决定你账户实时净资产浮动盈亏市值 − 成本决定你该不该卖的信号关键洞察三个指标里最容易算错的是持仓成本——因为它涉及买入时点、分红送股、手续费、分批补仓等 N 多场景。本文 70% 篇幅讲它。二、3 个核心公式含完整 Excel 实现2.1 持仓成本移动加权平均法最常用的标准公式 (SUMIFS(金额, 代码, [代码], 方向, 买) SUMIFS(手续费, 代码, [代码], 方向, 买)) / 当前持仓数量逻辑拆解把历史所有买入方向的金额加起来加上所有买入产生的手续费除以当前持仓数量注意不是历史总买入数量是当前还持有的数量为什么用移动加权平均每次新买入都会刷新单位成本相当于再摊薄一次卖出不刷新单位成本因为卖出的部分已经脱离持仓这就是移动加权的核心思想2.2 当前市值公式 当前持仓数量 × 最新价注意最新价用第 02 篇讲的 Power Query 自动拉每天自动更新。2.3 浮动盈亏公式 当前市值 − 持仓成本 当前持仓数量 × (最新价 − 单位成本)效率技巧盈亏率比绝对值更有意义盈亏率 (最新价 - 单位成本) / 单位成本 // 用 % 格式显示2.4 完整公式链路图graph LR A[SUMIFSbr/累计买入金额] -- B[ SUMIFSbr/累计手续费] B -- C[÷ 当前持仓] C -- D[单位成本] E[最新价br/PQ 自动拉] -- F[× 持仓数量] F -- G[当前市值] D -- H[× 持仓数量] G -- H style H fill:#6BCB77,color:#fff三、3 种成本法深度对比含手算演示⚠️重点章节很多人只学移动加权平均一种方法但不同场景应该用不同方法。本节用具体数字演示 3 种算法的差异。3.1 案例背景贯穿三节假设你在某只股票上有以下交易日期方向价格数量手续费备注2024-01-15买入10.0010005首批建仓2024-03-20买入8.505005补仓2024-06-10卖出12.005005部分止盈2024-08-05买入9.208005再次补仓当前2024-08-10持有1800 股最新价11.503.2 方法 A移动加权平均法推荐最常用手算过程第 1 次买入1000 × 10.00 10,000加手续费 5 10,005成本 10.005/股 第 2 次买入在第 1 次成本上累计 累计成本 10,005 (500 × 8.50 5) 10,005 4,255 14,260 累计数量 1500成本 14,260 / 1500 9.507/股 第 3 次卖出卖出不影响单位成本卖出不刷权重 剩余成本 14,260剩余 1000 股那部分的成本 剩余数量 1000 第 4 次买入再次累计 累计成本 14,260 (800 × 9.20 5) 14,260 7,365 21,625 累计数量 1800成本 21,625 / 1800 12.014/股 ⚠️ 注意第 3 次卖出对应的那部分成本500 × 9.507 4753.5已经实现 卖出后不再算在持仓成本里Excel 实现单位成本 SUMIFS(金额, 代码, X, 方向, 买) / 当前持仓数量 21,620 / 1800 12.011 元/股当前市值 1800 × 11.50 20,700 元浮动盈亏 20,700 - 21,620 -920 元亏损 4.25%3.3 方法 B先进先出法FIFO适合税务核算核心思想卖出时假设先卖出最早买入的那批按这个逻辑算成本。手算过程第 3 次卖出 500 股时按 FIFO 假设卖的是第 1 次买入的 500/1000 → 已实现成本 500 × 10.005 5,002.5 → 已实现收入 500 × 12.00 6,000 → 已实现利润 997.5这部分要交税 当前持仓的剩余批次 - 第 1 次剩余500 × 10.005 5,002.5 - 第 2 次500 × 8.507 4,253.5 - 第 4 次800 × 9.207 7,365.5 总剩余成本 16,621.5 单位成本FIFO 16,621.5 / 1800 9.234 元/股和移动加权的差异FIFO 算出来 9.234加权算出来 12.011——差 30%为什么差异这么大因为 FIFO 把高成本批次先实现了剩余持仓的成本自然变低。同样 1800 股FIFO 看起来赚得多但其实只是把一部分利润挪到了已实现里。3.4 方法 C后进先出法LIFO激进税收筹划核心思想卖出时假设先卖出最近买入的批次。手算过程第 3 次卖出 500 股时实际买入时间序列1月、3月、6月卖出、8月再买 但 LIFO 假设卖的是假设的最新批次——这里需要虚拟批次 通常做法把所有买入按日期倒序排列卖出时优先扣减最近的批次 本例比较特殊中间没买就卖简单按批次扣减理解 卖出 500 → 优先从 8月5日买入的 800 股里扣 500 卖出成本 500 × 9.207 4,603.5 卖出收入 6,000 已实现利润 1,396.5利润更多因为成本更高 剩余持仓 - 第 1 次1000 × 10.005 10,005 - 第 2 次500 × 8.507 4,253.5 - 第 4 次剩余300 × 9.207 2,762.1 总剩余成本 17,020.6 单位成本LIFO 17,020.6 / 1800 9.456 元/股3.5 三种方法对比表维度移动加权平均FIFO先进先出LIFO后进先出单位成本12.0119.2349.456浮动盈亏-920亏4,078赚3,679赚已实现利润不显式计算997.51,396.5计算难度★★★★★★★★税务合规✓ A股/港股/美股通用✓ 通用⚠️ A股不可用适合场景个人日常盯盘会计核算海外税收筹划本系列建议个人投资者统一用移动加权平均法——它最简单、最稳健、最适合实时盯盘。FIFO/LIFO 留给会计核算。四、4 个真实踩坑案例附修复方案⚠️ 这 4 个坑90% 的个人投资者都踩过。看完能省下几千块糊涂账。坑 1忽略手续费 → 盈亏算偏 0.05%症状每次交易 5 元手续费单笔 1000 股的小单可能让成本率偏 0.5%。示例买入 1000 股 × 10 元 10,000 手续费 5 元 实际成本 10,005 单位成本 10.005不是 10.00 如果忽略手续费你会以为成本是 10.00等到 10.005 卖出时 你以为赚 0.05%实际是 -0.05%修复方案 (SUMIFS(金额, 代码, X, 方向, 买) SUMIFS(手续费, ...)) / 持仓数量加粗提醒永远把手续费加进成本——这是职业和业余的分水岭。坑 2分红没调整 → 持仓虚假盈利症状现金分红到账了成本没调账面看起来赚多了。示例持有 1000 股成本 10 元市值 1 万 现金分红每股 0.5 元到手 500 错误算法单位成本仍是 10浮盈 0 正确算法单位成本应调整为 9.510 - 0.5 9.5浮盈仍是 0但口径一致修复方案单位成本新 (原成本 × 原数量 − 分红总额) / 原数量坑 3送股没调整 → 数量对不上症状10 送 5 后持仓数量翻倍但成本没摊薄。示例原持仓1000 股 × 10 元 1 万 10 送 5 后1500 股 错误算法单位成本仍是 10市值 1.5 万账面赚 50% 正确算法单位成本调整为 6.66710 × 1000 / 1500账面盈亏 0修复方案单位成本新 原单位成本 × (原数量 / 新数量)坑 4部分卖出算错成本 → 已实现盈亏失真症状分批卖出时用总成本平均算导致单笔已实现盈亏不准。示例总成本10,005 持有 1000 股 第 1 次卖 500 股 12 元 错误算法已实现利润 500 × (12 - 10.005) 997.5对的 但如果你后续又买了 500 股 11 元总成本变成 15,510 这时如果再卖 500 股 12 元 错误算法还是按总成本 15,510 / 1500 10.34 算 正确算法需要按批次识别算已实现利润修复方案// 单笔已实现利润 卖出价 × 卖出数量 − 该笔对应的成本 // 推荐用辅助列批次号标记每笔买入五、条件格式给盈亏上色 这一节很短但很实用——学会让 Excel 自动给盈亏上色看一眼就知道持仓健康度。5.1 三色规则设置条件格式 → 色阶 → 三色 - 最低值深红-10% 以下 - 中位白0% - 最高值深绿10% 以上5.2 进阶自定义阈值条件格式 → 新建规则 → 使用公式确定要设置的单元格 公式 盈亏率 0.1 → 浅绿填充 公式 盈亏率 -0.05 → 浅红填充个人建议阈值按你的策略设定。比如你做长线可以 -10% 才红色做短线-3% 就得警觉。六、避坑指南8 条血泪经验编号经验适用人群1永远把手续费加进成本所有人2分红当天就调单位成本所有人3送股当天就调整数量 单位成本所有人4不要把已实现盈亏和浮动盈亏混算进阶5每年报税前导出一份已实现盈亏明细进阶6用辅助列标记批次号方便后期审计进阶7定期备份交易台账每月 1 次所有人8不要在持仓表里用公式算汇率要单独维护跨境投资实战经验第 1、2、3 条是基本功4-8 条是分水岭。能做到 6 条以上的投资者盈亏准确率能到 99.5% 以上。七、3 个公式的深度数学推导7.1 公式 1 推导移动加权平均成本移动加权像**「算外卖均价」**——第一单 20 元第二单 30 元平均不是 25简单平均而是 (2030)/2 25数量加权。数学定义平均成本 rac{\sum_{i1}^{n}(买入价_i × 数量_i)}{\sum_{i1}^{n}数量_i}Excel 实现// 移动加权平均成本公式 SUMIFS( 台账[真实成本列], 台账[代码列], [代码], 台账[方向列], 买入 ) / SUMIFS( 台账[数量列], 台账[代码列], [代码], 台账[方向列], 买入 )实战案例2024-01-15买入 1000 股 10 元 10000 元 50 元手续费 10050 2024-03-20买入 500 股 12 元 6000 元 30 元手续费 6030 平均成本 (10050 6030) / (1000 500) 16080 / 1500 10.72 元7.2 公式 2 推导当前市值数学定义当前市值持仓数量×当前价格当前市值 持仓数量 × 当前价格当前市值持仓数量×当前价格Excel 实现// 当前市值 [数量] × VLOOKUP([代码], 行情表!代码:最新价, ...)实战注意T1 数据今天看到的是昨天收盘价盘中数据用实时 API仅交易时间内有效市值像**「汽车里程表」**——显示当前位置但发动机在跑价格在变。每刷新一次数据市值就更新一次。7.3 公式 3 推导浮动盈亏数学定义浮动盈亏当前市值−持仓总成本浮动盈亏 当前市值 - 持仓总成本浮动盈亏当前市值−持仓总成本Excel 实现// 浮动盈亏 [当前市值] - SUMIFS( 台账[真实成本列], 台账[代码列], [代码], 台账[方向列], 买入 ) SUMIFS( 台账[卖出收入列], 台账[代码列], [代码], 台账[方向列], 卖出 )实战案例2024-01-15买入 1000 股 10 元 10000 元 2024-06-30当前价 12 元浮动盈亏 (12 × 1000) - 10000 2000 元 2024-07-15卖出 500 股 13 元 6500 元 - 已实现盈亏(13 - 10) × 500 1500 元 - 剩余 500 股浮动盈亏(13 - 10) × 500 1500 元按 13 元算 - 总盈亏1500 1500 3000 元浮动盈亏像**「水温计」**——实时显示水温盈亏但只是显示没卖就是浮云。只有平仓后才是真金白银。八、3 个公式的实战组合应用8.1 持仓仪表盘架构graph TB A[台账] -- B[持仓明细表] B -- C[公式 1br/平均成本] B -- D[公式 2br/当前市值] B -- E[公式 3br/浮动盈亏] C -- F[持仓仪表盘] D -- F E -- F style A fill:#FFD93D,color:#000 style F fill:#6BCB77,color:#fff8.2 持仓盈亏率计算// 盈亏率百分比 盈亏率 [浮动盈亏] / [持仓成本] // 盈亏率百分点显示 TEXT(盈亏率, 0.00%)盈亏率分档着色盈亏率颜色含义 20%深绿大幅盈利5% ~ 20%浅绿盈利-5% ~ 5%黄色持平-20% ~ -5%浅红亏损 -20%深红巨亏盈亏率分档像**「红绿灯」**——绿灯赚继续走、黄灯持平减速、红灯亏停车检查。8.3 多 ETF 持仓汇总// 持仓总市值 SUM(持仓表[当前市值]) // 持仓总盈亏 SUM(持仓表[浮动盈亏]) // 整体盈亏率 持仓总盈亏 / SUM(持仓表[持仓成本]) // 单只 ETF 权重 [当前市值] / SUM(持仓表[当前市值])8.4 实战李先生的实时盯盘表graph LR A[9:30br/开盘] -- B[拉昨日收盘] B -- C[持仓表更新] C -- D[计算盈亏] D -- E{触达止损线?} E --|是| F[红色预警] E --|否| G[绿色正常] style F fill:#FF6B6B,color:#fff style G fill:#6BCB77,color:#fff李先生的盯盘节奏时间操作工具9:00拉昨日收盘PQ 自动9:30-11:30盘中监控实时数据12:00中午复盘仪表盘14:00-15:00下午监控实时数据15:30收盘汇总仪表盘九、5 大边界情况与应对9.1 情况 1分红除权症状分红日股价下跌持仓成本虚增。正解// 除权日调整成本 调整后成本 原成本 × (1 - 分红率) // 例每股分红 0.5 元原价 10 元 // 调整后成本 10 × (1 - 0.5/10) 9.59.2 情况 2拆分/合并症状1→2 拆分后股价减半账面显示亏损。正解// 拆分日调整 调整后数量 原数量 × 拆分比例 调整后成本 原成本 / 拆分比例 // 例1→2 拆分1000 股 20 元 // 调整后2000 股 10 元成本总额不变9.3 情况 3现金分红症状账户收到现金但持仓数没变。正解// 现金分红不改变持仓成本只减少账户现金 // 但已实现收益会增加现金到账9.4 情况 4红利再投症状分红自动买入更多份额。正解// 视为一次新的买入 分红金额 持仓数量 × 每股分红 买入价 分红日收盘价 新增份额 分红金额 / 买入价 - 手续费9.5 情况 5跨市场 ETFQDII症状纳斯达克 100513100受汇率影响。正解// 加上汇率折算 人民币市值 美元市值 × 汇率边界情况像**「驾照考试的难题」**——直线行驶你都会普通买卖但侧方停车拆分、倒车入库分红很多人挂科。提前了解规则才能稳过。十、避坑指南10.1 坑 1用错了成本概念症状把现价当成本算出假盈亏。正解严格区分平均成本 vs 当前价 vs 真实成本。10.2 坑 2忽略手续费症状看着赚了实际没赚。正解每笔交易都计入手续费重新算真实成本。10.3 坑 3忽略税症状卖出赚了 1000但扣税后只赚了 800。正解印花税 0.1%、红利税 10-20%——加进真实成本。10.4 坑 4复利效应低估症状分红再投赚的少复利没体现。正解长期视角——20 年复利可能是单利的 3 倍。10.5 坑 5心理账户症状赚 1000 和亏 1000 心理感受不同。正解统一看待所有盈亏不双标。 文末三件套【模板下载】实时盯盘模板含 PQ 自动更新 条件格式已上传 CSDN 资源关注此系列获取后续更新后台回复「excel投资」获取下载链接。【思考题】你的持仓现在有几只能不能建一个实时盯盘表【下篇预告】下一篇06 历史价格数据怎么整理4 步归一化让你的 K 线分析不再各说各话。标签#持仓管理#成本计算#实时盯盘#盈亏分析#投资公式#Excel实战#移动加权SEO 关键词移动加权成本、持仓盈亏计算、Excel 实时盯盘