Spreadsheet Foundations and Financial Modelling

Introduction

Spreadsheets are among the most widely used analytical tools in banking, finance and insurance. They support budgets, loan schedules, investment appraisal, premium calculations, claims analysis, management reporting and financial statements. Their value lies not merely in arithmetic, but in the ability to connect assumptions, calculations and outputs in a transparent and adaptable model.

A spreadsheet financial model is a structured representation of a financial situation. Inputs such as interest rates, transaction volumes, expenses and loss ratios are transformed through formulas into outputs such as cash flows, loan balances, profitability measures and solvency indicators. A reliable model must therefore be accurate, understandable, auditable and resistant to inappropriate changes.

This chapter uses spreadsheet concepts that apply broadly to Microsoft Excel, LibreOffice Calc and Google Sheets. Function names are generally based on Excel conventions; equivalent functions may have slightly different names in other applications.

Learning Objectives

After studying this chapter, you should be able to:

• explain spreadsheet structure, references, formulas and functions;
• distinguish relative, absolute and mixed references;
• design tables, validation rules and error controls;
• construct budgets, loan schedules, investment calculations, premium models and financial statements;
• use charts and pivot tables to analyse financial data;
• test, document and govern a financial model;
• evaluate the ethical and practical limitations of spreadsheet-based decisions.

1. Spreadsheet Architecture and Modelling Logic

A workbook contains one or more worksheets. A worksheet consists of rows and columns; their intersection is a cell identified by an address such as B7. A range is a collection of cells, such as B7:B18 or B7:D18. A cell may contain a label, a numeric value, a date, a formula or an error value.

A professional financial model normally separates three layers:

1. Inputs: assumptions and source data, such as salary growth, tax rates, interest rates and claim counts.
2. Calculations: formulas that transform inputs into schedules and intermediate results.
3. Outputs: reports, charts, dashboards and decision indicators.

This separation is sometimes called an input–process–output architecture. It reduces accidental overwriting and makes review easier. A model should also distinguish hard-coded assumptions from calculated values. For example, entering 8% as a policy rate is different from calculating an effective annual rate from monthly observations.

A useful modelling sequence is:

1. Define the decision or reporting question.
2. Identify data sources and units.
3. State assumptions explicitly.
4. Design the calculation structure on paper.
5. Build formulas using controlled references.
6. Test normal, extreme and invalid cases.
7. Present outputs with clear labels and documentation.
8. Protect and periodically review the workbook.

A spreadsheet is not automatically a model merely because it contains formulas. A model has a purpose, defined relationships, assumptions and outputs that support a decision.

2. Formulas, Functions and References

A formula begins with an equals sign. For example, =B5C5 multiplies the value in B5 by the value in C5. Operators include addition (+), subtraction (-), multiplication (), division (/) and exponentiation (^). Parentheses determine the order of evaluation.

A function is a predefined operation. Common financial and analytical functions include:

Relative, absolute and mixed references

A relative reference changes when a formula is copied. If D5 contains =B5C5 and is copied to D6, it becomes =B6C6. An absolute reference does not change: $B$2 always refers to B2. A mixed reference fixes either the column ($B2) or the row (B$2).

Suppose monthly sales are in B5:B16 and a commission rate is in F2. In C5, the formula =B5*$F$2 allows the sales reference to change down the column while keeping the rate fixed. Incorrect references are a frequent source of apparently plausible but wrong results.

Names and structured references

Meaningful names such as Tax_Rate, Loan_Principal and Annual_Rate can improve readability. Tables provide structured references, for example =SUM(Sales[Amount]). These references expand automatically when rows are added and can reduce range omissions.

Dates and time periods

Spreadsheets store dates as serial numbers, although they display them in date formats. Dates should not be entered as ambiguous text. A monthly model should define whether a period represents the beginning, end or average of a month. This matters for interest accrual, premium earned and claims development.

3. Financial Mathematics in a Spreadsheet

Simple and compound interest

For simple interest, the future value is:

where P is principal, r is the annual interest rate expressed as a decimal, and t is time in years. This formula assumes interest is calculated only on the original principal.

For compound interest with m compounding periods per year:

Here r is the nominal annual rate, m is the number of compounding periods per year and t is years. The periodic rate is r/m, and the number of periods is mt. The formula assumes a constant rate and reinvestment of interest.

The effective annual rate is:

For example, if P = 10,000, r = 6% and m = 12 for one year:

FV = 10,000 × (1 + 0.06/12)^12 ≈ 10,616.78.

In a spreadsheet, this may be entered as =10000*(1+6%/12)^12. The displayed result depends on rounding settings, but the underlying calculation should not be prematurely rounded.

Present value and net present value

The present value of a single future amount is:

where FV is the future amount, r is the effective rate per period and n is the number of periods. For several cash flows:

CF_t is the cash flow at period t, r is the discount rate per period, and C₀ is the initial investment at time zero. The rate and cash-flow periods must match. Spreadsheet NPV functions commonly discount the first supplied cash flow as occurring one period from now; an initial cash flow should therefore normally be added separately.

Annuities and loan repayments

For an ordinary annuity with equal payments at the end of each period:

A is the periodic payment, i is the periodic interest rate and n is the number of payments. Rearranging gives the payment:

The assumptions are equal payments, a constant periodic rate and payments at period-end. If payments occur at the beginning of periods, an annuity-due adjustment is required: the ordinary-annuity value is multiplied by (1 + i).

For a loan of 100,000 at 6% nominal annual interest compounded monthly over 5 years, i = 0.06/12 and n = 60. The monthly payment is approximately 1,933.28. In a spreadsheet, =PMT(6%/12,60,-100000) returns a positive payment when the loan advance is entered as negative under cash-flow sign convention.

A loan schedule calculates, for each period:

The closing balance of one period becomes the opening balance of the next. Small residual balances may arise from rounding; the model should disclose and control them rather than conceal them.

4. Building Core Financial Models

Budget model

A budget compares expected income and expenditure by period. A basic cash surplus is:

A budget workbook commonly includes assumptions, a monthly schedule, variance analysis and a summary dashboard. Actual-versus-budget variance may be expressed as:

For income, a positive variance is often favourable; for expenses, a positive variance is often unfavourable. Therefore, interpretation must be labelled rather than relying on colour alone. A favourable/unfavourable indicator can be created with an IF formula that considers the type of line item.

Investment model

An investment model may include initial cost, operating cash flows, terminal value, discount rate and sensitivity analysis. NPV is useful when the discount rate reflects required return and risk. IRR is the rate that makes NPV equal to zero, but it can be problematic with non-conventional cash flows or multiple sign changes. NPV should therefore not be replaced automatically by IRR.

Insurance premium model

A simplified risk-premium model can begin with expected loss cost:

Frequency is the expected number of claims per exposure unit, and severity is the expected monetary cost per claim. A simplified premium including expense and profit loadings can be expressed as:

The denominator must be positive, and the ratios must be defined consistently as proportions of gross premium. This is an illustrative pricing relationship, not a universal regulatory or actuarial formula. Real pricing may include risk adjustment, reinsurance, taxes, commissions, catastrophe loads, credibility, inflation and policy conditions.

Claims models should distinguish reported, paid and incurred claims. A simple incurred amount is often represented as:

This identity is useful for internal analysis, but reserve estimation requires actuarial methods and assumptions beyond a spreadsheet total.

Financial statement model

A simplified statement of financial position follows the accounting identity:

A model should include balance checks such as =Assets-Liabilities-Equity. The expected result is zero, subject to disclosed rounding. Income statement values may feed retained earnings, while cash-flow schedules explain changes in cash. The model must avoid double-counting depreciation, debt proceeds or asset purchases.

5. Data Tables, Validation and Error Controls

Tables and data quality

A well-designed data table has one row per observation and one variable per column. For example, a claims table may contain Claim_ID, Policy_ID, Accident_Date, Report_Date, Claim_Type, Paid_Amount and Reserve_Amount. Column names should be unique and consistent; dates and amounts should have controlled formats.

Data validation restricts entries. Examples include:

• whole numbers between 0 and 100 for percentage fields expressed as whole numbers;
• decimal values between 0 and 1 for ratios;
• dates within an approved policy period;
• drop-down lists for product, region or claim status;
• custom rules preventing a closing balance below zero.

Validation improves input quality but is not a substitute for review. Users can paste invalid values over rules or alter the workbook.

Error types and controls

Common spreadsheet errors include:

• #DIV/0!: division by zero or an empty denominator;
• #N/A: lookup value not found;
• #VALUE!: incompatible data type;
• #REF!: invalid or deleted reference;
• #NUM!: invalid numerical argument;
• circular reference: a formula depends directly or indirectly on itself.

IFERROR can improve presentation, for example =IFERROR(A2/B2,0), but indiscriminate use is dangerous because it can hide genuine data problems. A stronger approach is to test the denominator first, display a meaningful message, and maintain an error log. For instance, =IF(B2=0,"Check denominator",A2/B2) exposes the issue.

Control checks should include:

• balance-sheet check equals zero;
• loan closing balance reconciles to expected payoff;
• total detailed records equals total summary records;
• percentages sum to 100% where appropriate;
• no negative quantities where prohibited;
• all required fields are populated;
• scenario and date assumptions are valid.

6. Charts and Pivot Tables

Charts convert model outputs into interpretable patterns. A line chart is suitable for monthly cash flow or portfolio balances; a column chart compares products or regions; a scatter chart examines relationships such as premium and claim frequency. Pie charts should be used sparingly because comparing angles is difficult, especially with many categories.

A chart must have a meaningful title, labelled axes, units, a relevant time scale and an explanatory legend where needed. Truncated axes can exaggerate differences. Three-dimensional effects may also distort perception and should generally be avoided in financial reporting.

Pivot tables summarise large datasets by dimensions and measures. A claims pivot may place Region and Product in rows, Accident Month in columns and Paid Amount or Claim Count in values. Filters can isolate a period or product. Pivot results should be reconciled to the underlying data, and the refresh date should be documented. A pivot table is an aggregation tool, not evidence that the underlying data is correct.

7. Model Testing, Documentation and Governance

Testing should occur at several levels. Input tests verify data types, ranges and completeness. Formula tests compare selected outputs with independently calculated values. Scenario tests examine changes in rates, volumes, claims and expenses. Boundary tests include zero values, maximum permitted values and missing data. Reconciliation tests compare the model with accounting records or external systems.

Sensitivity analysis changes one assumption at a time, such as interest rate or claim frequency. Scenario analysis changes a coherent group of assumptions, such as a recession scenario. A data table can show how NPV or profit changes over a range of rates and volumes. Results should be interpreted as conditional outcomes, not forecasts with certainty.

Documentation should identify:

• purpose and intended users;
• data sources and extraction dates;
• assumptions and units;
• formula conventions and sign conventions;
• responsible owner and reviewer;
• version history;
• limitations and known exceptions.

Access controls, protected formula cells, separate input formatting and version control reduce operational risk. However, protection should not prevent legitimate independent review.

Applied Case: Illustrative Community Bank Budget and Loan Portfolio Model

Background

The following is a fictional illustrative case. A community bank wants to prepare a twelve-month budget while monitoring a small consumer-loan portfolio. Management needs monthly interest income, operating expenses, cash surplus and expected credit-loss information.

Problem

The bank currently keeps loan balances, interest assumptions and expenses on separate uncontrolled worksheets. A rate change in one sheet is not consistently reflected elsewhere. Management reports also contain unexplained differences between detailed loan data and summary totals.

Analysis

The analyst first defines a loan table with Loan_ID, opening balance, annual rate, term, payment and status. Monthly rates are calculated as annual rates divided by 12, assuming nominal annual rates with monthly compounding. A schedule calculates interest, principal and closing balances. The budget table contains fee income, interest income, salaries, technology costs, premises costs and expected credit-loss expense.

Approach and solution

Inputs are coloured and placed on a dedicated assumptions sheet. Data validation restricts rates to a sensible range and prevents negative balances. The loan schedule uses absolute references to the assumptions and relative references for the period rows. A control sheet checks that:

• each loan has a unique identifier;
• total closing balances agree with the portfolio system;
• every loan schedule ends at zero or has a documented residual;
• budget totals reconcile to the general ledger;
• the statement of financial position balances.

A pivot table summarises interest income by product and month. A line chart shows portfolio balance and a column chart compares budget with actual expenses. A downside scenario increases claim-like credit losses and reduces fee income, allowing management to examine liquidity pressure.

Outcome

The redesigned model identifies that the original summary omitted two loans because a fixed range stopped before the final rows. The structured table expands automatically, and the reconciliation check now reveals any future discrepancy. Management can trace reported interest income back to individual schedules and can explain changes caused by rates, balances or repayment timing.

Learning points

The principal improvement did not come from a sophisticated function. It came from controlled structure, explicit assumptions, reconciliations and independent review. Spreadsheet reliability is a governance issue as much as a technical issue.

Limitations, Ethics and Contemporary Practice

Spreadsheets are flexible but fragile. Hidden rows, overwritten formulas, inconsistent dates, undocumented manual adjustments and copy-and-paste errors can produce material losses. Large or highly connected models may be difficult to audit and should be replaced or supplemented by databases, controlled applications or specialist systems.

Financial models influence lending, pricing, claims settlement and investment decisions. Users must consider fairness, privacy, explainability and discrimination. A model that uses location, occupation or historical claims data may reproduce social inequalities or proxy for protected characteristics. Personal data should be collected and processed lawfully, accessed only by authorised persons and retained according to applicable policy and regulation.

AI-assisted spreadsheet features can generate formulas, classify data or explain trends. They can also invent formulas, misinterpret business definitions or expose confidential information through unsuitable services. AI output requires human validation, source checking, access controls and documented approval. It should augment professional judgement, not replace accountability.

Best practice is therefore to use spreadsheets for appropriate scale and complexity, maintain clear ownership, reconcile outputs to authoritative records, test material changes and escalate models whose risk exceeds spreadsheet governance capacity.

Chapter Summary

Spreadsheet financial modelling combines cell-based computation with financial theory, data governance and professional judgement. Reliable models separate inputs, calculations and outputs; use appropriate references; apply time-value-of-money formulas consistently; and document units, assumptions and sign conventions. Budgets, loan schedules, investment analyses, insurance pricing illustrations, claims summaries and financial statements can all be constructed from these foundations.

Tables and validation improve data quality, while error controls and reconciliation checks identify failures. Charts and pivot tables support interpretation but cannot correct poor source data. Testing, sensitivity analysis, documentation, version control and access management are essential because spreadsheet errors can affect financial reporting and customer outcomes. Ethical use requires privacy protection, fairness, explainability and human accountability.

Key Terms

Spreadsheet model: A structured workbook representing assumptions, calculations and outputs.

Cell reference: The address identifying a worksheet cell.

Relative reference: A reference that changes when copied.

Absolute reference: A reference fixed with dollar signs, such as $B$2.

Mixed reference: A reference fixing either its row or column.

Function: A predefined spreadsheet calculation.

Input: A value or assumption entered into a model.

Validation: A rule restricting permissible data entry.

Reconciliation: A comparison confirming that two related totals agree.

Compound interest: Interest calculated on principal plus accumulated interest.

Present value: The current value of a future cash flow at a specified discount rate.

Net present value: Discounted future cash flows less the initial investment.

Annuity: A series of equal cash flows at regular intervals.

Amortisation schedule: A period-by-period breakdown of interest, principal and balance.

Expected loss cost: Expected claim frequency multiplied by expected claim severity.

Pivot table: An interactive summary of structured data.

Sensitivity analysis: Examination of output changes caused by varying assumptions.

Scenario analysis: Examination of outcomes under a coherent set of assumptions.

Circular reference: A formula dependency that ultimately returns to itself.

Model governance: Policies and controls governing model creation, use, review and change.

Review Questions

Short Answer Questions

1. Distinguish between relative, absolute and mixed cell references.
2. Why should input, calculation and output areas be separated?
3. State the formula for a loan payment under an ordinary annuity and define its variables.
4. Give two examples of spreadsheet data-validation rules.
5. Why can indiscriminate use of IFERROR be dangerous?

Long Answer Questions

1. Explain how you would design and test a monthly bank budget workbook.
2. Describe the construction of an amortisation schedule and explain the relationship between interest, principal and closing balance.
3. Discuss how tables, pivot tables and charts can be used to analyse an insurance claims dataset.
4. Explain the importance of reconciliation, documentation, protection and version control in financial models.
5. Evaluate the advantages and limitations of using spreadsheets for insurance premium and claims analysis.

Analytical / Application Questions

1. A loan of 50,000 is issued for three years at a nominal annual rate of 9% compounded monthly. Build the formula for the monthly payment, identify the periodic rate and number of periods, and explain how you would verify the closing balance.
2. An insurer expects claim frequency of 0.08 claims per exposure and average severity of 2,500. Expenses equal 25% of gross premium and the target profit margin equals 10% of gross premium. Calculate the illustrative gross premium per exposure and state the assumptions and limitations of the calculation.
3. A financial statement model shows Assets of 12.4 million, Liabilities of 11.1 million and Equity of 1.2 million. Identify the control exception, calculate the difference, and propose two investigative steps before correcting the workbook.

Multiple Choice Questions with Answers

Question 1

Which reference remains fixed when a formula is copied to another cell?

A. A1

B. $A$1

C. A$1

D. $A1

Correct Answer: B. $A$1

Question 2

For an ordinary annuity loan payment, when are payments assumed to occur?

A. At the beginning of each period

B. At random dates

C. At the end of each period

D. Only at maturity

Correct Answer: C. At the end of each period

Question 3

What does the formula =IFERROR(A2/B2,0) do when B2 is zero?

A. Returns zero instead of the formula error

B. Deletes row 2

C. Changes B2 to one

D. Returns the numerator only

Correct Answer: A. Returns zero instead of the formula error

Question 4

If principal is 10,000, annual rate is 6%, monthly compounding applies for one year, which expression calculates future value?

A. 10,000 × (1 + 0.06)^12

B. 10,000 × (1 + 0.06/12)^12

C. 10,000 × (1 + 0.06/12)

D. 10,000 × (1 + 12/0.06)^12

Correct Answer: B. 10,000 × (1 + 0.06/12)^12

Question 5

Which chart is generally most suitable for showing a monthly trend in cash flow?

A. Line chart

B. Pie chart

C. Radar chart

D. Three-dimensional surface chart

Correct Answer: A. Line chart

Question 6

What is the principal component of a loan payment?

A. Payment plus interest

B. Interest divided by payment

C. Payment minus interest

D. Opening balance plus interest

Correct Answer: C. Payment minus interest

Question 7

Which spreadsheet feature is most appropriate for restricting entries to approved claim statuses?

A. Data validation list

B. Cell merge

C. Chart title

D. Page break

Correct Answer: A. Data validation list

Question 8

Using the chapter’s simplified premium model, expected frequency is 0.08 and severity is 2,500. If expense ratio is 25% and profit margin is 10%, what is gross premium per exposure?

A. 200.00

B. 307.69

C. 615.38

D. 2,500.00

Correct Answer: B. 307.69

Question 9

Under the accounting identity, if Assets are 12.4 million and Liabilities are 11.1 million, what Equity is required for balance?

A. 0.3 million

B. 1.1 million

C. 1.3 million

D. 23.5 million

Correct Answer: C. 1.3 million

Question 10

What is the principal purpose of a pivot table?

A. To encrypt a workbook

B. To summarise and analyse structured data

C. To prevent all formula errors

D. To replace source data permanently

Correct Answer: B. To summarise and analyse structured data