Introduction to Financial Modelling

Principles of Excel as a Modelling Tool

Chapter 2: Excel Foundations

8 Topics | 1 Quiz
Chapter 3: Sensitivity Analysis

10 Topics | 1 Quiz
Essential Operations and Short-Cuts

Chapter 4: Operations and Short-cuts (I)

8 Topics | 3 Quizzes
Chapter 5: Operations and Short-cuts (II)

14 Topics | 1 Quiz
Introduction to Excel Functions

Chapter 7: Creating Conditionality, Comparison and Aggregation

9 Topics | 1 Quiz
Chapter 10: INDEX and XLOOKUPs

5 Topics | 1 Quiz
Building Models: Common Structures and Best Practice Principles

Planning Models for Decision Support

Chapter 15: From Planning to Practice

12 Topics
There are many ways (or criteria) in economics that can be used to assess the “profitability” of a project or decision that involves an up-front investment before profits are generated over time. Payback periods are one of the simplest, both in terms of concept, and of practical implementation.

The core idea is to measure how long it takes (in time) for the initial investment amount to be repaid (with a project that has a shorter payback to be preferred over one with a longer payback, if all other factors were equal or not relevant). One can consider to basic forms:

- Time-to-(first)-breakeven. This is the time taken for a business or project to first become profitable, or to first become cash flow positive for the first time (assuming that it is negative in the earlier periods or start-up phase). The measure does not take into account how much investment has been made; it asks only for the first time at which the profit (or cash flow) is positive, irrespective of how loss-making or investment-intensive it was before this point. (For this reason, measures such as rate-of-return and net present value are preferable, as discussed in later chapters of this course).
- Time-to-(first)-cumulative-breakeven, or the payback period. This is the time until cumulative profits (or cash flows) become positive (for the first time), so that the start-up losses will have been recovered. The payback period obviously requires – as an additional line in the model – the calculation of the profit cumulated since the beginning. (Once again, whilst this measure reflects the cumulative situation, it does not reflect the time-value of money, since each dollar of cash flow is treated equally; measures such as rate-of-return and net present value do so, as discussed in later chapters).

Variations of these all exist. For example, one can use “time-interpolation” to try to assess the time-point more precisely (in addition to using alternative measures which reflect the time value of money, as discussed later).

An example of each of these simple breakeven measures is given below.

The examples use the following profile of revenues and costs (and where we treat profit and cash as identical items for ease of presentation here) with profits or cash flow calculated as the difference between these:

Especially in models whose time periods are annual, one may wish to estimate more precisely the exact timing of breakeven, rather than having a whole year figure only. An interpolation in time can be done to estimate more precisely the time point at which breakeven is reached.

The simplest approaches are:

- Uniform interpolation. In the example above for cumulative breakeven, one would use 6.5 years as the estimate time point, since the cumulative breakeven occurred after the end of year 6, but before the end of year 7.
- Linear interpolation. In the example above, one would calculate an annual “improvement rate” in cumulative NPV of $21 at the end of year 6 (i.e. the difference between the year 7 and year 6 figures). Thus, the interpolated time point would be 4/21 of a year after the end of year 6 i.e. during month 3 of year 7.

(In general, if such accuracy is required it is better to considering building a more granular model, such as one with a quarterly time axis, but obviously such issues should be identified before the model is built, not as a brand new modelling activity that would need to start almost from the beginning).

- Reciprocal approaches. This is where (for example) the cash flow in year 2 is weighted at 50%, that in year 3 at 33.3%, that in year 4 at 25% and so on. Variations of this concepts are also possible, but it is not explored further, as the economic basis of such approaches is weak.
- Discounting approaches. This is where future values are discounted at a specific annual rate, which may be constant, or which may adjust in accordance with other economic variables (e.g. an interest rate yield curve, which is covered in later courses).

To implement these, one calculates the weighting factors that apply in each period (discounting factors), use these to calculate the weighted cash flows for each period, and then cumulate these before finding the time to cumulative breakeven for the weighted cash flows.

For example, with a discount rate of 12% per year, the weighting factor for the first year is 1/1.12 (approximately 0.89), and for the second year it is the square of this (approximately 0.80), and so on. The weighted profit in year 1 is then approximately -67 (i.e. -75 times 0.89) and so on. The cumulated (time-weighted or discounted) payback time is 7 years:

The concepts of net present value (NPV) and internal-rate-of-return (IRR) are discussed in detail in later chapters of this course. Readers who are already familiar with these concepts can for the moment simply note that the cumulative time weighted cash flow used above is simply the same as the net present value of the cash flows (cumulated to the same point).

Similarly if a periodic discount rate of 28% p.a. were used for the weighting, (so that the weighting in the first period is 1/1.28), then the sum of the cash flows over the 10 years is zero. That is, the internal-rate-of-return (IRR) over a 10 year horizon is 28% p.a.

This website uses cookies to improve your experience. We'll assume you accept this policy as long as you are using this websiteAcceptView Policy

Scroll to Top
error: **Alert:** Content is protected !!