1、精品資料推薦如何利用 Excel 審計(jì)銀行貸款利息作者：如皋市審計(jì)局 周長(zhǎng)龍 文章來(lái)源： Excel Home本文選自中國(guó)審計(jì)出版社出版的審計(jì)人員巧用 Excel金融審計(jì)中，正確計(jì)算應(yīng)收利息并界定進(jìn)入當(dāng)期損益的金額，對(duì)于核實(shí)金融企業(yè)盈虧至關(guān)重要。如果用手工計(jì) 算，不僅工作量大，而且準(zhǔn)確率不高。現(xiàn)推薦一種利用 Excel 表格進(jìn)行快速計(jì)算的方法。利用 Excel 表格計(jì)算貸款利息的基本原理是，首先運(yùn)用 IF 函數(shù)對(duì)貸款的起訖時(shí)間進(jìn)行判斷，再運(yùn)用 SUM 等函數(shù)分別計(jì)算出貸款正常利息、一年內(nèi)逾期利息、一年以上逾期利息。具體操作列舉如下：圖1一、在Office97 Excel環(huán)境作如下表格（圖1）二

2、、假定從第4行開始錄入數(shù)據(jù)，按照被審單位基礎(chǔ)資料，錄入A-I列的數(shù)據(jù)，在單元格C1、F1、H1、L1分別輸入審計(jì)計(jì)算期的起止時(shí)間、逾期至轉(zhuǎn)表外的規(guī)定天數(shù)（現(xiàn)行會(huì)計(jì)制度規(guī)定為一年，即365天）和逾期日利率。本文所介紹的公式中E列“借款金額”單位為萬(wàn)元，J-N列利息的計(jì)算結(jié)果已換算成單位為元。因此，表格中F-H列單元格數(shù)字格式定義為日期（其中固定單元格H1為數(shù)值），J-N列的單元格數(shù)字格式應(yīng)定義為數(shù)值并保留兩位小數(shù)。丨列正常息為月利率。三、在J4、K4、L4、M4、N4單元格內(nèi)分別輸入表內(nèi)正常息、表內(nèi)逾期息、表內(nèi)息合計(jì)、表外息、應(yīng)收息合計(jì) 的計(jì)算公式四、選定J4-N4區(qū)域，在填充句柄處按住鼠標(biāo)左鍵

3、向下拖動(dòng)光標(biāo)至錄入數(shù)據(jù)的最后一行為止，所有利息即計(jì)算 完畢表中計(jì)息公式為：各計(jì)息時(shí)段計(jì)息日數(shù)(即：終點(diǎn)日期-起點(diǎn)日期) *借款金額 *借款利率 *金額單位換算系數(shù)利率單位換算系數(shù)由于表內(nèi)正常息J4)、表內(nèi)逾期息(K4)、表外息(M4)3個(gè)單元格內(nèi)的公式比較復(fù)雜，現(xiàn)分別說(shuō)明如下：(一) 、表內(nèi)正常息(J4單元格)：二SUM(IF(OR(G4<$C$1,F4>$F$1,(H4v$C$1)*(H4>0),IF(H4=0,MIN(G4,$F$1), MIN(G4,H4,$F$1)-IF(OR(G4<$C$1,F4>$F$1,(H4<$C$1)*(H4>0),M

4、AX(F4,$C$1)*E4*I4*10000/30判斷流程如圖 3。9=SUM(J4+K4)=fVW (XF 心盡a如F4>$F$1"(K4X) , If (HA=O,皿耳 ©I, $F$1 h miN 3$?*“ )IF (QB(54<|C$1, F*>$F$t QM<$C$ 1) * CH4XQ 人AI CF% $C$L ) J>E4*I4 +innnnm=SUN (QF ®UCT$l-G4)>$Hn, G4：4F$1,(X4>0)* Q14< iCSlX CH4>0>(X4<=G4); If

5、 0<4=G, NIBfjFJt G4+SH$1X hl口i(H卷 JF£1H WJliSL)- aJCDR(fCJL-(j4>$J5L 芻卩仇 OWX) (H4<JCJ1X CHX)* 甲&齊" XCjl)*EO$Li 1*10000二列M (0F (DIU(JFS1-鎭)<=$KJ1QWCJD* CH4/0 (hV WCfcMjJX，IF(H4=O, fJ$l. MIN 014,IF(OR(GF$1-G4K=JHI1, (K4<JC$1*CK4)O0«-勲)<斗IFJCJL, G4+JHS1圖1-2（二）表內(nèi)逾期息（

6、K4單元格）：二SUM(IF(OR($C$1-G4)>$H$1,G4>$F$1,(H4>0)*(H4<$C$1),(H4>0)*(H4v=G4),IF(H4=0,MIN($F$1,G4+$H$1), MIN(H4,$F$1,G4+$H$1)-(IF(OR($C$1-G4)>$H$1,G4>$F$1,(H4>0)*(H4<$C$1),(H4>0)*(H4v=G4),MAX(G4,$C$1)*E4*$L$1*10000判斷流程如圖4。(三) 表外逾期息(M4單元格)：二SUM(IF(OR($F$1-G4)v=$H$1,(H4<$C$

7、1)*(H4>0),(H4-G4)v=$H$1)*(H4>0),IF(H4=0,$F$1,MIN(H4,$F$1)- (IF(OR($F$1-G4)v=$H$1,(H4<$C$1)*(H4>0),(H4-G4)v=$H$1)*(H4>0),IF($C$1-G4)>$H$1,$C$1,G4+$H$1)*E4* $L$1*10000判斷流程如圖5。表內(nèi)息合計(jì)(L4單元格)為：二SUM( J4+K4)，應(yīng)收息合計(jì)(N4單元格)為：二SUM( L4+M4)下表為上述過程的一個(gè)審計(jì)實(shí)例，假定計(jì)息期從 1997年12月21日至1998年12月21日，按現(xiàn)行制度逾期365

8、天后轉(zhuǎn)入表外，逾期日利率為0.04%計(jì)算結(jié)果如下(如表1):序放借科借借款日期約定還還款日期正常 表 表 表內(nèi)表外息 應(yīng)號(hào)款部門款單位目代號(hào)款金額款日期利率（月）內(nèi)正常息內(nèi)逾期息息合計(jì)收息合計(jì)1319964201997-4-201998-4-200.70%0.0C1440.001440.000.001440.00P419964201997-4-201998-3-80.70%0.001232.001232.000.001232.00321996-4-201997-4-201998-7-100.70%0.00960.00960.00648.00)1608.00451996-4-201997-4-2

9、01999-7-70.70%0.002400.002400.004900.00)7300.00571997-4-201998-4-200.70%1963.506860.008823.5(0.008823.50611997-4-201998-4-201998-4-200.70%280.500.00280.500.00280.50721997-4-201998-4-201998-2-50.70%215.050.00215.050.00215.05881997-4-201998-4-201998-10-50.70%2244.005376.007620.000.007620.00981997-4-20

10、1998-4-201999-5-50.70%2244.007840.0010084.000.0010084.00101998-3-5 1998-8-0.70%3218.744968.08186.0.08186.74如果要計(jì)算審計(jì)期內(nèi)某一時(shí)段的銀行利息，只要更改固定單元格C1、F1中的計(jì)息起止時(shí)間，表中的計(jì)息結(jié)果將會(huì)自動(dòng)顯示出來(lái)。如要計(jì)算1998年第3季度的利息，在單元格 C1中輸入1998-6-21，在單元格F1中輸入1998-9-21，就會(huì)得出如下計(jì)算結(jié)果，方便而快捷（如表2）序號(hào)放款部門借款單位科 目代號(hào)借款金額借款日期約定還款日期還款日期正常利率（月）表內(nèi)正常息表內(nèi)逾期息表內(nèi)息合計(jì)表外息

11、應(yīng)收息合計(jì)131996-4-201997-4-201998-4-200.70%0.000.000.000.0C0.002419964201997-4-201998-3-80.70%0.000.000.000.000.003219964201997-4-201998-7-100.70%0.000.000.00152.00152.00451996-4-201997-4-201999-7-70.70%0.000.000.001840.001840.00571997-4-201998-4-200.70%0.002576.0C2576.000.002576.00611997-4-201998-4-201

12、998-4-200.70%0.000.000.000.000.00721997-4-201998-4-201998-2-50.70%0.000.000.000.000.00881997-4-201998-4-201998-10-50.70%0.002944.002944.000.002944.00981997-4-201998-4-201999-5-50.70%0.002944.002944.000.002944.001091998-3-51998-8-50.70%946.691692.002638.690.002638.69五、再運(yùn)用Excel的“分類匯總”功能，就可得出各借款單位各指標(biāo)的匯總金額。在對(duì)某銀行進(jìn)行貸款利息審計(jì)時(shí)，涉及到貸款企業(yè)339戶。如用手工計(jì)算，不但工作量大而且準(zhǔn)確性也低。采用上述方法，僅用兩個(gè)小時(shí)就查出其多計(jì)表外利息三千多萬(wàn)元。此舉大大節(jié)省了審計(jì)時(shí)間，提高了工作效率，拓展了審計(jì)的深度和廣度。精品資料推薦輸入借就金額（E4） 逐敖:日期（H涉"日供$1）惜款日期停4）正常月利率（1書逋期轉(zhuǎn)恚外的天MHS1）約定還款日期帖4） 車計(jì)起Ma（fcti） 適朋日利率（$Lii）是W<$C3