裁判実務用の利息計算・日数計算・充当計算を行うExcelブックを公開しました
裁判実務で用いられる計算方法に従い利息計算・日数計算を行うExcelブックを公開しました。マクロを使用していないのでiOS用のExcelでも動きます。法定充当や元金優先充当による残債権の計算もできます。
金額や日付など必要事項を入力すると、そのまま下記のような形式で損害金計算書として使用することができます。www.vector.co.jp
利息計算を行うExcel関数(マクロ不使用)
MicrosoftはiOS用のExcelを提供していますが、マクロを使うことはできません。そこで、(Excel用)裁判所における計算方法に従った利息計算を行う自作関数 INTCAL - 法律事務雑記帳や(Excel用)1年に満たない端数期間の日数を平年日と閏年日に分けて計算する自作関数 FRACDAYS - 法律事務雑記帳と同じ機能の計算をマクロを使わずにできないかと思い、下記の関数を作りました。異様に複雑な計算式になっており、構造を理解するのは骨が折れますが、不思議と計算できます。
なお、関数中、「始」は期間開始日のセル、「終」は期間終了日のセルに置き換えてください。「パラ」は初日参入の有無のパラメータであり、「0」を代入すれば初日不算入(片端入れ)、「1」を代入すれば初日参入(両端入れ)となります。
関数
ステップ1:年
まず「年」を計算します。Excel一般機能:DATEDIF関数のバグ対策を参考にしています。
=INT((YEAR(終)*12+MONTH(終)-YEAR(始-パラ)*12-MONTH(始-パラ)-IF(DAY(始-パラ+1)=1,IF(DAY(終+1)>1,1),IF(AND(DAY(終+1)>1,DAY(終)<DAY(始-パラ)),1)))/12)
ステップ2:平年日
次に1年に満たない端数期間のうち平年に属する日数(以下「平年日」といいます)を計算します。ここが山場です。
「YEAR(EDATE(始-パラ,年*12))=YEAR(終)」の部分は、1年に満たない端数期間が同じ年に属するか否かの判定部分です。
「DAY(DATE(YEAR(終),3,0))=29」の部分は閏年か否かの判定部分です。2月の末日が29日か否かで閏年か否かを判定しています。
=IF(YEAR(EDATE(始-パラ,年*12))=YEAR(終),IF(DAY(DATE(YEAR(終),3,0))=29,0,DATEDIF(EDATE(始-パラ,年*12),終,"d")),IF(DAY(DATE(YEAR(EDATE(始-パラ,年*12)),3,0))=29,DATEDIF(DATE(YEAR(EDATE(始-パラ,年*12)),12,31),終,"d"),IF(DAY(DATE(YEAR(終),3,0))=29,DATEDIF(EDATE(始-パラ,年*12),DATE(YEAR(EDATE(始-パラ,年*12)),12,31),"d"),DATEDIF(EDATE(始-パラ,年*12),終,"d"))))
(Excel用)月割計算による利息計算を行う自作関数 INTCALM
月割計算による利息計算を行うユーザー定義関数 INTCALM を作りました。1か月に満たない端数部分は年365日日割計算をします。
(Excel用)裁判所における計算方法に従った利息計算を行う自作関数 INTCAL - 法律事務雑記帳と併せて、住宅ローンの利息計算などにお使いください。
なお、INTCAL と同じく、ktDATEDIF(http://addinbox.sakura.ne.jp/Excel_Tips05.htm#ktDATEDIF2)が必要です。
使い方
=INTCALM(元金, 年利, 期間開始日, 期間終了日, [初日算入の指定, ][一円未満の端数処理の指定])
「初日算入の指定」以下は省略可です。省略した場合、「初日不算入(片端入れ)」「一円未満切捨」で計算します。
「初日算入の指定」には、0又は1を入力してください。0は「初日不算入(片端入れ)」、1は「初日算入(両端入れ)」です。何も入力しなければ「初日不算入(片端入れ)」となります。
「一円未満の端数処理の指定」には、0又は1又は2を入力してください。0は「切捨」、1は「四捨五入」、2は「切上」です。何も入力しなければ「切捨」となります。
使用例
H25.1.10融資実行のローン(元金6,000,000円)を元利均等払で毎月10日限り返済する場合を想定
(1) 元金6,000,000円、年利2.2%、期間H25.1.10~H25.2.10、初日不算入、切捨
(融資実行日から最初の約定弁済日までの1か月間の利息を計算する)
=INTCALM(6000000, 0.022, "2013/1/10", "2013/2/10", 0, 0) → 11000
(2) 元金5,503,240円、年利2.2%、期間H25.12.10~H25.12.25、初日不算入、切捨
(繰上返済する場合に直近の約定弁済日H25.12.10から繰上返済日H25.12.25まで15日間の利息を計算する)
=INTCALM(5503240, 0.022, "2013/12/10", "2013/12/25", 0, 0) → 4975
INTCALM のコード本体
'利息の月割計算 (interest calculation <month>) Function INTCALM(元金 As Currency, 年利 As Double, 期間開始日 As Date, 期間終了日 As Date, _ Optional 初日算入の指定 As Integer = 0, Optional 一円未満の端数処理の指定 As Integer = 0) amount = 元金 i = 年利 date_begin = 期間開始日 date_end = 期間終了日 opt2 = 初日算入の指定 opt3 = 一円未満の端数処理の指定 'opt2 初日算入の指定 ' 0(default) 初日不算入(片端入れ) ' 1 初日算入(両端入れ) 'opt3 一円未満の端数処理の指定 ' 0(default) 切捨 ' 1 四捨五入 ' 2 切上 '初日算入の処理 date_begin = date_begin - opt2 Dim ans As Currency m = ktDATEDIF(date_begin, date_end, "M") d = ktDATEDIF(date_begin, date_end, "MD") ans = amount * i / 12 * m + amount * i * d / 365 '1か月未満の端日数は年365日日割計算 '一円未満の端数処理 Select Case opt3 Case 1 '四捨五入 ans = Application.WorksheetFunction.Round(ans, 0) Case 2 '切上 ans = Application.WorksheetFunction.RoundUp(ans, 0) Case Else '切捨 ans = Application.WorksheetFunction.RoundDown(ans, 0) End Select If ((opt2 = 0) Or (opt2 = 1)) And ((opt3 = 0) Or (opt3 = 1) Or (opt3 = 2)) Then INTCALM = ans Else 'optが指定の数字以外の場合にエラーを出す INTCALM = CVErr(xlErrValue) End If End Function
「関数ライブラリ」からダイアログボックスにより INTCALM 関数を利用する
Sub RegisterINTCALM() Application.MacroOptions Macro:="INTCALM", _ Description:="月利による利息計算をします。1か月に満たない端数は年365日日割計算をします。", _ Category:="財務", _ ArgumentDescriptions:=Array( _ "には利息計算の対象となる元金を指定します。", _ "には年利を指定します。", _ "には利息計算の対象となる期間の開始日を指定します。", _ "には利息計算の対象となる期間の終了日を指定します。", _ "0: 初日不算入(片端入れ)(default)" & vbCrLf & "1: 初日算入(両端入れ)", _ "0: 切捨(default)" & vbCrLf & "1: 四捨五入" & vbCrLf & "2: 切上"), _ HelpFile:="" End Sub
(Excel用)1年に満たない端数期間の日数を平年日と閏年日に分けて計算する自作関数 FRACDAYS
1年に満たない端数期間の日数を、平年日と閏年日に分けて計算するユーザー定義関数 FRACDAYS を作りました。
例えば、平成26年12月1日から平成28年8月1日までの期間について考えると、①まず平成26年12月1日から平成27年11月30日までが1年間で、1年に満たない端数期間は平成27年12月1日から平成28年8月1日となります。②このうち、平成27年は平年、平成28年は閏年ですので、端数期間のうち平年日は平成27年12月1日から平成27年12月31日まで31日間、端数期間のうち閏年日は平成28年1月1日から平成28年8月1日まで214日間となります。これを関数一つで計算するのが、この FRACDAYS 関数です。(Excel用)裁判所における計算方法に従った利息計算を行う自作関数 INTCAL - 法律事務雑記帳と併せてお使いください。
なお、INTCAL と同じく、ktDATEDIF(http://addinbox.sakura.ne.jp/Excel_Tips05.htm#ktDATEDIF2)が必要です。
使い方
=FRACDAYS(期間開始日, 期間終了日, [平年閏年の指定, ][初日算入の指定])
「平年閏年の指定」以下は省略可です。省略した場合、「平年閏年の指定」は「平年」、「初日算入の指定」は「初日不算入(片端入れ)」となります。
「平年閏年の指定」には、0又は1を入力してください。0の場合は「平年」、1の場合は「閏年」となります。
「初日算入の指定」には、0又は1を入力してください。0の場合は「初日不算入(片端入れ)」、1の場合は「初日算入(両端入れ)」となります。
使用例
平成26年12月1日から平成28年8月1日までの期間(初日算入〔両端入れ〕)について
(1) 1年に満たない端数期間の平年日を調べるとき
=FRACDAYS("2014/12/1", "2016/8/1", 0, 1) → 31
(2) 1年に満たない端数期間の閏年日を調べるとき
=FRACDAYS("2014/12/1", "2016/8/1", 1, 1) → 214
FRACDAYS のコード本体
'端数の日数を平年・閏年に分けて計算する関数 FRACDAYS Function FRACDAYS(期間開始日 As Date, 期間終了日 As Date, Optional 平年閏年の指定 As Integer = 0, _ Optional 初日算入の指定 As Integer = 0) 'opt4 平年閏年の指定 ' 0(default) 平年 ' 1 閏年 'opt2 初日算入の指定 ' 0(default) 初日不算入(片端入れ) ' 1 初日算入(両端入れ) date_begin = 期間開始日 date_end = 期間終了日 opt4 = 平年閏年の指定 opt2 = 初日算入の指定 '初日算入の処理 date_begin = date_begin - opt2 Dim y, d As Integer Dim common_year_days As Integer '平年の日数 Dim leap_year_days As Integer '閏年の日数 y = ktDATEDIF(date_begin, date_end, "Y") date_recent = DateAdd("yyyy", y, date_begin) '端数期間が同じ年の場合 If year(date_recent) = year(date_end) Then d = ktDATEDIF(date_begin, date_end, "YD") If IsLeapYear(year(date_end)) Then '端数期間が閏年のとき common_year_days = 0 leap_year_days = d Else '端数期間が平年のとき common_year_days = d leap_year_days = 0 End If '端数期間が別の年の場合 Else days_first = ktDATEDIF(date_recent, DateSerial(year(date_recent), 12, 31), "YD") days_second = ktDATEDIF(DateSerial(year(date_recent), 12, 31), date_end, "YD") '最初の期間が閏年のとき If IsLeapYear(year(date_recent)) Then common_year_days = days_second leap_year_days = days_first '後の期間が閏年のとき ElseIf IsLeapYear(year(date_end)) Then common_year_days = days_first leap_year_days = days_second 'いずれも平年のとき Else common_year_days = ktDATEDIF(date_begin, date_end, "YD") End If End If Select Case opt4 Case 0 FRACDAYS = common_year_days Case 1 FRACDAYS = leap_year_days End Select 'optが指定の数字以外の場合にエラーを出す If Not (((opt2 = 0) Or (opt2 = 1)) And ((opt4 = 0) Or (opt4 = 1))) Then FRACDAYS = CVErr(xlErrValue) End If End Function '閏年判定 Function IsLeapYear(yyyy As Integer) As Boolean Dim b As Boolean If ((yyyy Mod 4) = 0 And (yyyy Mod 100) <> 0 Or (yyyy Mod 400) = 0) Then b = True Else b = False End If IsLeapYear = b End Function
「関数ライブラリ」からダイアログボックスにより FRACDAYS 関数を利用する
Sub RegisterFRACDAYS() Application.MacroOptions Macro:="FRACDAYS", _ Description:="1年に満たない端数部分につき、平年の日数又は閏年の日数を返します。", _ Category:="日付/時刻", ArgumentDescriptions:=Array( _ "には対象となる期間の開始日を指定します。", _ "には対象となる期間の終了日を指定します。", _ "0: 平年(default)" & vbCrLf & "1: 閏年", _ "0: 初日不算入(片端入れ)(default)" & vbCrLf & "1: 初日算入(両端入れ)"), _ HelpFile:="" End Sub
(Excel用)裁判所における計算方法に従った利息計算を行う自作関数 INTCAL
裁判所における計算方法に従った利息計算を行うユーザー定義関数 INTCAL を作りました。
自作関数でExcel作業の幅を「すんごく」効率的にするには | Excel VBAのお勉強を参考にして、「標準モジュール」内に下記コードをコピペすると、シート上で INTCAL 関数が使えるようになります。
東京地方裁判所民事執行センター「さんまエクスプレス〈第40回〉債権執行書記官室の紹介(その1)債権受付係」金融法務事情1840号28頁やhttp://www.courts.go.jp/tokyo/vcms_lf/suk_risokukeisan_207.pdfに記載の方法、すなわち端数期間暦年閏年説で利息計算します。端数期間暦年閏年説については金利計算と閏年・端数処理についてや遅延損害金の計算方法 - 刑裁サイ太のゴ3ネタブログが詳しいです。裁判用利息単発計算の詳細情報 : Vector ソフトを探す!と同様の計算方法です。
VBAの DateDiff 関数は使い勝手が悪いので、ktDATEDIF(http://addinbox.sakura.ne.jp/Excel_Tips05.htm#ktDATEDIF2)を使用します。同ページからktDATEDIFのコードを引っ張ってきて追記するか、あるいはkt関数アドイン(http://addinbox.sakura.ne.jp/ktfunc_main.htm)を登録してください。
もし計算方法の誤りにお気づきの方は御指摘ください。
なお、計算の正確性を保証するものではないので、御利用は自己責任でお願いいたします。
使い方
=INTCAL(元金, 年利, 期間開始日, 期間終了日, [計算方法の指定, ][初日算入の指定, ][一円未満の端数処理の指定])
「計算方法の指定」以下は省略可です。省略した場合、「特約なし」「初日不算入(片端入れ)」「一円未満切捨」で計算します。「特約なし」は、端数期間暦年閏年説による計算を行います。
「計算方法の指定」には、0又は1又は2を入力してください。0は「特約なし」、1は「年365日日割計算」、2は「1年未満の端数部分につき年365日日割計算」です。何も入力しなければ「特約なし」となります。
「初日算入の指定」には、0又は1を入力してください。0は「初日不算入(片端入れ)」、1は「初日算入(両端入れ)」です。何も入力しなければ「初日不算入(片端入れ)」となります。
「一円未満の端数処理の指定」には、0又は1又は2を入力してください。0は「切捨」、1は「四捨五入」、2は「切上」です。何も入力しなければ「切捨」となります。
使用例
http://www.courts.go.jp/tokyo/vcms_lf/suk_risokukeisan_207.pdfの例を用います。すなわち、元金100,000円、年利は年18%として計算します。いずれも初日算入です。
(1) 特約なし 期間H3.8.2~H5.8.15
=INTCAL(100000, 0.18, "1991/8/2", "1993/8/15", 0, 1, 0) → 36690
(2) 特約なし 期間H3.8.2~H5.7.15
=INTCAL(100000, 0.18, "1991/8/2", "1993/7/15", 0, 1, 0) → 35141
(3) 年365日日割計算 期間H3.8.2~H5.7.15
=INTCAL(100000, 0.18, "1991/8/2", "1993/7/15", 1, 1, 0) → 35210
(4) 1年未満の端数部分につき年365日日割計算 期間H3.8.2~H5.7.15
=INTCAL(100000, 0.18, "1991/8/2", "1993/7/15", 2, 1, 0) → 35161
遅延損害金の計算方法 - 刑裁サイ太のゴ3ネタブログの例でも確認しておきます。元金100,000円、年利は年5%、期間はH20.4.2~H24.9.25、特約なし、初日算入、一円未満は四捨五入とします。
=INTCAL(100000, 0.05, "2008/4/2", "2012/9/25", 0, 1, 1) → 22418
ユーザー定義関数 INTCAL のコード本体
'裁判所における計算方法に従った利息計算(interest calculation) Function INTCAL(元金 As Currency, 年利 As Double, 期間開始日 As Date, 期間終了日 As Date, _ Optional 計算方法の指定 As Integer = 0, Optional 初日算入の指定 As Integer = 0, _ Optional 一円未満の端数処理の指定 As Integer = 0) amount = 元金 i = 年利 date_begin = 期間開始日 date_end = 期間終了日 opt1 = 計算方法の指定 opt2 = 初日算入の指定 opt3 = 一円未満の端数処理の指定 'opt1 計算方法の指定 ' 0(default) 特約なし ' 1 年365日日割計算 ' 2 1年に満たない端数部分につき年365日日割計算 'opt2 初日算入の指定 ' 0(default) 初日不算入 ' 1 初日算入 'opt3 一円未満の端数処理の指定 ' 0(default) 切捨 ' 1 四捨五入 ' 2 切上 Dim ans As Currency Dim y As Integer Dim m As Integer Dim d As Integer '初日算入の処理 date_begin = date_begin - opt2 '特約なし If opt1 = 0 Then y = ktDATEDIF(date_begin, date_end, "Y") '年数 date_recent = DateAdd("yyyy", y, date_begin) '端数期間の初日 '端数期間が同じ年の場合 If year(date_recent) = year(date_end) Then d = ktDATEDIF(date_begin, date_end, "YD") If IsLeapYear(year(date_end)) Then '端数期間が閏年のときは端日数dを366で割る ans = amount * i * y + amount * i * d / 366 Else '端数期間が平年のときは端日数dを365で割る ans = amount * i * y + amount * i * d / 365 End If '端数期間が別の年の場合 Else '端数期間に閏年が含まれるとき If IsLeapYear(year(date_recent)) Or IsLeapYear(year(date_end)) Then days_first = ktDATEDIF(date_recent, DateSerial(year(date_recent), 12, 31), "YD") '端数期間の先の方の年の日数 days_second = ktDATEDIF(DateSerial(year(date_recent), 12, 31), date_end, "YD") '端数期間の後の方の年の日数 '端数期間の先の方が閏年のとき If IsLeapYear(year(date_recent)) Then ans = amount * i * y + amount * i * days_first / 366 + amount * i * days_second / 365 '端数期間の後の方が閏年のとき Else ans = amount * i * y + amount * i * days_first / 365 + amount * i * days_second / 366 End If '端数期間がいずれも平年のとき Else d = DateDiff("d", date_recent, date_end) ans = amount * i * y + amount * i * d / 365 End If End If '年365日日割計算 ElseIf opt1 = 1 Then d = DateDiff("d", date_begin, date_end) ans = amount * i * d / 365 '1年に満たない端数部分につき年365日日割計算 ElseIf opt1 = 2 Then y = ktDATEDIF(date_begin, date_end, "Y") d = ktDATEDIF(date_begin, date_end, "YD") ans = amount * i * y + amount * i * d / 365 End If '一円未満の端数処理 Select Case opt3 Case 1 '四捨五入 ans = Application.WorksheetFunction.Round(ans, 0) Case 2 '切上 ans = Application.WorksheetFunction.RoundUp(ans, 0) Case Else '切捨 ans = Application.WorksheetFunction.RoundDown(ans, 0) End Select If ((opt1 = 0) Or (opt1 = 1) Or (opt1 = 2)) And ((opt2 = 0) Or (opt2 = 1)) And ((opt3 = 0) Or (opt3 = 1) Or (opt3 = 2)) Then INTCAL = ans Else 'optが指定の数字以外の場合にエラーを出す INTCAL = CVErr(xlErrValue) End If End Function '閏年判定 Function IsLeapYear(yyyy As Integer) As Boolean Dim b As Boolean If ((yyyy Mod 4) = 0 And (yyyy Mod 100) <> 0 Or (yyyy Mod 400) = 0) Then b = True Else b = False End If IsLeapYear = b End Function
「関数ライブラリ」からダイアログボックスにより INTCAL 関数を利用する
下のSubプロシージャ RegisterINTCAL を実行すると、「関数ライブラリ」からダイアログボックスで関数 INTCAL を入力できます。
Excel ユーザー定義関数に機能説明や独自ヘルプをリンクする方法 – Japan Office Developer Support Blogを参考にしました。
Sub RegisterINTCAL() Application.MacroOptions Macro:="INTCAL", Description:="利息計算をする関数です。", _ Category:="財務", ArgumentDescriptions:=Array("には利息計算の対象となる元金を指定します。", _ "には年利を指定します。", _ "には利息計算の対象となる期間の開始日を指定します。", _ "には利息計算の対象となる期間の終了日を指定します。", _ "0: 特約なし(default)" & vbCrLf & "1: 年365日日割計算" & vbCrLf & _ "2: 1年に満たない端数部分につき年365日日割計算", _ "0: 初日不算入(default)" & vbCrLf & "1: 初日算入", _ "0: 切捨(default)" & vbCrLf & "1: 四捨五入" & vbCrLf & "2: 切上"), _ HelpFile:="" End Sub
PERSONAL.XLSB の ThisWorkbook などに下のものを書いておくと、ブックを開くと自動的に RegisterINTCAL を実行するので便利です。これは
vba - When to set MacroOptions for Excel Addin - Stack Overflowを参考にしました。
Private WithEvents App As Application Private Sub App_WorkbookActivate(ByVal Wb As Workbook) RegisterINTCAL End Sub Private Sub Workbook_Open() Set App = Application End Sub
いろいろなブックで INTCAL 関数を使いたいときは
いろいろなブックで繰り返し INTCAL 関数を使う場合は、アドインを利用すると便利です。次のページを参考にしてください。
ユーザー定義関数を使う(アドイン) | オフィスソフト活用・業務効率化Q&Aブログ