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]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]$20,000.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]$20,000.00[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]10.00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]25.68%[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"]PV[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]PV[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"]$4,796.32[/TD]
[TD="align: right"]$4,796.32[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$6,000.00[/TD]
[TD="align: right"]$6,000.00[/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"]$4,796.32[/TD]
[TD="align: right"]$4,360.29[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$6,000.00[/TD]
[TD="align: right"]$4,774.05[/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: right"]$4,796.32[/TD]
[TD="align: right"]$3,963.90[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$6,000.00[/TD]
[TD="align: right"]$3,798.60[/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: right"]$4,796.32[/TD]
[TD="align: right"]$3,603.54[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$6,000.00[/TD]
[TD="align: right"]$3,022.45[/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: right"]$4,796.32[/TD]
[TD="align: right"]$3,275.95[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$6,000.00[/TD]
[TD="align: right"]$2,404.89[/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: right"]$23,981.59[/TD]
[TD="align: right"]$20,000.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$30,000.00[/TD]
[TD="align: right"]$20,000.00[/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"]NPV[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]NPV[/TD]
[/TR]
</tbody>[/TABLE]

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


[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TH][/TH]
[TH]C[/TH]
[TH]D[/TH]
[TH]H[/TH]
[TH]I[/TH]
[TH]J[/TH]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Wrong![/TD]
[TD="align: right"]Correct[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"]$20,000.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]=C9?[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"]10.00%[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6.37%[/TD]
[TD="align: right"]10.00%[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]-$20,000.00[/TD]
[TD="align: right"]-$15,203.68[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"]$4,796.32[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$4,796.32[/TD]
[TD="align: right"]$4,796.32[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: right"]$4,796.32[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$4,796.32[/TD]
[TD="align: right"]$4,796.32[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: right"]$4,796.32[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$4,796.32[/TD]
[TD="align: right"]$4,796.32[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: right"]$4,796.32[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$4,796.32[/TD]
[TD="align: right"]$4,796.32[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: right"]$4,796.32[/TD]
[TD="align: right"][/TD]
[TD="align: right"]$4,796.32[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: right"]$23,981.59[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

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!