巧用excel這四個公式,理財計算不求人!?

excel可是理財投資好助手,巧用其中的計算函式,能幫我們解決很多問題。比如是每月定投還是每週定投好啦,股票實際收益率是多少啦,別爭論資料說話就好了。

工具/原料

office excel

終值函式FV FV公式

主要用來計算複利計算,適用於已知本金、時間和利息。

求最終受益的公式

FV( rate,nper,pv,pmt,type)

終值(本利和)=(利率,支付總期數,初值,每期投入,模式)

利率為單期利率,期數為投資總期數,初始資金,模式1代表每期一開始支付,0代表期i末支付。

巧用excel這四個公式,理財計算不求人!

舉個例子

我們假設基金定投的收益率12%,每年投資1.2萬每年投資10年,是每個月投1000投資120個月和每週投240投資500周(每年投資50周國慶和過年跳過不投)受益究竟差多少。 調出excel中FV函式(清姝使用的是WPS表格,和微軟的excel基本相同)

月投

每期利率為12%/12=1%;期數 120,初值為0 ,每期投入12000/10=1000(注意金額要填寫負的),模式填1或者不填都可以。

巧用excel這四個公式,理財計算不求人!

周投

每期利率為12%/50=0.24%,期數500,初值為0,每期投入12000/500=240,模式1或者不填都可以

巧用excel這四個公式,理財計算不求人!

實際上週投和月投的基金定投區別不大,如果資金來源比較單一隻有工資,只設置工資日後一日為定投日就好了,沒必要設定周投

內部收益率公式IRR

IRR函式返回由數值代表的一組現金流的內部收益率。但是要注意,IRR只能計算固定時間間隔的收益率。

IRR(values,guess)

收益率=(現金流,預估值)

現金流為一組資料,必須有正有負。

結果估計這個是用來矯正資料的就是精確度空著就行 IRR雖然能夠計算出收益率,但是有個缺點就是時間間隔必須相同,以年為單位、月為單位、周為單位都可以,但是要記得乘以對應的週期數才是年化收益率。

舉個例子

我們在1月1日用信用卡刷卡1.2萬,然後選擇分12期還款方式,信用卡的手續費為0.6%,總共手續費為12000*0.6%*12=846元,每期還款12000/12+846/12=1072。將刷卡12000和每期還款1072組成一列用IRR計算每期利率,實際信用卡還款利率為每月利率*12最終13.03%是不是遠高於想象的0.6%*12的7.2%.

巧用excel這四個公式,理財計算不求人!

巧用excel這四個公式,理財計算不求人!

再舉一個例子

小a,男,25歲。購買30萬保額人壽+重疾保險投保30年。

消費型保險繳費大約在2000元左右,到期不返還

儲蓄型保險在8000元左右,到期返還全部保費加10%的收益及264000元

實際上的儲蓄型保險就是每年繳納的8000元減去2000元的保障部分保費,用6000元投資最終收益為264000元,那麼這三十年的收益率是多少呢?

買儲蓄保險只有2.37%的收益率,這結果小編也只能呵呵了!

巧用excel這四個公式,理財計算不求人!

不連續資料收益率公式XIRR

IRR計算定投或者還信用卡還好,但是我們在投資的時候,單筆不是固定時間的投資就不能用了,這個時候我們就要清楚更加高階一點的公式XIRR,比IRR多引入了一行時間,就可以計算任意時間的收益率了。

XIRR(values, dates, guess)

收益率=(現金流,時間,預估值)

現金流依舊是要求有正有負,時間是對應的時間,結果估計空著就行

舉個例子 建設我們在2013年3月1日買入了一隻股票花了20000,在2013年11月12日賣了10000,在14年5月7日又補倉了20000,在2015年6月12日賣出63215元,求這隻股票的實際收益率為多少。(資料都是小編瞎編的,你們注意看計算方法)

將現金流和時間對應排列成2列,呼叫XIRR函式,選擇對應的單元格組拖拉出來就可以了

有50%的年化收益率呢,相當不錯!

巧用excel這四個公式,理財計算不求人!

再舉一個例子

在投資某平臺P2P的時候,45天標為6.8%,使用滿10000減100元券實際充值9900元,2016年9.1日充值投標,9.3日稽核通過計算日期,10.18資金及利息10083.83元(10000+10000*0.68%*45/365)並且申請提現,10.19資金回到銀行卡,實際上的投標收益率為多少?

我們實際上付款是9月1日付款9900元,10.19收到10083.83元,使用XIRR函式選對應的時間和金額

巧用excel這四個公式,理財計算不求人!

感覺還可以啦,在平臺安全的情況下,新註冊平臺都會有一些新手券也不失一個提升收益率的好辦法,就是考察平臺太消耗時間成本,大家算好了時間和投資收益的平衡點。

現金再投資收益率MIRR

XIRR基本可以滿足我們絕大部分收益的計算了,不過如果我們有兩款產品同時使用,需要算出綜合收益率,就只能用MIRR了,不過MIRR有個缺點,和IRR一樣只能計算固定間隔時間的收益率。

MIRR(values, finance_rate, reinvest_rate

收益率=(現金流,投資率,再投資利率)

舉個例子

兩款產品,一款等額本息年化收益率8.4%等額本息產品,投資十萬每月收到8716元,每月收到的錢再投資利息3.6%的活期理財產品;另一款年化收益率7%的到期一次性到期還本,哪個更划算。 我們使用MIRR公式,注意利率要/12,得出每期利率要*12

巧用excel這四個公式,理財計算不求人!

好了,最常用的計算工具就是這四個了,基本上掌握了這四個函式可以計算絕大部分理財方面的計算,自己動手少被忽悠。

函式, 公式, 不求人, 收益率, 定投,
相關問題答案