看板 CFP
標題
[心得] 常用的理財規劃Excel函數
作者 uyulala
時間 2021/06/08 21:00:20
人氣 推:10 噓:0 留言:14
熱門文章不漏接,馬上點此訂閱每日熱門文章通知
訂閱Line日報 熱門文章不漏接,馬上點此訂閱
函數:Rate https://i.imgur.com/pFQynef.jpg
使用時機舉例: **計算單筆年化報酬率** 有時候標的抱的太久,你除了總報酬率可以輕易的算出外,若是想要 了解實質的年化報酬率,Rate會是一個很好用的幫手。 例:2017/2/3買入A基金,淨值為20.68,扣款10000,手續費0.6%, 合計總成本10060,買入單位數為10000/20.68=483.559。 2020/7/9賣出,淨值為46.2,贖回總金額為483.559*46.2=22340 請問本項投資的年化報酬率為何? Rate所需引數有五項 *Nper:期數 *Pmt:各期給付金額 *Pv:現值 *Fv:終值 *Type:期初or期末付款 *Nper:期數 ---------------- 在上面的投資中,我們的期數是2020/7/9減掉2017/2/3,共1252天, 假設贖回後5天入帳,則總天數為1252+5=1257,換算成一年除以365天, 則可以得到期數值為3.44...年。 (可直接在Nper的框裡鍵入計算式) *Pmt:各期給付金額 ------------------------ 由於我們是單筆買入,因此Pmt這一項可以不用管(定期定額用) *Pv:現值 -------------- 單筆買入總成本為-10060,可以在框裡直接打-10060或按減號後按相 對應資料的儲存格。 *Fv:終值 -------------- 贖回時得到的款項為22340,可以在框裡直接打22340或直接按相對應 資料的儲存格。 *Type:期初or期末給付 ------------------------- 投資基本上都是期初付款,因此在框裡鍵入1 輸入好上述資料後,可看到左下角已幫我們算出年化報酬率為 0.2606...即26%左右。 Excel試做如下圖 https://i.imgur.com/ZiWc497.jpg
如果搭配標的每日價格的更新,可以讓我們的每筆投資的年化報酬率 更清楚。 https://i.imgur.com/dafrxfL.jpg
**計算儲蓄險年化報酬率** 例:每年繳保費10萬,6年期滿領回65萬,計算年化報酬率 Nper=6 Pmt=-100000 Fv=650000 Type=1 得到年化報酬率為2.29% Excel試做如下圖 https://i.imgur.com/MNx5udi.jpg
**計算定期定額投資的年化報酬率** 例:定期定額已扣款36個月,每個月扣成本加手續費共10060, 第37個月初全部贖回得款45萬,求本定期定額的年化報酬率。 Nper=36 Pmt=-10060 Fv=450000 Type=1 算出來的結果是月報酬率,所以要再乘以12,得到年化報酬率13.71%。 Excel試做如下圖 https://i.imgur.com/SRCx5x2.jpg
**計算貸款利率** 例:向銀行借10萬,每月攤還3100元,共36期,則借款年化利率為- Nper=36 Pmt=-3100 Pv=100000 算出月利率為0.61%,換算成年化利率需乘以12得7.27% **計算你要達到財務目標所需的平均投報率** 例1:你目前有100萬,你希望20年後能買1000萬的房子, 請問這20年需要多少平均投報率,你的100萬20年後才能 變成1000萬? Nper=20 Pv=-1000000 Fv=10000000 Type=1 得到年平均投報率為12.2% -- ※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 36.226.126.147 (臺灣) ※ 文章網址: https://www.ptt.cc/bbs/CFP/M.1623157222.A.140.html
inthepeace: 推分享 06/08 21:25
mazu: 有心 06/08 21:51
ss8651twtw: 推 06/08 21:57
sarino: 實際使用上比較多是不定期不定額想算年化報酬率,用XIRR來 06/09 06:25
sarino: 算年化報酬率彈性比較大 06/09 06:25
謝謝介紹更好用的函數^_^ 感覺也很適合拿來算有月配息標的的內部報酬率。 ※ 編輯: uyulala (36.226.126.147 臺灣), 06/09/2021 08:24:35
blackteam: 推 感謝分享 06/09 10:28
andyge: 感謝教學!推推推推推推推推 06/10 08:32
ken90007: 謝分享,但還是覺得XIRR比較萬用。 06/10 15:47
ken90007: 用月利率x12 和 直接算年利率,也是有不小的差異 06/10 15:50
ken90007: 像你舉例定期定額,我用XIRR算年化是14.57% 06/10 15:51
以準確度為標準的話,XIRR在算"有實際交易日期"的投資當然會比較 準確,因為你的資料型態就是要有確切日期,好比我們如果設定每月 28日扣款,然後每月又有配息的話,實際上的扣款日和配息入帳都不 會那麼剛好落在每個月的同一天,所以如果要算的非常精準,XIRR就 會是更好的選擇。 所以Rate就無用武之地了嗎? 我覺得要看你想要呈現的表格型態和需求,例如你每月存A元,打算B 年後要得到C元的規模,想求所需的平均報酬率,那麼你的選擇應該 就不會是XIRR而是Rate了。 又如果是想像第三張圖片在一個頁面呈現多筆資料的結果,以XIRR來 說它需要比較多的儲存格來輸入資料,不適合這種一行就要出結果的 情況。
andyge: 請問樓上,那這樣誰是對的? 06/10 16:17
※ 編輯: uyulala (36.226.47.84 臺灣), 06/10/2021 18:52:22
z5877308: 推,謝謝分享。 06/13 20:12
a0356482: 推 07/11 08:29
nipai: 感謝分享 09/11 14:07
近期熱門文章
CFP 看板熱門文章