エクセルでローン計算 ボーナス併用時の毎月の返済額を求める

2018/02/07


住宅ローンやマイカーローンを組む前には、ローンの返済シミュレーションをしておくことをおすすめします。

そこで、今回の h1r0-style.net では、Excelを使った ローン計算 を解説します。

借入金額や 返済期間、金利やボーナス払い併用などの条件による毎月の返済額の変化を確認して、自分に合った返済計画を立てましょう。

ローン返済の計画を立てる

エクセルで返済シミュレーション

今回の ローン計算 のサンプルケースは、借入金額 100万円、年利2.3%、期間3年のマイカーローンという設定で、返済を 月払いとボーナス払いで併用 した場合、として支払額を求めます。




ローン計算をエクセルに任せる

まず、Excel上で 下のような表を作成します。


クルマの購入にかかった費用は全部で 250万円(セルB2)、頭金を 150万円入れたので(セルB3)借入金は差額 100万円(セルB8)というサンプルで、ボーナス払い併用 です。
また、返済期間は3年、年利はありがちな 2.3%という設定です。

借入金100万円のうち、ボーナス払いに充てたい金額(サンプルは30万円)をセルB9に入力して、毎月の返済額(セルB11)とボーナス時の返済額(セルB12)を算出する仕組みを作ります。

そして、この「毎月の返済額とボーナス時の返済額」の算出に使用する関数が PMT関数 です。

PMT関数とは

PMT関数とは、『一定利率の支払いが定期的に行われる場合の、ローン定期支払額を算出する』関数で;

=PMT (利率, 期間, 現在価値, [将来価値], [支払期日] )

のように表されます。

Point「利率」には月払いの場合、月利(年利÷12)の数字を入れるのがポイントです。同様に「期間」も月払いの場合は月数に換算した数字を入れます。
「現在価値」はローン計算の場合、借入額が相当します。

 

上記ポイントに注意して実際に数式をセルに入れてみましょう。

PMT関数を使用したローン計算

毎月の返済額を算出する

毎月の返済額を求めます。
セルB11に入る数式は;

=PMT ( B6/12, B5*12, B8-B9 )

となります。

▲利率は12で割り、期間は12倍する、さらに借入額からボーナス返済分を差し引きます

すると、セルB11に毎月の支払額が表示されます。
※支払額は マイナス表記されます

マイナス表記がお気に召さない方は、上記数式を ABS関数 で囲むと「絶対値」での表記が可能です。

ABS関数で PMT関数の数式を囲むと。。。

▲金額が プラス(絶対値)で表示された


ボーナス時の返済額を算出する

つぎに、ボーナス時の返済額を求めます。
セルB12に入る数式は、B11のそれとは少し違います;

=PMT ( B6/2, B5*2, B9 )

ボーナス時の返済については、利率は2で割り、期間は2倍にします。ボーナスが半年に1回だからです。
※ボーナスが年1回という方は、セルB6の年利、セルB5の期間の数字がそのまま入ります。

▲マイナス表記ですが支払額が表示された

さらに、毎月の支払額と同様に ABS関数で処理すると;

▲プラス表記された

以上で「エクセルでローン計算する方法、ボーナス併用時の毎月の返済額を求める」は終了です。
ローンのご利用は計画的に。

関連記事



併せて読んでおくとさらに便利

株価チャートの作り方|Excelで株価のグラフを作る
Excelグラフ
株価チャートの作り方と見方を図解します。株価のグラフがうまく作成できないという方は「表の並び」が良くないかもしれません。ローソク足の見方にも触れていますので是非ご覧ください。

FXの基礎知識|FXのことが気になるけどよくわからないという方へ
FX・投資
FXのことが気になるけどよくわからないという方へFXの基礎知識をまとめました。外貨預金とFXとの違いや、BID・ASK、スプレッド、レバレッジなど初心者がFXに挑戦しやすくなるトピックです。

DMM FXは初心者向けFX・キャッシュバック
FX口座数『国内第1位』※ のDMM FXをご案内。初心者にも好評の取引ツールと充実のカスタマーサポートで初心者も安心...※2017年1月末時点。ファイナンス・マグネイト社調べ(2017年1月口座数調査報告書)

※その他関連記事は、このページの下の方にもあります。

-マネー, EXCEL&VBA
-,

シェアしていただけると助かります。