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

简介:

最近物价飞涨,得给钱找个保值的地方,朋友给我推荐了一个不错的楼盘投资保值,实在不行自住也很舒服。这么一大笔的投资,为了验证一下保值的潜力,当然需要慎之又慎,小心的不能再小心。作决定前自然要估计一下收益如何,这种纷繁复杂的计算,虽然每一步都是很简单的运算,但是很多步串起来,就有点顾此失彼了。因此就想到用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分析”->“数据表”:


在弹出的对话框里:


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

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

 

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

 

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


本文转自 donjuan 博客园博客,原文链接:   http://www.cnblogs.com/killmyday/archive/2010/12/23/1914301.html,如需转载请自行联系原作者

相关文章
|
1月前
|
存储 数据处理 索引
Python操作Excel常用方法汇总
Python操作Excel常用方法汇总
33 0
|
1月前
|
存储 数据可视化 数据处理
Python中读取Excel文件的方法
【2月更文挑战第18天】
66 4
Python中读取Excel文件的方法
|
5月前
|
Java
jsp页面中显示word/excel文档方法
jsp页面中显示word/excel文档方法
|
9月前
vue3导出excel表格方式 ---XLSX文件(最快的导出方法 )
vue3导出excel表格方式 ---XLSX文件(最快的导出方法 )
138 0
VS2005(excel2007)利用Automation(OLD Automation)方法。将Excel当成组件服务器的编译错误 我的系统盘和office都装在d盘。 自动生成的import有问题
VS2005(excel2007)利用Automation(OLD Automation)方法。将Excel当成组件服务器的编译错误 我的系统盘和office都装在d盘。 自动生成的import有问题
|
1月前
|
数据处理 Python
4种方法用Python批量实现多Excel多Sheet合并
4种方法用Python批量实现多Excel多Sheet合并
33 0
|
7月前
|
数据处理 Python
在Python中处理Excel文件有很多方法
在Python中处理Excel文件有很多方法
|
7月前
|
JavaScript 前端开发
vue3中将表格导出excel的方法(极简且有效)
vue3中将表格导出excel的方法(极简且有效)
224 0
excel双纵坐标轴展示的方法
excel双纵坐标轴展示的方法
|
2月前
|
测试技术 数据处理 Python
测试报告导出PDF和excel的方法
测试报告导出PDF和excel的方法