➊の計算式
1行目
=DATE(年,月+当列の左側のセル,27)
2行目以降
=IF(該当の支払回数行=” “,” “,DATE(年,月+当列の左側のセル,27))
➊の列は約定支払日を求めるもので、借入月の翌月から毎月27日限り支払う前提です。
➋の計算式
1行目
=DATE(貸付の年,貸付の月,貸付の日)-当該セルの左側のセル(利用年月日)
2行目以降
=IF(該当の支払回数行=” “,” “,前回返済日-今回返済日)
前回の約定返済日から次回の返済日までの利息計算期間を求めます。
❸の計算式
1列目
=第1回目の返済金額
2列目以降
=IF(該当の支払回数行=” “,” “,第2回目以降の返済金額)
貸付条件入力欄に表示されている返済金額を絶対参照で指定するか、金額を入力します。
❹の計算式
各列
=IF(A=” “,” “,
IF(OR(A=B,A=B+12,A=B+24,A=B+36,A=B+48,A=B+60,A=B+72,A=C,A=C+12,A=C+24,A=C+36,A=C+48,A=C+60,A=C+72),D,0))
A;該当行の最も左の数値、何回目の支払かの値
B;初回ボーナスまでの期間をセルの番号で指定
C;2回目のボーナスまでの期間をセルの番号で指定
D;ボーナス払い額
❺の計算式
各列
=A+B
A;毎月払額をセルの番号で指定
B;ボーナス払額をセルの番号で指定
該当月の支払額合計を求めます。
➏の計算式
各列
=2×(支払総額-元金)×(支払回数+1-何回目の返済)÷(支払回数×(支払回数+1))
七八分法による支払月毎の利息を求めます。
➐の計算式
各列
=利息×365/(元金残×前回返済日から今回返済日までの期間)
初回については、前回返済日を貸付日と読み替えます。
単月の実質月利を求め年率に換算しています。(あくまで参考値です)
➑の計算式
各列
=前回返済後の元金残×前回返済日から今回返済日までの期間
積数を求めます。
初回については、前回返済後の元金残を元金(貸付額)と、前回返済日を貸付日と読み替えます。
➒の計算式
1列目
=支払金額計-利息
元金返済額(充当額)を求めます。
➓の計算式
各列
=前回返済後の元金残-元金充当額
当月返済後の元金残高を求めます。
初回については、前回返済後の元金残を元金(貸付額)と読み替えます。
以上の計算結果から平均残高法で実質金利を求めますが、引き直し計算を行わないと正確な実質金利であるか確認出来ませんので、暫定実質金利とします。
=ROUND(利息合計÷積数合計×365,4)
＝四捨五入(119,800÷583,072,015×365,有効桁数下4桁)
＝7.50％
上記の表中最下部の赤地に白抜き文字が計算式で求めた暫定実質金利です。
暫定実質金利の検証
暫定実質金利7.5％が正しいか引き直し計算を行います。
下表が引き直し計算結果ですが途中計算を省略しています。
引き直し計算の結果、最終行➊の欄の値が-255になっていることから
⇒元金の返済が貸付金を超える過払金が発生している
⇒貸付金利7.50％は低すぎ貸付金利は7.5％超である
ことが明らかですので、0.01％高い7.51％で再度引き直し計算します。
再計算の結果、最終行➋の欄の値が-36になり大分0に近くなりました。
引き直し計算を行った7.50％と7.51％の残元金➊及び➋の値から実質金利を確定することが出来ますが、残元金がプラスになる(未払元金が残っている状態)金利で計算した方がより正確になります。
因みに、7.52％で引き直し計算した場合は、残元金は、144円になります。
ここでは、検証用実質金利①7.50％と②7.51％の残元金を比例按分して残元金が「0」になる金利を求めます。
または、検証用実質金利②7.51％と7.52％残元金を比例按分して残元金が「0」になる金利を求めることも出来ます。残元金が両方同じマイナスやプラスよりもプラス・マイナスの方が僅かに正確になります。
計算式は、
＝ROUNDUP(検証用実質金利7.50％+0.0001×検証用実質金利7.50％の最終残元金÷(検証用実質金利7.50％の最終残元金)-検証用実質金利7.51％の最終残元金)),表示桁数)
実際の数値を代入すると
＝0.0750＋0.0001×(-225)÷((-225)-(-36))
=0.075120（7.5120％）
実質金利7.51％と7.52％の残元金を基に計算した場合
=0.0751+0.0001×(-36)÷((-36)-(144))
=0.075120（7.5120％）
上記の計算の考え方を図で表すと右のようになります。
例えば、検証用実質金利7.51％と7.52％で説明しますと金利差0.01％で過不足金は144+36の合計180円になります。
それでは、36円は何％になるか計算すると、0.01％×36÷180＝0.002％になります。
7.51％より0.002％高いので7.512％が確定実質金利になります。
確定実質金利の検証
検証のため求められた確定実質金利7.512％で引き直し計算をすると、
になります。
確定実質金利7.5120％の引き直し計算の結果過不足金Ⓐの2円は誤差の範囲で実質金利7.5120％は正しいと言えます。