怪老子ETF線上課程

EXCEL函數-PV現值

日期:2009-06-17

讀完本篇後,不了解Excel裡的PV函數應用也難。現值是屬於貨幣的時間價值的一環,Excel也提供了一個相對函數PV (Present Value)來呼應。相信許多人或多或少都知道什麼是現值,但是當使用Excel PV函數時,卻常常碰到一些問題,尤其是正負號部分常常讓使用者頭疼。本篇主要介紹該函數的意義及其應用,除了理論介紹以外,還附有範例供讀者參考。

何謂現值

現值的英文為Present Value (PV),就是未來之一筆金額經過一段時間以複利折現(Discount)後,相當於現在的貨幣價值。簡單舉個例說,若利率為5%,5年後的100元,經過折現後相當於現值78.4元。也就是說現在只要78.4元,以5%的複利成長,剛好會等於5年後的100元。現值也相當於現在該準備多少,才足以支付未來的現金流量。想要5年後有100元,那現在就得存78.4元。

現值既然是以複利折現,那麼利率是多少,以及多久複利一次,就會影響到現值。在現實世界之財務狀況是複雜的,除了單筆金額外,常常也會有年金的應用,Excel的PV函數,除了單筆金額外,年金部分也一併考慮。現值的應用非常廣泛,例如預計5年後可以擁有100萬,若年利率為3%,現在應該存多少錢。又如貸款年利率為4%,期限為3年,每月都繳本息一萬元,相當於向銀行貸了多少錢?這些都是很典型的現值應用範例。

單筆的現值公式

單筆的現值於期末時,有一筆金額(fv),然後每期以利率(rate)複利折現,經過(nper)期後,其現值(PV)公式如下:

PV = fv / (1+ rate)nper

例如年利率10%,每年複利計算一次,請問現在要存多少錢,6年後可領回17,716?這是典型現值計算:
fv = 17716;nper = 6;rate = 10%;
所以未來值 pv = 17716/(1+10%)6
以Excel公式表示:=17716/(1+10%)^6 = 10,000

一般年金之現值

年金的意義是於期間內共分幾期(nper),每期均於期末產生一筆金額(pmt),每一筆金額都會以每期利率(rate)複利折現,若每期之金額pmt都相等。這些金額所累計的現值公式為:

上圖黃色長條代表年金現金流量。

例如James分期付款購買一手機,每月均於月底時還本息1000元,總共繳了6月,月利率1%,每月複利一次,問手機賣多少錢?
pmt = 1000;nper = 6;rate = 1%;
所以現值 pv = 1000*(1-1/(1+1%)6)/1%
以Excel公式表示:=1000*(1-1/(1+1%)^6)/1% = 5,795

現金流量於期初發生

前述的一般年金現金流量均發生於期末,若同樣的現金流量都發生於期初,這樣的狀況只需將一般年金的現值(現金流量於期末)再乘上(1+rate)就可以了,公式如下:

例如Peter向Jason借錢,約定每月均於月初時還本息1000元,總共6個月,月利率1%,每月複利一次,問Jason願意借多少錢給Peter?
pmt = 1000;nper = 6;rate = 1%;type =1
所以現值 pv = 1000*(1+1%)*(1-1/(1+1%)6)/1%
以Excel公式表示:=1000*(1+1%)*(1-1/(1+1%)^6)/1% = 5,853

如何使用Excel的PV函數

上述算法都是一般財務書籍所使用的方式,可是Excel的 PV函數所使用的計算方式有一些不同,也就是這一點點的不一樣,把許多的使用者弄得不知所措。

Excel的PV函數具有三大特色:

  • 單筆以及年金混合使用
  • 單筆及年金的現金流量,可各自使用正負值來代表
  • 未來值也是以正負值代表

PV函數的參數

PV函數的參數定義如下:
=PV(rate, nper, pmt, fv, type)

下表列出了參數的意義以及與單筆或年金相關的參數,可以看出利率rate及期數nper單筆或年金都會用到。fv為單筆之期末金額;pmt為年金之每期金額,type參數則只跟年金有關,說明年金是於期末(預設值)或期初發生。前面三項參數 rate、 nper、pmt是一定要有的參數不可以省略,後面兩項fv及type是可有可無,若這兩項沒有輸入,PV函數會採用預設值fv = 0; type = 0(期末)。

參數
意義
必要參數
單筆相關
年金相關
rate 每期利率
必要
nper 期數
必要
pmt 年金之每期金額
必要
fv 單筆之期末金額
選項
type 期初或期末
0:期末(預設)
1:期初
選項

fv及pmt的正負值

PV函數可計算:

  1. 未來值(fv)的現值
  2. 年金(pmt)的現值

而且fv及pmt都可以用正負值來表示,正值代表現金流入,負值代表現金流出。

例如定期存款:投資者將錢存入銀行,就是現金從投資者流出(負值),到期後收到銀行支付之本利和,就是現金流入投資者口袋(正值)。又如銀行房貸,以貸款者角度來看:期初時銀行會撥一筆款項進來,這筆金額便是現金流入貸款者(正值),之後每一月(期)均必須繳之攤還金額,是現金流出貸款者(負值)。反過來看,一個一模一樣的房貸,但從銀行角度來切入,期初時銀行會撥一筆款項給貸款者,這筆金額便是現金從銀行流出(負值),之後每一月(期)均收到貸款者的還款金額,這屬於現金流入銀行(正值)。所以說相同一件貸款,期初的撥款動作,對貸款者是現金流入,對銀行卻是現金流出。兩者之分別,端看以何種角度來看。

PV計算結果的正負值

計算出來的現值PV也是以正負值來表示。咦~~~這就奇怪了,正的現值很容易理解,負的現值又代表何意義呢?其實PV函數所計算出來的結果等於『終值的現值』加上『年金的現值』的補數。也就是說:

終值(fv)的現值+ 年金(pmt)的現值 + PV = 0

這又代表啥意義呢?為簡化說明起見,先不考慮年金現值(pmt=0),公式簡化為:終值(fv)的現值 + PV = 0,也就是:

PV = - 終值(fv)的現值。

若fv為正值,PV就是負值,代表一個若是現金流出,另一個就必須是現金流入,反之亦然。

例如一筆100元貸款(fv=-100),約定3年後還款,年利率10%,所以PV = -(-100/(1+10%)^3) = 75.1。白話的說就是借入75.1元,3年後該還人家100元。好似期初(PV)拿進75.1元,期末(fv)應該拿出100元。

假若 fv=0 ,只剩下年金(pmt)現值,這時:年金(pmt)現值 + PV = 0,PV = - 年金(pmt)現值,也是相同的意義。

當終值及年金同時都有時:終值(fv)的現值 + 年金(pmt)的現值 + PV = 0。也就是說

PV = - (終值(fv)的現值 + 年金(pmt)的現值)

等於兩項相加之負值。可是fv及pmt都各自可能為正值或負值,問題就複雜了。若fv及pmt均為正值(兩者都是現金流入),那麼PV就肯定是負值(必須現金流出才會平衡)。相反的,若fv及pmt均為負值,那麼PV肯定是正值。

當fv及pmt都同為正值或負值時,PV負責平衡單筆及年金兩者現值之加總;若fv及pmt正負號各自相反時,『單筆現值』及『年金現值』會有部分自行相互抵消,那麼PV就只要平衡這兩者之差額了。若差額為 0,代表『單筆現值』跟『年金現值』兩者是相互平衡或相等。

範例

由以上這些敘述可以看出Excel的PV函數不只是一個單純的現值而已,其功能是非常大的,也難怪不容易弄清楚。我想用一些範例加上圖解的方式來解釋,讀者會應該比較容易理解。

範例Excel

以下的範例也提供下載

單筆借款

James想跟朋友借一筆款項,雙方同意以年利率10%計息,借期2年、一年複利一次,到期還款10萬元。請問James可以借到多少錢?

=PV(10%, 2, 0, -100000) = 82,645

以James角度來看,因為是借款,期末有一筆現金10萬元流出James,所以fv = -100000。算出來的答案是82,645,代表James應該拿到借款(現金流入)82,645,這筆帳才會平衡。

銀行貸款

Lisa於每月底必須繳交貸款本息一萬元,年利率2%、每月計算複利一次、期限為一年。請問Lisa跟銀行貸了多少錢?

=PV(2%/12, 12, -10000) = 118,710

Lisa每個月底拿出10,000元(所以是年金式的現金流出 pmt = -10,000),那麼期初(PV)當然要拿到118,710的銀行撥款(現金流入),這樣才划算。

退休規劃

Michael現年35歲,預計60歲退休,目前『每年』可結餘30萬元,均投入5%報酬率的商品。希望退休時可有擁有2000萬的退休金,請問Michael現在必須已經擁有多少存款才有辦法達到這目標?

=PV(5%, 25, -300000, 20000000) = -1,677,872

這是單筆(fv)為正值、年金(pmt)為負值的例子,希望期末時拿到2000萬元(fv=20,000,000),而且每年於期末(type = 0)還拿出30萬元(pmt = -300,000),那麼期初必須拿出-1,677,872元,這帳才會平衡。也就是尚欠1,677,872元。

如果完全相同的一個例子,但是每年結餘由30萬元變成50萬元,結果又是不一樣了:
=PV(5%, 25, -500000, 20000000) = 1,140,917
現值由負轉正喔,每年結餘30萬(pmt = -300,000)時,還缺約168萬。當每年結餘50萬(pmt = -500,000)時,就多了約114萬。當PV=0的那個pmt,就是剛好所需要的每年結餘,一定是介於30萬~50萬之間,答案是-419,049

貸款餘額

Peter有一筆10年期貸款,年利率10%,每月支付13,215.07元,第5年底貸款餘額還剩621,972元,請問Perter貸了多少錢?

=PV(10%/12, 12*5, -13215.07, -621972)
= 1,000,000

這也是單筆(fv)加年金(pmt)的例子,只是fv、pmt均為負值。Peter每月繳納13,125.07(pmt = -13215.07),到第5年時,還必須拿出62萬多(fv = -621972),也就是說期初時貸款1,000,000,這筆帳才會平衡。

Excel的通用公式

PV函數其實就是當rate、nper、pmt、fv、type為已知時,解下列函數的PV值。

以PV函數計算未來值因子(PVIF及PVIFA)

了解Excel PV函數的應用之後,應該不難理解PV函數也可以用來計算單筆現值因子,及年金現值因子。欲更深入了解的讀者可參考:現值及未來值因子表

未來值因子
Excel PV公式
PVIF(r, n) =PV(rate, nper, 0, -1)
期末 PVIFA(r, n) =PV(rate, nper, -1)
期初 PVIFA(r, n) =PV(rate, nper, -1, 0, 1)

不適用的年金

Excel的PV函數只適用每期金額大小都一致的年金。這種年金的現金流量每期一樣,所以可以使用公式來計算。若年金每期發生的現金流量金額都不一樣,如變額年金、每期金額以一定百分比成長(例如5%)之年金等,就無法使用Excel的PV函數了。這時便必須將每期所發生的現金流量,個別視為單筆,一一計算每一筆之現值,然後再全部加總即可。有關這部份可參考:

年金-理論篇

相關文章

EXCEL投資理財應用

Excel函數-FV未來值

複利-完全攻略篇

年金-理論篇

年金-應用篇




■ 本網站內容儘可能精確完整,但不保證無誤。若做為投資依據,風險請自行斟酌 ,本網站不負賠償之責任。
■ 網站所有資料均為版權所有,非經書面允許請勿轉載或使用。