Windows TIPS

［Office Master］

→ Windows TIPS TOPへ → Windows TIPS全リストへ → 内容別分類一覧へ PMT関数とゴールシーク機能によるローン・シミュレーション → 解説をスキップして操作方法を読む WINGSプロジェクト 本田 将輝 2004/02/28 　 対象ソフトウェア Excel 2000 Excel 2002 Excel 2003

■ Excelに用意されているPMT関数を利用することで、ローン支払における利率計算を容易に行うことができる。 ■ PMT関数とゴールシーク機能を併用することにより、簡単なローン・シミュレーションを実現してみる。

　

解説

　ローンは、マイホームやマイカーなどの高額な買い物をする際の手段として欠かせない便利な支払い方法であるが、このローンの計算はなかなか複雑なため、金融機関に計算を任せっきりにしてしまう方も少なくないのではないだろうか。だがローン計算は、購入時に限らず、ローン完済までは「ほかの金融機関に借り換え」をしたり、「繰り上げ返済」を行ったりと、何かと必要に迫られるであろうし、その過程では、自分自身でさまざまなローン・シミュレーションを行ってみたいというニーズも強いはずだ。

　本稿では、財務関数の1つであるPMT（paymentの略。投資に対する定期支払額の総計）関数とゴールシーク機能を利用することで、ローン支払いにおける支払額のシミュレーションを実現してみよう。ゴールシークは、計算結果を元に数値を逆算する機能だ。以下では、マイカー購入のために150万円の2年ローンを組む場合の月々の返済額をPMT関数で算出し、さらに、月々の返済額を切りの良い数字にした場合の借入総額を算出するためにゴールシーク機能を利用することにする。

操作方法

手順1―ローン試算表を作成する

　Excelを開いて、ローン試算表を作成してみよう。ここでは次のような表を作成する。

• サンプル・ファイルのダウンロード
  （注：サンプル・ファイルloan_simulation.xlsをダウンロードするには、上のリンクを右クリックして、loan_simulation.xlsというファイル名で保存してください。）

作成したローン試算表のテンプレート

作成するローン試算表のテンプレート。ローン計算に必要な情報を入力すると月々の返済額が自動計算される。

ローンとして借り入れる金額を入力。 　 ローン金利（年利）を入力。 　 ローン期間を月数で入力。2年の場合は「24」と入力。 　 返済日を入力。で入力したローン月数に併せて表を作成。。 　 PMT関数により自動計算。 　 月々の返済額（）から利息（）を減算した金額を自動計算。 　 前月残高おより金利から利息を自動計算。 　 前月残高から返済元金（）を減算した金額を自動計算。

　本稿では、の借入額は150万円、の金利（年利）は3％、そしてのローン月数は24カ月と指定することにする。

　実際にPMT関数を使用している、のセルD10に注目してみよう。当該セルには次のような計算式（関数式）を入力する。

=INT(PMT($G$4/12,$D$5,-D$4))

　PMT関数で月々の返済金額を算出した結果に対して、INT関数で小数点以下を切り捨てて整数にしている。PMT関数の書式は次のとおりである。

引数	概要
第1引数（利率）	金利を月単位の利率で指定する。元の参照値が年利の場合は12で割る
第2引数（期間）	返済回数を指定する
第3引数（現在価値）	借り入れる額を負数で指定する（負数にすることにより、結果が正の値になる）
第4引数（将来価値）	ローン期間終了後の金額を指定する。省略時は0と見なされる
第5引数（支払期日）	支払いの時期を期首とするか期末とするかで指定する。「期首」の場合は省略または「0」を指定し、「期末」の場合は「1」を指定する
PMT関数の引数
PMT関数は、Excelで利用できる財務関数の1つ。利率が一定であると仮定して、指定された期間だけ支払うとすると（各期間ごとの支払い額は定額とする）、毎期間ごとにいくらずつ支払わなければならないかを計算する。

　本稿では、第2引数には「$D$5」（24カ月）、第3引数には「-D$4」（150万円）を指定している。第2引数で「$」を使ってD列に固定しているのは、後の手順3で試算表をコピーした際にも変わらずD列を参照させるためだ。

　PMT関数の3つの引数を次のようにセットする。

PMT関数の引数

　セルE10〜G10に対しては、次のように指定する。

セル位置	内容	説明
E10	=D10-F10	今回返済額-前回残高の金利
F10	=INT(G9*$G$4/12)	前回残高の金利を計算
G10	=G9-E10	前回残高-今回の返済元金
ローン初回の設定

　セルD10〜G10へ入力した計算式を、セルD32〜G32（ローン最終回の1回前）までコピーする。セルD33〜G33（ローン最終回）については、ローン残高を0円とするための帳尻合わせが必要になるので、次のように指定する。

セル位置	内容	説明
D33	=E33+F33	前回残高+前回残高の金利
E33	=G32	前回残高
F33	=E33*$G$4/12	前回残高の金利を計算
G33	=G32-E33	残高(=0)
ローン最終回の設定

　ここまでの手順によって、月々の返済額は6万4471円（最終回のみ6万4482円）という結果が得られる。

手順3―ゴールシークでローン・シミュレーションを行う

　ローンのシミュレーションを行うにあたり、借入金額を変更してみたり、金利を変更してみたりといった操作であれば、手順1〜2を繰り返してみればよい。だが、例えば「月々の返済額を切り上げした場合、借入金額はいくら増額できるだろうか？」といったシミュレーションには対応できない。このような場合は、Excelのゴールシーク機能を活用するとよい。「ゴールシーク機能」とは、数式の計算結果が目的の値となるように、逆算処理を行うための機能である。通常の演算式では、入力パラメータに基づいて最終結果が計算されるが、ゴールシークでは逆に、指定された最終結果に近づくように、入力パラメータの値を変えながら自動的に試行が行われる。

　ここでは、月々の返済額6万4471円を切り上げて6万5000円とした場合、借り入れ可能な金額の総額がいくらになるかを試算してみる。

　まず、元々のPMT関数による計算結果を残しつつシミュレーションするために、試算表をコピーする。本稿では、同一シートの右横にコピーを作成する。ただし金利欄とローン月数欄は変更しないものとしてシミュレーションするので、コピー対象外とする。

コピーしたローン試算表

ゴールシーク機能を使ってシミュレーションを行うため、元の試算表を右側へコピーする。

　次に、返済額6万5000円を基点にゴールシークの逆算処理を行うため、返済額を6万5000円に設定するセルK10をクリックし、［ツール］メニューから［ゴールシーク］を選択する。すると、「ゴールシーク」ダイアログが表示される。

「ゴールシーク」ダイアログ（条件入力）

ゴールシークで目標とする値の入っているセルと、値を変化させるセルを指定する。

目的の数式が入っているセル。 　 のセルの値がこの値になるように、次ののセルの内容を変化させる。 　 このセルの値を変化させる。

　「数式入力セル」欄に返済額の数式が入力されている「$K$10」（または「K10」）を入力する。「目標値」欄には「65000」を入力する。そして、「変化させるセル」欄には「$K$4」（または「K4」）を入力する。入力したら「OK」ボタンをクリックすることでゴールシークの逆算処理が実行される。処理結果は次のようなダイアログで結果表示される。

「ゴールシーク」ダイアログ（結果表示）

セルK4の値を変化させたところ、K10の値が目標値ちょうどの65000になるケースが見つかった。

　上記の例の場合、「解答が見つかりました。」と表示され、「目標値」と「現在値」に同じ値が表示されている。この場合は、期待した月々6万5000円の支払額にマッチした借入額が見つかったことを示している。なお、ゴールシークの処理は、実際には「変化させるセル」の数値を少しずつ変化させながら「目標値」と一致するまで計算を繰り返す、という動作になる。そのため、条件によっては計算に時間を要したり、解答が見つからなかったりという場合もある。この場合は、毎月の支払額の値が「65000」になるように、「借入額」の値を変化させている。

　ゴールシークの結果表示ダイアログで「OK」ボタンをクリックすると、ゴールシークの逆算結果がセルに反映される。以下は、「OK」ボタンをクリックしてセルに結果が反映された状態だ。ゴールシークにより、金利3％で2年払い、そして月々の支払を6万5000円としたケースにおいては、ローンとして借り入れる金額は151万2302円になる、という結果がセルK4に得られている。つまり返済額を増やしているので、そのぶん借り入れ可能な総額（総支払額）が増えていることが分かる。

「ゴールシーク」の計算結果ダイアログで「OK」ボタンをクリックした結果

元の例と比べると、返済額が増えたぶんだけ、借入額（借り入れ可能な額。総支払額）が増えていることが分かる。

ゴールシークによって求められた結果の借入額。

　

「Windows TIPS」

＠ITメールマガジン　新着情報やスタッフのコラムがメールで届きます（無料）