10級建管一班

1、應(yīng)用excel財務(wù)計算公式完成以下年金終值表(保存兩位小數(shù))表1年金A折現(xiàn)率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二、應(yīng)用excel財務(wù)計算公式完成以下現(xiàn)值終值表(結(jié)果保存兩位小數(shù))表2現(xiàn)金P折現(xiàn)率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三、應(yīng)用excel財務(wù)計算公式完成以下年金現(xiàn)值表(結(jié)果保存兩位小數(shù))表3年金A折現(xiàn)率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四、應(yīng)用excel財務(wù)計算公式完成以下終值現(xiàn)值表結(jié)果保存兩位小數(shù)表4終值F折現(xiàn)率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五、應(yīng)用excel財務(wù)計算公式計算下表6個方案的凈現(xiàn)值

4、NPV、內(nèi)部收益率IRR，計算期均為16年，基準(zhǔn)收益率12%。保存兩位小數(shù)表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%,假設(shè)每年年末進(jìn)行等額本利和還款，試用excel財務(wù)計算函數(shù)完成下表。提示：運用函數(shù)

6、PMT、PPMT、IPMT計算，結(jié)果保存兩位小數(shù)。表7還款計算表期數(shù)歸還本金歸還利息本息合計貸款余額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七、應(yīng)用線性折舊法，利用excel財務(wù)計算公式計算

10、完成，固定資產(chǎn) 的線性折舊計算表。提示：應(yīng)用SLN函數(shù)，保存兩位小數(shù)：設(shè)備原值500000元；折舊年限30年；預(yù)計殘值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、八、應(yīng)用年數(shù)總和法，利用excel財務(wù)計算公式計算完成，固定資產(chǎn)的線性折舊計算表。提示：應(yīng)用SYD函數(shù)，保存兩位小數(shù)：設(shè)備原值500000元；折舊年限30年；預(yù)計殘值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、應(yīng)用雙倍余額遞減法，利用 excel財務(wù)計算公式計算完成，固定：設(shè)備原值500000元；折舊年限30年；預(yù)計殘值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九、應(yīng)用三倍余額遞減法，

22、利用 excel財務(wù)計算公式計算完成，固定：設(shè)備原值500000元；折舊年限30年；預(yù)計凈殘值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十、某工程有關(guān)數(shù)據(jù)如表12所示，基準(zhǔn)收益率i c= 10%。試計算靜 態(tài)、動態(tài)投資回收期。表12工程支出、收入表單位：萬元年份01234-78-12投資200500 :200經(jīng)營費用300450400收入350700700解：用列表計算各現(xiàn)金流量，如下表。表13現(xiàn)金流量計算表單位：萬元年份凈現(xiàn)金流量累計凈現(xiàn)金流 量凈現(xiàn)金流量現(xiàn) 值累計凈現(xiàn)金流 量現(xiàn)值0-200-200-200.00-200.001-500-700-454.55-654.552-200-900-165.29-819.83350-85037.57-782.274250-