Applied Lease Finance

A practitioner’s guide to applied financial Excel in leasing

Alastair Day

Copyright

Published by: Independent Publishing Network

Publication date: 31st January 2021

ISBN: 978-1-80049-277-6

Author: Alastair Day

Email: info@system.co.uk

Website: www.system.co.uk

Please direct all enquiries to the author.

Copyright © 2021 Alastair Day

All rights reserved.  No part of this book may be reproduced in any form or by any electronic or mechanical means, including information storage and retrieval systems, without permission in writing from the publisher, except by reviewers, who may quote brief passages in a review.

This publication is designed to provide accurate and authoritative information about the subject matter covered. In the preparation of this book, every effort has been made to offer the most current, correct and clearly expressed information possible. The materials presented in this publication are for informational purposes only. They reflect the subjective views of authors and contributors and do not necessarily represent current or past practices or beliefs of any organisation. In this publication, none of the contributors, their past or present employers, the editor or the publisher is engaged in rendering accounting, business, financial, investment, legal, tax or other professional advice or services whatsoever and is not liable for any losses, financial or otherwise, associated with adopting any ideas, approaches or frameworks contained in this book. If investment advice or other expert assistance is required, the individual services of a competent professional should be sought.

Note: Electronic books are not to be copied, forwarded or resold. No alterations, additions or other modifications are to be made to the digital content. Use is for purchaser’s sole use. Permission must be sought from the publisher regarding any content from this publication that the purchaser wishes to reproduce (info@system.co.uk). Libraries and booksellers and eBook distributors must obtain a licence from the publishers (info@system.co.uk). If there is found to be misuse or activity in contravention of this clause action will be brought by the publisher and damages will be pursued.

About the Author

Alastair Day has worked in the finance industry for more than 40 years in treasury and marketing functions and was formerly a director of a vendor leasing company specialising in structured finance for the IT and technology industries. After sale of the company to a public group, Alastair established Systematic Finance as a consultancy specialising in the following:

- Finance and operating lease structuring as a consultant and lessor

- Financial modelling – design, build, audit and review

- Training in financial modelling, leasing, corporate finance, projects, energy and credit analysis for in-house and public clients

- Author of leasing and financial modelling books:

- Mastering Financial Modelling, Pearson ‘Mastering’ series, 3rd Edition

- Mastering Risk Modelling, Pearson ‘Mastering’ series, 2nd Edition

- Mastering Financial Mathematics in Excel, Pearson ‘Mastering’ series, 3rd Edition

- Mastering Cash Flow and Valuation Modelling, Pearson ‘Mastering’ series

- Risk Analysis and Evaluation with Neil Jones, CIB/IFS

- Lease and Finance Evaluation, Euromoney

- The Finance Director’s Guide to Purchasing Leasing, Pearson

- Leasing, a Euromoney/DC Gardner workbook

- Elements of Finance and Leasing, CIB/IFS - a leasing credit textbook

- Leasing in the corporate finance series, CIB/IFS

- Systematic Business Calculator for time value of money and other business functions

Alastair has a degree in Economics and German from London University and an MBA from the OUBS.  He has held positions as an associate lecturer at the OUBS and the ifs School of Business (Chartered Institute of Bankers).

Who Needs this Book?

Leasing can be traced back to Sumerians in Ur who engaged in financing livestock.  Fifty years later, the king of Babylonia in his Code of Hammurabi enacted the first leasing laws and later Egypt, Greece and Rome engaged in leasing transactions of real and personal property.  Nearby the Phoenicians actively promoted leasing by chartering ships to local merchants.  Therefore, the leasing and hire of goods has a long history.

Moving forward to the 1700's, enterprises financed the use of horse-drawn wagons and as the Industrial Revolution in the UK required increasing amounts of capital, rolling stock and other capital goods were financed through leasing.  The joint stock companies’ acts were passed around this time and this made it easier for individuals to form limited liability companies and share the risk of failure.  A similar pattern can be found in the US with the growth of the railroads in the last nineteenth century.

After the Second World War, US Leasing was incorporated in San Francisco and in 1955 the US Revenue Service (IRS) passed ruling 55-540 which defined a finance lease as a separate financing arrangement.  Changes in US tax regulations from 1950’s provided tax breaks and made leasing more attractive together with economic growth requiring capital acquisitions.  European leasing took off in the early 1960’s buoyed by similar taxation advantages and the industry has matured in the intervening period to become a major source of finance.

In the early days, it was difficult to evaluate leases or price contracts correctly without the use of interest rate tables and mathematics performed on slide rules or log tables; however, this changed in the mid-1970s with the introduction of Hewlett Packard financial calculators like the HP38C and later the HP12C.  In 1982, IBM launched the IBM PC and simultaneously allowed the development of spreadsheet programmes such as Visicalc, Supercalc and Lotus 123.  These tools gave managers the ability to perform financial mathematics easily and put together bespoke programmes without the need to consult with IT departments.

Spreadsheets removed the requirement to learn free-form programming languages by providing an interface between the user and the machine code.  Microsoft originally marketed Multiplan and later launched Excel initially as a stand-alone application but it outpaced Lotus 123 when packed with Word to form Office 95.  Today Excel is everywhere, and the finance industry depends on it as the basic analysis tool.  However, many people attain a certain standard in Excel without being able to use all the available functionality.  The result is often:

- Models with serious unresolved errors

- Longer unstructured development time

- Models that cannot be understood or used by others in the company or team

- Sub-optimal information and analysis leading to poor decision-making

This book is for practitioners who need a comprehensive reference book outlining the basic financial theory of leasing and how it can be applied to Excel.  Each chapter consists of an explanation of the methodology followed by discussion of an Excel template with examples.  After finishing this book, you will have a satisfactory understanding of the key leasing procedures in Excel together with ideas for building and developing further models within your own organisation.

The file accompanying this book is available for download at www.system.co.uk. There is a flowchart of the Excel model below, setting out the chapters in this book and the structure of the Excel sheets.

How to Use this Book

- Read each of the chapters in turn as each chapter uses material from previous chapters

- Open the accompanying model and work through the schedules and the calculations

- Try the examples and ensure that you receive the same answers

- Flex the model inputs and see what happens to the answers

- Use the Systematic Design Method modelling style as a standard

- Practise and improve your efficiency and competence with Excel

- Develop comprehensive models within your own organisation

Alastair Day

info@system.co.uk

www.system.co.uk

Acknowledgement

I would like to thank my family, Angela, Matthew and Frances, for their support and assistance with this project.  Also, I would like to acknowledge the input of all the clients and thousands of attendees of my courses who have provided inspiration and valued discussion of applied Excel methods and solutions.

Conventions

The main part of the text is set by the eReader. For example:

Enter the Scenario Name as Base Case

Items on the menu bars are also shown in the same font:

Select Tools, Goal Seek

The names of functions are in capitals. This is the payment function, which requires inputs for the interest rate, number of periods, present value and future value:

=PMT(INT,NPER,PV,FV,TYPE)

Equations are formed with the equation editor and shown in normal notation. For example, net present value:

NPV = (Cashflow)N / (1 + r)N

Genders: the use of ‘he’ or ‘him’ refers to masculine or feminine. This convention is used for simplicity to avoid repetition.

Accompanying Excel Model

The comprehensive model used in the book is available at www.system.co.uk.

IMPORTANT: to use the file, make sure you allow macros and have the Analysis Toolpak installed.

(A) Macros: go to File, Options, Customize Ribbon and make sure that the Developer Tab is ticked.  Developer will then appear on the Ribbon at the top of the screen.

ALF2_SetUp_01 Customize Ribbon

Go to Developer, Macro Security and ensure that Macros are enabled.  If they are disabled, enable the macros, exit Excel and restart Excel.  Security does not reset until you restart Excel.

ALF2_SetUp_02 Macro security

(B) Analysis Toolpak: the model needs advanced Excel functions which are present in the Analysis Toolpak.  Go to File, Options, Add-ins, Manage Excel Add-ins.  Ensure that Analysis Toolpak and Solver are ticked.

ALF2_SetUp_03 Analysis Toolpak

When you open the file, the model defaults to the first sheet (Menu) as below:

ALF2_Intro_01 Menu

The model contains different schedules as below.  Inputs sheets have a dark blue tab colour while report sheets are turquoise and backing sheets, violet.  Use the list control above to jump to the different sheets:

- Menu – front sheet with hyperlinks to all other sheets

- TVM_Calculator – stand-alone time value of money calculator like an HP12C

- Lease_Inputs – this is the main input sheet with up to five scenarios built-in

- Annual_CashFlow – simplified cash flow sheet for annual rentals only

- Cashflow – lease cash flow taking data from the Lease_Inputs sheet

- Lessee_Accounting – lessee financial statements

- Lessee_Amort – lessee amortisation schedule

- Lessee_Accts – income statement and balance sheet

- Lessor_Fin_Lease – financial lease simple evaluation

- Lessor_NCI – net cash investment method of allocating finance income

- Lessor_SYD – sum of digits (Rule of 78) method of allocating income

- Lessor_Op_Lease – operating lease entries using asset depreciation

- Lessor_IFRS – constant actuarial rate method of recognising  finance income

- Comparison – NCI, SYD, operating and actuarial methods compared

- LeasevsPurchase – lessee evaluation of lease against purchase

- Settlements – asset management calculations

- Depreciation – comparison of depreciation formulas

- WACC – specimen weighted average cost of capital calculator

- Explanation – spare sheet

- Version - information on current model and version history

- Formats – model style guide on colours and formats

Each of the sheets displays a Menu button at the top of the sheet which returns you to the Menu.  This means that you can access any other sheet with two clicks.

The model uses a strict style guide as below to differentiate between different types of cells. All input cells are marked in turquoise.  You should not overwrite any of the formulas on unmarked (non-turquoise) cells as this may lead to unexpected results.

ALF2_Intro_02 Model formats

The main Lease_Inputs sheet controls the subsequent sheets in the model.  This allows you to load examples and examine lessor pricing, accounting, lessee accounting and evaluation and asset management.  The grey cells below are non-input cells and scenarios are loaded from cells on the right by entering the scenario number (one to five).  You should only vary the inputs on the right-hand cells with your own examples.  The cells are validated and offer choices to reduce erroneous inputs.

If you want to reload the initial examples, these are saved in Data, Forecast, What-if Analysis, Scenarios.

ALF2_Intro_03 Lease Inputs

ALF2_Intro_04 Lease scenarios

1 Overview

Scope of the Book

Sheet: TVM_Calculator / ALF2_Int_Rates

The book is designed to offer a technical introduction to financial mathematics and building leasing models to show the importance of basic design standards and clarity of layout.  Using these tools, the book applies these methods to model key leasing questions.  It covers:

- Discussion of types of leases from a lessee and lessor standpoint

- Key Excel financial functions in Excel for discounted cash flows and time value of money

- Leasing calculations for present values, interest rates, future values and amortisation

- Basic leasing calculations and methods

- Theory and practice of lease pricing

- Lease accounting for lessees and lessors

- Lessor evaluation methods and working out real returns

- Lessee evaluation and deciding whether it is better to lease or buy equipment

- Lessor asset management from acquisition through the lease term to final disposal

Each section outlines a specific leasing area and describes the theory behind the mathematics and then displays the model with worked examples.  It is easier to show the calculations using Excel models rather than HP or Texas Instruments calculators since you can always see the inputs, interim outputs and final answers.  The model is available online at www.system.co.uk so you can enter new data and try your own examples.

The next sections provide a refresher on financial mathematics starting with explanations of the basic calculations and standard Excel model design as followed in this book.

Financial Mathematics

The basic concepts of financial mathematics underpin investment analysis, corporate valuations, leasing, foreign exchange and other areas of corporate finance.  Lessors and end users face the problem of investing today in the hope of some financial gain or deferring expenditure.  Investing or writing leases attract a variety of potential risks and uncertainty so companies expect a reasonable return for investing and accepting an exposure to risk.  A lessor does not have to write a lease and must be able to decide if the overall forecasted returns outweigh the risks involved in writing the lease.

Similarly, investors require some extra return for investing in risky assets. This is the time preference of money as the basic financial theory, which depends on the following:

- If companies and individuals think logically and rationally, they usually prefer money today rather than the vague promise of returns in the future.  Money today is more certain than the promise of funds in the future.  Investors are assumed to be risk averse and require some sort of gain to give up the certainty of funds today.

- Inflation affects the decision as it can quickly erode gains when translated into current purchasing power. This may not be significant in Europe at present, however, there have been recent periods where inflation has been more than 10 per cent.

- Some lending is more risky than other forms.  Loans to governments can sometimes be considered ‘risk-free’ and perhaps attract a lower risk premium or rating than corporate lending, whereas lending to corporates exposes a lessor to a whole range of market and other risks.  In theory, the risk premium should reflect the timescale and relative risk reflected such that the decision to delay provides a greater gain when compared against alternatives.  Ratings agencies specialize in grading institutions and countries according to the perceived risk they represent.

These three factors underpin the concepts of time value of money and discounted cash flows, which drive the financial concepts and leasing examples in this book.  The next section provides a refresher on interest rates and calculations.

Simple Interest

The easiest interest rate to compute is a simple rate which predates the use of computers and calculators.  The simple or flat rate interest charges a price for the principal and does not consider the amount of capital paid back during the period and it is therefore a simplification of compounding covered later in this chapter. This method calculates interest only on the principal and does not consider the fact that a portion of capital is paid in each period. Nevertheless, for ease of calculation, simple interest is usually used for periods of less than a year in the bonds market or for calculating personal and consumer loans.

The simple interest INT on an investment (or loan) of a present value (initial) amount at an annual interest rate of r for a period of t years is:

INT = PV * R *t

INT = Interest

PV = Present Value

The future value (FV or maturity value) of a simple interest investment of PV dollars at an annual interest rate of r for a period of t years is:

FV = PV + INT = PV(1 + r t)

This is the amount that you would have at the end of the period. You can also solve for the present value PV to obtain:

PV = FV / (1 + r t)

PV = Present Value

FV = Future Value

These calculations can easily be translated into Excel as on the Simple Interest sheet.

ALF2_01_01 Simple Interest

Figure 01_01 above shows the simple interest on 1,000,000 from 28th February to the next year in a leap year. Three conventions show the number of days and the standardized number of days in the year. The interest, future value and present value are calculated using the formulas above.

ALF2_01_02 Example 2

The example above (figure 01_02) displays a two-day period in a leap year.  There are market conventions for deciding the number of days:  you need to consider leap years and the different lengths of the months. The above uses the US (NASD) method and the rules adopted are:

- US (NASD) method: if the start date is the 31st of a month, it becomes equal to the 30th of the same month. If the end date is the 31st of a month and the start date is earlier than the 30th of a month, the ending date becomes equal to the 1st of the next month; otherwise, the ending date becomes equal to the 30th of the same month.

- European method: start dates and end dates that occur on the 31st of a month become equal to the 30th of the same month.

Here the gap between the two dates is 2 days, but it is calculated as three on a 30/360-day basis. The addition of the standard number of days in the year results in three different levels of interest based on the simple rate of 10 per cent.

Simple interest could be used for calculating loan payments and is frequently used for hire purchase (US - dollar out) or lease purchase contracts where there is final purchase option available to the user. These are often structured as transactions with deposits and the balance of payments over a period. Figure 01_03 shows a 1,000,000 loan with a 10 per cent deposit repayable over five years monthly at a simple interest rate of 10 per cent.

ALF2_01_03 Simple loan interest

The net advance is 900,000 after the ten per cent deposit. The annual interest is 90,000 at ten per cent so the total interest payable is 450,000 over five years. The total payable is 1,350,000 after the deposit, which can be divided by 60 monthly payments. The monthly rental is 22,500 and the total payable with the deposit is 1,450,000. Notice that the total payable does not vary as you select monthly, quarterly, semi-annual or annual payments. This is because the method does not recognize the effect of time value of money on the interest payments.  For most requirements, this method is too simplistic since the cost of borrowing must vary as the repayment interval changes.

Compound Interest

Compound interest considers the time value of money of investments. Compounding means that interest is calculated not only on the initial investment but also on the balance in subsequent periods.  This is often termed time value of money.  These calculations underpin many applications in finance such as investment analysis, bonds, options, etc.  The two main structures are:

- Single cash flow time value of money such as calculating equal rentals or future values

- Multiple cash flow or discounted cash flow where cash flows vary in each period such as a leveraged lease evaluation

The interest rate in these structures is made up of three basic components:

- Risk-free rate – this is a rate that investors could invest fund at no or little risk. Typically, this is a ten-year government bond as a proxy to a risk-free rate.  These rates can be found on-line or in the Financial Times daily.

- Risk premium – this is a rate above the risk-free rate that investors demand as compensation for losing the use of the money today and potentially investing in more risky assets than government bonds at the risk-free rate.

- Inflation premium – inflation eats away at real returns and again investors need compensation for the loss of future purchasing power.

In the previous section, the amount payable was the same irrespective of the payment period, however compounding would lead to different results due to the intervals between payments.  You can split any lease or loan with equal payments into a distinct set of variables.  These are the number of payments, interest rate, capital value, future or residual value and regular payment.  The notation often used in these calculations is:

N – number of periodic payments

I – periodic interest

PV – present value or capital value

PMT – periodic payment

FV – future value

It is often useful to draw timelines of problems to understand the timing of cash flow or alternatively to draw a grid of the known and unknown parameters. Figure 01_04 shows a present value advance of 1,000. The annual nominal (compound) interest rate is 14 per cent and with quarterly payments the rate needs to be divided by four such that the periodic rate becomes 3.5 per cent per period.  As with all financial models, the recommended cash flow notation is used: cash out is always negative and cash in is positive.

ALF2_01_04 Timeline

This is a specimen grid showing the known and unknown parameters as an alternative to a timeline.  It is always a good idea to set the problem out in one of these forms.

Future Value

The future value is the amount to which a deposit will grow over time with compound interest.  The relevant formula is:

Future Value = FV = PV(1 + I/Y)N

I/Y = periodic interest rate

N = number of compounding periods

Figure 01_05 uses the future value formula as follows:

FV = 1,000 * (1 + 0.833%)^12 = 1,511.07

ALF2_01_05 Future value

Present Value

The future value formula can be restated to calculate the present value of a single future value (see Figure 01.06). This is discounting the future cash flow back over all the periods to today’s value.  The formula is the reverse of the future value.

Present Value = PV = FV / (1 + I/Y)N

ALF2_01_06 Present value

The example present values 1,511.07 back over 12 periods at a rate of 3.5 per cent per period. The answer is, of course, the original value, 1,000.  The full formula for solving other variables is below.  Since the formula must equal zero, you can always solve for the missing value.

0 = PV+ (1 + iS) PMT [1 – (1 + i)-N / i] + FV(1 + i)-N

Where

- S = payment toggle or switch denoting whether payments are made at the beginning or the end of each period.  It makes a difference whether you receive the payments at the beginning or the end of a period.  Payments at the beginning of each period will reduce the size of the net advance and lower the capital outstanding in every period.

- i = periodic interest rate

Nominal and Effective Rates

The interest rates above are nominal or stated annual interest rates. This means that a rate of 3.5 per cent with quarterly payments is quoted as 14 per cent, i.e., 3.5 per cent multiplied by four payments per annum.  The true rate of return that is actually paid or charged depends on the number of compounding periods in the year and this is the annual percentage rate (APR).  Other terms for the same rate are effective or effective annual rate (EAR).  Adjustments must be made for the number of compounding periods so the rate will vary between monthly, quarterly, semi-annual and annual payments.

The formula to migrate from a nominal to an effective rate is:

Effective = ( 1 + Nominal / C ) ^ C – 1 where C is the number of compounding periods

The formula to determine a nominal rate from an effective rate is:

Nominal = (1 + (Effective) ^ (1 / C) – 1) * C.

The example below uses 14 per cent as a nominal rate and calculates the effective rates and back again. The example in Figure 01_07 of 14 per cent with quarterly periods is:

Formula = [ (1 + 14%/4))4] – 1 = 14.752%

ALF2_01_07 Nominal and effective

Excel contains built in functions for interest rate conversions such as EFFECT and NOMINAL.  The inputs are the rate and the number of compounding periods in a year and the functions calculate the result without the need for a formula. You can test the model by working backwards and forwards.  Annual effective and nominal rates will always be the same since there is only one period in the year.

Continuous Discounting

You can apply interest over smaller and smaller periods until it becomes continuous rather than discrete (see Figure 01_08). The mathematical relationship is:

Formula = (1+effective rate) = e Nominal Rate

e= Euler’s constant of 2.718281828, the base of natural logarithms, is gained by using the function EXP in Excel with the argument 1.  This constant is the inverse of LN, the natural logarithm of a number.

ALF2_01_08 Continuous discounting

The continuous effective rate for 14 per cent is 15.03 per cent, which compares with the monthly rate of 14.9 per cent using the EFFECT function. This method of compounding arises in option pricing and is an option on most financial calculators.

Conversions and Comparisons

It is important to understand the cash flows and their timing since it can be misleading to review cash flows over different time periods without understanding the intervals. The example in Figure 01_08 compares a quarterly transaction with payments at the beginning of the period against a monthly transaction with payments at the end of the respective periods. In each case, the nominal rate is 14 per cent and the PMT function is used to calculate the regular payment from N, I, PV, FV together with the periods per annum and the advance/arrears switch.

The simple interest rate is calculated based on the charges and the true number of periods. This is eleven quarters in the first case (after payment of the deposit on signing) and 36 months in the second case (rentals are in arrears). The simple interest rate is higher for the quarterly transaction since the periodicity is not considered. The effective rate is higher for the monthly option since there are twelve compounding periods against four.

ALF2_01_09 Comparison

This section outlined the basic interest calculations and demonstrated the importance of setting out the true nature of the cash flows to understand the price being charged for the funding

Basic Excel Design

This book uses Excel models and acknowledges that effective modelling requires an understanding of the Excel program, finance, presentation and design.  The models were developed using the methodology set out in the following sections.  This involves in tandem:

- Adoption of a structured design methodology and process

- How to develop initial ideas into Excel applications

- How to audit and check models before use

- How individual variables ‘flex’ or ‘behave’

- How to isolate key variables for further testing

- How to avoid costly mistakes by proper testing of potential scenarios

- How to present and summarise findings effectively

For example, a simple lease versus purchase model could show a positive net present value to the lessee by producing a spreadsheet in the place of manual calculations with a calculator.  A correctly produced application would not only find a single answer but also:

- Outline all the rules and display the key inputs in a logical format

- Provide a series of answers based differing inputs such as the alternative client borrowing rate

- Show charts of key variables demonstrating how they flex with changes in variables

- Demonstrate levels of risk and uncertainty

- Show how likely you are to be close to the answer or alternatively the potential variance

- Act as a template for future examples without major redesign

Many corporate Excel models fail since the author has not followed a clear method for writing the model.  The model ‘emerges’ from a series of iterations rather than exhibiting a clear structure.  Common Excel errors include:

- No visible form of layout – inputs, calculations outputs and reports are not clearly marked and mixed together

- No marked inputs or control section containing all the variables that can be changed

- No specific colour for inputs so that a user does not know where to enter new data

- No borders or shading to improve overall appearance of the management reports

- No data validation of inputs to ensure that the inputs contain the correct type or length of data

- Mixture of number formats with differing numbers of decimal places, for example where numbers are formatted in millions without any clear notation

- Mixture of numbers and formulas e.g., C6/4 for a quarterly interest rate

- Hard coded data where you expect to see formulas such that the code is inconsistent and difficult to check

- No management reporting or analysis of the answer or deciding if the answer meets a desired threshold;

- No use of functions since the net present value is built up using a factor for each period. Use of the function NPV would help to reduce the number of possible errors by reducing the individual cell codes

- No sensitivity analysis. What happens if you change the discount rate or fail to generate the benefits on schedule?

- Graphics normally help to show to management the cash flows or the sensitivity analysis. For example, a graph of the cumulative cash flow to demonstrate the payback would assist.  Also, charts are useful for checking and auditing models

- No self-checks to ensure that the cash flow adds up across and down

- No commenting on individual cells or explanation of how the model works

- No information on the version number or the author

- No set up for printing with standard headers and footers on each sheet

Basics of Design

There is no unique method of designing models and authors develop a functional style that is quick to implement and can be repeated easily without error This may sound simplistic, but a sound methodology reduces development time and error correction significantly.  There is no correct way to design spreadsheet models, but there are errors that people make if they have no overall plan or method. While there are degrees of planning needed, depending on the complexity of the application or the amount of money involved, it is important to have a defined plan and a method for different sorts of spreadsheets. It is easy to start keying formulas without thinking too carefully about the overall design and then spend more time moving code and starting more sheets. The objective is to define a model plan or tick list of methods and formulas for superior design.

The most efficient method is to follow a systematic design process and method on all models and make all the sheets follow a consistent pattern. The examples with this book follow the same layout and design. While simple spreadsheets may be sufficient for one-person, corporate models should conform to standards and rules, especially when used by others or incorporated into the decision-making process. In its basic form, this means splitting the functions in the model between inputs, calculations and outputs, as in Figure ALF2_01_10.

ALF2_01_10 Design process – Systematic Design Method

Objectives

Many people appear not to consider the aims and objectives before starting a model. It is necessary to know the precise outputs required. For example, what are the key answers required? Although it sounds simplistic, it is a good idea to focus on five to ten key answers, write them down somewhere in the documentation and refer to them during development to make sure that you do not deviate from the original aims. You could, for example, write down the critical information required by the model.

In many models, it is difficult to work out where the answer is, as it is hidden in the calculations. Models are often capable of providing more useful information and analysis. For example, a simple cash flow budget could also use further sheets for recording the actual profit and loss and balance sheet. With both budget and actual figures, variance reports based on absolute and percentage differences are possible together with management reports and graphs for divisional reporting. In this way the model starts to produce meaningful management information for decision making.

User Interface

This needs to be reviewed critically since this is what you and your users will work with. There may be several different audiences for the same model with different requirements for inputs, detail and information. Older models sometimes put the variables on the left between the label and the figures; for example, the tax rate could be placed here. However, users may like to see all the inputs in one place and be directed as to what and where to input data. This is logical when you want to flex key inputs. It is very frustrating for somebody who has received a copy of a new application to have to spend time understanding how it works and where to enter data.

The interface should:

- Be instantly intuitive

- Be clear to the user how to use the model

- Guide the user through a logical flow of information from input through calculations to end result

- Give a pointer to the answers and executive summary

The use of borders, colours and formats assists this process as in the calculator shown in Figure ALF2_01_11. The user is directed to enter variables and press buttons to calculate an answer as with a hand-held financial calculator such as the HP17BII or TI BA II Plus. In this financial calculator, you know the term, interest rate and capital and want to calculate the loan payment or rental required.  The answer is updated at the bottom based on the button the user pushes (Payment/PMT), so that the flow of information is from top to bottom.

ALF2_01_11 Financial calculator

Key Variables and Rules

Variables and rules should be broken down and identified. Variables must be placed together as in the calculator in Figure ALF2_01_11.  It is essential that variables are not hard-coded: you cannot enter code as =C6/4 as this breaks the rule.  For example, the frequency must be a user input; otherwise, what would the user change if the payments were monthly as opposed to quarterly? Setting out the rules and constraints in one place mean that the author becomes better organised and may understand the process of solving the business problem more succinctly. The process may also uncover new variables which need to be modelled.

Rules are also important: corporate taxes are complex in most jurisdictions and models need to reflect tax shields and tax settlement dates exactly. The corporate tax payment method has changed in the UK from once a year to a four-quarter payment system for most companies and across Europe there is a tendency to reduce marginal corporate tax rates. This presents the modeller with a fresh set of challenges to understand both the transitional and final arrangements. A modular approach showing interim calculations and results assists with simplifying the coding and auditing.

Layout

Breaking down the calculations into manageable groups shows workings and results clearly. Excel allows separate sheets in one two-dimensional workbook rather than attempting to link a series of separate files as was the norm with the original Lotus 1-2-3 and Excel 4.0. Rather than placing accounting schedules on the same sheet, it is surely more logical to put separate statements such as income statements and balance sheets on separate sheets in a single file.

The example in Figure ALF2_01_12 breaks up the layout into:

- User inputs in a control section

- Management summary – visible on updating inputs; this saves the user from scrolling to the answer

- Calculations area using variables only from above inputs area

- Marked answer

- Area for sensitivity, graphics or other detail

- Workings area outside the printed area of the sheet

ALF2_01_12 Layout

The flow of information through the model follows a logical pattern with the inputs in the top left where a user would expect to find them. Models that are more complex would place these areas on different worksheets, but again inputs and calculations should not be mixed, and development should be split into logical sections.

As in Figure ALF2_01_12, the use of colours, typefaces, patterns and borders for different data and information in a consistent manner can assist to show the logical framework. Colours and formats are used purely for function, not decoration, as it is fundamental to the design method to identify different types of cells. For example, column K and line 41 delineate the end of the model and no code should be placed beyond this boundary. The models in this book and those developed by the author for commercial purposes follow this style and format.

Individual Modules

Individual modules can then be produced within a planned framework and calculations broken up into separate areas or sheets. The layout is important for user and author understanding and is critical for ease of further development and auditing.

Calculation areas must contain only formulas and they must not be mixed with hard-coded numbers. This is to ensure the integrity of the calculations. For example, multiplying by 0.19 for corporation tax will only cause problems if the tax rate were to change, since you would have to search and replace through all the sheets in a file and in the Visual Basic macro code. Using an input cell as a range or a named cell means that you can be confident of changing only one cell for the whole file to update itself accurately.

Menu Structure

A menu navigation structure is useful in complex models, since it:

- Forces the developer to think about the structure the model

- Makes it easier for a user to understand the overall structure

- Facilitates easier navigation using buttons rather than manual tabbing along sheets

Management Reporting

Management reports and summaries are normally required for larger models as they would be in a full management report. Not everybody needs all the detail and calculations, and summaries assist different users in understanding the results and the important outcomes.  For example, a project management application could show the cash, loans and expenditure summary to demonstrate the coverage ratios and the degree of loan security in the model.

Future Development

Development within a model is important: an evaluation model may need further variables and changes in the next year and a structured model designed in modules aids future development. The test is to see how new variables could be added and check the potential disruption in the design.  It is a mark of a poor model if you must redesign the model substantially.

Alternatively, sensitivity tables and scenarios allow a user to produce multiple answers within the same model and test the variance based on changing inputs. A single base case answer such as net present value is not enough for informed decision making and development should include some further testing of how variables ‘flex’ the eventual results. A well-constructed model can accept a number of further techniques as ‘layers’ without major redesign.

Similarly, graphs and dashboards can be very useful in demonstrating the answer to management or other audiences. People often grasp complex ideas more easily through pictures. For example, a settlement model could include a chart of the asset exposure to show the difference between market values and capital outstanding.

Testing and Auditing

Systematic testing is required to ensure that there are no mathematical errors and that the information flow through the model is correct. The calculator in Figure ALF2_01_11 can be tested against third-party discount rate tables or the output from another financial calculator. Test data is required, which makes uses of all the buttons, inputs, frequencies and payment types.

Protection

Protection is beneficial if a model is given to others to prevent accidental destruction of code. This is simple if the author clusters all the inputs together and colour-codes them. Entire sheets can be protected, and the input cells can be unprotected in blocks. Protecting sheets and workbooks preserves the author’s work and ensures that the application is used as intended. For example, if an evaluation model were given out to a user who then overwrote cell formulas with numbers, the integrity of the model would be threatened, and one would have to begin by checking every cell for possible changes. Spreadsheet best practice uses protection to limit access to data and unauthorised changes to models except where authorised by the model ‘owner’.

Documentation

Many authors do not bother to write notes about a spreadsheet and its construction. This is risky, since either they or their colleagues may have difficulty at some point in the future in maintaining the code. Many models may start as ‘pet projects’ and, as with any other computer program, need background information. Ideally, notes should be in the model rather than on scraps of paper in a file. You could add comments or paste Word or Acrobat PDF documents into the file as objects since integral notes are usually better than separate files or hand-written notes.

Notes could include:

- Reasons for adopting a particular design or template

- Key formulas and calculations

- Rules and methodology

- Summary of history and changes to the model linked to version numbers

Peer Group Comments

Users or colleagues can often make constructive suggestions and, although this process is often painful after you have spent time on producing a masterpiece.  Potential users need to attempt to enter data and be comfortable with how a model operates. Users involved in the design process who are asked for their opinions may be more enthusiastic adopters and users.

The main factors are:

- Ease of use with a clear interface

- User guidance from inputs through calculation to answers and reports

- Complexity reduced to a minimum for audit and checking purposes

- Answers or output shown clearly

Example Model

Whilst the number of payments, payment or interest rate can be calculated manually, it is simpler to use the built-in functions in Excel. These are:

NPER – number of payments

RATE – periodic interest rate

PV – present value or capital

PMT – periodic payment

FV – future value

ALF2_01_13 TVM calculator

The upper cells E5:E12 in Figure ALF2_01_13 indicate the inputs at the top as a loan of 1,000 over twelve periods quarterly in advance with no future value.  The model works out the periodic interest and the answer section uses conditional IF statements to show an answer solely for the zero variable in the inputs section. The five formulas are:

=NPER(PeriodicIntRate,Payment,Present_value,Future_value,Payment_Toggle)

=RATE(Number_of_payments,Payment,Present_value, Future_value, Payment_Toggle)*100*(12/Payment_ Frequency) – the rate must be multiplied back to an annual rate

=PV(PeriodicIntRate,Number_of_payments,Payment, Future_value, Payment_Toggle)

=PMT(PeriodicIntRate,Number_of_payments,Present_ value,Future_value, Payment_Toggle)

=FV(PeriodicIntRate,Number_of_payments,Payment, Present_value, Payment_Toggle)

The answer is shown as 99.98.  The model is set up to calculate any fifth variable when four are known and input even if one such as the future value is zero. For example, the payment is only 90.0 so what is the future value left unamortized over the period?  Twelve payments of 90.00 are not enough to write off the loan at an interest rate of 3.5 per cent per quarterly period (14 per cent per annum).

ALF2_01_14 Future value calculation

Since there are answers above the future value, no answers are calculated in the upper cells B19:B22. The future value is calculated as 150.90 (see Figure ALF2_01_14).  As a further example, you could calculate the number of periods needed to write off 1,000 at 90.00 per period (see Figure ALF2_01_15). This is not an integer number of payments as the answer as 13.70.  Therefore, the calculator is flexible since the initial time value of money formula with the answer equal to zero must hold.  If you change the payment and all other variables remain the same, solving for the number of payments will produce the number of payments required to write off the loan.

ALF2_01_15 Number of periods calculation

Summary

This chapter outlines the contents of the book and shows the basic financial mathematics behind loan calculations.  It is important that Excel models are designed to a consistent standard and this chapter discusses common mistakes and outlines the modelling standard used in this book.

2 Leasing Structures

Definitions

As discussed in chapter 1, leasing has a long history since there is a general requirement for assets in business, but not always the funds available to pay for them up front.  Definitions of leasing usually compare leases with loans except that the sole security is usually the equipment rather than a fixed charge or other security. The essential benefit for the user is to enjoy the use of assets while paying for them principally to aid cash flow.

This is one definition of leasing:

“A lease is an agreement whereby the owner (lessor) allows another party (lessee) to use an asset for a specific period of time in return for a rental. The asset could be fixed or movable plant or real estate. The ownership and use of the asset are separated allowing one party to fund the purchase of the asset and the other to use the asset in its business or trade.”

The lessor is the owner of the equipment who grants certain rights in return for obligations such as the payment of rental during the lease period.

The lessee is the user of the equipment who provides assurances about the use, insurance and maintenance of the leased assets and pays rental to the owner in return for ‘quiet possession’ of the equipment.  This means that the user enjoys almost all the rights of ownership in return for the rental payments.

Based on current accounting standards, there are three main types of lease irrespective of the marketing terms:

- Finance lease (or capital lease in USA) is like a loan, where the rentals usually write off the whole of the capital cost over the rental period and depending on the country the user will own the asset on expiry or have unlimited use in return for a small fee.  This is like a loan except that the lessor security comprises the equipment rather than a charge on real estate or other assets

- Operating lease denotes a lease where the lessor relies not only on the rental period for its profits but also retains a significant economic interest in the asset. It must continue to rent, sell or dispose of the asset on expiry to realize 100 per cent of its investment. Thus, it is both a financier and a trader in the asset and needs to rent to more than one user to obtain a reasonable return on the financing

- Lease or hire purchase is like a finance lease except that the hirer usually has an automatic right of purchase on expiry.  There is usually a nominal purchase fee to pass title as required by local law

In Britain, users do not always have an automatic right to purchase goods on expiry of a finance lease since the tax depreciation, at the time of writing, may follow legal ownership for most short-term leases.  Automatic transfer would turn the contract into a hire purchase arrangement and any tax benefits would be claimed by the user.  The usual expiry arrangement is a nominal rental and participation in the sale proceeds to provide the user with almost all the benefits of legal ownership.  In continental Europe, tax benefits normally follow economic ownership and finance leases usually allow a simple transfer of ownership on expiry.  These differences are discussed in more detail in the next section since there are differences between short- and longer-term leases.

Leasing Products

Renting, hiring and leasing are often used in marketing literature to describe different forms of lease.  Renting often appears ‘easier’ than leasing and may be used by lessors to describe a basic leasing contract.  In English law, leasing is merely a form of bailment where a party is given possession of a chattel or asset owned by somebody else.  Further classification requires a discussion of ownership and tax issues to ascertain the exact position of lessor and lessee in the contract.

Classification can be difficult due to the variety of marketing terms used to define leasing products by banks and lessors.  Whatever definition is chosen, there can be some overlap and therefore it is important to understand the type of product being offered.

Example: many technology leases state that they are ‘fixed term rentals’.  On closer examination, the proposal may be for a finance lease except that the lessor has omitted a right to retain the equipment on expiry of the rental period or dispose of it and participate in the sale proceeds.  Run-on rentals are charged at a ‘market’ or substantial rate.  If the interest rate appears commensurate with a full-pay-out lease, this may represent an expensive method of acquiring the equipment, especially if the equipment is retained on expiry.

This is a table of characteristics for each product type, finance leases, operating leases and hire purchase:

ALF2_02_01 Lease products

The above is a summary of leasing products from a UK perspective where there are distinct differences of title, upgrading and taxation.  Where possible, other terms are given since it is vitally important to ascertain the form of contract and determine the rights and restrictions under the agreement.  UK leasing distinguishes between legal and economic ownership.  There are also differences between short- and longer-term leases, where the latter allows the end user to claim tax allowances on the equipment.

Financial leasing is a means of financing the use rather than the ownership of the asset.  This distinguishes leasing from other forms of financing such as renting, service contracts and loans.  However, there is no clear dividing line between use and ownership since different distinctions apply for different purposes.

Example: A hire purchase or lease purchase of a printing press over five years.  The user does not obtain legal ownership of the goods until payment of all rentals and the nominal passage of title fee on expiry, yet he has economic ownership of the goods throughout the rental period.  There are different treatments for accounting, law, tax and ownership.  One could consider a gradual change as illustrated by the figure below.

ALF2_02_02 Comparison of lease products

A short-term rental may represent the most flexibility but will almost certainly be the costliest over a longer period.  An exhibition company may only require extra personal computers for a two-week period and this option represents the most cost-effective solution.  Retaining the equipment at the same rental for a longer period would incur extra charges leading to a higher cost of financing.  They are more likely to fund computers in their offices on medium-term leases since such flexibility is not a prime consideration.

The three main product types by accounting and tax treatment are finance leases, operating leases and hire purchase.  This is a more complete list of available products and their characteristics.

A. Finance Lease

A finance lease is also known as a full pay-out lease since the leasing company seeks to recover the whole of the capital cost out of the leasing rentals in the minimum rental period.  The role of the lessor is principally one of financier and does not participate in the residual or the continued use of the equipment.  The margin is derived usually from the lease rentals alone.  This type of lease is called a capital lease under US terminology.  The lessor owns the asset for the duration of the agreement and there are no provisions for sale to the user.

There are features that typify a finance lease:

Selection

The user selects the equipment from a manufacturer, distributor or vendor and the leasing company plays no part in this decision.  Consequently, the lessor usually requires an indemnity from the user that he has been responsible for the selection and that he will continue to pay the rentals whether the equipment performs in whole, part or at all.  Often there is a separate certificate of acceptance as part of the documentation.  The lessee confirms acceptance and fitness for purpose at the time of delivery together with a note of the insurance provisions.  This document provides comfort to the lessor in the event of a later dispute concerning the applicability of the asset.

The user is responsible for normal wear and tear and insuring the equipment for full replacement value unless there are separate arrangements such as self-insurance backed by a bond or indemnity.  Obsolescence and technology change are also borne by the user.  The leasing company is providing ‘quiet possession’ and it requires the user to take full responsibility for managing and maintaining the asset.

Leasing Period

The lease period covers most of the economic life of the asset.  In practice, this is more than 75 per cent of the useful economic life.  For example, clients often lease computers on three-year rental periods, which equate to the normal upgrade periods and there may some use in a secondary market after this period.  Although the equipment could be utilised by another user, any residual value is often ignored, and most users would consider the economic life to be the same as the leasing period.

Bargain Purchase Option

In the UK, the user does not obtain the right to take automatic title to the goods on expiry by means of an automatic passage of title or bargain purchase option.  This is because the lessor is the legal owner of the equipment and has the right to claim capital allowances.  A lease with an eventual automatic purchase option would be classed as a hire or lease purchase.  The rentals continue with an annual peppercorn or nominal rental.  This is usually a low figure such as one or two per cent per annum of the original capital cost in advance.

Automatic Transfer of Title

A UK lease does not provide for a direct sale to the user.  Usually, the lessor appoints the user as its agent to identify a third-party buyer and allows the user to retain 95 per cent of the sale proceeds.

Present Value

Originally all leases were ‘off balance sheet’, which means that leased assets were not shown as assets with a corresponding loan liability to the lessor.  This was clearly an imbalance since bank loans had to be disclosed as liabilities.  The accounting profession produced standards for categorising leases and setting out the accounting treatment.  There was no agreed universal international standard however the first standard, Federal Accounting Standard Board 13 (FASB 13), was passed in the US in 1976 and is still considered an international benchmark.  The UK standard, SSAP 21, came into force in 1984 and has since been clarified by later standards such as SSAP 21 and FRS 5.  The international standard IFRS 16 has replaced UK standards for international and public companies since 2019 and this uses a ‘Right of Use’ accounting framework.  The standard removes the distinction between finance and operating leases and requires most leases to be categorised and capitalised using the same method.

Under the US standard FASB 13 and the UK accounting standard SSAP 21, the present value of the minimum lease payments is greater than 90 per cent of the fair value of the asset on inception.  IFRS 16 follows this method except that there is no fixed ‘90%’ test but merely lease examples that would be considered finance leases.  Thus, the rentals do not include a significant residual and as stated earlier, the lessor is dependent on the initial rental stream for its margin.  A lease could be structured with a final ‘balloon’ payment and still be a finance lease.  If the final payment were an option, then the test would be required on the minimum rentals payable by the user.

A finance lease is therefore a form of rental that ‘transfers substantially all the risks and rewards of ownership of an asset to the lessee’.  This is the definition first used in SSAP 21 and is intended to denote the position of the parties.  The user acquires an economic interest in the asset and the lessor is the financier and owner of the equipment.

Accounting and Taxation

Under IFRS 16, a lessee is required to capitalise the lease on the balance sheet and show the equipment as an asset and the rental payments as a liability to the lessor.  Since the lessee has ‘economic ownership’ the rules oblige the disclosure of the borrowings and this impacts financial ratios such as gearing (leverage), current ratio and gross margin.

The rentals are allowable as a trading expense and the lessee may not claim capital allowances (tax depreciation) in respect of the leased assets.  Previously, there were special rules under the Inland Revenue directive, SP3/91, to prevent a lessee accelerating lease payments and relief is available based on the normal depreciation period.

Example: A client chose to lease machinery over twelve months and expensed the rentals.  A one-year lease would, in theory, give rise to a 100% first year allowance.  UK Inland Revenue rules would however equalise the relief over the normal client’s depreciation period for such assets.  This would not normally be twelve months and would more likely be three or five years.

Value Added Tax and Sales Tax

The rentals are normally treated as a trading expense and VAT (sales tax) is added to the rental.  This is reclaimable on the quarterly VAT form as input VAT.  In the UK, there are special rules relating to the VAT treatment of car leases which reduce the amount of reclaimable tax.  Under current legislation, cars purchased by leasing companies for the sole purpose of leasing and contract hire are deemed to ‘VAT qualifying’ cars.  This means that the leasing company can reclaim the VAT on the purchase and finance the net of VAT capital value.  A portion of the VAT on the rentals is also reclaimable by lessees depending on the proportion of private miles.  Private mileage must be supported by auditable mileage logs.  These VAT rules are not applicable where there is a final purchase option as in hire or lease purchase.

B. Fixed term Rental

Many fixed term rentals are in fact finance leases deprived of the secondary rental and opportunity to participate in the eventual sale proceeds.  Lessors accept leases as part of vendor schemes from third parties and then have agreements to sell the lease or the equipment back to the original supplier on termination.  The lease rental may be very similar to a standard finance lease and therefore it is important to understand the cost of terminating early, retaining the asset or extending the lease.

C. Operating Lease

Traditionally, operating leases were rental agreements with two broad characteristics:

- The present value of the minimum rentals is less than a threshold of around 90 per cent of the fair value on inception

- The rental period is for less than about 75 per cent of the economic life of the asset.  As such the lessor retains a significant interest in the residual value and is dependent on the resale value for attaining his required return

IFRS 16 uses the same broad characteristics without firm percentages showing that economic ownership has not been transferred.

The rentals write the equipment down to a pre-determined residual value and the lessor assumes an interest rate in the pricing.  The user does not know the residual value and his quotation states periodic rentals over a given term.  At the end of the period, there are usually three choices:

- Return equipment in good working order and condition

- Continue renting at the same or a ‘market’ rental

- Upgrade or take out a new rental agreement

Example: a manufacturer is well-placed to sell and re-market used equipment into secondary markets, which do not jeopardise its pricing and branding in main markets.  Alternatively, it could sell to education, refurbish or upgrade to dispose of volumes of equipment and thereby use its expertise.  It understands and precipitates product life cycles and is therefore able to predict with more certainty sale values in the future.  Such knowledge is a competitive advantage, and it may be able to set higher residuals than its competitors and still realise the same returns on capital.  In the computer industry, there are several ‘captive’ leasing companies, which support the sales effort of the parent company.  They tend to offer leasing and rental since they want customers to upgrade and buy more equipment and use finance to generate higher product sales volumes.  This is part of the integrated solution offered to clients.

A lessor would usually set stringent return conditions in order that the equipment would be worth the maximum attainable.  The lessee would of course not gain from the final sale of the equipment.  For example, a lessor would require the return of all accessories to a laptop or PC since he would wish to re-market a complete unit rather than individual components.

ALF2_02_03 Cash flow comparison

An operating lease may be attractive where an organisation requires equipment for a relatively short period of time or expects to change equipment due to changing needs or technology advances.  The cash flow comparison is above in figure 02_03.

Example: A contractor working on a project for an oil company, which is expected to last a maximum of two years.  The client can charge the rentals on to the oil company and whilst operating leasing may seem expensive, it may be more cost-effective than outright purchase or finance leasing due to the limited requirement.  The leasing company accepts the risks of obsolescence and the client attains risk-free budgeting.

The user does not mind that the inherent interest rates may be higher than in a typical finance lease.  The lessor is taking extra risk as a ‘trader’ in the assets and such risk must, in corporate finance terms, be rewarded in the pricing.  Risk should drive the required return on capital and therefore the interest rate charged to the user.  In contrast, the exposure of the user to loss on disposal is lower as it reduces his exposure to potentially higher individual loss.  Most users are not skilled in disposing of used equipment and prefer to leave it to experts.

Lease Purchase or Hire purchase

A lease purchase or hire purchase agreement provides for a final purchase option at a nominal figure.  The leasing company remains the legal owner until payment of all rentals, other charges and a nominal title fee payable on expiry.  This is often £1 or £10 as a nominal ‘passage of title’ fee.  Sometimes this is a balloon or higher amount, but the result is the same where the owner receives the total capital sum.  Some companies describe the two terms as:

- Hire purchase – writes off all the capital during the rental period

- Lease purchase – rentals over the period followed by an agreed balloon rental

Example: A car hire purchase provides for a deposit followed by periodic rentals and then a final payment of forty per cent of the original price.

The lease or hire purchase agreement does not usually provide for a secondary period or run-ons.  The lessor wishes to sell the equipment directly to the lessee on expiry of the agreement and the arrangement is simply one of financing secured on the leased asset.

This arrangement mirrors a loan except that the lessor retains ownership until the final option to purchase fee is paid.  It is accounted for by capitalising the asset and showing the liability to the lessor, since the user is the economic owner and almost automatically becomes the legal owner on expiry.  An option to purchase of £10 would be considered a bargain purchase and all users would be expected to take up the option.

Hire purchase is treated as a sale for tax purposes.  UK capital allowances are available on the capital portion and relief on the interest portion of the rentals.  The interest is derived using a method such as the sum of digits (Rule of 78) to allocate the interest to accounting periods.

VAT is not added to the rentals in the UK.  Instead, the first rental includes the full VAT on the asset, which is reclaimable by the user at the end of the first VAT period.  This is the same as if the user had purchased the asset where he would pay the agreed price plus the sales tax.  As mentioned earlier, special rules apply to cars and VAT is not reclaimable.

Contract Hire

In the motor trade, contract hire is common and this combines features of finance and operating leasing.  Organisations require vehicles for a specific period and mileage and expect to return vehicles at the end of the period.  Maintenance and other services can be included with the rentals as a ‘fleet management’ package such that the provision of the vehicle is reduced to a single monthly charge.  Fleet management often masks the pricing of the vehicle funding; however there is current move towards ‘open book’ pricing where the fleet financing company divulges more information about the true cost of servicing the fleet and its margins.

A fixed price rental for a predetermined period and mileage, usually inclusive of maintenance and other services may be selected by the user.  Some facilities include insurance, replacement vehicles and asset tracking.  The lessor owns the vehicle and accepts the operating and residual value risks.  The main benefit is a fixed budget monthly cost for a vehicle and maintenance covering an agreed mileage over the contract period.  Extra rentals are of course payable if the vehicle exceeds the mileage and the lessee assumes the cost of returning the vehicle in the ‘agreed’ condition.  Disputes may occur on the interpretation of the contract regarding the return conditions.

Contract Purchase

This is like contract hire, but based on a hire purchase or conditional sale financing agreement. The user can buy the vehicle at the end of the contract or return it. The lessor may then offer to buy it back and therefore assumes the residual value risk.  This is of course in times where the lessor believes there is a profit to be made by trading the vehicle.

Many consumer vehicle agreements are based on this methodology.  Where the client pays a significant deposit, he reduces the credit risk to the lessor.  Since the residual value is set at a low level, the lessor can then afford to offer competitive funding based on the reduced risk.

Example: An organisation is considering a vehicle costing £20,000.  The leasing company proposes a deposit of £5,000 and thereafter 35 rentals of £300.56 based on an interest rate of 10.25 per cent.  The final payment of £8,000 represents 40 per cent of the original value.  The client can return the vehicle or purchase it for £8,000.  The illustration below shows a net advance of 15,000 after the deposit followed by the rentals.

ALF2_02_04 Vehicle cash flow

The settlement amounts per month against the predicted declining value would show that the lessor has significant security in the asset.  If it could repossess the vehicle, there would be little risk of loss in the financing.  The value of the asset is always likely to cover the capital outstanding.

Fleet Management

The provision by a lessor of administrative and cost control services in respect of a fleet of vehicles for a fee. The user selects the services, which can include vehicle acquisition and disposal, together with all other aspects of the running of the vehicles.  IT and cost control increasingly play a major role in servicing a large fleet and this product allows ‘one-stop’ shopping together with management information services.

Rental and Hire

Self-drive, rental or hire applies to vehicles (cars, minibuses, vans, trucks, tractors etc.) and equipment of all types), which clients rent for varying periods on a daily, weekly or monthly basis.  Prices are high since the user is paying for flexibility and is not interested in the disposal or continued use of the asset.  Organisations often overlook equipment on rental and continue to pay the monthly invoices.  Inherent interest rates are of course high to repay hire companies for significant obsolescence and other uncertainties.

This is a summary of the final ownership decision:

ALF2_02_05 Ownership decision

Benefits of Leasing

Lessors and lessees both have reasons to lease and this section outlines some of the principal reasons.  Normally only a few of the advantages will be important on any proposal.

Lessee Reasons for Leasing

Figure 02_06 shows a selection of reasons why organisations decide to lease rather than purchase with own funds or bank lines.

ALF2_02_06 Lessee reasons for leasing

Spreading the Cost

The major reason for leasing is usually to spread the cost of purchasing the asset.  A small company leases a machine and pays for it while using and deriving the benefits and revenue from it.  This conserves capital within the business for buying stock and funding the working capital cycle.  The credit problem for the leasing company is to decide if the company can maintain both the quantity and quality of its cash flow to pay the rentals punctually.  A client must generate sufficient cash every period to pay the rentals on time.

Example: A three-year lease payable at a rate of ten per cent nominal quarterly in advance would result in payments of about 95.10 per thousand of asset value, however this could be further reduced by:

- Residual or balloon payment

- Longer term, for example the quarterly rental would reduce to about 80 over four years

The table below works out how much a quarterly rental of £9,500 payable quarterly in advance ‘buys’. This is 99,884 over three years quarterly and 127,123 over four years.  Lessors often display advertisements for light vans and commercial vehicles on a weekly basis to make the cost seem affordable against the potential revenue.  A trader can assess how much he can afford out of weekly revenue to finance new transport.

The table also shows rates per thousand, e.g., over three years with quarterly rentals.

9500 / (99,884.98/1,000) = 95.11

ALF2_02_07 Rental to capital grid

The advantage of a lower payment is that is creates affordability and lessees can acquire more equipment for the same monthly or quarterly payment.  This is important with small contractors who compare the weekly cost of a new van on lease with their other weekly cash out-goings.

Clear Source of Finance

Leasing represents a major source of funding both for private and government organisations as alternative to cash, loans or direct grants.  In some cases, a clearing bank may not be willing to extend further credit if it already provides factoring, overdraft or medium-term finance and has exhausted the full value of available security or charges.

Leasing often increases debt capacity, notwithstanding the disclosure of the debt in annual reports.  The lessor’s security comprises usually the asset alone and does not interfere with a clearing bank’s charge or debenture over the traditional fixed assets of the company such as land and buildings.  The chapter on lease versus purchase discusses debt capacity and includes a debt capacity factor to enable more precise calculation of the after tax of borrowing.

100% Finance

Leasing often requires no deposit and offers a 100% financing option.  Banks are often reluctant to fund an entire package, when they only possess security in the equipment.

Example: An organisation has run out of budget in this financial year and negotiates a lease to start in this financial year with rentals commencing in three months when budget is available in the new financial year.

Cost Relative to Other Sources of Finance

In a later chapter, a methodology for a lease versus purchase analysis is discussed.  In circumstances where a lessee does not pay mainstream corporation tax, a lessor may pass on the benefits of tax relief in the pricing and thereby offer lessees a lower rental.  Alternatively, if the lessee’s after-tax cost of borrowing is higher than the after-tax cost of leasing, the lessee may be better off leasing rather than funding by way of a loan or cash.

Cross-border leasing uses taxation incentives in different countries to present a cheaper form of borrowing to multi-national and large corporations.  Lessors use complex computer models to assess the relative benefits and include them in their transaction pricing.  A full analysis of the financial alternatives together with non-financial factors is needed to understand the lease versus buy decision.

Ownership

Whilst many lessees wish to own assets, there are occasions where ownership can be a burden.  With IT equipment, it can be beneficial to allow others to collect leased equipment on expiry of the rental period and dispose of it safely.  Most lessors offer facilities, which allow the lessee to take title on expiry, enabling lessees to choose the type of facility they require at the outset.

Example: technology leasing companies have started to advertise that they will collect your old equipment at the end of lease and dispose of it in an environmentally friendly way.  Surely this is the ultimate expression of a revenue asset where you pay for usage and not final ownership.

Flexibility

Bank loans are often thought to be inflexible and lessors introduce contracts for specific markets to assist with the marketing of those products.  In IT, lessees want to replace their equipment regularly and need facilities with regular additions or a pre-planned upgrade route.  The pace of technology is so fast that upgrading is often the only the option to maintain a competitive advantage.  One thinks of financial services where the systems run much of the decision-making and organisations must continuously improve their systems and controls.  A five-year lease with no break options is clearly not appropriate for this type of organisation.  ‘Flexleases’, exchange hire and ‘technology refresh’ leases constitute some of the terms, which purport to provide added flexibility.  These leases allow some form of upgrading or equipment exchange at regular intervals to provide some flexibility and fit in with the lessee’s strategy.

Example: vendor leasing companies sell the idea of flexibility and continuous upgrading.  A major objective for them is to manage relationships with their clients.  They want to keep in touch and both supply the equipment and other services to increase their overall margin.  Products tend to consist of fixed term rentals without renewal options or operating leases.  Both offerings mean that it is more expensive for users to retain equipment on expiry of rental periods.  Potential clients perhaps should be aware of the potential costs for retention and upgrading which are normally extra to the periodic rentals.

Another market is vehicle leasing, where users want a low repayment per month and may be prepared to offer a higher deposit to secure a lower rental.  Personal contract purchase assumes a residual value at the end and a sizeable deposit (where eradicates much of the credit risk).  At the end of the two- or three-year period, the hirer can purchase the vehicle, return it to the distributor and walk away or lease a further vehicle.  This is compatible with the desire to change vehicles frequently and uses the residual value to lower periodic payments.

Security

In most cases, the only security a lessor acquires is the equipment itself.  Whereas a bank will require a charge over the business as security for a loan, a lessor is often limited in its actions on bankruptcy.  This is of course an advantage for the lessee where leases can be agreed with a minimum of paperwork.

Example: A car distributor employs finance specialists who can discuss financing options at the same time as signing the order for the car.  The financing is available at the point of sale and no further security is usually required.  This is of course subject to minimal credit checks; however, the car distributor stays in control of the sale and can offer a complete acquisition and financing package.

Further Bank Credit

As stated above, banks may not wish to extend credit lines and leasing offers another source of credit without setting further large-scale facilities.  Often lessees do not want to be totally reliant on one source of credit.  This could be important if the lessee is likely to require more traditional banking lines to finance, for example the development of a new factory.

Leasing may be the only source of finance open to new and small companies.  In venture capital situations, clearing banks are often unwilling to advance funds without a trading history.  A leasing company may be able to structure a lease with a sizeable deposit and shorter period and be dependent on the intrinsic declining value of the asset.

Convenience

Leasing is often available at the point of sale.  For example, an office equipment salesperson will have at his disposal a range of financing options.  He may also carry documents and information on more than one leasing company.  His objective is to close a sale and if the client wishes to lease, to ensure that he can provide a financing facility.  If he is unable to gain a leasing company credit confirmation, his company may not accept the order for processing.

Computer companies use their leasing companies to assist with marketing as part of a total product offering.  They attempt to manage the leasing and product life cycles of their clients and add value to their products.  A company with hundreds or thousands of laptop computers often has difficulties in managing the asset register, whereas a leasing company has a vested interest in knowing where its security lies and may be prepared to help.  Vendor leasing companies may offer this service since it needs complete inventory information to time upgrade and other offers.

Example: A leasing company offering operating leasing to local authorities on heating systems.  If the equipment lies in tenanted flats and apartments, the local authority may require the ability to terminate individual locations.  In this instance, a leasing company needs to be able to provide a simple termination procedure and a real-time register of equipment and locations in a dedicated database.  It must charge individual settlements and update the periodic rental to reflect the reduced capital.

Responsibility

Some companies require formal project appraisals as applications for capital whereas ‘rental’ capital may be available at a lower level of authority.  Overseas subsidiaries may lease rather than gain formal capital sanction from head office.  Vending machines or communications equipment may be leased by a bank, which prices the facility at relatively high rates of interest.  Institutions approve such agreements at a relatively low level.  Certainly, leases to banks and other financial institutions for office equipment often reflect rates, which must be greater than the bank’s marginal cost of funds.

Balance Sheet Treatment

The balance treatment of leases in the UK was governed by the accounting standards SSAP 21 and IAS 17 and since 2019, IFRS 16 has applied to most companies.  This latest accounting standard uses a ‘Right of Use’ model and seeks to remove the distinction between formerly on-balance sheet finance leases and off-balance sheet operating leases.  Nevertheless, there are opportunities to remove some leases from balance sheets for example by packaging up contracts with added services.

Lessee Disadvantages

The advantages of leasing need to compare with the possible disadvantages:

- Owning assets avoids any of the leasing small print on flexibility, upgrading and termination.

- Owner participates fully in residual value proceeds.  These may be insignificant for technology equipment but potentially valuable in the case of printing presses or other long-life machinery.

- Companies may be able to gain greater taxation advantages than lessors.  This is true in the UK where lessors claim lesser tax allowances than lessees.

- Some UK grants for technology have usually only been available to legal owners and not lessees.

- Certain organisations such as financial services wish to show all assets to bolster the value of their balance sheets.

- Leasing may not be appropriate if an organisation only has an uncertain or short-term requirement as the penalty for early termination can add significantly to the overall cost of the funding.

- Some organisations want the prestige of owning assets and have a risk adverse attitude to borrowing.

Lessor’s Reasons for Leasing

Lessors are often banks which view leasing is an extension of mainstream lending.  Their profit is derived from payment of the principal and a return on the principal.  The lessor assesses the risk in lending to the lessee and uses the pricing to reward it for taking the risk.  This is a standard commercial relationship.

The lessor interest income is used to pay for the cost of borrowing, bad debt, marketing, administration and other costs.  In times of recession, lessors are not willing to lend and may spent more time on restructuring and refocusing the business.

Here is a summary of the lessor’s reasons for offering leasing:

ALF2_02_08 Lessor reasons

Tax benefits

With finance and operating leases, lessors capitalise the equipment and may claim writing down allowances as a benefit of ownership.  In the UK, starting in 1984, the first-year allowance of 100 per cent and corporation tax rate at 52 per cent have been gradually reduced to 18 per cent and 19 per cent.  This pattern has been repeated across Europe with other countries reducing the corporate tax rates and benefits of investment.

Similarly, the UK government have introduced a 100 per cent first year allowance for small and medium businesses for an annual investment of £1,000,000, which is not available to lessors.  Therefore, a company entering a lease purchase arrangement could gain the benefit of an enhanced tax position whereas a lessor is restricted to 20 per cent.  Again, this reduces the real benefit of leasing for lessors and puts pressure on the pricing.

Interest Spread

Yields in leasing are usually quoted as a rate per thousand or per cent per period.  It is not usual except in consumer markets to quote an effective or annual percentage rate (APR).  Pricing especially for small unit leases can be high when ranked alongside traditional bank rates.  Leasing may provide lessors with the opportunity to earn higher yields than could be available in general banking.

Similarly, leasing may provide the opportunity to market other services such as life assurance or maintenance.  Lessors often receive maintenance charges from clients, and then repay the funds to providers in arrears and thereby receive the deposit interest on the deposits.

Leases in the UK are usually fixed repayment in the small and medium sectors.  This is of course one of the selling points for leasing and lessors may protect themselves by borrowing fixed or floating depending on the yield curve for interest rates.  Variation clauses are notoriously difficult to administer due to the periodic variations, and lessors have tended to prefer fixed payments.

Fees

Many leasing companies now charge fees for documentation, termination or handling.  Auto agreements often contain charges on inception as well as on expiry and clients do not focus on the extra ‘minor’ cost.  These charges should of course be included when calculating the overall cost of funding.  If a finance company charges £50 per document and processes 10,000 documents a year, this is £250,000 of extra gross margin for little extra effort.  On larger transactions, there may be arrangement fees, which must be factored into the cost of the debt to arrive at a true cost of borrowing.

Terminal Values

In operating leases, the lessor has a significant economic interest and risk in the asset.  This is greater than ten per cent in present value terms and it therefore depends on disposing of the equipment for at least its book value on expiry.  Some lessors may wish to speculate to try and realise a second margin.  For example, a car leasing company could take back vehicles at the end of rental periods and refurbish them for sale.  With professional treatment, the vehicles will be more saleable than offered for sale as returned by users.  The vehicles may yield extra profits at the end of the rental period.

The nature of operating leasing means that the lessor must be concerned with asset management and ‘churning’ the portfolio.  The required yield may be higher than for finance leasing since the lessor needs to be rewarded for the extra risk inherent in operating leasing.  Other profits could derive from early terminations and renewals.  For example, fork-lift trucks may be refurbished at the end of a five-year lease and then re-leased for a further five-year period.  The equipment possesses an economic life more than five years and the secondary lease at a lower cost allows a second-tier user to make use of the asset.  This market demonstrates the benefit of financing and trading in the assets for leasing companies which understand the risks and rewards in their market sector.

Vendor Leasing

Many lessors use sales-aid or vendor leasing to increase sales.  This describes an arrangement whereby a lessor teams up with a manufacturer or distributor to offer financing packages.  It could be an informal arrangement or a more substantial joint venture with sharing of losses and administration systems.  The manufacturer gains through increased sales whereas the lessor acquires an expert to assist with re-marketing and terminations.  This allows both parties to establish a mutually beneficial partnership and reduce the cost of finding new business.  Leasing companies are of course very positive about such arrangements, but recession often brings about a strategy re-think and a return to potentially safer lending.

Risk Analysis

Lessors accept a variety of risks when writing a lease and usually the potential losses are greater than the potential gains over the lease period.  Leasing is more risky than traditional borrowing due to the nature of the assets and the lack of contact between the lessor and the user.  Traditional clearing banks have far more information with their access to the bank accounts.

ALF2_02_09 Financial analysis framework

The framework above tries to separate risk into related categories rather than providing a long list of risk elements.

High Level Macro or Environmental Risk

There are many risks that the leasing company cannot directly control, which may have a direct bearing on the lease portfolio.  Risk increases with time and therefore long-term leases are more susceptible to shifts in the underlying environment.  Examples are:

- Country and political risk which affects cross-border leases

- Economic cycles and patterns as they affect lessees and increase the incidence of default and delinquency

- Legislation such as Health and Safety which may increase costs

- Interest rates and the shape of the forward yield curve

- Technology risk where equipment may be obsolete on expiry of the lease or worth less than the original residual forecast

- Currency changes for the supply of equipment or payment of rental

- Accounting standards that change the pattern of income allowable in different accounting periods or affect the balance sheet disclosures

- Tax rate changes that reduce the after-tax profit available in future periods

- Retrospective tax changes relating to the level capital allowances (tax shield) and how they can be claimed

- Consumer protection legislation and the effect on lessee rights, contracts and their enforceability

Industry Risk

Lessors specialise in specific industries such as small ticket, medium or large value items and use their expertise to offer services in these areas.  Specific considerations are:

- Market conditions vary due to supply and demand

- Residual values are initially a forecast of final value and lessors vary in their ability to pinpoint on a portfolio basis the likely end values which allow them to sell the equipment at the required value and recoup all their costs.  Secondary markets depend on supply and demand within geographic markets

- Competition between leasing companies and manufacturers leading to reduced margins and increases residuals in a bid to gain more business

Client Risks

Companies can be checked for probability of default at the time of writing the lease, but circumstances change, for example with the loss of a major client or difficulties in receiving payment from client.  Areas of risk include:

- Credit downgrades and the effect on borrowing costs

- Liquidity issues or failure to gain sufficient funds to cover working capital needs

- Slow or missed payments or general delinquency

- Poor maintenance of assets leading to impairment costs

- Insurance issues where client fail to insure equipment for replacement cost

- Increased borrowings

- Theft of assets due to poor security

- Fraud on the part of employees or other parties

- Seizure of assets by landlords or other third parties

All the above issues affect the lessee’s ability to make returns on their invested capital and will reduce available cash for paying lease rentals

Non-financial Risk

The financial analysis framework does not include non-financial factors.  Here is a selection:

- Environmental risk due to the use and misuse of the assets.

- Asset management and the expertise of the lessor in refurbishing and remarketing assets irrespective of the economic climate.

- Information and systems risk where the lessor requires concise reports to be able to manage payments, arrears, asset values and terminations

- Concentration risks based on timing of equipment return, portfolio weightings, geographic location and equipment type which could affect equipment values due to excessive return numbers in specific quarters or locations.

- Legal and documentation present problems due to sloppy drafting, incomplete documentation and unforeseen outcomes

This is not an exhaustive list of risks faced by a leasing company but tries to provide an overview of the risk and how they can combine to reduce the cash flow gained, downgrade the asset security and reduce the final resale value in the lease.

Future of Leasing

Leasing is now more than fifty years old in modern form and the finance industry continues to evolve in response to changing market conditions.   The economic conditions following the demise of Lehman Brothers in 2008 or the pandemic in 2020 have brought a wave of austerity to Europe, which has posed problems for lessors in terms of credit and asset issues.

Companies that offer simple products such as finance leases do not offer huge increases in value and can only compete on the price of money.  Products need to offer solutions to different segments of markets and add services to reduce the overall cost of leasing in relation to purchasing.  Vendor programmes and specialist lessors continue to take a larger share of the marker as lessors seek to offer bundled solutions rather than simply offering a ‘money on money’ loan facility.

Accounting standards will continue to evolve as in IFRS 16 where the distinction between finance and operating leases has largely disappeared.  The International Accounting Standards Board continues to monitor leasing to ensure a fair disclosure of obligations.  The implementation of standards is discussed in more detail in a later chapter.

The tax benefits available to lessors continue to fall across Europe with the advent of ‘tax tourism’ based on corporate tax in Ireland on 12.5 per cent and alternative tax breaks in other European countries.  In response, the corporate tax rate has reduced to 19 per cent in the UK.  This means that the profits from leasing are under pressure and lessors must be more innovative in the facilities they offer.

Summary

This chapter describes the types of leases and the benefits for lessors and lessees together with the risks accepted by lessors.  Monetary losses on client non-payment or bankruptcy are potentially greater than the margin gained by writing the lease and therefore lessors need to assess the probability of default and other possible downsides.  This means that lessors must continue to innovate in the next decades in a challenging and changing market.

3 Financial Functions

Sheet: TVM_Calculator

This chapter demonstrates basic calculations in Excel models for financial mathematics which are needed for lease pricing, accounting, evaluation and settlement analysis.  The building blocks of single and multiple cash flows are analysed with the Excel functions in the file accompanying the book.

Time Value of Money

A single cash flow lease contract with regular rentals can be broken down into seven variables as detailed in chapter one:

- Number of periodic payments (N)

- Periodic interest rate (I)

- Present value or capital value (PV)

- Regular payment (PMT)

- Future value, balloon payment or residual value (FV)

- Number of regular payments per annum

- Payment toggle or switch (payments at the beginning (advance) or end of period (arrears))

ALF2_03_01 TVM

The annotated example above details a lease of 1,000 over three years quarterly in advance at a rate of ten per cent nominal and a zero-future value.  The workings at the bottom take the text inputs in cells C11 and C12 and find a match for them on the payment interval and advance/arrears lists.  An OFFSET lookup function is used to find the interval and toggle inputs for the financial functions.

$D$25: ' =MATCH(TVM_Calculator!E11 ,B25:B28, 0)

$D$26: ' =OFFSET(C24,D25,0)

$G$25: ' =MATCH(TVM_Calculator!E12 ,F25:F26, 0)

$G$26: ' =IF(G25=1,1,0)

The periodic payment is 95.11 and the code in workings finds an answer in cell E22.  The IF statement suppresses any value that is already known since the Excel functions follow the formula:

0 = PV + (1 + iS) PMT [(1-(1 + i)-N) / i] + FV (1 + i)-N

Where S = payment toggle denoting whether payments are made at the beginning or the end of each period.

$E$19: ' =IF(E5=0,NPER(C19,E8,E7,E9,G26),0)

$E$20: ' =IF(E6=0,RATE(E5,E8,E7,E9,G26)*100*(12/D26),0)

$E$21: ' =IF(E7=0,PV(C19,E5,E8,E9,G26),0)

$E$22: ' =IF(E8=0,PMT(C19,E5,E7,E9,G26),0)

$E$23: ' =IF(SUM(E19:E22)<>0 ,0,IF(E9=0 ,FV(C19,E5,E8,E7,G26),0))

The formulas at the top use the calculated payment to derive the number, interest rate, capital value and future value.  Provided that you know four of the values, even if the value is zero, the Excel function will always calculate the fifth variable.

NPER

12

RATE

10%

PV

(1,000.00)

PMT

95.11

FV

0.00

$H$5: =NPER(C19,E14,-ABS(E7),ABS(E9),G26)

$H$6: ' =RATE(E5,E14,-ABS(E7),ABS(E9),G26)*D26

$H$7: ' =PV(C19,E5,ABS(E14),ABS(E9),G26)

$H$8: ' =PMT(C19,E5,-ABS(E7),ABS(E9),G26)

$H$9: ' =FV(C19,E5,ABS(E14),-ABS(E7),G26)

You can use this calculator to solve any single cash flow problems, for example:

- A car rental based on an initial value 20,000 over 36 months payable monthly in advance at a rate of eight per cent based on an 8,000 final value as set out in the Case 1 scenario below.  You can access all the scenarios using Data, What-if, Scenarios.  The monthly payment is 435.97 but the budget available is only 425 per month.  To make the equation work, the future value must rise to 8,433.

ALF2_03_02 Case 1

ALF2_03_03 Future value

Multiple Cash Flows

Where there are irregular or multiple cash flows you need to use discounting functions in Excel.  The next sheet contains a grid of cash flows from an initial investment of 1,000 growing at a constant rate of 10 per cent. The number of periods is five and on expiry the salvage value gained is 100. The grid uses a simple IF statement to grow the cash flows up until the expiry period:

=IF(E12<=$C$9,SUM(D14:D15)*(1+$C$7/100),0)

The net cash flow sums the cash flows above. The discount factor is calculated as:

Cash flow / (1 + periodic interest rate) ^ N

N = number of the compounding periods

Therefore, period three is:

1 / (1 + 10%)^3 = 0.7513

The cash flow can then be multiplied by the factor and the net cash flows added to form the net present value (see figure). The cash flow today is not discounted and therefore multiplied by one.

ALF2_03_04 Net present value

The simpler method is to use the NPV function to reduce the amount of code and therefore the propensity for error (see Figure 03_05). You select the interest rate and the outstanding cash flows. The answer here is 1,198 and then you add the cash flow received today.  If you include all the cash flows, Excel assumes that the first cash flow is also to be discounted and will assume that all the subsequent cash flows occur one period later.

ALF2_03_05 NPV function

The net present value is greater than the initial investment so the internal rate of return must be higher than the discount rate of ten per cent. The internal rate is the iterative rate at which net present value is equal to zero.  The net present value will vary depending on the discount rate: as the discount rate increases, the net present value falls and vice versa.

The schedule contains a simple data table to show the effect of interest rate changes (see Figure 03_06).

ALF2_03_06 Data table

The data table is a built-in sensitivity function accessed through Data, Data Tools, What-if Analysis on the menus.  The single variable grid must be set out as above with variables across and the answer in the next row down on the left. You highlight the whole grid ($B$33:$I$34) and then enter the variable $C$10, the interest rate, in a Data Table dialog box. Excel calculates all possibilities and completes the results in cells $C$34:$I$34 showing the net present value at varying discount rates.

ALF2_03_07 Sensitivity

Internal Rate of Return

The alternative calculation is internal rate of return or yield when you know the net present value. This is the maximum percentage that could be afforded before the net present value becomes negative. Alternatively, this is the rate at which the net present value becomes zero.

It is possible to impute an internal rate by calculating two net present values at assumed rates and then working towards the solution using the formula:

IRR = Positive Rate + (Positive NPV / (Positive + Negative NPV) * Range of Rates

ALF2_03_08 IRR

The model in Figure 03_08 calculates a net present value at 10 and 20 per cent of 198.56 and minus 77.88.  The adjustment using the formula is 7.18 per cent and the adjustment is added to the lower rate. The workings for the adjustment are:

=($E$19/(ABS($E$19)+ABS($E$20)))*(($D$20-$D$19))

Excel has a built-in function for the internal rate of return, IRR, which is simpler than imputing alternative rates. In this case, the interest rate guess is left blank since the default rate is ten per cent.  This is simple cash flow with only one crossing from positive to negative cash flows and therefore there can be only one solution. Where there are multiple sets of positive and negative cash flows, there can be more than one potential result.

An alternative method uses a chart to plot the interest rates and the net present values (see Figure 3.10). The net present value is zero at a rate of 16.75 per cent. The data table shows the results at rates above and below the correct answer.

ALF2_03_09 IRR Function

ALF2_03_10 Sensitivity

The example in Figure 03_11 shows extreme cash flows which cross twice from positive to negative. This means that there are multiple solutions as shown by the sensitivity table.  The trend line crosses the axis twice, meaning that IRR results from these figures are unreliable with two possible answers.  It would be better to use a known risk-adjusted discount rate and reduce the cash flows to a net present value.  This approach would be advantageous if you wanted to rank or compare results from two sets of cash flows.  Another failing with the internal rate assumes that any cash receipts are re-invested at the IRR rate which may not be possible in times of falling interest rates and a sinking fund method might be needed to account for the funds received.

ALF2_03_11 Multiple IRR

NPV and IRR

Excel contains other more advanced functions for net present value and internal rate of return, which allow you to select uneven periods.  The standard functions assume that each period such as a month is the same length, which is rarely the case.  This means that the default is a 30-day month and a 360-day year.  Annual payments have leap years whereas monthly periods are punctuated with months of 28, 29 (leap years), 30 and 31 days.  The example in Figure 03_12 calculates the net present value and internal rate using the XNPV and XIRR functions.  Note that these functions are in the Analysis ToolPak and you must install this add-in within Excel options (see installation instructions).  If you do not, then you will see function errors on the schedule.

Dates are problematic in Excel since it is difficult to increment by one month or quarter correctly without missing leap years or short months.  Two Excel functions are useful: EDATE goes forward in multiple of whole months and EOMONTH which does the same but stops at the end of the designated month.  For example, cell C14 with a locked start date and a calculation for the number of months:

=EDATE($C$6,$C$7*C15)

The XNPV function requires the effective annual interest rate, the cash flows and the dates.  The first date is assumed to be the first cash flow even if it is zero.  The answers on the standard functions are 16.75 per cent and 198.46.  Answers using the day-to-day functions are normally lower than the standard functions due to the irregular number of days in each period.

ALF2_03_12 XNPV and XIRR

NPV Periodic Example

You must be careful with XNPV and XIRR since they use effective rather than nominal rates. The example in Figure 03_12 generates a three-year cash flow at 14 per cent with quarterly payments based on a 1,000 present value. This is a loan or rental of 99.98 per quarter.

ALF2_03_13 Periodic NPV

The IRR function calculates a yield of 14 per cent, the same as the input rate. The XIRR function yields 14.75 per cent which is the effective rate. The NOMINAL function reduces the yield to 14 per cent nominal.  Similarly, the XNPV function using the effective rate produces a net present value close to the present value of 1,000.  Note that you must include all the cash flows including the opening cash flow on the XNPV function in contrast to the NPV function where you only include the outstanding cash flows.  Blank periods must also be represented by zeros.

Modified Internal Rate of Return

The modified internal rate of return function attempts to overcome the disadvantages of using a simple internal rate.  This uses a separate finance and reinvestment rate to deal with multiple positive and negative cash flows. Using the same data as the Multiple IRR sheet with a finance rate of 10 per cent and a reinvestment rate of 5 per cent the answer is 4.03 per cent (see Figure 03_14). As with net present values, loans can be ranked based on this measure. For example, leveraged leases need to be measured using this more advanced internal rate function, since you are not sure that rentals received can be reinvested at the same rate as the initial lease.

ALF2_03_14 MIRR function

The full equation for the function is:

MIRR = [-NPV(rate, values[positive]) * (1 + rrate)N / NPV(frate, values[negative]) * (1 + frate)]1/(n-1) - 1

The table in Figure 03_15 shows how the MIRR changes with increasing finance and reinvestment rates. The highest rate is at the bottom right of the table whereas the lowest MIRR is at the top left-hand corner.

ALF2_03_15 Sensitivity to MIRR

Amortisation

Lessees are required to apportion lease rentals into capital and interest under certain accounting standards and show changing proportions of capital and interest.  The amortisation schedule starts by multiplying the outstanding capital by the periodic interest. The principal repayment in the period becomes the rental less the interest which is subtracted from the capital outstanding. In the  next period the interest charges reduce due to the lower capital outstanding and the capital repayment increases. At the end of all the periods, the cumulative capital repayment is equal to the initial capital and the interest equal to the total charges. This is a specimen calculation:

Cell E15 = IF(C15=0, 0, -F14 * ($C$7 / (12/$C$43)))

ALF2_03_16 Amortisation

The second amortisation example uses the Excel function PPMT to compute the  principal in each period (see Figure 03_17). The interest is therefore the rental less the principal paid which will decrease during the rental period. You could calculate the interest with an IPMT function, and this function takes the same arguments as PPMT. There is also a function called CUMPRINC which calculates the total principal paid over several periods. A further function called CUMIPMT will provide the interest between two periods. This example accumulates the principle paid.

Cell D16 =-IF(E16=0, 0, CUMPRINC($E$12, $E$5, -$E$7, $B$16, B16, $E$9))

Again, the PPMT column computes the total capital and the IPMT the total charges on the lease.  On expiry the balance outstanding is zero.

ALF2_03_17 Amortisation by function

Summary

This chapter explains more building blocks of financial mathematics in Excel in terms of single and multiple cash flows.  The results are present values, rates of return, interest rates and amortisation.  The next chapter sets out leasing examples for evaluation from a lessee and lessor standpoint.

4 Leasing Calculator

Sheet: TVM Calculator

This chapter develops an integrated calculator in Excel using the same basic examples which are combined with further sheets in the next chapters to explain:

- Lease pricing

- Lease accounting for lessees and lessors

- Lessor evaluation

- Client lease versus purchase analysis

- Asset management, exposure and settlements

There are several complications with calculating all the above on a single sheet and the first sheet seeks to provide a calculator for several different structures.  The other sheets in the model will use the data as required.  For example, to calculate rentals, the required entries are:

- Number of rentals in total

- Number of rentals on signing – UK leasing companies often quote rentals such as 6+30 meaning six rentals on signing followed by 30 rentals starting in the next period.  If there is a residual or final rental, there will be five periods of zero before the final rental is due.  The logic of the model needs to take this into account and a different approach is needed

- Advance or arrears denoting when in the period the rentals are paid.  With multiple rentals this is always in arrears

- Payment frequency – monthly, quarterly, semi-annual or annual

- Interest rate which is usually quoted as a nominal rate and must be broken down into a periodic rate for Excel functions

- Capital value

- Future value as a residual, final payment or a balloon rental

The examples used in this chapter are:

Excel Methodology

The first scenario example on the TVM sheet is a simple three-year quarterly lease with advance payments and a zero-residual value.  All the scenarios are saved in the application at Data, What-if, Scenario Manager.  The calculated rental is 95.11.  The payment interval and toggle are set up as a data validation list so that you can select values from a dropdown.

ALF2_04_01 Example 1

The model needs to decide on a payment interval and whether the payments are in advance or arrears and then calculate the input.  The value to be computed is always entered as zero.

To use a list validation, you need to match the value to a separate list to find its index number.  If the interval is monthly the MATCH function will find it as the first entry and an OFFSET or INDEX function will provide 12 as the number of payments per annum.  Thus, a 12 per cent nominal rate can be easily divided by 12 as Excel functions usually require the periodic interest rate.  There is a similar procedure for the payment toggle.  The workings in lines 23 to 28 calculate one result only which corresponds to the zero value in the workings.

If the payment is not sufficient to amortise the lease and the model has not computed an answer in the four cells immediately above, the future value function is invoked.

=IF(SUM(C23:C26)<>0, 0,IF(E10=0, FV(C20,E5,E9,E8,$C$40), 0))

ALF2_04_02 Rental workings

To make sure that the value is correct, there is a cash flow below the workings.  To simplify the code, there are masks or binary flags for the rentals and the future value.  In effect this breaks an IF function into two parts with the logic in one column and the result in another.  These control whether a specific period is calculated or not.

Column C displays the flag for rentals due.  The complication in the code here is advance and arrears rentals plus the number of rentals on signing.

Cell C69: =IF(B69<=E5-$C$51+1,1,0)

Similarly, there is only one period when a future value can be due.  This is the next period to the final anniversary of the lease.  If the payments are in arrears, then the final rental is due at the same time as the future value.

The actual net cash flow is in column H.  If you assume that each period is the same length, the IRR function will confirm that the rental calculation is correct.

Cell I66: =IRR(H68:H128)*$C$37

You can also plot the present values and future value to show a zero on commencement or zero on expiry.  If you keep future valuing each rental you will finish at zero with the final payment.  Similarly, if you present value the final payment by one period and use this is the future value for the next period, you can repeat the process until the initial period when the present value should be zero.

$I$68: =H68

$I$69: =H69-FV($C$20,1,0,I68)

$J$68: =PV($C$20,1,0,-J69)+H68

ALF2_04_03 Cash flow workings

The second scenario example includes a residual value of 150 which reduces the periodic rental to 84.50.  The future value shows up in period 13 after payment of all the rentals and is reflected in the cash flow.  The internal rate (IRR) is confirmed at 10 per cent in Figure 04_05.

ALF2_04_04 Example2

ALF2_04_05 Cash flow example 2

Functions and Methods

The next two examples have three monthly rentals on signing and this presents problems for the simple calculator.  There is nowhere on a normal financial calculator to enter the multiple rentals.  The capital value is effectively the amount less the three rentals due on signing, but you do not yet know the rental.  This is a circular argument which you could solve with Goal Seek or Solver, however there is a mathematical method using rental factors that will solve the payment directly.

The method is known as the step or $1 method and comprises four stages:

- Calculate the net outflow of everything you know about at the inception of the lease.  This includes costs, tax depreciation and any other known cash flows

- Calculate the final residual value or final payment (if applicable)

- Compute the present value of the net residual value at the required rate

- Present value any other cash flows you know about, for example costs

- Add all the present values together to form the PV factor (A)

- Compute the PV of the unknown periodic lease payments, letting $1 equal each payment

- With multiple rentals on signing and in advance, add the number in advance to the PV factor.  This is the Rental Factor (B)

You calculate the payment (PMT) by dividing the PV Factor (A) by the Rental Factor (B).  When you have done this, you need to check the periodic cash flows to ensure that you have the correct rental.  The example below comprises a three plus thirty-three monthly rentals structure at a rate of ten per cent.  You can use this grid to find the lease payment required.

ALF2_04_06 Example 3

The workings below show the calculation of the PV and rental factor.  The PV factor is simply the capital value as there are no other cash flows as in cell C45 below.

ALF2_04_07 Rental workings

The net stage is to present value 33 rentals of $1 at a periodic rate of 0.08333 per cent (10%/12) which totals 28.75.  When you add the three rentals you receive today the result is 31.75.  The final rental is 1,000 divided by 31.75.  Therefore, the rental pattern comprises three rentals of 31.50 followed by 33 rentals of 31.50 starting in the next month to return the rate of ten per cent.

ALF2_04_08 Rental factor

The cash flow confirms that this is the rental as the overall internal rate of return is ten per cent.  The net advance reduces to 904.50 and the future value is zero at period number 37.

ALF2_04_09 Multiple rents cash flow

When you add a residual value of 150 into the transaction, the final value must be present valued to today and added to the capital value to form a net PV factor.  This amounts to 888.74.  The calculation of the rental factor remains the same and the monthly rental is 27.99 as in Figure 04_10.  Notice that there is a gap of two periods between payment of the final rental and the timing of the residual value.  Figure 04_12 confirms that the internal rate is ten per cent.

ALF2_04_10 Multiple rents plus a final value

ALF2_04_11 Workings

ALF2_04_12 Cash flow

Worked Examples

You can use the calculator for a range of examples.  The next example starts with a 6+30 monthly structure at a rate of ten per cent and a 150 residual value.  It then manipulates the answers and asks what real rate of interest the client will pay since the residual value is the responsibility of the lessor.  This is sometimes known as the ‘street’ or ‘client’ rate.

ALF2_04_13 Initial example 6+30

The model derives the rental of 29.44 with a deposit of 176.66.  This structure provides a deposit of nearly 18 per cent to the lessor and improves the asset exposure against the value of the asset.  The revised structure will of course reduce the profit on the lease since the loan is not 1,000 but net of the deposit at 823.34 and the average balance outstanding is correspondingly reduced.

ALF2_04_14 Initial cash flow

Suppose the budget available is only 29.00 per month and therefore the choice for the lessor is to reduce the interest rate or increase the residual value.  If you reduce the rental and zero the interest rate, the model will calculate the resulting yield.  This is 8.79 per cent.

ALF2_04_15 Reduced interest rate

You may wish to increase the residual value rather than reducing the rate.  This method preserves the interest rate over the rental period, but could result in a loss on expiry.  This is more complicated since you can find a future value at the end of thirty periods (36-6), but this then must be future valued to the end of 36 periods.  The code is:

$C$28: ' =IF (SUM(C23:C26)<>0, 0, -FV( C20, E6, 0, FV(C20, E5-E6, ABS(E9), -ABS(E8)+(E6*E9), 0)))

This code finds the net advance after six rentals have been paid and then considers the rentals.  The outer FV function calculates the value for the correct final period.  The cash flow below shows the future value to be correct as the yield is still ten per cent.

ALF2_04_16 Increased future value

ALF2_04_17 Cash flow check

The final question is ‘what price is the client paying?’.  Since the final payment is a residual rather than a balloon payment, the client only pays the periodic rentals and then returns the equipment to the supplier or lessor.  The interest rate the client will pay is 4.54 per cent negative.  Again, the cash flow in figure 04_19 confirms the rate.

ALF2_04_18 Street rate

ALF2_04_19 Client cash flow

Summary

This chapter introduces a calculator for financial transactions plus a more complex method of calculating rental streams and analysing them.  The $1 or four step method is a flexible method of solving structures with multiple rentals on signing with and without final payments.  The next chapter starts a complete model that can be used for accounting, lessee and lessor evaluation and asset management.

5 Lease Pricing

Sheets: Lease_Inputs, WACC Cashflow and Annual Cashflow

Previous chapters have demonstrated the use of Excel functions to build calculators to analyse interest rates and cash flows.  The next model uses these building blocks to generate the pricing, accounting and evaluation for the leases.

Theory

For the lessor, the primary goal is to receive the total invested capital in full plus a return on the investment.  The principal could include commissions, closing fees, security deposits, legal fees, a charge for overheads and the cash effect of corporate taxation.  These costs and benefits need somehow to be factored into the model to produce an overall rate of return.

So far, the models have computed an interest rate with or without a residual value and introduced the idea of a ‘street’ rate to describe the price actually paid by the lessee for the funding.  There is a wide range of rates that a lessor could charge given trading circumstances as below.  The market rate for similar transactions could be above or below the threshold market rate.  If the market rate is below the lessor’s minimum rate, then the transaction would be unprofitable.  The problem for the lessor is to price the leases correctly to ensure that the all the costs, provisions and potential bad debts are included.  Similarly, the lessor wants to charge as high a price as possible without making itself too expensive within the market.

ALF2_05_01 Hurdle rates

There is also a trade-off between functions in a leasing company.  The hurdle rate is the minimum that the lessor wishes to charge for a lease with a deposit, period, payment interval and final value.  The market rate is the spread of rates available in the market.  Credit, risk and legal departments may desire a short period to reduce the potential asset exposure whereas marketing may prefer longer periods and smaller deposits to make the rental rates and facility proposals easier to sell.  As you lend more money for longer the profitability of a lease tends to increase however the lease becomes riskier as the capital outstanding is generally higher when compared to asset values.  Therefore, there is always some discussion on the correct structure and pricing for a specific client.

ALF2_05_02 Trade-off

Clients on the other hand may review lease proposals in terms of the total cash outlay, total charges, deposit percentage or the lease rate expressed as a rate per thousand borrowed.  The client could also calculate the implicit or interest rate cost as a flat, nominal or effective rate to understand the price being charged against the cost of alternatives.

There may also be financial ‘hooks’ in the proposal which could end up costing extra rentals or charges.  Examples not shown on the face of the document include:

- Default interest rate on any overdue payments

- Termination discount rate for early termination which is usually some form of penalty rate

- Voluntary termination clause which again could include penalties

- Secondary (peppercorn) rentals due at the end of the initial rental period

- Expiry notice period which if not given in full can require further rentals to be paid

- Ownership on expiry and whether retained by the lessor

- Flexibility to change and upgrade the equipment

- Documentation for example, a restrictive tax clause

- Accounting classification under the existing or proposed IFRS standard

- Other restrictions such as the ability to temporarily export the equipment

So far, the models have calculated a gross yield based merely on the interest rate.  This method ignores several relevant cash flows such as:

- Initial direct costs because of writing the lease

- Sales general and administration costs as a cost of maintaining the lease in a portfolio

- Interest expense on debt and dividends paid by the lessor

- Tax benefits such as tax relief on interest or the tax shield available on the equipment

- Origination fees and benefits paid to suppliers and intermediaries

- Interim and contingent rents based on usage

- Allowance for doubtful and bad debts

To calculate the net yield, you need to model several stages:

- Calculate a cost of funding available to the lessor based on the proportion on debt, equity and other sources used to fund the transaction.  This is known as the cost of capital, weighted average cost of capital (WACC) or the merged cost of funds.  The cost of funding depends on the cost of each source and its relative weight and acts as the lessor hurdle rate.  You cannot simply add a percentage to the cost of debt funding as this will not include an appropriate return for each capital provider

- Plot all the known cash flows

- Use the step method in the last chapter to compute the rental at the merged cost of funds

- Generate the full cash flow and check that the internal rate of return is equal to the merged cost of funds

- Also check the gross rate of the lease and street rates paid by the lessee

This process is like project evaluation where you plot the free cash flow available to the project and then evaluate the cash flows using a weighted cost of capital.  You may be interested in other outputs such as loan life cover or debt service cover ratios, but you still need to generate a sufficient return on the lease cash flow.

Cost of Capital

The method for calculating the merged discount rate (weighted average cost of capital / WACC) is:

- Isolate the different sources of capital and amounts

- Calculate the cost of the individual sources of capital

- Compute the weights or percentages based on market (not book) values. The book value of debt is difficult to determine unless publicly traded and therefore the book value as a proxy to market value is normally used

- Factor the debt by (1 – marginal tax rate) if the company pays mainstream corporate tax

- Multiply the relevant equity and after-tax debt cost by their percentage weighting

- Add the constituents to derive the weighted average cost of capital

The formula for the discount rate (WACC) is:

WACC = D / (D + E) * Rd * (1 – Tax) + E / (D + E) * Re

Rd = market value cost of debt

Re = cost of equity

Tax = marginal rate of tax

A company should earn a return on its equity of at least as much as its shareholders could obtain on alternative investments of equivalent risk (RE).  There are several theories beyond the scope of this book for calculating the cost of equity.  Examples of theories are the Capital Asset Pricing Model (CAPM), Dividend Discount model, Arbitrage Pricing Model (APM) and options methods.  This model uses an input equity cost of ten per cent.

Returns

The graphics below show the inputs for an example lease.  This is a five-year annual full pay out lease with rentals in advance based on a capital value of 100,000.  The key inputs for the cost of capital and rental calculation are:

- Required return on equity: 10 per cent

- Cost of debt: 7.5 per cent

- Debt / equity ratio: 90 per cent

- Tax rate: 25 per cent

- Tax depreciation method:  equal percentages

Costs in each of the years must be included in the rental cost.  These could be ancillary costs, security deposits, inspection costs or any other cash flows.

The equal tax depreciation percentages and 25 per cent tax rate are used for simplicity in this example.

ALF2_05_03 Lease inputs

ALF2_05_04 Costs

The cost of capital is:

WACC = D / (D + E) * Rd * (1 – Tax) + E / (D + E) * Re

7.5% * (1-Tax) * 90% + 10% * (1-90%)

The result is 6.6025 per cent.  The cost of capital will increase as the proportion of equity rises as a percentage of all capital as shown in Figure 05_05.  In simple terms, the higher the gearing (leverage), the lower the cost of capital and the lower the rental required to meet the threshold return figure.

ALF2_05_05 Cost of capital

For simplicity, the tax depreciation is equal as different countries have different systems, based on:

- Equal percentages or straight line.

- Declining balance as in the UK where the current rate is 18 per cent per annum based on the balance outstanding.

- Declining balance followed by straight line to remove any remaining balance.  This similar the USA where the Modified Accelerated Cost Recovery System (MACRS) defines the asset type and then the depreciation percentages.  Examples of the depreciation percentages are in the workings at the bottom.

Excel has functions for all these methods: straight line (SLN), declining balance (DB and DDB) and modified declining balance (VDB).

ALF2_05_06 Depreciation table

There are other lessor structuring variables that are not included in the basic inputs as they are not firm cash flows.  As a rule, the pricing should only include cash flows that are part of the minimum rentals payable.

- Penalties for late payment of rentals or other fees

- Renewal rentals such as market, peppercorn (nominal) or annual rentals

- Tax benefits passed on or retained by lessor and not included in the pricing

- Inclusion of insurance, maintenance which could also be a source of revenue to the lessor

- Contingent rentals dependent on usage or some other threshold

- Flexible lease options such upgrades, equipment swaps and additions which are discussed in a later chapter

Residuals or salvage values available to the lessor need to be included as you must calculate a rental based on the whole equipment price.  On inception, the residual value represents the forecast value for the equipment on expiry.

The $1 or step method described in the last chapter is needed to compute the annual rental due.  The factors include:

- Number of lease rental payments

- Number of periodic lease payments in advance

- Yield or merged cost of funds required by lessor

- Initial direct costs

- Invoiced equipment cost

- Any closing or brokerage fee

- Refundable security deposit (if applicable)

- Residual value or balloon rental

For completeness as a reminder, the Step Method comprises:

- Calculate the net outflow of costs at the inception of the lease = PV factor

- Calculate the final residual value (if applicable)

- Compute the present value of the net residual value

- Add together the present value of all the know cash flows (A)

- Compute the PV of the unknown periodic lease payments, letting $1 equal each payment

- With multiple rentals on signing and in advance, add the number in advance to the factor

- Add the number received on signing plus the present value to form the rental factor (B)

- Calculate the periodic payment as PV Factor / Rental Factor = A / B

The first section present values the known cash flows on an after-tax basis.  The cash value of the tax depreciation is the present value multiplied by the tax rate.  The net costs are the present value of the costs multiplied by (1 – tax).  These added together total 79,800.

Then you have to present value four ‘ones’ at an annual rate of 6.6025%.  This is 4.4602, but then you must multiply it by (1 – tax) to ensure that tax is considered on both sides of the equation.  The present value of the costs is after tax so the rental factor must be presented post tax as well.

The calculated rental is a deposit of 23,856 followed by four annual rentals of 23,856.  The rental now considers the costs, frequency of payments, tax benefits and the effects of the structuring.

ALF2_05_07 Rental calculation

The actual rate paid by the client is 9.6809 per cent and this may or may not be a saleable rate in the market, but this is the minimum the lessor must charge in to reward the two providers of capital.  The ninety per cent of debt needs a rate of 7.5 per cent before tax and equity demands ten per cent for its ten per cent input.  Anything less will impact a capital provider and since loan interest payment are not usually optional, it is likely that equity would have to bear a reduced return through lower dividends.

ALF2_05_08 Rental calculation

Cash Flows

To make sure that the rental is correct, you must plot all the cash flows for the leases and ensure that the internal rate of return is equal to the weighted cost of capital.  The annual cash flow sheet uses these cash flows:

- Initial capital outlay

- Annual rentals and residual value

- Tax depreciation (equal percentages)

- Ancillary costs

Taxable income is multiplied by the tax rate and the depreciation added back as a non-cash flow.  This non-cash item is only included at this level to calculate the tax correctly.  The result is the after-tax cash flow (ROA cash flow) arising directly from the lease.  The internal rate of return equals 6.6025 per cent confirming that the rentals are correct.

The schedule below also shows the cumulative cash flow, and you will see that it is year four before the lessor become cash positive.  Despite the high deposit, there is a significant exposure which has implications for credit analysis.  The lessor must be sure that the market value of the asset will perform well against the lease capital outstanding.

ALF2_05_09 ROA cash flow

ALF2_05_10 Cumulative cash flows

There is also an alternative cash flow schedule which is more useful for non-annual cash flows and will be used for the other examples.  This sets out the cash flows across the page to demonstrate the build-up of the pre-tax margin and then the tax charge as the addition of the rental income, costs and capital relief.  As in previous examples, cash out is included as a negative item in brackets.

ALF2_05_11 Periodic cash flow

Targeting Returns

You could also use the model for targeting rentals or returns.  If you wanted to find the change in the return on equity for a final rate of 9.5 per cent, you could use Goal Seek to target the result. (Data, What-if Analysis, Goal Seek).  This method only works since the model is built to a design standard where you are confident that that the control inputs lead directly to calculations and outputs with no mixed formulas or hard coded cells in the calculation area.

The Goal Seek target must be a formula cell and the changing cell must be an input.  You must type in the value for the target unless you program it with macros.  When you press ‘OK’, Excel will iterate an answer by working backwards from the desired output value.

If Excel does not find an accurate enough result, check Options, Formulas, Calculation Options.  In the same section as Iteration, then is an accuracy number.  Since you are dealing with percentages, you lose two decimal places so amend the Maximum Change to 0.000001.

ALF2_05_12 Goal Seek inputs

The graphic below shows that the rental reduces to 23,784.14 and the cost of capital to 5.92500 per cent, but the overall rate to the client is nine per cent.  When you check the cash flow sheet, you will see that the internal rate of return is 9.5 per cent.  Again, models should always ‘self-check’ the results and trigger an error message if anything is incorrect.  Using this method, you can choose which variable you flex to increase or decrease the rental.

ALF2_05_13 Goal Seek result

ALF2_05_14 Cash flow at a gross rate of 9.5 per cent

You can also check the result using the simple TVM calculator on the first sheet since we are looking at the client side of the equation.  The client receives a document with five rentals of 23,784.15, which he can compare against other offerings.  The costs and the tax position are the business of the lessor and not known by the potential client.  All he is interested in is a ‘street’ rate of 9.5 per cent.

ALF2_05_15 TVM Calculator

Summary

This chapter describes a further part of the equation illustrating the number of variables that are needed by a lessor to calculate a rental price.  The model computes the net yield available to the lessor and compares it to the ‘street’ rate payable by the client and shows how you can target specific end results by flexing the inputs.  The cash flows directly associated with the leasing structure confirm the yield and ensure that the model checks itself.

6 Lease Accounting

Sheet: Lease_Accounting

The first chapter discussed the different types of leases from a marketing view using terms such as operating or finance (capital) lease.  This is satisfactory in general terms, but you need models to analyse leases, decide the classification and therefore the entries in the annual reports.  This chapter outlines methods for deciding on the accounting treatment.  The next chapter introduces a model for lease accounting.  The classification has been subject to change over the last few years, so the methodology is discussed from a historic and existing standpoint.

For UK accounting, a lease contract is defined as a contract between lessor and lessee for hire of a specified asset.  The lessor retains ownership and allows lessee the right to use asset in return for rentals.  This makes a distinction between a lease and a service contract where the latter does not automatically the lessee give the right to use a specific asset.  A lease usually provides the same asset for the lease term unless swapped for maintenance purposes as in the case of certain computer equipment.

Accounting Standards

Prior to 1984 in the UK, the key advantage of leasing was that all leases were off-balance sheet.  This meant that rentals were merely expensed through the income statement and no liability was present on the balance sheet.  This masked the true extent of the lessee’s funding liabilities, especially if they wrote leases with a variety of different lessors.  This was an imbalance since bank loans had to be disclosed in annual accounts which increased the gearing or leverage ratios.

There were a number of high-profile bankruptcies in the UK at that time following the recession of the early 1980’s and the accounting profession finally adopted the idea that the liabilities should be shown in the annual accounts.

SSAP 21

The first standard was enacted in the US by the US Federal Accounting Standard Board and is known as FASB 13 or SFAS 13.  This was an international benchmark that other countries followed as their basic framework.  The first accounting standard in the UK came in to force in 1984 and was known as ‘SSAP 21 – Accounting for leases and hire purchase contracts’.  This was the first attempt to show the economic reality that a lease is a form of loan secured on the asset.  Showing leases on accounts has the effect of:

- Increasing the level of borrowing shown in the accounts.

- Increasing the financial leverage to include the extra borrowings

- Reducing the debt service and cover ratios

- Changing the margin ratios for gross and net margin.

Under the US standard SFAS 13 and UK accounting standard SSAP 21 leases are divided into operating and finance leases.  Finance (capital) leases are on balance sheet and operating leases are merely expensed through the income statement.  SSAP 21 introduced a present value test to decide on the type of lease.  Here are two quotations from the standard that set out the objectives.

Transfers ‘substantially all the risks and rewards of ownership of an asset to a lessee’ (SSAP 21 Paragraph 15)

SSAP 21 test: ‘presumed that a transfer of risks occurs if at the inception of the lease the present value of the minimum lease payments including any initial payment, amounts to substantially all (normally 90 per cent or more) of the fair value of the leased asset’ (SSAP 21 Paragraph 15).

It was assumed that economic ownership had passed if the present value of minimum lease payments was greater than 90 per cent of fair value of asset on inception.

This means that there can be no significant residual and the lessor is dependent on the rental stream for its margin and return.  The idea is that the lessor finances the asset in the case of a finance lease whereas he is a trader in the assets under an operating lease.

Each party decided about the lease type independently so both could think it an operating or a finance lease.  Alternatively, the lessor could classify the lease as operating and the lessee as finance or vice versa.  Therefore, there was a certain amount of scope available with the classification which also depended on the competence of the lessee and its auditors.

The example below shows an initial pricing using a lessor rate of ten per cent and a residual value of fifteen per cent.  The future value could be an uncovered residual or a guarantee from a third party such as a manufacturer under a sales-aid programme.

ALF2_06_01 Initial rental with final value

The lessee will only pay twelve rentals of 84.50 and then have options to return the equipment or extend the rental and has no knowledge of the size of the residual assumed by the lessor.  He therefore present values the rental at his marginal rate of borrowing.  In this case this is eleven per cent and the derived present value is 87 per cent.  This simple measure could classify the contract as an operating lease.

ALF2_06_02 Lessee present value without residual value

Under SSAP 21 the lessee capitalised a finance lease and showed equipment as an asset and a corresponding liability to the lessor.  The liability was split between current portion long term debt and long-term debt on the balance sheet.  The lessee had ‘economic ownership’ and the rules obliged the disclosure of the borrowings.  Secondly the rentals were allowable as a trading expense.

In the UK, the lessee may not claim capital allowances in respect of most leased assets.  The UK tax treatment follows legal form rather than economics and in most cases the lessor entitled to tax depreciation.  The rules have changed and in the future lessees will be entitled to the tax depreciation on long term leases for periods greater than seven years.

The initial standard set a precedent to show more liabilities, but also allowed several loopholes and enabled lessors to structure leases to look like operating leases and therefore account for them off balance sheet.  For example, the UK Inland Revenue were concerned about structured and short-term leases e.g., leases with large deposit rents or balloon rentals in the last year.  A twelve-month finance lease could provide one hundred per cent of the tax relief based on relief on rentals over a shorter period than the normal depreciation period.  Other examples of difficulties included:

- Minimum period could exclude or include run-on rentals or options depending on the exact wording of the contract

- Interest rates are difficult since the lessee may not know the lessor rate and must assume a marginal rate for an equivalent loan.  There is some room here for negotiation

- The capital value may be subject to an undisclosed or ‘blind discount’.  This could take the form of an overriding volume reduction or a marketing incentive that is not obvious in the figures presented to the lessee

Since there was some abuse of the initial standard SSAP 21, the UK Inland Revenue sought to clarify the position with SP3/91 which was issued as a revenue statement of practice.  This endorsed a case called Threlfall v Jones to ensure that rentals were spread over the normal economic life irrespective of when paid.  The accounting depreciation was set as an indicator of amounts to be claimed.  UK accounting tries to look at the substance of transaction rather than its strict legal form, but nonetheless there is always some room for interpretation or discussion on the accounting principles to be applied.

The above defined finance leases, but there was no specific definition of an operating lease.  Where no transfer takes place, a lease was called as an operating lease.  In other words, an operating lease is simply ‘a lease other than a finance lease’.  In this case, the lessee expensed the rentals through the income statement and did not need to capitalise the asset on the balance sheet.  There were however rules for specifying the rentals payable within one year, two to five years and greater than five years.  You could assume an interest rate with this information and try to adjust the accounts to put these borrowings back on balance sheet.

The commercial risks undertaken by lessee and lessor vary on operating and finance leases. Extreme cases are easy to classify with no residual the lessor accepts the credit risk of lessee.  A high residual value of 40 per cent may be treated as an operating lease.  It is more difficult for cases around the 90 per cent threshold.

You discounted the minimum lease payments and compared them to the value of the leased asset.   The lessee’s treatment may be different to that of lessor e.g., different discount rate.  If the present value was greater than 90 per cent, the lease was presumed to be a finance lease (current test).  The sales test is perhaps different: ‘did the lessor transfer substantially all the risks and rewards of ownership?’  The simple SSAP 21 test is rather crude and was never intended to be a strict mathematical test.  SSAP 21 provided for overriding the test where there was evidence that the substance of the lease was different to the strict mathematical basis, however it was a useful yardstick, but not a conclusive test in determining where the risks and rewards lie.

You needed to review true substance of the lease and a further UK standard called FRS 5 reported on the substance of a transaction or string of transactions.  Where FRS 5 overlapped with another standard, standard with the more specific provisions of FRS 5 applied.  This forced auditors to apply the substance and not just the legal form.  Nevertheless, there were still problem areas such break clauses, option clauses, exchange clauses, return conditions, inflated list prices, incorrectly reported interest rates, residual guarantees, manipulation of lease periods and other ways of structuring leases to gain a desired accounting classification.

The commercial impact was clear, since the objective was to ensure that SSAP 21 applied in the spirit intended and not applied mechanically.

‘All the aspects and implications should be identified and greater weight given to those more likely to have a commercial effect in practice’.

‘It will be important to consider the position of all parties to it, including their apparent expectations and motives for agreeing to its various terms’.

‘A series of transactions that achieves or is designed to achieve an overall commercial effect should be viewed as a whole’.

IAS 17

Until the introduction of IFRS 16, IAS 17 and EU 2005 Regulations were obligatory for UK public and international companies and were part of an on-going process to harmonise accounting standards in the rest of the world with the US standards.  IAS sought to deal with different methodologies and provide a way of comparing companies across borders so that investors could make rational decisions about asset values and liabilities without endless adjustments to the figures.

Under IAS 17, a lease is “an agreement whereby the lessor conveys to the lessee in return for a payment or series of payments the right to use an asset for an agreed period of time”.

IAS 17 did not provide for a ninety per cent test but still classified a finance lease as one that ‘transfers substantially all the risks to lessee’.  This was the transfer of economic ownership and majored on substance rather than form.  Instead of a test, the standard provides for indicators of a finance lease:

- Transfer of ownership at the end of the lease.  This was allowable on most of continental Europe and would be classed as a hire or lease purchase in the UK

- The term was for most of the economic life of the asset

- Lease payments amounted to approximately the fair value of the asset plus charges

- Bargain purchase options were available on expiry.  A bargain purchase is often expressed as “you would be a fool not to exercise it”

- Cancellation costs were accepted by the lessee as a penalty.  The lessee could not simply give notice and return the equipment as you might with a simple rental contract

- Residual value risks were accepted by the lessee to reduce the risk to the lessor.  This could take the form of a guarantee or a remarketing agreement with penalties for not selling at a minimum figure

- Peppercorn or bargain renewal rentals were available to the lessee on expiry rather than a market rental.  This would confirm that the lessor had received back the capital sum over the rental period

IAS 17 was adopted by UK listed companies and non-listed companies could use IFRS or UK GAAP. Under IAS 17, a finance lease was capitalised on the balance sheet and showed an obligation to pay future rentals.  At inception, the lease was recorded as the present value of the lease payments.  The annual charge comprised the amortised charges plus the normal accounting depreciation.  This gives rise to a timing difference due to the imbalance with the cash paid.

You depreciate the asset over the shorter of lease term and useful economic life.  The useful economic life is consistent with other similar owned assets.  The lease term is defined as the minimum rental term plus any further periods for which lessee has the option to continue (and it is reasonably certain he will exercise the option).  The inception date is the earlier of the date the asset is brought into use and the date when rentals commence.  There is perhaps a difficulty with construction projects; for example, debt during a construction phase settled by a sale and leaseback at completion but the same principle is followed.

For lessors, finance leases were recorded as receivables and income recognised based on a constant periodic rate of return.  The allocation of finance income by lessors was based on the pattern reflecting a constant period rate of return on either:

- Lessor's net investment outstanding in respect of the finance lease or

- Lessor's net cash investment outstanding in respect of the finance lease

Chapter eight contains worked examples of lessor evaluation.

SFAS 13

The original US standard, FASB 13 or SFAS 13 was passed in 1976 and is used for comparison internationally.  This was the model for UK SSAP 21 and conceptually uses the same principles.  In US terminology a capital lease is a finance lease.

The standard lays out requirements for a capital lease.  If any one of the four criteria is met the lease will be classed as a capital lease ( on balance sheet).  The decision on the status is made at the inception of the lease and is irrevocable notwithstanding future changes in economic circumstances, for example a material residual devaluation or asset impairment.  The classification is considered by both the lessor and lessee independently.

There are four basic rules for classification:

1. Automatic ownership transfer (UK – not a credit sale or conditional sale agreement)

2. Bargain purchase option (UK – not a lease or hire purchase contract)

3. Lease term represents 75 per cent or more of the asset's economic life (UK SSAP 21 does not contain a similar test)

4. Present value or more of the minimum lease payments represent 90 per cent of the asset's fair market value reduced by any lessor retained investment tax credit (is like UK present value test)

There are exceptions for classifying leases with specific circumstances. For example, the above does not apply if asset is in the last 25 per cent of its economic life.  You could classify leases differently under SFAS 13 and SSAP 21 due to the economic life or other elements.

The capital amount is the lower of present value of minimum lease payments and fair market value of asset, however the lessee may not know the lessor’s rate.  If not known, the lessee should use its incremental borrowing rate.  The discount rate is:

- Lessor: implicit rate in the lease

- Lessee: lower of lessor implicit rate in the lease, if known or an equivalent rate expressed as the incremental, pre-tax, coterminous, secured, instalment, borrowing rate.  This means the rate for an equivalent loan and payment profile

The lease term comprises the fixed non-cancellable term of the lease plus:

- All periods covered by bargain renewal options

- All periods covered by high failure to renew charges

- All periods covered by a lessee guarantee of lessor debt related to the leased property

- All periods covered by a lessee loan to the lessor

- All periods covered by ordinary renewal options preceding a bargain purchase option

- All periods representing renewals or extensions at the lessor's option

The asset’s economic life is the life of the asset in the hands of multiple users with normal repairs and maintenance.  This could include primary users followed by secondary user such as education or export markets.

The minimum lessee cash flows are:

- Basic periodic rentals payable during the non-cancellable lease term

- Bargain purchase options on expiry

- Purchase option puts (whether bargain or not)

- Lessee guaranteed residual equivalent to a balloon rental

- Non-renewal penalties due at the end of the lease

- Non-refundable fees

The standard excludes cash flows such as ancillary costs of execution, contingent rentals and refundable fees since these are sunk costs.

The minimum lessor cash flows consist of:

- Basic rentals during the non-cancellable lease term

- Bargain purchase options on expiry

- Purchase option puts (whether bargain or not)

- Lessee guaranteed residual

- Non-renewal penalties due on expiry

- Non-refundable fees

- Third-party residual guarantees (excluded by lessee as a criterion but included here as a known cash flow.  This could also be a buyback or a manufacturer guarantee)

Again, ancillary costs, contingent rentals and refundable fees are excluded, but buybacks and other firm cash flows are included where the lessor possesses an exit route.

There are two additional criteria:

1. Collectability of the minimum lease payments is reasonably predictable and normal estimates of un-collectability will not rule out a capital lease

2. No important uncertainties surround the amount of non-reimbursable costs yet to be incurred by the lessor under the lease

An operating lease is one that meets all the tests as a ‘true’ operating lease and the rentals are merely expensed through the income statement.  There is no disclosure on the balance sheet but more detail on the operating cash flows is required.  Rentals due must be noted for each of the next five years rather than batched as two to five.

SFAS 13 has now been amended by ASC 842 (ASU 2018-11) and is effective for annual periods beginning after December 15, 2019, and for interim periods within those years beginning after December 15, 2020.  The most main change is the capitalization of operating leases, which is required under both FASB’s ASC 842 and the IASB standard, IFRS 16. However, the FASB and IASB split on how to classify leases to be capitalized on the balance sheet, with the FASB allowing a dual classification model of operating and finance leases, while the IASB declared that all on balance sheet leases will be accounted for as finance leases.

Finance lease accounting means that an asset and a liability are recorded at the present value of the lease payments on the balance sheet, with certain modifications for other elements such as prepayments and initial direct costs. On the income statement, the lease costs are recorded using a straight-line amortization expense plus a declining interest expense.

Under the FASB’s operating lease accounting standard, operating leases are capitalized like finance leases (previously called capital leases under ASC 840), however, the profit and loss (P&L) expense is recorded as a straight-line average expense.

The FASB and the IFRS allows a short-term lease (12 months or less) exemption from lease capitalization. The IASB allows an exemption from capitalization for leases less than or equal to $5,000 in value.

New Standards – IFRS 16 and ASC 842

In 1999, Sir Bryan Carsberg (Secretary General of the IASC) decided to set up a body to decide uniform global accounting practice.  The resulted from ‘Accounting for Leases: A New Approach – Recognition by Lessees of Assets and Liabilities arising under Lease Contracts’ (1996) by Warren McGregor, which discussed moves to capitalise all leases and get rid of the distinction between finance and operating leases.

In 1999, an Accounting Standards Board Discussion Paper, ‘Leases – Implementation of a New Approach’ was published by the G4 + 1 comprising Australia, Canada, IASC, New Zealand, UK and USA.  This has been discussed in the interim and various proposals put forward culminating in an Exposure Draft in 2010.  This involved a change in conceptual framework and definitions and a re-thinking the current accounting model.  The leased asset would either be capitalized by the lessee or treated as off-balance sheet (all or nothing).

Given the companies have significant lessee obligations are off-balance sheet; these obligations were viewed as a distortion and a major reporting problem.  The SEC estimated the total footnoted lease obligations at $1.25 trillion.

The original exposure draft proposed two methods, depending on whether lessor retained significant exposure to risks or benefits related to a leased asset.  Where the lessor retained the risks and benefits, a performance obligation approach was proposed.  With no retention, a de-recognition approach was put forward.  This caused a certain amount of confusion in the industry since the two-model approach was inconsistent with lessee accounting and contrary to project’s objective of single model.  The two models have subsequently been replaced by single ‘right of use’ model.

IFRS 16 leases introduce significant changes in accounting requirements for lease accounting, primarily for lessees. The new standard began on 1st January 2019 and replaces the existing framework of standards and interpretations on leases:

- IAS 17 Leases (IAS 17)

- IFRIC 4 Determining whether an arrangement or rental contract contains a lease (IFRIC 4)

- IC 15 Operating Leases – Incentives (SIC 15)

- SIC 27 Evaluating the Substance of Transactions Involving the Legal Form of a Lease (SIC 27)

Under IFRS 16, the definition of a lease is:

‘A contract, or part of a contract, that conveys the right to use an asset (the underlying asset) for a period of time* in exchange for consideration.’

To distinguish between a lease and a service contract, the answers to these questions should be affirmative:

- Is there an identified asset that the customer has the right to use?

- Does the lessee obtain substantially all the economic benefits?

- Does the lessee have the right to direct use of the asset?

IFRS 16 introduces an estimated US$2.8 trillion of lease commitments to global balance sheets, but does not include all leases as organisations are not required to report:

- Short term leases that span 12 months or less

- Leases that terminate within 12 months of implementation

- Small ticket asset leases

In the US, Accounting Standard Codification (ASC) 842, requires most leases to be recognized on the balance sheet and enhanced disclosures mandatory compared with SFAS 13.  Leases are accounted for based on a ‘right-of-use model’.  On the commencement date, a lessee has a financial obligation to make lease payments to the lessor for its right to use the underlying asset during the lease term.  ASC 842 requires lessees to classify most leases as either finance or operating leases.  The main differences to IFRS 16 include the lack of an exemption for low value assets and lessee classification where ASC 842 retains a distinction between operating and finance leases.

Under ASC 842, lessors classify all leases as sales-type, direct financing or operating leases, whereby IFRS 16 does not distinguish between types of leases.

Lessees

All leases, with optional exception of short-term leases, are recognised in the statement of financial position as a ‘right of use’ asset together with a lease liability.  Like current finance or capital leases; lessees recognise the asset and liability (debt) of PV of lease payments.  The asset is subsequently accounted for based on the cost or revaluation model in IAS 16 Property, Plant and Equipment (IAS 16).  Assets are amortised based on the interest rate or estimated use over term giving rise to an interest expense and a depreciation charge.  The lessee calculates a finance charge expensed according to the outstanding liability to maintain a consistent rate of interest amortised.

Short-term leases continue to be accounted for under existing operating lease accounting, (as a rental expense).  Contingent rentals cause more problems since lessees must account for contingent rentals linked to a rate or index.  There are practical difficulties for the lessee in estimating the future rentals and a regular reconciliation is required.

For lessees with large operating lease commitments, the impact of changes is significant.  In particular, real estate leases should be included in liabilities in accordance with the cost or revaluation model in IAS 16 Property, Plant and Equipment (IAS 16), so companies with leased property are especially affected by a large increase in their reported borrowings.

Existing operating leases need to entered in the accounts and this increases current gearing.  There is therefore an issue with breaking existing debt covenants by reporting extra borrowings.  Covenants need to be reviewed with conversion to the new standard and large debt increases could adversely affect the lessee’s ability to get new credit.  For banks, bringing all leased retail branches on balance sheet may also impact Basel capital adequacy requirements depending, in part, on whether they are treated as tangible or intangible assets.

Lessors

Lessor operating leases are accounted using the ‘right of use’ model. The lessor holds the underlying asset on its balance sheet and depreciates on a straight-line basis or amortised basis to the residual value.

In a finance lease, the lessor derecognises the asset and records the right to receive lease receivables on its balance sheet.  This remains substantially unchanged from IAS 17.  Receivables are discounted using the implicit rate in the lease (IRR) and the lessor recognises net the interest income on the income statement.  The interest component of payments is recognised as income at a constant rate according to outstanding balance.  This method is considered to generate a more appropriate income recognition and reflects the lessor’s funding interest costs.

Short-term leases are excluded from the scope of the above model.  Lessors will continue to apply existing operating lease accounting by recognising and depreciating the leased asset.  Similarly, the lessor will recognise lease income over lease term.

IFRS 16 started as a joint project between the International Accounting Standards Board (IASB) and its US counterpart, the Financial Accounting Standards Board (FASB).  Subsequently, the Boards did not agree on some elements and, ultimately, the FASB’s standard differs from the IASB’s in that the FASB’s standard retains distinct categories of leases for lessees with diverse accounting requirements.

Summary

The development of accounting standards starts from no disclosure to attempts to distinguish between firm commitments and more short-term hire of equipment.  The leasing market has become more sophisticated and operating leases constitute similar commitments to finance leases.  The current standards seek to capitalise all leases and show all liabilities to better reflect financial commitments.  Since the requirements of the standards may appear confusing, the next chapter shows some examples of lessee accounting on the old and new bases.

7 Lessee Accounting

Sheet: Lease_Accts

The previous chapter details the differences between the historic and IFRS 16 standards from a theoretical point of view.  This section introduces some worked examples using inputs and scenarios on the ‘Lease_Inputs’ sheet.  Each of the first three examples comprise a 36-month transaction written at an interest rate of ten per cent with varying residuals, final cash flow type and accounting standard.  The purpose here is to show the differences in lessee accounting depending on how you view the lease contract.  The classification decides the level of disclosure in the statements of financial position.

ALF2_07_01 Example inputs

ALF2_07_02 Example 1 rentals

Finance Leases

Finance leases using the accounting standards must be capitalised by the lessee and the accounting entries needed are:

- Periodic depreciation

- Amortised interest

- Fixed asset and accumulated depreciation

- Lease liability less than and greater than one year

- Any timing differences between the accounting entries and the cash flow

The costs included in the rental calculations are expensed by the lessee and not included in these cash flows.  It is assumed that they are not lessee commissions or security deposits, but internal costs.

The calculator below confirms that rate as 9.22 per cent and this is the rate that will be used to amortise or split each payment into interest and principal.

ALF2_07_03 Confirmation of IRR

As in the earlier examples, the capital outstanding is multiplied by the periodic interest rate and the capital repaid is the difference between the rental and the interest portion.  This progresses through the thirty-six periods such that the final balance is zero.  Amortisation means that the interest payable declines as the balance is paid down while the capital repayment increases.  For completeness, there are also the advanced IPMT and PPMT functions which attain the same answer as the manual method.

The lessee income statement consists of amortised interest and depreciation.  Most companies use straight line depreciation to a zero salvage value unless there is some other reason such as contingent usage to change the pattern of write off.  This means that the amount expensed will not necessarily equal the cash rentals and so there will be a timing difference on the balance sheet.

ALF2_07_04 Example 1 amortisation

The balance sheet will consist of depreciated fixed assets and a liability to pay the outstanding rentals.  You could split the liabilities into the current and long-term portion as below.  Excel has two Analysis Toolpak functions CUMIPMT and CUMPRINC for the interest and principal respectively between two payment dates as per the example below.  For calculating flags, the MOD function produces a remainder based on a divisor and is a simple way of identifying a year end.  Where there is no remainder, the MOD function is equal to zero and in the case, you want to display only the amounts at the year ends.

Cell K25: =MOD(B25,12/$D$10)

ALF2_07_05 CUMPRINC and CUMIPMT

ALF2_07_06 CUMIPMT function

The income statement shows the thirty-six monthly rentals and the amortised interest and depreciation.  The total charge to the income statement is the depreciation plus the interest.  In the early stages of the lease, the charge is greater than the cash flow and this evens out by the end of the lease.

ALF2_07_07 Income statement

In the balance sheet, the cumulative cash is the total amount paid out in rentals.  The fixed asset comprises the total cost less the accumulated depreciation.  The total liabilities are the starting total less each capital repayment.  This is then split on the schedule into a current and long-term liability using OFFSET formulas:

Cell K15 Current: =-SUM(E16 : OFFSET(E16, 12 / Lease_Inputs!$C$190-1 , 0))

Cell L15 Long term: =OFFSET(M15, 12/Lease_Inputs!$C$190, 0)

The shareholders’ funds constitute the sum of the income statement depreciation and charges while the difference represents the timing difference between the total charges and the cash rentals.  There are checks in the model to make sure that the assets equal the liabilities.

ALF2_07_08 Balance sheet

You can use functions such as SUMIF based on the year number to aggregate the amounts into annual statements.  The function ROUNDUP with zero places is a useful way of finding the year number:

Cell C79: =SUMIF($Q$15:$Q$75, $B79, C$15:C$75)

Cell Q15: ROUNDUP((B15 - IF(Lease_Inputs!C$196 = 0, 1, 0)) / (12 / Lease_Inputs!$C$190), 0)

ALF2_07_09 Annual income statement

ALF2_07_10 Annual balance sheet

Using the chart in Figure 07_11, you can see clearly the equal rentals made up of interest and capital repayment and the income statement charge declining over the term.  The total liabilities are on the right-hand scale and reduce to zero as this lease does not have a balloon final payment.  Charts of rental cash flows are useful for checking that the model is producing a plausible solution.

ALF2_07_11 Cash flow chart

Operating Leases

Under historic operating lease standards, rentals were not split into interest and capital and merely expensed through the profit and loss account.  The figure below shows the calculation of the rental with a fifteen per cent residual value.  The residual rate risk is borne by the lessor.  The rental reduces to 2,789.27, which represents a very low cost of net borrowing to the lessee.  Known as the ‘street rate’, this is 0.28 per cent per annum nominal.

ALF2_07_12 Operating lease rental

The income statement shows only the rentals and the balance sheet consists of the accumulated cash outflow offset by the shareholders funds.  Thus, there is an improvement in the leverage ratios as the debt is not visible, however the cash flow for the lessee remains the same.

ALF2_07_13 Operating lease cash flow

New IFRS 16 Standard

The new standard ignores the historic distinction between finance and operating leases and seeks to capitalise the liabilities due to the leasing company.  In the case of a finance lease, the lessee can calculate the inherent rate and capitalise the equipment at full value.  He can then write down the lease using the existing method as described above.

The lessee should also capitalise operating lease liabilities:

- Fixed payments from the commencement date

- Certain variable payments that depend on an index or an interest rate

- Residual value guarantee given to the lessor or supplier

- Exercise price of purchase options

- Any termination penalties

Where the lessee has all the information, the right of use asset consists of:

- Lease liability

- Initial direct costs

- Costs of removal and restoring

- Payment made before the lease inception

- Less any lease incentives received

The definition of the lessee’s incremental borrowing rate should represent what the lessee ‘would have to pay to borrow over a similar term and with similar security, the funds necessary to obtain an asset of similar value to the right-of-use asset in a similar economic environment.’  This is not the weighted average cost of capital (WACC), but a marginal market rate consisting of:

- Base borrowing factors for the environment, lease term and asset value

- Financing factors specific to the lease

- Asset factors determined by the type of asset

The lessee will not necessarily know the capital value, inherent rate or future value.  In an operating lease, he is presented with a document showing the periodic rentals and that is usually all.  There is no legal requirement for the lessor to disclose the interest rate, residual value or assumed capital value.  Therefore, the only rate that the lessee can apply is his marginal borrowing cost for a transaction of similar size and duration.  In this case the borrowing cost is input at twelve per cent.

The present value of the periodic rentals (without the residual value) is 84,817.  This is confirmed on the calculator below.

ALF2_07_14 Lessee present value confirmation

The IFRS 16 presentation elements are:

- Statement of financial position – right of use assets shown separately to other fixed assets together with lease liabilities

- Statement of profit and loss – interest expense with other finance costs and amortisation of right-of-use assets

- Statement of cash flows – cash payments of lease liabilities as financing activities plus where applicable short term, low value and variable lease payments within operating activities

The disclosure requirements are more extensive than under IAS 17, since the new standard contains a requirement for a lessee to provide information to allow users to understand the impact of leases on the financial position.  IAS 17 was criticised for failing to provide enough information for financial analysis and the new standard seeks to redress this position.

The lessee now must amortise the liability and split each rental into interest and principal as in the finance lease.  The capital remaining must be equal to zero on expiry and the total interest must be equal to the charges.

The initial balance is 82,028 after payment of the first rental of 2,789.27.  When you multiply the balance by the periodic rate of one per cent, it totals 820.28.  This is the interest payable on the balance outstanding and the capital repayment is the difference between the rental and the interest.  The process continues until expiry of the lease term and the capital has been fully written down.

ALF2_07_15 Operating lease amortisation

For completeness, the following graphics repeat the operating lease entries with the capitalised liability.  These use straight line depreciation in the same way as the finance lease.  The new model shows the full liabilities and reduces the number of adjustments that must be made to assess more accurately the extent of a client’s borrowings and commitments.

ALF2_07_16 Operating lease income statement

ALF2_07_17 Operating lease balance sheet

ALF2_07_18 Annual income statement

ALF2_07_19 Annual balance sheet

ALF2_07_20 Rental chart

Summary

This chapter presents some worked examples of lessee accounting classified as historic finance and operating leases together with an operating lease under the revised IFRS 16 method of capitalising the known liabilities.  The classification radically alters the amount of disclosure required by the lessee.

8 Lessor Evaluation

Sheets: Lessor Fin_Lease, NCI, SYD, Op_Lease and IFRS

This chapter switches to lessors to show firstly different methods of income recognition and then how you can construct cash flows and statements of accounts including taxation.  Lastly it demonstrates the effects of optimising and changing variables to produce more margin or reduce risk.

Financial Objectives and Accounting

The basic lease objective is to reward the lessor when he is most at risk in the early stages of the lease and spread the margin on a logical basis.  The lessor requires the payment of the principal plus a sufficient return on the principal as calculated in the pricing models.  Returns are important since the lessor is at a disadvantage to banks offering loans, because the physical asset is usually the only security available.  Most assets lose value rapidly in the early periods and then depreciate gradually to a salvage value meaning that there are often periods where a lessor could lose on termination.  Amortisation curves pay off less capital in the early stages and increase the capital repayment as the balance outstanding reduces.  Similarly, a lessor does not have the same relationship with clients as principal banks and may not recognise early signs of financial difficulty.  Therefore, the lessor is likely to have to accept equipment and credit risk in the lease in the event of a default.

The lessor accounting requirements in IFRS 16 are unchanged in most respects from IAS 17. Leases that transfer ‘substantially all of the risks and rewards incidental to ownership’ of the underlying asset are finance leases. All other leases are classed as operating leases.  IFRS 16 does provide more guidance on the definition of a lease as set out in the last chapter and classification of variable payments, sale and leaseback, sub-leases and lease modifications.

UK Taxation

UK corporation tax was 52 per cent in the 1970’s but this was matched by a 100 per cent tax write off of assets.  Therefore, a bank would receive 52 per cent of the asset value as saved corporation tax usually within twelve months of writing the lease.  The lessor received the capital back quickly allowing him to write more leases.  The tax environment made leasing extremely profitable, however governments across Europe have been steadily reducing the level of taxation and tax depreciation rates and thereby reducing the tax effects of leasing.

The UK Finance Acts of 1997 had an adverse effect on finance leases and made short term leases less attractive.  Similarly, the Finance Act 2006 tried to remove the preferential tax treatment for longer-term funding transactions.  Capital allowances are now available to the lessee rather than lessor for certain types of leases.  This brings the UK into line with much of Europe where the lessee rather than the lessor normally claims the tax depreciation on finance leases.

The UK now taxes ‘long funding leases’ in substantially the same way as current hire purchase agreements (lessee claims tax depreciation).  These agreements are defined through a two-stage process where either:

- NPV of the minimum lease payments is greater than 80 per cent of fair value of leased asset at inception.

- Lease term is greater than 65 per cent of asset's remaining useful economic life.

A ‘funding lease’ is a long funding lease unless:

- The term is no longer than five years

- If the term is between five and seven years, the rentals are substantially even, and the implicit residual value is no more than five per cent of the initial fair value of the asset

- The lease contains a substantially even rental pattern with less than five per cent variation in rentals between any two twelve-month periods, excluding any pre-lease or upfront balloon rentals and any variation due to changes in interest rates

The new rules do not apply to plant or machinery leased as part of a lease of land and buildings or to ships leased to companies within the tonnage tax regime.  There were also changes to rules which restrict capital allowances in the first year to a time apportioned amount.

Capital allowances were increased for small companies but continue to fall for large companies.  The rate of writing down allowances has reduced in stages to nineteen per cent.

The recent budget announced that corporation tax would remain at a rate of nineteen per cent.  The tax rate for small companies has converged to be the same as large companies.  Therefore, it is important to understand the taxation benefits available and the pricing of a lease.

For the purposes of evaluation, you need to know:

- The rate of corporate tax

- The level of tax allowances available to the lessor

- The method of applying the tax depreciation for example, straight line, declining balance or some hybrid method

The model uses nineteen per cent and UK tax depreciation over the term of the lease and tax payment delays are ignored.  Again, the actual dates of corporate tax payment vary by country and jurisdiction.  As an example, a comparison of depreciation methods is below showing straight line (SLN), declining balance (DB) and sum of digits (SYD).  This example assumes an asset valued at 10,000 with a 1,000-salvage value after ten years.

ALF2_08_01 Depreciation

ALF2_08_02 Depreciation chart

Financial and Non-financial Reasons

The lessor needs to allocate earnings from a finance lease over the term to produce a constant periodic return on the net investment in the lease.  The basic rule is that margin is greatest when the lessor in the early stages is most on risk.  The allocation of income should also match finance income with expenses as far as possible.  The costs (funding) reduce during the term of the lease as the lessor recovers the investment and income is recognised based on the amount outstanding.  There are different methods of apportioning gross income based on the forecast of cash flows at inception.  Normally the lessor does not revise the forecast during lease period unless there is a fundamental change in circumstances.

The first example uses the finance lease from the previous chapter with 36 monthly rentals of 3,165.91 and a gross interest rate of 9.22 per cent.  There is no residual value or balloon payment.

ALF2_08_03 Finance lease

Finance Leases

Leases written under existing standards require the lessor to record the investment in a finance lease as a lease debtor.  The capitalised amount is the net investment less provision for bad and doubtful debts receivable, which , on inception, is usually the cost of asset.  The income statement needs to show the finance income.

Whilst IFRS 16 advocates the actuarial method of amortising income, there are various methods of allocating margin to periods and the next sections illustrate four of the methods.  These are:

- Amortised interest

- Investment period or net cash invested

- Sum of digits also known as Rule of 78

- Actuarial method

Amortised Interest

If the lessor were to use a simple amortisation schedule, the rentals would be split between interest and principal in the same way as the lessee.  The model uses a constant rate to write down the lease debtor.  The initial capital represents the 100,000 cost less the first rental in advance.  This is multiplied by the periodic interest rate to form the pre-tax margin and then any costs are deducted.  The periodic debtors’ reduction becomes the difference between the rentals, the margin and costs.

You could also use Analysis Toolpak Excel functions to compute the interest and principal:

- IPMT - Interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate.

- PPMT - Payment on the principal for a given period for an investment based on periodic, constant rate.

The lease debtor reduces with each period debtor reduction, the cash is the accumulated rentals and the shareholders’ funds the cumulative margin.  The model checks itself since the total income must equal the interest charges and the total debtors’ reduction must be the same as the initial value.

ALF2_08_04 Pre-tax calculation

ALF2_08_05 Pre-tax

Net Cash Invested

The net cash or investment period method allocates margin based on the actual cash invested and does not provide any margin when the lease becomes cash positive.  This means that the margin is highest when the lessor is most at risk in the early stages of the lease.

First you work out the amount of cash remaining at the end of each period and then you add up all the balances.  The first period becomes the cash balance for that period divided by the total of the negative balances and so on until the accumulated cash becomes positive.  This will allocate one hundred per cent of the available margin based on the pre-tax cash investment.

The debtor reduction is the difference between the rental and the margin, and the accounts follow on with cash and shareholders’ funds:

- Periodic profit = (Balance / Sum of balances) * Gross margin

- Debtor reduction = Rent – Gross earnings

The cash flows below demonstrate the calculation of the balances together with the accounts.

ALF2_08_06 Cash balance outstanding

ALF2_08_07 IPM Accounts

Sum of Digits

The sum of digits approximates to an amortisation curve and its name originates from the fact that 12 + 11 + 10 ….. = 78.  It is a simple formula that is like a constant rate and dates from times when computers were available.  It is one of the traditional methods of allocating interest on hire purchase and loan agreements.

If there are twelve periods and the total factor is 78, you take 12 / 78 in the first period, 11 / 78 in the second period and so on.  The simple formula for working out the factor is:

Formula = [ n * (n+1) ] / 2

12 * (12+1) / 2 = 78

This method uses the total charges and takes no account of the cash flows and is inappropriate for leases with:

- Multiple payments in advance

- Long duration

- High interest rates

- Uneven rental profile

- Odd or part periods, for example the method would calculate a full month if written before 15th of month and nothing if written after 15th

Nevertheless, it is a straightforward method for allocating charges which lends itself to the use of tables.  Excel has a built-in function for sum of digits called SYD which works out the amounts in each period directly.  The model assumes that the first rental is all capital and uses 35 as the total number of periods.   The inputs to the function are the total charges, any salvage value, the total periods and the period number.

Cell F8: =SYD(Lease_Inputs!$H$61 - Lease_Inputs!$D$7 , 0 , Lease_Inputs!$D$10 * (12 / Lease_Inputs!$C$190)-1, B7)

ALF2_08_08 SYD function

The accounts follow a similar pattern as the investment period method where the debtor’s reduction is the default item.  The lease debtor is the capital less the accumulated lease debtor, the cash represents the sum of the rentals and the shareholders’ funds the cumulative margin after costs.

ALF2_08_09 SYD accounts

Operating Leases

Operating leases are treated as a fixed asset investment as it is assumed that the asset will be rented to multiple hirers during its useful life.  This means that the asset is capitalised as a fixed asset and then depreciated on a straight line to zero or a known salvage value.  There could be an argument for using usage rather than straight line where there are contingent rentals.

The lessor may or may not receive rentals during a particular period, for example if the asset is back in the warehouse between hires for refurbishment, but he continues to write off the equipment.  There are significantly greater risks for the lessor with an operating lease as the cash flows are not fixed:

- The capital outstanding is always higher than a finance lease due to the residual value

- The residual value is a forecast of the asset value and not fixed unless supported by a supplier or manufacturer guarantee.  Market conditions can change in the interim due to economic circumstances and the lessor bears the risk

ALF2_08_10 Operating lease rentals

There is a fifteen per cent residual in the operating lease example.  The rentals can be discounted at the lessee cost of funds of twelve per cent and the present value is 85 per cent and therefore significantly less than the 90 per cent threshold for an operating lease using historic standards.  Therefore, the lease may transfer substantially all the risks and rewards of ownership to the hirer.

The amount to be depreciated is 85,000 after inclusion of the 15,000 assumed residual value.  The periodic depreciation is therefore 2,361 and this is subtracted from the rental received and the costs.  The fixed asset becomes the cost less the depreciation which is balanced by the cash and accumulated margin in the form of the shareholders’ funds.

ALF2_08_11 Operating lease accounts

The chart below shows the very different recognition of profit between the operating and finance lease methods.  The finance leases offer more margin in the early stages of the lease because of the balance outstanding.

ALF2_08_12 Comparison

IFRS 16 Standard

The favoured UK method used to be a post-tax actuarial calculation with income allocation apportioned based on the after-tax cash flows.  This was deemed to be more accurate since this reflected the true cash invested in the lease.  The modelling is more complicated since you must generate the cash flows and then find a constant rate that can be applied to the cash balance and compute a margin to be removed from the cash flow.  This is an iterative calculation since you must find the constant rate at which the final cash balance is zero.  If the rate is too high or low, then you have a balance outstanding.  The new standards mainly use a pre-tax method which simplifies the modelling.

ALF2_08_13 IFRS 16 margin

Finance Leases

The model requires a constant pre-tax return, considering positive and negative cash flows.  You need a computer to predict the future cash flows as this is a circular calculation.  You must solve for a constant rate that results in a final zero balance outstanding.  As with the investment period, no margin should be available once the lease is cash positive.

The cash flows to consider are:

- Amount of finance

- Tax saved due to capital allowances

- Ancillary costs

- Tax relief on costs

- Periodic lease rentals

- Tax payable on lease rentals

The first stage is to generate a cash summary of the net investment.  The rentals are brought forward from the cash flow sheet.  You can compute the period ends and year numbers easily using ROUNDUP and MOD.

Cell D20: MOD(B19-IF($C$12=0,0,0),$C$10)

Cell E20: =ROUNDUP((B20 - IF(Lease_Inputs!C$196 = 0, 1, 0))/(12 / Lease_Inputs!$C$190), 0)

The annual rental can be extracted using SUMIF with the year number as the selection.  The costs are brought forward from previous sheets.  The tax charge consists of:

- Net finance income

- Costs

- Tax depreciation (capital allowances) simplified as the equal percentages below

ALF2_08_14 Rental cash flow

The cash invested uses the cash figures only when there is cash invested and the rate of 10.69 per cent is an iterative figure to ensure that there is no cash outstanding at the end of the period.  There is an IF statement to suppress negative cash flows:

Cell K20: =IF(K19=0, 0, IF(K19-F20-I20-J20> = 0, K19-F20-I20-J20, 0))

The amortisation charge emerges as the difference between the finance charge and the rentals.  The lease debtor asset can be written down using this charge.  The final columns are the capital allowances and the tax written down value.  The input choice of the Lease_Inputs sheet is simplified as equal written down amounts and therefore the tax depreciation is 33 per cent in each year.  If UK tax were used as an option, the rates would be:

18.00%

14.76%

12.10%

Balance of 55.14%

The tax depreciation method can be changed in line 22 on the Lease_Inputs sheet (scenarios 1-5 in the blue cells only).

ALF2_08_15 Amortisation

The above schedule generates the necessary entries:

- Rentals

- Costs

- Finance charge

- Taxation due

- Brought forward and carried forward value

The correct interest rate needs to be found with Goal Seek as it is a circular or iterative calculation.  You must find the rate at which all the capital has been amortised and written off.  It is useful to colour code the target and changing cells.  This author uses green for the target and yellow for the changing cell as below.  When you press ‘OK’ to run the macro, the model will try to converge on a solution.  The balance sheet check confirms that there are no errors in the accounts.

ALF2_08_16 Goal Seek

It is usually better to use a macro for Goal Seek as you must enter all three entries every time using the Excel menus.  The macro code is:

Sub GoalseekRate()

'GoalseekRate Macro

Range("F5") = 0.1

Range("S8").GoalSeek Goal:=Range("c7"), ChangingCell:=Range("F5")

End Sub

Cell S8 needs to be the same as the capital value and the changing cell is the interest take-out rate.  The next stage is to put it all the entries together as a set of accounts.

The net finance income represents the charges less the annual costs.  The total tax is calculated on the rental income and the costs.  The current tax considers the tax depreciation allowable on the equipment.  Again, different countries differ as to whether this offered to the legal or economic owner.  In the UK, this is legal owner for leases that are not greater than seven years and length and deemed not to be ‘long funding’ leases.  The tax depreciation has the effect of delaying the tax payment and a portion will be deferred to later taxation periods.

ALF2_08_17 Income statement

The accumulated cash on the balance sheet represents the sum of the rentals, costs and tax and will be equal to the net profit on expiry.  The lease receivable includes the periodic charge for debtors.  There is no residual asset as this is a finance lease.  The model assumes that the current tax has been paid and the deferred tax is the difference between the amounts due and the total amounts payable over time.  Tax depreciation works since the tax depreciation is greater than the accounting depreciation and leads to delayed tax payments.  The shareholders’ funds are the total of the net profit which accumulates over the rental periods.

ALF2_08_18 Balance sheet

The workings on the right-hand side double check that the income and the tax are correct since the deferred tax account must be zero at the end.  Similarly, the assets and liabilities must match in every period.  On expiry, the shareholders’ funds are equal to the accumulated income after tax.

ALF2_08_19 Balance sheet checks

For completeness, the model produces annual statements together with a chart.  You can see the finance charge reducing as the debtor is paid down.  The stacked column chart also confirms that the debtor and interest charges equal the periodic rental.

ALF2_08_20 Chart confirmation

Operating Leases

The operating lease evaluation now remains the same as previous standards with the capitalisation of the asset and straight-line depreciation.  Income is derived as the rental income less depreciation and costs.

To show the difference, this example uses the same methodology as the finance lease with the calculation of an actuarial rate to write off the lease debtor during the rental period.

The inherent rate in the lease with all the costs is 8.8936 per cent as below.

ALF2_08_21 Confirmation of interest rate

You also must recalculate a take-out rate since the lease now has a residual value.  Pressing the ‘Update’ button activates the Goal Seek macro.  Again, you must make sure that you have amortised all the costs by the end of the lease.  The rate here is 10.50 per cent.

ALF2_08_22 Calculation of take-out rate

The cash flow uses the same methodology as the finance lease with tax calculations using the finance charge.  The finance charge is worked out at 10.50 per cent based on the positive cash outstanding.  Because of the residual value, the lease cash flow does not become positive until near the end of the lease.  With the subtraction of lease debtor, the balance is carried forward to the next period.

ALF2_08_23 Operating lease cash flow

ALF2_08_24 Finance charges

The income statement uses the costs and finance charges to form the net income.  The tax calculations are the same as for the finance leases as the lessor is the owner of the asset.  The cumulative net profit is carried forward to the balance sheet shareholders’ equity.

ALF2_08_25 Income statement

The balance sheet also now contains the lease receivable, and the cash represents the cumulative rentals received less the tax and costs.  The deferred tax is simply the timing difference between the tax due on the net income and tax paid after inclusion of the tax depreciation.

ALF2_08_26 Balance sheet

The annual statement in Figure 08_27, chart and confirmation check the figures.  You will notice the change in the profile with the final residual value, suggesting the increased risk from writing an operating lease.  On the plus side the margin in the lease rises to 11,513 from 10,345.  Nevertheless, the lessor needs to achieve a sale at or above the residual value to maintain the margin.  If the asset is disposed of for less than 15,000, the lessor’s return will be adversely impacted.

ALF2_08_27 Chart confirmation of the rentals and final value

ALF2_08_28 Calculation of results

Sensitivity

The above sections compared the finance and operating leases under different standards and showed differences in the allocation of margin.  The next question is what factors will improve the profitability of the lease?  There are a defined number of inputs to the model and generally improving the profitability will worsen the credit position with more capital outstanding against the potential asset values.  The actions below will increase profitability and to illustrate the differences, there are two worked examples below.

- Reduce deposit

- Increase term

- Increase residual value

- Increase debt to equity percentage

- Reduce tax rate

- Increase rate of capital allowances

- Change of tax method

ALF2_08_29 Example 1 base calculation

Increasing the period from three to four years increases the net charges from 12,772.71 to 17,079.66.  This will increase further if the lease includes a balloon rental as the average amount outstanding increases.

ALF2_08_30 Increased period

ALF2_08_31 Increased period and balloon rental at twenty per cent

Increasing the tax rate reduces the cost of capital due to the tax effect on the cost of debt.  The capital allowances are worth more since the tax depreciation allowance has a 25 per cent rather than a 19 per cent effect.  On the other side the income is taxed at a higher rate.  On a pre-tax basis the net charges increase but reduce on a post-tax basis.  The original example had a net margin after tax of 10,345 and this reduces to 10.269.

ALF2_08_32 Increased tax rate

ALF2_08_33 Increased tax rate

If you change the method of capital allowances to UK, this changes the method to an eighteen per cent declining balance method.  Instead of equal allowances, allowances are staggered, and the remaining written down amount recognised in the fourth year.  This is termed de-pooling to gain the full benefit over the lease term.  In the UK, if an asset is not deemed to be short life, the asset continues to be written down at eighteen per cent of the remaining balance with the amounts getting smaller and smaller each year.  The change of method increases the net profitability of the lease, due to the increased tax effect.

ALF2_08_34 Change in method of capital allowances

ALF2_08_35 After-tax return and capital allowances

Summary

This chapter presented worked examples from a lessor perspective to illustrate the differences in accounting approaches and how they lead to different profit recognition and accounting treatment under both the historic and IFRS 16 accounting standards.  Since there are a defined number of inputs to the model you can also sensitise and optimise the margin from a lessor perspective.  You can enter different scenarios on the Lease_Inputs sheet and then fully understand the workings of each of the variables and their relative importance in the optimisation of the rental payments.

9 Lessee Evaluation

Sheets: LeasevsPurchase

In the previous chapter, the model evaluates leases from a lessor standpoint and plots the cash flows and accounting statements to illustrate the returns available.  Lessors evaluate lease proposals to ensure that the returns are sufficient against the perceived risk.  They can then optimise the cash flows which emanate from a lease contract to maximise the effects of tax deferral and return on capital.  Users on the other hand are interested in lease evaluation to assess the attractiveness of leasing against outright purchase or borrowing.  By assessing all the lease cash flows on a post-tax basis, users can consider more closely the ‘lease or buy’ question and select the lowest cost alternative.

When considering lessee evaluation there are several elements that may be ignored or excluded, but may cost the lessee more in fees or extra rentals.  Since the items below are not firm cash flows these are normally excluded from lessee evaluation.

- Default rate or termination interest rate payable

- Secondary rental due after the initial period

- Expiry notice period which if ignored will require further rentals to be payable

- Who gets the benefit of ownership on expiry

- Upgrading and the flexibility to add or exchange equipment

- Agreements requiring the return of equipment to the lessor such that the lessee does not participate in the salvage value

- Restrictive clauses in the documentation

- Tax clauses, which require more rentals to be paid to maintain the lessor’s return.  Clauses can be one or two way meaning that rentals are only increased when changes adversely affect the lessor and not the other way around

- On or off-balance sheet treatment depending on the accounting standards

- Restrictions on use, for example clauses requiring the equipment to remain in a specific country

This is an example of a run-on clause which will require an extra rental to be paid if no formal notice is given.  Clients need to be aware of interest rates and other potential charges.

“The hire of each item of the Equipment will continue after the Rental Period until terminated by the Hirer having given to the Owner not less than three months written notice to expire at the end of the Rental Period or any time thereafter.”

Alternatively, clauses can be written very widely and from the point of view of the lessor giving the lessor options in the event of non-payment and changes in credit status.  Jeopardy is open to interpretation.

“The Owner may forthwith terminate this Agreement on written notice to the Hirer in the event of.

a: any material breach of this Agreement (including non-payment of rental) by the Hirer, which has not been immediately remedied (if capable or remedy) following a demand by the Owner

b: if any order is made proceedings are commenced or a resolution is passed for the liquidation or winding up of the Hirer

c: if a distress or execution is levied against any property of the Hirer

d: if an incumbrancer takes possession of, or an administrator, an administrative receiver or a receiver is appointed or the undertaking or any property or assets of the Hirer

e: if the Hirer ceases or threatens to cease to carry on its business or is unable to pay its debts as they fall due or enters any arrangements with its creditors generally

f: if the Owner has reason to believe that its rights in the Equipment are in jeopardy”

Financial and Non-financial Reasons

The lessee needs a financial framework to understand whether leasing is the least cost option.  There are several sources of capital and leasing may prove to be more expensive than other sources.  A formal evaluation is needed to assess the least cost option amongst a range of alternative leases and methods of acquisition.  Examples of alternatives are:

- Cash which for large companies is arguably the biggest competitor

- Retained earnings and current cash

- Issuing new ordinary or preference shares

- Issuing bonds

- Borrowing on overdraft from clearing banks

- Term or bank loans from specialist banks

- Hire or lease purchase rather than a taxed based lease

- Short term rental

The assumptions for the lease versus purchase model are:

- The client has made a positive decision about acquiring the asset

- He will use leasing or borrowing to finance the asset

- He does not possess sufficient liquid funds and must borrow the funds or lease the equipment

- Leasing and borrowing possess similar risk characteristics and there is no difference in the type of security demanded by the lenders

- Inflation is disregarded as it affects leasing and borrowing similarly

- Any uncertainty about corporation tax is ignored and you assume that the organisation can claim all tax relief available

- No economies of scale can be gained from leasing

In all cases, a sensitivity analysis is needed to demonstrate the effect of changing variables.

Financial Objectives

The model treats a lease like any other investment in that you need to know the net present value discounted at a risk adjusted rate, considering all the incremental cash flows.  This includes rentals, capital allowances lost through leasing and tax relief on the rentals paid.  If the net present value is below the initial capital value of the equipment, then from a financial point of view, leasing offers the lowest cost option.  You could use the inherent interest rate (internal rate of return), but this is not so useful for comparing options as you should use a marginal cost of funds.  This is the cost of a loan over the same period and circumstances.

There may be of course other non-financial factors as above to consider, which may make the decision-making more complex. The next sections use the finance lease and operating lease examples from the last chapter to show the benefits or otherwise of leasing.

ALF2_09_01 Finance lease

Finance leases

Discounted Cash Flows

The model calculates the net present value of the rentals payable and other cash flows at the client’s cost of funds.  This could be materially different from the interest rate offered by the lessor as in this example case where the client rate is twelve per cent.  The lessor could benefit from a low cost of funds or be able to pass on tax benefits in the pricing, which are not available to the lessee.  You need an after-tax present value for each alternative so that you can compare each of the options objectively.

The depreciation and the finance portion of the rentals on a finance lease are allowable against tax and therefore the model provides for interest relief based on the organisation’s marginal corporation tax rate.  Here the lessee tax regime is the same as the lessor.  The tax delay is set to one month after the year end.  Different countries have different rules: in the UK where small companies pay tax nine months after their year-end while public companies pay on a four-quarter system starting with a forecast of the year end profits.  This is complex so the model uses an approximate delay of one month.

ALF2_09_02 Lessee inputs

First year allowances at one hundred per cent are currently available to UK small business for the first block of expenditure.  Most companies gain a twenty per cent writing down allowance for purchases, but this is lost if the lessor gains the tax depreciation.  This loss is therefore a negative cash flow to the lessee.

ALF2_09_03 Tax depreciation percentages

The client funding cost is the rate for an overdraft, loan or hurdle rate of an equivalent term.  Alternatively, where a lessee has liquid funds, you could use an opportunity cost of capital.  In either case, you must be sure that you are using the alternative marginal rate of funding for an equivalent loan with similar security.

Pre-tax Evaluation

Where an organisation does not pay tax, you can discount the rental stream at the client cost of funds.  In each case you are only interested in the incremental cash flows arising directly from the decision to lease or buy.

ALF2_09_04 Pre-tax evaluation

There are 36 rentals of 3,165.91 discounted at twelve per cent.  The present value is 96.270.83, which is less than the alternative capital value of 100,000.  The inherent rate in the lease is 9.22 per cent against a client marginal rate of twelve per cent and therefore one would expect a lower net present value to reflect the difference in rates.  The lease or buy decision is a function of the differential in rates and here the lessor price is more than two per cent below the client alternative.

Discount Rate

When clients pay corporation tax, then you need to consider more cash flows.  Firstly, rentals are a cost to the business and the consequent tax depreciation and finance charges are allowable against tax.  Secondly on shorter term leases, the client waives the right to claim capital allowances on the asset and therefore the loss of the tax depreciation must be factored into the equation.

The discount rate is the pre-tax rate factored firstly for the tax rate.  In this example:

Pre-tax rate 12 per cent multiplied by ( 1 – tax rate )

12.0% * 0.75 = 9.00 per cent

Most companies account for tax during or after the year-end in the UK.  This timing difference is a separate cash flow and should be included in the calculation of the after-tax discount rate.  The general formula is:

Int = Intpre-tax – (Intpre-tax * D * T) / (1 + Intafter-tax)n

Where:

- Int = user’s after-tax borrowing rate

- Intpre-tax = user’s pre-tax interest rate entered on the Inputs schedul

- n = average tax delay expressed in years.  The input tax delay is one month, which is 0.083 years.

- T = marginal tax rate

- D = displacement factor: there is usually the assumption that leasing displaces an equivalent amount of borrowing capacity (i.e., the factor equals 1).  You can relax this assumption by entering a percentage factor as for example 0.8 for eighty per cent.  Theoretically, leasing should displace virtually an equal amount of debt, however in practice, leasing does increase debt capacity by providing additional lines of credit.

The source of this formula is Myers, S., Dill, D. and Bautista, A., ’Valuation of Financial Lease Contracts’, 1976, Journal of Finance, 31, 799-819

In this example:

Int = 0.12 – (0.12 * 0.8 * 0.20) / (1 + 0.09)0.083

Post-tax interest rate equals 9.62 per cent

Post-tax Evaluation

Initially, the model uses the calculated after-tax rate of 9.22 per cent to discount the net cash flow for each period.  The incremental cash flows are made up of:

- Rentals

- Tax relief on rentals

- Writing down allowances lost

The cost to the user in each period comprises the rentals less the tax relief plus the tax relief on the capital allowances lost through leasing.  The model plots the rentals and accumulates them on an annual basis for tax relief.  The tax relief is reclaimed one month after the year end.  In this example, the lessee loses the right to claim tax depreciation.  This is 33 per cent of the equipment cost in each year multiplied by the tax rate of 19 per cent.  The column on the right-hand side aggregates the cash flows for each period.

ALF2_09_05 Finance lease cash flows

Rather than making the cash flow formulas complex, the workings on the right-hand side insert the tax and period counters.  For example, a salvage is only included if the lease is an operating lease and there is a residual value.  The salvage will fall due in the period after the end of the lease.  The formula in cell K77 is:

=IF(AND(C77 = Lease_Inputs!$D$10 * (12 / Lease_Inputs!$C$190) +1, LEFT($D$16,1) = "R", LEFT($D$22,1) = "O"), 1, 0)

The other counters use MOD functions starting with the original input that the lease is written in the first period of the accounting year.  The workings below make it easier to check the exact timing of the cash flows since you can see the year ends and the payments one period later.

ALF2_09_06 Period counters

The totals at the bottom also agree: the total rentals are 113,972.71 and the tax relief should be 113,972.71 multiplied by the tax rate of 19 per cent.  This is 21,654.81.

ALF2_09_07 Finance lease result

When you include the tax cash flows, the present value of the lease is 98,570.69 using the simple net of tax rate of nine per cent [12 per cent * (1-tax)].  This represents a gain for leasing of 1.43 per cent.  Using the factored after-tax rate of 10.19 per cent, the net present value is 98,010.77, representing a 1.99 per cent gain.

The net present value for the lease is heavily dependent on the choice of the lessee cost of funds.  As the lessee rate increases the net present value falls and vice versa.  The sensitivity table and chart demonstrate the inverse relationship between the rate and the net present value.

ALF2_09_08 Sensitivity to discount rate

Operating Leases

In the operating lease case, you assume that the lessee will pay the rentals and then return the equipment within the specified time, in the correct condition and at the location as directed by the lessor.  Economic ownership is not being transferred and the lessee only needs and uses the equipment for a defined period. For evaluation purposes, you only include the rentals and ignore the residual value since this is the responsibility of the lessor.

The operating lease provides for 36 monthly rentals of 2,789.27 and the lessee disregards the 15,000 residual value.

ALF2_09_09 Operating lease

The evaluation shows a clear benefit from leasing since the operating lease does not amortise all the capital and the economic benefits of ownership are not transferred.  The present value of the cash flows is less than ninety per cent.  This could represent a cost-effective option for the lessee as the rate of borrowing for the 100,000 is only 0.28 per cent.

ALF2_09_10 Cash flow

ALF2_09_11 Operating lease net present value

The above calculation is simplistic as it assumes that the equipment is worth nothing on disposal if acquired on a bank loan or for own funds.  This may not be strictly true, but of course, one of the benefits of operating leases is the transfer of ownership risk to the lessor.  This includes attempting to attain the fifteen per cent residual after three years.  Since the value of the equipment to the client on disposal is an incremental cash flow, the correct analysis should include the salvage value lost on the operating lease.

The specimen input is 7,500 to reflect the lower marketing expertise.  This is a negative or lost cash flow as the lessor rather than the lessee will gain the benefit on disposal.  You always assume of course that the lessor is better at remarketing the equipment than the lessee and so there should still a benefit for the lessee under an operating lease.  The present value of leasing based on a discount rate of 10.19 per cent is now 92,872.24, which still represents a substantial saving.  You could of course test the model with greater salvage values and the present value would increase towards 100,000.

ALF2_09_12 Operating lease with salvage

Sensitivity

There are limited inputs to the model determining the net present value of leasing of which the discount rate generally has the greatest impact. As you flex the month of writing the lease or the tax rate, the net present value and therefore the benefit of otherwise of the lease will increase or decrease.

The tables below use the finance lease example and demonstrate how these factors affect the lease or buy decision.  The present value rises as you go through the year as the present value of the capital allowances lost due to leasing rises.  Due to the relatively high cost of client funding, there is still a small benefit for leasing.

ALF2_09_13 Sensitivity to month

The two-way table below demonstrates the trade-off with changes in the marginal tax rate.  The worst position is a high tax rate and a lease written in the final month of the tax year.  Leasing is more favourable than borrowing in the early stages of the year as the effect of the lost tax allowances are minimised.

ALF2_09_14 Sensitivity to tax rate and period in year

Summary

This chapter has examined finance leases and operating leases on an after-tax basis by analysing the incremental cash flows arising from each type of transaction.  The model applies an after-tax discount rate factored for the delay period to the next tax payment or credit date.  It also includes where applicable the writing down allowances forgone by the lessee through the lease option.  The model then compares the final net present value to the capital value to ascertain the monetary benefit or otherwise from leasing.  Using this methodology, you can:

- Examine proposals and quotations on a pre-tax basis

- Factor in the cost of flexibility, options and end of period costs

- Examine best proposal against your own tax and cost of funds position.

When you have the present value of each option you can then consider non-financial factors and decide whether to lease or buy.

10 Asset Management

Sheet: Settlements

Asset management is a generic term for managing the equipment on commencement, during and after the initial lease term.  Leasing companies need to manage their assets however, in security seniority, they rank behind clearing banks that accept blanket security since the asset is often the only source of value in the event of default.  The first leasing companies operated as extensions of their parent bank and were not noted as asset specialists.  As the leasing market has developed in different countries and the range of products has proliferated, lessors have become more sophisticated in their approach to asset management as a profit source.

Modern leasing commenced in the early 1950s in the US with definitions of a lease.  To have a functioning leasing market, a country needs a product defined and accepted by the domestic courts as a lease together with a legal system that will support a lessor in gaining arrears and repossessing equipment on client default or slow (delinquent) payment.  In the UK, a leasing company can repossess assets in the event of a material default whereas many countries following European law require a court order to allow repossession.  Delays of this kind can work against a lessor as equipment can be damaged or stolen while the necessary paperwork is gained.

ALF2_10_01 Product development

Figure 10_01 displays a grid of lease products.  The easiest product to offer is a finance lease on known assets such as cars and trucks.  These assets depreciate slowly compared with technology equipment, can be repossessed easily and sold quickly.  Auctions exist to buy and sell these assets efficiently, which limits the lessor’s net exposure.

As more competition develops, lessors need to find new sources of margin; otherwise, they are competing with other banks merely on margin.  Operating leases require more asset knowledge and specialisation, but promise higher margins and potential profits on the eventual sale of the equipment.  Lessors also seek to offer bundles of services such as maintenance, key man insurance, replacement equipment and administration support.  Potential asset exposure can be mitigated by participation in sales-aid schemes and joint marketing linked to manufacturers or joint ventures.  As you go further to the right on the diagram above, more product innovation and higher margins tend to demand more risk-taking and asset sophistication.

Technology refresh involves the option to swap a pre-determined percentage of equipment at different stages of the lease.  At the far right, leases merge with service contracts where a lessor provides a full comprehensive service or outsourcing.  For example, the lessor offers a contract for 1,000 laptops at X per month to include help desks, maintenance, support, replacements and any other required services.  The client only pays for the number of laptops on site in each month but signs off a 36-month contract.  The finance and the equipment are not directly linked making it difficult to define the contract as a basic service or an operating lease.  This is like an out-sourcing contract which provides more areas of potential margin, but also increased risk compared to a simple finance lease.

Asset Use Cycle

Some lessors may wait until equipment is due to be returned before seeking to manage the process, however this is a mistake.  There are opportunities to upgrade or sell more equipment on lease during the rental period.  The diagram below shows that asset management is a continual process since the leasing company needs to understand its potential exposure to the market at each stage of the lease period.

ALF2_10_02 Value creation

Value Creation

To be successful, leasing companies need to develop strategies to manage assets as part of the risk management process.  This diagram describes banking competencies on the left and current requirements on the right.  For example, Lombard North Central as a subsidiary of National Westminster Bank managed over one hundred branches in the 1980s offering a wide range of motor, leasing and personal finance products to their geographic areas.  This model has been replaced by a small number of business centres backed up by modern technology and standardised product offerings.  The leasing industry has fragmented into a series of specialists rather than relying on the subsidiaries of banking groups.

ALF2_10_03 Competences

Lease Termination

The diagram below sets out the life cycle of a lease starting with an initial enquiry and credit checking and assessment.  The lease can terminate due to:

- Upgrading to new equipment on lease

- Client default or equipment theft

- Early voluntary return and payment of a settlement to cover the net capital outstanding

- Equipment return on expiry in a pre-agreed condition

- Ad hoc rental extension with no fixed term

- Re-lease over a new rental period to the existing or a new client

- Purchase at a bargain purchase rate, balloon payment

Each of these methods can affect the profitability of the lease and it is clear that lessors should plan intervention and for the possible return of the equipment in advance.  If the assets do not possess a substantial value, an equipment return is the worst option for the lessor who must incur storage, remarketing and other costs with little chance of a substantial return.

ALF2_10_04 Relationship management

The sources diagram (Figure 10_05) may offer more sources of margin to the lessor in terms of advance intelligence about the client intentions.  There are other products that a lessor can offer to retain contact with the client and its equipment.

ALF2_10_05 Sources of margin

This is an example of a finance lease return clause which allows for an agency sale of the equipment and retention of most sale proceeds.  The clause also confirms that the lease will continue unless the client gives notice to terminate.  This means that the lessee must do ‘something’ to terminate the lease.

“Unless the Hirer notifies the Owner in writing not later than three months before the end of the Rental Period (or at the end of any secondary or subsequent period) that an extension of the letting is not required the letting shall automatically be extended for a further period.  In respect of any secondary or subsequent period the annual rental will be as specified in the Schedule hereto and the provisions of this Agreement shall apply mutatis mutandis.

In the event that such notice is given, the Hirer may then also request that the Owner appoints the Hirer as Sales Agent solely for the purpose of disposing of the Equipment, provided that:

a: the Hirer has complied with all the Terms and Conditions of the Agreement and has paid to the Owner all sums payable under the Agreement on due dates during the Rental Period and any secondary or subsequent extension period;

b: the Owner agrees to the terms and conditions of any sale;

c: the Owner approves of the intended purchaser(s) who shall not be a party connected with the Hirer within the meaning of the Income and Taxes Acts;

d: the sale price of the Equipment shall be an amount equal or greater than the sum calculated in accordance with clause 14 of the Agreement.

Upon completion of the sale 95% of the net sale proceeds will then be returned to the Hirer by way of a rebate of Rental provided that the Hirer indemnifies the Owner

i: against loss suffered as a consequence of such payment not being a deductible trading expense for the Owner, and;

ii: against any loss liability damage cost expense and demand which the Owner may suffer as a result of any claim being made against the Owner ( or any person for whom the Owner acts as agent) in respect of the Equipment following any such sale by the Hirer.

Except where a sale of the Equipment has taken place pursuant to clause XX hereof, on termination of this Agreement howsoever occasioned, the Hirer shall forthwith return the Equipment to the Owner at such address as the Owner may direct in good order and in working condition and at the Hirer's risk and expense.”

An operating lease will not allow the lessee to retain the equipment and will often contain a simple notice and return clause:

“The hire of each item of the Equipment will continue after the Rental Period until terminated by the Hirer having given to the Owner not less than three months written notice to expire at the end of the Rental Period or any time thereafter.

On termination of this Agreement howsoever occasioned, the Hirer shall forthwith return the Equipment to the Owner at such address as the Owner may direct in good order and in working condition and at the Hirer's risk and expense.”

Lease Type

Similarly, the type of lease makes a difference.  Operating leases carry substantial asset risk as the residual value is a forecast of value at expiry of the lease term.  The example in the previous chapters used a lease with a fifteen per cent residual and the workings below demonstrate the effect of a reduced residual on the lessor return.  The interest rate is just over nine per cent at fifteen per cent.  Of course, the return increases with a higher residual.

ALF2_10_06 Operating Lease

A one per cent difference in residual leads to a 0.5 per cent change in the lessor yield.

ALF2_10_07 Sensitivity to residual value

Equipment Type

The equipment is the primary security, and it does matter what equipment is leased.  The lessor needs to mitigate its liability as the potential loss is the difference between the net equipment value realised and the capital outstanding.  Risk managers need to review the assets and their clients at regular intervals and check for obsolescence.

The lessor needs to be sure that the equipment will be secured by the lessee and typical clauses include confirmations of title, limiting the ability to sell or give up possession, usage, maintenance, insurance, name plates, location and blank indemnities in the event of third-party action.  These are usually set out as acts of default together with an overriding clause such as the lessor believes that its rights in the equipment are in jeopardy.

There are a whole range of equipment factors that influence residual values since many factors can change during a lease.  Many factors are not under the control of the lessor as market prices are subject to supply and demand.

Exterior considerations could include:

- The health of the economy and the impact of economic cycles during the lease

- Political influences such as restrictive regulation and tax law

- Social influences and changing tastes for particular products

- Technology changes that render equipment overpriced on expiry or unable to meet current standards

- Environmental considerations and the application of pollution standards

The lessor has many sources of information available for assessing the market value of equipment and he needs to assess the effect of changing key variables.  Examples could include historical data, independent appraisals, equipment guidebooks especially in the motor industry, trade journals, business periodicals, valuation guides and advertising.  The experience on previous leases is also important.  In addition, there are other sources such as economic and country reports, government reports such as inflation or economic surveys, auctioneers, used equipment and lessor experience.  Increasingly there is more information available on the internet with equipment exchanges and auction sites.

Equipment considerations include:

- The manufacturer or supplier is important since the reputation and viability are important for underpinning future value

- Maintenance support may also be required for refurbishment and upgrading

- Secondary market support is also vital on sales-aid schemes to ensure the recycling of equipment

- Trade-ins, special offers and upgrades can play a key role in renewing equipment and providing stock for secondary markets

- A manufacturer may also offer remarketing support or residual value guarantees and therefore will need sufficient financial standing for the lessor credit committee to be satisfied with its viability

Additional equipment considerations include:

- Model and serial numbers and whether the equipment can be separately identified

- Quantity since it is easier to repossess single units rather than a collection of sub-units

- Age and technology life

- Cost including hard and soft costs especially with technology equipment

- Special features which may add value on the second-hand equipment

- Standard physical life and normal wear and tear in the hands of multiple users

- Replacement and maintenance costs

- Technology upgrades and associated costs

There are many lessee factors and these could include:

- Portfolio issues with weightings of equipment type, expiry date, geographical location and industry.  A poor economic outlook can render equipment harder to sell at lower than expected values

- Lessee configuration and whether the equipment is standard or specified and customised for a particular purpose

- Use of equipment, for example a forklift could be used for 1,000 or 2,000 hours a year or a truck single or double shifted during the lease term

- Extra use depresses the value on expiry

- Application of equipment which will similarly affect value.  Operating forklifts in marine environments or on cobble stones mean that components wear out quicker

- Maintenance practice where regular inspections can be skipped especially if there are cash flow problems

Some lessors refer to ‘sticky’ assets meaning that historically, the lessee tends to retain or purchase assets on expiry.  This is perhaps an ideal end situation since the lessor does not have waste time and effort on warehousing, advertising and selling assets.

ALF2_10_08 Equipment matrix

In Figure 10_08, lessors can assume value with long lasting assets such as cars and trucks in the left-hand corner of the matrix.  Alternatively, they can lease assets with known values on operating leases such as the top right above since they are confident that the assets will be worth the residual value on expiry.  Similarly, assets such as rail cars or construction equipment tend to be ‘sticky’ and remain in place on expiry.  The problem area for lessors is perhaps the bottom left where technology assets could be listed.  Here the lessor cannot rely on historic equipment values or the lessee’s propensity to re-lease the assets and the equipment loses value rapidly.

Where there is little known value in the assets, the lessor must consider the facility as unsecured lending and assume that the equipment will have zero value in the event of default or expiry.  The financial standing and reputation of the lessee will assume a greater importance.  For example, it may be safer to lease technology equipment to law firms with unlimited partner liability than small medium enterprises with limited liability.

Maintenance and Upgrading

Where lessors upgrade or retake possession of equipment, they need to look carefully at the values as a common problem is that the residual values are less than market values due to changes in the economic or social environment.  Market efficiency, market size and location can increase the lessor’s difficulties.  Similarly, the equipment location, transport costs, insurance, refurbishment costs, current demand, time to remarket and technology shifts can also depress prices.  The lessor needs to ensure that the residual reflects a forced sale value after all applicable costs rather than a gross market value.

Lessors need suitable asset tracking to cover the end of agreement, disposal and remarketing.  The objective is to reduce risk and liability and available tools include:

- Manufacturer buy backs and remarketing agreements

- Mid-life marketing to plan for equipment returns or upgrade equipment

- ‘Stickiness’ factor of the equipment (whether retained by the lessee on expiry)

- Condition shortfall penalties written into the contract, where the lessee is charged for excessive usage or damage to the equipment.  An example often used is scratches larger than a credit card on cars, where the client is charged for rectifying the bodywork damage

- Residual value insurance where an outside company in return for a fee provides a value to underpin market values

- Portfolio modelling using decision trees, simulation modelling and scenarios built up from historic data as methods of presenting different outcomes and the effect on the lessor portfolio value

Cash Flows

Lessors need to understand the market-value depreciation of the leased asset against the equipment value outstanding.  A model needs to plot the total rents outstanding and the discounted rents against the forced sale market equipment value.  If the amounts outstanding are less than equipment value, you have asset cover and if it is the other way around you have a real exposure.  When looking at a potential lease merely on equipment, you need an equipment type that holds its value to limit the potential exposure to the lessor.

The model needs to generate values for all the scenarios below so that you have a settlement at the end of each period as in Figure 10-09 compared with your forecasted range of values for the asset.  Asset risk is present throughout the lease.

ALF2_10_09 Equipment phases

Evaluation of Gains and Losses

The example below shows 24 rentals outstanding on the finance lease example with rentals of 3,176.95 per month.  The discount rate is input as five per cent which represents a penalty rate compared with the inherent rate of nearly ten per cent in the lease.  Contracts are normally constructed to provide some benefit to a lessor to compensate for earlier termination or loss of tax benefits.  The settlement here is 72,415 which can then be compared to the market equipment value.

ALF2_10_10 Settlement calculation

This is a specimen termination clause which provides for a five per cent compound discount of the rentals as in the above example.  The clause also protects the lessor by providing for payment of overdue interest and other sums involved in locating and repossessing equipment.

A: Upon the termination of the Agreement pursuant to Clause 11 or upon any repudiation or renunciation by the Hirer Owner shall be entitled on demand to recover the following amounts:-

i: all arrears of Rental and all other amounts accrued due under the terms of the Agreement.

ii: any costs and expenses incurred by the Owner in locating, repossessing or recovering the Equipment or collecting any payments due under the Agreement or otherwise in obtaining the due performance of the obligations of the Hirer under the Agreement and

iii: by way of liquidated damages an amount equal to the aggregate of all Rental payments which would have become due under the Agreement had it not been so terminated less a discount for accelerated payment of five per cent per annum compounded on ( and at the frequency of ) each such Rental payment.

B: Without prejudice to the foregoing provisions or to the Owner's claim for any arrears of Rentals or any other rights hereunder the Owner may at any time upon the expiration or earlier termination of the Agreement retake possession of the Equipment and may for such purpose enter upon any land or buildings belonging to or in the occupation or control of the Hirer and on or in which the Equipment is or believed by the Owner or its agents to be situated and if the Equipment or any part thereof is affixed to such land or building shall be entitled to sever the same therefrom and to remove the Equipment or part therefore so severed and the Hirer shall be responsible for all costs, charges and expenses incurred by the Owner in retaking the Equipment aforesaid and for all damage caused (other than arising from the negligence of the Owner or its agents) to the land or buildings by such removal.

The model needs to calculate the equipment value for every period as below.  The table in Figure 10_11 shows a technology asset which drops rapidly in value in the first year and then levels off to a fifteen per cent residual after three years.  The discount rate to be applied to the rentals is five per cent and there are other options available in the combo box.  There is an equipment flex percentage to find the effect of increasing and decreasing the value.

ALF2_10_11 Equipment value percentages

The first column in Figure 10_12 below shows the rental pattern and the next the total outstanding rents.  The five per cent per annum discount provides a reduction in the capital outstanding.  The right-hand columns discount the equipment in periodic intervals to the year-end percentages from the table above.  You see immediately that the lease creates a significant equipment exposure.

ALF2_10_12 Rents and capital outstanding

The right-hand side of the schedule in Figure 10_13 shows the period end exposure as a number and percentage.  The potential loss narrows towards expiry with a numerical peak at around 12 months.  If the lessee were to go bust at this time, the lessor is likely to lose many times the potential profit in the lease.  This may not be an issue if the credit status of the lessee is undoubted, but it is still a consideration for most potential leases.

ALF2_10_13 Exposure analysis

A chart of the total outstanding, discounted amounts outstanding and equipment values clearly demonstrates the peak exposure of over 40,000 at the end of year one.  This is 40 per cent of the capital value and represents a significant risk.  The second chart shows the amounts in percentages as an alternative.

ALF2_10_14 Settlement chart

ALF2_10_15 Settlement percentages chart

The flex table in Figure 10_16 below details the exposure at the end of each year where the equipment values are varied by positive and negative percentages.  The flex is useful since column J represents a zero change which is incorporated into the value formula:

Percentage including flex in cell H18: =G18*(1-$G$6)

Percentage in cell I19: =MAX(0,ROUND(I18-H19,8))

This uses a MAX function to make sure you cannot subtract more value than is left from the previous period.

The exposure increases substantially if the equipment values drop due to economic or market considerations.

ALF2_10_16 Flex table

As noted in earlier chapters, operating leases demand a higher level of asset risk from the lessor since the capital outstanding will always be greater than found in a finance lease.  The second example uses the lease with a 15,000 residual value to demonstrate the increase in exposure.  The residual percentage of 15 per cent coincides with the equipment value at the end of three years.

ALF2_10_17 Operating lease settlement cash flows

ALF2_10_18 Operating lease chart

The flex chart also illustrates the increased exposure from the operating lease structure.  The residual value is of course a forecast made at the beginning of the lease and the market value could change drastically during the term.  Lax return conditions, sloppy notification periods, poor inspection regimes, increased wear and tear, inappropriate return locations, shipping costs, demonstration costs, unforeseen remarketing costs, advertising and storage costs could all reduce the net value achieved on sale.

ALF2_10_19 Operating lease flex table

If the equipment values are fixed and not supported by a manufacturer, one solution could be to offer a structure where the capital outstanding tracks the market value of the equipment more closely.  If the client were not of the highest credit standing, the lessor could insist on a lease with six monthly rentals on signing followed by 30 monthly rentals and no residual value.  The multiple rentals on signing equate to around 20 per cent of the equipment value and therefore reduce exposure on commencement.

ALF2_10_20 6+30 structure

In Figure 10_20, the deposit increases to 18,536 and there are 30 rentals of 3,089 at the merged cost of capital rate of 6.4675 per cent.  The maximum exposure reduces below 30,000 and disappears after 24 months.  If you increased the deposit or reduced the period further, you could eradicate the exposure but this will reduce the size of the loan and the average capital outstanding.  Since the lessor makes money by lending, the profitability of the lease could only be maintained by increasing the rate charged.

ALF2_10_21 6+30 cash flow

The higher deposit reduces rather than eradicates the exposure and there is still a substantial settlement due at the end of one year.  Since the lease is shorter the potential losses towards the end of the lease are negligible.

ALF2_10_22 6+30 chart

ALF2_10_23 6+30 structured flex table

The next cash flow compares the three structures in terms of capital outstanding, equipment market value, exposure and cover.  Each of the structures presents different levels of risk against the primary security.  The safest structure is the 6+30 rentals but at the same rate will yield a lower profit to the lessor.  Therefore, the risk in the lease must be factored against the potential margin as the default termination losses are likely to be higher than the profit to be gained by writing the lease.

ALF2_10_24 Comparison of three lease structures

ALF2_10_25 Comparison chart

The lessor can limit the potential losses by a greater involvement in the asset cycle by being proactive rather than reacting to events as they occur.  For example, the lessor should:

- Seek consensus on valuations from a wide range of sources

- Use upgrades and extensions to mitigate the amount of equipment handling

- Avoid residual value risk by upgrading and restructuring before expiry

- Acquire knowledge of the whole equipment cycle for varied assets

- Develop mathematical models to calculate exposure on an individual and portfolio basis

Summary

The sections above discuss the issues relating to equipment on inception, during and on expiry of a lease.  Lessors need to understand the market value behaviour of their assets together with the likely changes in value to economic and market cycles.  Settlement models assist as part of the credit consideration to demonstrate the real risk in the lease where the asset is the primary source of security.

11 Case Study

Sheet: Various based on Scenario 5

This chapter works through a complete case as an example of lessee and lessor.  It is intended to demonstrate the application of the model to generate the pricing, accounting and evaluation schedules.  The sections are:

- Initial financial calculations

- Lessor evaluation

- Lessee accounting

- Lessee evaluation

- Settlement analysis

Case Study Outline

AA Leasing, Inc. ("AA") agreed to lease equipment to ZZ Corporation ("ZZ") for 48 months.  Under the terms of the lease, ZZ will make monthly payments in arrears of $12,600 per quarter, such payments include maintenance charges of $200 per month.  ZZ also agreed to pay a non-refundable lease origination fee of $3,000 and a refundable security deposit of $10,000 at the inception of the lease.  AA incurred initial direct costs of $1,000 for commissions and ancillary costs to originate the lease.

AA purchased the equipment for lease to ZZ from the MJD Manufacturing Co. ("MJD") at a 10% discount from the manufacturer's list price of $200,000.  ZZ could have purchased the equipment from MJD at the inception of the lease and received the same discount from the manufacturer's list price through a quarterly sales promotion program.

AA and MJD entered into an agreement whereby AA can require MJD to purchase the leased equipment at the end of ZZ's lease for the greater of $15,000.00 or the fair market value ("FMV") of the equipment at that time.  The FMV is to be determined, under the terms of this agreement, by reference to a specific independent valuation service's published wholesale prices for this equipment.

AA estimates that the equipment will have a residual value of 20 per cent of the invoiced price on expiry of the lease.  At the end of the lease, ZZ Corporation has an option to purchase the equipment for the lower of FMV or 20% of the manufacturer's list price.  AA estimates that the equipment being leased will have an economic life of 6 years.

Based on conversations with its lenders, ZZ estimates that a four-year instalment loan to finance 100% of the purchase price of the equipment would be priced at 7.5 per cent per annum plus a 2.5 per cent annual loan facility fee.  The facility fee represents the cost of the $40,000 compensating balance that would be required by the lender for the first three years of the loan.  ZZ does not know AA's implicit rate for this lease transaction.

ZZ leases equipment from AA on a regular basis and normally exercises its option to purchase the equipment at the end of the lease.  ZZ estimates that this equipment will have an economic life of 7 years.

Worked Case Study

The above outline provides all the information needed to check the accounting treatment for each party.  The lessor cash flow (AA) on commencement is $180,000 including the discount followed by 16 quarterly rentals of $12,000 and a final residual value of 20 per cent or $36,000.  With rentals in advance this equates to a nominal rate of 11.3391 per cent.  The lessee (ZZ) does not know the lessor rate or future value and is presented merely with a rental document showing 16 quarterly rentals of $12,000 with one rental on signing.  The lessee has rights to purchase the equipment or return the equipment on expiry.  Since the lessee does not have to pay the residual value, the ‘street’ rate is substantially lower at 3.5 per cent.

ALF2_11_01 Lessor cash flow

ALF2_11_02 Lessee IRR

The lessee cost of funds is ten per cent (7.5 per cent per annum plus a 2.5 per cent annual loan facility fee).  When you discount the rentals at this rate, the present value is 160,576 and this represents 89 per cent of the invoiced amount of 180,000.

ALF2_11_03 Lessee present value

The initial sale contract between the manufacturer (MJD) and lessee (ZZ) is novated to allow the lessor (AA) to take clean title to the equipment and leave the other benefits such as warranties of the initial contract in place.  Novation substitutes the old contract with a new one purely for the purposes of title.  The manufacturer delivers and installs the equipment and invoices the lessor.  The lessor completes a lease agreement, requests a cheque or transfer for the first rental and gains a certificate of acceptance to confirm full installation of the equipment and comprehensive insurance of the equipment.  On completion, the lessor can countersign the lease as acceptance and settle the manufacturer invoice.

ALF2_11_04 Lease diagram

Lessor Evaluation

The table below in Figure 11_05 summarises the lessor transaction from the notes above.  The lessor cost of debt is 7.5 per cent and the tax rate 35 per cent.  To make the rental of 12,000 possible, the lessor has discounted the return on equity to around 14 per cent (14.6634862 per cent).

ALF2_11_05 Lessor summary

The client is situated in the US and therefore the tax depreciation used is based on the US MACRS (Modified Accelerated Cost Recovery System).  This lays out annual depreciation percentages dependent on the equipment type as per the table below.  The model assumes that the equipment is a five-year asset from this table.

The three, five, seven, and 10-year classes use 200 per cent and the 15 and 20 year classes use 150 per cent declining balance depreciation.  All classes convert to straight-line depreciation in the optimal year, which is shown with an asterisk (*).  The half year convention applies where a half-year depreciation is allowed in the first and last recovery years.  Similarly, if more than 40 per cent of the year's MACRS property is placed in service in the last three months, then a mid-quarter convention must be used (not shown with this table).

ALF2_11_06 MACRS table

The cost of capital is 7.3221 per cent using the WACC formula in an earlier chapter.  The calculation is:

WACC = 14.6635% * (1-75%) + 7.5% * (1-35%) * 75%

WACC = 7.3221%

The cash flow below in Figure 11_07 sets out the quarterly rentals payable and the tax consequences of the tax shield and the tax on the finance income.  The initial advance after initial costs before receipt of the 12,000 first rental is 178,000.

The model uses the $1 approach to discount 16 times $1 at a rate of 7.3221 per cent to form an initial present value.  This is multiplied by (1- tax) to complete the present value factor (A).  The other side of the equation is the after-tax present value of the known cash flows (B).  The present value divided by the factor displays the quarterly rental of 12,000 over the lease term (A/B).  The cash flow confirms the correct internal rate as the same as the merged cost of funds.

ALF2_11_07 Lease cash flow

ALF2_11_08 Rate confirmation

If you adopt a simple approach to the lessor accounting, the lease makes a gross profit of 50,000.  For current IFRS accounting, you discount the known lessor cash flows with the up-front charges and commissions without the final residual value at a rate of 11.3391 per cent, the present value is 156,982.15.  Plus the net 2,000 commissions, the total is 158,982.15 which can be compared against 180,000.  The percentage is 88 per cent so it depends on how you view the substance of the contract.  Certainly, under historic US GAAP, the percentage is below 90 per cent and the lease is for a term less than 75 per cent of economic life with no bargain purchase options or automatic title transfers. ASC 842 may yield a different result and categorise the contract as a finance lease.

Simple finance lease accounting amortises the rentals at the periodic rate and derives the capital repayable as the rental less the interest charge.  The initial lease debtor is the net advance which is written down to 36,000 at the end of four years.  Upon completion the lessor is left with a gross margin of 50,000.

ALF2_11_09 Lessor financial lease

Operating lease accounting equalises the capital repayable with straight line depreciation of 9,000 per period down to the residual value.  The pre-tax margin is also equalised as the rental of 12,000 less 9,000.  The shareholders’ funds represent the cumulative margin which maximises at 50,000.

ALF2_11_10 Lessor operating lease

If the lessor accounts for the lease using the revised IFRS, margin is only available while the lessor maintains an investment in the lease.  The method rewards the lessor with most profit while there is the greatest cash investment based on an actuarial constant rate of profit take-out.

ALF2_11_11 Lessor IFRS cash flow

The amortisation writes down represents the differences between the periodic rental and the charges.  This adds up to the total asset value of $180,000 on commencement.

ALF2_11_12 Margin calculation

The income statement brings together all the elements of rental income, net costs, charges and residual value.  The column market ‘Gross Profit’ comprises the net financial income.  The net profit after taxes is $32,500.  Similarly, the quarterly balance sheet confirms the invested cash and the initial lease debtor and residual asset.  The other side of the balance sheet consists of the deferred tax liability and the cumulative net margin in the shareholders’ funds.  The deferred tax asset disappears at the end of the lease and the net position represents the net margin after tax.

ALF2_11_13 Income statement

ALF2_11_14 Balance sheet

The annual statement splits the rentals to show the gross and net income.  The margin declines with the investment in the lease and confirms the net margin of 32,500.  The net tax after deferred tax is 17,500, which represents 35 per cent of the gross margin of 50,000.  The chart clearly illustrates the mix of interest and amortisation in the quarterly rentals.

ALF2_11_15 Lessor chart

Lessee Accounting

The schedules above confirm that the lessor targets are met with sufficient return to debt and equity proportions of the capital.  Since the present value of the rentals is below 90 per cent and there are no automatic title transfers, the lease would probably be accounted for as an operating lease under historic IFRS and US GAAP.  This means that the rentals would be expensed, and notes added to the accounts.  For completeness, the four SFAS 13 tests are:

- Present value of rentals less than 90 per cent on inception (89 per cent)

- Lease term less than 75 per cent of economic life (4 out of 7 years)

- No bargain purchase option (option to purchase the equipment for the lower of FMV or 20% of the manufacturer's list price)

- No automatic title transfer (option to purchase only)

ASC 842 follows the same basic framework without the firm percentages for economic life and the present value of the sum of lease payments.

ALF2_11_16 Operating lease accounting

If the lease were accounted for under IFRS 16 and ASC 842, the lessee would be required to capitalise the minimum lease payments and amortise the liability over the term of the lease.  The rentals are discounted at the lessee cost of funds (ten per cent) since he does not know the lessor interest rate or the residual.  Simple amortisation splits the 12,000 quarterly rentals into interest and principal.  The cash represents the cumulative cash paid while the lease liability is the starting present value less the amortised capital.  The difference between the cash and the liability leads to a small timing difference which is resolved by the end of the lease.

ALF2_11_17 IFRS accounting

Lessee Evaluation

The lessee needs to choose between buying, borrowing and leasing.  The model needs to discount the incremental cash flows applicable to the lease and compare the present value to the invoiced value.  The lessee discount rate for an alternative loan is ten per cent, which after 35 per cent tax is 6.5 per cent.  Using the formula below, the factored interest rate becomes 7.24 per cent.

Int = Intpre-tax – (Intpre-tax * D* T) / (1 + Intafter-tax)n

D = displacement factor

T = marginal tax rate

n =tax delay as a percentage of a year (0.083 equals one month)

ALF2_11_18 Lease versus purchase

The cash flows need to include the 20,000 salvage value which will be lost if equipment is returned to the lessor on expiry.  Similarly, the equipment tax allowances will also be lost so the cash flow needs to include the rentals and their tax effect together with the cash flows lost through the leasing decision.  The schedule below sets out the timing of the rentals and net tax relief with the incremental cash flow on the right-hand side.

ALF2_11_19 Lease versus purchase cash flow

The present value results in a five per cent benefit to the lessee from entering into the lease.  The net interest rate is below the lessee rate of ten per cent and so from a financial perspective, the leasing alternative makes sense.  The lessee would need to examine the contract and the non-financial aspects of the proposal to check for other ‘financial hooks’.  If the lessee is likely to retain the equipment on expiry, a simpler form of contract such as hire purchase may be more applicable than an operating lease with options.  The sensitivity table below illustrates how the benefits diminish with a lower lessee borrowing rate.

ALF2_11_20 Sensitivity to rate

Settlement Analysis

The lessor and lessee also need to consider the equipment market value against the capital outstanding.  For the lessor, the gap between these two values represents the key risk in entering into the lease.  On the lessee side, the gap between the values represents the net settlement payable on default or the amount needed to cover a voluntary termination.  After research, the equipment percentages at the end of each year are 70, 50, 30, 20 and 10 per cent.

The lease ends after four years and the forecast equipment value is equal to the residual value.  The schedule below plots the outstanding rents followed by the capital outstanding, equipment percentage write-down and net equipment value.  The right-hand two columns detail the exposure against total rents and capital outstanding.  The exposure peaks at around 37,000 in year one.

ALF2_11_21 Settlement cash flow

The exposure is clearer on the chart where the area chart represents the exposure is the contract, which remains fairly constant during the lease term.  A finance lease with equal payments over four years and no residual value or balloon payment would further reduce the exposure to the lessor.

ALF2_11_22 Settlement chart

The flex table details how the potential exposure would change if the equipment write-downs changed by intervals of five per cent.  Even with a 25 per cent reduction in the equipment write-off the exposure still results in excessive losses.

ALF2_11_23 Flex table

Summary

This chapter demonstrates how spreadsheet models can be used to evaluate a lease from a lessee and lessee perspective to answer questions about rate, pricing, accounting treatment, annual accounts, evaluation, lease versus purchase decisions and settlement analysis.  The basic rules about spreadsheet design apply where a properly constructed spreadsheet model allows flexible inputs and superior analysis and reporting.

Applied Lease Finance

A practitioner’s guide to applied financial Excel in leasing

Alastair Day

Published by: Independent Publishing Network

Publication date: 31st January 2021

Author: Alastair Day – Systematic Finance

ISBN: 978-1-80049-277-6

Email: info@system.co.uk

Website: www.system.co.uk

Ref: 32 : Words : 41,596 : Saved: 24-January-2021 11:15

Table of Contents

(Unbenannt)

Applied Lease Finance

(Unbenannt)

Copyright

About the Author

Who Needs this Book?

How to Use this Book

Acknowledgement

Conventions

Accompanying Excel Model

1 Overview

Scope of the Book

Financial Mathematics

Simple Interest

Compound Interest

Future Value

Present Value

Nominal and Effective Rates

Continuous Discounting

Conversions and Comparisons

Basic Excel Design

Basics of Design

Objectives

User Interface

(Unbenannt)

Key Variables and Rules

Layout

Individual Modules

Menu Structure

Management Reporting

Future Development

Testing and Auditing

Protection

Documentation

Peer Group Comments

Example Model

Summary

2 Leasing Structures

Definitions

Leasing Products

A. Finance Lease

Selection

Leasing Period

Bargain Purchase Option

Automatic Transfer of Title

Present Value

Accounting and Taxation

Value Added Tax and Sales Tax

B. Fixed term Rental

C. Operating Lease

Lease Purchase or Hire purchase

Contract Hire

Contract Purchase

Fleet Management

Rental and Hire

Benefits of Leasing

Lessee Reasons for Leasing

Spreading the Cost

Clear Source of Finance

100% Finance

Cost Relative to Other Sources of Finance

Ownership

Flexibility

Security

Further Bank Credit

Convenience

Responsibility

Balance Sheet Treatment

Lessee Disadvantages

Lessor’s Reasons for Leasing

Tax benefits

Interest Spread

Fees

Terminal Values

Vendor Leasing

Risk Analysis

High Level Macro or Environmental Risk

Industry Risk

Client Risks

Non-financial Risk

Future of Leasing

Summary

3 Financial Functions

Time Value of Money

Multiple Cash Flows

Internal Rate of Return

NPV and IRR

NPV Periodic Example

Modified Internal Rate of Return

Amortisation

Summary

4 Leasing Calculator

Excel Methodology

Functions and Methods

Worked Examples

Summary

5 Lease Pricing

Theory

Cost of Capital

Returns

Cash Flows

Targeting Returns

Summary

6 Lease Accounting

Accounting Standards

SSAP 21

IAS 17

SFAS 13

New Standards – IFRS 16 and ASC 842

Lessees

Lessors

Summary

7 Lessee Accounting

Finance Leases

Operating Leases

(Unbenannt)

New IFRS 16 Standard

Summary

8 Lessor Evaluation

Financial Objectives and Accounting

UK Taxation

Financial and Non-financial Reasons

Finance Leases

Amortised Interest

Net Cash Invested

Sum of Digits

Operating Leases

IFRS 16 Standard

Finance Leases

Operating Leases

Sensitivity

Summary

9 Lessee Evaluation

Financial and Non-financial Reasons

Financial Objectives

Finance leases

Discounted Cash Flows

Pre-tax Evaluation

Discount Rate

Post-tax Evaluation

Operating Leases

Sensitivity

Summary

10 Asset Management

Asset Use Cycle

Value Creation

Lease Termination

Lease Type

Equipment Type

Maintenance and Upgrading

Cash Flows

Evaluation of Gains and Losses

Summary

11 Case Study

Case Study Outline

Worked Case Study

Lessor Evaluation

Lessee Accounting

Lessee Evaluation

Settlement Analysis

Summary

(Unbenannt)

Applied Lease Finance