Financial and What-If Models

Introduction

A model is a spreadsheet that represents a situation so you can ask "what if?": what if sales fall 10 percent, or interest rates rise? Models inform decisions. They are only as good as their assumptions, so structure and honesty matter. This lesson is general education, not financial advice. Check important figures with a qualified professional.

Structure: inputs, calculations, outputs

A clear model has three parts, ideally on separate sheets or areas:

1. Inputs (assumptions): every number you choose or estimate, such as a loan amount, an interest rate, a price or a growth rate. Mark them clearly with a colour such as blue.
2. Calculations: formulas that use the inputs.
3. Outputs: the results and charts people actually read.

Rule: never type a number inside a formula. If 5 percent appears in ten formulas, you cannot change it safely. Put it in one input cell and refer to it.

Compound growth

If you invest 1000 at 6 percent a year for 5 years, the value after each year is multiplied by 1.06:

=1000 * (1 + 0.06) ^ 5      → 1338.23

With input cells: amount in B1, rate in B2, years in B3, the formula is =B1*(1+B2)^B3. The checked result is 1338.23 to two decimals.

Percent change between two values: =(new - old) / old. From 100 to 120 it is (120 − 100) ÷ 100 = 0.2, which formats as 20%.

Loan payments with PMT

PMT calculates a regular loan payment:

=PMT(rate, number_of_payments, loan_amount)

For a loan of 10,000 at 5 percent a year, paid monthly over 36 months:

=PMT(0.05/12, 36, -10000)

The monthly rate is the annual rate divided by 12. Money you borrow is entered as a negative number in some programs, so that the payment appears positive. A checked calculation gives about 299.71 a month. Over 36 months you pay 299.71 × 36 ≈ 10,789.52, so the interest is about 789.52.

Use the same function for savings plans and other regular amounts, being careful with the signs convention. Real loans have fees and conditions, so a model gives an estimate only.

Scenarios

Test best, expected and worst cases:

1. Put the key assumptions in a small table with a column per scenario.
2. Use a selector cell (a drop-down) to choose the scenario, and a lookup or INDEX formula to feed the live assumptions.
3. Show the outputs for each scenario side by side.

Name the scenarios honestly. A model that only has the best case is a wish.

What-if tools

• Goal Seek finds the input that gives a target result, such as the loan size that gives a payment of 250. It changes one input.
• Data tables or sensitivity tables show how an output changes across a range of inputs, such as payment for several interest rates.
• A manual approach: build a row of rates across the top and a column of loan terms down the side, and use formulas to fill the grid. It works everywhere.

Sensitivity thinking

Ask which assumptions matter most. If changing the growth rate by one point moves the answer a lot, spend effort checking that number. If a number barely matters, do not agonise over it.

Checking a model

• Sanity checks: do totals reconcile? Are results in a reasonable range?
• Break the model: put in extreme values to see if it still behaves.
• Check units and time periods: monthly rates with annual numbers cause common errors.
• Peer review: ask someone to find mistakes. Errors in spreadsheets are very common.
• Document assumptions and sources on a notes sheet.

Try it yourself

Build a small loan model with inputs for amount, annual rate and months, calculation cells for the monthly payment and total interest, and a sensitivity grid for three rates and three terms. Check one cell by hand.

Summary

• Structure a model as inputs, calculations and outputs, and never hide numbers inside formulas.
• Use growth and PMT formulas carefully, check the units, and compare scenarios honestly.
• Test sensitivity, sanity-check the results and document assumptions.