In our case, the cell at A2 should now read about 4.31%. So, use cell A2.Д¬lick the OK button and you may notice that Excel flashes a bunch of numbers in the respective cells until the iterations finally converge on a final number. The By Changing Cell variable is the interest rate you want Excel to find for you so that the $100,000 mortgage will cost you only $6,000 per year. Remember that Excel sees payments as a negative cash flow. The To Value variable is the amount you want the cell at D2 to be at the end of the analysis.ДЇor us, it is -6,000.

The Set Cell variable must be a cell that contains a formula. Click on the What-If Analysis button and choose Goal Seek from the menu.Д®xcel opens up a small window and asks you to input only three variables. Essentially, you will make Excel work backwards from the result in D4 until it arrives at an interest rate that satisfies your maximum payout of $6,000.Д«egin by clicking on the Data tab on the Ribbon and locating the What-If Analysis button in the Data Tools section. However, you can make Excel do the work for you by using the What-If Analysis Goal Seek tool. Many people in this situation would simply start typing numbers in cell A2 until the figure in D2 reached approximately $6,000.

You are now wondering what is the highest interest rate you can take on for the loan to make sure you donвЂ™t end up paying more than $6,000 per year. Suppose you do some figuring and find out that you can afford to pay back $6,000 per year in mortgage payments. Unfortunately, no mortgage lender is going to lend you $100,000 at 0% interest. Notice that the figure in D2 is negative since Excel assumes that the payments are a negative cash flow from your financial position. And represents the yearly payments of a 30-year, $100,000 mortgage at 0% interest.

Use goal seek in excel for mac