07/ 03
ゴールシークで逆計算シミュレーション(Excel)
さまざまな条件を指定して結果を計算する。Excelの関数を使えば何でも計算できます。でも、『結果(ゴール)を元に前提条件を逆計算させる』なんてこともできるんですよ。そんな逆計算シミュレーションをするのがゴールシークです。
Contents
Excelは計算が得意
コンピューターは中国では電脳なんて言われていますが、日本では最初は電子計算機と呼ばれていました。もともとコンピューターの開発目的が複雑かつ大量の計算をさせることでしたから、電子計算機でも良かったのですが、現在ではデータベースですとか画像の処理ですとか通信ですとか、本来の計算以外の使われ方も多くなってきたので、電子計算機という言われ方はあまりふさわしくなくなってきたようですね。
しかしもともと電子計算機ですから（今でもですけど）、計算は得意中の得意です。そしてそのコンピュータを計算機として使用するのが表計算ソフトのエクセルですから、エクセルも計算はもっとも得意とするところですよね。
そんなエクセルにややこしいシミュレーションの計算をやらせてみましょう。
ローンの計算をしてみる
Excelでローンの計算をする
金利と返済回数、借入額から月々の返済額を計算するには・・・
本来なら難しい数式を計算するのでしょうが、エクセルなら関数を使って一発で答えがでます。
ローンを計算するPMT関数の説明
PMTという関数で、最初の引数（関数に渡す値）には金利を入れます。ここでは年5%の設定として、0.05÷12が入ります。関数にはセルの番地（B6÷12）で指定してあります。
二つめの引数には返済回数。同様にセルの番地（C6）で指定してあります。
3つめの引数の「現在価値」には借入金の金額が入ります。同様にセルの番地（Ｄ6）で指定してあります。
4つめの引数の「将来価値」はローンの残高です。全額返済ですからもちろん 0 ですよね。何も指定しなければ 0 を入力したのと同じになります。
5つめの引数の「支払期日」は 0 もしくは省略すれば、各期の期末に、1 を入力すれば、各期の期首に支払いが行われることになります。
とにかく、こんな感じで簡単にローンの支払額が計算できます。
ゴールシークで逆計算のシミュレーション
では今度は支払い額を決めて借入額を計算してみましょう。もちろん計算式を入れて計算してもいいのですが、せっかく作ったワークシートですから、そのまま利用することにします。
エクセルのゴールシークという機能を使うと、答えから設定数値を逆算することができるのです。
ゴールシーク機能を呼び出す
早速やってみましょう。『ゴールシーク』はリボンの『データ』タブの『データツール』グループにある、『What-If分析』をクリックして、展開するメニューから『ゴールシーク』をクリックして呼び出します。
Excel2003以前では、上のメニューから『ツール（Ｔ）』をクリックし、開いたドロップダウンメニューから『ゴールシーク（Ｇ）』を選択します。
ゴールシーク設定のダイアログ
『ゴールシーク』のダイアログ(小さな設定用ウインドウ)が開くので、それぞれの値を設定します。
『数式入力セル（E)』には計算式の入っている F6 を指定します。今回は返済額を35000円の場合の借入限度額を計算してみることにするので、『目標値（V)』には返済額 -35000 を入力します。そして『変化させるセル（C)』には、借り入れが入力されている D6 を指定します(6)。
ゴールシークによって結果から条件が逆計算された。
『OK』ボタンをクリックすると、コロコロと計算をして収束値を求めてくれます。今回はどうやら 1,167,800円 だったようです。
変化させるセルをC6にすれば返済回数が何回になるか計算してくれます。このように、返済回数や金利を変えながらいろいろとシミュレーションをすることができますよね。
どうですか？すでに計算式が用意されたワークシートを使っている場合、シミュレーション用のワークシートを用意しなくても、ゴールシークを使ってさまざまなシミュレーションをしてみることができます。そんんなに使う機会は多くないかもしれませんが、とても手軽な方法ですから、頭の隅にでも入れておいてください。
