Reverse-engineer NPV to get rate

[hammond3]

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?

Thanks for any advice.

 

[B___P]

Hi hammond3,

put in G9 not in F9, leave it empty

=IRR(F8:F16)

F8 must be negative -20.000

Hope this helps

 

[joeu2004]

	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>

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#.

 

[hammond3]

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

 

[joeu2004]

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%.

	C	D	H	I	J
7			Wrong!	Correct
8	$20,000.00		FALSE	TRUE	=C9?
9	10.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>

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.

 

[hammond3]

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!