ローン支払い明細書

PMT関数 ローン計算 元利均等払い元利均等払いの内訳 元金と利息で元利均等払いの計算方法をご紹介しました。

今回は月払いの明細が出力されるエクセルシート作成してみました。使い道はあまりないかもしれません。

loandetails

あくまで、下調べ程度でお使いいただきたいのですが、もしかしたら、不動産屋さんや自動車屋さんなどで重宝するのかもしれません。(自前でいいのをお持ちですよね。)

元利均等払いの内訳 元金と利息ではローン残高は元金から支払済み元金を引いて計算していますしたが、今回はIMPT関数から算出しています。計算結果は同じなのですが、最終回の残高0となるところが、まれに小数点以下でマイナスになる場合があったので、IMPT関数で残高を算出しました。

IPMT(利率, 次の期, 期間, 現在価値)/利率

計算に当たっては月賦に換算しますので利率(年利)を12で割り、期間(年)を月に直すのに12をかけたりします。残高は次の期(次の支払)の利息分から逆算するので期に1を足して計算しています。

使用に当たっては入力は年利と支払年数のほうがわかりよいと思いますので月あたりに変換したセルを文字色白で見えないようにしています。また、間違って消さないように保護をかけています。(数式には保護をかけていません 保護にパスワードは使用していません)

期間は1年から40年まで対応しています。住宅ローンでも40年あれば対応できると思います。

40年だと480か月分の明細行が出力されますので計算式もそれだけ入っています。1年でも数式で空白など出力していますので480か月分の行が印刷されてしまいます。そこでフィルタで元金が0以上の行のみ表示するようにしていますが、フィルタは自動再計算が行われても更新してくれません。

仕方がないのでVBA(マクロですね。)でフィルタを作動させています。

ファイルの拡張子がxlsmになっています。また実行時にはマクロを有効にしますか?などの警告が表示されます。

どうでもいいことですが、エクセルのセルの枠と行番号、列番号は非表示にしています。

どうか自己責任でご使用ください。計算間違いなどの精査はしていますが本ファイルご使用による損害等には責任は負いかねますのでよろしくお願いします。

ローン支払明細書のダウンロード

コメントを残す

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