
【摘要】在日常生活中,人們?cè)絹碓蕉嗟赝y行的存貸業(yè)務(wù)打交道,如住房貸款、汽車貸款、教育貸款及個(gè)人儲(chǔ)蓄等。但很多人對(duì)某一貸款的月償還金額的計(jì)算或利息計(jì)算往往感到束手無策,Excel提供的PMT函數(shù)是完成這一任務(wù)的好工具。本文介紹利用Excel的PMT函數(shù)及雙變量模擬運(yùn)算表計(jì)算在“還款期數(shù)”和“貸款本金”兩個(gè)參數(shù)同時(shí)變化的情況下“貸款的每期(月)償還額”,具有一定的實(shí)用價(jià)值。
貸款購(gòu)房是時(shí)下的熱門話題之一,隨著銀行信貸業(yè)務(wù)的廣泛開展,貸款購(gòu)房成為多數(shù)家庭購(gòu)房時(shí)選擇的方案。但是,由于購(gòu)房舉貸數(shù)額大,貸款周期長(zhǎng),部分家庭在利用抵押貸款方式購(gòu)買住房時(shí)會(huì)因?yàn)榭紤]不周而造成還貸困難甚至嚴(yán)重影響正常生活的尷尬局面。那么如何根據(jù)自己的還款能力制定一個(gè)切實(shí)可行的購(gòu)房貸款計(jì)劃呢?我們可以利用Excel提供的PMT函數(shù)以及雙變量模擬運(yùn)算表做一個(gè)購(gòu)房貸款方案表,從中選擇適合自己的一套方案,這樣就不會(huì)因?yàn)檫€貸而影響正常生活了。
一、PMT函數(shù)
Excel提供了PMT函數(shù),PMT函數(shù)是基于固定利率及等額分期付款方式。 PMT函數(shù)可以計(jì)算為償還一筆貸款,要求在一定周期內(nèi)支付完時(shí),每次需要支付的償還額,也就是我們平時(shí)所說的“分期付款”。購(gòu)房貸款或其它貸款時(shí),可以用PMT函數(shù)計(jì)算貸款的每期(月)償還額。
PMT函數(shù)的格式為:
PMT(rate,nper,pv,fv,type),返回值為“投資或貸款的每期(月)償還額”。
Rate必要。Double指定每一期的貸款利率。例如,如果有一筆貸款年百分比率(APR)為百分之十且按月付款的汽車貸款,則每一期的利率為0.1/12或0.0083。
Nper必要。Integer指定一筆貸款的還款期數(shù)。例如,如果對(duì)一筆為期四年的汽車貸款選擇按月付款,則貸款共有4×12(或48)個(gè)付款期。
Pv必要。Double現(xiàn)值或一系列未來付款的當(dāng)前值的累積和,也稱為本金。例如,當(dāng)貸款買一輛汽車時(shí),向貸方所借貸的金額為將來每月償付給貸方款項(xiàng)的現(xiàn)值。
Fv可選。Variant指定在付清貸款后所希望的未來值或現(xiàn)金結(jié)存。例如,貸款的未來值在貸款付清后為0元。但是,如果想要在8年間存下50000元作為子女教育基金,那么50000元為未來值。如果省略的話,缺省值為0。
Type可選。Integer如果貸款是在貸款周期結(jié)束時(shí)到期,請(qǐng)使用0;如果貸款是在周期開始時(shí)到期,則請(qǐng)使用1;如果省略的話,缺省值為0。
為了便于理解與操作,我們可以把PMT函數(shù)簡(jiǎn)化成如下形式:
PMT(貸款利率、還款期數(shù)、貸款本金),返回值為投資或貸款的每期(月)償還額。
說明:
第一,PMT返回的支付款項(xiàng)包括本金和利息,但不包括稅款、保留支付或某些與貸款有關(guān)的費(fèi)用。
第二,應(yīng)確認(rèn)所指定的“貸款利率”和“還款期數(shù)”單位的一致性。例如,同樣是四年期年利率為12%的貸款,如果按月支付,“貸款利率”應(yīng)為12%/12,“還款期數(shù)”應(yīng)為4×12;如果按年支付,“貸款利率”應(yīng)為12%,“還款期數(shù)”為4。
第三,對(duì)所有參數(shù),用負(fù)數(shù)表示現(xiàn)金支出(如儲(chǔ)蓄存款),而用正數(shù)表示現(xiàn)金收入(如紅利支票)。
二、雙變量模擬運(yùn)算表
所謂模擬運(yùn)算表實(shí)際上是Excel工作表中的一個(gè)單元格區(qū)域,它可以顯示一個(gè)計(jì)算公式中某些參數(shù)的值的變化對(duì)計(jì)算結(jié)果的影響。它可以將所有不同的計(jì)算結(jié)果以列表方式同時(shí)顯示出來,因而便于查看、比較和分析。根據(jù)分析計(jì)算公式中參數(shù)的個(gè)數(shù),模擬運(yùn)算表又分為單變量模擬運(yùn)算表和雙變量模擬運(yùn)算表。當(dāng)需要其它因素不變,計(jì)算兩個(gè)參數(shù)的變化對(duì)目標(biāo)值的影響時(shí),需要使用雙變量模擬運(yùn)算表。
雙變量模擬運(yùn)算表就是考慮兩個(gè)變量的變化對(duì)公式計(jì)算結(jié)果的影響,在財(cái)務(wù)管理中應(yīng)用最多的是長(zhǎng)期借款雙變量分析模型,筆者利用雙變量模擬運(yùn)算表在PMT函數(shù)中讓“還款期數(shù)”和“貸款本金”兩個(gè)參數(shù)同時(shí)為變量,然后計(jì)算各種情況下“貸款的每期(月)償還額”。
雙變量模擬運(yùn)算表的操作步驟:
選擇某個(gè)單元格區(qū)域作為模擬運(yùn)算表存放區(qū)域,在該區(qū)域的最左列輸入假設(shè)的還款期數(shù)范圍數(shù)據(jù);在該區(qū)域的第一行輸入可能的貸款本金。
在模擬運(yùn)算表區(qū)域的左上角單元格輸入計(jì)算“貸款每期(月)償還額”的計(jì)算公式:=PMT(貸款利率,還款期數(shù),貸款本金)
三、利用雙變量模擬運(yùn)算表進(jìn)行購(gòu)房貸款方案決策
一般購(gòu)房者在選購(gòu)住房時(shí)要考慮諸多因素,例如房?jī)r(jià)、按揭年限等,在眾多方案中選擇適合自己的方案。下面我們通過一個(gè)例子來具體說明。假設(shè)某人想通過貸款購(gòu)房改善自己的居住條件,可供選擇的房?jī)r(jià)有20萬元、30萬元、40萬元、50萬元、60萬元、80萬元和100萬元;可供選擇的按揭方案有5年、10年、15年、20年和30年。由于收入的限制,其每月還款額(以下稱為月供金額)最高不能超過3000元,但也不要低于2000元,已知銀行貸款利率為6%。現(xiàn)用雙變量模擬運(yùn)算表幫助其選擇貸款方案,方法如下:
?、傩陆ㄒ籈xcel工作簿,打開一張工作表,在B2單元格輸入房?jī)r(jià)600000(此單元格將被設(shè)置為行變量),在B3單元格建立公式計(jì)算月利率:=6%/12(結(jié)果為0.5%),在B4單元格建立公式計(jì)算5年按揭的月份數(shù):=5×12(結(jié)果為60)(此單元格將被設(shè)置為列變量)。
?、谠贑6∶I6區(qū)域輸入不同房?jī)r(jià),在B7∶B11區(qū)域輸入不同按揭年數(shù)的月份數(shù)。
?、墼贐6單元格建立公式:=PMT(B3,B4,B2),回車確認(rèn),即可在B6單元格得到房?jī)r(jià)60萬元5年按揭的月供金額。(①②③后結(jié)果如上圖1所示)
?、苓x取區(qū)域B6∶I11,建立模擬運(yùn)算表。選擇“數(shù)據(jù)”→“模擬運(yùn)算表”命令,打開“模擬運(yùn)算表”對(duì)話框。
?、莘謩e指定$B$2為“引用行的單元格”(即行變量),$B$4為“引用列的單元格”(即列變量),如下圖2所示,單擊“確定”按鈕,隨后,在C7∶I11區(qū)域便顯示不同還款期限、不同房?jī)r(jià)的房屋月供金額,如上圖3所示。例如:F9單元格的數(shù)值表示50萬元房?jī)r(jià)、15年按揭的月供金額。
?、薰ぷ鞅碇杏?套方案滿足月供不超過3000元同時(shí)也不低于2000元的條件,可供購(gòu)房時(shí)選擇,如圖3中粗線框起的部分。
四、結(jié)束語
在市場(chǎng)經(jīng)濟(jì)的今天,投資活動(dòng)越來越頻繁,人們對(duì)不同的投資方案進(jìn)行分析比較也就顯得愈發(fā)重要。筆者以購(gòu)房貸款為例,利用Excel的PMT函數(shù)并結(jié)合雙變量模擬運(yùn)算表對(duì)投資方案進(jìn)行分析比較,為投資決策提供依據(jù),此方法在實(shí)際工作和生活中具有很好的實(shí)用價(jià)值。Excel是一個(gè)應(yīng)用廣泛的電子表格軟件,盡管人們認(rèn)識(shí)與使用它已有十多年的歷史,但認(rèn)識(shí)與使用它的深度與廣度都有待提高。現(xiàn)成的財(cái)務(wù)管理軟件盡管操作比較簡(jiǎn)單,但它能解決的問題是有限的。解決問題的模式是固定的,而用Excel解決這些問題恰恰能夠彌補(bǔ)這些缺點(diǎn)。Excel在工資核算、財(cái)務(wù)處理、報(bào)表編制、固定資產(chǎn)核算、資金的時(shí)間價(jià)值計(jì)算、籌資決策、項(xiàng)目投資決策、投資項(xiàng)目不確定性風(fēng)險(xiǎn)分析、證券投資、營(yíng)運(yùn)資金管理、財(cái)務(wù)預(yù)測(cè)、財(cái)務(wù)預(yù)算、財(cái)務(wù)分析、企業(yè)并購(gòu)等方面都有很好的應(yīng)用,利用Excel進(jìn)行財(cái)務(wù)管理與分析靈活、方便。
本文中所涉及到的圖表、注解、公式等內(nèi)容請(qǐng)以PDF格式閱讀原文。