知人から利率・借入額・年数から返済の額を算出してくれ。という依頼がありました。
そういう電卓や、計算してくれるサイトは多々あるかと思いますが、Excelで作ってみます。
ただ借入金の返済方法には主に３つの方法があります。
- 元利均等返済方式
- 元金均等返済方式
- アドオン方式
返済方法にも触れてみたいと思います。
各返済方法の計算式は・・・？
まずはこの計算式が解らないと作れませんのでそこの説明から。
元利均等返済方式
上図のように毎月の返済金額（元金＋利息）を均等にした方式。ローンで最も普及した返済方式。裁判所の調停では，一般に，この返済方式が用いられています。
【メリット】
・ 毎回の返済額が一定で，返済計画が立てやすい。
・ 返済するにつれ，元本が増加，利息減少。
・ 元金均等返済に比し，当初返済額が少なくて済む。
【デメリット】
・ 元金均等返済に比し，総返済額が多くなる。
【計算式（毎月の返済金額）】
（借入金額 × 月利） ÷ （１ － （１ ＋ 月利）－返済回数
※月利は年利÷１２です。
元金均等返済方式
毎回の元金の返済額を均等にした返済方式
【メリット】
・ 毎回確実に均一に元金部分が減少，これにつれ利息も逓減。
・ 元利均等返済に比し，総返済額が少なくてすむ。
【デメリット】
・ 当初の返済額が多くなる。収入が多くないと借りにくい。
【計算式（毎月の返済金額）】
（借入金額 ÷ 返済回数） × （１ ＋ （返済回数 － 計算対象月 ＋ １） × 月利）
※月々同額の支払いとならない。元金は一定だが利息が変わる為。
アドオン方式
毎回の返済額が一定で，しかも，利息計算の際，元本の減少を認めない方式。
割賦販売商品の代金返済，少額の消費者金融等に適用されている。
【メリット】
・ 毎回の返済額が均一で，計算が簡単。返済計画が立てやすい。
【デメリット】
・ 返済終了時点まで当初元本に対する利息を付すので，実質金利は高い。
・ 実質金利月利は，元利均等返済方式よる月利より，大体1.4～1.9倍多いといわれている。
【計算方法（毎月の返済金額）】
（借入金額 × （１ ＋ 月利 × 返済月数）） ÷ 返済月数
じゃあExcelでどうすんの
うだうだと金利について説明しましたが本題です。Excelでどうやって作るのか。
まずこの表を見てください。一般的なローン返済方法、元利均等返済方式を使っています。
上でも載せましたが、これを使って説明していきます。
B1の借入額、D1の年利、F1の年数は可変としています。
①月目の元金は当然借入額そのまま「=B1」ですね。
では①月目の利息は・・・というと
「=B4 * (D1 / 12)」
と、元金に月利を掛けたものになります。
①月目の返却額は上記の計算式を使って・・・
「=(B1 * (D1 / 12)) / (1 – (1 + (D1 / 12)) ^ -(F1 * 12))」
となります。
Excelで乗数を使う場合は「＾」を使います。
元金＋利息－支払額＝その月の元金残
これを月々計算を繰り返すと３６ヶ月後には残が０となるわけです。
ちなみに小数点以下の四捨五入はお忘れなく。。
もっと利息計算について詳しく知りたい場合はこちら
次回［ボタンで返済方法を切り替える］