今回はExcelで超便利な、PMT関数(パーミュテーション)・PPMT関数(プリンシパル・ペイメント)をご紹介します。
どちらも財務関数になります。

PMT関数は、ローンを申し込んだ際の返済額や積立貯蓄を申し込んだ時の毎月の払込額を求めるときに使います。
PPMT関数は、利率が一定であるとした時、定額の支払いを定期的に行う場合の任意の期間に支払う元金を求めます。
PPMT関数を使用して、元利均等返済の元金計算を行うこともできますよ。

PMT関数

元利均等払いのローンの返済や複利の積立貯蓄の払い込みで、
1月当たりの返済額がいくらになるかを求められるんです。
通常、結果はまだ支払っていないのでマイナスで示されます。
書式は、=PMT(利率, 期間, 現在価値, 将来価値, 支払期日)で求めます。
下記の表が引数の内訳になります。

例1:年利5%の利率で5年ローンで100万円借りるとします。
毎月の支払額はいくらになるでしょうか?

下記の表を作成してください。

では、「D2」に先ほどの書式に当てはめていきます。=PMT(利率, 期間, 現在価値, 将来価値, 支払期日)でしたね!
=PPT( 利率は「A3」の5%ですが利率なので12ヶ月で割ってあげます。=PPT(A3/12, になりますね!
次に期間です。期間は「B3」の5年ですが月に直す必要がありますので、=PPT(A3/12,B3*12, になりますね!
更に、現在価値は「C6」の100万になりますので、=PPT(A3/12,B3*12,C3, です。
次は将来価値は無いので「0」ですね!従って数式は=PPT(A3/12,B3*12,C3,0)となります。
「D2」関数を挿入してみてください!結果:「-18,871」と結果が出ます。毎月18,871円の支払いになります。
銀行屋さんですね!!

使用するポイント
[利率]と[期間]の単位は同じにしてください。たとえば、毎月の返済額を求めるのであれば、
[利率]は月利(年利÷12)で指定し、[期間]も月数(年数×12)で指定します。
PMT関数では、手元に入る金額は「+」で示され、手元から出ていく金額(返済額や払込額)は「-」で示されます。
PMT関数の結果には表示形式として通貨スタイルが自動的に適用されるんです。
通貨スタイルでは、小数点以下が表示されていませんが、結果は小数点以下も求められています。
(表示されている値は小数点以下が四捨五入されたものが表示されます)

PPMT関数

ExcelのPPMT関数は、利率が一定であると仮定して、定額の支払いを定期的に行った場合の任意の期間に支払う元金を求めてくれます。
PPMT関数を使用して、元利均等返済の元金計算を行うことができるんですね!
できることは元金の支払額を求めることが出来ます。
書式は、=PPMT(利率, 期, 期間,現在価値, 将来価値, 支払期日) で求めます。

例1:1,000万円を年利2.5%の15年ローン、元利均等返済で借りた場合の1回目の元金返済額を求めます。
月末に返済する事になします。

下記の表を作成してください。

「B6」に=PPMT(B1/12,B2,B3*12,B4,0) で求めます。関数を挿入してみてください。
年利のため12で割って月の利率を求め、借入期間を12倍して月回数にしています。
支払いの場合の計算結果はマイナスで表示されます。

結果:1回目の支払額は「45,846」になりましたね!

例2:例1と同一条件のローンで、5年経過後の61回目の元金支払額を求めます。
下記の様に表の「B2」の値を「61」に変更をして頂くだけで61回目の支払額が出ますね。

結果:「51,943」と結果が出ました。5年後の61回目は51,943円の支払いが発生するんです!

PMT関数・PPMT関数のまとめ

今回のPMT・PPMT・IPMT関数ですが財務関数とありますので、ローンなどのシミュレーションに使える関数になっているんですね!
ローン(積立)の計算に使います。それぞれの関数は以下のような関数です。
PMT関数 :月々の支払(積立)金額合計を出す
PPMT関数:月々の支払金額の元金分
でしたね!PMT関数は月々の支払額を算定することが出来ますし、PPMT関数は月々の元金分の金額を算定出来ます。
銀行などでローンを借りる時に算定して出してくれますよね、そんな時に利用されている関数です。
自分でも計算できるので、借りる前にExcelで作成してみるのも様のではないでしょうか?
是非、利用してみて下さい。
これからもソフトキャンパスのブログ、Youtubeで色々な関数の使い方をご紹介しいて参ります。
是非参考にしてください!応援よろしくお願いいたします。

コメントを残す

メールアドレスが公開されることはありません。