现金流量与净现值计算

1、第一年第二年第三年第四年第五年第六年第七年经纪人监理费用$75,000$75,000$75,000$75,000$75,000$75,000$25,000广告费用$50,000$25,000$25,000$25,000$25,000$25,000$8,250保险费用$2,000$2,000$2,000$1,000$1,000$1,000$1,000土地购价总额$8,000,000200200200购地贷款$6,400,000股东投资$1,600,000每英亩共用土地配套开发成本$60,000200200200每英亩出售土地开发成本$6,250200200200共用土地面积 (英亩)$64200

2、200200预计售出土地总面积 (英亩)$256 2002002002000配套开发成本$3,840,000每平方英尺售价$2.5股本现值2072977.1经纪人佣金占销售收入比例5%股东投资1600000边际所得税率28%净现值472977.11贴现率20.0%内部报酬率27.5%第一年第二年第三年第四年第五年第六年第七年预计售出土地面积 (英亩)40404040404016总销售收入4,356,0004,356,0004,356,0004,356,0004,356,0004,356,0001,742,400减：分摊土地购置成本1,250,0001,250,0001,250,0001,250

3、,0001,250,0001,250,000500,000分摊共用土地开发成本600,000600,000600,000600,000600,000600,000240,000售出土地开发成本250,000250,000250,000250,000250,000250,000100,000中介费用217,800217,800217,800217,800217,800217,80087,120净销售收入2,038,2002,038,2002,038,2002,038,2002,038,2002,038,200815,280减：管理费+广告费+保险费127,000102,000102,000101

4、,000101,000101,00034,250房地产税1,115,136940,896766,656592,416418,176243,93669,696净经营收入796,064995,3041,169,5441,344,7841,519,0241,693,264711,334减：利息1,024,000864,000704,000544,000384,000224,00064,000税前利润-227,936131,304465,544800,7841,135,0241,469,264647,334应缴（边际）所得税-63,82236,765130,352224,220317,807411,3

5、94181,254税后利润-$164,114$94,539$335,192$576,564$817,217 $1,057,870$466,080偿还贷款1,600,0001,600,0001,600,0001,600,0001,600,0001,600,000640,000-$1,600,000$85,886$344,539$585,192$826,564 $1,067,217 $1,307,870$566,080现金流量现金流量NPV = 472977, IRR = 27.5%AB CDEFGHIJKLM12345678910111213141516171819212223242526272

6、829303132333435363738394041424344$472,977NPV = 472977, IRR = 27.5%in E26: =$E$20*$E$16*E25in E33: =房地产税计算!D1510% 1458444.3in E27: =$E$8/$E$14*E25in E34: =E31-E32-E3312% 1217414.3in E28: =$E$15/$E$14*E25in E35: =偿还贷款与利息计算!D1414% 1001184.5in E29: =$E$12*E25in E36: =E34-E3516%806717.2in E30: =$E$17*E26

7、in E37: =$E$18*E3618%631399.4in E31: =E26-SUM(E27:E30)in E38: =E36-E3720%472977.1in E32: =SUM(E4:E6)22%329499.824%199274.926%80830.2in E40: =偿还贷款与利息计算!D1528%-27118.8in E41: =E38+E27+E28-E4030%-125695.6in K16: =NPV(E19,E41:K41)in K18: =K16-K17in K17: =E10in K19: =IRR(D41:K41)20%-40000020%$472,97720%1

8、200000NOPQRSTUVWXY12345678910111213141516171819212223242526272829303132333435363738394041424344=房地产税计算!D15=偿还贷款与利息计算!D14Z12345678910111213141516171819212223242526272829303132333435363738394041424344in D13:=D5in D14:土地总面积(英亩)320in E13:=D13-D12in D15:共用土地面积(不纳税)(英亩)64预计售出土地总面积(英亩)256每平方英尺物业税0.1每英亩平方英尺

9、数43560第一年第二年第六年第七年当年预计售出土地面积(英亩)40404016当年纳税土地面积(英亩)2562165616当年纳税土地面积(平方英尺)1115136094089602439360696960预计房地产税款$1,115,136$940,896$243,936$69,696ABCDEIJKLMNOP12345678910111213141516=D13*$D$7=D14*$D$6Q12345678910111213141516in D13: =D5in D14:购地贷款$6,400,000in E13:=D16in D15:配套开发成本贷款$3,840,000in D16:总贷款$10,240,000贷款年利率10.0%100每售出1英亩土地须偿还贷款额$40,000第一年第二年第六年第七年预计售出土地面积 (英亩)40404016期初贷款余额$10,240,000 $8,640,000 $2,240,000$640,000支付利息$1,024,000$864,000$224,000$64,000偿还贷款本金$1,600,000 $1,600,000 $1,600,000$640,000期末贷款余额$8,640,000 $7,040,000$640,000$0ABCDEIJKLMN OP1234567891011121314151617=$D$