[转载]新年快乐,介绍个简单的Excel理财工作的制作方法

baacloud免费翻墙vpn注册使用

[转载]新年快乐,介绍个简单的Excel理财工作的制作方法 – Killmyday – 博客园.

最近物价飞涨,得给钱找个保值的地方,朋友给我推荐了一个不错的楼盘投资保值,实 在不行自住也很舒服。这么一大笔的投资,为了验证一下保值的潜力,当然需要慎之又慎,小心的不能再小心。作决定前自然要估计一下收益如何,这种纷繁复杂的 计算,虽然每一步都是很简单的运算,但是很多步串起来,就有点顾此失彼了。因此就想到用Excel自己做一个分析工具,省去自己编程的麻烦,可能你也有类似的需求,就把制作方法介绍如下。

设计的目标:

1. 用户需要输入房子的单价、面积以及购买时间。

2. 用户需要输入首付和公积金贷款的总额,程序能够计算出每个月的还贷的压力。

3. 用户输入预计房子的出售时间以及出售时每平的单价,程序能够根据出售时间和单价的变化,计算出总利润和均摊到每个月的利润。

4. 最后,用户可以修改一些其他参数,例如商业贷款利率,公积金贷款利率,还款年限,出售房屋的手续费等。

自动计算利润的表格如下所示(可以通过修改第一列的每平售价和第一行的出售时间来分析盈亏):

制作步骤:

1. 分析每月还贷压力,表格截图如下:

其中C2D2分别是允许用户输入的购买单价和房屋面积,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自己带了等额本息还款方式的计算每月还贷的公式PMTPMT函数接受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无法从公式链表里,使用参数表个里面的值替换匹配公式的单元格。

C29E29(当然Z29也可以—如果你觉得有必要分析这么长的时间的话)这一行设置要分析的出售时间。

B30B42这一列设置要分析的出售单价。

选中B29E42这一个范围,点击菜单里的“数据”-> What-if分析”->“数据表”:

在弹出的对话框里:

l 设置行输入单元格(Row input cell)为E19,即我们在第二步计算中使用的出售时间。

l 设置列输入单元格(Column input cell)为C19,即我们在第二步计算中使用的出售单价。

最后就可以看到随着出售时间和单价的变化,总利润的变化了。

好啦,当前物价飞涨,跑不过刘翔,也要争取跑过CPI呀。新年快乐,恭喜发财,附送一个简单的小程序,示例文件下载:/Files/killmyday/Excel_Sample.zip

赞(0) 打赏
分享到: 更多 (0)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

微信扫一扫打赏