利用EXCEL構(gòu)建大學(xué)教育基金存錢計劃模型

宋 琛

（無錫廣播電視大學(xué)基礎(chǔ)部，江蘇無錫 214021）

利用EXCEL構(gòu)建大學(xué)教育基金存錢計劃模型

宋 琛

（無錫廣播電視大學(xué)基礎(chǔ)部，江蘇無錫 214021）

文中利用Excel中幾個常見的內(nèi)置函數(shù)，構(gòu)建及驗證了大學(xué)教育基金年存款計劃模型，并在該模型的基礎(chǔ)上，構(gòu)建了月存月取的大學(xué)教育基金存款計劃模型。另外，文中還利用EXCEL的規(guī)劃求解功能，運用該模型，進行了提前存錢年限的計算及在存錢計劃確定的前提下大學(xué)教育基金可供支取的學(xué)費及生活費金額的計算。

Excel財務(wù)建模；大學(xué)教育基金；規(guī)劃求解

MicrosoftExcel作為企業(yè)普遍應(yīng)用的辦公自動化軟件，具有極強的靈活性以及豐富的內(nèi)置函數(shù)和各種實用工具，不僅可用于日常普通表格的制作和簡單的數(shù)理統(tǒng)計，財務(wù)人員還能夠使用它很容易地構(gòu)建出復(fù)雜的財務(wù)模型。文中即利用Excel中幾個常見的內(nèi)置函數(shù)，構(gòu)建及驗證了大學(xué)教育基金存錢計劃模型，并利用該模型進行了其它相關(guān)問題的求解。

一、問題的提出

有這樣一個問題：在你的孩子剛剛出生的時候，你開始為她的大學(xué)教育基金存錢了。如果她將會正好在她18歲生日那天開始她的大學(xué)生活，并且在她大學(xué)生活開始的前三年，你可以繼續(xù)向她的大學(xué)教育基金中存入金額，直至她大學(xué)最后一年開始，那時，最后一筆學(xué)費要付出了。預(yù)期她大學(xué)四年的大學(xué)學(xué)費將達到每年15000元，且教育基金能獲得每年5%的利息。那么，你每年至少要存入多少錢？

為了回答這個問題，我們利用Excel構(gòu)建了如表1所示的模型。

表1

表2

二、幾點說明

關(guān)于該模型，有以下幾點說明：

1、表1中大學(xué)四年級開始時四年學(xué)費的終值，即B5單元格的計算公式為：B5＝FV（B3,4,-B2），其中FV（）函數(shù)的功能和語法結(jié)構(gòu)如下：

·FV（）函數(shù)

語法：FV（rate，nper，pmt，pv，type）其中 rate為各期利率，nper為總投資期，pmt是各期所獲得的金額，在整個投資期內(nèi)不變，pv是從該項投資開始計算時已經(jīng)入賬的款項或一系列為了付款當(dāng)前值的累積和。如果忽略則pv＝0，type是邏輯值0或者1，用以指定付款時間在期初還是期末，如果為1，付款在期初，如果為0或忽略，付款在期末。

通過本單元格的計算，我們得知了一共要向大學(xué)教育基金存入的四年大學(xué)學(xué)費的總金額在大學(xué)四年級開始時的終值為64651.88元。

2、表1中每年最低存款額，即單元格B6的計算公式為：B6＝-PMT（B3,B1+3,,B5,1），其中PMT（）函數(shù)的功能和語法結(jié)構(gòu)如下：

·PMT（）函數(shù)

功能：返回在固定利率下，投資或貸款的等額分期償還額。

語法：PMT（rate，nper，pv，fv，type）其中 rate、nper、pv、type等各項參數(shù)同F(xiàn)V（）函數(shù)，而參數(shù)fv是未來值，或在最后一次付款后可以獲得的現(xiàn)金余額。

此單元格即計算出了在5%的存款利率條件下，在長達21年的時間內(nèi)，要獲得64651.88元的最終存款總額，則必須每年至少存入教育基金賬戶的金額為1723.81元。

3、關(guān)于此表結(jié)果正確性的檢驗，我們只需構(gòu)建一張教育基金收支一覽表即可。如表2所示。

4、如果年存款計劃改為月存款，則只需修改一下該模型，即可獲得想要的結(jié)果。

表3

比如有這樣一個問題：你的小孩剛升入中學(xué)，預(yù)計還有6年的時間，將開始她的大學(xué)生活。預(yù)計她大學(xué)4年每年學(xué)費為15000元（按年支付），而生活費為每月1200元（按月支取），因此，你現(xiàn)在開始為她的大學(xué)費用存錢了。假設(shè)你可以持續(xù)向她的大學(xué)教育基金中存入金額，直至她大學(xué)最后一月。那么，在5%的利率條件下，你每月向基金賬戶存入的金額至少應(yīng)為多少？

解答該問題的模型如表3所示，其中單元格B5的計算公式為：B5＝（1+B4/12）^12-1，因為已知的是基于期間為月的名義年利率，故B5單元格計算了實際年利率為5.12%。B6＝FV（B5,4,-B2,,1）+FV（B4/12,4*12,-B3,,1），計算了大學(xué)4年的學(xué)費及各月生活費，到小孩大學(xué)畢業(yè)前最后一個月初去提取生活費時的總費用的終值為131960.00元。B7＝-PMT（B4/12,（B1+4）*12,,B6,1），從而計算出每月應(yīng)存入的金額為846.28元。證明從略。

三、模型的其他應(yīng)用

運用以上討論的大學(xué)教育基金存錢計劃模型，我們除了可以用來進行每年或每月存錢金額多少的計算以外，還可以利用EXCEL強大的計算功能，進行其他我們感興趣的問題的求解。

（1）計劃存錢年限的計算

同上例，預(yù)計小孩大學(xué)4年每年學(xué)費為16000元（按年支付），而生活費為每月1000元（按月支?。?，而你計劃每月存款為1000元，那么在5%的利率條件下，你必須提前多久開始你的教育基金存錢計劃？

為了解決這個問題，我們利用上述討論的教育基金存錢計劃模型，利用EXCEL的規(guī)劃求解功能，來完成相應(yīng)計算。如圖1所示，將B7單元格設(shè)為目標單元格，并且設(shè)定目標單元格的值為1000元，將B1單元格，也就是上大學(xué)之前的年份作為可變單元格，點擊求解按鈕，EXCEL就會進行迭代計算，從而給出相應(yīng)的計算結(jié)果為4.4207年，如表4所示。

圖1

表4

（2）限額支取費用的計算

我們同樣可以利用EXCEL的規(guī)劃求解功能，利用該模型，在有了存款計劃后，即在提前開始存錢的年限及每月存錢金額已確定的情況下，來預(yù)測可供小孩今后選擇學(xué)費在什么范圍內(nèi)的學(xué)校，或者預(yù)測在學(xué)費固定的情況下，每個月供小孩花費的生活費為多少。具體數(shù)據(jù)如表5、表6所示。

表5

表6

四、結(jié)語

由上述討論可以看出，我們可以利用EXCEL強大的計算功能和豐富的內(nèi)置函數(shù)，構(gòu)建一大學(xué)教育基金存錢計劃模型。利用該模型，我們可以計算出各種條件下每年或每月應(yīng)向教育基金存入的金額的多少。還可以利用EXCEL的規(guī)劃求解功能，運用該模型，進行提前存錢年限的計算及在存錢計劃確定的前提下大學(xué)教育基金可供支取的學(xué)費及生活費金額的計算。這在大家都十分注重教育投資的今天是具有一定的現(xiàn)實意義的。

［1］小特洛伊.A.阿代爾.EXCEL在財務(wù)管理中的應(yīng)用［M］.中國人民大學(xué)出版社,75-82.

［2］國際財務(wù)管理師資格考試中國指導(dǎo)教材編寫組.財務(wù)管理［M］中國財政經(jīng)濟出版社,2005.

F830.48 < class="emphasis_bold">［文章標識碼］ A

A

1671-5136（2011） 03-0077-02

2011-09-07

宋?。?972—），女，江蘇無錫人，無錫廣播電視大學(xué)基礎(chǔ)部講師、碩士。研究方向：EXCEL在財務(wù)管理中的應(yīng)用、物流管理等。功能：基于固定利率及等額分期付款方式，返回某項投資的未來值。