融资租赁IRR测算表.xlsx

目录说明：1、本工作薄用于租赁项目尽调信息录入，并测算项目租金及收益；2、根据项目租赁方案选择租金测算表，鉴于各行业报表项目有所差异，如果是内部报表请转化成以企业会计准则为准的报表格式；3、该工作薄处于试用阶段，欢迎提出意见以供改进；4、该工作薄要求至少使用0ffice2007版，低版本需升级后使用；5、标黄单元格如不带公式需手动填列，带公式的如有需要可自行修改；丰汇租赁 FENGHUI LEASING目录 Contents工作表名称工作表标签1租金测算收益分析【必填】附附表表1 1等额本息附表1-1等额本金附表1-2等息期末附表1-3不等额本息附表1-42财务数据财务图表附表2-1报表分析附表2-2租赁物清单附表2-3经营活动附表2-4投资活动附表2-5筹资活动附表2-6利润表附表2-7现金流附表2-8附表1 收益分析返回目录公司名称利率表浮动比率50%基准利率年利率(%)浮动后六个月以内（含6个月）4.35%6.53%六个月至一年（含1年）4.35%6.53%一至三年（含3年）4.75%7.13%三至五年（含5年）4.75%7.13%五年以上4.90%7.35%试用融资额（元）起租日期2016/1/20残值1结束日期2024/1/31租息率（年）8%先付后付0租赁期限（年）8残值计入本金0年付息次数4保证金率0%其他收益（起租日）0保证金（元）-0%手续费-保证金利息0%宽限期（年）1手续费率0.0%首付款比例0%首付款是否计入本金0手续费支付方式一次性实际年利率RATE#NUM!注：仅“标黄”单元格允许手动修改IRR参考值 16%项目收益IRR(等额本息)8.24%项目收益IRR(等额本金)8.24%项目收益IRR(等息期末还本）#NUM!IRR(不等额本息）8.24%附表1-1 租金测算表(等额本息）单位：元返回目录客户名称0租赁方式回租增值税率0%项 目 参 数融资额30,000,000,000租赁物价格30,000,000,000租金支付方式0手续费支付一次性与企业的税差0%租赁利率8.00%租赁期限（年）8年付租次数4其他收益（起租日）0补税方式分期期末残值1残值计入本金否贷款基准利率4.75%注：手续费一次性支付无需修改，如果是按其他方式支付（如：每年），以及保证金不是末端一次性而是分期支付，请根据实际情况在其他现金流入和流出中填写，如果行业有税差需要在本表单独核算保证金率0.00%保证金0手续费率0.00%折现率16.00%保证金存款利率0.00%手续费0收 益 测 算每期租金 1,278,318,220.05租金总额#收回本金30,000,000,000收回利息10,906,183,042净融资额现值30,000,000,000净现值(XNPV)(6,503,020,923)现值收益率-21.68%年化现值收益率-2.71%XIRR8.24%IRR8.24%现 金 流 量日期租赁相关流入利息收回本金尚未收回本金其他现金流入其他现金流出现金流入现金流出净现金流日历日利息交税本金销项本金进项起租日0.000.00#0.00#0.00#-30,000,000,000.002016/1/20-第1期 1,278,318,220.05600,000,000.00678,318,220.05#0.000.001,278,318,220.050.001,278,318,220.052016/4/20-第2期 1,278,318,220.05586,433,635.60691,884,584.46#0.000.001,278,318,220.050.001,278,318,220.052016/7/20-第3期 1,278,318,220.05572,595,943.91705,722,276.14#0.000.001,278,318,220.050.001,278,318,220.052016/10/20-第4期 1,278,318,220.05558,481,498.39719,836,721.67#0.000.001,278,318,220.050.001,278,318,220.052017/1/20-第5期 1,278,318,220.05544,084,763.95734,233,456.10#0.000.001,278,318,220.050.001,278,318,220.052017/4/20-第6期 1,278,318,220.05529,400,094.83748,918,125.22#0.000.001,278,318,220.050.001,278,318,220.052017/7/20-第7期 1,278,318,220.05514,421,732.33763,896,487.73#0.000.001,278,318,220.050.001,278,318,220.052017/10/20-第8期 1,278,318,220.05499,143,802.57779,174,417.48#0.000.001,278,318,220.050.001,278,318,220.052018/1/20-第9期 1,278,318,220.05483,560,314.22794,757,905.83#0.000.001,278,318,220.050.001,278,318,220.052018/4/20-第10期 1,278,318,220.05467,665,156.11810,653,063.95#0.000.001,278,318,220.050.001,278,318,220.052018/7/20-第11期 1,278,318,220.05451,452,094.83826,866,125.23#0.000.001,278,318,220.050.001,278,318,220.052018/10/20-第12期 1,278,318,220.05434,914,772.32843,403,447.73#0.000.001,278,318,220.050.001,278,318,220.052019/1/20-第13期 1,278,318,220.05418,046,703.37860,271,516.69#0.000.001,278,318,220.050.001,278,318,220.052019/4/20-第14期 1,278,318,220.05400,841,273.03877,476,947.02#0.000.001,278,318,220.050.001,278,318,220.052019/7/20-第15期 1,278,318,220.05383,291,734.09895,026,485.96#0.000.001,278,318,220.050.001,278,318,220.052019/10/20-第16期 1,278,318,220.05365,391,204.37912,927,015.68#0.000.001,278,318,220.050.001,278,318,220.052020/1/20-第17期 1,278,318,220.05347,132,664.06931,185,555.99#0.000.001,278,318,220.050.001,278,318,220.052020/4/20-第18期 1,278,318,220.05328,508,952.94949,809,267.11#0.000.001,278,318,220.050.001,278,318,220.052020/7/20-第19期 1,278,318,220.05309,512,767.60968,805,452.46#0.000.001,278,318,220.050.001,278,318,220.052020/10/20-第20期 1,278,318,220.05290,136,658.55988,181,561.50#0.000.001,278,318,220.050.001,278,318,220.052021/1/20-第21期 1,278,318,220.05270,373,027.321,007,945,192.73#0.000.001,278,318,220.050.001,278,318,220.052021/4/20-第22期 1,278,318,220.05250,214,123.471,028,104,096.59#0.000.001,278,318,220.050.001,278,318,220.052021/7/20-第23期 1,278,318,220.05229,652,041.531,048,666,178.52#0.000.001,278,318,220.050.001,278,318,220.052021/10/20-第24期 1,278,318,220.05208,678,717.961,069,639,502.09 9,364,296,396.060.000.001,278,318,220.050.001,278,318,220.052022/1/20-第25期 1,278,318,220.05187,285,927.921,091,032,292.13 8,273,264,103.920.000.001,278,318,220.050.001,278,318,220.052022/4/20-第26期 1,278,318,220.05165,465,282.081,112,852,937.98 7,160,411,165.950.000.001,278,318,220.050.001,278,318,220.052022/7/20-第27期 1,278,318,220.05143,208,223.321,135,109,996.74 6,025,301,169.210.000.001,278,318,220.050.001,278,318,220.052022/10/20-第28期 1,278,318,220.05120,506,023.381,157,812,196.67 4,867,488,972.540.000.001,278,318,220.050.001,278,318,220.052023/1/20-第29期 1,278,318,220.0597,349,779.451,180,968,440.60 3,686,520,531.940.000.001,278,318,220.050.001,278,318,220.052023/4/20-第30期 1,278,318,220.0573,730,410.641,204,587,809.42 2,481,932,722.520.000.001,278,318,220.050.001,278,318,220.052023/7/20-第31期 1,278,318,220.0549,638,654.451,228,679,565.60 1,253,253,156.920.000.001,278,318,220.050.001,278,318,220.052023/10/20-第32期 1,278,318,220.0525,065,063.141,253,253,156.920.001.000.001,278,318,221.050.001,278,318,221.052024/1/20-第33期0.000.000.000.000.000.000.000.000.002024/4/30-第34期0.000.000.000.000.000.000.000.000.002024/7/31-第35期0.000.000.000.000.000.000.000.000.002024/10/31-第36期0.000.000.000.000.000.000.000.000.002025/1/31-第37期0.000.000.000.000.000.000.000.000.002025/4/30-第38期0.000.000.000.000.000.000.000.000.002025/7/31-第39期0.000.000.000.000.000.000.000.000.002025/10/31-第40期0.000.000.000.000.000.000.000.000.002026/1/31-第41期0.000.000.000.000.000.000.000.000.002026/4/30-第42期0.000.000.000.000.000.000.000.000.002026/7/31-第43期0.000.000.000.000.000.000.000.000.002026/10/31-第44期0.000.000.000.000.000.000.000.000.002027/1/31-第45期0.000.000.000.000.000.000.000.000.002027/4/30-第46期0.000.000.000.000.000.000.000.000.002027/7/31-第47期0.000.000.000.000.000.000.000.000.002027/10/31-第48期0.000.000.000.000.000.000.000.000.002028/1/31-第49期0.000.000.000.000.000.000.000.000.002028/4/30-第50期0.000.000.000.000.000.000.000.000.002028/7/31-第51期0.000.000.000.000.000.000.000.000.002028/10/31-第52期0.000.000.000.000.000.000.000.000.002029/1/31-第53期0.000.000.000.000.000.000.000.000.002029/4/30-第54期0.000.000.000.000.000.000.000.000.002029/7/31-第55期0.000.000.000.000.000.000.000.000.002029/10/31-第56期0.000.000.000.000.000.000.000.000.002030/1/31-第57期0.000.000.000.000.000.000.000.000.002030/4/30-第58期0.000.000.000.000.000.000.000.000.002030/7/31-第59期0.000.000.000.000.000.000.000.000.002030/10/31-第60期0.000.000.000.000.000.000.000.000.002031/1/31-合计#Check计算年度租金40,906,183,041.74 租金链接至筹资表注：1.我司统一用IRR为标准;2.仅“标黄”单元格允许手动修改。附表1-2 租金测算表(等额本金）单位：元返回目录客户名称0租赁方式回租增值税率0%项 目 参 数融资额30,000,000,000租赁物价格30,000,000,000租金支付方式0手续费支付一次性与企业的税差0%租赁利率8.00%租赁期限（年）8年付租次数4其他收益（起租日）0补税方式分期期末残值1残值计入本金否贷款基准利率4.75%注：手续费一次性支付无需修改，如果是按其他方式支付（如：每年），以及保证金不是末端一次性而是分期支付，请根据实际情况在其他现金流入和流出中填写保证金率0.00%保证金0手续费率0.00%折现率16.00%保证金存款利率0.00%手续费0收 益 测 算每期本金937,500,000.00租金总额39,900,000,000.00收回本金30,000,000,000.00收回利息9,900,000,000净融资额现值30,000,000,000净现值(XNPV)(6,015,015,020.23)现值收益率-20.05%年化现值收益率-2.51%XIRR8.24%IRR8.24%现 金 流 量日期租赁相关流入利息收回本金尚未收回本金其他现金流入其他现金流出现金流入现金流出净现金流日历日起租日0.000.0030,000,000,000.000.00#0.00#2016/1/20第1期1,537,500,000.00600,000,000.00937,500,000.0029,062,500,000.000.000.001,537,500,000.000.001,537,500,000.002016/4/20第2期1,518,750,000.00581,250,000.00937,500,000.0028,125,000,000.000.000.001,518,750,000.000.001,518,750,000.002016/7/20第3期1,500,000,000.00562,500,000.00937,500,000.0027,187,500,000.000.000.001,500,000,000.000.001,500,000,000.002016/10/20第4期1,481,250,000.00543,750,000.00937,500,000.0026,250,000,000.000.000.001,481,250,000.000.001,481,250,000.002017/1/20第5期1,462,500,000.00525,000,000.00937,500,000.0025,312,500,000.000.000.001,462,500,000.000.001,462,500,000.002017/4/20第6期1,443,750,000.00506,250,000.00937,500,000.0024,375,000,000.000.000.001,443,750,000.000.001,443,750,000.002017/7/20第7期1,425,000,000.00487,500,000.00937,500,000.0023,437,500,000.000.000.001,425,000,000.000.001,425,000,000.002017/10/20第8期1,406,250,000.00468,750,000.00937,500,000.0022,500,000,000.000.000.001,406,250,000.000.001,406,250,000.002018/1/20第9期1,387,500,000.00450,000,000.00937,500,000.0021,562,500,000.000.000.001,387,500,000.000.001,387,500,000.002018/4/20第10期1,368,750,000.00431,250,000.00937,500,000.0020,625,000,000.000.000.001,368,750,000.000.001,368,750,000.002018/7/20第11期1,350,000,000.00412,500,000.00937,500,000.0019,687,500,000.000.000.001,350,000,000.000.001,350,000,000.002018/10/20第12期1,331,250,000.00393,750,000.00937,500,000.0018,750,000,000.000.000.001,331,250,000.000.001,331,250,000.002019/1/20第13期1,312,500,000.00375,000,000.00937,500,000.0017,812,500,000.000.000.001,312,500,000.000.001,312,500,000.002019/4/20第14期1,293,750,000.00356,250,000.00937,500,000.0016,875,000,000.000.000.001,293,750,000.000.001,293,750,000.002019/7/20第15期1,275,000,000.00337,500,000.00937,500,000.0015,937,500,000.000.000.001,275,000,000.000.001,275,000,000.002019/10/20第16期1,256,250,000.00318,750,000.00937,500,000.0015,000,000,000.000.000.001,256,250,000.000.001,256,250,000.002020/1/20第17期1,237,500,000.00300,000,000.00937,500,000.0014,062,500,000.000.000.001,237,500,000.000.001,237,500,000.002020/4/20第18期1,218,750,000.00281,250,000.00937,500,000.0013,125,000,000.000.000.001,218,750,000.000.001,218,750,000.002020/7/20第19期1,200,000,000.00262,500,000.00937,500,000.0012,187,500,000.000.000.001,200,000,000.000.001,200,000,000.002020/10/20第20期1,181,250,000.00243,750,000.00937,500,000.0011,250,000,000.000.000.001,181,250,000.000.001,181,250,000.002021/1/20第21期1,162,500,000.00225,000,000.00937,500,000.0010,312,500,000.000.000.001,162,500,000.000.001,162,500,000.002021/4/20第22期1,143,750,000.00206,250,000.00937,500,000.009,375,000,000.000.000.001,143,750,000.000.001,143,750,000.002021/7/20第23期1,125,000,000.00187,500,000.00937,500,000.008,437,500,000.000.000.001,125,000,000.000.001,125,000,000.002021/10/20第24期1,106,250,000.00168,750,000.00937,500,000.007,500,000,000.000.000.001,106,250,000.000.001,106,250,000.002022/1/20第25期1,087,500,000.00150,000,000.00937,500,000.006,562,500,000.000.000.001,087,500,000.000.001,087,500,000.002022/4/20第26期1,068,750,000.00131,250,000.00937,500,000.005,625,000,000.000.000.001,068,750,000.000.001,068,750,000.002022/7/20第27期1,050,000,000.00112,500,000.00937,500,000.004,687,500,000.000.000.001,050,000,000.000.001,050,000,000.002022/10/20第28期1,031,250,000.0093,750,000.00937,500,000.003,750,000,000.000.000.001,031,250,000.000.001,031,250,000.002023/1/20第29期1,012,500,000.0075,000,000.00937,500,000.002,812,500,000.000.000.001,012,500,000.000.001,012,500,000.002023/4/20第30期993,750,000.0056,250,000.00937,500,000.001,875,000,000.000.000.00993,750,000.000.00993,750,000.002023/7/20第31期975,000,000.0037,500,000.00937,500,000.00937,500,000.000.000.00975,000,000.000.00975,000,000.002023/10/20第32期956,250,000.0018,750,000.00937,500,000.000.001.000.00956,250,001.000.00956,250,001.002024/1/20第33期0.000.000.000.000.000.000.000.000.002024/4/30第34期0.000.000.000.000.000.000.000.000.002024/7/31第35期0.000.000.000.000.000.000.000.000.002024/10/31第36期0.000.000.000.000.000.000.000.000.002025/1/31第37期0.000.000.000.000.000.000.000.000.002025/4/30第38期0.000.000.000.000.000.000.000.000.002025/7/31第39期0.000.000.000.000.000.000.000.000.002025/10/31第40期0.000.000.000.000.000.000.000.000.002026/1/31第41期0.000.000.000.000.000.000.000.000.002026/4/30第42期0.000.000.000.000.000.000.000.000.002026/7/31第43期0.000.000.000.000.000.000.000.000.002026/10/31第44期0.000.000.000.000.000.000.000.000.002027/1/31第45期0.000.000.000.000.000.000.000.000.002027/4/30第46期0.000.000.000.000.000.000.000.000.002027/7/31第47期0.000.000.000.000.000.000.000.000.002027/10/31第48期0.000.000.000.000.000.000.000.000.002028/1/31第49期0.000.000.000.000.000.000.000.000.002028/4/30第50期0.000.000.000.000.000.000.000.000.002028/7/31第51期0.000.000.000.000.000.000.000.000.002028/10/31第52期0.000.000.000.000.000.000.000.000.002029/1/31第53期0.000.000.000.000.000.000.000.000.002029/4/30第54期0.000.000.000.000.000.000.000.000.002029/7/31第55期0.000.000.000.000.000.000.000.000.002029/10/31第56期0.000.000.000.000.000.000.000.000.002030/1/31第57期0.000.000.000.000.000.000.000.000.002030/4/30第58期0.000.000.000.000.000.000.000.000.002030/7/31第59期0.000.000.000.000.000.000.000.000.002030/10/31第60期0.000.000.000.000.000.000.000.000.002031/1/31合计39,900,000,000.009,900,000,000.00 30,000,000,000.00Check计算年度租金39,900,000,000.00租金链接至筹资表注：1.我司统一用IRR为标准;2.仅“标黄”单元格允许手动修改。附表1-3 测算表(等息期末还本）单位：元返回目录客户名称0租赁方式回租增值税率0%项 目 参 数融资额30,000,000,000租赁物价格30,000,000,000租金支付方式0手续费支付一次性与企业的税差0%租赁利率8.00%租赁期限（年）8年付租次数4其他收益（起租日）0补税方式分期期末残值1残值计入本金否贷款基准利率4.75%注：手续费一次性支付无需修改，如果是按其他方式支付（如：每年），以及保证金不是末端一次性而是分期支付，请根据实际情况在其他现金流入和流出中填写保证金率0.00%保证金0手续费率0.00%折现率16.00%保证金存款利率0.00%手续费0收 益 测 算每期租金0.00租金总额0.00收回本金30,000,000,000.00收回利息19,200,000,000净融资额现值30,000,000,000净现值(XNPV)(9,827,975,776.79)现值收益率-32.76%年化现值收益率-4.09%XIRR8.2%IRR#NUM!现 金 流 量日期租赁相关流入利息收回本金尚未收回本金其他现金流入其他现金流出现金流入现金流出净现金流日历日起租日0.000.0030,000,000,000.000.00#0.00#-30,000,000,000.002016/1/20第1期0.00600,000,000.000.0030,000,000,000.000.000.00600,000,000.000.00600,000,000.002016/4/20第2期0.00600,000,000.000.0030,000,000,000.000.000.00600,000,000.000.00600,000,000.002016/7/20第3期0.00600,000,000.000.0030,000,000,000.000.000.00600,000,000.000.00600,000,000.002016/10/20第4期0.00600,000,000.000.0030,000,000,000.000.000.00600,000,000.000.00600,000,000.002017/1/20第5期0.00600,000,000.000.0030,000,000,000.000.000.00600,000,000.000.00600,000,000.002017/4/20第6期0.00600,000,000.000.0030,000,000,000.000.000.00600,000,000.000.00600,000,000.002017/7/20第7期0.00600,000,000.000.0030,000,000,000.000.000.00600,000,000.000.00600,000,000.002017/10/20第8期0.00600,000,000.000.0030,000,000,000.000.000.00600,000,000.000.00600,000,000.002018/1/20第9期0.00600,000,000.000.0030,000,000,000.000.000.00600,000,000.000.00600,000,000.002018/4/20第10期0.00600,000,000.000.0030,000,000,000.000.000.00600,000,000.000.00600,000,000.002018/7/20第11期0.00600,000,000.000.0030,000,000,000.000.000.00600,000,000.000.00600,000,000.002018/10/20第12期0.00600,000,000.000.0030,000,000,000.000.000.00600,000,000.000.00600,000,000.002019/1/20第13期0.00600,000,000.000.0030,000,000,000.000.000.00600,000,000.000.00600,000,000.002019/4/20第14期0.00600,000,000.000.0030,000,000,000.000.000.00600,000,000.000.00600,000,000.002019/7/20第15期0.00600,000,000.000.0030,000,000,000.000.000.00600,000,000.000.00600,000,000.002019/10/20第16期0.00600,000,000.000.0030,000,000,000.000.000.00600,000,000.000.00600,000,000.002020/1/20第17期0.00600,000,000.000.0030,000,000,000.000.000.00600,000,000.000.00600,000,000.002020/4/20第18期0.00600,000,000.000.0030,000,000,000.000.000.00600,000,000.000.00600,000,000.002020/7/20第19期0.00600,000,000.000.0030,000,000,000.000.000.00600,000,000.000.00600,000,000.002020/10/20第20期0.00600,000,000.000.0030,000,000,000.000.000.00600,000,000.000.00600,000,000.002021/1/20第21期0.00600,000,000.000.0030,000,000,000.000.000.00600,000,000.000.00600,000,000.002021/4/20第22期0.00600,000,000.000.0030,000,000,000.000.000.00600,000,000.000.00600,000,000.002021/7/20第23期0.00600,000,000.000.0030,000,000,000.000.000.00600,000,000.000.00600,000,000.002021/10/20第24期0.00600,000,000.000.0030,000,000,000.000.000.00600,000,000.000.00600,000,000.002022/1/20第25期0.00600,000,000.000.0030,000,000,000.000.000.00600,000,000.000.00600,000,000.002022/4/20第26期0.00600,000,000.000.0030,000,000,000.000.000.00600,000,000.000.00600