Fraser 的 excel 課堂分享: EXCEL密技

顯示具有 EXCEL密技 標籤的文章。顯示所有文章

2009年3月2日星期一

EXCEL財務函數的使用

財務函數之介紹 pdf檔案連結

EXCEL財務函數的使用
EXCEL的財務函數可以大致上可分為四類：投資計算函數、折舊計算函數、償還率計算函數、債券及其他金融函數。
一、投資計算函數

投資計算函數可分為與未來值fv有關，與付款pmt有關，與現值pv有關，與複利計算有關及與期間數有關幾類函數。

1、與未來值fv有關的函數--FV、FVSCHEDULE
2、與付款pmt有關的函數--IPMT、ISPMT、PMT、PPMT
3、與現值pv有關的函數--NPV、PV、XNPV
4、與複利計算有關的函數--EFFECT、NOMINAL
5、與期間數有關的函數--NPER

（一）
求某項投資的未來值FV

FV可以幫助我們進行一些有計劃、有目的、有效益的投資。FV函數基於固定利率及等額分期付款方式，返回某項投資的未來值。

語法形式為FV(rate,nper,pmt,pv,type)。
rate為各期利率，是一固定值
nper為總投資（或貸款）期，即該項投資（或貸款）的付款期總數
pv為各期所應付給（或得到）的金額，其數值在整個年金期間（或投資期內）保持不變，通常Pv包括本金和利息，但不包括其他費用及稅款，pv為現值，或一系列未來付款當前值的累積和，也稱為本金，如果省略pv，則假設其值為零
type為數字0或1，用以指定各期的付款時間是在期初還是期末，如果省略t，則假設其值為零。

例如：假如某人四年後需要一筆大學教育費用支出，計畫從現在起每月初存入3000元，如果按年利2.25%，按月計息（月利為2.25%/12），那麼四年以後該帳戶的存款額會是多少呢？

公式寫為：FV(2.25%/12,
48,-3000,0,1)

（二）
求投資的淨現值NPV

NPV函數基於一系列現金流和固定的各期貼現率，返回一項投資的淨現值。投資的淨現值是指未來各期支出（負值）和收入（正值）的當前值的總和。

語法形式為：NPV(rate,value1,value2,
...)

rate為各期貼現率，是一固定值；value1,value2,...代表1到29筆支出及收入的參數值，value1,value2,...所屬各期間的長度必須相等，而且支付及收入的時間都發生在期末。
NPV按次序使用value1,value2，來注釋現金流的次序。

例如，假設開一家早餐店。初期投資100,000，而希望未來三年中各年的收入分別為50,000、80,000、60,000。假定每年的貼現率是4%，則投資的淨現值的公式是：

=NPV(4%,
50000,80000,60000)+(-100000)

（三）
求貸款分期償還額PMT

PMT函數基於固定利率及等額分期付款方式，返回投資或貸款的每期付款額。PMT函數就是我們平時所說的"分期付款"。

其語法形式為：PMT(rate,nper,pv,fv,type)
rate為各期利率，是一固定值
nper為總投資（或貸款）期，即該項投資（或貸款）的付款期總數
pv為現值，或一系列未來付款當前值的累積和，也稱為本金
fv為未來值，或在最後一次付款後希望得到的現金餘額，如果省略fv，則假設其值為零（例如，一筆貸款的未來值即為零）
type為0或1，用以指定各期的付款時間是在期初還是期末。如果省略type，則假設其值為零。

例如，有一個房貸需要20年,每月付清的年利率為3%的400,0000貸款的月支額為：

PMT（4%/12,240,-4000000）
。

（四）
求某項投資的現值PV

PV函數用來計算某項投資的現值。年金現值就是未來各期年金現在的價值的總和。如果投資回收的當前價值大於投資的價值，則這項投資是有收益的。

其語法形式為：PV(rate,nper,pmt,fv,type)

Rate為各期利率。
Nper為總投資（或貸款）期，即該項投資（或貸款）的付款期總數。
Pmt為各期所應支付的金額，其數值在整個年金期間保持不變。通常
pmt 包括本金和利息，但不包括其他費用及稅款。
Fv 為未來值，或在最後一次支付後希望得到的現金餘額，如果省略
fv，則假設其值為零（一筆貸款的未來值即為零）。
Type用以指定各期的付款時間是在期初還是期末。

例如，假設要購買一項保險年金，該保險可以在今後十年內於每月末回報500。此項年金的購買成本為60,000，假定投資回報率為4%。那麼該項年金的現值為：

PV(0.08/12,
12*10,600,0) 計算結果為：-49452。

負值表示這是一筆付款，也就是支出現金流。年金：-49452
的現值小於實際支付的（60,000）。因此，這不是一項合算的投資。

EXCEL中常見的債券及其他金融函數
可分為計算本金、利息的函數，與利息支付時間有關的函數、與利率收益率有關的函數、與修正期限有關的函數、與有價證券有關的函數以及與證券價格表示有關的函數。如下說明
:
可由EXCEL的說明中找到相關解說

1、計算本金、利息的函數--ACCRINT、ACCRINTM、CUMIPMT、COUPNUM
2、與利息支付時間有關的函數--COUPDAYS、COUPNCD、COUPPCD
3、
與利率收益率有關的函數--INTRATE、ODDFYIELD、ODDLYIELD、YIELD、YIELDMAT
4、與修正期限有關的函數--DURATION、MDURATION
5、與有價證券有關的函數--ODDFPRICE、ODDLPRICE、PRICE、PRICEDISC、PRICEMAT
6、與證券價格表示有關的函數--DOLLARDE、DOLLARFR

簡單說明
1
.
求定期付息有價證券的應計利息的函數ACCRINT

ACCRINT函數可以返回定期付息有價證券的應計利息。

其語法形式為ACCRINT(issue,first_interest,settlement,rate,par,frequency,basis)

issue為有價證券的發行日
first_interest為有價證券的起息日
settlement為有價證券的成交日
rate為有價證券的年息票利率
par為有價證券的票面價值

例如，本國國庫券的交易情況為：發行日為2008年5月20日；起息日為2008年6月1日；成交日為2008年7月1日，息票利率為8.0%；票面價值為10,000；按半年期付息；日計數基準為30/360，那麼應計利息為：
=ACCRINT(2008/5/20,2008/6/1,2008/7/1,8.0%,10000,2,0)

2009年1月13日星期二

擷取Word與Excel圖片圖表

[密技] 終極劫圖技：擷取Word與Excel圖片圖表 (轉貼)

話說PowerPoint與Visio 都可以直接將檔案中的圖片另存成圖檔，所以都可以很方便的使用圖片，但是說到Word與Excel就沒有這麼方便了，即便我們有很多方案可以解決問題，但始終都無法同時滿足這樣的需求，而本次介紹的工具，則強化了這樣的需求，而且使用者只要統一在一個地方，就可分別擷取出欲使用的圖檔。

下載網址：http://tlcheng.twbbs.org/Tools/OfficePic/0.2/OfficePic.zip
Step1.
首先，輸入OfficePic下載網址：「http://tlcheng.twbbs.org/Tools/OfficePic/0.2/OfficePic.zip」，開始進行安裝程序。接著再開啟欲抓出圖片的Word 或Excel 檔案。

Step2.
再來選取要存放圖片的目錄，預設為該Office檔案所在的目錄位置。

Step3.
接著選取要輸出的圖檔類型，本工具支援「bmp/gif/emf/emz/png/jpg/wmf/wmz」格式，預設採用png格式，建議採用較不易失真的EMF格式。

Step4.
按下〔執行〕按鈕後，程式會建立Excel.Application 物件，以將圖片檔讀出，並存於指定目錄，圖片檔名規則為 [工作表名稱]00000.emf。

Step5.
執行期間，畫面中間會顯示正在處理的圖片。

Step6.
最後，於指定的目錄位置，即可看到成功抓出的圖檔。

2008年12月14日星期日

2009中國版超強萬年曆

2009年行事曆下載(所有類型加總，共 37份)：
下載網址：範本藝廊搜尋目標: "2009"

中國版超強萬年曆：

中國版超強萬年曆

大事記資料總來源：維基百科，自由的百科全書
中國超強行事曆來源：http://it.csu.edu.tw/aseip_folder/行事曆.xls

2008年11月28日星期五

WEEKDAY函數

WEEKDAY函數
說明：顯示一個日期是
星期幾
參數：WEEKDAY(日期,傳回值類型)
傳回值類型有1、2、3共三種，分別表示如下：

在TQC EXCEL考試中，傳回值類型都是輸入2
因為第2種類型比較直覺性
如果星期一就顯示1
星期二就顯示2……星期日就顯示7
不像第1種類型
星期一會顯示2；星期二會顯示3……星期日會顯示1
第3種類型也是不太好用

了解這個麻煩的傳回值類型後
來幾個範例吧

例1：要顯示2007/7/25是星期幾？