この記事には広告を含む場合があります。
記事内で紹介する商品を購入することで、当サイトに売り上げの一部が還元されることがあります。
こんにちは。かわべです。
お金を借りるときに、月々の返済額、借入期間、利率等の条件によって、いくらまで借りれるのか？って気になりますよね。
ExcelのPV関数を使えば、月々の返済額（元利均等返済）、借入期間、利率等の条件を指定して借入可能額をシミュレーションすることができます。住宅ローンなど長期にわたる借入れについては、PV関数が便利です。
[aside type=”yellow”]当たり前ですが、シミュレーションで算出された金額を実際に借りられるかどうかはわかりません。収入金額、家族構成、他の借入れの状況、担保等など様々な要因が借入れには影響するので、希望の金額が借りれない場合もあります。[/aside]
参考
参考 Office Support；PV関数
ExcelのPV関数を使って月々の返済額から借入可能額をシミュレーションする
借入金の返済には一般的に元利均等返済と元金均等返済があり、ExcelのPV関数を利用すれば、月々の元利均等返済額から借入可能額をシミュレーションすることができます。
PV関数の書式
PV関数の書式は次のとおりです。
（Office Support；PV関数より）
PV(利率, 期間, 定期支払額, [将来価値], [支払期日])
PV関数の引数
引数の説明は次のとおりです。（詳細はLINK Office Support；PV関数）
■ 利率
年利を12で割った数値を指定します。
私は「年利/12」と記述しています。
■ 期間
返済期間を月数で指定します。
35年ローンなら420ヶ月（35×12ヶ月）です。
■ 定期支払額
毎月の返済額を指定します。
月々の返済する金額（元金+利息）を負の数値（「-」をつけて）で記述します。（たとえば月々70,000円返済する予定でシミュレーションしたい場合は「-70000」と記述します。）
※ 計算式を工夫すればプラスの数値でも大丈夫です。私はプラスで指定しています。
■ [将来価値]
省略可能です。返済可能額をシミュレーションする場合は、最後の支払いをしたあとの価値を設定します。
返済可能額をシミュレーションする場合は「0」でいいと思います。（私は「0」でシミュレーションしています。）
■ [支払期日]
省略可能です。設定する場合は、「0」（末日返済）と「1」（月初返済）のどちらかを記述します。（私は「0」でシミュレーションしています。）
PV関数の使用例。借入可能額のシミュレーション
PV関数を利用して次のような表を作成しました。A、B、C、3種類の定期支払額からそれぞれいくら借入が可能なのかをシミュレーションする表になっています。
PV関数を利用した借入可能額のシミュレーションの解説
上のExcelのPV関数の表について簡単に解説します。
■ 利率（D3セル）
年利を入力します。
■ 期間（D4セル）
返済の回数（月数）を入力しますが、私はF4セルに返済の年を入力して「年数×12」をD4セルに表示するようにしました。
■ 定期支払額（D5、D6、D7セル）
借入可能額を比較できるように、毎月の返済額を3パターン（A、B、Cの3種類）入力するようにしました。
※ 私は正の数を入力しています。（PV関数を利用した式を記述するときに「*(-1)」を記述して解が正の数になるようにしています。）
■ 将来価値（D8セル）
省略しました。（省略した場合、将来価値は「0」を設定したことと同じです。）
■ 支払期日（D9セル）
省略可能ですが、「0」（月末返済）を入力しました。
■ PV関数の式（D11、D12、D13）
D11セルには次のような式を入力しました。ROUND関数を使って円未満の端数を四捨五入しています。
[aside type=”boader”]『＝ROUND(PV(D$3/12,D$4,D$5,,D$9)*(-1),0)』[/aside]
D12セル、D13セルには、それぞれ定期支払額をD6セル、D7セルに設定して上記と同じような式を記述しています。
※ 上記の式には「*(-1)」という記述を含めています。これは、PV関数の引数である「定期支払額」を負の数ではなく、正の数で指定した場合に解がマイナスで表示されてしまうことを防ぐためです。
PV関数の元利均等返済と元金均等返済の返済総額の比較
PV関数は元利均等返済の場合の借入限度額をシミュレーションすることができますが、その借入限度額をもとに返済予定表を作成して、元金均等返済による返済予定表と比較してみました。
両者を比較すると「（PV関数でシミュレーションをした）元利金等返済のほうが、元金均等返済と比較すると利息の支払額が多くなる。」ことを確認することができます。
PV関数を利用した返済予定表
次のようなPV関数を利用した返済予定表を作成しました。
PV関数を利用した返済予定表の解説
上のExcelのPV関数を利用した返済予定表について簡単に解説します。
■ 残高（G列）
「残高」列では、PV関数を利用して各返済回数の残高を算出しています。
たとえば1回目の残高セル（G18セル）では、PV関数を利用して「月々63,715円の元利均等払いであと419回の返済でいくら借りれるか？」を計算しています。
■ 利息（E列）
「利息」列では、「前回（前行）の残高×利率÷12」で計算しています。
たとえば、2回目の利息セル（E19セル）では、「19,965,288（E18セル）×1.750%÷12」と計算しています。（ROUND関数で四捨五入しています。）
「12」で割っているので月単位で計算しています。実際の返済予定表は「日数」をもとに作成されますので、この返済予定表とは異なります。
■ 元本返済額（D列）
「元本返済額」列では、「前回（前行）の残高－今回の残高」で計算しています。
■ 元本＋利息（F列）
「元本＋利息」列は、D列とE列を合計しています。
本来、定期支払額（毎月の返済額）を指定してPV関数を利用して借入可能額を計算しているので、「元本＋利息」列の数値は、その定期支払額に一致しなければなりませんが、端数処理等の関係で1円のずれが生じるため、あえて元本と利息を加算した結果を表示させるようにしました。
利息計算が月単位に基づく概算計算であることと、ズレが生じるかどうかの確認のためにあえてこのような計算にしました。
元金均等返済の返済予定表
PV関数でシミュレーションした借入可能額を元金均等で返済した場合の返済予定表を作成しました。
元金均等返済の返済予定表の解説
上の元金均等返済の返済予定表について簡単に解説します。
■ 元本返済額（C列）
「元本返済額」列では、PV関数でシミュレーションした借入可能額（19,999,837円）を返済回数で割った数値を記載しています。420回目については、419回目の残高（47,895円）を記載しています。
■ 利息（E列）
「利息」列では、「前回（前行）の残高×利率÷12」で計算しています。
たとえば、2回目の利息セル（E15セル）では、「19,952,219（E14セル）×1.750%÷12」と計算しています。（ROUND関数で四捨五入しています。）
「12」で割っているので月単位で計算しています。実際の返済予定表は「日数」をもとに作成されますので、この返済予定表とは異なります。
■ 元本＋利息（F列）
「元本＋利息」列は、D列とE列を合計しています。
まとめ
ExcelのPV関数は毎月の返済額から借入可能額をシミュレーションするのに便利なのでおすすめです。
■□◆◇ 編集後記 ◇◆□■
関東、近畿では木枯らし1号が吹いたようです。でも関東は心地よい晴れですね。
