計(jì)算機(jī)應(yīng)用基礎(chǔ)課件第8章 Excel高級(jí)應(yīng)用2-投資貸款分析

第8章Excel高級(jí)應(yīng)用2

-投資貸款分析2015年1月8.1項(xiàng)目分析在Excel中財(cái)務(wù)函數(shù)可以進(jìn)行一般的財(cái)務(wù)計(jì)算，如確定貸款的支付額、投資的未來值或凈現(xiàn)值，以及債券或息票的價(jià)值。這些財(cái)務(wù)函數(shù)大體上可分為四類：投資計(jì)算函數(shù)、折舊計(jì)算函數(shù)、償還率計(jì)算函數(shù)、債券及其他金融函數(shù)。它們?yōu)樨?cái)務(wù)分析提供了極大的便利。使用這些函數(shù)不必理解高級(jí)財(cái)務(wù)知識(shí)，只要填寫變量值就可以了。本章以身邊常見的各種情景介紹一些典型財(cái)務(wù)函數(shù)的應(yīng)用，效果如圖8.2任務(wù)一：投資計(jì)算函數(shù)應(yīng)用任務(wù)要求與效果：本節(jié)利用PV和FV函數(shù)對(duì)個(gè)人貸款及償還能力加以分析，解決生活中的身邊事。企業(yè)或個(gè)人在確定要投資某個(gè)項(xiàng)目后,其在指定投資年限后可以得到的未來值是可以預(yù)測(cè)的。如果是用傳統(tǒng)的財(cái)務(wù)知識(shí)進(jìn)行計(jì)算需要列出如圖

所示的復(fù)雜數(shù)學(xué)公式，還要代入正確的數(shù)值才能計(jì)算出來。在excel中,如果要預(yù)測(cè)某項(xiàng)投資基于固定利率及等額分期付款方式的未來值，可以使用程序內(nèi)置的FV()函數(shù)來完成，方法也很簡(jiǎn)單。另外，如果想計(jì)算某項(xiàng)投資的一系列將來要償還額的當(dāng)前總值的話，或者一次性償還額的現(xiàn)值，那么利用EXCEL內(nèi)置的PV()函數(shù)來完成。知識(shí)點(diǎn)解析1.常見的參數(shù)學(xué)習(xí)具體財(cái)務(wù)函數(shù)之前，需要首先理解財(cái)務(wù)函數(shù)中常見名詞及參數(shù)的意義。年金:在某一段連續(xù)時(shí)間內(nèi)一系列的固定金額給付活動(dòng)。例如汽車、房屋的分期付款就是年金的一種。即年金是指一定時(shí)期內(nèi)每期相等金額的收付款項(xiàng)，如折舊、租金、利息、養(yǎng)老金、保險(xiǎn)金等未來值（fv）--在所有付款發(fā)生后的投資或貸款的價(jià)值。例如零存整取的期末領(lǐng)回金額。期間數(shù)（nper）--為總投資（或貸款）期，即該項(xiàng)投資（或貸款）的付款期總數(shù)。付款(pmt)--對(duì)于一項(xiàng)投資或貸款的定期支付數(shù)額。其數(shù)值在整個(gè)年金期間保持不變。通常pmt包括本金和利息，但不包括其他費(fèi)用及稅款?，F(xiàn)值(pv)--在投資期初的投資或貸款的價(jià)值。例如，貸款的現(xiàn)值為所借入的本金數(shù)額。利率(rate)--投資或貸款的利率或貼現(xiàn)率。

類型（type）--付款期間內(nèi)進(jìn)行支付的間隔，在期初或期末用0或1表示，省略或0時(shí)表示期末給付，1為期初給付。知識(shí)點(diǎn)解析2.求現(xiàn)值FV（）函數(shù)用途：基于固定利率及等額分期付款方式，返回某項(xiàng)投資的未來值。語法：FV(rate，nper，pmt，pv，type)參數(shù)：Rate為各期利率，Nper為總投資期(即該項(xiàng)投資的付款期總數(shù))，Pmt為各期所應(yīng)支付的金額，Pv為現(xiàn)值（即從該項(xiàng)投資開始計(jì)算時(shí)已經(jīng)入帳的款項(xiàng)，或一系列未來付款的當(dāng)前值的累積和，也稱為本金），Type為數(shù)字0或1(0為期末，1為期初)。默認(rèn)狀態(tài)下系統(tǒng)假設(shè)其值為0。0或忽略表示收付款時(shí)間是期末，1表示是期初。3.求未來值PV（）函數(shù)用途：返回投資的現(xiàn)值(即一系列未來付款的當(dāng)前值的累積和)，如借入方的借入款即為貸出方貸款的現(xiàn)值。語法：PV(rate，nper，pmt，fv，type)參數(shù)：Rate為各期利率，Nper為總投資(或貸款)期數(shù)，Pmt為各期所應(yīng)支付的金額，F(xiàn)v為未來值，Type指定各期的付款時(shí)間是在期初還是期末(1為期初。0為期末)。

任務(wù)實(shí)現(xiàn)步驟1.

情景一：FV函數(shù)的應(yīng)用張老師準(zhǔn)備買一部車，計(jì)劃從現(xiàn)在開始的5年內(nèi)，每月初存入2000元到銀行。如果銀行的年利率為12%，那么計(jì)算出第5年末該筆存款的未來值，張老師便能夠確定可以購(gòu)買多少價(jià)位的車子了。2.

情景二：PV函數(shù)的應(yīng)用張老師準(zhǔn)備現(xiàn)在貸款買一部車，其每月能承受的支付能力為2000元，如果銀行的貨款年利率為12%，貸款期為5年，即5年后還清貨款，計(jì)算張老師承受的最高貸款額是多少？現(xiàn)在買車能買多少價(jià)位的車？總結(jié)與提高利用投資計(jì)算函數(shù)分析身邊事趣味分析（欺騙廣告）

"最近，一些商家都這樣宣稱“來試一下我們的產(chǎn)品。如果你試了，我們將為你的光顧支付100元”。結(jié)果發(fā)現(xiàn)，商家所給的并不是現(xiàn)鈔100元，而是30年后支付100元的存款證書。如果該存款年利率是10%的話，現(xiàn)在他們真正能給你多少錢？8.3任務(wù)二：計(jì)算機(jī)本金和利息任務(wù)要求與效果生活中經(jīng)常遇到貸款買車、購(gòu)房等情景，那么根據(jù)個(gè)人每月收入情況怎么合理確定貸款年限、貸款金額以便在個(gè)人償還能力范圍中呢？利用PMT函數(shù)可以計(jì)算出每月的還款額，同進(jìn)也可利用PPMT，IPMT分別計(jì)算出還款額中的本金和利息。知識(shí)點(diǎn)解析本金是指未來各期年金現(xiàn)值的總和。而上節(jié)所說年金是在某一段連續(xù)時(shí)間內(nèi)一系列的固定相等金額給付活動(dòng)。1.PMT（）函數(shù)用途：基于固定利率及等額分期付款方式，返回貸款的每期付款額。語法：PMT(rate，nper，pv，fv，type)參數(shù)：Rate貸款利率，Nper該項(xiàng)貸款的付款期總數(shù)，Pv為現(xiàn)值(也稱為本金)，F(xiàn)v為未來值(或最后一次付款后希望得到的現(xiàn)金余額)，Type指定各期的付款時(shí)間是在期初還是期末(1為期初。0為期末)。知識(shí)點(diǎn)解析2.PPMT（）函數(shù)用途：基于固定利率及等額分期付款方式，返回投資在某一給定期間內(nèi)的本金償還額。語法：PPMT(rate，per，nper，pv，fv，type)參數(shù)：Rate為各期利率，Per用于計(jì)算其本金數(shù)額的期數(shù)(介于1到nper之間)，Nper為總投資期(該項(xiàng)投資的付款期總數(shù))，Pv為現(xiàn)值(也稱為本金)，F(xiàn)v為未來值，Type指定各期的付款時(shí)間是在期初還是期末(1為期初。0為期末)。3.IPMT（）函數(shù)用途：基于固定利率及等額分期付款方式，返回投資或貸款在某一給定期限內(nèi)的利息償還額。語法：IPMT(rate，per，nper，pv，fv，type)參數(shù)：Rate為各期利率，Per用于計(jì)算其利息數(shù)額的期數(shù)(1到nper之間)，Nper為總投資期，Pv為現(xiàn)值(本金)，F(xiàn)v為未來值(最后一次付款后的現(xiàn)金余額。如果省略fv，則假設(shè)其值為零)，Type指定各期的付款時(shí)間是在期初還是期末(0為期末，1為期初)。

任務(wù)實(shí)現(xiàn)步驟1.情景一：利用PMT函數(shù)計(jì)算還款額張老師計(jì)劃買一部200000元的車，他想到銀行貸款200000元，計(jì)劃分5年按月償還，年利率為12%，請(qǐng)計(jì)算在等額分期償還的情況下張老師每月的還款額。(假設(shè)為月初還款）2.情景二：利用IPMT、PPMT函數(shù)計(jì)算還款數(shù)額和利息張老師申請(qǐng)了200000元的按揭貸款并已經(jīng)買到心儀的靚車，貸款年利率為12%，計(jì)劃8年內(nèi)還清，請(qǐng)幫張老師分別計(jì)算他每年還款的本金數(shù)額和利息數(shù)額?？偨Y(jié)與提高制作貸款按揭動(dòng)態(tài)分析表。效果如圖張老師計(jì)劃購(gòu)買一套住房，需要向銀行貸款約15~30萬元，分10到30年時(shí)間還貸，還款執(zhí)行年利率為4.85%，，請(qǐng)制作一貸款按揭分析表幫張老師計(jì)算等額還款方式不同年份不同貸款額情況下各期每月所需按揭的費(fèi)用，并統(tǒng)計(jì)出還款總額及所還利息總數(shù)以便張老師選擇出合適的貸款額度及年限。8.4任務(wù)三、折舊計(jì)算函數(shù)應(yīng)用

任務(wù)要求與效果企業(yè)固定資產(chǎn)折舊有四種方法：即直線法、固定余額遞減法、雙倍余額遞減法、年數(shù)總和法。本節(jié)通過四個(gè)折舊函數(shù)（DB、DDB、SLN、SYD）的應(yīng)用的對(duì)比，比較四種折舊方法并制作出圖表進(jìn)行更直觀的對(duì)比。知識(shí)點(diǎn)解析折舊：企業(yè)固定資產(chǎn)的使用壽命是有限的在使用期間內(nèi)會(huì)因?yàn)橛行位驘o形的損耗而逐漸喪失其服務(wù)潛力所以有必要將其成本在有限的使用年限內(nèi)逐漸轉(zhuǎn)化為費(fèi)用這個(gè)過程就叫固定資產(chǎn)折舊。與固定資產(chǎn)折舊有關(guān)的幾項(xiàng)因素成本：固定資產(chǎn)取得的成本估計(jì)殘值：固定資產(chǎn)廢棄時(shí)可收回的材料價(jià)值或處置價(jià)值一般的扣除拆遷或處理費(fèi)用。估計(jì)使用年限：固定資產(chǎn)的總使用壽命、工作小時(shí)數(shù)或生產(chǎn)數(shù)量。企業(yè)在存活期間，不管是否生產(chǎn)、是否經(jīng)營(yíng)，其固定資產(chǎn)的使用都是有限的。在其使用年限內(nèi)，固定資產(chǎn)會(huì)逐漸損耗。企業(yè)固定資產(chǎn)折舊的方法有很多種，常用的有直線法、雙倍余額遞減法、年限總和法、固定余額遞減法等。知識(shí)點(diǎn)解析1.DB（）函數(shù)用途：使用固定余額遞減法，計(jì)算一筆資產(chǎn)在給定期間內(nèi)的折舊值。語法：DB(cost，salvage，life，period，month)參數(shù)：Cost為資產(chǎn)原值，Salvage為資產(chǎn)在折舊期末的價(jià)值(也稱為資產(chǎn)殘值)，Life為折舊期限(有時(shí)也稱作資產(chǎn)的使用壽命)，Period為需要計(jì)算折舊值的期間。Period必須使用與life相同的單位，Month為第一年的月份數(shù)(省略時(shí)假設(shè)為12)。說明:固定余額遞減法依據(jù)固定資產(chǎn)的估計(jì)使用年數(shù)按公式求出其折舊率每年以固定資產(chǎn)的帳面價(jià)值乘以折舊率來計(jì)算其當(dāng)年的折舊額。2.DDB（）函數(shù)用途：使用雙倍余額遞減法或其他指定方法，計(jì)算一筆資產(chǎn)在給定期間內(nèi)的折舊值。語法：DDB(cost，salvage，life，period，factor)參數(shù)：Cost為資產(chǎn)原值，Salvage為資產(chǎn)在折舊期末的價(jià)值(也稱為資產(chǎn)殘值)，Life為折舊期限(有時(shí)也稱作資產(chǎn)的使用壽命)，Period為需要計(jì)算折舊值的期間。Period必須使用與life相同的單位，F(xiàn)actor為余額遞減速率(如果factor省略，則假設(shè)為2)。說明：按照現(xiàn)行的會(huì)計(jì)制度規(guī)定，在采用雙倍余額遞減法計(jì)算折舊時(shí)，在最后兩年應(yīng)改為直線法，即將固定資產(chǎn)的賬面余額按兩年平均分?jǐn)?。知識(shí)點(diǎn)解析3.

SLN（）函數(shù)用途：返回某項(xiàng)資產(chǎn)在一個(gè)期間中的線性折舊值。語法：SLN(cost，salvage，life)參數(shù)：Cost為資產(chǎn)原值，Salvage為資產(chǎn)在折舊期末的價(jià)值(也稱為資產(chǎn)殘值)，Life為折舊期限(有時(shí)也稱作資產(chǎn)的使用壽命)。說明:該函數(shù)使用平均法每期提相同的折舊額返回一項(xiàng)資產(chǎn)每期的折舊費(fèi)4.SYD（）函數(shù)用途：返回某項(xiàng)資產(chǎn)按年限總和折舊法計(jì)算的指定期間的折舊值。語法：SYD(cost，salvage，life，per)參數(shù)：Cost為資產(chǎn)原值，Salvage為資產(chǎn)在折舊期末的價(jià)值(也稱為資產(chǎn)殘值)，Life為折舊期限(有時(shí)也稱作資產(chǎn)的使用壽命)，Per為期間(單位與life相同)。說明:SYD:年數(shù)總和法以固定資產(chǎn)的應(yīng)折舊總額乘以遞減的分?jǐn)?shù)其分母為使用年數(shù)的合計(jì)數(shù)分子則為各使用年次的相反順序求得各項(xiàng)的折舊額。

任務(wù)實(shí)現(xiàn)步驟某工廠購(gòu)買了一臺(tái)價(jià)值為80000元的設(shè)備，預(yù)計(jì)使用年限為10年，殘值率為10%，即殘值為8000元?，F(xiàn)采用四種折舊法計(jì)算該設(shè)備各年的折舊值（假設(shè)是1月份購(gòu)入的）。1.情景一：利用DB函數(shù)計(jì)算設(shè)備各年折舊值2.情景二：利用DDB函數(shù)計(jì)算設(shè)備各年折舊值3.情景三：利用SLN函數(shù)計(jì)算設(shè)備各年折舊值4.情景四：利用SYD函數(shù)計(jì)算設(shè)備各年折舊值總結(jié)與提高制作經(jīng)營(yíng)表：制作一產(chǎn)品折舊計(jì)算表為企業(yè)經(jīng)營(yíng)進(jìn)行核算。某家工廠購(gòu)進(jìn)一臺(tái)數(shù)控車床，價(jià)值2000000元，計(jì)劃使用15年，預(yù)計(jì)殘值為100000元，采用雙倍余額遞減法計(jì)提折舊，請(qǐng)計(jì)算15年內(nèi)每月計(jì)提多少折舊（按照現(xiàn)行的會(huì)計(jì)制度規(guī)定，在采用雙倍余額遞減法計(jì)算折舊時(shí)，在最后兩年應(yīng)改為直線法，即將固定資產(chǎn)的賬面余額按兩年平均分?jǐn)偅?，制作一產(chǎn)品折舊計(jì)算表為企業(yè)經(jīng)營(yíng)進(jìn)行核算8.5任務(wù)四、投資分析與決策相關(guān)函數(shù)應(yīng)用技巧任務(wù)要求與效果通過NPV、IRR的計(jì)算分析學(xué)生們的創(chuàng)業(yè)計(jì)劃是否可行。在本節(jié)中學(xué)習(xí)利用Excel2010內(nèi)置的NPV、IRR能及MIRR函數(shù)來進(jìn)行計(jì)算，不僅省時(shí)省力、準(zhǔn)確率高，還可以將財(cái)務(wù)人員從繁雜的手工勞動(dòng)中解放出來，把更多的時(shí)間投入到專業(yè)的思考和判斷上。知識(shí)點(diǎn)解析1.NPV（）函數(shù)用途：通過使用貼現(xiàn)率以及一系列未來支出(負(fù)值)和收入(正值)，返回一項(xiàng)投資的凈現(xiàn)值。語法：NPV(rate，value1，value2，...)參數(shù)：Rate為某一期間的貼現(xiàn)率，value1，value2，...為1到29個(gè)參數(shù)，代表支出及收入的金額。參數(shù)值value1、value2...所屬各期間的長(zhǎng)度必須相等而且支付及收入的時(shí)間都發(fā)生在期末。NPV按次序使用value1,value2來注釋現(xiàn)金流的次序。所以一定要保證支出和收入的數(shù)額按正確的順序輸入。2.IRR（）函數(shù)用途：返回由數(shù)值代表的一組現(xiàn)金流的內(nèi)部收益率。語法：IRR(values，guess)參數(shù)：values為數(shù)組或單元格的引用，包含用來計(jì)算返回的內(nèi)部收益率的數(shù)字。Guess為對(duì)函數(shù)IRR計(jì)算結(jié)果的估計(jì)值。3.MIRR（）函數(shù)用途：返回某一期限內(nèi)現(xiàn)金流的修正內(nèi)部收益率。語法：MIRR(values，finance_rate，reinvest_rate)參