10级建管一班

1、应用excel财务计算公式完成以下年金终值表(保存两位小数)表1年金A折现率i(%)计息期n终值F2000b5-19,098.223000610-39,542.385000712-89,442.266000I*；15-162,912.6870005.511-102,084.498000；6-91,930.539000414-164,627.2010000316-201,568.81120009：18-495,616.0614001815-85,351.37二、应用excel财务计算公式完成以下现值终值表(结果保存两位小数)表2现金P折现率i(%)计息期n终值F200058-2,954.9130

2、00610-5,372.545000712-11,260.966000815-19,033.0170005.511-12,614.65800069-13,515.839000414-15,585.0910000316-16,047.0612000918-56,605.4514001815-16,763.25三、应用excel财务计算公式完成以下年金现值表(结果保存两位小数)表3年金A折现率i(%)计息期n终值P200058-2,954.913000610-5,372.545000712-11,260.966000815-19,033.0170005.511-12,614.65800069-13

3、,515.839000414-15,585.0910000316-16,047.0612000918-56,605.4514001815-16,763.25四、应用excel财务计算公式完成以下终值现值表结果保存两位小数表4终值F折现率i%计息期n终值P200058-1,353.683000610-1,675.185000712-2,220.066000815-1,891.4570005.511-3,884.37800069-4,735.199000414-5,197.2810000316-6,231.6712000918-2,543.92五、应用excel财务计算公式计算下表6个方案的净现值

4、NPV、内部收益率IRR，计算期均为16年，基准收益率12%。保存两位小数表5万案ABCDEF投资600640700750720680年净收益250280310285245210ABCDEF期间净收益净收益:净收益净收益净收益净收益0-600-640-700-750-720-680125028031028524521022502803102852452103250280310285245210425028031028524521052502803102852452106250280310285245210725028031028524521082502803102852452109250280

5、31028524521010250280310285245210112502803102852452101225028031028524521013250280310285245210142502803102852452101525028031028524521016250280310285245210NPV1143.501312.721461.941237.59988.63784.54IRR41.51%43.62%44.16%37.77%33.70%30.44%六、贷款700000元，期限30年，年利率为6%,假设每年年末进行等额本利和还款，试用excel财务计算函数完成下表。提示：运用函数

6、PMT、PPMT、IPMT计算，结果保存两位小数。表7还款计算表期数归还本金归还利息本息合计贷款余额1-8854.24-42000.00-50854.24691145.762-9385.49-41468.75-50854.24681760.273-9948.62-40905.62-50854.24671811.654:-10545.54-40308.70-50854.24661266.115-11178.27-39675.97-50854.24650087.846-11848.97-39005.27-50854.24638238.877-12559.91-38294.33-50854.2462

7、5678.968-13313.50-37540.74-50854.24612365.469-14112.31-36741.93-50854.24598253.1510 :-14959.05-35895.19-50854.24583294.1011-15856.59-34997.65-50854.24567437.5112-16807.99-34046.25-50854.24550629.5213:-17816.47-33037.77-50854.24532813.0614-18885.45-31968.78-50854.24513927.6015-20018.58-30835.66-50854

8、.24493909.0216 :-21219.70-29634.54-50854.24472689.3317-22492.88-28361.36-50854.24450196.4518-23842.45-27011.79-50854.24426354.0019:-25273.00-25581.24-50854.24401081.0020-26789.38-24064.86-50854.24374291.6221-28396.74-22457.50-50854.24345894.8822-30100.55-20753.69-50854.24315794.3323-31906.58-18947.6

9、6-50854.24283887.7524-33820.97-17033.27-50854.24250066.7825-35850.23-15004.01-50854.24214216.5526-38001.25-12852.99-50854.24176215.3127-40281.32-10572.92-50854.24135933.9928-42698.20-8156.04-50854.2493235.7929-45260.09-5594.15-50854.2447975.7030-47975.70-2878.54-50854.240.00七、应用线性折旧法，利用excel财务计算公式计算

10、完成，固定资产 的线性折旧计算表。提示：应用SLN函数，保存两位小数：设备原值500000元；折旧年限30年；预计残值5000元；清理费用2000元表8年份年净残值年折旧费累计折旧额年末净值00.000.000.00500000.001500000.0016566.6716566.67483433.332483433.3316566.6733133.33466866.673466866.6716566.6749700.00450300.004450300.0016566.6766266.67433733.335433733.3316566.6782833.33417166.676417166.

11、6716566.6799400.00400600.007400600.0016566.67115966.67384033.338384033.3316566.67132533.33367466.679367466.6716566.67149100.00350900.0010350900.0016566.67165666.67334333.3311334333.3316566.67182233.33317766.6712317766.6716566.67198800.00301200.0013301200.0016566.67215366.67284633.3314284633.3316566.

12、67231933.33268066.6715268066.6716566.67248500.00251500.0016251500.0016566.67265066.67234933.3317234933.3316566.67281633.33218366.6718218366.6716566.67298200.00202200.0019202200.0016566.67314766.67185233.3320185233.3316566.67331333.33168666.6721168666.6716566.67347900.00152100.0022152100.0016566.6736

13、4466.67135533.3323135533.3316566.67381033.33118966.6724118966.6716566.67397600.00102400.0025102400.0016566.67414166.6785833.332685833.3316566.67430733.3369266.672769266.6716566.67447300.0052700.002852700.0016566.67463866.6736133.332936133.3316566.67480433.3319566.673019566.6716566.67497000.003000.00

14、八、应用年数总和法，利用excel财务计算公式计算完成，固定资产的线性折旧计算表。提示：应用SYD函数，保存两位小数：设备原值500000元；折旧年限30年；预计残值5000元；清理费用2000元表9年份年净残值年折旧费累计折旧额年末净值00.000.000.00500000.001500000.0032064.5232064.52467935.482467935.4830995.7063060.22436939.783436939.7829926.8892987.10407012.904407012.9028858.06121845.16378154.845378154.8427789.25

15、149634.41350365.596350365.5926720.43176354.84323645.167323645.1625651.61202206.45297993.558297993.5524582.80226589.25273410.759273410.7523513.98250103.23249896.7710249896.7722445.16272548.39227451.6111227451.6121376.34293924.73206075.2712206075.2720307.53314232.26185767.7413185767.7419238.71333470.9

16、7166529.0314166529.0318169.89351640.86148359.1415148359.1417101.08368741.94131258.0616131258.0616032.26384774.19115225.8117115225.8114963.44399737.63100262.3718100262.3713894.62413632.2686367.741986367.7412825.81426458.0673541.942073541.9411756.99438215.0561784.952161784.9510688.17448903.2351096.772

17、251096.779619.35458522.5841477.422341477.428550.54467073.1232926.882432926.887481.72474554.8425445.162525445.166412.90480967.7419032.262619032.265344.09486311.8313688.172713688.174275.27490587.109412.90289412.903206.45493793.556206.45296206.452137.63495931.184068.82304068.821068.82497000.003000.00九、

18、应用双倍余额递减法，利用 excel财务计算公式计算完成，固定：设备原值500000元；折旧年限30年；预计残值5000元；清理费用2000元表10年份年净残值年折旧费累计折旧额年末净值00.000.000.00500000.001500000.0033333.3333333.33466666.672466666.6731111.1164444.44435555.563435555.5629037.0493481.48406518.524406518.5227101.23120582.72379417.285379417.2825294.49145877.20354122.806354122.

19、8023608.19169485.39330514.617330514.6122034.31191519.70308480.308308480.3020565.35212085.05287914.959287914.9519194.33231279.38268720.6210268720.6217914.71249194.09250805.9111250805.9116720.39265914.48234085.5212234085.5215605.70281520.18218479.8213218479.8214565.32296085.50203914.5014203914.5013594

20、.30309679.80190320.2015190320.2012688.01322367.82177632.1816177632.1811842.15334209.96165790.0417165790.0411052.67345262.63154737.3718154737.3710315.82355578.46144421.5419144421.549628.10365206.56134793.4420134793.448986.23374192.79125807.2121125807.218387.15382579.94117420.0622117420.067828.0039040

21、7.94109592.0623109592.067306.14397714.08102285.9224102285.926819.06404533.1495466.862595466.866364.46410897.6089102.402689102.405940.16416837.7683162.242783162.245544.15422381.9177618.092877618.095174.54427556.4572443.552972443.554829.57432386.0267613.983067613.984507.60436893.6163106.39九、应用三倍余额递减法，

22、利用 excel财务计算公式计算完成，固定：设备原值500000元；折旧年限30年；预计净残值5000元;清理费用2000元。表11年份年净残值年折旧费累计折旧额年末净值00.000.000.00500000.001500000.0050000.0050000.00450000.002450000.0045000.0095000.00405000.003405000.0040500.00135500.00364500.004364500.0036450.00171950.00328050.005328050.0032805.00204755.00295245.006295245.0029524

23、.50234279.50265720.507265720.5026572.05260851.55239148.458239148.4523914.85284766.40215233.619215233.6121523.36306289.76193710.2410193710.2419371.02325660.78174339.2211174339.2217433.92343094.70156905.3012156905.3015690.53358785.23141214.7713141214.7714121.48372906.71127093.2914127093.2912709.333856

24、16.04114383.9615114383.9611438.40397054.43102945.5716102945.5710294.56407348.9992651.011792651.019265.10416614.0983385.911883385.918338.59424952.6875047.321975047.327504.73432457.4167542.592067542.596754.26439211.6760788.332160788.336078.83445290.5154709.492254709.495470.95450761.4549238.552349238.5

25、54923.85455685.3144314.692444314.694431.47460116.7839883.222539883.223988.32464105.1035894.902635894.903589.49467694.5932305.412732305.413230.54470925.1329074.872829074.872907.49473832.6226167.382926167.382616.74476449.3623550.643023550.642355.06478804.4221195.58十、某工程有关数据如表12所示，基准收益率i c= 10%。试计算静 态、动态投资回收期。表12工程支出、收入表单位：万元年份01234-78-12投资200500 :200经营费用300450400收入350700700解：用列表计算各现金流量，如下表。表13现金流量计算表单位：万元年份净现金流量累计净现金流 量净现金流量现 值累计净现金流 量现值0-200-200-200.00-200.001-500-700-454.55-654.552-200-900-165.29-819.83350-85037.57-782.274250-