Useful Excel Functions For Financial Modeling And Data Analysis

Goal Seek and Data Table function in excel are important tools used for financial modeling and data analysis.

“Goal Seek” in Excel is a function to back-calculate the input so as to get the desired output. It is very useful when one knows the desired result but does not know the inputs to reach the desired result. We can also refer to it as back solving function.

Let’s understand with the help of a simple example.

Mr. John has USD 100,000/- in his bank account for investment today. He wants to invest this amount in such a way that he can double it in 10 years. The tax rate in this example is 0%.

Here are the details.

First, let us calculate the amount Mr. John gets if he invests it in the Bank deposit.

This can be calculated with the help of the following formula

This means that Mr. John will get USD 148,024 after 10 years if he invests it in a Bank deposit. This amount is far less than his original requirement (ie; USD 200,000).

The question is, what is the required rate of return to double up his initial investment. We can either help Mr. John calculate this amount by doing several iterations manually or we can calculate it quickly with the help of “Goal Seek” Function in excel.

To use “Goal Seek” first calculate the return from the amount invested in a bank deposit in Excel (as already done above).

Select cell D6 (which is the output cell in this example). Then Go to Data in the Menu Bar, Select What-if analysis, then select Goal Seek from the drop-down menu.

You get the following Goal Seek arguments (see below)

The first argument (ie; Set cell) is already filled in, as we have already selected cell “D6”

In the second argument, To Value enter 200,000 (as Mr. John wants to double his initial investment after 10 years).

For the third argument, By changing cell, select Cell D4 (as this is the required input, to get the desired output).

See Below.

Then select OK.

Goal Seek calculates the rate of return of 7.2% to get USD 200,000/- required by Mr. John. That means that the rate of return has to be at least 7.2% to double the investment amount.

In Excel, we can also do the scenario analysis with the help of the “Data table” function.

Let us understand how to use it.

Use of Data Table with Single variable.

In our above example, Mr. John wants to analyze the maturity amount at each 1% increase in the rate of return from 3% to 10%.

We can either calculate it manually or create an automated table with the help of the “Data Table” Function in Excel.

Let us discuss, how to use it.

In Excel, first, calculate the maturity amount at the bank deposit rate (as already done in the above example).

Now, enter the rate of returns at which Mr. John wants to see the maturity amount in the spreadsheet (as done below).

Now, in the next column just above the entered rate of returns (cell D8 in this example) type = D6 (as done below). You get the maturity amount there (which is USD 148,024).

Now select the table range, Cell C8: D16.

Then Go to Data Tables, Select What-if analysis, Then Select “Data Table” from the drop-down menu.

You get the following argument

Leave Row input cell as blank,

In column input, select cell D4 (Rate of return in this case). Then Press OK.

You get the following table.

This table shows the maturity amount at different interest rates.

If the required amount after 10 years is USD 200,000/-, then Mr. John will have to invest at a rate between 7–8%.

Use of Data Table with Two variables.

We can also use the Data Table function in excel with two variables.

Let us assume that,

Mr. John want to analyze the maturity amount;

- At each 1% increase in the rate of return from 3% to 10%,

- At each 1 year increase in period of investment 10 to 15 years.

In this case, we need to create a data table with 2 variables, where the x-axis is the period of investment, and the y-axis is the rate of return (see below). Link Cell C9 to the desired output (Maturity amount in this case).

Now Select range C8: I17, Then Go to Data Tables, Select What-if analysis, Then Select “Data Table” from the drop-down menu (see below).

In the Data Table Argument, Link Row Input cell to Tenure (ie; Cell D5) and Column input Cell to Rate of return (Cell D4).

Press OK. You get the following Table.

On analyzing the data, we see that Mr. John has to invest at >7% rate of return if the period of investment is 10 years. As the period increases the required rate of return decreases in order to double his investment amount.

That means that the maturity amount is correlated to both rates of return and tenure of the investment.

To learn more about financial modeling excel analysis, valuation and modeling techniques, you can check out the online training financial modeling course The Financial Analyst Skills Training (FAST) here

Hope you enjoyed learning. Have fun.