Hi there, this is a follow-on to an earlier video
on the XLOOKUP function across worksheets.
Our business unit companies have various
financial data, which we can take to calculate
the IRR using the XIRR formula in Excel.
Like this previous video,
we want to calculate the respective IRR
for each entity on the summary sheet.
Firstly, let's compute each worksheet's
IRR using the XIRR formula -
because our time series isn't uniform.
Given our sheet layouts and cell ranges are the
same, we can use the Group feature
to simultaneously derive all IRRs like so.
This quick exercise will help validate
the IRR numbers presented on the summary
sheet are working in this exercise.
We can start composing the IRR
for Alpha on the Summary sheet.
Like the XLOOKUP video, we must correctly
apply the INDIRECT function for each syntax
element of the formula, as we need to
reference the respective range
in the source Alpha worksheet.
It is vital to correctly type these
formulas accurately, otherwise the dynamic
XIRR formula won't work.
This first part of the formula references
row 8 containing the EBITDA numbers
across the time series.
The second part needs to refer to the
time series of the source data set; in order to
calculate the Internal Rate of Return.
Please take your time writing these formulas,
because it is very easy to mistype
the syntax in these INDIRECT formulas.
As you can see row 3 in the Alpha sheet
contains the time series, which this
second INDIRECT function must reference.
Our formula is complete.
Let's fill down the dynamic XIRR formula.
In summary, I will apply the FORMULATEXT
to capture the syntax of the XIRR
INDIRECT formulas for your reference.
Let's check our formulas against the
individual worksheet formulas of XIRR.
Our Internal Rate of Return
formulas are working correctly.