IPMT・PPMT関数で返済額・利息・元金を自動計算｜融資判断を誤らないための実務ガイド
不動産投資では、返済計画の精度が投資の成否を左右します。
金利が少し変わるだけでキャッシュフローは大きく変動し、融資条件の違いが“買っていい物件かどうか”の判断を分けます。
この記事では、エクセルを使って 元利均等返済のローンシミュレーションを5分で作成する方法 を、不動産投資と融資に精通した専門家が分かりやすく解説します。
エクセルでローンシミュレーションをする方法の概要
融資には
・利息を均等に支払う「元利均等返済」
・元金を均等に支払う「元金均等返済」
以上の２つがあります。
住宅ローンやアパートローン等は、基本的に「元利均等返済」ですので、今回ご紹介するのは「元利均等返済」の計算方法です。
結論を先に書きますと、エクセル関数の・・・
を利用して返済額の計算を行います。
IPMT関数は利息を、PPMT関数は元利を求め、最後に合算する方法です。
先に、全体的に俯瞰した表をお見せすると下の表のようになります。
一番右の合計が、IPMT関数とPPMT関数で求めた数字、つまり、毎年の返済金額になります。
それでは、それぞれの関数を使ってシミュレーション表を作成してみましょう。
IPMT関数
IPMT関数は主に「利息」を求める関数です。
まずは下記表のように「金利」「融資期間」「融資金額」を決めてください。
そしてセルを選択し「IPMT関数」を以下のように入力します。
IPMT関数の・将来価値, 支払期日は入力しなくて大丈夫です。
また、利率＝金利、期＝支払回数、期間＝年数、となりますが、以下の点に注意してください。
・「利率」は月利（年利÷12）で指定
・「期間」は月数（年数×12）を指定
尚、「期」とは融資期間に伴う支払回数で、今回の場合「利息」の支払回数を意図し、初めに「期」の前準備が必要となります。
下の表でいうと、一番左の表です。
今回は、360回(30年)まで自動計算できるようにしたいので、下のように「期」の作成を行いましょう。
「期」の前準備
Mの列まで１～12の数字を入力
次にセルB8に「＝B8＋12」と入力し・・・
Mの列まで「オートフィル」
赤い点をドラッグし・・・
37の行までオートフィル
Aの列に何年目の支払期か分かりやすいように年度を記入
全体をマス目で囲う
最後にマス目を入れた範囲を全て選択して、ショートカットキー「ctr＋ｔ」を同時に押しEnterキーを押すと・・・
色分けができました。
これで「期」の前準備が完成です。
IPMTの( ）内に数字を入力
IPMTは、下の表でいうと真ん中の表になります。
それでは、改めてIPMTの（ ）内に数字を入力していきます。
はじめに注意してほしいのが、「金利のセル」「期間のセル」「融資額のセル」は、セルを固定(ロック)したいので、「＄」で挟みます。
例：金利のセルはC2なので、こんな感じ ⇒ ＄C＄２
※ちなみに、C2を選択して「ファンクションキー：F4」を1回押せば自動的に固定になるので簡単です。
＝IPMTの（）内を入力した後、Enterキーをおせば下の通りになります。
この△66,667円が「B9(セル)で指定した1回目の支払利息」となります。
続いて「Z」の列までオートフィルをかけましょう。
「1回目～12回目の各期の支払利息」がでました。
これも分かりやすいように上の段に1～12と記入し、最後に「支払利息合計」と入力しましょう。
「支払利息合計」下部に、「＝SUM（範囲）」関数で合計を計算すれば、年間の支払利息合計がでます。
今度は「セル：O9」から「セル：AA９」まで指定範囲し、青い点をドラッグし・・・
行38までオートフィルをかけます。
同じく、全体をマス目で囲ってあげて、ショートカットキー「ctr＋t」からEnter
で表を見やすくしてあげましよう。
ここまでが、各期(30年間)の「支払利息」と「支払金額合計」になります。
PPMT関数
次にPPMT関数を入力していきます。
下の表でいうと、IPMT表の右にある表です。
PPMT関数は主に元金を計算する関数です。
早速「セル：AC９」に関数を入力しましょう。
この時も注意してほしいのが、金利：期間：融資額のセルの固定です。
例： ＄C＄２
※これも「ファンクションキー：F4」を1回押せば自動的に固定になるので簡単です。
PPMT関数を入力して、Enterを押すと 102,785円という数字がでました。
これが1回目の元金支払金額になります。
そして前回同様に以下の様な表を作成してみましょう。
ここまでできたら、毎年の支払利息合計「列AA」と支払元金「列AO」を・・・
各年ごとに合計して返済額合計を計算しましょう。
「セル：AQ９」に＝AA9＋AO9を入力してEnter
2,034,501円とでました。これが初年度の元金の金額となります。
そして、各年の元金を出す為に、下までオートフィルをかけます。
元利均等返済なので、毎年の支払は一定の金2,034,501円になります。
ここで一定にならなければどこかが間違っていることになります。
上手くいってれば「セル：AQ8」に「返済額合計」と記入しマス目で囲って色分けすれば完成です。
全体的に俯瞰すると下の表のようになります。
あとは、「金利」「期間」「融資額」をご自身で変更すれば返済額合計が自動的に計算されますので、是非ご利用ください。
【お知らせ】
「どの金融機関が自分に最適なのか知りたい」
「年収や資産で、どこまで融資が出るのか確認したい」
といったご相談に対して、WealthAgentでは“無料の銀行調査”を承っております。
お客様の 年収・金融資産・お借入状況 などをもとに、利用できる可能性の高い金融機関を丁寧にお調べし、金利・融資期間・LTV・審査のポイント までわかりやすくご案内いたします。
さらに、金融機関ごとの 最新の融資スタンス や直近の 成功事例 も交えながら、あなたに最適な融資戦略を一緒に設計していきます。
強引な営業等は一切ございませんので、お気軽にお申し込みください。
