# Reverse-engineer NPV to get rate

#### hammond3

##### New Member
Hi

I can't find any information on how to do this other than through some sort of goal-seek. Ideally I'd like a formula-based solution, if one exists.

Basically I need to 'reverse-engineer' the NPV calculation to find the rate. I've illustrated my problem in the image below (I'm trying to find the value in the yellow-highlighted cell).

I have two ways of selling a product with equal annual cashflows:

• Option A assumes a cost of capital of 10% and calculates the five even annual cashflows required to reach a target NPV (£20k).
• Option B starts with a given figure for total cashflows (£30k in this example) and divides that by five to get equal annual cashflows. I want to know the effective cost of capital / interest rate applied to make the NPV of this option the same as option A (£20k). Is this possible?

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Hi hammond3,

put in G9 not in F9, leave it empty

=IRR(F8:F16)

F8 must be negative -20.000

Hope this helps

 C D E F G 8 \$20,000.00 \$20,000.00 9 10.00% 25.68% 10 PV PV 11 \$4,796.32 \$4,796.32 \$6,000.00 \$6,000.00 12 \$4,796.32 \$4,360.29 \$6,000.00 \$4,774.05 13 \$4,796.32 \$3,963.90 \$6,000.00 \$3,798.60 14 \$4,796.32 \$3,603.54 \$6,000.00 \$3,022.45 15 \$4,796.32 \$3,275.95 \$6,000.00 \$2,404.89 16 17 \$23,981.59 \$20,000.00 \$30,000.00 \$20,000.00 18 NPV NPV

<tbody>
</tbody>
Rich (BB code):
``````Formulas:
C11: =PMT(C9,5,-C8,0,1)
C12: =C\$11
C17: =SUM(C11:C15)
D11: =C11 / (1+C\$9)^(ROWS(C\$11:C11)-1)
D17: =SUM(D11:D15)
Copy C12 into C13:C15
Copy D11 into D12:D15

F9:  =RATE(5,F11,-F8,0,1)
F11: =F17 / 5
F12: =F\$11
G11: =F11 / (1+F\$9)^(ROWS(F\$11:F11)-1)
G17: =SUM(G11:G15)
Copy F12 into F13:F15
Copy G11 into G12:G15``````

Note that columns D and G are not part of the solution. They are provided to demonstrate the correctness of the calculations in columns C and F.

Also note that because cash flows are at the start, not the end, of each period, they are discounted by the period#-1 instead of by the period#.

Last edited:
Thanks so much both for your helpful replies - that's brilliant! Simple and effective as all good solutions are.

The IRR solution is incorrect, as written. It fails to take payment "in advance", your situation, into account. It is easy to prove: simply apply it to Option A, which we know should have a discount rate of 10%.

CDHIJ
7Wrong!Correct
8\$20,000.00FALSETRUE=C9?
910.00%6.37%10.00%
10
11-\$20,000.00-\$15,203.68
12\$4,796.32\$4,796.32\$4,796.32
13\$4,796.32\$4,796.32\$4,796.32
14\$4,796.32\$4,796.32\$4,796.32
15\$4,796.32\$4,796.32\$4,796.32
16\$4,796.32\$4,796.32
17
18\$23,981.59

<tbody>
</tbody>
Code:
``````C12: =PMT(C9,5,-C8,0,1)
C13: =\$C\$12
C18: =SUM(C12:C16)
Copy C13 into C14:C16

H8:  =H9=\$C\$9
H9:  =IRR(H11:H16)
H11  =-C8
H12: =C12
Copy H12 into H13:H16

I8:  =I9=\$C\$9
I9:  =IRR(I11:I15)
I11: =-C8+C12
I12: =C13
Copy I12 into I13:I15``````

Column H shows the original IRR solution. It fails to reproduce the known rate of 10%. It would be correct for payments "in arrears".

(BP wrote, effectively, IRR(H8:H16), with -20000 H8. Assuming that BP left H9:H11 empty, the result is the same as my IRR(H11:H16).)

Column I shows the correct IRR solution for payments "in advance". As I noted in my first response, the key difference for payments "in advance" is: (a) the first payment is not discounted; in effect, it is added to the initial amount (-NPV) in I10; and (b) all subsequent payments are discounted as if they occurred at the end of the previous period.

Although column I demonstrates that we could use IRR, it is unnecessary. As I demonstrated in my first response, because payments are equal and they occur at a regular frequency, we can use RATE, which provides are more compact solution.

Last edited:
Column I shows the correct IRR solution for payments "in advance". As I noted in my first response, the key difference for payments "in advance" is: (a) the first payment is not discounted; in effect, it is added to the initial amount (-NPV) in I10; and (b) all subsequent payments are discounted as if they occurred at the end of the previous period.

Although column I demonstrates that we could use IRR, it is unnecessary. As I demonstrated in my first response, because payments are equal and they occur at a regular frequency, we can use RATE, which provides are more compact solution.

Indeed - I noticed this myself when I tested it (and had to add the first payment to the NPV to get the correct result). I was interested to see that answer though as I had originally tried using an IRR calculation to get the answer but I was using the wrong figures so never managed it.

I ended up using your solution in my model as it was a better fit for the rest of the calculations I'm using. Thanks!

Replies
2
Views
3K
Replies
7
Views
8K

1,196,429
Messages
6,015,198
Members
441,882
Latest member
rcgyuk

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back