使用目標搜尋藉由調整輸入值來找出想要的結果
如果我們建立一個運算模型,並且利用公式計算出結果,可是結果並不一定是我們理想中的值,若希望結果值達到我們理想中的值,並想知道與結果有關聯性的某一公式所在儲存格中值會變成怎樣,這時可以藉由「目標搜尋」功能來進行,即俗稱的「反推運算」。
例如,假設我們需要向銀行借一筆錢購屋。我們知道自己想要借多少錢、需要多久的時間才能還清貸款,以及每個月可以支付的金額。此時可以使用「目標搜尋」來運算出符合貸款目標的利率。
附註:目標搜尋只可以使用運算一個變數。如果想要接受一個以上的輸入值,例如,貸款金額及每月支付金額,則可以使用「規劃求解」增益集。
由於想要計算符合我們期望的每月付款金額,故使用了PMT函數。PMT函數會計算每月支付金額。在此例中,每月支付金額(期付款)就是我們所尋找的目標。
建造運算模型
01 開啟一個新的空白工作表。
02 首先,在第一欄中加入一些標籤,使工作表的內容易於閱讀。
在儲存格A2中,輸入「貸款金額」。
在儲存格A3中,輸入「貸款年數」。
在儲存格A4中,輸入「貸款期數」。
在儲存格A5中,輸入「年利率」。
在儲存格A6中,輸入「月利率」。
在儲存格A7中,輸入「期付款」。
03 接下來,請加入您知道的數值。
在儲存格B2中,輸入「3000000」。這是我們向銀行借款金額。
在儲存格B3中,輸入「30」。這是預計要還款的年數。
在儲存格B4中,輸入「=B2*12」公式。這是預計要還款的期數。
在儲存格B5中,輸入「2.8%」。這是預計銀行要給的年利率。
在儲存格B6中,輸入「=B5*12」公式。這是預計要還款的年數。
在儲存格B7中,輸入目標值的公式「=PMT(B6,B4,B2)」,此公式會計算出每月付款金額,在此例中,每個月需要支付12326.83元。
因為想要使用「目標搜尋」來決定貨款年數,而「目標搜尋」需要以公式啟動。此公式參照儲存格B2、B4與B6,其中包含我們在先前步驟中指定的數值。
使用目標搜尋來決定利率
我們想要依照目前運算的結果,求出若我們每月付款9000元,付時才可付清貨款?
01 點取「資料Æ資料工具Æ模擬分析Æ目標搜尋」,開啟「目標搜尋」對話方塊。
02 點取「目標儲存格」欄位,輸入含有我們想要求解其公式的儲存格參照。就本範例而言,此儲存格參照為B7。
03 點取「目標值」欄位,輸入我們想要的公式結果。此例中為-9000。請注意,此數字為負數是因為代表付款。
04 點取「變數儲存格」欄位,輸入含有我們想要調整之數值的儲存格參照。就本範例而言,此儲存格參照為B3。
附註:在「目標儲存格」欄位中所指定之儲存格內的公式必須能夠參照到「目標搜尋」的「變數儲存格」欄位。
05 點取「確定」鈕,如果可以求得解答會出現如下圖對話方塊。
06 點取「確定」鈕,產生如下圖所示的結果。
哇!一個月少付3326元(約佔1/3),卻要多奮鬥23.78年(1.8倍)左右,真是恐佈。
留言列表