NPV & IRR with nested Offset Function help required

MegTorb

New Member
Joined
Aug 16, 2018
Messages
3
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!
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,080
Office Version
  1. 2010
Platform
  1. Windows
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.
 
Last edited:

joeu2004

Well-known Member
Joined
Mar 2, 2014
Messages
3,080
Office Version
  1. 2010
Platform
  1. Windows
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

New Member
Joined
Aug 16, 2018
Messages
3
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

Well-known Member
Joined
Mar 2, 2014
Messages
3,080
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,136,314
Messages
5,675,028
Members
419,544
Latest member
MVPDoug

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top