To the right is the 'BSYS 2050 Sports' Bank Loan Information. As you can see they are paying $48,766.18 a month. Now to calculate how much they would pay if they wanted to pay off within 15 years we need to use the Goal Seek Function.
From MS Excel-click on Data,
-under Data Tools, click What-If Analysis
-select Goal Seek
This is what the dialog box should look like when you click on Goal Seek
-A Set Cell is the answer you are looking for, it's the GOAL. The Set cell needs a formula or function inorder for it to work.-For 'BSYS 2050 Sports', B21 is selected as this portion of the equation(periods) is what we would like to change.
-To Value is the actual value you are looking for.
-For 'BSYS 2050 Sports', we enter the value 180 as this is the desired number of periods.
-By Changing Cell is the portion of the formula that will need to be changed in order to attain our desired value.For 'BSYS 2050 Sports', we select the cell B15 as this is the input that will need to be changed in order to reach our desired number of periods.
After entering the values in the dialog box click OK.The function found a solution, click OK on the dialog box.
The Goal Seek function has given us the answer we wanted. From this table we see that they can pay off the bank loan in 15 years by paying $58,020.68 a month.