This blog is an extension of our blog on Financial Modeling: How To Perform Sensitivity Analysis Using Two Input Variables.
One of our students asked about how to do Sensitivity Analysis with more than two variables:
In Financial Modeling, our final output is generated with several inputs which are further based on several assumptions. In reality, these assumptions can vary, and one should test the different assumptions to get a range of output possibilities. For this, we need to go for Sensitivity Analysis. Sensitivity Analysis is a very important tool in Financial Modeling. Most of the sensitivity analyses use one or two variable inputs which can be easily done in Excel with data tables. The problem comes when we have more than two variable inputs to check for as there is no direct way to include this in Excel. But there are several indirect ways in which we can achieve the desired analysis. We are going to discuss what we feel is the best way to include more than two input variables below:
Let us take an example; we need to calculate Present Value (PV) of a loan/bond in which we have future value given as $1,000. The other inputs which are Maturity, Yearly Payment, and Interest rate need to be varied for sensitivity analysis. As in Excel, we can handle only two variables, we will first combine two variables and then split for analysis. It is shown in the following snapshot:
In the above picture, we have now two inputs for Excel: One is interest rate and another is combination of Maturity and Yearly payment which we will split for our calculation. Let’s first calculate the PV for this given data. PV has 4 inputs (in our case), first is interest rate (8%), second is maturity (5), third is pmt ($500) and last is FV ($1,000). If we apply this formula (shown below), we will get the present value:
This formula is pretty clear, but the question is how we got the Maturity (B5) and Yearly payment (B6) out of B2 (our input). For this we will be using LEFT, LEN, FIND, and RIGHT; in case we have more than four input variables then we will use MID too. Let us see how we do it in the following screen shot:
As we know that the number left to “/†is our maturity and number right to “/†is our yearly payment. So, to get maturity we will use LEFT function. LEFT asks for two inputs, one is the ‘text’ and till what character you need to cut the left side. As an example if we know that the maturity is always double digit (e.g. 10, 15, 20 etc) then we will use the simple formula as LEFT (B2,2) as it will just cut the left two digits as shown following:
But as we don’t know if the maturity is one digit (5, 7 etc) or different, we will find the number of digits by using FIND function. FIND function searches for the location of the desired text. In our example we will be looking for the position of “/â€. See below:
We got 3, but we need 2; so we will subtract 1 from our result. Thus our final formula is:
Now we have got Maturity, and we need yearly payment. Yearly payment is written on the right side, so we will be using RIGHT to find out the desired result. RIGHT works in a similar fashion as LEFT. It cuts the number of characters of text from the right side. As in LEFT, here also we need to find the number of digits to cut the yearly payment can be in 3 digits or 4 digits or different. So, for that we will again use FIND, but find tells the location from left side. So, to find out the location from Right side we will subtract the left side location from the total length of the text (we use LEN for total length). Please look for details as below:
Now, we have separated the desired results from the input. We now will run the data table which we have learnt in our previous blog (Financial Modeling: How To Perform Sensitivity Analysis Using Two Input Variables.). Following is the result,
Now, it gives sensitivity analysis on 3 variables at any one point of time. The process for the above table is same as of two variable analyses.
Please find the attached excel sheet here for you to play around with!
Comments