PMT関数 ローン計算 元利均等払いでローンの支払額の計算が出来ます。
PPMT関数を使用すると元利均等払いの元金分の支払いがどれくらいかを計算することが可能です。また、IPMT関数で利息分の支払額がわかります。
PPMT関数もPMT関数同様、月払いの計算時には年利を1か月分に、支払回数を年から月に変換しますので以下の図のような表を作成します。百万円を年利5%で1年の分割払いで考えてみます。（図はexcel2013）
セルE3には年利を12で割った結果です。=D3/12という数式が入力済み。セルE4は総支払回数になるよう年数×12の=D4*12という数式を入力しました。また、セルD5にはE列の数値を使って毎月の返済額が計算済みです。=PMT(E3,E4,E2)*-1
では、セルB8に支払第1回の元金分はいくらかPPMT関数で計算します。
PPMT関数
一定の利率で定期的に支払するローンの1回の元金分を計算する
PPMT(利率, 期, 期間, 現在価値, [将来価値], [支払期日])
利率が、3行目の利率です。期は何回目の支払かです。期間は返済期間、現在価値が元金に相当します。元利均等払いは後になるほど支払額に含まれる元金の割合が増えてきます。よってPPMT関数は期の値が何回目の支払かを指定して、都度支払元金を求めます。数式タブの関数ライブラリの財務をクリックし、PPMTをクリックします。
PPM関数の関数の引数のダイアログボックスが表示されますので適切なセル番地を入力します。利率も期間もE列側の12か月換算の方を使います。期はセルA8です。また、後ほど支払回ごとの計算をしますので期以外は絶対参照にします。
PPMT関数も結果がマイナスで返ってきますので-1をかけて正の数にしておきます。
A列の回数を全期間分の12回に増やし、元金の計算式セルB8 もコピーします。
これで毎月の支払額の中の元金額が作成できました。次に利息分を計算します。セルD5の毎月支払額から元金を引いてもいいですが、利息分がわかる関数がありますので使用して計算してみます。
IPMT関数
一定の利率で定期的に支払するローンの1回の利息分を計算する
IPMT(利率, 期, 期間, 現在価値, [将来価値], [支払期日])
セルC8にIPMT関数をつかって利息を計算しますので先ほどと同じように数式タブの関数ライブラリの財務をクリックし、IPMTをクリックします。
IPMT関数の関数の引数のダイアログボックスにセル番地を入力します。参照するセルは先ほどのPPMT関数と全く同じになります。
また、負の値が返ってきますので-1をかけておきます。
同様にコピーしてD列に元金と利息を足した( =B8+C8) 返済額を出しておきます。
そのほか少し手を加えて積み上げ縦棒のグラフなんかも追加してみました。支払期間に合わせて表が伸び縮みする工夫なんかがあるといいかもしれませんね。
今回ローン残高は元金から支払済み元金を引いて計算していますがIMPT関数から算出することも可能です。
http://support.microsoft.com/kb/214091/ja
PMT関数の記事もそうですが、小数点以下の端数や借入日から初回返済日までの期間、月単位ではなく日割りで計算する、などいろいろな要素で実際の数値とは異なることがりますのであくまでシミレーションとしてご利用ください。