跳到主要内容

Excel财务函数应用

本章概览​

Excel内置了50多个财务函数,涵盖投资分析、贷款计算、折旧计算、债券定价等领域。掌握这些函数对财务分析、投资决策具有重要意义。

学习目标

  • 掌握贷款相关函数(PMT/PPMT/IPMT)
  • 学会投资回报分析(NPV/IRR/XIRR)
  • 熟练使用折旧函数(SLN/DB/DDB)
  • 理解债券和证券函数应用
  • 掌握财务场景实战应用

25.1 贷款计算函数​

25.1.1 PMT函数(月供计算)​

语法

=PMT(利率, 期数, 现值, [终值], [类型])

参数说明

  • 利率:每期利率
  • 期数:总期数
  • 现值:贷款本金(负数)
  • 终值:最后一笔支付后的余额(默认0)
  • 类型:0期末付款(默认),1期初付款

示例:房贷月供

贷款本金:100万元
年利率:4.9%
期限:30年

=PMT(4.9%/12, 30*12, -1000000)
→ ¥5,307.27

注意:
- 年利率需除以12转为月利率
- 年数需乘以12转为月数
- 本金用负数(贷出去的钱)

按月/季/年付款

# 月供
=PMT(年利率/12, 年数*12, -本金)

# 季供
=PMT(年利率/4, 年数*4, -本金)

# 年供
=PMT(年利率, 年数, -本金)

25.1.2 PPMT函数(本金偿还)​

语法

=PPMT(利率, 期, 期数, 现值, [终值], [类型])

示例:第12期偿还本金

=PPMT(4.9%/12, 12, 30*12, -1000000)
→ -¥3,893.42

含义:第12个月还款中,偿还本金3893.42元

累计本金偿还

# 第1年累计偿还本金
=CUMPRINC(4.9%/12, 30*12, 1000000, 1, 12, 0)

25.1.3 IPMT函数(利息偿还)​

语法

=IPMT(利率, 期, 期数, 现值, [终值], [类型])

示例:第12期偿还利息

=IPMT(4.9%/12, 12, 30*12, -1000000)
→ -¥1,413.85

验证:PMT = PPMT + IPMT
5307.27 = 3893.42 + 1413.85 ✓

累计利息偿还

# 第1年累计支付利息
=CUMIPMT(4.9%/12, 30*12, 1000000, 1, 12, 0)

25.1.4 贷款还款表制作​

表格结构

   A      B          C          D          E          F
1  期数   期初余额   月供       本金       利息       期末余额
2  1    1000000   5307.27    3893.42    1413.85    996106.58
3  2     996106.58 5307.27   3909.34    1397.93    992197.24
...

公式

B2: =1000000  (初始本金)
C2: =PMT($H$1/12,$H$2*12,-$H$3)  (月供)
D2: =PPMT($H$1/12,A2,$H$2*12,-$H$3)  (本金)
E2: =IPMT($H$1/12,A2,$H$2*12,-$H$3)  (利息)
F2: =B2-D2  (期末余额)

B3: =F2  (上期期末余额)
向下复制到360行(30年)

其中:
H1: 年利率 4.9%
H2: 期限(年) 30
H3: 本金 1000000

可视化

插入图表:堆积面积图
- 下部:累计本金偿还
- 上部:累计利息支付

观察:前期利息占比大,后期本金占比大

25.2 投资分析函数​

25.2.1 NPV函数(净现值)​

概念 将未来现金流折现到现在的价值

语法

=NPV(折现率, 值1, [值2], ...)

示例:项目投资评估

初始投资:100万(第0年)
第1年现金流:30万
第2年现金流:40万
第3年现金流:50万
第4年现金流:40万
折现率:10%

=NPV(10%, 30, 40, 50, 40) - 100
→ 22.97万

结论:NPV>0,项目可行

注意事项

# 错误示范
=NPV(10%, -100, 30, 40, 50, 40)  # 错误!

# 正确示范
=NPV(10%, 30, 40, 50, 40) - 100

原因:NPV假设第一笔现金流在第1期末
初始投资在第0期(当前),需单独减去

现金流不均匀

使用数组:
=NPV(10%, A1:A10) + B1
其中B1为初始投资(负数)

25.2.2 IRR函数(内部收益率)​

概念 使NPV=0的折现率

语法

=IRR(值, [猜测值])

示例

现金流:
年份   金额
0     -100
1       30
2       40
3       50
4       40

=IRR(A1:A5)
→ 21.59%

含义:该项目的年化收益率为21.59%

决策准则

IRR > 折现率(资金成本) → 可行
IRR < 折现率 → 不可行

示例:IRR=21.59% > 10% → 项目可行

猜测值

=IRR(A1:A5, 10%)

用途:当有多个IRR或计算不收敛时
提供猜测值帮助Excel找到正确结果

25.2.3 XIRR函数(不规则IRR)​

应用场景 现金流发生时间不规则

语法

=XIRR(值, 日期, [猜测值])

示例:基金定投

日期        现金流
2024-01-15  -10000  (投入)
2024-03-20  -10000  (投入)
2024-06-10  -10000  (投入)
2024-12-31   32000  (赎回)

=XIRR(B1:B4, A1:A4)
→ 18.32%

含义:考虑时间因素的实际年化收益率

与IRR对比

  • IRR:假设现金流等间隔
  • XIRR:现金流可以任意日期

25.2.4 XNPV函数(不规则NPV)​

语法

=XNPV(折现率, 值, 日期)

示例

折现率:10%
日期        现金流
2024-01-01  -100000
2024-06-15    30000
2024-09-20    40000
2025-03-10    50000

=XNPV(10%, B1:B4, A1:A4)
→ 结果考虑了实际日期间隔

25.3 折旧计算函数​

25.3.1 SLN函数(直线折旧)​

概念 每年折旧额相等

语法

=SLN(成本, 残值, 使用年限)

示例

固定资产原值:10万
残值:1万
使用年限:5年

=SLN(100000, 10000, 5)
→ 18000元/年

计算:
年折旧额 = (成本-残值) / 使用年限
         = (100000-10000) / 5
         = 18000

折旧表

年份  年初账面  折旧额   累计折旧  年末账面
1    100000   18000    18000    82000
2     82000   18000    36000    64000
3     64000   18000    54000    46000
4     46000   18000    72000    28000
5     28000   18000    90000    10000

25.3.2 DB函数(固定余额递减)​

语法

=DB(成本, 残值, 使用年限, 期间, [月份])

示例

=DB(100000, 10000, 5, 1)  # 第1年折旧
→ 36900元

=DB(100000, 10000, 5, 2)  # 第2年折旧
→ 23268元

特点:前期折旧多,后期折旧少

25.3.3 DDB函数(双倍余额递减)​

语法

=DDB(成本, 残值, 使用年限, 期间, [因子])

示例

=DDB(100000, 10000, 5, 1, 2)  # 第1年
→ 40000元

=DDB(100000, 10000, 5, 2, 2)  # 第2年
→ 24000元

折旧率 = 2 / 使用年限 = 2/5 = 40%
第1年: 100000 × 40% = 40000
第2年: (100000-40000) × 40% = 24000

月度折旧

=DDB(100000, 10000, 5*12, 1, 2)  # 第1个月
使用年限改为月数(5*12=60月)

25.3.4 SYD函数(年数总和)​

语法

=SYD(成本, 残值, 使用年限, 期间)

示例

=SYD(100000, 10000, 5, 1)  # 第1年
→ 30000元

计算:
年数总和 = 5+4+3+2+1 = 15
第1年折旧率 = 5/15
第1年折旧额 = (100000-10000) × 5/15 = 30000

第2年: 90000 × 4/15 = 24000
第3年: 90000 × 3/15 = 18000

25.4 证券和债券函数​

25.4.1 PRICE函数(债券价格)​

语法

=PRICE(结算日, 到期日, 年利率, 收益率, 赎回价值, 频率, [基准])

示例

=PRICE("2024-01-01","2029-01-01",5%,4%,100,2,1)
→ 104.49

含义:
- 票面利率5%,每半年付息一次
- 市场收益率4%
- 债券价格104.49元(面值100元)

参数说明

频率:
1 = 年付息
2 = 半年付息
4 = 季付息

基准:
0或省略 = 30/360
1 = 实际/实际
2 = 实际/360
3 = 实际/365

25.4.2 YIELD函数(债券收益率)​

语法

=YIELD(结算日, 到期日, 年利率, 价格, 赎回价值, 频率, [基准])

示例

=YIELD("2024-01-01","2029-01-01",5%,104.49,100,2,1)
→ 4.00%

含义:以104.49价格买入,持有到期的年化收益率为4%

25.4.3 ACCRINT函数(应计利息)​

语法

=ACCRINT(发行日, 起息日, 结算日, 年利率, 面值, 频率, [基准])

示例

=ACCRINT("2024-01-01","2024-01-01","2024-06-30",5%,100,2,1)
→ 2.49

含义:半年累计利息2.49元

25.5 实战案例​

案例1:购房vs租房决策​

场景 房价200万,首付60万,贷款140万 年利率4.9%,期限30年 租金3000元/月,年涨幅3%

购房总成本

首付: 600000
月供: =PMT(4.9%/12,30*12,-1400000) = 7430元
30年总月供: 7430 × 360 = 2,674,800
总成本: 600000 + 2674800 = 3,274,800

租房总成本

第1年: 3000×12 = 36000
第2年: 3000×(1+3%)×12 = 37080
...
第30年: 3000×(1+3%)^29×12

使用FV函数计算:
=FV(3%, 30, -36000, 0, 1) × -1
→ 约170万

但需考虑首付投资收益:
60万投资30年,年化8%
=FV(8%, 30, 0, -600000)
→ 604万

结论:租房+投资更优

案例2:提前还贷计算​

场景 贷款100万,已还5年,剩余25年 月供5307元,考虑提前还款30万

方案对比

# 方案1:继续还款
剩余期数: 25×12 = 300
剩余总额: 5307×300 = 1,592,100

# 方案2:提前还30万,期限不变
新贷款余额: =PV(4.9%/12,300,-5307) - 300000
新月供: =PMT(4.9%/12,300,-新余额)

# 方案3:提前还30万,月供不变
新期限: =NPER(4.9%/12,-5307,新余额)

对比:节省利息、缩短年限

案例3:投资项目评估​

项目A

初始投资: 100万
现金流:
年1: 30万
年2: 35万
年3: 40万
年4: 35万
年5: 30万

项目B

初始投资: 100万
现金流:
年1: 20万
年2: 25万
年3: 30万
年4: 40万
年5: 50万

对比分析

# 项目A
NPV(10%): =NPV(10%,30,35,40,35,30)-100 = 25.79万
IRR: =IRR({-100,30,35,40,35,30}) = 21.36%

# 项目B
NPV(10%): =NPV(10%,20,25,30,40,50)-100 = 23.60万
IRR: =IRR({-100,20,25,30,40,50}) = 19.54%

结论:
- 若看重NPV → 选A
- 若看重回收期 → 选B(后期现金流大)
- 综合看IRR → 选A

案例4:固定资产折旧对比​

数据

设备原值: 50万
残值: 5万
使用年限: 10年

四种方法对比

      第1年   第2年   第3年   第4年   第5年
直线  45000  45000  45000  45000  45000
余额  92500  66375  47619  34156  24511
双倍  100000 80000  64000  51200  40960
年数  81818  73636  65455  57273  49091

分析

  • 直线法:简单,每年相等
  • 加速折旧(余额/双倍):前期多,利于纳税筹划
  • 年数总和:适中

选择依据

  • 技术更新快 → 加速折旧
  • 收入平稳 → 直线法
  • 税务筹划 → 加速折旧

25.6 财务函数综合应用​

25.6.1 房贷计算器​

输入参数

B1: 贷款金额 1000000
B2: 年利率 4.9%
B3: 期限(年) 30
B4: 还款方式 等额本息

计算结果

# 月供
=PMT(B2/12,B3*12,-B1)

# 总利息
=PMT(B2/12,B3*12,-B1)*B3*12-B1

# 第一期利息
=IPMT(B2/12,1,B3*12,-B1)

# 第一期本金
=PPMT(B2/12,1,B3*12,-B1)

# 第N年累计利息
=CUMIPMT(B2/12,B3*12,B1,起始期,结束期,0)

# 第N年累计本金
=CUMPRINC(B2/12,B3*12,B1,起始期,结束期,0)

25.6.2 投资回报计算器​

输入

初始投资: 100000
每月定投: 5000
投资年限: 10
年化收益率: 8%

计算

# 最终价值
=FV(8%/12, 10*12, -5000, -100000)
→ 1,121,997元

# 总投入
=100000 + 5000×10×12
=700000元

# 总收益
=1121997 - 700000
=421997元

# 收益率
=421997/700000
=60.28%

25.6.3 年金计算器​

退休规划

问题:
退休时需要多少钱,
才能每月领取1万,持续30年?

=PV(8%/12, 30*12, -10000)
→ 1,365,952元

含义:假设年化8%收益,
需要准备136.6万元

25.7 财务函数注意事项​

25.7.1 符号约定​

现金流方向

流出(支付): 负数
流入(收入): 正数

示例:
贷款本金: 负数(我借入)
月供: 负数(我支付)
投资成本: 负数(我支出)
投资回报: 正数(我收入)

25.7.2 期数和利率匹配​

常见错误

# 错误
=PMT(4.9%, 30, -1000000)
# 年利率配年数,结果是年供

# 正确
=PMT(4.9%/12, 30*12, -1000000)
# 月利率配月数,结果是月供

转换表

年→月: 年利率/12, 年数×12
年→季: 年利率/4, 年数×4
月→年: 月利率×12, 月数/12

25.7.3 类型参数​

0 vs 1

0或省略: 期末付款(大多数贷款)
1: 期初付款(租金等)

影响:
期初付款比期末付款少1期利息

本章小结​

核心函数速查

类别函数用途
贷款PMT等额还款额
贷款PPMT本金偿还额
贷款IPMT利息偿还额
贷款NPER计算期数
贷款RATE计算利率
投资NPV净现值
投资IRR内部收益率
投资XIRR不规则IRR
投资FV终值
投资PV现值
折旧SLN直线折旧
折旧DB固定余额递减
折旧DDB双倍余额递减
折旧SYD年数总和
证券PRICE债券价格
证券YIELD债券收益率

学习建议

  1. 理解财务概念再学函数
  2. 注意现金流方向(正负号)
  3. 期数和利率单位要匹配
  4. 多做实际案例练习
  5. 结合敏感性分析

下一步学习

  • 第26章:Power Query数据处理
  • 第27章:Power Pivot数据建模
  • 第28章:Excel VBA编程基础

思考练习​

  1. 制作一个房贷计算器,包含还款计划表
  2. 对比等额本息和等额本金两种还款方式
  3. 用IRR和NPV评估一个投资项目
  4. 制作公司固定资产折旧表
  5. 计算退休需要准备多少钱

练习提示

  • 验证计算结果的合理性
  • 使用数据有效性防止错误输入
  • 添加敏感性分析(利率变化影响)
  • 用图表可视化结果