• 100% Satisfaction Guarantee
Lane, JD, CFP, MBA, CRPS
Category: Finance
Satisfied Customers: 11592
Experience:  Law Degree, specialization in Tax Law and Corporate Law, CFP and MBA, Providing Financial & Tax advice since 1986
1929974
Lane is online now

# Im looking for a formula or function that allows you to input

I'm looking for a formula or function that allows you to input a desired NPV, a term in years, a discount rate and a starting cash flow in year 0...and solve for what rate does cash flow in year 0, need to grow every year for the term, at the discount rate to achieve the NPV specified????

olwagner :

olwagner :

Also, once you have such a spreadsheet, you can use the solver to get the discount rate that will give you a given NPV (for instance)

Customer:

This is not exactly what I'm looking for. I am well aware how to calculate NPV if I input cash flows and use a discount rate. What I'm trying to do is solve for a growth rate that gets me a particular NPV given a term and discount rate. I'm trying to take what I've given you and simplify the equation to X =

Customer:

(30,000 / (12% - X)) * (1 - ((1+X)^10 / (1+12%)^10)) = 375,000 needs to get simplified to X = .....

olwagner :

Make X another cell, then use the solver. I'm using Office 2003 as we speak, so things may be different if you have another version (use Excel help to get the solver).
In 2003: If Solver is not installed, use Tools => Add-ins to install it.
Then, Tools => Solver ; Set Target cell (the cell in which you have NPV), Value of (what you want NPV to be), by changing cells (the cell in which you have the discount rate. And it will solve for the discount rate you're looking for.

olwagner :

Alternatively, you may use "Goal Seek" (in Tools => Goal Seek), which is a simplified version of the solver.

Customer:

Both Solver and Goal Seek will work if I input the target value (in this example the NPV of 375,000 - however I don't want to input it every time. I want to be able to type in a number in a cell and it automatically return the value I need - this doesn't happen with goal seek or solver. This is why I want an equation for X = . that way I won't need to use any fancy excel function and can just solve for x with any level of inputs. Solver and Goal seek help but doesnt get at the meat of it. This is not worth \$30

Hello,

I’m Lindie, and I’m a moderator for this topic. Thank you for your patience, your business is very important to us; we are waiting on another professional with the right expertise to come online. Feel free to let us know if you would like us to continue searching for a professional or if you would like us to close your question. We appreciate your understanding!

Best,

Lindie

Customer: replied 3 years ago.

Hello,

Thank you, XXXXX XXXXX continue to look for a professional to assist you. Please let me know if I can be of any further assistance while you wait.

Best,

Lindie

What you're trying to do can't be done.

... not simply by solving, simplifying to, x.

The rate cash flow in year 0, needs to grow every year for the term will be different for every possible 1st cash flow and different for every possible term.

You're oversimplifying, (pun intended).

You need a differential equation here ... COULD be done if you use diffyQ AND specify some ranges.

Lane, JD, CFP, MBA, CRPS
Category: Finance
Satisfied Customers: 11592
Experience: Law Degree, specialization in Tax Law and Corporate Law, CFP and MBA, Providing Financial & Tax advice since 1986