数式から取得する結果がわかっていて、その結果を取得するために必要な数式の入力値がわからない場合は、ゴール シーク機能を使用します。 たとえば、お金を借り入れる必要があるとします。 あなたは、あなたが望むどのくらいのお金、ローンを返済するために取りたいどのくらいの時間、そして毎月支払う余裕があるかを知っています。 目標シークを使用して、ローンの目標を達成するために確保する必要がある金利を決定できます。
数式から取得する結果がわかっていて、その結果を取得するために必要な数式の入力値がわからない場合は、ゴール シーク機能を使用します。 たとえば、お金を借り入れる必要があるとします。 あなたは、あなたが望むどのくらいのお金、ローンを返済するために取りたいどのくらいの時間、そして毎月支払う余裕があるかを知っています。 目標シークを使用して、ローンの目標を達成するために確保する必要がある金利を決定できます。
注: ゴール シークは、1 つの変数入力値のみを処理します。 ローン額と毎月の返済額の両方など、複数の入力値を受け入れる場合は、ソルバー アドインを使います。 詳細については、「 ソルバーを使用して問題を定義して解決する」を参照してください。
操作手順の例
上の例について手順ごとの操作を見てみましょう。
目標を達成するために必要なローン金利を計算するため、 PMT 関数を使用します。 PMT 関数は、毎月の返済額を計算します。 この例では、毎月の返済額が目標値となります。
ワークシートを作成する
-
空白のワークシートを新しく開きます。
-
まず、見やすいワークシートにするために、最初の列にいくつかのラベルを追加します。
-
セル A1 に「ローン額」と入力します。
-
セル A2 に「期間 (月数)」と入力します。
-
セル A3 に「金利」と入力します。
-
セル A4 に「返済」と入力します。
-
-
次に、わかっている値を入力します。
-
セル B1 に「10,000,000」と入力します。 これは、借入額です。
-
セル B2 に「180」と入力します。 これは、ローンの返済月数です。
注: 返済額は式の結果として取得されるため、返済額がわかっていても値として入力しません。 代わりに、数式をワークシートに追加し、後の手順 (ゴール シークを使うとき) で返済額を指定します。
-
-
次に、目標の数式を追加します。 たとえば、PMT 関数を使います。
-
セル B4 に 「=PMT(B3/12,B2,B1)」と入力します。 この数式では、支払金額が計算されます。 この例では、毎月 900 ドルを支払う必要があります。 この金額はここに入力しません。これは、目標シークを使用して金利を決定する必要があり、ゴール シークでは数式から始める必要があるためです。
数式は、前の手順で指定した値を含むセル B1 と B2 を参照します。 数式はセル B3 も参照します。ここで、目標シークで金利が設定されることを指定します。 この数式は、毎月の支払いを指定したため、B3 の値を 12 で除算し、PMT 関数は年利を想定しています。
セル B3 には値が含まれていないため、金利は 0% と見なされ、55,556 円という返済額が返されます。 この時点では、この値は無視して構いません。
-
ゴール シークを使用して金利を求める
-
[データ] タブの [データ ツール] グループで、[What-If 分析] をクリックし、[ゴール シーク] をクリックします。
-
[数式入力セル] ボックスに、解決する数式があるセルの参照を入力します。 この例では、この参照はセル B4 です。
-
[目標値] ボックスに、目的の数式の結果を入力します。 この例では -90,000 です。 これは返済を表すため、負の数値になっています。
-
[変化させるセル] ボックスに、調整する値が含まれているセルの参照を入力します。 この例では、この参照はセル B3 です。
注: ゴール シークによって変化させるセルは、[数式入力セル] ボックスで指定したセルの数式によって参照される必要があります。
-
[OK] をクリックします。
次の図に示すように、ゴール シークが実行され、結果が生成されます。セル B1、B2、B3 は、ローン金額、期間長、および金利の値です。
セル B4 には、数式 =PMT(B3/12,B2,B1) の結果が表示されます。
-
最後に、結果がパーセントで表示されるようにターゲット セル (B3) を書式設定します。
-
[ホーム] タブの [数値] グループで [パーセンテージ] をクリックします。
-
[小数点以下の表示桁数を増やす] または [小数点以下の表示桁数を減らす] をクリックし、小数点以下の桁数を設定します。
-
数式から取得する結果がわかっていて、その結果を取得するために必要な数式の入力値がわからない場合は、ゴール シーク機能を使用します。 たとえば、お金を借り入れる必要があるとします。 あなたは、あなたが望むどのくらいのお金、ローンを返済するために取りたいどのくらいの時間、そして毎月支払う余裕があるかを知っています。 目標シークを使用して、ローンの目標を達成するために確保する必要がある金利を決定できます。
注: ゴール シークは、1 つの変数入力値のみを処理します。 ローンの金額と月払い金額の両方など、複数の入力値を受け入れる場合は、ソルバー アドインを使用します。 詳細については、「 ソルバーを使用して問題を定義して解決する」を参照してください。
操作手順の例
上の例について手順ごとの操作を見てみましょう。
目標を達成するために必要なローン金利を計算するため、 PMT 関数を使用します。 PMT 関数は、毎月の返済額を計算します。 この例では、毎月の返済額が目標値となります。
ワークシートを作成する
-
空白のワークシートを新しく開きます。
-
まず、見やすいワークシートにするために、最初の列にいくつかのラベルを追加します。
-
セル A1 に「ローン額」と入力します。
-
セル A2 に「期間 (月数)」と入力します。
-
セル A3 に「金利」と入力します。
-
セル A4 に「返済」と入力します。
-
-
次に、わかっている値を入力します。
-
セル B1 に「10,000,000」と入力します。 これは、借入額です。
-
セル B2 に「180」と入力します。 これは、ローンの返済月数です。
注: 返済額は式の結果として取得されるため、返済額がわかっていても値として入力しません。 代わりに、数式をワークシートに追加し、後の手順 (ゴール シークを使うとき) で返済額を指定します。
-
-
次に、目標の数式を追加します。 たとえば、PMT 関数を使います。
-
セル B4 に 「=PMT(B3/12,B2,B1)」と入力します。 この数式では、支払金額が計算されます。 この例では、毎月 900 ドルを支払う必要があります。 この金額はここに入力しません。これは、目標シークを使用して金利を決定する必要があり、ゴール シークでは数式から始める必要があるためです。
数式は、前の手順で指定した値を含むセル B1 と B2 を参照します。 数式はセル B3 も参照します。ここで、目標シークで金利が設定されることを指定します。 この数式は、毎月の支払いを指定したため、B3 の値を 12 で除算し、PMT 関数は年利を想定しています。
セル B3 には値が含まれていないため、金利は 0% と見なされ、55,556 円という返済額が返されます。 この時点では、この値は無視して構いません。
-
ゴール シークを使用して金利を求める
-
[ データ ] タブで、[ What-If Analysis]\(What-If 分析\) をクリックし、[ ゴール シーク] をクリックします。
-
[数式入力セル] ボックスに、解決する数式があるセルの参照を入力します。 この例では、この参照はセル B4 です。
-
[目標値] ボックスに、目的の数式の結果を入力します。 この例では -90,000 です。 これは返済を表すため、負の数値になっています。
-
[変化させるセル] ボックスに、調整する値が含まれているセルの参照を入力します。 この例では、この参照はセル B3 です。
注: ゴール シークによって変化させるセルは、[数式入力セル] ボックスで指定したセルの数式によって参照される必要があります。
-
[OK] をクリックします。
次の図に示すように、ゴール シークが実行され、結果が生成されます。 -
最後に、結果がパーセントで表示されるようにターゲット セル (B3) を書式設定します。 [ ホーム ] タブで、[ 10 進数の を増やす ] または [ 10 進数を小さく する]をクリックします