《會(huì)計(jì)之友》2007年第6期上刊登了蔣秀蓮、宋言東等同志的《利用Excel雙變量模擬運(yùn)算表進(jìn)行購(gòu)房貸款決策》(以下簡(jiǎn)稱“蔣文”)一文,筆者認(rèn)為,該文實(shí)用價(jià)值頗大,利用 “蔣文”中的模型,根據(jù)不同房?jī)r(jià)、不同期限來(lái)選擇房貸方式,解決了不同收入層次的人們的房貸選擇問(wèn)題。但是貸款方案一旦確定,人們更想知道的信息是到一定時(shí)間為止共償還了多少貸款,如果提前還貸,還需向銀行支付多少貸款。本文在“蔣文”的基礎(chǔ)上以案例方式對(duì)分期償還貸款進(jìn)行了明細(xì)分析。
一、案例的提出
2006年12月1日,甲從中國(guó)銀行申請(qǐng)住房商業(yè)貸款15萬(wàn)元,貸款年限15年,采用等額本息付款方式按月償還貸款,其間中國(guó)銀行利率變化了四次:2006年利率為3.5127%,2007年利率為6.156%,2008年利率為6.9977%,2009年利率為4.4254%(注:前三年的利率在基準(zhǔn)利率上打九折,2009年的利率在基準(zhǔn)利率上打七折)。問(wèn):
1.每年的月償還額為多少元?
2.至2009年底,甲共償還多少貸款?其中本金多少?利息多少?如果這時(shí)想提前償還貸款,還需向銀行支付多少元?
二、模型的建立
由于知道現(xiàn)值(150 000元),利率(變動(dòng)利率)和期限(15年),求每年支付的金額(年金),可以用時(shí)間價(jià)值函數(shù)中的年金函數(shù)(PMT)、年金中的本金函數(shù)(PPMT)和年金中的利息函數(shù)(IPMT)來(lái)分別計(jì)算月償還額、月償還額中的本金和利息,最后再用Excel中的常用計(jì)算函數(shù)求出累計(jì)償還額、累計(jì)償還本金和利息,以及剩余貸款金額。
(一)建立分期償還貸款分析模型表
創(chuàng)建一個(gè)新的工作簿,將其命名為“貸款償還分析表”,在工作簿中選擇一工作表,并將該工作表重命名為“分期償還貸款明細(xì)分析表”,在此工作表上建立“分期償還貸款分析模型”,如表1:
各項(xiàng)目的公式設(shè)置如下:
總付款期數(shù):C8=C6*C7
每期償還金額:C9==ABS(PMT(C5/C7,C8,C4))
該模型建立之后,每期償還金額與貸款金額、貸款年利率、貸款年限、每年還款期數(shù)等因素之間建立了動(dòng)態(tài)鏈接,可以通過(guò)直接輸入數(shù)據(jù)的方式改變貸款金額、貸款年利率、貸款年限、每年還款期數(shù)中的任意一個(gè)或幾個(gè)因素的值,來(lái)觀察每期償還金額的變化,選擇一種當(dāng)前能力所及的固定償還金額進(jìn)行貸款。“蔣文”是把貸款金額和利率變化的多種結(jié)果綜合顯示到一張表上,便于決策,而本文討論的重點(diǎn)不是決策,而是決策后需要了解的相關(guān)信息。為了便于貸款額的明細(xì)分析,文中只顯示一種結(jié)果,表1顯示的結(jié)果就是案例中2006年的月償還額1 073.26元。如果要求以后年度的月償還額,得用下面的模型。
(二)建立分期償還貸款雙變量分析表
在“蔣文”中,由于是對(duì)貸款進(jìn)行決策,所以其選用的雙變量為“利率”和“貸款額”。在實(shí)際中,一旦決策確定,貸款深受利率和期限的影響,貸款期限的長(zhǎng)短可能影響其貸款利率,貸款利率的變動(dòng)又對(duì)貸款分析產(chǎn)生較大的影響。為了觀察兩個(gè)因素不同組合下的每期償還額,需要借助模擬運(yùn)算表建立雙變量分析表來(lái)達(dá)到目的,本文選用的雙變量為“利率”和“期限”。
1.目標(biāo)函數(shù)的輸入
在行與列的交叉單元A13輸入目標(biāo)函數(shù):= ABS(PMT(C5/C7,C8,C4)),設(shè)置一個(gè)雙變量數(shù)據(jù)表,用13行的各種總付款期數(shù)替換第一個(gè)變量(行變量C8,即總付款期數(shù))的值,并且用A列的各種年利率替換第二個(gè)變量(列變量C5,即年利率)的值,與此對(duì)應(yīng)的每期償還金額將放在第14行下面,A列右方的單元區(qū)域中,如表2:
2.雙變量分析表值的計(jì)算
選擇A13至F21單元區(qū)域,在菜單欄“數(shù)據(jù)”下選擇“模擬運(yùn)算表”,在“輸入引用行的單元格”中輸入$C$8,在“輸入引用列的單元格”中輸入$C$5,確定,即可看到不同利率、不同期限下的月償還額,如表3所示:
通過(guò)該模型,可以觀察兩個(gè)因素不同組合下的貸款分析的結(jié)果。當(dāng)貸款各因素的值發(fā)生變化時(shí),只需改變第一變量或第二變量所在行和列的值或其他因素的值,系統(tǒng)就會(huì)自動(dòng)重新計(jì)算雙變量分析表中的所有值。在案例中 ,多種不同利率下15期貸款月償還額對(duì)應(yīng)的值為第D列,則甲2006年12月應(yīng)還的貸款額為1 073.26元,2007年每月應(yīng)還的貸款額為1 278.46元。2008年每月應(yīng)還的貸款額為
1 348.05元,2009年每月應(yīng)還的貸款額為1 141.78元。如果以后利率有變化,只需調(diào)整第A列A17以下單元格的利率則可以觀察到不同的月償還額。此時(shí),案例中的第一個(gè)問(wèn)題已解決。
(三)建立分期償還貸款明細(xì)分析表
在分期償還貸款雙變量分析表中,可以觀察到不同期限和利率組合下的月償還額,但如果想知道目前還了多少貸款,還剩多少貸款沒還清,則需要通過(guò)明細(xì)分析表來(lái)實(shí)現(xiàn)。在本案例中,由于中國(guó)銀行2006年至2009年利率變動(dòng)了四次,所以在計(jì)算每期償還本金和利息時(shí)需分別計(jì)算,公式設(shè)置如下:
1.還款額(第J列)公式的設(shè)置
2006年每月還款額J3=D14;
2007年每月還款額J4=$D$15,利用自動(dòng)填充功能將公式復(fù)制到J5至J15單元格中;
2008年每月還款額J16=$D$16,利用自動(dòng)填充功能將公式復(fù)制到J7至J27單元格中;
2009年每月還款額J28=$D$17,利用自動(dòng)填充功能將公式復(fù)制到J29至J39單元格中。
2.償還本金(第K列)公式的設(shè)置
2006年每月償還本金K3= -PPMT($A$14/12,I3,180,$C$4);
2007年每月償還本金K4= -PPMT($A$15/12,I4,180,$C$4),利用自動(dòng)填充功能將公式復(fù)制到K5至K15單元格中;
2008年每月償還本金K16= -PPMT($A$16/12,I16,180,$C$4),利用自動(dòng)填充功能將公式復(fù)制到J17至J27單元格中;
2009年每月償還本金K28= -PPMT($A$17/12,I28,180,$C$4),利用自動(dòng)填充功能將公式復(fù)制到J29至J39單元格中。
3.償還利息(第L列)公式的設(shè)置
2006年每月償還本金L3= -IPMT($A$14/12,I3,180,$C$4);
2007年每月償還本金L4= -IPMT($A$15/12,I4,180,
$C$4),利用自動(dòng)填充功能將公式復(fù)制到L5至L15單元格中;
2008年每月償還本金L16= -IPMT($A$16/12,I16,180,$C$4),利用自動(dòng)填充功能將公式復(fù)制到L17至L27單元格中;
2009年每月償還本金L28= -IPMT($A$17/12,I28,180,$C$4),用“填充柄”將公式復(fù)制到L29至L39單元格中。
4.剩余貸款金額(第M列)公式的設(shè)置
2006年12月剩余貸款金額:M3==$C$4-K3;
2007年后每月還款后剩余貸款金額:M4=M3-K4,利用自動(dòng)填充功能將公式復(fù)制到M5至M39單元格中;
合計(jì):J40=SUM(J3:J39),利用自動(dòng)填充功能將公式復(fù)制到K40至L40單元格中。
5.剩余貸款金額(第41行)公式的設(shè)置
M41=C4-K40,這個(gè)金額應(yīng)該與M39相等。如表4(本表已作優(yōu)化處理):
至此,可以計(jì)算出甲在三年多中,累計(jì)償還貸款額為
46 292.75元,其中累計(jì)償還本金21 178.76元,累計(jì)支付利息25 113.99元,如果在12月底想提前償還貸款,此時(shí)還應(yīng)支付給銀行128 821.24元。
三、模型的應(yīng)用
以上三個(gè)模型是緊密聯(lián)系的,前一個(gè)模型的數(shù)據(jù)直接影響后面模型的結(jié)果,所以當(dāng)對(duì)貸款進(jìn)行明細(xì)分析時(shí),以上三個(gè)模型都會(huì)運(yùn)用到,但是具體運(yùn)用到各種情況中時(shí)操作又有不同。假定在利率變動(dòng)的情況下,討論幾種情況:一是貸款金額變動(dòng),期限不變,比如本文的案例,如果貸款20萬(wàn)元,其它條件不變,這時(shí)只要把圖表中的貸款金額C4由150 000改成200 000萬(wàn)元即可得到想要的結(jié)果;二是貸款金額不變,期限變動(dòng),比如本文的案例,如果貸款期限為10年,其它條件不變,則需要改表1和表4,在表1中,把貸款年限C6由15改成10,此時(shí)要注意表2不要改,但是取值時(shí)要注意的是對(duì)應(yīng)10年的月償還額,即第C列,而不是本文開頭案例中的第D列,由于取值列數(shù)有改變,所以在表4中公式需要稍作變動(dòng),月償還額第J列的公式中需要把D改為C,比如J3=C14,J4-J15=$C$14,月償還本金和月償還利息中由于年限由15年變成10年,所以需要把第K列和第L列公式的期數(shù)180改成120,比如K3=-PPMT($A$14/12,I3,120,$C$4),L3=-IPMT($A$14/12,I3,120,$C$4),這樣就可得到想要的結(jié)果。一般情況下,選擇了貸款后,除了利率會(huì)變動(dòng)外,總的償還期限不會(huì)變化,隨著分期償還期限的增加,只要在第40行下面增加相應(yīng)的行,利用“填充柄”功能則可擴(kuò)充表4中的結(jié)果,如果利率2009年后再有變化,只需修改表2中A17單元格以下的利率,在表4中修改利率變化月份第J列的公式即可得到想要的結(jié)果。
四、結(jié)束語(yǔ)
在當(dāng)今社會(huì)中,投資機(jī)會(huì)無(wú)處不在,如何選擇一種合理的貸款結(jié)構(gòu)來(lái)籌集資金,進(jìn)行理性的投資,顯得頗為重要。本文的模型在實(shí)際中具有很好的實(shí)用價(jià)值,不僅可以隨時(shí)了解每期還貸金額里包含的利息額、累計(jì)償還額等相關(guān)信息,而且可以隨時(shí)確定實(shí)際利率,通過(guò)與投資的預(yù)期收益率相比較,據(jù)此作出是提前還貸還是另選其它投資項(xiàng)目的決定,從而作出理性的投資決策。
【參考文獻(xiàn)】
[1] 蔣秀蓮,宋言東,等.利用Excel雙變量模擬運(yùn)算表進(jìn)行購(gòu)房貸款決策[J].會(huì)計(jì)之友,2007(6)上.
[2] 張瑞君.計(jì)算機(jī)財(cái)務(wù)管理[M].北京:中國(guó)人民大學(xué)出版社,2007.
