XIRR CALCULATION WITH BLANK CELLS

Leo1999

New Member
Joined
Dec 7, 2023
Messages
1
Office Version
  1. 365
Platform
  1. Windows
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:

1701982780794.png

1701982803043.png


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.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Welcome to the Forum!

Here's one way you could do this:

ABCD
112 Apr 2018-750,000
231 Aug 2018-500,000
31 Mar 2019-500,000
430 Dec 201912,971
55 Jun 2020-500,000
610 Dec 2020-400,000
7  
8  15.29%
9  
10  
11  
1230 Sep 20235,000,000
13
Sheet1
Cell Formulas
RangeFormula
A7:B11A7=""
C8C8=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)
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

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
Back
Top