ローン返済シミュレーション for Excel 活用

1. ローン金利について
   
2. いろいろなシミュレーション
   
   1. 融資額見積り
      
   2. 返済期間見積り
      
   3. ボーナス併用ローン返済シミュレーション
      
   4. 金利変更シミュレーション
      
   5. ローン借換シミュレーション
      
   6. 一部繰上返済シミュレーション
      
   7. 返済額指定シミュレーション
      
   8. 返済途中からシミュレーション
      
3. シミュレーション結果の利用テクニック
   

ローンを借りる際に最も重要なのは金利です。
もちろん、金利が安い（低い）に越したことはないのですが、どのような金利があるのかを知っておくに越したことはありません。「敵を知り味方を知れば、百戦危うからず」です。


• 固定金利型
  
  最初の契約時の金利がずっと適用されるものです。金融機関にとっては、リスクが高いために高めの金利が設定されています。
  借りる方からいえば、わかりやすいし返済額も決まっているので安心ですね。
  
  
• 変動金利型
  
  世の中の金利（長期プライムレート（長プラ）や短期プライムレート（短プラ）など）に合わせて金利が変動するものです。金融機関にとっては、リスクが低いために低めの金利が設定されています。
  一般的な住宅ローンだと、金利見直しは年に２回で４月１日と１０月１日というパターンが一般的です。この金利見直し後、新金利が適用されるのは金融機関により異なっていて、
  　・見直し後の６月返済、または１２月返済後から
  　・見直し後の最初のボーナス返済後から
  などのパターンがあります。
  また、金利が変わっても毎回の返済額は５年ごとに見直すというのが一般的です。その際、大幅な金利上昇があった場合でも返済額の変動は最大２５％アップに抑えられます。この場合、返済額すべてを利息に充ててもまだ足りないというケースが起こり得ますが、この場合、不足分の利息は「未払い利息」という形で累積されます。当然、元本返済はされませんので残高は一定のままです。
  　（「ローン返済シミュレーション for Excel」では未払い利息が発生するパターンはサポートしていません）
  世の中の金利が動くときには、ローンの金利も動くので注意が必要です。金利が下がる場合は歓迎ですが、上がる場合には返済額が増えることになります。これまでのプライムレートの動きは下のグラフをご覧ください。ここのところ、金利が底を打っていましたが、上昇気味になってきそうなのがわかります。
  
  
  
• 固定金利特約型
  
  固定金利と変動金利のいいところを取ってきたような商品です。ある期間（特約期間）は固定金利で、その期間が過ぎたところで再度固定金利にするか、変動金利にするかを選ぶことができます。金融機関にとってはリスクの期間を限定できるので、固定金利より低い金利が設定されています。
  この商品の場合、特約期間を過ぎたところでどのような金利を選択するかがポイントです。
  
  
  やがて金利が上昇すると考える場合はなるべく長い固定金利を選ぶのがよいでしょう。
  数年は金利上昇がないが、その後上昇すると考える場合は、短い期間の固定金利を選んで様子を見るのがよいでしょう。
  当分低金利が続くと考えるならば、最も金利の低い変動金利を選ぶのがよいでしょう。
  
  
  金融機関によっていろいろな商品があり、それぞれいろいろな特徴がありますので、このへんも金融機関を選ぶポイントになります。
  例えば、最長特約期間はどれくらいか、特約期間終了後にどんな金利を選べるのかなどが要注意項目でしょう。もちろん、金利の高低が一番重要なのですが、特約期間後の選択幅によりトータルな支払額は変化しますので、長い目で見て一番お得なものを選びましょう。

• 融資額見積り
  現在借家にお住まいの方が家賃をローン返済に充てるとして、どれくらいの融資を受けることが可能かを見積もってみます。
  例えば、現在12万円の家賃でアパートにお住まいの方が、この家賃を２０年ローンの返済に充てるとしたら、どれくらいの融資を受けることが可能でしょうか。金利は２．５％としましょう。
  
  1. 「ローン返済額 for Excel」を使って融資額を算出します。まず、毎月返済のみとして、「元利均等計算」シートで計算種別「融資金額」を選び、利率を２．５％、返済期間を２０年、返済額を１２万円とすると、約２千２百万円の融資が可能であるということがわかります。
  2. サラリーマンであれば、ボーナス併用にして毎月の返済額を減らすことができるでしょう。毎月の返済額を８万円、ボーナス月の返済加算額を３２万円にしたら、同様にして、約２千５百万円の融資が可能であるということがわかります。
  3. 必要であれば、この結果を「ローン返済シミュレーション」を使って確認してください。
  4. 同様にして、返済期間や金利をいろいろ変えてシミュレーションしてみましょう。
  
  
• 返済期間見積り
  現在借家にお住まいの方が家賃をローン返済に充てるとして、どれくらいの期間返済しなければならないかを見積もってみます。
  例えば、現在12万円の家賃でアパートにお住まいの方が３０００万円の融資を受ける場合、この家賃を返済に充てるとしたら、どれくらいの期間返済しなければならないでしょうか。金利は２．５％としましょう。
  
  1. 「ローン返済額 for Excel」を使って返済期間を算出します。「元利均等計算」シートの計算種別「返済期間」を選び、毎月返済のみであれば、約３０年間の返済期間（融資期間）となることがわかります。
  2. サラリーマンであれば、ボーナス時にも返済して返済期間を短縮しましょう。融資額のうち１０００万円をボーナス返済に充てるとしたら、同じ条件で。約１７年間となります。
  3. 毎月の返済額は「元利均等返済額」シートを選び、返済期間を上で算出した１７年とし、融資額や金利を入力すると、ボーナス月の加算額は約３６万円となることがわかります。
  4. 必要であれば、この結果を「ローン返済シミュレーション」を使って確認してください。
  
  
• ボーナス併用ローン返済シミュレーション
  借入金額や利率・融資期間などを入力することにより、元利均等／元金均等ローンの毎月の返済額を算出したり、毎月の返済明細を作成することができます。
  
  例えば、借入金額：1500万円、融資期間：25年、金利：2.45%、ボーナス併用で返済すると毎月の返済額は約３万６千円、ボーナス月には約19万円加算となります。この場合の返済総額は約２千万円です。
  
  
• 一括完済シミュレーション
  この機能を使って、今借りているローンを一括完済する際に必要な金額を計算できます。また、一括完済すれば返済金をどれくらい節約できるのかといったことを調べることができます。
  
  例えば、1500万円を25年、金利2.45%で借りた場合、
  1. そのまま返済していけば返済総額は約２千万円です。
  2. 10年後に一括完済する場合には約１千万円が必要で、返済総額は約１千８百万円となります。約２百万円節約することができるわけですね。
  
  
• 金利変更シミュレーション
  この機能を使えば、例えば当初固定金利でその後変動金利に変わるような段階金利ローンをシミュレーションすることができます。
  
  例えば、1500万円を25年、当初金利2.45%で借りた場合、
  1. 金利が変わらない場合は返済総額は約２千万円です。
  2. その後金利が上がって、５年後に金利が4.56%になったとすると、条件変更日に2006年4月を指定します。次の条件変更画面で新金利4.56%を指定します。そうすると、毎月の返済額は約７千円多くなり、返済総額は約２千３百万円となります。
  
  
• ローン借換シミュレーション
  条件変更シミュレーションを応用すると、借換シミュレーションが可能です。
  借り換え後の金利を条件変更画面で入力しましょう。
  
  
  【借換え事例】
  金融機関のサイトなどにもいろいろと紹介されていますが、これは貸し手側から書いたもので、眉に唾をつけて読んだ方がいいかもしれません。
  長谷部 敏子さんのサイト「うまれてはじめてWeb」の「はじめての住宅ローン借り替え」には実際に住宅ローンを借り換えされたことが丁寧に書かれており、これから借り換えを計画されている人の参考になると思います。
  
  
  
• 一部繰上返済シミュレーション
  例えば、1500万円を25年、当初金利2.45%で借りていた場合ですと、そのまま返済していけば返済総額は約２千万円です。
  
  
  【融資期間は変更せず返済額を少なくする】
  返済を始めてちょうど10年後に300万円を一部繰上返済するシミュレーションを行います。
  
  
  1. 条件変更を選択して条件変更日に10年後の日付：2011年4月を指定します。
  2. 条件変更画面で毎月分とボーナス分の繰上返済額に150万円ずつ入力します。
     
  3. 返済総額は約１千９百万円余りとなり、返済総額を約50万円少なくすることができることがわかります。
     
  
  
  【返済額を変えないで早めに返済を終わらせる】（その１：返済金額指定）
  
  返済を始めてちょうど10年後に300万円を一部繰上返済すると、どれくらい返済期間を短縮できるかのシミュレーションを行ってみましょう。
  
  
  1. 条件変更を選択して条件変更日に10年後の日付：2011年4月を指定します。
  2. 条件変更画面で毎月分とボーナス分の繰上返済額に150万円ずつ入力します。毎月返済額横の「変更無」ボタンを押します。
     
  3. 返済総額は約１千９百万円となり、返済総額を約50万円少なくすることができることがわかります。また、最終回返済日は2021年7月となり、返済期間を57ヶ月短縮できることがわかります。
  
  
  【返済額を変えないで早めに返済を終わらせる】（その２：返済回数指定）
  
  金融機関によっては、返済回数をキッチリと減らすのでないと一部繰上返済ができないことがあります。この場合、ボーナス併用ではボーナス月でないと一部繰上返済ができないことが多いです。
  以下に返済回数を１２回減らす（１年間の繰り上げ）場合の例を示します。
  
  
  1. 条件変更を選択して条件変更日に10年後のボーナス返済月：2011年7月を指定します。
  2. 条件変更画面では何も入力せずに「条件変更」ボタンを押します。そして一旦シミュレーションを終了して返済明細シートを表示させます。
  3. 条件変更を行った日付付近を表示させ、条件変更後の毎月分返済元本を減らす回数分のセルを選択します。そしてステータスバーに表示された合計（繰り上げ返済額）をメモしておきます。（この例では301,866です）
  4. 同様にボーナス分の返済元本を減らす回数分のセルを選択します。そしてステータスバーに表示された合計（繰り上げ返済額）をメモしておきます。（この例では264,234です）
  5. 再度１と同様に条件変更を選択して条件変更日に10年後のボーナス返済月：2011年7月を指定します。
  6. 条件変更画面で毎月分繰上返済額に上記３でメモした301,866円、ボーナス分の繰上返済額に264,234円を入力します。また、毎月返済額横の「変更無」ボタンを押します。
     
  7. 返済総額は約１千９百８十万円となります。返済総額が約20万円少なくなり、返済期間は１年短縮されたことがわかります。
  
  
• 返済額指定シミュレーション
  この機能によれば、設定した返済額により何年後に返済が終了するかを知ることができます。リボルビングのカードローンや消費者金融ローンなどはこの機能を使ってシミュレーションすることができます。
  
  例えば、15万円を金利19.6%のカードローンで借りて、毎月の返済額を１万円、ボーナス月に３万円を加算すれば、10ヶ月で返済が終了して返済総額は約16万円となります。
  Ｃ銀行など繰上返済の手数料を無料にしている金融機関があります。このような銀行から借りている場合、返済額指定シミュレーションを使えば毎月の返済額に１万円ずつ上乗せしていけばどれくらい早く返済が終わるのか、総返済額はどれくらい少なくなるのかを調べることが可能です。
  
  例えば、3000万円を35年、金利3.45%で借りた場合、
  1. そのまま返済していくと返済総額は約５千１百万円です。
  2. ボーナス月の返済額をそのままに毎月の返済額を２万円増やすと、返済期間は８年短縮され、総返済額は約４千６百万円となります。
  3. 逆に毎月の返済額をそのままにしてボーナス月の返済額に２０万円上乗せすると、融資期間は13年短縮され、総返済額は約４千３百万円となります。
  
  
• 返済途中からシミュレーション
  Ver.8より、返済途中からシミュレーションを行うことができるようになりました。金利変更や一部繰上返済など、条件変更を行っているローンのシミュレーションは面倒なものでしたが、この機能を使えば現在以降のシミュレーションを簡単に行うことができます。
  
  例えば、変動金利ローンでしたら金利が変わるたび、固定金利や固定金利選択型でしたら何年かおきに金融機関から返済予定表が送られてきますので、それを使ってシミュレーションしてみましょう。
  この返済予定表を見ると、このローンは平成14年（2002年）３月現在で金利（5.0%）であることがわかります。
  ボーナス併用ローンなので、11月のボーナス返済後のシミュレーションを行ってみましょう。11月の返済後の残高は20,042,566円（うちボーナス分6,187,129円）で、毎月の返済額は376,769円、ボーナス月の加算額は1,123,270円となっています
  これらのことをもとにシミュレーションを行うことができます。
  
  1. 11月の返済後の残高を融資金額に、新金利を金利に、返済残り期間40ヶ月を融資期間に、旧金利での最後の返済日を融資日に、新金利での返済開始日を初回返済日に入力します。そして、[詳細条件]ボタンをクリックします。
  2. 詳細条件設定画面では、毎月の返済額・ボーナス月加算額を入力し、初回計算方法初回月割を指定して[ＯＫ]をクリックし、メイン画面の[返済明細作成]をクリックします。
  3. シミュレーションが終了して[戻る]ボタンをクリックすると、シミュレーション結果が得られます。この他に金利変更などをシミュレーションする場合は、条件変更または固定金利選択シミュレーションを選択して行うことができます。

Excelを使い慣れた方であれば、以下のことは「あたりまえ」かもしれません。しかし、いただいたいろいろな質問を見ていますと、必ずしも使い慣れた方ばかりではないようなのでここに紹介する次第です。
ここに書いた以外にもいろいろと便利な使い方があると思いますので、メールなどで教えていただけるとありがたいです。


• 年ごとの返済額を知る
  Excelの機能を利用すると、いろいろな切り口での集計が簡単にできます。まず、百万円のローンを例として年ごとの返済額の算出方法を説明しましょう。
  
  1. 返済明細表を作成したあと、まずシート保護を解除してください。
  2. 作成した返済明細表の右に「年」と「返済額」の列を作ります。
     そして、年の列に返済日の年を表す関数「=year(a5)」と入力してください。
     
  3. 同様に、年のセルの右側に返済額の列に返済額を表示する関数「=b5」を入力します。
  4. 年のセルと返済額のセルを選択します。（年のセルを選択して、左ボタンを押しながら返済額のセルまで動かせばよい）
  5. セルの右下にある小さな■（フィルハンドル）のところにカーソルを持っていき、左ボタンを押しながら下に動かします。そして、最終回返済の行まで来たところで左ボタンを放します。
     これで３．で入力した関数がコピーされました。
  6. この状態でデータの集計を行います。
     このとき、このような警告メッセージがでるかもしれませんが、「OK」をクリック。
     集計の設定画面ではそのまま「OK」をクリックします。Excelは賢いですね。
     これで年ごとの返済額を集計することができました。
  7. 左上にあるアウトラインのレベル「２」を指定すると年間返済額合計行のみが表示されます。
     

• いろいろな集計
  上の例では返済額を集計対象としましたが、例えば３．のところで「=C5+G5」とすれば元本の集計が可能ですし、「=D5+H5」とすれば利息を集計対象とすることができます。複数の列を同時に集計することもできますので、必要な項目を指定してください。
  

• 年度ごとの返済額
  上記２．のところで年の列に「=year(a5)」という関数を使ったので暦年ごとの集計を行いましたが、例えば４月が年度始まりの年度別集計を行いたい場合には年の列に次のように入力すれば可能です。
  「=IF(MONTH(A5)<4,YEAR(A5)-1,YEAR(A5))」
  （これは年度が４月から始まるので、月が４未満の時は年から１を減じ、そうでない場合は年をそのまま使うという意味です。上の式をコピーしてExcelのセルに貼り付ければそのまま使えます。）

• 返済予定表に年齢を表示
  掲示板にてkenjiさんより「返済予定表に誕生日を入力して年齢を表示できるようにしてほしい」というご要望をいただきました。(2009/8/19)
  これは返済予定表をちょっといじることで可能になります。
  
  1. 作成した返済明細表の返済日の右側に「年齢」の列を作ります。
     
  2. C3のセルに年齢をyy/mm/ddで入力、C4の列に「年齢」と入力し、C5を選択してメニューの書式−セルの書式で標準を選択します。
     
     
  3. C5のセルに以下の関数を入力します。（これをコピーして貼り付けてください）
     　=ROUND(((YEAR(B5)*12+MONTH(B5)-YEAR($C$3)*12+MONTH(12))/12),0)
     
  4. C5セルの右下にある小さな■（フィルハンドル）のところにカーソルを持っていき、左ボタンを押しながら下に動かします。そして、最終回返済の行まで来たところで左ボタンを放します。
     これで以下のように年齢を表示することができます。
     
     

戻る