![]() This check will simply be the difference between the copy and paste values, rounded to the designated rounding tolerance.įor a time-series copy-paste macro this check should be performed on a periodic basis and an overall check created which sums the absolute value of each individual check. Delta checkĪ check should be set up to enable VBA to iteratively implement the copy and paste via a loop (explained below), until the applied (paste) value is within the designated tolerance of the calculated (copy) value. Named ranges can be created using the Name Manager, accessed with the keyboard shortcut Ctrl + F3. This will ensure the VBA code won’t break if rows or columns are inserted or removed from the sheet, causing the cell references of these ranges to change. Instead, the copy and paste ranges for each macro should be setup as named ranges in the model. VBA code should never reference specific cell references in Excel. The below screenshot demonstrates the basic setup for our two copy-paste macros: Named ranges: The paste areas to be used by the macro should be clearly formatted as such, we recommend setting up a special and distinct cell style with a label such as “macro paste “ (see orange cells in below exhibit), to ensure the user understands these are not input cells. Setup Macro sheetĪs a matter of best practice all copy-paste macros should be setup on a specified “Macro” sheet as this improves transparency and reduces risk of pasting values in the wrong cells without realising. We will cover this type of copy-paste macro as well as a DSRA target balance copy-paste macro in this tutorial. ![]() In this quite common situation the debt limit will be calculated as the total amount of principal repayments able to be paid over the loan tenor given the project cash flows (CFADS / DSCR – Interest), however the interest in this calculation is dependent on the debt limit as this affects the amount of debt drawn initially and therefore will depend on itself (a circularity). ![]() The typical project finance example is the case of debt sizing based on a DSCR-sculpted repayment methodology. ![]() A circularity just means that the value of a cell is dependent on itself and therefore Excel cannot properly calculate as a result. You may have encountered a circular reference in your modelling before or seen the dreaded pop-up and blue arrows, however you may not necessarily know what a circularity is or why they exist in financial models. This tutorial will demonstrate the best practice approach to implementing such copy-paste macros and discuss potential optimisations which will help you in applying this code robustly and efficiently in different situations. Although VBA is to be avoided where possible in project finance models, due to the inherent circularities in some aspects of project finance calculations VBA is sometimes needed to implement copy-paste functionality in a model in order to ‘break’ these circularities. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |