1

I want to use the IRR function in Excel; however, my inputs exists in a non-contiguous range.

For example, I want to know the return of an asset over time (each row is a time period).

  1. Column A has the cost (cash outflow) of owning the asset/

  2. Column B is the price of the asset (cash inflow if liquidated).

    • So A1 is cost of owning the asset at time 1, A2 is the cost of owning at time 2 etc.
    • B1 is the liquidation price of the asset at time 1, B2 is the liquidation price of the asset at time 2 etc.
    • In Column C, I would like the return for each time period if I liquidate.

So for example C6 is the return of the asset if I liquidate at period 6, C7 is the return of the asset if I liquidate at period 7 etc. So for C6, the cash outflow would be A1:A5, the cash inflow would be B6.

How do I feed IRR these two inputs?

brettdj
  • 54,857
  • 16
  • 114
  • 177
Agrim Pathak
  • 3,047
  • 4
  • 27
  • 43

1 Answers1

1

From a technique I saw lori_m use

=IRR(IF(1-FREQUENCY(9^9,A1:A5),A1:A5,B6))

As per the simple example below, the FREQUENCYformula is used to mesh together A1:A5with B6. Returning an identical ÌRRto the same cashflows in a continuous range

enter image description here

Community
  • 1
  • 1
brettdj
  • 54,857
  • 16
  • 114
  • 177
  • Can you elaborate what this is doing? – Agrim Pathak Feb 27 '15 at 22:06
  • @agrimpathak Why would you downvote a working answer I took the time to create for your question? – brettdj Mar 22 '15 at 12:33
  • I don't see how this will work, but still I tried and played around with it, and it didn't work. And is this suppose to be an array formula? Also, I think the answer will benefit with a brief explanation. – Agrim Pathak Mar 22 '15 at 13:03
  • It isn't an array formula. It worked as is when I tried it on the same data layout you described. – brettdj Mar 22 '15 at 13:10
  • Thanks, I confirmed it works and accepted it. Though conceptually, how is FREQUENCY combining A1:A5 and B6? – Agrim Pathak Mar 22 '15 at 13:54
  • 1
    Its a little complex ..... `FREQUENCY` is used to look up a very high number against 5 bins (A1:A5) which then returns 5 zeroes (as the data doesnt fit in these bins) and a single 1 (FREQUENCY returns an extra element. So the `IF` part then reads 1-{0,0,0,0,0,1} which then gives five TRUE results (A1:A5) and then 1 FALSE (B6) in a single array of values. – brettdj Mar 22 '15 at 14:02