Combining ranges/arrays to feed into IRR

Question

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?

Answer 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