Reverse a calculation

[stockman]

Here is a challenge for you:


So I have done a DCF (discounted cash flow analysis)

And I get a value of 94$ with a growth rate of 8% from year 1-5 and 7% from 6-10.

Current stock price is 69$.

So how can I show what the growth rate would be if the value from the calculation was at exactly the stockprice 69$?
I can change the growth rate manually so that the value gets at close to the current stock price (69$), but is it a way to get this done automatically?
So that I can see the growth rate the is implied at current stock price?

[AliGW]

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[stockman]

Ok finally I have made it. Hope someone can help

[MrShorty]

A few different strategies available:

1) At the risk of offending the mathophobic, you could try algebra. Many (but not all) problems like this can be solved by writing the expression down, then manipulating the expression to solve for the desired unknown.
2) Your problem sounds like a PV/NPV/FV/IRR type of problem. Since Excel has many of these financial functions preprogrammed in, you might look at the IRR, XIRR and similar functions to see if the inverse function is already preprogrammed into Excel. https://support.office.com/en-us/art...__toc309306711
3) Many problems like this can be solved using Excel's built in Solver or Goal seek utilities. With a spreadsheet like you have, you could tell these to "set the value of B29 to a value of 80 by changing G3".

[stockman]

Since I am a mathophobic solution nr 3 seems like the most useful approach for me. But I have no clue how to do that.

[gwebb]

Go to Data > What If Analysis > Goal Seek

Capture.JPG

[MrShorty]

In addition to gwebb's dialog picture, I would suggest the following help file: https://support.office.com/en-us/art...4-0e24406b7fcb

[stockman]

The problem that arises here is that "to value" is in my case not a static value but can be changing. So when I change "current stock price" then growht rate in g3 should also be changing. Thanks!

[MrShorty]

In those cases, I like to add a cell where I can enter the "to value" (if you don't already have a cell where you enter the 80). Then add a helper cell that is B29-(cell containing 80). Then use this helper cell in Goal, where the Goal Seek is now "Set cell": this helper cell
"to a value of": 0
"by changing": G3
This way, my goal seek/Solver definition never changes (it is always finding 0), and I can change the outcome by changing the cell where I enter my desired target value.

[stockman]

I am sorry but your solution doesent work or doesent make really sense to me. Tried many times.

"In those cases, I like to add a cell where I can enter the "to value" (if you don't already have a cell where you enter the 80)"
G11 is where I enter the "to value" that is where i would enter the current stock price.

"Then add a helper cell that is B29-(cell containing 80)."
Cell b29 is the intrinsic or fair value of a stock and does usually not correspond with the current stock price.

[JohnTopley]

With Current stock Price in G13,

set G14 =B31-G13 ($10) (intrinsic - Stock price)

In Goal Seek

Set cell: G14
To Value: 0
By Changing cell: B5

[stockman]

That goal seek tool. Which cell shall I place it? g14?

[stockman]

But I want the user to not need to start the goal seeking tool. I just want the users of the sheet to be able to see intrinsic value: B31
And see what growth rate it's implied in with todays stock price.

So in the sheet the user should both see the original intrinsic value in b31 (110$) with its 8% growth rate, and also see the implied growth rate with the current stock price.

So user should see:
Intrinsic value: B31 (110$) and its growth rate: 8%
Current stock price of f.example 80$ with its implied growth rate: 3.2%

The user should not need to use the goal seeking tool but get this information automatically as long as the yellow fields has a input.

Hope you understand what I am looking for.

[MrShorty]

That sort of "automatic" calculation is not where Goal Seek works best. That is where the 1st option (algebra) or the 2nd option (Excel function) works best. Since you rejected the algebra approach, did you look through Excel's financial functions to see if one of them represents the desired "reverse calculation"? This looks to me like a standard PV-NPV-FV-IRR type problem, so I would wonder if the IRR() or XIRR() or similar function would perform the desired calculation (see links in list of functions in post #4).

If you are willing to delve into VBA, Goal Seek can be programmed into a Change or Calculate event procedure.

I hesitate to suggest this with your mathophobic tendencies, but I illustrate in this thread how to set these root finding algorithms up in a spreadsheet and bypass the Goal Seek utility: http://www.excelforum.com/tips-and-t...ind-roots.html These approaches usually use "circular references" and "iterative calculations", but they can work very well and are not difficult to set up.