【Excel日期函數】月底計算利息 - 會計

Sierra Rose avatar
By Sierra Rose
at 2020-06-18T17:10

Table of Contents


【Excel日期函數】月底計算利息,一次學會TODAY、DATE、DAY、EOMONTH等函數組合

網誌圖文版:

https://www.b88104069.com/archives/4549

Excel許多日期相關函數,TODAY、DATE是基本,DAY、MONTH、YEAR很好用,EOMONTH是進
階函數,實務中經常搭配邏輯和文字函數組合運用,本文以會計借款應付利息為例介紹。

公司銷貨帶來應收帳款收現,進貨必須應付帳款支付,還有生產運作種種環節的現金收支
,因此在經營過程中,難免手頭上都會有一些短期閒置資金。專注本業的穩健公司,不會
把這些錢從事風險性的股票投資,所以銀行短期定存便是這些短期資金最佳的去處。

銀行定期存款有幾個關鍵屬性:定存金額、利率、發息日、開始日期、定存期間、到期日
,因為會決定發放多少利息、何時發放利息、何時收回本金。這些在Excel可以利用簡單
數學公式計算。不過在會計有個應計基礎觀念,例如在25日發息好了,那麼在月底便有
5-6天的暫估應收利息,這個需要藉助Excel較為進階的日期函數公式,以下具體分享:

一、定期存款明細

到期日由開始日期(存入日期)加上定存期間簡單計算而來:「=F2+G2」。通常在定存單
上也會有到期日,這裡利用Excel公式直接計算,同時也是作為複核機制。

二、TODAY函數

有時候系統跑出來的報表會是所有的定存明細,即使已經到期的也會出來,就算系統不是
如此,也有的時候是內部存檔管理需要,不會去刪掉已到期的定存。無論哪種情形,我們
都可以透過日期函數TODAY和邏輯函數IF,設計公式判斷定存是否到期:「
=IF((TODAY()-H2)>0,"Y","N")」。

TODAY函數是傳回系統今天的日期,以這一個步驟來說,當時的日期為「2019/2/5」,函
數的計算結果是每天都在變,這裡的是否到期是以當天作為基準。

三、DATE函數

TODAY函數雖然很方便可以得到當天的日期,然而在會計帳務處理,都是以某個期間的月
底最後一日作為基準點,所以還是希望能得到月底日,這裡的範例也是如此。

設計一連串函數公式,以便得到當期期末日期:J欄公式為「
=CONCATENATE("20",LEFT(A2,2))」、K欄公式為「=RIGHT(A2,2)」、L欄公式為「
=DATE(CONCATENATE("20",LEFT(A2,2)),RIGHT(A2,2),1)」,這些主要配合A欄的系統年月
期間,以文字函數LEFT取得左邊字串、RIGHT取得右邊字串,CONCATENATE合併字串,最終
再以DATE函數依照年月日三個參數得到當月的月初日期。

四、EOMONTH函數

有了月初日期,設計公式:「=EOMONTH(L2,0)」,EOMONTH函數顧名思義:「傳回所指定
月份數之前或之後的月份最後一天的數列數字」,公式裡的「L2」代表開始日期、「0」
代表不往前也不往後,所以正是當月份,如此計算結果即為「2019/1/31」。

圖片中的「43466」為Excel的日期序列值,以日期而言便是「2019/1/1」。關於Excel日
期序列值,在下一節第四步驟將有更進一步的說明。這裡從「43466」和「43496」相差剛
好30,應該能理解它就是以數字方式紀錄日期,以便進行計算。

五、DAY函數

有了月末日期,設計公式:「=DAY(M2))」,表示傳回「M2」日期的天數,計算結果為「
31」,亦即1月「當月天數」有「31」天。圖片中的「Serial_number」其值為43496。

六、ROUND函數

最後終於可以計算暫估利息了。於O欄設計簡單公式:「=IF(I2="Y",0,N2-E2)」,代表如
果已經到期,「暫估利息天數」為「0」,否則以當月天數減掉發息日計算「暫估利息天
數」。P欄公式為「=ROUND(C2*D2/365*O2,0)」,代表定存金額及利率以暫估利息天數計
算的「暫估利息」,加個ROUND函數避免尾差。

七、函數公式模擬測試

配合原始報表會將已到期定存也列入的特性,在上個步驟標黃色部份資料,可以看到「暫
估利息」皆為零。這個步驟測試性地將期間設定為「1902」﹐標黃色部份可以看到「當月
天數」皆為「28」,「暫估利息」也會同步更新,可見此報表公式模型的設計相當完整。

設計函數公式模型的好處

這裡的銀行定存暫估利息表,其實熟悉Excel基本操作的讀者,毋須如此大費周章也能計
算暫估利息。這節範例花了很多心思設計函數公式,用意有兩點:

其一:如同第六第七步驟所示,只要將公式模型架好了,幾乎是一勞永逸,長期以往可以
提升每月結帳的效率;

其二:這是個很好的範例機會,嘗試使用設計較為進階的日期函數,只要熟悉了這些函數
,在其他相關的實務個案,例如員工年資、銀行借款、帳款帳齡存貨庫齡,都有可能會用
到,因此值得研習。


會計人的Excel小教室直播課程:

https://www.b88104069.com/archives/4535

--


周末,我們繼續Excel:精華區=>21.心得=>5.其他=>3.office

會計人的Excel小教室: https://www.facebook.com/acctexcel


--
Tags: 會計

All Comments

請問重學會計教材年份選擇?

Charlotte avatar
By Charlotte
at 2020-06-18T06:32
大家好: 有感於現在工作實在難有成長性, 想拼考試來轉職, 但本身離開學校(會計系)已經十年, 前幾天買了中會來看發現相看兩不識… 聽說會計改了不少東西, 請問函授選擇上中會年份要以多久以後比較好呢? 謝謝大家! - ...

應收帳款移轉問題

Andy avatar
By Andy
at 2020-06-17T23:31
想請問計算應收帳款移轉時 產生的服務負債於計算按公允價值相對比例分攤原金融資產 是否要自出售金融資產所得價款中扣除 題目有點長 兩題都是題組 想問的都是題組中的第二題 麻煩大家了 第一個題組 計算原金融資產之分攤時 出售對價有扣除服務負債 https://i.imgur.com/MhlbOg2.jpg ...

政會的新舊公報

Dorothy avatar
By Dorothy
at 2020-06-17T22:20
大家好 因為我目前在上第一輪的政會 然後我原本的老師是羅威老師 但我發現他似乎上的是舊公報 分錄也都是代舊公報 總共二十堂課程 目前還剩下十堂左右 然後我最近有在上陳友心老師的 他上的是新公報+一些舊公報 而且新公報比舊公報好瞭解多 我是考110的高普考 我想請問我這樣還要繼續上羅威老師的課程嗎 還是就 ...

現金流量表 間接法 直接法觀念問題

Faithe avatar
By Faithe
at 2020-06-17T19:18
大家好 因為是函授如果有問題反覆想過沒解答因此上來發問 就疑惑第一點跟第三點是否有衝突? 因為第一點說投資收益並未真正流出流入現金但是第三點又說間接法在一開始要先將投資 收益(損失)做加減 有點不太明白這個道理... https://i.imgur.com/2SuR28x.jpg 再來是想請教一下大家做 ...

合併報表幾個小基本問題

William avatar
By William
at 2020-06-16T22:31
大家好 目前在針對企業評價 閱讀合併報表時有出現幾個問題 希望版上的高手能替小弟解答一下 Q1: 假設母公司持有60%子公司 A 依規定要合併財務報表 那假設今天 我賣一台電腦 2萬元 其中CPU 5千元 是母公司和子公司A購買的 那母公司的合併損益表 合併營業收入是否是 2000 ...