Excel 2016數(shù)據(jù)處理與分析應(yīng)用教程 課件 第9章 財務(wù)分析函數(shù)及應(yīng)用

第9章財務(wù)分析函數(shù)及應(yīng)用

主要內(nèi)容9.1.1投資函數(shù)9.1.2利率函數(shù)9.1.3利息與本金函數(shù)9.1.4折舊函數(shù)9.1.1投資函數(shù)功能是計算不同形式的投資回報。Excel中常用的投資函數(shù)有：1.PV函數(shù)2.FV函數(shù)3.NPV函數(shù)4.XNPV函數(shù)9.1.1投資函數(shù)1.PV函數(shù)語法格式：PV(rate,nper,pmt[,fv,type])函數(shù)功能：基于固定利率計算投資的現(xiàn)值，或者說總額。參數(shù)說明：rate投資利率。nper投資總期數(shù)（期間數(shù)）。pmt各期支付金額。fv未來值或在最后一次付款后希望得到的現(xiàn)金余額，省略時默認其值為0。type付款時間類型，省略時默認其值為0（期末）。9.1.1投資函數(shù)

【例9-1】假設(shè)要購買一份保險理財產(chǎn)品，一次性投資30萬，投資回報率7%（年回報率），購買該理財產(chǎn)品后，可以在今后20年內(nèi)于每月底返還1500元。該投資合算嗎？

分析：表面看累計返還1500*12*20=36萬，投資可行，但考慮到資金的時間價值，需要將每月等額收款1500元，按照每月回報率（7%/12），折現(xiàn)期20*12月進行折現(xiàn)，看其現(xiàn)值是否大于初始投資金額，如果大于，該投資合算，否則不合算。9.1.1投資函數(shù)【例9-1】假設(shè)要購買一份保險理財產(chǎn)品，一次性投資30萬，投資回報率7%（年回報率），購買該理財產(chǎn)品后，可以在今后20年內(nèi)于每月底返還1500元。該投資合算嗎？PV(rate,nper,pmt[,fv,type])根據(jù)計算結(jié)果可知，該投資不合算9.1.1投資函數(shù)2.FV函數(shù)語法格式：FV(rate,nper,pmt[,fv,type])函數(shù)功能：基于固定利率及等額分期付款方式，計算某項投資在將來某個日期的價值（未來值）。參數(shù)說明：rate投資利率。nper投資總期數(shù)（期間數(shù)）。pmt各期支付金額。fv未來值或在最后一次付款后希望得到的現(xiàn)金余額，省略時默認其值為0。type付款時間類型，省略時默認其值為0（期末）。9.1.1投資函數(shù)【例9-2】假設(shè)一個家庭5年后需要一筆比較大的孩子教育費用支出，計劃從現(xiàn)在起每月初存入2000元，如果按年利率6％，按月計息，那么5年以后該賬戶的存款額應(yīng)該是多少呢？分析：按照每月利率6%/12，存期共5*12月，每月初等額存入2000元，現(xiàn)值為0進行計算。FV(rate,nper,pmt[,fv,type])9.1.1投資函數(shù)3.NPV函數(shù)語法格式：NPV(rate,value1[,value2,…]）函數(shù)功能：基于一系列現(xiàn)金流和固定的各期貼現(xiàn)率，計算一項投資的凈現(xiàn)值（當前純利潤）。凈現(xiàn)值越大，投資效益越好。參數(shù)說明：rate

某一期間的貼現(xiàn)率。value1，value2，…編號可以從1到254，代表支出或收入的現(xiàn)金流。要求所屬各期間的長度必須相等，而且都發(fā)生在期末，一定要保證支出和收入的數(shù)額按正確的順序輸入。9.1.1投資函數(shù)【例9-3】假設(shè)某用戶想開一家食品加工廠，打算初期投資20萬元，而希望未來4年中每年的收入分別為5萬元、10萬元、20萬元和40萬元。假定每年的貼現(xiàn)率是8%，問投資的凈現(xiàn)值是多少？分析：年貼現(xiàn)率為8%，第一筆20萬元付款發(fā)生在期初，所以不應(yīng)包含在value參數(shù)中。NPV(rate,value1[,value2,…]）9.1.1投資函數(shù)4.XNPV函數(shù)語法格式：XNPV(rate,values,dates)函數(shù)功能：計算一組現(xiàn)金流的凈現(xiàn)值，這些現(xiàn)金流不一定定期發(fā)生。參數(shù)說明：rate應(yīng)用于現(xiàn)金流的貼現(xiàn)率，所有后續(xù)付款都基于每年365天進行貼現(xiàn)。values一系列現(xiàn)金流。如果第一個values值是成本或付款，則它必須是負值，

而且values系列必須至少要包含一個正數(shù)和一個負數(shù)。dates與values中的現(xiàn)金流相對應(yīng)的付款時間。9.1.1投資函數(shù)【例9-4】假定某項投資需要在2019-1-1支付現(xiàn)金3萬元，并于下述時間獲取以下金額的返回資金：2019-7-1返回8750元；2020-1-1返回7250元；2020-7-1返回16250元；2020-12-31返回9750元。假設(shè)資金流轉(zhuǎn)貼現(xiàn)率為7％，則凈現(xiàn)值為多少？分析：年貼現(xiàn)率為7%，values參數(shù)對應(yīng)所有的金額，dates參數(shù)對應(yīng)相應(yīng)的日期。XNPV(rate,values,dates)9.1.2利率函數(shù)功能是計算不同形式的利率。常用的利率函數(shù)：1.RATE函數(shù)2.IRR函數(shù)3.MIRR函數(shù)4.XIRR函數(shù)9.1.2利率函數(shù)1.RATE函數(shù)語法格式：RATE(nper,pmt,pv[,fv,type,guess])函數(shù)功能：用于計算連續(xù)分期等額投資（或貸款）的利率，也可以計算一次性償還的投資（或貸款）利率。參數(shù)說明：nper總投資（或貸款）期數(shù)。pmt各期支付金額。pv現(xiàn)值（本金）。fv未來值或在最后一次付款后希望得到的現(xiàn)金余額，省略時默認其值為0。type付款時間類型，省略時默認其值為0（期末）。guess預(yù)期利率，省略時默認其值為10%。9.1.2利率函數(shù)【例9-5】假設(shè)某用戶計劃貸款8萬元裝修房子，貸款5年，月還款額為1600元，計算該筆貸款的月利率及年利率。分析：按照貸款期數(shù)共5*12個月，每月末等額償還1600元，貸款現(xiàn)值為8萬元計算月利率。RATE(nper,pmt,pv[,fv,type,guess])9.1.2利率函數(shù)

【例9-6】假設(shè)有人建議你給他投資10萬元，期限4年，那么是每年拿回3萬元收益合適還是4年后一次性拿回13萬元收益合適呢？分析：這實際上就是投資收益率多少的問題。（1）按照期數(shù)共4年，每年末等額投資收益3萬元，投資現(xiàn)值為10萬元計算投資收益率；（2）按照期數(shù)共4年，每年支付0元，投資現(xiàn)值為10萬元，未來值13萬元計算4年一次性的投資收益率。9.1.2利率函數(shù)【例9-6】假設(shè)有人建議你給他投資10萬元，期限4年，那么是每年拿回3萬元收益合適還是4年后一次性拿回13萬元收益合適呢？RATE(nper,pmt,pv[,fv,type,guess])

顯然，每年拿回3萬元收益回報率更高9.1.2利率函數(shù)2.IRR函數(shù)語法格式：IRR(values[,guess])函數(shù)功能：計算由數(shù)值代表的一組現(xiàn)金流的內(nèi)部收益率。參數(shù)說明：values一個數(shù)組或?qū)Π瑪?shù)值的單元格的引用，包含用來計算返回的內(nèi)部收益

率的數(shù)值。values值的順序與現(xiàn)金流的順序?qū)?yīng)。guess對函數(shù)IRR計算結(jié)果的估計值，省略時默認其值為10%。9.1.2利率函數(shù)【例9-7】某用戶計劃開一個食品廠，預(yù)計投資為11萬元，并預(yù)期今后5年的凈收益分別為：1.5萬元、2.1萬元、3萬元、4萬元和5.5萬元。分別求出投資2年、4年以及5年后的內(nèi)部收益率。分析：values參數(shù)對應(yīng)各個現(xiàn)金流，所以計算4年后的內(nèi)部收益率應(yīng)該包含初期投資和第1年、第2年、第3年、第4年的凈收益。IRR(values[,guess])9.1.2利率函數(shù)3.MIRR函數(shù)語法格式：MIRR(values,finance_rate,reinvest_rate)函數(shù)功能：計算某一連續(xù)期限內(nèi)現(xiàn)金流的修正內(nèi)部收益率，同時考慮了投資的成本和現(xiàn)金再投資的收益率。參數(shù)說明：values一個數(shù)組或?qū)Π瑪?shù)值的單元格的引用，包含各期的一系列支出及收入，

其中必須至少包含一個正值和一個負值，才能計算修正后的內(nèi)部收益率。finance_rate現(xiàn)金流中使用的資金支付的利率。reinvest_rate將現(xiàn)金流再投資的收益率。9.1.2利率函數(shù)【例9-8】如果貸款12萬元進行投資，5年的凈收益分別為：3.9萬元、3萬元、2.1萬元、3.7萬元和4.6萬元；12萬元貸款的年利率為10％，再投資收益的年利率為12％，則3年、5年后投資的修正收益率是多少？分析：values參數(shù)對應(yīng)各個現(xiàn)金流，所以計算5年后的修正收益率應(yīng)該包含初期投資和第1年、第2年、第3年、第4年、第5年的凈收益。MIRR(values,finance_rate,reinvest_rate)9.1.2利率函數(shù)4.XIRR函數(shù)語法格式：XIRR(values,dates[,guess])函數(shù)功能：計算一組現(xiàn)金流的內(nèi)部收益率，這些現(xiàn)金流不一定定期發(fā)生。參數(shù)說明：values與dates中的付款時間相對應(yīng)的一系列現(xiàn)金流。guess對函數(shù)XIRR計算結(jié)果的估計值，省略時默認其值為0.1（10%）。9.1.2利率函數(shù)【例9-9】假定在2018-1-1投資現(xiàn)金1萬元，并于下述時間獲取以下金額的返回資金：2018-3-1返回2750元；2018-10-30返回4250元；2019-2-15返回3250元；2019-4-1返回2750元，那么該筆投資的內(nèi)部收益率為多少？分析：values參數(shù)對應(yīng)所有的現(xiàn)金流，dates參數(shù)對應(yīng)相應(yīng)的付款日期。XIRR(values,dates[,guess])9.1.3利息與本金函數(shù)功能是計算不同形式的投資（或貸款）利息與本金。1.PMT函數(shù)2.IPMT函數(shù)3.PPMT函數(shù)4CUMPRINC函數(shù)5.CUMIPMT函數(shù)6.NPER函數(shù)9.1.3利息與本金函數(shù)1.PMT函數(shù)語法格式：PMT(rate,per,nper,pv[,fv,type])函數(shù)功能：基于固定利率及等額分期付款方式，計算投資（或貸款）的每期償還額。參數(shù)說明：rate投資（或貸款）利率。nper總投資（或貸款）期數(shù)。pv現(xiàn)值（本金）。fv未來值或在最后一次付款后希望得到的現(xiàn)金余額，省略時默認其值為0。type付款時間類型，省略時默認其值為0（期末）。9.1.3利息與本金函數(shù)【例9-10】某用戶準備向銀行貸款10萬元，期限為1年，假設(shè)銀行給的年利率為8%，采用等額分期還款方式，那么每月需要向銀行還多少錢？分析：按照每月利率為8%/12，共12個月，貸款現(xiàn)值10萬元進行計算。PMT(rate,per,nper,pv[,fv,type])9.1.3利息與本金函數(shù)2.IPMT函數(shù)語法格式：IPMT(rate,per,nper,pv[,fv,type])函數(shù)功能：基于固定利率及等額分期付款方式，計算投資貸款在某一給定期間內(nèi)的利息償還額。參數(shù)說明：rate投資或貸款利率。per要計算利息數(shù)額的期數(shù)，必須在1到nper之間。nper總投資（或貸款）期數(shù)。pv現(xiàn)值（本金）。fv未來值或在最后一次付款后希望得到的現(xiàn)金余額，省略時默認其值為0。type付款時間類型，省略時默認其值為0（期末）。9.1.3利息與本金函數(shù)【例9-11】某用戶在銀行貸款1萬元，假定年利率為10%，期限為5年，那該筆貸款在第一個月償還的利息是多少？在上述條件下貸款最后一年的利息（按年支付）又是多少？分析：要計算第一個月償還的利息，按照每月利率10%/12，第一個月即第1期，共5*12個月，貸款現(xiàn)值1萬元進行計算。要計算最后一年償還的利息，按照年利率10%，最后一年即為按年計算的第5期，共5年，貸款現(xiàn)值1萬元進行計算。IPMT(rate,per,nper,pv[,fv,type])9.1.3利息與本金函數(shù)3.PPMT函數(shù)語法格式：PPMT(rate,per,nper,pv[,fv,type])函數(shù)功能：基于固定利率及等額分期付款方式，計算投資（或貸款）在某一給定期間內(nèi)的本金償還額。參數(shù)說明：rate投資或貸款利率。per要計算利息數(shù)額的期數(shù)，必須在1到nper之間。nper總投資（或貸款）期數(shù)。pv現(xiàn)值（本金）。fv未來值或在最后一次付款后希望得到的現(xiàn)金余額，省略時默認其值為0。type付款時間類型，省略時默認其值為0（期末）。9.1.3利息與本金函數(shù)【例9-12】如果年利率為8％，貸款年限為2年，貸款額為20萬元，問貸款第一個月和第一年的本金償還額分別是多少？分析：要計算第一個月償還的本金，按照每月利率8%/12，第一個月即第1期，共2*12個月，貸款現(xiàn)值20萬元進行計算。要計算第一年償還的本金，按照年利率8%，第一年即為按年計算的第1期，共2年，貸款現(xiàn)值20萬元進行計算。PPMT(rate,per,nper,pv,[fv],[type])9.1.3利息與本金函數(shù)4.CUMPRINC函數(shù)語法格式：

CUMPRINC(rate,nper,pv,start_period,end_period,type)函數(shù)功能：計算投資（或貸款）在給定的start_period到end_period期間累計償還的本金數(shù)額。參數(shù)說明：rate投資或貸款利率。nper總投資（或貸款）期數(shù)。pv現(xiàn)值（本金）。start_period計算期間的首期（付款期數(shù)從1開始計數(shù)）。end_period計算期間的末期。type付款時間類型，省略時默認其值為0（期末）。9.1.3利息與本金函數(shù)【例9-13】某人在銀行貸款12.5萬元，假定年利率為9%，期限為30年，那該筆貸款在第一個月償還的本金和第二年（第13到24期）償還的全部本金分別是多少呢？分析：要計算第一個月償還的本金，按照每月利率9%/12，共30*12個月，貸款現(xiàn)值12.5萬元，首期1，末期1進行計算。要計算第二年償還的本金，按照每月利率9%/12，共30*12個月，貸款現(xiàn)值12.5萬元，首期13，末期24進行計算。

CUMPRINC(rate,nper,pv,start_period,end_period,type)9.1.3利息與本金函數(shù)5.CUMIPMT函數(shù)語法格式：CUMIPMT(rate,nper,pv,start_period,end_period,type)函數(shù)功能：返回一筆投資（或貸款）在給定的start_period到end_period期間累計償還的利息數(shù)額。參數(shù)說明：rate投資或貸款利率。nper總投資（或貸款）期數(shù)。pv現(xiàn)值（本金）。start_period計算期間的首期（付款期數(shù)從1開始計數(shù)）。end_period計算期間的末期。type付款時間類型，省略時默認其值為0（期末）。9.1.3利息與本金函數(shù)【例9-14】某人在銀行貸款12.5萬元，假定年利率為9％，期限為30年，那該筆貸款在第一個月償還的利息和第二年（第13到24期）要償還的全部利息分別是多少呢？分析：要計算第一個月償還的利息，按照每月利率9%/12，共30*12個月，貸款現(xiàn)值12.5萬元，首期1，末期1進行計算。要計算第二年償還的利息，按照每月利率9%/12，共30*12個月，貸款現(xiàn)值12.5萬元，首期13，末期24進行計算。CUMIPMT(rate,nper,pv,start_period,end_period,type)9.1.3利息與本金函數(shù)6.NPER函數(shù)語法格式：NPER(rate,pmt,pv[,fv,type])函數(shù)功能：計算按指定償還金額分期償還貸款的總期數(shù)。參數(shù)說明：

rate投資或貸款利率。

pmt每一期支付額。

pv現(xiàn)值（本金）。

fv未來值，省略時默認其值為0。

type付款時間類型，省略時默認其值為0（期末）。9.1.3利息與本金函數(shù)【例9-15】某用戶計劃從銀行貸款50萬元，年利率為6%，采用等額分期付款方式，每月可償還6000元，那么需要多久才能還清？分析：按照每月利率為6%/12，每期償還6000元，貸款現(xiàn)值50萬元進行計算。NPER(rate,pmt,pv[,fv,type])9.1.4折舊函數(shù)使用不同的方法計算資產(chǎn)折舊。具體選用哪種折舊方法，則須視情況而定。1.DB函數(shù)2.DDB函數(shù)3.VDB函數(shù)4.SLN函數(shù)5.SYD函數(shù)6.AMORDEGRC函數(shù)9.1.4折舊函數(shù)1.DB函數(shù)語法格式：DB(cost,salvage,life,period[,month])函數(shù)功能：使用固定余額遞減法，計算指定的任何期間內(nèi)的資產(chǎn)折舊值。參數(shù)說明：

cost資產(chǎn)原值。

salvage資產(chǎn)殘值。

life折舊期限（使用壽命）。

period需要計算折舊值的期間（必須使用與life相同的單位）。

month第一年的月份數(shù)，省略時默認其值為12。。9.1.4折舊函數(shù)【例9-16】某用戶于5年前的6月份花8500元購買了一臺計算機，現(xiàn)在報廢得到1000元，那么這臺計算機每年的折舊值分別是多少？分析：計算機原值8500元，殘值1000元，折舊期限5年，第一年使用的月份數(shù)是7。DB(cost,salvage,life,period[,month])9.1.4折舊函數(shù)2.DDB函數(shù)語法格式；DDB(cost,salvage,life,period[,factor])函數(shù)功能：使用雙倍余額遞減法，計算指定的任何期間內(nèi)的資產(chǎn)折舊值。參數(shù)說明：

cost資產(chǎn)原值。

salvage資產(chǎn)殘值。

life折舊期限（使用壽命）。

period需要計算折舊值的期間（必須使用與life相同的單位）。

factor余額遞減速率（折舊因子），省略時默認其值為2（雙倍余額遞減法）9.1.4折舊函數(shù)【例9-17】某用戶花10萬元購買了一臺小汽車從事出租運營，假定使用期限為10年，報廢價值為1萬元，分別計算第一天，第一個月、第一年、第三年各期間內(nèi)的折舊值。分析：汽車原值10萬元，殘值1萬元，折舊期限按天計算為10*365天，按月計算為10*12月，按年計算為10年。DDB(cost,salvage,life,period[,factor])9.1.4折舊函數(shù)3.VDB函數(shù)語法格式：VDB(cost,salvage,life,start_period,end_period[,factor,no_switch])函數(shù)功能：使用可變余額遞減法，計算指定的start_period到end_period期間內(nèi)的資產(chǎn)折舊值。參數(shù)說明：

cost資產(chǎn)原值。

salvage資產(chǎn)殘值。

life折舊期限（使用壽命）。

start_period進行折舊計算的起始期間（必須與1ife單位相同）。

end_period進行折舊計算的截止期間（必須與life單位相同）。

factor余額遞減速率（折舊因子），省略時默認其值為2（雙倍余額遞減法）。no_switch邏輯值，指定當折舊值大于余額遞減計算值時，是否轉(zhuǎn)用直線折舊法。9.1.4折舊函數(shù)【例9-18】某用戶花10萬元購買了一臺小汽車從事出租運營，假定使用期限為10年，報廢價值為1萬元，分別計算第一天、第一年、第6～15個月各期間內(nèi)的折舊值。VDB(cost,salvage,life,start_period,end_period[,factor,no_switch])分析：汽車原值10萬元，殘值1萬元，折舊期限按天計算為10*365天，按月計算為10*12月，按年計算為10年。9.1.4折舊函數(shù)4.SLN函數(shù)語法格式：SLN(cost,salvage,life)函數(shù)功能：基于直線折舊法計算某項資產(chǎn)每期的線性折舊值，即平均折舊值。參數(shù)說明：cost

資產(chǎn)原值。salvage資產(chǎn)殘值。life折舊期限（使用壽命）。9.1.4折舊函數(shù)【例9-19】某用戶要購買一輛價值30萬元的小汽車，假設(shè)其折舊年限為20年，殘值為2萬，那每年的線性折舊值是多少？？分析：汽車原值30萬元，殘值2萬元，折舊期限按年計算為20年。SLN(cost,salvage,life)9.1.4折舊函數(shù)5.SYD函數(shù)語法格式：SYD(cost,salvage,life,