SOLVED

# Formula help

Brass Contributor

# Formula help

Dears,

Could you please assest regarding the attached IRR & NPV function for the highlighted cells only.

Thanks

10 Replies

# Re: Formula help

Without opening your file for personal security reasons, here is a solution suggestion.

To calculate the Internal Rate of Return (IRR) and the Net Present Value (NPV) at a 12% discount rate for the given cash flows, you can use Excel's built-in formulas. Here's how you can do it:

1. In an empty cell, enter the cash flows for each period. Based on the data you provided, it seems like the cash flows are listed under the "Net" column from Till Dec 2022 to Dec 2036. Let's assume these cash flows are in cells B2 to AF2.
2. To calculate the IRR, use the following formula:

=IRR(B2:AF2)

Enter this formula in a blank cell, and it will return the IRR percentage for the cash flows.

1. To calculate the NPV at a 12% discount rate, use the following formula:

=NPV(12%,B2:AF2)

Enter this formula in a blank cell, and it will return the NPV value at a 12% discount rate for the cash flows.

Make sure the cash flows are entered correctly in the formula and cover the entire cash flow period. Adjust the range in the formulas accordingly if your data is in a different range.

Note that the IRR and NPV calculations assume that the cash flows are evenly spaced over the given periods. Also, keep in mind that the IRR is the discount rate at which the NPV equals zero, and the NPV represents the present value of cash flows discounted at a specific rate.

# Re: Formula help

From IRR function:

"Values must contain at least one positive value and one negative value to calculate the internal rate of return."

# Re: Formula help

The attached figures are an assumption not the actual figures. If i want to skip column from the irr function what should i do

# Re: Formula help

Thank you Mr. NikolinoDE for your assist, if i have a data

A  B  C  D  E  F  G  H  I

1  4  5   8   11   12  14

and i need to apply the IRR function on all cullmns except culumn E

# Re: Formula help

@Hussein_Mohamed  wrote:  ``i need to apply the IRR function on all cullmns except culumn E``

Providing abstract numbers is meaningless and not helpful.

Returning to your original file, you ask for the IRR and NPV @ 12% for the values in C4 and T4:AG4.

For the purposes of the IRR, the initial CF must be negative (in this case).  Enter the formula =-C4 into C5.

Also, I suspect the values in T4:AG4 are (cumulative) ending balances.  But the IRR calculation requires cash flows, namely:  initial balance, intermediate external changes in cash, and final balance.

To that end, enter the formula =T4-C4 into T5 and the formula =U4-T4 into U5.  Copy U5 into V5:AF5.

Then, the IRR in B7 is =IRR( (C5, T5:AF5, AG4) )

Note the extra set of parentheses around the range list.  That is called a range union.  It is allowed as a parameter for only a few functions.  IRR is one of them.

The IRR is 42.9540304963006%.

That can be confirmed by the fact that =NPV(B7,T5:AF5,AG4)+C5 returns exactly zero (or sometimes only relatively close to zero).

For the NPV at 12%, use the formula =NPV(12%, T5:AF5, AG4) + C5

Note that the (signed) initial balance in C5 is added to NPV(...).  It is not the first parameter because Excel NPV discounts all cash flows, including the first one.  In other words, it assumes the first cash flow is in time 1, and it discounts to time 0.  But I presume that C5 is time 0.

The NPV @ 12% is 147,150,643.873167, which is displayed as 147,151 if you copy the format of C4, Custom #,##0,

# Re: Formula help

First of all, i would like to thank you for the expnation you have been provided. and i have attached the full details of data in order to be clear to understand. so i would be happy to get your help to perform the formula (IRR - NPV) considering the different dates.

Thank you Mr. Joe

best response confirmed by Hussein_Mohamed (Brass Contributor)
Solution

# Re: Formula help

@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.)

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.

# Re: Formula help

first of all, i am hubled by your generosity and the time and effort you have invested in helping me.

1. Please explain the abbreviations Disc, Inst and Deli, and please explain Returned.

------ Discription - Installments (collected amount) - Deliver payments ( collected amounts)- returned (collected amount of returned checks.

2. Why do you add (!) "expected" to "actual"?

------ i have to add the collected amounts generated from actual sales & forecasted sales)

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?

-------- just commulative for the collection since the begining of 2019 till the end of dec 2022

4. The bottom line:  what value(s) do you consider to be the "initial investment"?

-------- for the initial investment i have confused of what consider the initial investment should be is it the collection amount of sold units as i work in a real estate company and i have collect the amounts before the begining of constructions and delivering the unit sold or i should consider the initial investment is the capital injections (please advice)

Finaly i would appreciate any advices you will provide to do this at the optimum way.

Thanks

# Re: Formula help

@Hussein_Mohamed  wrote:  ``i have confused of what consider the initial investment should be``

Considering that we do not know the balance of the account at the beginning of Dec/22 (i.e. the end of Nov/22), I think you are right to calculate the IRR and NPV@12% only from the end of Dec/22.

Just understand that that is not the IRR and NPV@12% of "the project", which you say began with the collection of funds starting at the beginning of 2019.

Disclaimer:  I do not have experience with calculating the IRR and NPV of real estate projects per se.  I am relying on my interpretation of your descriptions, and I am not confident of my understanding of them.  If this is critical to your business operation, I suggest that you find a professional who claims to do this kind of analysis.

# Re: Formula help

Thank you Mr. Joe
1 best response

Accepted Solutions
best response confirmed by Hussein_Mohamed (Brass Contributor)
Solution

# Re: Formula help

@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.)

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.