Lesson 15: "What ... If "Analysis:

What … if analysis

We use a “what if …” analysis to get an answer when there is a change in one or more variables.

In the first example we look at ‘what-value’ if a variable in the calculation changes
eg:

Select cell B4 where you have the calculation for the amount of payment
Click the Wizard feature
Select the category ‘Financial’ (Financial)
In the “Function Name” choose PMT (PMT) (The function with which a periodic payment is calculated)
Click ‘OK’
In step 2 of 2 you fill in the ‘Interest Rate’ (Rate). You must fill out the interest rate per period (in this case cell B3/12)
(Divided by 12 because there are 12 months in a year)
Click on the Tab
Enter the number of periods (in this case cell B2) (Nper)
Click on the Tab
Enter the total amount of the loan in the next box (in this case B1) (Pv)

The amount payable per month will be displayed as a negative number.

If you later change one or more cells (B1, B2, B3), the monthly payment amount is recalculated.

In the second example we look for a ‘what-value’ for the loan amount when we have the payment amount / month:

We take the example of the previous exercise

Select cell B4 (the formula in B4 must continue)
Choose the Tools menu (Tools) and ‘Goal Seek’ (Goal Seek)
We place our curor in the ‘Goal Seek’ dialog box where the amount to be paid is already filled in the cell
Press the Tab key
In the “Value” box fill in the desired outcome (eg -30000)
Press Tab
The ‘By changing cell’ is the cell in which the loan amount is to be determined (cell B1)

Click OK.
In the worksheet, you will see what the loan amount is going to be.
If you wish to change the amount of the payment / month , then repeat all the steps.

Awesome!
You've completed Lesson 15 START NEXT LESSON