Excel でローン計算

最終更新日：2010/08/17

住居などローンを借りるときに計算をすることは多々ありますが，Excel でももちろん計算できます．参考文献のサイトにはいろいろと書かれていますので，参考になると思います．

毎月の支払額等を考える

参考サイトのデータを参考に考えてみます．借入金 2,000 万円で固定年利が 4%で，35 年間で返却する場合，毎月の返済額を考えます．この場合，毎月で考えますので，固定年利を固定月利にするため，4%÷12=0.003333…(%)．支払回数は 35×12=420 回となります．このことを次の図のように Excel で考えてみましょう．

この場合，月の支払いは 88,555 円で 420 回目の支払いで残金が -47.33 円になっています．実際は小数点以下は支払えないこともありこのように，420 回目でちょうど終了することはまれで，最終調整することになります．具体的な式は以下のようになります．

ちなみにこの月の支払額は具体的に上記の計算環境で，いろいろ変えていけばよいですが，Excel の標準機能のゴールシークを使うと見つけやすいでしょう．「データ」タブの「データツール」＞「What-If 分析」の「ゴールシーク」を選びます．ゴールシークは目標とするセルをある値にするためにあるセルの値を変化させる機能です．今回のケースだと以下の図のようになります．

図のように設定して，「OK」をクリックすると 88,555 円（実際は 88554.9482066632 円）が求められます．もちろん支払は日本円は整数になるため，整数値での近似解を求めることになります．ちなみに似たような機能の Excel のソルバーも使えます．なおソルバーは Excel のアドインですので，アドインの設定画面（Office ボタンで，Excel のオプションから設定します）でソルバーを選びます．アドインではこのソルバーと分析ツールをチェックすることを前提に紹介します．ソルバーをチェックしておくと「データ」タブの「分析」＞「ソルバー」を選びます．なお，ソルバーで整数解に限定できますが，きっちり整数解では求められませんので，注意してください．

▲ページのトップへ

毎月の支払額等を関数で求める

上の方法だと各月の支払い状況が見えるので有用ですが，一気に求められた方がよい場合があります．そのときは Excel 関数で求めることができます．

PMT 関数を使うと固定金利のときの毎月（毎回？）の支払額を求めることができます．PMT(1 回の利率,支払回数,借入金)（※Excel のコメントを少し意訳しています）で求めることができます．今回のケースだと，PMT(0.04/12,420,20000000) = - 88555 円となります．負になっているのは支払だからでしょうかね．表示として赤字で (88,554) となっています．次の図を見てください．

これを上記の Excel の式にすると支払額ですので，PMT 関数の前に - をつけて正の数にしておきます．計算をみると 420 回目のところが 0 になります．具体的な式は次の図のようになります．

なお，毎月の支払額のうち元金に対しての支払いと利子に対しての支払いを考えることもできます．これまでの計算でも利子を求めていますので，利子に対する支払は（利子から先に払うと考えて）この利子の支払いとして，残りを元金に対する支払としますので，支払額から利子を引いた値になります．これも Excel 関数で一気に求めることができます．利子は IPMT 関数，元金は PPMT 関数です．それぞれ，1 回の利率，何回目の支払，総支払回数，借入金で指定します（これも Excel のコメントを少し意訳しています）．定義から PMT = IPMT + PPMT の関係になります．図では B 列から F 列までを非表示にしているので，注意してください．

数値でみると以下のようになります．画像のサイズの関係で，一部の列を非表示にしているので注意してください．

この利子合計，支払合計も SUM 関数を用いると求められますが，これも関数を用いると一気に求められます．CUMIPMT 関数を用いるとある支払回数からのある支払回数までの支払う利子総額が求まります．また CUMPRINC 関数を使うと同様に元金に対する支払い総額を求めることができます．それぞれ，1 回の利率，総支払回数，借入金，調べたい開始の支払回数，調べたい終了の支払回数，0 で指定します（これも Excel のコメントを少し意訳しています）．最後の 0 は今回は省略不可で 0 と入力してください．また定義から調べたい開始の支払回数は 1 回目として 1 とし，調べたい終了の支払回数は終了とすると 420 になります．定義から 1 から 420 とした場合は，CUMPRINC は借入金になります．また支払総額 = CUMIPMT + CUMPRINC（借入金）の関係になります．図では B 列から F 列までを非表示にしているので，注意してください．図は式と値です．省略の列は上記を参考にしてください．