金融中都有哪些必须熟练掌握的 Excel 公式？都有哪些技巧和心得？

提示: 作者被禁止或删除内容自动屏蔽

吟风 · 2018-10-13 14:24:55

一个在读狗来试答一下，因为Master学的很多东西都是用Excel/Matlab作为基础

做PE/VC/M&A的，统称Corporate Finance，也是这一部分Excel用的最多。
1.NPV
简单易懂，重中之重。一个project值不值钱，通过DCF projection+NPV的公式就行。只需要算FCF就好，不用每个单独再进一步计算DCF，NPV公式帮你搞定。后续的sensitivity analysis/scenario analysis 也都是以此为出发点。
上图：

PS：感谢Professor的getformula公式，把所有公式都可视化了……（PPS:这个是他用VBA自己编写的）
这样做，follow up analysis就很好办了。
Break-even Analysis

这里我用的是solver，variable是natural oil price。
solver我就不说了，毕竟不是公式。

技巧心得：input只能是从year 1 开始的Cash Flow，因为会自动折算到Year 0。所以如果有初始花费，总的NPV应该是NPV(..)+intitial cost（负数）。
2.RAND()
产生一个0到1间的随机数。做simulation的基本公式。用VBA从数据中挑选进行simulation也使用。
上图：

assumption是假设variable 遵循某种特定的分布，然后用random shock 来估计每一次simulation 的variable value.FYI, 这里的分布是triangular distribution.
至于simulation我也不说了，毕竟是动用了data table，非公式不切题。
技巧心得：随机生成从X到Y之间的函数之类的扩展，这里就不展开了。
3. TREND
用已知数据来estimate所需数据，比如：Option Choice.在t=2的时候你无法知道t=3会发生什么，所以只能estimate t=3时候的option price，再折现到t=2进行比较，然后做出选择。
上图。

在F和G的11栏可以看到TREND。原始data是下图。

技巧心得：这是一个矩阵函数，记得输完公式按shift+ctrl再按enter；我的教授使用的X和X^2来estimate Y，比用X来estimate Y准确一些。

做券商的统称Investment Management,我学的课程一般不用Excel，用的是Matlab(虽然networking得知matlab根本不会用到....)
Quant的更是吧？感觉C++, Python/R/Stata 用的多些？这个我不知道……
希望抛砖引玉哈

侯瑞琪 · 2018-10-13 14:24:56

TVM货币时间价值：PV、FV、RATE、PMT、NPER

固定资产折旧：直线折旧SLN、年限总和SYD、固定余额递减DB、双倍余额递减DDB

公司金融类：NPV、IRR、MIRR，更复杂点儿的XNPV、XIRR

债券类：本金、利息 CUNPRINC、ACCRINT、ACCRINTM、CUMIPMT、COUPNUM；支付时间 COUPDAYBS、COUPDAYS、COUPDAYSNC、COUPNCD、COUPPCD；收益率 INTRATE、ODDLYIELD、TBILLEQ、TBILLPRICE、TBILLYIELD、YIELD、YIELDDISC、YIELDMAT；久期 DURATION、MDURATION；证券价格 DOLLARDE、DOLLARFR

Ps - 建议先了解公式的数理特征，这样带入值的选择上会有质的飞跃！
PPs - 函数涉及时间或者期数的选择，最好先画Timeline，套用公式不混乱.
PPPs - 推荐金融函数最好哒纸质教程：德州仪器（Texas Instruments）TI BA II 金融计算器——的说明书

祝题主诸事顺遂( ° °)

psychonerdspian · 2018-10-13 14:24:57

量化其实excel的使用不是很多，不过也还是有人用vba的。pivot table玩熟了，会写vba，算是基本功吧。solver是个好用的东西。类似的Excel其实有许多非常好的插件以及接口。

我不是很喜欢直接用NPV、IRR这些计算公式，这些简单但不小心就会犯错的东西我还是比较喜欢自己写公式算。用Excel公式的时候千万记清楚它是怎么算的。

还有就是像cumsum（累加）这种函数Excel没有自带，学学怎么用VBA写一个也是很简单很有用的。以及B-S公式，monte-carlo，二叉树等等。

Excel是可以直接scraping的，这也是个不错的技巧。

啊说了半天一个公式都没说。。除了上面说过的，觉得必要的函数还有*IF类的函数，COUNTIF，SUMIF，还有要好好掌握Excel的逻辑，AND、OR、NOT、FALSE、IF、TRUE这些。

vlookup不要在几十万条的数据里用，慢是一方面，它还有可能错，比如没有完全跑完的时候复制粘贴了，结果算出负的振幅什么的我也是醉了。。搭配vlookup使用的还有clean函数。

吱吱 · 2018-10-13 14:24:58

我做过credit和equity research，处理的多为财务和行业数据，可能比较低端，感觉并不需要特别高深的函数，键盘快捷键使用熟练更重要。最近处理一百多个国家的各种数据，觉得vlookup+match, sumproduct,sumifs等这些匹配函数很给力。当要处理大量的数据时，一定要想办法偷懒，不要手动，比如上excel home这个论坛提问找答案等。

Fred Hu · 2018-10-13 14:24:59

我觉得没有什么是“必须掌握”的。该掌握什么取决于你的position做的内容。

我平时用的最多的就是普通的table和pivot,再加上vlookup和macro，还有就是需要做各种图。

最最基本的就是各种快捷键，可以在谷歌搜一下excel reference sheet就可以了

Zak47 · 2018-10-13 14:25:00

1，DCF估值模型：

2，IRR内部收益率：

3，sumif：

4，vlookup：
从源数据里提取与A18项目对应的第九列的数据：VLOOKUP(A18,'Cresus BS FY15'!$A$12:$I$185,9,FALSE)（【第二象限，自需提取的数据开始】
5，if：
C13是空白则True：IF(LEN(A13)>3,IF(ISBLANK(C13),E13*G13,D13*G13),"")
6，Max-计算税值:
-MAX(L5*$G$6,0)

喵先生 · 2018-10-13 14:25:01

Index + Match
任何基于DCF的估值模型必备神器，尤其是project finance

Bingbing · 2018-10-13 14:25:02

数据狗来回答：
作为一个business analyst，处理big data我常用的是pivot table, conditional formatting, index match, index match match, vlookup, VBA,
现在公司正在转型使用SQL和SAS

慕雨柔 · 2018-10-13 14:25:03

Ctrl+Shift+N，自动展开 Array Formula，简直神器...不过可惜这个不是 Excel 自带的。

金融中都有哪些必须熟练掌握的 Excel 公式？都有哪些技巧和心得？

9 个回复