Mikel
[转载]新年快乐，介绍个简单的Excel理财工作的制作方法 – Killmyday – 博客园.
最近物价飞涨，得给钱找个保值的地方，朋友给我推荐了一个不错的楼盘投资保值，实 在不行自住也很舒服。这么一大笔的投资，为了验证一下保值的潜力，当然需要慎之又慎，小心的不能再小心。作决定前自然要估计一下收益如何，这种纷繁复杂的 计算，虽然每一步都是很简单的运算，但是很多步串起来，就有点顾此失彼了。因此就想到用Excel自己做一个分析工具，省去自己编程的麻烦，可能你也有类似的需求，就把制作方法介绍如下。
设计的目标：
1. 用户需要输入房子的单价、面积以及购买时间。
2. 用户需要输入首付和公积金贷款的总额，程序能够计算出每个月的还贷的压力。
3. 用户输入预计房子的出售时间以及出售时每平的单价，程序能够根据出售时间和单价的变化，计算出总利润和均摊到每个月的利润。
4. 最后，用户可以修改一些其他参数，例如商业贷款利率，公积金贷款利率，还款年限，出售房屋的手续费等。
自动计算利润的表格如下所示（可以通过修改第一列的每平售价和第一行的出售时间来分析盈亏）：
制作步骤：
1. 分析每月还贷压力，表格截图如下：
其中C2和D2分别是允许用户输入的购买单价和房屋面积，E2是购买时间—留着做出售时获利分析。
房屋的总价B2就是简单的：=C2 * D2
因为公积金贷款各地都有政策，而且都有贷款上限，所以我在D7这个单元格输入了公积金的贷款上限，逻辑是，能从公积金贷就从公积金贷，如果不能贷，就把D7这个值设置为0好了。
在B7里，就是（房屋总价–首付）和公积金贷款上限的最小值—如果公积金贷款能全部搞定，为什么要贷商业贷款呢？因此B7的公式就是：=MIN($D$7, $B$2-$B$5)
商业贷款B6的值就是剩下要贷款的总额了：=IF(B2-B5-B7>0, B2-B5-B7,0)
Excel自己带了等额本息还款方式的计算每月还贷的公式：PMT，PMT函数接受3个参数：
1) 第一个参数是月利率，比如说你的贷款年利率是5.22%的话，那月利率就是5.22%除以12。
2) 第二个参数是还款周期，以月份计算，即如果你的贷款是20年的话，那就应该是240 = 20 * 12。
3) 第三个参数是贷款的总额。
因此B10每个月商业贷款的还款是：=PMT($B$3/12, $B$8*12, $B$6)
而B9每个月公积金贷款的汇款是：=IF($B$7=0, 0, PMT($B$4/12, $B$8*12, $B$7))
还款总额就是将每个月的固定的还款乘以还款周期，例如B13的商业贷款总额是：=$B$10 * $B$8*12。
2. 根据指定的出售时间和价格，计算利润，表格截图如下：
根据最近出台的房屋出售营业税规定，如果是购买5年之内的住宅出售所得，需要交纳房价差额（卖价与买价的差额）的5.55%作为营业税，如果房子是5年以上的，则免交营业税。
第一步先计算出出售时间和购买时间的时差，Excel提供了一个函数，YEARFRAC就是用来计算两个时间间隔的年份的。因此D21的公式是：=YEARFRAC(E2, E19, 3)*12。即出售时间减去购买时间的年份乘以12—我使用月份来表示，是因为在按出售时间分析利润时，公式编写会方便一些。
出售房屋时，包括几个成本：当初的首付成本—B5，已还的贷款以及提前还贷需要还得本金。在等额本息还款方法里，前面月份还的钱大部分都是利息—不是本金，换句话说，已还贷款里大部分都是要交给银行的利息，所以提前还贷还不是简单的预计贷款还款总额减去已还的贷款。
Excel提供了一个函数，CUMPRINC可以用来计算在指定的还款次数后，总共还贷的本金额度，用法跟PMT类似。因此，实际缴纳商业贷款本金B23的公式是：=CUMPRINC(B3/12, B8*12, B6, 1,D21+1,1)。
最后实际出售所得B26 – 实际成本B25就是我们的利润B27了。
3. 变动出售时间和价格，分析利润，表格截图如下：
设置B29的公式为：=B27，一定要指定公式，不要指定值，否则Excel无法从公式链表里，使用参数表个里面的值替换匹配公式的单元格。
在C29到E29（当然Z29也可以—如果你觉得有必要分析这么长的时间的话）这一行设置要分析的出售时间。
在B30到B42这一列设置要分析的出售单价。
选中B29到E42这一个范围，点击菜单里的“数据”-> “What-if分析”->“数据表”：
在弹出的对话框里：
l 设置行输入单元格（Row input cell）为E19，即我们在第二步计算中使用的出售时间。
l 设置列输入单元格（Column input cell）为C19，即我们在第二步计算中使用的出售单价。
最后就可以看到随着出售时间和单价的变化，总利润的变化了。
好啦，当前物价飞涨，跑不过刘翔，也要争取跑过CPI呀。新年快乐，恭喜发财，附送一个简单的小程序，示例文件下载：/Files/killmyday/Excel_Sample.zip
