MOS受験にむけて番外編5(What-If分析：ゴールシーク2）

ゴールシークの２回目です。
前回、説明不十分なところがあったと思います。

PMT関数（利率,期間,現在価値,[将来価値],[支払日]）で、定期支払金を求める関数で、通常の借入では現在価値が借入金額になります。（[将来価値],[支払日]は省略します。）

すなわち、この関数は、たとえば年利率が1.2％、返済期間30年の条件で2000万円を借りる場合の月あたりの返済金を求めるものです。
したがって、返済金を変えたときに、返済期間や借入金がどうなるかを求める関数ではないのですが、ゴールシークを使えば求めることができるというわけです。
このPMT関数はMOS試験のExpertに出ます。どうようにFV(Future Value、満期後の受取金額））関数もExpertの範囲になっています。
書式はFV(利率,期間,定期支払額,現在価値（－で入力）,支払期日）で現在価値は頭金（－で入力）で省略すると「0」とみなされます。支払期日は預期日で、期末の場合「0」、期首の場合「1」になります。



このような表において、毎月5万円の積み立てを10年続けたときの受取金額は
セル[B2]に「=FV(B3/12,B4*12,B5)」という式で求めると




のように求めることができます。

このFV関数は積立額、年利率、積立期間から受取金額を求めるもので、受取金額から積立額や積立期間を求める関数ではありません。

そこで、一例として受取金額を「1000万」として年利率、積立期間を変えずに、積立額を求める場合、ゴールシーク機能を使うことになります。

①[データ]タブ→[What-if分析]の[ゴールシーク]クリック
②開く[ゴールシーク]画面で[数式入力セル]に「B2」、[目標値]として「10000000」、[変化させるセルとして「B5]を入力後、[OK]クリック
ゴールシーク画面で解答が見つかりました。としてセル[B5]に「82,921」と表示されます。





受取金額を同じ1000万にして年利率、積立金額を変えずに、積立金を変えた場合にもゴールシーク機能により、約「16.5」年と求めることができます。





このようにPMT関数、FV関数などの財務関数でゴールシーク機能の威力が発揮できそうです。

・PV（利率,期間,定期支払額,[将来価値],[支払期日]）：present value
一定利率に基づいて、ローンまたは投資の現在価値を計算します。すなわち、一連の支払の合計金額を現時点での価値に換算する関数。PV 関数は、定　期的な定額支払 (住宅ローンや他のローンなど) または投資目標であ　る将来価値とともに使用できます。
　じつは前回のPMT関数と関連する関数で積立金額、年利率、支払期間から求めた現在価値はPMT関数の現在価値に相当します。
　したがって、前回の「返済期間「30」年、年利率「1.2％」で毎月の返済額を「80000」円とすると、借入金はいくらになるかという問題」はゴール　　シーク機能を使わなくてもPV関数で求めることができました。

・NPER（利率,定期支払額,現在価値,[将来価値],[支払期日]）：number of period
投資や返済に必要な期間を求める関数で、この関数もPMT関数に関連する関数で、NPER関数で求める期間はPMT関数やPV関数の期間に相当します。

そのほかRATE関数、PPMT関数、IPMT関数などの関数でも使えそうです。関数の説明は省略しますが、関数パレットで関数を表示させ、左下の[この関数のヘルプ]をクリックすると、詳しい説明があります。

ゴールシークは数値の大きさを少しずつ変えて反復計算を行うことによって目標の値に最も近づく時点の値を算出するので、明らかに間違った結果を出すことがあります。
下記の表のセル[B1]には、「=B2^2」という式が入力されています。（^はべき乗を表します）


　
目標値を「4」としてゴールシークど求めると



誤差がでてしまいます。
そこで、精度を上げるrために[ファイル]→[オプション]、[Excelのオプション]画面で[数式]タブの[計算方法の設定]の[反復計算を行う]にチェックをいれ[最大反復回数](初期設定100）を大きくするか、、[変化の最大値](初期設定0.01）を小さくすることによりじゃっかん精度が上がりますが、正解にはならないようです。下記の例では[最大反復回数]を「10000」に、[変化の最大値]を「0.0001」にしています。





いろいろなケースでゴールシークを実行した場合に結果を残したい場合には「シナリオ」として登録しておけば便利だとおもいます。

次回は「ソルバー」についてふれます。