色々なパターンを自分で想定、編集できるように、複利のローン返済シュミレーションをExcelを使って自作する方法を紹介したいと思います。
ここではボーナス返済を考慮しません。
ボーナス払いをすれば月々の返済額は負担が少なくなりますが、長いローン期間でボーナスは安定してあてにできるのか・・？不安に思う人も少なくないと思います。
ボーナス払いを除いたら、どのくらいの返済計画が建てられるのか？を参考に試算してみるのも良いですよね。
Excelで住宅ローンシュミレーターを作ってみる
1. Excelを開き、下図のように基本情報を打ち込む
「借入金額」の下のセル（枠）「A4」を右クリック⇒「セルの書式設定」⇒「数値」を選んで「桁区切り（,）を使用する」のチェックボックスにチェックを入れます。
月々の返済額を導きだしてみる
2. 下図のように、返済回数、月々返済額、月々の返済額の内訳（元金と利息）の枠を設けます。
3. 「借り入れ金額」「返済期間（月回数）」「利率」の下のセル（枠）に数値を打ち込みます。
今回も先ほどから出している、例の数値にしていきます。
4. 今度は「月返済額」の下のセル（枠）に「＝」を打ち込み、下図の②の赤丸をクリックします。
5. すると「関数の挿入」というウィンドーが出てくるので、➀のプルダウンメニューをクリックし、②の「財務」を選択して「OK」を押します。
6. 「財務」を選択したら、その中の「PMT」を選んで「OK」をクリックします。
（項目はアルファベット順に並んでいます）
7. 「OK」をクリックすると、また新たなウィンドーが現れます。
・「利率」の項目は「3.00％」と打ち込んであるセルをクリック選択し、「/12」と入力し、「月利」に変換します。
・「期間」の項目は「420」と打ち込んであるセルをクリック選択します。
・「現在価値」の項目は「30,000,000」と打ち込んであるセルをクリック選択します。
・「将来価値」の項目は最終的に「0円」になるよう返済するので、「0」と入力します。
・「支払期日」は返済を期首に行うか、期末に行うか、の選択で、「1：期首」「0：期末」という意味で、空白にした場合「0：期末」を選択した意味になります。
ここは、空白で構わないと思います。
最後に「OK」をクリックします。
8. すると、「＝」を打ち込んだセルに「￥－115,455」と数値が入ります。
ただし、マイナスの数値だと見づらいので編集します。
「＝」と「PMT」の間に「0－」と入力して「Enter」
でマイナスの数値がプラスに変換されました。
9. ついでに同じ箇所のアルファベットの前後に下図のように「＄」を打ち込んでください。
これで、下までオートフィルにしても数値が崩れません。
オートフィルとはセル（枠）の右下にマウスポインタが「十字」になるように合わせて、そのまま左クリックしながら（ドラッグしながら）下にマウスを降ろしていくと下のセル（枠）が同じ数式になる機能です。
これで、月々の返済額を導きだすことができました。
あとは、利率や返済期間を自由に打ち込んで、色々と調べることができます。
返済額の内訳を導きだしてみる （利息編）
1. 金利の下のセル（枠）に「＝」と打ち込み、下図の②をクリックします。
2. 「関数の挿入」ウィンドーが現れるので「財務」⇒「IPMT」を選び「OK」をクリックします。
3. 新たに「関数の引数」ウィンドーが現れます。
・「利率」の項目は「3.00％」と打ち込んであるセルをクリック選択し、「/12」と入力し、「月利」に変換します。
・「期」は支払い回数を表しますので、「返済回数（回目）」の下のセル（枠）「1」をクリックします。
・「期間」の項目は「420」と打ち込んであるセルをクリック選択します。
・「現在価値」の項目は「30,000,000」と打ち込んであるセルをクリック選択します。
・「将来価値」の項目は最終的に「0円」になるよう返済するので、「0」と入力します。
4. 「OK」ボタンを押すと、下図のように「１回目」の金利が算出されます。
ここでも、マイナス表記をプラスに、オートフィルをしても数値が算出されるように「$」を打ち込み編集します。
（下図参照）
注） 「返済回数（回目）」の下のセル（枠）を意味するA8のAの隣には$を打ち込まないようにしてください。
420回目となると、返済額のうち金利の占める額は288円となっています。
オートフィルのやり方はこちらを参照してください。
返済額の内訳を導きだしてみる （元金編）
1. 元金の下のセル（枠）に「＝」と打ち込み、下図の②をクリックします
2. 「関数の挿入」ウィンドーが現れるので「財務」⇒「PPMT」を選び「OK」をクリックします。
3. 新たに「関数の引数」ウィンドーが現れます。
・「利率」の項目は「3.00％」と打ち込んであるセルをクリック選択し、「/12」と入力して年利を「月利」に変換します。
・「期」は支払い回数を表しますので、「返済回数（回目）」の下のセル（枠）「1」をクリックします。
・「期間」の項目は「420」と打ち込んであるセルをクリック選択します。
・「現在価値」の項目は「30,000,000」と打ち込んであるセルをクリック選択します。
・「将来価値」の項目は最終的に「0円」になるよう返済するので、「0」と入力します。
4. 「OK」ボタンを押すと、下図のように「１回目」の元金の返済額が算出されます。
ここでも、「＝」の後ろに「0－」を打ち込んでマイナス表記をプラスに、オートフィルをしても数値が算出されるように「$」を打ち込み編集します。
（下図参照）
注） 「返済回数（回目）」の下のセル（枠）を意味するA8のAの隣には$を打ち込まないようにしてください。
オートフィルを行って月々の返済額の内訳、元金の部分が見えてきます。
返済額と金利の合計額を算出する
1. ウィンドー枠の固定をして、Excel画面を下にスクロールしても、項目を見られるようにしておきます。
「月返済額」の下のセル（枠）をクリックして選択します。
2. 上の「表示」から「ウィンドー枠の固定」を選びます。
これで、下にスクロールしても項目は隠れないので見やすくなりました。
3. 下図➀のように、「420」の下に「合計」と打ち込み、その右隣りの「月返済額」の合計欄に「＝」、②のアイコンをクリックします。
3. 次に「SUM」を選んでOKします。
4. 数値1に「B8：B427」と入力して、「OK」ボタンを押します。
「B7」は「月返済額」の１回目の支払い（スタート）のセル（枠）です。
「B427」は420回目（ゴール）のセル（枠）です。この2つのセルを入力して初回から最終回までの数値の合計を求める機能です。
6. 隣のセル（枠）にも、それぞれ「元金」「金利」の合計額をオートフィルを使って求めます。
月返済額の合計が表示されているセルの、右下端にカーソルを合わせます。
そしれそもまま、左クリックをして、クリックを押したまま右2つスライド（ドラック）します。
7. それぞれの項目に合計が表示されました。
8. 合計額に数字が表示されなくて、「#####」となった時は、文字が大きさ的にセル（枠）に収まらないという意味ですので、セルの横幅を大きくしたり、文字サイズを小さくしてみてください。
本日も最後までお読み頂き、ありがとうございました。
無料で、資金計画の相談を受けてくれるサイトを紹介します。
コメント