Commercial Mortgages – Spreadsheets for Cashflow

Commercial mortgages can be an asset to an organisation if the conditions are correct, but careful cashflow anaysis is required.

How do spreadsheets make it easier for corporate funders such as banks?

Bank loans are a financial instrument. They are recognised as an asset on a bank’s balance sheet and are tradable as an investment in their own right. They also form part of a banks or lending funds credit portfolio. This is analysed continuously to determine the credit quality of the lending institution. Often the credit quality drops when for example there is a credit crunch caused by a financial collapse such as happened in 2008 across the world. As a series of cash flows, the repayments under a loan can be accurately forecast in advance of the loan being granted. This is done by taking the borrower’s business plan and using it to determine what the bank or lender feels are the most appropriate free cash flows which are available for debt repayment over the lifetime of the loan. This reflects the cash flows after all of the business expenses have been deducted but usually before taxes, depreciation and amortization re taken into account. Each lender will have slightly different criteria which they utilise. By seeking an accurate financial statement of the borrower’s free cash flows throughout the term of the loan at regular intervals and inputting these into a spreadsheet, banks and lenders can determine the credit quality of their loan portfolios.

Why are spreadsheets so useful?

A properly constructed spreadsheet allows the cash flows to be sensitised according to scenarios which the lender can chose to model. For instance a spreadsheet that records the cash flows of a loan to property development company building apartments on bare land will record property sales that are recorded net of the costs of sales at times which the borrower and bank believe are sensible based on their analysis of the building project. In the event there are delays due to unforeseen construction issues such as discovering that there are environmental hazards in the ground which were unknown. This will add a period of months to the build and hence will delay the apartment sales. It could mean that the lender will have to wait for a further year for its loan to be repaid as the property company may miss the summer selling season. The spreadsheet allows the lender to model interest costs, holding costs and other time related costs easily so that the effect of the construction delay can be forecast accurately and the lender can see the effect on its loan. The lender can then determine whether the risk of the project is appropriate to financial reward it receives by offering the loan. The business mortgage spreadsheet calculates the Internal rate of return of the loan and other financial ratios such as risk adjusted return on capital. The metrics help the lender determine the risk of their individual loans and when amalgamated, of the loan portfolios.


About Sandra Nelson