![見出し画像](https://assets.st-note.com/production/uploads/images/85466035/rectangle_large_type_2_341b2457af80274450a2c8cdf5ccdadd.jpeg?width=1200)
誰でもできる!エクセルで簡単入力!ローン返済額の計算方法!
住宅ローンやCARローンなど、利息を伴う返済額を計算するには「何か」が必要ですよね。
時にはローンシミュレーションの「アプリ」やポータルサイトから「自動計算」を利用する人も多いのではないでしょうか。
今回はエクセルで、超簡単に返済額の計算がご自身でできる方法を伝授します。
エクセル関数で「毎年」の借入返済額の計算式が5分程で完成します。
一度完成してしまえば、継続利用できますので、とてもオススメです。
是非、最後までご覧にください。
~計算方法~
融資には、毎年利息を均等に支払う「元利均等返済」と元金を均等に支払う「元金均等返済」の2つがあり、住宅ローンや事業用ローン(プロパーローン)は基本的に元利均等返済が採用されます。
そこで、今回ご紹介するのは「元利均等返済」の計算方法です。
※ここからはエクセルを準備しましょう。
エクセル関数は・・・
「IPMT」関数
「PPMT」関数
を利用して返済額の計算を行います。
IPMT関数
初めに「IPMT」関数ですが、「IPMT」関数は主に「利息」を求める関数です。
まずは下記表のように「金利」「融資期間」「融資金額」を決めてください。
![](https://assets.st-note.com/img/1661421729387-zlHtRsyqTa.png?width=1200)
そしてセルを選択し「IPMT関数」を入力します。
=IPMT(金利, 期, 期間, 現在価値, 将来価値, 支払期日)
![](https://assets.st-note.com/img/1661421739055-tgIdti0F3Y.png?width=1200)
IPMT関数の( )内には金利, 期, 期間, 現在価値, を入力し、将来価値, 支払期日は入力しなくて大丈夫です。
そして、全ての単位を同じ値にする為に以下の点に注意してください。
・「利率」は月利(年利÷12)で指定
・「期間」は月数(年数×12)を指定
尚、「期」とは融資期間に伴う支払回数で、今回の場合「利息」の支払回数を意図します。
そこで、初めに「期」の前準備が必要なのですが、利息を360回(30年)まで自動計算できるようシステム化したいので、下のように「期」の作成を行いましょう。
Mの列まで1~12の数字を入力
![](https://assets.st-note.com/img/1661421752924-WeMEwERltx.png?width=1200)
次にセルB8に「=B8+12」と入力し・・・
![](https://assets.st-note.com/img/1661421761911-uwwVMklFbK.png?width=1200)
Mの列まで「オートフィル」
![](https://assets.st-note.com/img/1661421769156-f1t0yiSRly.png?width=1200)
赤い点をドラッグし・・・
![](https://assets.st-note.com/img/1661421776844-KWzTVQNVbi.png?width=1200)
37の行までオートフィル
![](https://assets.st-note.com/img/1661421785592-RrXcpieFI6.png?width=1200)
Aの列に何年目の支払期か分かりやすいように年度を記入
![](https://assets.st-note.com/img/1661421794997-6rI2eidyTQ.png?width=1200)
全体をマス目で囲う
![](https://assets.st-note.com/img/1661421803025-7Zvh8yoxrb.png?width=1200)
最後にマス目を入れた範囲を全て選択して、ショートカットキー「ctr+t」を同時に押しEnterキーを押すと・・・
![](https://assets.st-note.com/img/1661421811503-yIA2002FAb.png?width=1200)
色分けができました。
これで「期」の前準備が完成です。
次に、改めてIPMTの( )内に数字を入力していきます。
この時に注意してほしいのは「金利セル:C2」「期間セル:C3」「融資額セル:C4」は、セルを固定したいので、Cを$で挟みます。→金利セルの場合はこんな感じ「$C$2」。
ちなみに、C2を選択して「ファンクションキー:F4」を1回押せば自動的に固定になるので簡単です。
![](https://assets.st-note.com/img/1661421824959-Ge32BBwAUw.png?width=1200)
=IPMTの()内を入力した後、Enterキーをおせば下の通りになります。
![](https://assets.st-note.com/img/1661421834496-QDHRnAtrkY.png?width=1200)
この△66,667円が「セル:B9」で指定した1回目の支払利息の金額となります。
続いて「Z」の列までオートフィルをかけましょう。
![](https://assets.st-note.com/img/1661421843902-4Q0lUuJwt0.png?width=1200)
1回目~12回目の各期の支払利息がでました。これも分かりやすいように上の段に1~12と記入し「セル:AA8」に「支払利息合計」と入力しましょう。
![](https://assets.st-note.com/img/1661421853667-p2siRqUXiQ.png?width=1200)
そして、支払利息合計下の「セル:AA9」を「=SUM(範囲)」関数で合計すれば年間の支払利息合計がでます。
![](https://assets.st-note.com/img/1661421862314-gQ6ALMnCvT.png?width=1200)
今度は「セル:O9」から「セル:AA9」まで指定範囲し、青い点をドラッグし・・・
![](https://assets.st-note.com/img/1661421870379-OQ9KTei3Ti.png?width=1200)
行38までオートフィルをかけます。
![](https://assets.st-note.com/img/1661421878442-6dQqWxOOFl.png?width=1200)
同じく、全体をマス目で囲ってあげて、ショートカットキー「ctr+t」からEnter
で表を見やすくしてあげましよう。
![](https://assets.st-note.com/img/1661421887322-YwF1GoOOqe.png?width=1200)
これがIPMT関数の30年間における各期の支払利息と毎年の支払金額合計となります。
PPMT関数
次にPPMT関数を入力していきます。
PPMT関数は主に元金を計算する関数です。
=PPMT(金利, 期, 期間, 現在価値, 将来価値, 支払期日)
早速「セル:AC9」に関数を入力しましょう。
この時に注意してほしいのが「金利:C2」「期間:C3」「融資額:C4」は、セルを固定したいので、Cを$で挟みます。→こんな感じ「$C$2」。
これも「ファンクションキー:F4」を1回押せば自動的に固定になるので簡単です。
![](https://assets.st-note.com/img/1661421920144-pDOvsZruXz.png?width=1200)
ここまでできたらEnterを押して、102,785円という数字がでました。
これが1回目の元金支払金額になります。
![](https://assets.st-note.com/img/1661421930450-zfL12OjOqo.png?width=1200)
そしてIPMT同様に以下の様な表を作成してみましょう。
![](https://assets.st-note.com/img/1661421941885-qTdxewYmT8.png?width=1200)
ここまでできたら、毎年の支払利息合計「列AA」と支払元金「列AO」を(下記表の選択範囲部分)・・・
![](https://assets.st-note.com/img/1661421992582-Hs8PCbjR8V.png?width=1200)
各年ごとに合計して返済額合計を計算しましょう。「セル:AQ9」に=AA9+AO9を入力してEnter
![](https://assets.st-note.com/img/1661422014649-5mrjddpl13.png?width=1200)
2,034,501円とでました。これが初年度の元金の金額となります。
![](https://assets.st-note.com/img/1661422022544-mHxWsY0mnS.png?width=1200)
そして、各年の元金を出す為に、下までオートフィルをかけます。
![](https://assets.st-note.com/img/1661422032005-YZvgQ312xM.png?width=1200)
元利均等返済なので、毎年の支払は一定の金2,034,501円になります。
ここで一定にならなければどこかが間違っていることになります。
上手くいってれば「セル:AQ8」に「返済額合計」と記入しマス目で囲って色分けすれば完成です。
全体的に俯瞰すると下の表のようになります。
![](https://assets.st-note.com/img/1661422045244-ARn9KGd8M2.png?width=1200)
あとは、「金利」「期間」「融資額」をご自身で変更すれば返済額合計が自動的に計算されますので、是非ご利用ください。
最後までお読み頂きありがとうございました。