Hoewel de lange lijst met functies van Excel een van de meest aanlokkelijke kenmerken van Microsoft's spreadsheetprogramma is, zijn er een paar onderbenutte juweeltjes die deze functies verbeteren. Een tool die vaak over het hoofd wordt gezien, is de What-If-analyse.

Excel's What-If Analysis- tool is onderverdeeld in drie hoofdcomponenten. Het deel dat hier wordt besproken, is de krachtige functie Doel zoeken^{(Goal Seek)} waarmee u achteruit kunt werken vanuit een functie en de invoer kunt bepalen die nodig is om de gewenste uitvoer van een formule in een cel te krijgen. Lees verder om te leren hoe u Excel's What-If Analysis Goal Seek- tool gebruikt.

Voorbeeld van Excel's Goal Seek Tool

Stel dat u een hypothecaire lening wilt afsluiten om een ​​huis te kopen en u zich zorgen maakt over de invloed van de rente op de lening op de jaarlijkse betalingen. Het bedrag van de hypotheek is $ 100.000 en u betaalt de lening terug in de loop van 30 jaar.

Met de PMT^(PMT) -functie van Excel kunt u eenvoudig achterhalen wat de jaarlijkse betalingen zouden zijn als de rente 0% zou zijn. De spreadsheet ziet er waarschijnlijk ongeveer zo uit:

De cel bij A2 vertegenwoordigt de jaarlijkse rente, de cel bij B2 is de lengte van de lening in jaren en de cel bij C2 is het bedrag van de hypothecaire lening. De formule in D2 is:

=PMT(A2,B2,C2)

en vertegenwoordigt de jaarlijkse betalingen van een 30-jarige hypotheek van $ 100.000 tegen 0% rente. Merk^(Notice) op dat het cijfer in D2 negatief is, aangezien Excel ervan uitgaat dat de betalingen een negatieve cashflow zijn uit uw financiële positie.

Helaas zal geen enkele hypotheekverstrekker u $ 100.000 lenen tegen 0% rente. Stel dat^(Suppose) u wat uitzoekt en ontdekt dat u het zich kunt veroorloven om $ 6.000 per jaar aan hypotheeklasten terug te betalen. U vraagt ​​zich nu af wat de hoogste rente is die u kunt nemen voor de lening om ervoor te zorgen dat u niet meer dan $ 6.000 per jaar betaalt.

Veel mensen in deze situatie zouden gewoon getallen in cel A2 typen totdat het cijfer in D2 ongeveer $ 6.000 bereikte. U kunt Excel echter het werk voor u laten doen met behulp van de What-If Analysis Goal Seek - tool. In wezen zorgt u ervoor dat Excel achteruit werkt vanaf het resultaat in D4 totdat het een rentepercentage bereikt dat voldoet aan uw maximale uitbetaling van $ 6.000.

Begin door op het tabblad Gegevens op het ^(Data)lint^(Ribbon) te klikken en de knop Wat-als-analyse^{(What-If Analysis)} te zoeken in het gedeelte Gegevenshulpmiddelen^{(Data Tools)} . Klik op de What-If Analysis- knop en kies Goal Seek in het menu.

Excel opent een klein venster en vraagt ​​u om slechts drie variabelen in te voeren. De variabele Set Cell moet een cel zijn die een formule bevat. In ons voorbeeld hier is dat D2 . De variabele Waarde^{(To Value)} is het bedrag dat u wilt dat de cel bij D2 aan het einde van de analyse is.

Voor ons is dat -6.000 . Onthoud dat Excel betalingen als een negatieve cashflow ziet. De variabele Door van cel te^{(By Changing Cell)} veranderen is het rentepercentage dat Excel voor u zoekt, zodat de hypotheek van $ 100.000 u slechts $ 6.000 per jaar kost. Gebruik dus cel A2 .

Klik op de knop OK en u zult merken dat Excel een aantal getallen in de respectieve cellen flitst totdat de iteraties uiteindelijk samenkomen op een definitief getal. In ons geval zou de cel op A2 nu ongeveer 4,31% moeten zijn.

Deze analyse leert ons dat om niet meer dan $ 6.000 per jaar uit te geven aan een 30-jarige hypotheek van $ 100.000, je de lening zeker moet stellen tegen niet meer dan 4,31%. Als je wat-als-analyses wilt blijven doen, kun je verschillende combinaties van getallen en variabelen proberen om de opties te verkennen die je hebt bij het verkrijgen van een goede rente op een hypotheek.

Excel's What-If Analysis Goal Seek- tool is een krachtige aanvulling op de verschillende functies en formules die in de typische spreadsheet te vinden zijn. Door achteruit te werken vanuit de resultaten van een formule in een cel, kunt u de verschillende variabelen in uw berekeningen duidelijker verkennen.

Using Excel’s What-If Analysis Goal Seek Tool

Αlthough Excel’s long list of functions is one of the mоst enticing featureѕ of Microsoft’s spreadsheet application, therе a few underutilized gems that enhancе these functions. One often-overlоoked tool is thе What-If Analysis.

Excel’s What-If Analysis tool is broken down into three main components. The part discussed here is the powerful Goal Seek feature that lets you work backwards from a function and determine the inputs necessary to get the desired output from a formula in a cell. Read on to learn how to use Excel’s What-If Analysis Goal Seek tool.

Excel’s Goal Seek Tool Example

Suppose that you want to take out a mortgage loan to buy a house and you are concerned about how the interest rate on the loan will affect the yearly payments. The amount of the mortgage is $100,000 and you will pay back the loan over the course of 30 years.

Using Excel’s PMT function, you can easily figure out what the yearly payments would be if the interest rate were 0%. The spreadsheet would likely look something like this:

The cell at A2 represents the yearly interest rate, the cell at B2 is the length of the loan in years, and the cell at C2 is the amount of the mortgage loan. The formula in D2 is:

=PMT(A2,B2,C2)

and represents the yearly payments of a 30-year, $100,000 mortgage 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.

Unfortunately, no mortgage lender is going to lend you $100,000 at 0% interest. Suppose you do some figuring and find out that you can afford to pay back $6,000 per year in mortgage payments. 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.

Many people in this situation would simply start typing numbers in cell A2 until the figure in D2 reached approximately $6,000. However, you can make Excel do the work for you by using the What-If Analysis Goal Seek tool. 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.

Begin by clicking on the Data tab on the Ribbon and locating the What-If Analysis button in the Data Tools section. Click on the What-If Analysis button and choose Goal Seek from the menu.

Excel opens up a small window and asks you to input only three variables. The Set Cell variable must be a cell that contains a formula. In our example here, it is D2. The To Value variable is the amount you want the cell at D2 to be at the end of the analysis.

For us, it is -6,000. Remember that Excel sees payments as a negative cash flow. 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. So, use cell A2.

Click 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. In our case, the cell at A2 should now read about 4.31%.

This analysis tells us that in order not to spend more than $6,000 per year on a 30-year, $100,000 mortgage, you need to secure the loan at no more than 4.31%. If you want to continue doing what-if analyses, you can try different combinations of numbers and variables to explore the options you have when trying to secure a good interest rate on a mortgage.

Excel’s What-If Analysis Goal Seek tool is a powerful complement to the various functions and formulas found in the typical spreadsheet. By working backwards from the results of a formula in a cell, you can explore the different variables in your calculations more clearly.

Related posts

• De What-If-analyse van Excel gebruiken

• De functie voor het uitspreken van cellen in Excel gebruiken

• 2 manieren om de transponeerfunctie van Excel te gebruiken

• Wat-als-analyse in Microsoft Excel begrijpen?

• Hoe lege regels in Excel te verwijderen

• Hoe If en Geneste If-statements in Excel te gebruiken?

• Hoe de PMT-functie in Excel te gebruiken?

• Hoe rasterlijnen in Excel te verwijderen

• Een eenvoudige draaitabel maken in Excel

• Hoe gebruik je Flash Fill in Excel?

• Sorteren op datum in Excel

• Gegevens in meerdere Excel-bestanden samenvoegen

• Snel meerdere rijen invoegen in Excel

• Hoe Word en Excel te openen in de veilige modus

• Hoe cellen, kolommen en rijen in Excel samen te voegen?

• Een VBA-macro of -script maken in Excel

• Een Excel-bestand delen voor eenvoudige samenwerking

• Wat is Microsoft Publisher? Een beginnershandleiding

• Snel getallen spellen in Word en Excel

• Wat is een VBA-array in Excel en hoe programmeer je er een?