NPV & IRR with nested Offset Function help required

[MegTorb]

I am attempting to understand an NPV/IRR calculation using nested Offset from 2017 to 2027. The formulae are as follows:

NPV(C15,OFFSET(D39,0,0,1,C16-1))+C39

Where C15 = WACC, D39 = 2018 net annual benefits, C39 = 2017 net annual benefits & C16 = valuation years

IRR(OFFSET(C39,0,0,1,C16))

Where C39 = 2017 net annual benefits & C16 = valuation years

I have never used an NPV or IRR with an offset before and am confused as to what values they are picking up as they are not looking at the net annual benefits over the 2017 to 2027 period.

Any help is appreciated!

 

[joeu2004]

We might be more helpful if you provided more complete and concrete data; actual numbers. Also, tell us how you intend to use the formulas, and how you know "they are not looking at" 2017 to 2027.

For example, your usage implies that there is much more in column D (after D39) than just "2018 net benefits". I presume you mean that D39:D48 are net benefits for 2018 to 2027. At least, that is what your NPV formula implies.

But your IRR formula implies that C39:C48 contain something, presumably the net benefits for 2018 to 2027. That would seem odd to duplicate the data.

Also, I presume you are aware of the need for signed cash flows. So C39 is negative and D39:D48 are positive, or vice versa. Right?

Finally (not really!), if you intend to copy the formulas, perhaps you need some judiciously placed absolute references; for example, $C$39 and $D$39.

All of these details and more are needed for us to help you in any way.

 

[joeu2004]

Errata.... I misread your OFFSET expression. I presume that C39:M39 contains the net benefits for 2017 to 2027. Thus, your NPV and IRR expressions are consistent.

Nevertheless, the gist of my comments still stand: you should provide more concrete information and more explanation of what seems to be wrong. (Although the latter might be self-evident when you do the former.)

 

[MegTorb]

Thanks...forum novice!

2017

 

[MegTorb]

Apologies - I am truly a neophyte - More information

C D E F G H I J K L M

2017 2018 2019 2020 2021 2022 2023 2024 2025 2026 2027
39 annual benefits -261,272 0.00 123,991 123,818 145,405 164,270 180,768 195,208 207,861 218,959 228,708
40 cum ann benefits -261,272 -261,272 -137,281 -13,463 131,942 296,212 476,980 672,188 880,049 1,099,008 1,327,716

This is the information that is being looked at, with cell references to C15 being the WACC of 11%, and cell reference of C16 being the economic years (5)

NPV($C$15,OFFSET(D39,0,0,1,$C$16-1))+C39 = $25,680
IRR(OFFSET(C39,0,0,1,$C$16)) = 14.6%

I am attempting to understand how the NPV and Offset are working together to get these outputs

 

[joeu2004]

If you use the Formulas > Evaluate Formula feature, you should be able to answer the question yourself.

OFFSET(D39,0,0,1,C16-1) returns a range of C16-1 cells in a row, starting with D39. With C16=5, this is equivalent to NPV(C15,D39:G39)+C39.

Likewise, OFFSET(C39,0,0,1,C16) returns a range of C16 cells in a row, starting with C39. With C16=5, this is equivalent to IRR(C39:G39).

That should answer your question.

------

Aside.... IMHO, it is better to use:

=NPV(C15,D39:INDEX(D39:M39,1,C16-1))+C39
and
=IRR(C39:INDEX(C39:M39,C16))

The effect is the same, to wit: we are specifying variable-length ranges of C16-1 and C16 cells.

But OFFSET is a "volatile" function. Consequently, NPV and IRR are recalculated every time any cell in the workbook is edited.

INDEX is not a "volatile" function.

It make no perceptible difference if you have only the one IRR (and NPV) formula of that form.

But as a matter of principle, it is prudent to avoid unnecessary calculations, because in some contexts, it can cause noticable delays when modifying an Excel workbook.

These forums are fully of inquiries about why Excel becomes "non-responding", why Excel "loops" for 10 min, or why Excel causes 100% utilization of one or more CPUs. The use of "volatile" functions is often the answer.