# Formula to arrive at interest rate based on Net present Value

#### Nandakumar

##### Member
Hi,

Can you please help me with a formula to arrive at interest rate based on NPV,cashflow and Tenor years:

Cashflow - 20,000 for 11 years
Net Present value - 165,823
Interest rate - X.XXX%

#### Chihiro

##### Excel Ninja
Not enough info to help you. What was the initial investment into the project or cost of capital?

By interest rate... do you mean discount rate? Or do you mean Internal Rate of Return (IRR)?

By definition, IRR is the discount rate at which the NPV of the project is zero.

Have a read of following article to get better understanding of NPV and IRR.

#### Nandakumar

##### Member
Initital Investment is Zero
Yes - Discount rate

Not enough info to help you. What was the initial investment into the project or cost of capital?

By interest rate... do you mean discount rate? Or do you mean Internal Rate of Return (IRR)?

By definition, IRR is the discount rate at which the NPV of the project is zero.

Have a read of following article to get better understanding of NPV and IRR.

#### Chihiro

##### Excel Ninja
So is cashflow total for the 11 year duration. Or is it 20k for each of 11 years?

#### Chihiro

##### Excel Ninja
Assuming cashflow of +20k each year for 11 years...

You can solve this by using Solver tool.

Set up your worksheet as following:
IN B2: =0.05
i.e. estimated Discount Rate

In E3:E13 = 20000 for each cell.

In B3: =NPV(B2,E3:E13)

Use Data -> Analyze -> Solver.
Set Objective: \$B\$3
To: Value of 165823

By Changing Variable Cells: \$B\$2

I used solving method of GRG Nonlinear. Hit solve.

Depending on quality of your initial guess, you may want to refine solver solution, by entering value closer to solution provided.
Ex: By providing 0.05035 as initial guess, you get solution value that will be much closer to target value of 165,823 NPV. #### Nandakumar

##### Member
Thank you so much.Let me try this.