税理士法人エスペランサ岡崎　ブログ

元利均等返済のExcel返済予定表の作成方法【PCのExcel操作の紹介】

こんにちは。皆さんはローンや借入金の返済予定表の数字を見たことはありますでしょうか？

私は業務上、よく見かけます。そして返済予定表を自分で作成することもあります。

その中で、返済方法について元利均等返済と元金均等返済の主に２種類の返済方法があることを最近知りました。

ちなみに私は、パソコンのExcelの知識は、高校の情報処理で勉強したのと、職業訓練で勉強した程度の知識です。

そのため、元利均等返済の返済予定表を作成した際に少し苦労をしました。

なので今回は私から元利均等返済に使用した関数式を２つ紹介させていただきます。

①　＝PPMT(利率,期,期間,現在価値,［将来価値］,［支払期日］)

②　=PMT(利率,期間,現在価値,［将来価値］,［支払期日］)

それぞれ説明いたします。

①の関数式からでる数字は、毎年の決まった返済額です。こちらの金額が借りた人が実際に払う金額になります。

②の関数式からでる数字は、①とは違い、利息を抜いた元金の返済金額になります。

 

返済予定表の作成方法

必要な情報

・借入金額　・返済期間（年）　・利率

（例として　借入金額→100万円、返済期間20年、利率0.75％とします。）

①返済額を算出します。

Enterを押すと、、、-54,031　となります。

②元金の返済金額と対する利息の分かる表を作成します。

※完成した表にそって手順説明

まずは表を作ります。グレーの箇所はそれぞれ関数式が入ります。

↓

1の関数式を入れます。（キーボードのF4を押せば、＄マークを簡単に付けることができます。これをすればセルの固定ができます。）

↓

2の関数式を入れます。

こちらは簡単です。返済額合計から、先ほどの元金返済額を差し引くだけです。

↓

3の関数式を入れます。

こちらも簡単な引き算になります。

一つ上のセルから、元金返済額を差し引く式を入力しましょう。

（返済額合計は利息が含まれているため、差し引くことのないように気を付けましょう。）

↓

それぞれ下方向にセルのコピーをしたら完成。

（最終の返済後残高が”０”になっていれば正しく返済予定表ができている証拠です。）

 

～従業員の所感として～

私が基本的によく見る返済予定表は、元金が一定の額のものでした。

そして利息の計算式は。返済後の残高に利率を掛けるシンプルな形のだったため、【＝PMT、＝PPMT】という関数式を使ったことがありませんでした。

実際に電卓をたたいて数字を出すのも難しいため、とても便利な関数式だと感動しました。

ただ、この元利均等の返済予定表を作る以外で使用する場面があるかは分かりません( ;∀;)