XIRR CALCULATION WITH BLANK CELLS

[Leo1999]

Hello all,

I am trying to calculate an XIRR based on a series of dates and cash flows, the issue I am running into is that the blank cells are producing a !value error which I am trying to overcome.

I am populating 2 separate columns based on a distribution/capital call table which is pulling the dates and producing a blank cell if there is no date
=IF(J95="netted from next call","",IF(OR(F95="Capital call",F95="both",F95="distribution",F95="deemed"),IF(J95=0,I95,J95),""))


Same procedure with the cash amounts
=IF(K38=0,"",K38)

The bottom row is a pull for a quarter end date and current NAV values as seen below:

I am hoping there is a way to auto populate the formula so that the XIRR function will pull correctly based empty rows between the latest cashflows/dates and final cashflow/date.

 

[StephenCrump]

Welcome to the Forum!

Here's one way you could do this:

	A	B	C	D
1	12 Apr 2018	-750,000
2	31 Aug 2018	-500,000
3	1 Mar 2019	-500,000
4	30 Dec 2019	12,971
5	5 Jun 2020	-500,000
6	10 Dec 2020	-400,000
7
8			15.29%
9
10
11
12	30 Sep 2023	5,000,000
13
Sheet1

Cell Formulas
Range	Formula
A7:B11	A7	=""
C8	C8	=XIRR(IF(B1:B12="",0,B1:B12),IF(A1:A12="",0,A1:A12))

(This assumes your blank cells line up properly. A non-zero number with a blank date will produce an error)