Bayfield Tips
Solver Command (Non-Negative Results)
TODAY Versus NOW Function
Portfolio Risk (Matrix Multiplication)
Legal Definition of the IRR
Types of Cash Flow Construction
The Rent Function
Visual Basic for Applications
Auditing Models
Switching Languages in Excel
Generating English (or Scottish) Quarter Day Series
Solver Command (non-negative results)
The Solver command in Excel is an add-in developed by a company called Frontline Systems. A basic version is available as standard in all copies of Excel. More advanced versions can be bought from Frontline systems.
Solver Window
Among the uses of Solver for real estate applications are: calculating the equivalent yield, optimising return on portfolio investments (minimum risk for a given return, or maximum return for a given risk), and capital budgeting.
Many uses of Solver require a constraint (condition) that none of the changing values should have a negative value. For example in portfolio investments you might wish to restrict funds to 0% or more in each investment
It is possible to specify this with an expression in the ‘Subject to the Constraints’ box above. Another simpler option is to select the options button which will present you with the window below, and tick the ‘Assume Non-Negative’ check box.
Solver Options
The TODAY and NOW function don’t require any arguments, as they use the internal clock of the computer to return a value. The TODAY function, returns today’s date, and changes every time you open the spreadsheet on a different day. The NOW function returns today’s date and the current time precise to the second. This accuracy is unlikely to be required in real estate models. In addition given that it changes every second it will also force your spreadsheet to recalculate every second, and will therefore slow down your model.
Back to Tip List
Portfolio Risk (Matrix Multiplication)
Calculating the return on a portfolio is simply the weighted average or the return on the individual investments. Calculating the overall risk of a portfolio similarly depends on the risk of the individual investments but the calculation of portfolio risk is not so simple. It depends on the individual variances, and the covariance between assets. Xi is the weight allocated to each of three investments, σi2 is the variance of each of three investments, and σij is the covariance between each pair of investments.
You would multiply this calculation out in the following way:
As the number of investments in the portfolio increases the increasingly larger the collection of variances and covariance becomes. This is because the number of possible combinations of pairs, increase. This can be tedious to set up and calculate in Excel. Fortunately the above is a particular type of calculation called matrix multiplication, which exists in Excel.
Assuming the weights (Xi) are in cells A1:C1, and the variances and covariance are in cells E1:G3 the portfolio risk can be calculated in the following way:
=SUMPRODUCT(MMULT(A1:C1,E1:G3),A1:C1)
The value of this function is that it stays the same for any number of investments.
Back to Tip List
The Internal Rate of Return (IRR) is open to wide interpretation and error. Different Analysts can and do in many cases produce dramatically different IRRs when set the same project. This is of critical importance to anyone involved in a joint venture, or lenders requiring thresholds based on IRR measures to be maintained. There is no legal definition of an IRR, nonetheless a definition of IRR will be required in joint venture agreements and some loan documentation. It is important that parties to an agreement understand the potential misinterpretation of the IRR and set out clearly how the cash flow is to be constructed and returns calculated.
Bayfield Training’s Real Estate Analyst course discusses the IRR in considerable depth, and uses different techniques to check for errors in the IRR as well as looking at the acceptable range of assumptions that can be made and suggesting alternatives to the IRR where appropriate. Bayfield Training’s Real Estate Funding Models course teaches delegates how to calculate IRRs in joint venture agreements.
Back to Tip List
Types of cash flow construction
There are a number of ways to construct cash flows in Excel. In all cases you are restricted to the two dimensions of the spreadsheet. It can be argued that some models become so complex that a spreadsheet is no longer sufficient on the grounds of the number of simplifying assumptions that might be required, the potential for error and the increasing opaqueness causing Excel’s transparency advantage to be severely compromised. Nonetheless models do become complicated, and depending on this complexity different methods of cash flow construction may be required.
FORMULA, TAGS, & TABLES
The rent function is the most difficult and important formula in a property cash flow, and is most likely to require consideration of the following three possible techniques.
The formula is the most intuitive technique. Here all the calculations needed are constructed in a single formula. The problem is that the formula can become quite lengthy. Rent functions are likely to be copied across and down a spreadsheet into several thousand cells. The longer the function, the more memory consuming your spreadsheet will be which will make calculating, saving and emailing difficult to do. Nonetheless there are many techniques available in Excel to make rent functions compact, and comprehensible.
Tags are formulae split out from and subsequently referred to by the rent function. Assuming cash flows are presented with their date series horizontally, tags are formulae that are referenced vertically down the spreadsheet. Splitting out formulae makes the rent function easier to understand, and perhaps counter-intuitively even though it will create a visibly larger spreadsheet it will ultimately use less memory.
Tables are collections of formulae grouped together that are split out from and subsequently referred to by the rent function. Tables are usually event and/or rent formulae i.e. they calculate in advance when rent changes will take place, and what those rent changes will be. The rent function can use a simple LOOKUP function to question whether a date in the cash flow is in the event table, if it isn’t, nothing changes, if it is it returns the corresponding rent from a pre-calculated rent table. Again assuming cash flows are presented with their date series horizontally, tables are a collection of formulae that are referenced horizontally to the left of the cash flow.
In complicated models rent functions may use a combination of all three techniques.
Back to Tip List
The income function in property asset modelling is called the Rent Function. The Rent Function must recognise Events and Rents. Events are dates in the lease or in the future cash flow beyond the current lease where the rent will change. Rents are the rents that they will change to on a particular date.
The rent function can be very complicated to model, and require a degree of problem solving. See the tip ‘Types of cash flow construction’ on different ways to generate the rent function.
The Investment Property Databank www.ipd.com run a useful annual briefing called the Strutt & Parker Annual Lease Events review.
Bayfield Training’s Real Estate Analyst course, and Bayfield Training’s European Real Estate Analyst course demonstrate a number of different Rent Functions in Excel for the UK market, and the varying different lease patterns occurring worldwide. The courses also allow the delegate to practice a number of problem solving techniques that will allow them to build their own unique rent functions with ease.
Back to Tip List
Bayfield training argues against the use of Visual Basic when developing property models, except in exceptional non-formula circumstances e.g. compressing the size of the model, repetitive tasks, standard printing functions. This is because most procedures required can be done using the standard features of Excel. There are no models our sister company BFM Ltd have not been able to build despite this limitation. Where models become too complicated for Excel, there are more efficient programming languages than Visual Basic. Since most Analyst’s expertise lies in Finance and not programming, advice should usually be sought at this stage, if not before.
Please feel free to call Bayfield Training Ltd, or BFM Ltd if you have any questions regarding what is possible in Excel, or whether you have any immediate or future modelling requirements.
Back to Tip List
People will use the expression ‘Auditing models’ to mean a simple second check of a model before use by the model builder themselves through to full liability backed external Audits by accountants or professional model auditors.
The need for full professional audits depends on the scope, use and complexity of the model. Most large accounting firms are experienced at this sort of audit. Alternatively specialists such as Operis are recommended www.operis.com
Operis also sells automated spreadsheet analysis software called the Operis Analysis Kit (OAK). There are a number of this type of software available, all check for the logical consistency of model formulae and highlight when and whether models contravene best practice rules so that the model builder can investigate further. These tools can be very useful in objectively identifying any potential problems areas but will not find all types of errors. There is always an element of human judgement required as to whether a model fulfils the task set.
Given that there have been many high profile cases of auditors discovering million pound holes in spreadsheets assumed to be correct and actually used in actual decisions, there is a lot of research in this area. The research concentrates on the preponderance of errors, identifying those errors and classifying them. The conclusions perhaps unsurprisingly are that errors are reduced through best practice design and testing. Testing has been shown to dramatically reduce the number of errors in spreadsheets. The most important element being that the person testing the spreadsheet should not be the person who built the spreadsheet.
For further information see the European Spreadsheets Risk Interest Group website: www.eusprig.org
Back to Tip List
If you want to use different languages and settings in Excel you need to change the regional settings on your PC.
First go to the Control Panel from your Windows Start button and select Regional and Language Options. Choose the country whose settings and formats you want from the drop down list on the Regional Options tab and the appropriate language from the Details window on the Languages tab.
Then open Excel and check that for date and currency formats Use System Separators is ticked on the International tab under Tools and Options. You may also want to switch the dictionary language via the Spelling tab on the Options window.
Back to Tip List
Generating English (or Scottish) Quarter Day series
The quarter days used in the English property market are unique and can pose a problem when wanting to generate a date series for a quarterly cash flow. The main problem is that the quarter days do not fall on the same day of every third month, they occur instead on the 25th December, 25th March, 24th June and 29th September. There are also two separate sets of quarter days in Scotland.
The EDATE function short for ‘Exact Date’ (available as an Add-In) helps us add calendar months to dates. However in order to set up a quarter day series we need a method that will maintain the days of the month e.g. 25th, 29th etc. Although it is possible to create a single formula to generate the correct series from the first date an easy approach is to input the first four quarter days for your model and then use the EDATE function in the fifth cell to add 12 calendar months to the first entry. Copying this formula will always add a year to the date four cells above or behind the formula and thus the exact quarter dates and months are maintained.
For example if the first four cells (say A1:A4) contained the following dates 25th Dec 08, 25th Mar 09, 24th Jun 09 and the 29th Sep 09, type the following function in the fifth cell =EDATE(A1,12)
Bayfield Trainings Real Estate Analyst and Bayfield Training’s European Real Estate Analyst course both look at the construction of date series in detail. In particular it discusses the various solutions to Excel’s difficulty with daily discounting techniques.
Back to Tip List
