@Hussein_Mohamed wrote: ``considering the different dates``
What do you mean by that?
If you are considering mixing monthly data in columns G:U and annual data in columns X:AJ, I would discourage that due to the complexities of using XIRR and XNPV. See below.
But if you just want to use the annual data as you highlighted before, the implementation of the IRR and NPV formulas are much simpler. They are similar to my previous formulas, but they can be simplified because your complete spreadsheet has some of the calculations that I had to add.
Refer to the following image. See details in the attached Excel file.
For the IRR and NPV of the annual data, we only need the changes highlighted in green.
For the IRR from the end of Dec/22, we need the negative of the initial balance (E29). So E30 has the formula =-E29.
Then the formulas are:
C36, IRR: =IRR( (E30, W28:AI28, AJ29) )
D36, NPV check: =NPV(C36, W28:AI28, AJ29) - E29
C37, NPV@12%: =NPV(12%, W28:AI28, AJ29) - E29
The NPV check is optional. It is provided for "proof of concept".
Note that for the NPV, I write "- E29" instead of "+ E30". The point is: if we only want to calculate the NPV, we do not need the negative initial cash flow in E30. We can subtract the initial cash flow instead of adding it.
If that is all you wanted, you can stop reading here.
-----
With the additional data, I wonder if you want to calculate the IRR and NPV from the beginning of Dec/22, instead from the end. That would differ from your original posting.
If so, it is unclear to me what the initial investment is: E3, E6 or E10.
(Even though E6 and E10 are currently the same, there seems to be the potential for them to be different.)
To help me understand please answer the following questions.
1. Please explain the abbreviations Disc, Inst and Deli, and please explain Returned.
2. Why do you add (!) "expected" to "actual"?
3. Did the values in E3 and perhaps E4 occur at the beginning of Dec/22? Or did they occur over the course of the month?
4. The bottom line: what value(s) do you consider to be the "initial investment"?
-----
If you want to mix the monthly and annual data, that would require that we use XIRR and XNPV.
I would discourage that for several reasons:
1. In general, XIRR and XNPV provide misleading "accuracy" insofar as they rely on the exact days between cash flows.
So, for example, the same monthly 12-month cash flows in a leap year has a slightly different IRR than in a non-leap year. Likewise, the same 12-month cash flows that start in Jan have a different IRR than if they start in July.
2. For that reason, we must choose the cash flow dates carefully. For example, instead of the first of the month as you used, the dates should be the end the month.
3. In general, XIRR is less reliable than Excel IRR. However, it seems to work fine with your data.
4. The use of XIRR and XNPV requires all of the changes highlighted in yellow in order to meet all of their requirements.
XIRR and XNPV require two contiguous ranges of data. That requires that we avoid the extraneous data in your design. See rows 32 and 33.
(Ostensibly, these helper cells might be avoidable by using new features of Office 365 and Excel 2019. I don't know. But beware: they must adhere to all of the requirements below.)
For XIRR, we could just leave unwanted columns blank; for example, F32 and F33.
But for XNPV, we must have a valid date and a value of zero. Since the date can be arbitrary (but not earlier than the PV date), I use the date of the previous cell; for example, the formula =E32 in F32.
Note: We could use cell formatting to make those columns appear to be blank. I chose not to do that in order to expose the requirement.
Since the monthly data includes Dec/22, we must exclude the 2023 annual cash flow in W28.
With those changes, the formulas are:
C40, XIRR: =XIRR(E33:AJ33, E32:AJ32)
D40, XNPV check: =XNPV(C40, E33:AJ33, E32:AJ32)
F40, XNPV rel err: =D40 / MAX(ABS(E33:AJ33))
C41, XNPV@12%: =XNPV(12%, E33:AJ33, E32:AJ32)
The XNPV check and rel err (relative error) are optional. They are provided for "proof of concept".
The rel err demonstrates that the NPV is indeed "relatively close to zero" relative to the largest cash flow, an arbitrary choice. Note that the formula in F40 must be array-entered (press ctrl+shift+Enter instead of just Enter) in some versions of Excel.
FYI, I confirmed the XIRR by using Solver. The Solver IRR is only infinitesimally smaller than the XIRR, but the XNPV with the Solver IRR is truly close to zero.
Accepted Solutions
@Hussein_Mohamed wrote: ``considering the different dates``
What do you mean by that?
If you are considering mixing monthly data in columns G:U and annual data in columns X:AJ, I would discourage that due to the complexities of using XIRR and XNPV. See below.
But if you just want to use the annual data as you highlighted before, the implementation of the IRR and NPV formulas are much simpler. They are similar to my previous formulas, but they can be simplified because your complete spreadsheet has some of the calculations that I had to add.
Refer to the following image. See details in the attached Excel file.
For the IRR and NPV of the annual data, we only need the changes highlighted in green.
For the IRR from the end of Dec/22, we need the negative of the initial balance (E29). So E30 has the formula =-E29.
Then the formulas are:
C36, IRR: =IRR( (E30, W28:AI28, AJ29) )
D36, NPV check: =NPV(C36, W28:AI28, AJ29) - E29
C37, NPV@12%: =NPV(12%, W28:AI28, AJ29) - E29
The NPV check is optional. It is provided for "proof of concept".
Note that for the NPV, I write "- E29" instead of "+ E30". The point is: if we only want to calculate the NPV, we do not need the negative initial cash flow in E30. We can subtract the initial cash flow instead of adding it.
If that is all you wanted, you can stop reading here.
-----
With the additional data, I wonder if you want to calculate the IRR and NPV from the beginning of Dec/22, instead from the end. That would differ from your original posting.
If so, it is unclear to me what the initial investment is: E3, E6 or E10.
(Even though E6 and E10 are currently the same, there seems to be the potential for them to be different.)
To help me understand please answer the following questions.
1. Please explain the abbreviations Disc, Inst and Deli, and please explain Returned.
2. Why do you add (!) "expected" to "actual"?
3. Did the values in E3 and perhaps E4 occur at the beginning of Dec/22? Or did they occur over the course of the month?
4. The bottom line: what value(s) do you consider to be the "initial investment"?
-----
If you want to mix the monthly and annual data, that would require that we use XIRR and XNPV.
I would discourage that for several reasons:
1. In general, XIRR and XNPV provide misleading "accuracy" insofar as they rely on the exact days between cash flows.
So, for example, the same monthly 12-month cash flows in a leap year has a slightly different IRR than in a non-leap year. Likewise, the same 12-month cash flows that start in Jan have a different IRR than if they start in July.
2. For that reason, we must choose the cash flow dates carefully. For example, instead of the first of the month as you used, the dates should be the end the month.
3. In general, XIRR is less reliable than Excel IRR. However, it seems to work fine with your data.
4. The use of XIRR and XNPV requires all of the changes highlighted in yellow in order to meet all of their requirements.
XIRR and XNPV require two contiguous ranges of data. That requires that we avoid the extraneous data in your design. See rows 32 and 33.
(Ostensibly, these helper cells might be avoidable by using new features of Office 365 and Excel 2019. I don't know. But beware: they must adhere to all of the requirements below.)
For XIRR, we could just leave unwanted columns blank; for example, F32 and F33.
But for XNPV, we must have a valid date and a value of zero. Since the date can be arbitrary (but not earlier than the PV date), I use the date of the previous cell; for example, the formula =E32 in F32.
Note: We could use cell formatting to make those columns appear to be blank. I chose not to do that in order to expose the requirement.
Since the monthly data includes Dec/22, we must exclude the 2023 annual cash flow in W28.
With those changes, the formulas are:
C40, XIRR: =XIRR(E33:AJ33, E32:AJ32)
D40, XNPV check: =XNPV(C40, E33:AJ33, E32:AJ32)
F40, XNPV rel err: =D40 / MAX(ABS(E33:AJ33))
C41, XNPV@12%: =XNPV(12%, E33:AJ33, E32:AJ32)
The XNPV check and rel err (relative error) are optional. They are provided for "proof of concept".
The rel err demonstrates that the NPV is indeed "relatively close to zero" relative to the largest cash flow, an arbitrary choice. Note that the formula in F40 must be array-entered (press ctrl+shift+Enter instead of just Enter) in some versions of Excel.
FYI, I confirmed the XIRR by using Solver. The Solver IRR is only infinitesimally smaller than the XIRR, but the XNPV with the Solver IRR is truly close to zero.