Only so much can be achieved through the use of spreadsheets even when using advanced modelling skills such as arrays, nested logic formulae and even nested worksheets and workbooks. Generally, a spreadsheet solution is a compromise between complexity, usability and accuracy. For example, given that rent on property is collected quarterly and interest is paid at the same frequency, financial models with static formulae generally include logic tests to determine whether or not a letting is “live” or not on the quarterly test date. Such methodology results in smaller (i.e. less memory demanding) and easier to understand models but with inferior accuracy (+/- 3 months) and a limit on the number of business rule assumptions which can be accommodated.
pi-FRAME uses Visual Basic code to handle numerous and often complex assumption scenarios yet still enables output accuracy to the day. It uses a single sheet in the workbook as the data source and delivers the calculated results to active reporting sheets which can be further manipulated by the user. The following bullet points highlight some of the key features:
- pi-FRAME Appraiser comprises a single file with all calculations being self-contained when including computing activity generated through the installed Windows library file.
- A separate standalone, macro-free appraisal and cashflow may be produced by clicking on an export button on the source file Controls page. Being macro-free the exported file is readily shared by email with colleagues, professional advisers and clients.
- For less complex and lower risk deals, there is a basic functionality setting to allow users to quickly populate the data sheet with basic assumptions and produce first cut simple cashflows as a precursor to more in depth analysis if required. Users can toggle the Data sheet between the “Basic” and “Advanced” views. The process for modelling all deals is therefore entirely “self-contained” and the same data source is used for both simple and complex analysis.
- Cashflow assumptions (regarding, inter alia, loan term, sinking fund, interest only periods, amortisation, etc.) are menu driven allowing for speedier completion.
- The body of the cashflow is locked down so users are unable to overwrite formulae without first unprotecting the worksheet.
- User defined lettings assumptions allow sensitivity analysis of: void periods; void costs; re-letting periods; re-letting costs; rental inflation and deflation; capital costs or receipts; and, property sales (adjusting sales proceeds for any rent apportionment due to the purchaser).
- Senior and mezzanine debt performance is compared against default covenants.
- A comprehensive interest hedging module is incorporated into the application. Up to 5 separate hedging structures can be modelled. Each of these five scenarios might comprise: SWAP, SWAP with embedded Floor, CAP or Collar. The Forward LIBOR curve can be populated centrally and copied into or linked to Appraiser and then sensitivity tested against upside and downside interest rate scenarios pre-set by the user. In the absence of interest hedging, users can switch the model to a simple assumed fixed rate and period as might be the case when the funding structure is based on long term gilts.
- pi-FRAME code adopts the UK rent review scenario of ‘fixed review period, variable uplift amount’ but German lease indexation is also catered for by using formulae to calculate the ‘fixed uplift, variable review period’ methodology thereby allowing modelling of cross border portfolios within the same file.
- For regulated banks, a return on capital calculation takes into account the FSA’s new “Slotting” risk asset weightings so the impact on deal pricing under the changing regulatory regime is immediately apparent.
- An Equity Return page allows an investor / borrower to assess returns and rationale for investment with or without gearing taking into account an exit yield from which the exit value is calculated.
- An automated data import routine caters for “generic” Excel data sources to be used in the data population process requiring only minimal data manipulation prior to importing. Data population is quick and easy.
The User Guide and a fully detailed example report and cashflow file in pdf format is available to download from the Client Area upon registration. Registration only requires you to enter your email address and a password which is validated through a link sent by us to your inbox. The process is quick, costs nothing and commits you to nothing. Once registered, click on the "Downloads" link in the Client Area menu.