CODEWITHSUNDEEP
excelexcel-formulairr
Agrim Pathak
Agrim Pathak

Reputation: 3207

Combining ranges/arrays to feed into IRR

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?

Upvotes: 1

Views: 2025

Answers (1)

brettdj
brettdj

Reputation: 55692

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

Upvotes: 1

Related Questions