AdvancedLesson 23 of 305 min read

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.

Good to know

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, =PMT(A2/12,A3,–A1) for 500000 at 10 percent over 36 months returned 16133.59 a month. Over 36 months that is a total of 580809.37, which means interest of 80809.37. These figures were calculated by the spreadsheet program and agree with the standard loan formula worked out separately in Python. The minus sign before the loan makes the payment come out positive.

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 =PMT($G2/12,H$1,–$A$1), locking the rate column, the term row and the loan amount. A longer term lowers the payment but raises the total interest, so the cheapest monthly payment is not the cheapest loan.

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, =NPV(0.1,D2:D5)+D1 gave 38877. A positive value means the project earns more than the 10 percent hurdle. IRR finds the rate at which NPV is zero; here it was 24.9%. Both were cross-checked with independent calculations. Note that Excel’s NPV treats the first value as one period away, which is why the initial cost is added separately.

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-reference formula, and compute total interest for each. Then use PMT with the NPER or RATE functions to answer reverse questions, such as how many months of saving reach a target.

Practice answer

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.

Common mistake

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?

  1. Multiply by 12
  2. Divide it by 12
  3. Leave it
  4. Subtract 12

Rate and period units must match.

Which function gives a regular loan payment?

  1. FV
  2. IRR
  3. LARGE
  4. PMT

PMT(rate, periods, -loan).

What does a positive NPV at your chosen rate suggest?

  1. The project loses money
  2. The project earns more than that rate
  3. The loan is free
  4. The data is wrong

It beats the hurdle rate.

What does Goal Seek do?

  1. Sorts data
  2. Finds errors
  3. Imports files
  4. 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?

  1. A longer term adds more interest
  2. Banks charge for Excel
  3. It always costs less
  4. 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.

Next lesson

Next: statistics and forecasting.

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.

Progress is saved in this browser.