1、 第十章 Excel在金融理財(cái)中的應(yīng)用 2掌握FV、PV、PMT、RATE等財(cái)務(wù)函數(shù)的使用方法, 達(dá)到舉一反三處理問題的能力理解復(fù)利、現(xiàn)值、未來值、等額本金、等額本息等相關(guān)概念熟練掌握銀行儲(chǔ)蓄、金融貸款中的相關(guān)概念，并能就實(shí)際問題提出解決方案能正確運(yùn)用EXCEL的數(shù)據(jù)模擬分析工具分析解決實(shí)際問題本章學(xué)習(xí)目標(biāo)2掌握FV、PV、PMT、RATE等財(cái)務(wù)函數(shù)的使用方法, 達(dá)3 目前Excel工具被廣泛應(yīng)用在金融計(jì)算中，Excel的公式、函數(shù)、圖表、假設(shè)分析、數(shù)據(jù)分析等功能已成為計(jì)算貨幣時(shí)間價(jià)值、投資決策指標(biāo)、債券，以及評(píng)價(jià)投資風(fēng)險(xiǎn)與收益等方面的不可缺少的工具。本節(jié)通過金融理財(cái)?shù)膶?shí)際案例，主要介紹Exc

2、el工具在銀行儲(chǔ)蓄收益計(jì)算方面的應(yīng)用。10.1 銀行儲(chǔ)蓄收益計(jì)算10.1.1 定額定投型儲(chǔ)蓄收益 在銀行提供的眾多理財(cái)方案中，像零存整取、教育儲(chǔ)蓄、養(yǎng)老保險(xiǎn)等都屬于定額定投型儲(chǔ)蓄方案，客戶在進(jìn)行投資時(shí)都希望預(yù)先知道未來值，從而選擇最優(yōu)方案，本節(jié)將介紹使用EXCEL函數(shù)FV計(jì)算未來值的方法。下面介紹FV函數(shù)相關(guān)知識(shí)要點(diǎn)。3 目前Excel工具被廣泛應(yīng)用在金融計(jì)算中，Exce4案例要求老王從銀行購買了一款保險(xiǎn)，約定每月向銀行存入2000元，連續(xù)繳存5年，期滿銀行一次性給付，年利率為6%，5年后老王可以拿到多少儲(chǔ)蓄收益。案例10-1計(jì)算定額定投型儲(chǔ)蓄收益FV格式：FV(rate,nper,pmt,p

3、v,type)功能：FV函數(shù)是基于固定利率及等額分期付款方式，計(jì)算某項(xiàng)投資在將來某個(gè)日期的價(jià)值，即未來值的函數(shù)。在日常工作和生活中，合理利用FV函數(shù)，可以幫助我們進(jìn)行一些有計(jì)劃、有目的、有效益的投資。4案例要求老王從銀行購買了一款保險(xiǎn)，約定每月向銀行存入2510.1.2 復(fù)利計(jì)算儲(chǔ)蓄收益 對(duì)于比較常見的復(fù)利計(jì)息問題，我們可以使用公式和函數(shù)兩種方法進(jìn)行計(jì)算。第一種方法，需先計(jì)算出每年的利息，并和期初余額相加，才能得到下一年的期初余額，再使用相同方法計(jì)算本年利息，直到期滿后的本利合計(jì)。第二種方法，使用EXCEL提供的FV函數(shù)進(jìn)行計(jì)算，可以直接得出期滿后的本利合計(jì)。比較兩種方法，第一種方法相對(duì)繁瑣，

4、但容易理解，第二種方法簡(jiǎn)單快捷，但需要理解函數(shù)中的相關(guān)參數(shù)。510.1.2 復(fù)利計(jì)算儲(chǔ)蓄收益 對(duì)于比較常見的復(fù)利計(jì)案例要求某公司向銀行存入10萬元，存款年限為10年，年利率為5%，銀行按照復(fù)利計(jì)息原則計(jì)算利息，10年后該公司可以拿到多少儲(chǔ)蓄收益。分析題意可以得到利率（rate=5%），期數(shù)（nper=10），現(xiàn)值（pv=100000），根據(jù)以上條件求10年后的終值（fv）。案例10-2復(fù)利計(jì)算儲(chǔ)蓄收益案例要求某公司向銀行存入10萬元，存款年限為10年，年利710.1.3 單筆投資計(jì)算復(fù)利現(xiàn)值 在給定的利率下，已知一筆資金在未來的終值，求出該資金目前的價(jià)值，這就是現(xiàn)值?，F(xiàn)值是未來的資金按照一定利

5、率折算而成的當(dāng)前值。計(jì)算現(xiàn)值的利率稱為貼現(xiàn)率。在投資分析領(lǐng)域，這種用貼現(xiàn)的方法計(jì)算投資方案現(xiàn)金流量現(xiàn)值的方法是一種基本的分析方法。通常稱為貼現(xiàn)現(xiàn)金流量法。 現(xiàn)值計(jì)算是終值計(jì)算的反向過程，故兩者之間存在著對(duì)應(yīng)關(guān)系。同時(shí)現(xiàn)值和終值還受利率和期數(shù)兩個(gè)因素的影響。在Excel中提供了專門計(jì)算現(xiàn)值的PV函數(shù)，下面介紹該函數(shù)相關(guān)知識(shí)要點(diǎn)。710.1.3 單筆投資計(jì)算復(fù)利現(xiàn)值 在給定的利率下，8PV格式：PV(rate,nper,pmt,fv,type)功能：PV函數(shù)用來計(jì)算某項(xiàng)投資的現(xiàn)值?，F(xiàn)值就是未來各期年金現(xiàn)在價(jià)值的總和，如果投資回收的當(dāng)前價(jià)值大于投資的價(jià)值，則這項(xiàng)投資是有收益的。說明：參數(shù)rate，n

6、per，pmt，type參見FV函數(shù)，參數(shù)fv為投資的未來值或最后一次支付（或收入）后的希望得到的現(xiàn)金余額，如果省略 fv，則假設(shè)其值為零。案例要求小張計(jì)劃5年后投資60萬元買房，現(xiàn)在投資收益率為5%，那現(xiàn)在需要準(zhǔn)備多少本金？分析題意可以得到利率（rate=5%），期數(shù)（nper=5），5年后的終值（fv=600000），根據(jù)以上條件求現(xiàn)值（pv）。案例10-3單筆投資計(jì)算復(fù)利現(xiàn)值8PV案例要求小張計(jì)劃5年后投資60萬元買房，現(xiàn)在投資收910.1.4 定額定期計(jì)算復(fù)利付款 本節(jié)案例和案例3給出了相同的已知條件，看似沒有區(qū)別，實(shí)則一個(gè)是整存整取，一個(gè)是零存整取。一個(gè)是求現(xiàn)值（pv），一個(gè)是求每期

7、付款額（pmt）。在Excel中提供了專門計(jì)算每期付款額的PMT函數(shù)，下面介紹該函數(shù)相關(guān)知識(shí)要點(diǎn)。PMT格式：PMT(rate,nper,pmt,fv,type)功能：PMT函數(shù)是基于固定利率及等額分期付款方式，根據(jù)貸款利率、定期付款和貸款金額返回投資或貸款的每期付款額。說明：參數(shù)參見FV、PV 函數(shù)。910.1.4 定額定期計(jì)算復(fù)利付款 本節(jié)案例和案例310案例要求小張計(jì)劃10年后投資60萬元買房，現(xiàn)在投資收益率為5%，如果采取定額定期的方式投資，每月需要向銀行存入多少錢。分析題意可以得到利率（rate=5%），期數(shù)（nper=1012），10年后的終值（fv=600000），根據(jù)以上條件求

8、各期支付金額（pmt）。案例10-4定額定期計(jì)算復(fù)利付款10.1.5 浮動(dòng)利率儲(chǔ)蓄收益 針對(duì)利率會(huì)隨著社會(huì)的經(jīng)濟(jì)狀況以及政策調(diào)整出現(xiàn)浮動(dòng)的情況，我們?nèi)匀豢梢允褂霉街鹉暧?jì)算，最終得到未來值。在Excel中提供了專門計(jì)算浮動(dòng)利率未來值的FVSCHEDULE函數(shù)，使我們可以快速計(jì)算出浮動(dòng)利率下的未來值，下面介紹該函數(shù)相關(guān)知識(shí)要點(diǎn)。10案例要求小張計(jì)劃10年后投資60萬元買房，現(xiàn)在投資收11FVSCHEDULE格式：FVSCHEDULE (principal, schedule)功能：FVSCHEDULE函數(shù)用于計(jì)算某項(xiàng)投資在變動(dòng)利率或可調(diào)利率下的未來值。說明：參數(shù)principal為投資或貸款的期

9、初價(jià)值。參數(shù)schedule為利率數(shù)組，通常為變動(dòng)利率單元格區(qū)域。11FVSCHEDULE1210.2.1 等額本金分期還款計(jì)算 銀行貸款的還款方式主要包括等額本金還款和等額本息還款。10.2 貸款方案計(jì)算 目前，在現(xiàn)實(shí)的經(jīng)濟(jì)生活中，貸款消費(fèi)已經(jīng)成為人們個(gè)人理財(cái)?shù)闹匾獌?nèi)容。例如，助學(xué)貸款、購房貸款、購車貸款以及裝修貸款等方面。針對(duì)人們的不同貸款需求，各金融機(jī)構(gòu)推出了不同種類的貸款業(yè)務(wù)，人們?nèi)绾胃鶕?jù)自己的還款能力，和金融機(jī)構(gòu)貸款業(yè)務(wù)的政策，以及需求情況來優(yōu)選貸款方案，是本節(jié)要解決的問題。1210.2.1 等額本金分期還款計(jì)算 銀行貸款的還款131.等額本金等額本金是指一種貸款的還款方式，是在還款

10、期內(nèi)把貸款數(shù)總額等分，每月償還同等數(shù)額的本金和剩余貸款在該月所產(chǎn)生的利息。2.等額本息把貸款的本金總額與利息總額相加，然后平均分?jǐn)偟竭€款期限的每個(gè)月中。131.等額本金14案例要求小張購房向銀行貸款30萬元，貸款利率為6%，還款期限為5年，還款方式為等額本金，小張?jiān)谫J款期間的期初余額、還款本金、利息、本金余額、每月還款數(shù)各是多少。分析題意可以得到期數(shù)（nper=512），現(xiàn)值（pv=300000），利率（rate=6%），根據(jù)以上條件求期初余額、還款本金、利息、本金余額、每月還款數(shù)。案例10-6等額本金分期還款計(jì)算14案例要求小張購房向銀行貸款30萬元，貸款利率為6%，1510.2.3 等額本

11、息分期還款計(jì)算 等額本息還款方式，要求每期還款額是一樣的，但由于期滿后的未來值未知，故不能簡(jiǎn)單的使用公式來進(jìn)行計(jì)算。在Excel中提供了專門計(jì)算貸款的一組函數(shù)，下面介紹相關(guān)知識(shí)要點(diǎn)。1. PPMT格式：PPMT(rate,per,nper,pv,fv,type)功能：PPMT函數(shù)是基于固定利率及等額分期付款方式，返回投資在某一給定期間內(nèi)的本金償還額。說明：參數(shù)Rate，nper，pv，fv，type參見FV、PV函數(shù)。參數(shù)Per是用于計(jì)算其本金數(shù)額的期數(shù)，必須介于 1 到 nper 之間。1510.2.3 等額本息分期還款計(jì)算 等額本息還款方162. CUMPRINC格式：CUMPRINC(r

12、ate，nper，pv，start_period，end_period，type)功能：CUMPRINC函數(shù)返回一筆貸款在給定的start-period到end-period期間累計(jì)償還的本金數(shù)額。說明：參數(shù)rate，nper，pv， type參見FV、PV函數(shù)。 參數(shù)Start_period 為計(jì)算本金（或利息）的首期期數(shù)，付款期數(shù)從 1 開始計(jì)數(shù)。 參數(shù)End_period 為計(jì)算本金（或利息）的末期期數(shù)。該值小于等于nper參數(shù)值。3. IPMT格式：IPMT(rate,per,nper,pv,fv,type)功能：IPMT函數(shù)基于固定利率及等額分期付款方式，返回給定期數(shù)內(nèi)對(duì)投資的利息償

13、還額。說明：參數(shù)Rate，per，nper，pv，fv，type參見FV、PV函數(shù)。162. CUMPRINC174. CUMIPMT格式：CUMIPMT(rate, nper, pv, start_period, end_period, type)功能：CUMIPMT函數(shù)用于計(jì)算一筆貸款在給定的 start_period 到 end_period 期間累計(jì)償還的利息數(shù)額。說明：參數(shù)rate，nper，pv，start_period，end_period，type參見FV、PV、CUMPRINC函數(shù)。174. CUMIPMT18案例要求小張購房向銀行貸款30萬元，貸款利率為6%，還款期限為5年

14、，還款方式為等額本息，小張?jiān)谫J款期間的期初余額、還款本金、利息、本金余額、每月還款數(shù)各是多少。分析題意可以得到期數(shù)（nper=512），現(xiàn)值（pv=300000），利率（rate=6%），根據(jù)以上條件求期初余額、還款本金、利息、本金余額、每月還款數(shù)。案例10-7等額本息分期還款計(jì)算18案例要求小張購房向銀行貸款30萬元，貸款利率為6%，19Nper格式：nper (rate,pmt,pv,fv,type)功能：nper函數(shù)指定定期定額支付且利率固定的年金總期數(shù)。說明：參數(shù)Rate，pmt，pv，fv，type參見FV、PV函數(shù)。10.2.3 等額本息還款期數(shù)計(jì)算 本節(jié)將介紹給定貸款金額、貸款利

15、率、每月還款數(shù)等信息，如何計(jì)算還款期數(shù)。此種情況在實(shí)際生活中很常見，可以根據(jù)本人的實(shí)際經(jīng)濟(jì)情況，合理做出調(diào)配，選擇最佳的貸款方案。在Excel中提供了專門計(jì)算貸款期數(shù)的Nper函數(shù)，下面介紹該函數(shù)相關(guān)知識(shí)要點(diǎn)。19Nper10.2.3 等額本息還款期數(shù)計(jì)算 本節(jié)將20案例要求小張購房向銀行貸款10萬元，貸款利率為6%，計(jì)劃每月還款2000元，還款方式為等額本息，小張需要還貸多少期，在貸款期間的期初余額、還款本金、利息、本金余額各是多少。分析題意可以得到現(xiàn)值（pv=300000），利率（rate=6%），每期還款數(shù)（pmt=2000），根據(jù)以上條件求期數(shù)（nper）。案例10-8等額本息還款期數(shù)

16、計(jì)算20案例要求小張購房向銀行貸款10萬元，貸款利率為6%，21RATE格式：rate (nper,pmt,pv,fv,type,guess)功能：RATE函數(shù)用于計(jì)算投資或貸款的各期利率?？梢杂?jì)算連續(xù)分期等額投資或貸款的利率，也可以計(jì)算一次性償還的投資或貸款利率。說明：參數(shù)nper，pmt，pv，fv，type參見FV、PV函數(shù)。參數(shù)guess為預(yù)期利率。如果省略預(yù)期利率，則假設(shè)該值為10%。10.2.4 貸款利率計(jì)算 Excel常用的利率函數(shù)有RATE、IRR、MIRR、XIRR，這些函數(shù)用于計(jì)算不同形式的利率。RATE常用于計(jì)算投資或貸款的各期利率，下面介紹該函數(shù)相關(guān)知識(shí)要點(diǎn)。21RAT

17、E10.2.4 貸款利率計(jì)算 Excel常用22案例要求小張裝修房屋向銀行貸款3萬元，貸款期限為2年，每月還款2000元，該筆貸款的月利率和年利率各是多少。分析題意可以得到現(xiàn)值（pv=30000），貸款期限（nper=2），每期還款數(shù)（pmt=2000），根據(jù)以上條件求利率（rate）。案例10-9貸款利率計(jì)算22案例要求小張裝修房屋向銀行貸款3萬元，貸款期限為2年2310.3.1 不同年限存款比較 銀行貸款的還款方式主要包括等額本金還款和等額本息還款。10.3 理財(cái)方案比較 本節(jié)將通過3個(gè)案例對(duì)銀行存款方案、投資方案和購房貸款方案等理財(cái)方案進(jìn)行比較，為客戶做出決策判斷提供參考。案例要求老王到銀行存款，銀行員工向他推薦了幾種方案，但由于年限不同，利率也不相同，導(dǎo)致得到的利息也不一樣，老王希望設(shè)計(jì)一張表進(jìn)行比較。案例10-10不同年限存款比較2310.3.1 不同年限存款比較 銀行貸款的還款方式2410.3.2 投資方案比較 銀行貸款的還款方式主要包括等額本金還款和等額本息還款。案例要求老王想從銀行購買一款保險(xiǎn)，購買成本為10萬元，且在今后20年內(nèi)每月可以回報(bào)800元，假設(shè)投資收益率為8%，幫老王算算這是一個(gè)合算的