Modelling an Arrangement Fee Circularity

Ok, in project finance deals you’re going to come across a particularly circularity around calculating senior debt arrangement fees. This blog tutorial will focus on how to model this circularity.

What is an Excel circularity?

In basic terms an Excel circularity is where a calculation refers to itself. In Excel you can allow circularities and the number of iterations which the calculation undergoes; however when you’re modelling and there are a few circularities it is best to break these up.

Why are arrangement fees circular?

Let’s think about this process. Firstly debt arrangement fees are calculated based on the total amount of debt. However debt arrangement fees are used to build up the total construction phase funding requirements, which are funded by debt. So basically as the arrangement fee amount goes up so too does the debt amount, which then causes the arrangement fee to increase. The arrangement fee and debt amount do converge though. Hopefully you see this circularity; however we will look at an example below.

An example of arrangement fees

For this example you will need to open up the pre-populated spreadsheet and youtube video attached to this blog tutorial. Now in this example we assume that all construction costs are fully debt funded. Arrangement fees are 2% of the debt amount and the other construction phase costs are as per the spreadsheet.

Now if we calculate the arrangement fees by multiplying the debt amount by the 2% arrangement fee, you’ll notice that we get a circularity as shown below.

Now to break this circularity we need to hardcode a debt amount. You’ll see why this is important soon. Copy and paste the calculated debt amount into the hardcoded amount as shown below. You’ll need to paste the value so push Home, then the Paste dropdown, then values to do this.

As shown above, create a check by subtracting the hardcoded debt amount from the calculated debt amount. Now when the model is solved this check amount will be zero. Link up the arrangement fee calculation to the hardcoded debt amount. Now, copy and paste values the calculated debt amount into the hardcoded debt amount, until the check amount is zero. This will occur when the arrangement fee converges and model is solved.

If you liked this, check out our free project finance modelling training by signing up to our newsletter.

 

 

Naming Cells and Ranges in Excel

At Video Financial Modelling we like to keep up to date with opinions of other financial modelling and Excel professionals. We had a quick look at an articlehttp://www.fimodo.com/2009/08/3-approaches-to-avoid-complex-off-sheet-references/ which fimodo produced regarding off-sheet references. Whilst fimodo is great and has a lot of fantastic articles, we tended to disagree with the extensive use of named cells and ranges. The remainder of the article was spot on, in terms of both dedicated import lines for each sheet and avoiding multi-level linking.

Now, whilst there are some best practice methods of developing a model which are wide spread within the industry, personal preference does come into financial model development. At Video Financial Modelling we like naming cells and ranges within our models. There are a number of reasons for this; however the main two are model audit and efficiency.

Model Audit

It is much easier to do a simple sense check of a financial model with names. For example if we saw a formula for operating expenditure which says, opex x inf_index x opsind. i.e. this says the operating index multiplied by the inflation index multiplied by the operations indicator. This is much easier than quickly sense checking a formula as follows assG4 x ts33:33 x ts 55:55. This is especially helpful when a third party is looking through your model.

Of course, you have to be careful that the right cells and rows are named, however this should be relatively straightforward and no more onerous than looking up ordinary cell references.

Efficiency

If you start to use standard names regularly in your modelling then you can just type them in rather than going back to the page where the input is on. This is like a shortcut, and should be much quicker than navigating to the actual Excel sheet.

How to name cells and ranges

You can follow along with some of the below examples in the Excel spreadsheet and video tutorial contained in this blog.

Naming Cells

Now this is easy, you can simply put the data in a cell, write a name to the left of the data cell, select the data cell and push CTRL +F3. Follow and accept the prompts and the cell will be named. We also have some other tricks for naming numerous cells at one time which you can find in our Excel Functions training course.

Ok, let’s do an example. Put 3% into a cell, then label it Inf (for inflation) to the left of the data. Click on the data (3%) again and press CTRL+F3. Follow the prompts until you’ve named the cell.

Now if you go onto it, there will be a name in the top left hand of the Excel screen as per below.

Naming Ranges

Put your data in a row. Type a name in say column E of row. Then select the name, press SHIFT+spacebar, to select the row. Once selected press CTRL+F3 and follow the prompts.
Now let’s do an example by naming the inflation index below.

Write InfIndex, select the name, then select the whole row by pushing SHIFT+spacebar

Push CTRL+F3 and follow the prompts to name the row.

User beware – Naming Ranges

Now we do acknowledge that the use of named ranges can end in disaster, if they are not setup correctly. Here are a couple of tips to ensure that you setup named ranges in financial models correctly.

1. You should consistently setup time series or data pages starting in the same column, say G. This will allow labels and totals to the left of the first date.
2. Make sure that you name time data ranges and use the named ranges on other pages. This will ensure the other pages time data will be aligned correctly.
3. Split your time data pages into monthly and quarterly/semi-annual data. Project finance models are usually monthly during construction and quarterly/semi/annual thereafter when debt starts to be paid. Now from Excel 2007,(which has extra columns all the way out to XFD or 16,384 columns!) you could get away with an all monthly financial model, however that is a topic for another day.

Like this video tutorial. Check out our Excel and financial modelling training courses.

Naming cells and ranges in Excel

 

 

At Video Financial Modelling we like to keep up to date with opinions of other financial modelling and Excel professionals. We had a quick look at an article (http://www.fimodo.com/2009/08/3-approaches-to-avoid-complex-off-sheet-references/) which fimodo produced regarding off-sheet references. Whilst fimodo is great and has a lot of fantastic articles, we tended to disagree with the extensive use of named cells and ranges. The remainder of the article was spot on, in terms of both dedicated import lines for each sheet and avoiding multi-level linking.

Now, whilst there are some best practice methods of developing a model which are wide spread within the industry, personal preference does come into financial model development. At Video Financial Modelling we like naming cells and ranges within our models. There are a number of reasons for this; however the main two are model audit and efficiency.

Model Audit

It is much easier to do a simple sense check of a financial model with names. For example if we saw a formula for operating expenditure which says, opex x inf_index x opsind. i.e. this says the operating index multiplied by the inflation index multiplied by the operations indicator. This is much easier than quickly sense checking a formula as follows assG4 x ts33:33 x ts 55:55. This is especially helpful when a third party is looking through your model.

Of course, you have to be careful that the right cells and rows are named, however this should be relatively straightforward and no more onerous than looking up ordinary cell references.

Efficiency

If you start to use standard names regularly in your modelling then you can just type them in rather than going back to the page where the input is on. This is like a shortcut, and should be much quicker than navigating to the actual Excel sheet.

How to name cells and ranges

You can follow along with some of the below examples in the Excel spreadsheet and video tutorial contained in this blog.

Naming Cells

Now this is easy, you can simply put the data in a cell, write a name to the left of the data cell, select the data cell and push CTRL +F3. Follow and accept the prompts and the cell will be named. We also have some other tricks for naming numerous cells at one time which you can find in our Excel Functions training course.

Ok, let’s do an example. Put 3% into a cell, then label it Inf (for inflation) to the left of the data. Click on the data (3%) again and press CTRL+F3. Follow the prompts until you’ve named the cell.

Now if you go onto it, there will be a name in the top left hand of the Excel screen as per below. 

 

 

 

Naming Ranges

Put your data in a row. Type a name in say column E of row. Then select the name, press SHIFT+spacebar, to select the row. Once selected press CTRL+F3 and follow the prompts.

Now let’s do an example by naming the inflation index below.

 

Write InfIndex, select the name, then select the whole row by pushing SHIFT+spacebar

              

Push CTRL+F3 and follow the prompts to name the row.

User beware – Naming Ranges

Now we do acknowledge that the use of named ranges can end in disaster, if they are not setup correctly. Here are a couple of tips to ensure that you setup named ranges in financial models correctly.

1)      You should consistently setup time series or data pages starting in the same column, say G. This will allow labels and totals to the left of the first date.

2)      Make sure that you name time data ranges and use the named ranges on other pages. This will ensure the other pages time data will be aligned correctly.

3)      Split your time data pages into monthly and quarterly/semi-annual data. Project finance models are usually monthly during construction and quarterly/semi/annual thereafter when debt starts to be paid. Now from Excel 2007,(which has extra columns all the way out to XFD or 16,384 columns!) you could get away with an all monthly financial model, however that is a topic for another day.

Like this video tutorial. Check out our Excel and financial modelling training courses.