What-if analysis and financial functions
Calculate loan payments, savings growth, NPV and break-even, build what-if tables with mixed references, and use Goal Seek and scenarios.
In this lesson you will
- Calculate loan payments and savings
- Judge a project with NPV and IRR
- Build a what-if table
- Use Goal Seek
About 17 minutes
What if the rate changes? What if we sell more?
Naveed is thinking of borrowing 500000 for equipment, and each lender quotes a different rate and term. His brother also asks, ‘If I save 2000 every month for ten years, how much will I have?’ Both questions are ‘what if’ questions: change one number and see the result. Excel is built for this. In this lesson you will learn the financial functions that do the maths, and three what-if tools: Goal Seek, Data Tables and Scenario Manager.
The figures here are for learning, not financial advice. Real loans have fees, taxes and rules that these functions ignore. Always check a lender’s own schedule.
Loan payments with PMT
=PMT(rate per period, number of periods, -loan amount) gives the regular payment. With annual rate 10 percent, monthly payments and 36 months, divide the rate by 12. In a test sheet, =
A Data Table by formulas
A what-if table shows one result for many inputs. Excel has a Data Table tool (Data, What-If Analysis, Data Table), and the same grid can be built with a formula and mixed references (lesson 6). Here is the monthly payment for three rates and three terms, all really calculated:
| Rate | 24 months | 36 months | 48 months |
|---|---|---|---|
| 8% | 22614 | 15668 | 12206 |
| 10% | 23072 | 16134 | 12681 |
| 12% | 23537 | 16607 | 13167 |
Each cell uses =
Savings with FV
=FV(rate, periods, -payment) gives the future value of regular saving. Saving 2000 a month for 120 months at an assumed 6 percent a year (0.5 percent a month) gave 327759, of which 240000 was the money paid in and 87759 was growth. Real returns are not guaranteed, so treat any rate as an assumption you can change, not a promise.
Is a project worth it? NPV and IRR
Suppose a machine costs 100000 now and brings 30000, 40000, 50000 and 60000 over four years. NPV adds up the future cash discounted at a rate you choose, then you subtract the cost. At 10 percent, =
Break-even
With fixed costs of 50000, a selling price of 60 and a unit cost of 35, break-even units are fixed cost divided by the margin per unit: =E1/(E2-E3), which gave 2000 units. Simple, but powerful: change the price and see how the break-even moves.
Goal Seek: work backwards
Goal Seek (Data, What-If Analysis, Goal Seek) changes one input until a formula reaches a target. Say profit is units times (price minus cost) minus fixed costs, and you want a profit of 20000. Excel tries values until it finds the price. The program below does the same by repeated halving, with 1500 units, a unit cost of 35 and fixed costs of 50000. The output is real:
# Goal Seek by hand: what price makes profit 20000? profit = units * (price - cost) - fixed
units, cost, fixed, target = 1500, 35, 50000, 20000
lo, hi = 0.0, 500.0
for step in range(60):
price = (lo + hi) / 2
profit = units * (price - cost) - fixed
if profit < target:
lo = price
else:
hi = price
print(f"price found: {price:.2f}")
print(f"profit at that price: {units * (price - cost) - fixed:.2f}")
price found: 81.67
profit at that price: 20000.00
Check: 1500 times (price minus 35) minus 50000 equals 20000 gives a price of about 81.67. In Excel you set the profit cell to 20000 by changing the price cell. Remember that Goal Seek changes one input only, and it overwrites that cell with the answer, so keep a copy.
Scenarios
Scenario Manager stores named sets of inputs, such as Best, Expected and Worst, and shows them side by side. A simpler approach is to build three columns of inputs and one set of formulas that picks a column using a drop-down and INDEX or CHOOSE. Either way, always label the assumptions, so a reader knows what was assumed.
Practice
Pick a loan or a saving goal that interests you. Build a table of payments for three rates and three terms with one mixed-
NPER(rate, -payment, loan) gives the number of periods, and RATE gives the per-period rate. If PMT returns a negative number, you have the loan as a positive in the last argument; use a minus sign before it or in front of the whole formula.
Forgetting to divide the annual rate by 12 for monthly payments, or to multiply years by 12 for the number of periods. The rate and the period must be in the same unit.
Check what you learned
Five quick questions. Pick an answer and you will see straight away whether it is right and why.
For monthly payments, what do you do to an annual rate?
- Multiply by 12
- Divide it by 12
- Leave it
- Subtract 12
Rate and period units must match.
Which function gives a regular loan payment?
- FV
- IRR
- LARGE
- PMT
PMT(rate, periods, -loan).
What does a positive NPV at your chosen rate suggest?
- The project loses money
- The project earns more than that rate
- The loan is free
- The data is wrong
It beats the hurdle rate.
What does Goal Seek do?
- Sorts data
- Finds errors
- Imports files
- Changes one input until a result hits a target
It works backwards from the answer.
Why is a lower monthly payment not always cheaper overall?
- A longer term adds more interest
- Banks charge for Excel
- It always costs less
- Interest stops
Compare the total paid.
Summary
- PMT, FV, NPV and IRR handle common money questions.
- Keep rate and period in the same unit.
- What-if tables vary inputs; Goal Seek works backwards.
- Label every assumption; results are not promises.
Questions
Is Excel's loan result the same as my bank's?
It may differ slightly because of fees, rounding, payment timing and day-count rules. Use it for comparison, and rely on the lender's schedule.
What is the difference between NPV in Excel and in textbooks?
Excel's NPV discounts the first value by one period, so the starting cost is usually added outside the function.
Does Goal Seek work in Google Sheets?
Goal Seek is available as an add-on in Sheets, and the financial functions work the same.
