誰でもできる！エクセルで簡単入力！ローン返済額の計算方法！

住宅ローンやCARローンなど、利息を伴う返済額を計算するには「何か」が必要ですよね。

時にはローンシミュレーションの「アプリ」やポータルサイトから「自動計算」を利用する人も多いのではないでしょうか。

今回はエクセルで、超簡単に返済額の計算がご自身でできる方法を伝授します。

エクセル関数で「毎年」の借入返済額の計算式が５分程で完成します。

一度完成してしまえば、継続利用できますので、とてもオススメです。

是非、最後までご覧にください。

～計算方法～

融資には、毎年利息を均等に支払う「元利均等返済」と元金を均等に支払う「元金均等返済」の２つがあり、住宅ローンや事業用ローン(プロパーローン)は基本的に元利均等返済が採用されます。

そこで、今回ご紹介するのは「元利均等返済」の計算方法です。

※ここからはエクセルを準備しましょう。

エクセル関数は・・・

「IPMT」関数
「PPMT」関数

を利用して返済額の計算を行います。

IPMT関数

初めに「IPMT」関数ですが、「IPMT」関数は主に「利息」を求める関数です。

まずは下記表のように「金利」「融資期間」「融資金額」を決めてください。

そしてセルを選択し「IPMT関数」を入力します。
 
＝IPMT（金利, 期, 期間, 現在価値, 将来価値, 支払期日）

IPMT関数の（ ）内には金利, 期, 期間, 現在価値, を入力し、将来価値, 支払期日は入力しなくて大丈夫です。
 
そして、全ての単位を同じ値にする為に以下の点に注意してください。
 
・「利率」は月利（年利÷12）で指定
・「期間」は月数（年数×12）を指定
 
尚、「期」とは融資期間に伴う支払回数で、今回の場合「利息」の支払回数を意図します。

そこで、初めに「期」の前準備が必要なのですが、利息を360回(30年)まで自動計算できるようシステム化したいので、下のように「期」の作成を行いましょう。

Mの列まで１～12の数字を入力

次にセルB8に「＝B8＋12」と入力し・・・

Mの列まで「オートフィル」

赤い点をドラッグし・・・

37の行までオートフィル

Aの列に何年目の支払期か分かりやすいように年度を記入

全体をマス目で囲う

最後にマス目を入れた範囲を全て選択して、ショートカットキー「ctr＋ｔ」を同時に押しEnterキーを押すと・・・

色分けができました。

これで「期」の前準備が完成です。

次に、改めてIPMTの( ）内に数字を入力していきます。

この時に注意してほしいのは「金利セル：C2」「期間セル：C3」「融資額セル：C4」は、セルを固定したいので、Cを＄で挟みます。→金利セルの場合はこんな感じ「＄C＄２」。

ちなみに、C2を選択して「ファンクションキー：F4」を1回押せば自動的に固定になるので簡単です。

＝IPMTの（）内を入力した後、Enterキーをおせば下の通りになります。

この△66,667円が「セル：B9」で指定した1回目の支払利息の金額となります。
続いて「Z」の列までオートフィルをかけましょう。

1回目～12回目の各期の支払利息がでました。これも分かりやすいように上の段に1～12と記入し「セル：AA８」に「支払利息合計」と入力しましょう。

そして、支払利息合計下の「セル：AA9」を「＝SUM（範囲）」関数で合計すれば年間の支払利息合計がでます。

今度は「セル：O9」から「セル：AA９」まで指定範囲し、青い点をドラッグし・・・

行38までオートフィルをかけます。

同じく、全体をマス目で囲ってあげて、ショートカットキー「ctr＋t」からEnter
で表を見やすくしてあげましよう。

これがIPMT関数の30年間における各期の支払利息と毎年の支払金額合計となります。

PPMT関数

次にPPMT関数を入力していきます。

PPMT関数は主に元金を計算する関数です。

＝PPMT（金利, 期, 期間, 現在価値, 将来価値, 支払期日）
 
早速「セル：AC９」に関数を入力しましょう。
 
この時に注意してほしいのが「金利：C2」「期間：C3」「融資額：C4」は、セルを固定したいので、Cを＄で挟みます。→こんな感じ「＄C＄２」。

これも「ファンクションキー：F4」を1回押せば自動的に固定になるので簡単です。

ここまでできたらEnterを押して、102,785円という数字がでました。
これが1回目の元金支払金額になります。

そしてIPMT同様に以下の様な表を作成してみましょう。

ここまでできたら、毎年の支払利息合計「列AA」と支払元金「列AO」を（下記表の選択範囲部分）・・・

各年ごとに合計して返済額合計を計算しましょう。「セル：AQ９」に＝AA9＋AO9を入力してEnter

2,034,501円とでました。これが初年度の元金の金額となります。

そして、各年の元金を出す為に、下までオートフィルをかけます。

元利均等返済なので、毎年の支払は一定の金2,034,501円になります。

ここで一定にならなければどこかが間違っていることになります。

上手くいってれば「セル：AQ8」に「返済額合計」と記入しマス目で囲って色分けすれば完成です。

全体的に俯瞰すると下の表のようになります。

あとは、「金利」「期間」「融資額」をご自身で変更すれば返済額合計が自動的に計算されますので、是非ご利用ください。

最後までお読み頂きありがとうございました。