# XIRR calculation to date and 6 month

#### rsd007

##### Member
Hello,

I have been trying to work on this project from some time as I am not strong with Excel. so, trying to get some help from EXCEL GURU's.
Sheet with multiple entries in a month for adding money or taking money out. When try to calculate with limited EXCEL Knowledge I get #VALUE or #NUM.
really appreciate if you Gurus can help me.

#### Attachments

• 12.1 KB Views: 6

#### joeu2004

##### New Member
The #VALUE error arises because the ranges \$A1:\$A\$4 and \$B1:\$B\$4 reference row 1, which contains text that Excel cannot interpret as a number.

Ostensibly, change them to \$A2:\$A\$4 and \$A2:\$A\$4

But that just exposes another instance of the #NUM error.

-----
The #NUM errors arise for a different reason for each formula.

With the correction above, the formula in E3 becomes:

=IF(\$A3="", " - ", XIRR(IF(\$A2:\$A\$4=\$A3, -(\$C3-\$B3), \$B2:\$B\$4), \$A2:\$A\$4, 0.1))

If we highlight the first parameter and press f9 in the Formula Bar, it becomes:

=IF(\$A3="", " - ", XIRR({ 20; 20; 3390.56 }, \$A2:\$A\$4, 0.1))

(Be sure to press Esc to restore the original formula.)

Note that all of the values are positive. (I'll discuss the reason why below.)

XIRR requires at least one negative and one positive value.

-----
Similarly for E4 and E5, if we highlight the first parameter and press f9 in the Formula Bar, the formulas become:

E4: =IF(\$A4="", " - ", XIRR({ 0; 0; 3390.56 }, \$A2:\$A\$4, 0.1))
E5: =IF(\$A5="", " - ", XIRR({ 0; 3390.56 }, \$A3:\$A\$4, 0.1))

Note there is only one non-zero value.

XIRR requires at least two non-zero values.

And FYI, another error is: the first cash flow is zero. XIRR does not allow that (for no good reason, IMHO).

But instead of returns a bona fide Excel error, XIRR returns a bogus numerical result (typically plus or minus 2.98E-9, which is usually displayed misleadingly as zero because of how the XIRR is typically formatted (Percentage, not Scientific).

-----
Finally, the #N/A error arises because the ranges are comprised of only one row, to wit:

=IF(\$A6="", " - ", XIRR(IF(\$A4:\$A\$4=\$A6, -(\$C6-\$B6), \$B4:\$B\$4), \$A4:\$A\$4, 0.1))

Ostensibly, this is no different from the error in E4 and E5 (only one non-zero value).

But apparently XIRR makes a special case of this situation (undocumented !).

-----
The root cause of some of these errors is: your formula is fundamentally wrong.

One of the key corrections, in the formula in E3, is to use the conditional expression ROW(\$A\$2:A3)=ROW(A3), not \$A2:\$A\$4=\$A3, to calculate the final valuation "to date" (i.e. to the current row).

There are two important changes.

1. The use of the ROW function instead of comparing dates (or any value). This is especially important in your design, where you have multiple rows for the same date.

2. "Anchoring" the first row (\$A\$2), not the last row (\$A\$4).

-----
Another important change is the calculation of the final valuation "to date" when the condition above is met.

It should be: minus "value @ end month" minus "withdrawal @ end month" plus "deposit @ end month"

assuming that withdrawals are negative cash flows, and deposits and end-of-month values are positive cash flows.

Thus, ostensibly, in the formula in E3, the expression should be -B3-C3+D3, not -(\$C3-\$B3), which is effective B3-C3.

-----
But there are other flaws in your design that complicates things.

1. Apparently, your intent is to enter "value @ end month" in a row where the date is the first of the next month. That is confusing. And it complicates the design of the XIRR formula. And you did not accommodate that dubious design correctly.

2. Furthermore, you are not consistent about assigning "the date is the first of the next month" in a row that corresponds to the "value @ end month".

3. Any withdrawal and deposit "@ end month" might not in the same row that corresponds to the "value @ end month".

-----
And finally, I believe there is a typo in row 16. I presume that the date should be Dec 30 or 31, not Dec 20.

-----
I am not willing to completely redesign your application.

If I did, I would make the following suggestions, at a minimum:

1. Ensure that the row that corresponds to the "value @ end month" have the month-end date.

2. Ensure that the sum of all deposits and sum of all withdrawals on the month-end date are included in the one row with the "value @ end month", not in separate rows, which are permissible for all other dates.

3. Calculate the XIRR only in the row for the month-end date.

-----
But even if it were designed correctly (or "more reasonably, IMHO"), you can still expect #NUM errors, at least for some of the earliest end-of-month XIRRs.

(And perhaps plus or minus 2.98E-9 instead of #NUM -- a design flaw, IMHO.)

The reason is: the guess of 10% might not be sufficient for some months close to the date of the first non-zero cash flow.

There really is no good solution for that.

At a minimum, I would suggestion embedding the XIRR calculation in an IFERROR function. That will catch any #NUM error.

But of course, that will not correct it, if that is even possible. And it will not correct any "bogus" return from XIRR, like 2.98E-9.

"The solution is left as an exercise for the student". (wink)

rsd007

#### rsd007

##### Member
The #VALUE error arises because the ranges \$A1:\$A\$4 and \$B1:\$B\$4 reference row 1, which contains text that Excel cannot interpret as a number.

Ostensibly, change them to \$A2:\$A\$4 and \$A2:\$A\$4

But that just exposes another instance of the #NUM error.

-----
The #NUM errors arise for a different reason for each formula.

With the correction above, the formula in E3 becomes:

=IF(\$A3="", " - ", XIRR(IF(\$A2:\$A\$4=\$A3, -(\$C3-\$B3), \$B2:\$B\$4), \$A2:\$A\$4, 0.1))

If we highlight the first parameter and press f9 in the Formula Bar, it becomes:

=IF(\$A3="", " - ", XIRR({ 20; 20; 3390.56 }, \$A2:\$A\$4, 0.1))

(Be sure to press Esc to restore the original formula.)

Note that all of the values are positive. (I'll discuss the reason why below.)

XIRR requires at least one negative and one positive value.

-----
Similarly for E4 and E5, if we highlight the first parameter and press f9 in the Formula Bar, the formulas become:

E4: =IF(\$A4="", " - ", XIRR({ 0; 0; 3390.56 }, \$A2:\$A\$4, 0.1))
E5: =IF(\$A5="", " - ", XIRR({ 0; 3390.56 }, \$A3:\$A\$4, 0.1))

Note there is only one non-zero value.

XIRR requires at least two non-zero values.

And FYI, another error is: the first cash flow is zero. XIRR does not allow that (for no good reason, IMHO).

But instead of returns a bona fide Excel error, XIRR returns a bogus numerical result (typically plus or minus 2.98E-9, which is usually displayed misleadingly as zero because of how the XIRR is typically formatted (Percentage, not Scientific).

-----
Finally, the #N/A error arises because the ranges are comprised of only one row, to wit:

=IF(\$A6="", " - ", XIRR(IF(\$A4:\$A\$4=\$A6, -(\$C6-\$B6), \$B4:\$B\$4), \$A4:\$A\$4, 0.1))

Ostensibly, this is no different from the error in E4 and E5 (only one non-zero value).

But apparently XIRR makes a special case of this situation (undocumented !).

-----
The root cause of some of these errors is: your formula is fundamentally wrong.

One of the key corrections, in the formula in E3, is to use the conditional expression ROW(\$A\$2:A3)=ROW(A3), not \$A2:\$A\$4=\$A3, to calculate the final valuation "to date" (i.e. to the current row).

There are two important changes.

1. The use of the ROW function instead of comparing dates (or any value). This is especially important in your design, where you have multiple rows for the same date.

2. "Anchoring" the first row (\$A\$2), not the last row (\$A\$4).

-----
Another important change is the calculation of the final valuation "to date" when the condition above is met.

It should be: minus "value @ end month" minus "withdrawal @ end month" plus "deposit @ end month"

assuming that withdrawals are negative cash flows, and deposits and end-of-month values are positive cash flows.

Thus, ostensibly, in the formula in E3, the expression should be -B3-C3+D3, not -(\$C3-\$B3), which is effective B3-C3.

-----
But there are other flaws in your design that complicates things.

1. Apparently, your intent is to enter "value @ end month" in a row where the date is the first of the next month. That is confusing. And it complicates the design of the XIRR formula. And you did not accommodate that dubious design correctly.

2. Furthermore, you are not consistent about assigning "the date is the first of the next month" in a row that corresponds to the "value @ end month".

3. Any withdrawal and deposit "@ end month" might not in the same row that corresponds to the "value @ end month".

-----
And finally, I believe there is a typo in row 16. I presume that the date should be Dec 30 or 31, not Dec 20.

-----
I am not willing to completely redesign your application.

If I did, I would make the following suggestions, at a minimum:

1. Ensure that the row that corresponds to the "value @ end month" have the month-end date.

2. Ensure that the sum of all deposits and sum of all withdrawals on the month-end date are included in the one row with the "value @ end month", not in separate rows, which are permissible for all other dates.

3. Calculate the XIRR only in the row for the month-end date.

-----
But even if it were designed correctly (or "more reasonably, IMHO"), you can still expect #NUM errors, at least for some of the earliest end-of-month XIRRs.

(And perhaps plus or minus 2.98E-9 instead of #NUM -- a design flaw, IMHO.)

The reason is: the guess of 10% might not be sufficient for some months close to the date of the first non-zero cash flow.

There really is no good solution for that.

At a minimum, I would suggestion embedding the XIRR calculation in an IFERROR function. That will catch any #NUM error.

But of course, that will not correct it, if that is even possible. And it will not correct any "bogus" return from XIRR, like 2.98E-9.

"The solution is left as an exercise for the student". (wink)

Thank you for the explaining the steps,
I have made modifications to the formula and corrected my mistakes on the sheet but not sure if the calculations are correct. can you see and help.
Thanks

#### Attachments

• 13 KB Views: 2

#### joeu2004

##### New Member
not sure if the calculations are correct. can you see and help.
You have a winner! Great job! That was a lot to digest and apply. But you did it!

#### rsd007

##### Member
You have a winner! Great job! That was a lot to digest and apply. But you did it!

For some reason it looks like that the XIRR percentages are not correct. can it be verified?

Thanks

#### joeu2004

##### New Member
For some reason it looks like that the XIRR percentages are not correct. can it be verified?
Funny that you ask. I thought you might be interested, and I just logged in to show you how I verified the formulas in column E.

The following image demonstrates how to verify the formula in E14 for Nov 30 2021, for example.

Copy the address range A2:A14 and paste twice into I2:I14 and I15:I27. Note: paste-value_and_number_formats, if any cells might have formulas.

Then copy the cash flow range D2:D14 and paste into J2:J14. And copy C2:C14 and paste into J15:J27. We copy-and-paste D2:D14 first because it contains the earliest cash flow, which Excel XIRR requires to be first in its value and date input ranges.

Then in the row following the pasted data, enter the following formulas: =A14 in I28, and =-B14 in J28. Note that we negate the value in B14 to conform to the cash flow signs that you chose, namely negative for outflows.

In J29, enter the formula =XIRR(J2:J28, I2:I28). In J30, enter the formula =ISNUMBER(MATCH(J29, E14, 0)).

The result in J30 should be TRUE, demonstrating an exact match of the binary values for the XIRR calculated in E14 and J29. QED.

Of course, the range references will be different if you follow the same paradigm to verify other XIRRs that are calculated in column E.

-----
Which IRRs in column E do not look correct? And why?

Effectively, Excel XIRR returns an average annual "interest" (discount) rate that causes the (X)NPV to be zero -- or nearly so. Caveat: "nearly so" is a relative term, depending on the magnitude of the cash flows.

So ostensibly, we can verify that the IRR in E14 and J29 satisfies that requirement by calculating =XNPV(J29, J2:J28, I2:I28).

But for this example, that formula returns a #NUM error !

Unfortunately, XNPV has several design flaws (IMHO), to wit:
1. It does not accept empty cells.
2. It does not allow negative discount rates.

Consequently, I prefer to use SUMPRODUCT to calculate the XNPV, for example:

=SUMPRODUCT(J2:J28 / (1+J29)^( (I2:I28 - I2) / 365) )

For J29, that returns 1.63E-06 when formatted as Scientific. That is a very small value that is "nearly zero", namely 0.00000163.

For your "table" in A2:G52, enter the following formula into H2, formatted as Scientific, and copy down through H52:

Code:
``=IF(ISNUMBER(E2)=FALSE, "", SUMPRODUCT(IF(ROW(\$B\$2:B2)=ROW(B2), -B2+C2+D2, \$D\$2:D2 + \$C\$2:C2) / (1+E2)^( (\$A\$2:A2-\$A\$2)/365 ) ) )``

The formula must be array-entered in some versions of Excel; that is, press ctrl+shift+Enter instead of just Enter.

For the IRRs in E2:E52, that returns plus or minus 1.30E-07 to 4.83E-06 -- all "nearly zero".

-----
Be that as it may, there are several reasons why the Excel XIRR result might be "wrong" for some series of cash flows.

1. There might be no IRR. Excel XIRR should return #NUM. But sometimes, it returns a bogus infinitesimal number like 2.98E-09 (a design flaw, IMHO).

2. There might be multiple IRRs, and Excel XIRR might not find the IRR that makes the "most sense", in a subjective way. Sometimes we can choose a "guess" in order to coerce Excel XIRR to find a "more sensible" IRR. But that presumes that we know what the "sensible IRR" is approximately.

3. Especially when the cash flows are "eccentric", Excel XIRR might not find any IRR because of limitations of its iterative algorithm. Excel XIRR should return #NUM. But again, it might return a bogus an infinitesimal number. And again, sometimes we can choose a better "guess".

I looked at the negative IRRs in E37, E40, E46 and E60. None seems to have multiple IRRs.

Last edited:

#### rsd007

##### Member
Once again Thanks for detail explanation.
So, what is the best way to find more accurate return on a portfolio on a monthly and yearly base.

#### Attachments

• 17.7 KB Views: 3

#### joeu2004

##### New Member
That depends on what you consider to be "more accurate".

First, you did not answer my question: Which IRRs in column E do not look correct? And why?

#### rsd007

##### Member
Yes, let's talk about very first month gain in month of Aug is about \$409.56 which is about 13.65% in 25 days and XIRR is so high 555.12%.
In sept month total gain is \$271.21 in 55 days is 9%. and if calculate per day and then for 365 days is about 60%.
So, look like my XIRR are not correct.

#### joeu2004

##### New Member
Yes, let's talk about very first month gain in month of Aug is about \$409.56 which is about 13.65% in 25 days and XIRR is so high 555.12%.
In sept month total gain is \$271.21 in 55 days is 9%. and if calculate per day and then for 365 days is about 60%. So, look like my XIRR are not correct.

No, the XIRR is "correct" insofar as it is the annual(ized) IRR.

But I agree with you -- and I explained this to you in another conversation: IMHO, it is misleading to annualize sub-annual rates of change.

And most financial institutions agree. For sub-annual rates of change, often they report the __simple__ percentage change for the period of time -- not even compounded daily.

Alternatively, they might annualize by multiplying the __simple__ percentage change over a unit of time by the frequency of unit time per year.

For example, the SEC 7-day yield is (simplifying) the percentage change over 7 days times 365/7.

But typically, the multiplier is 52 for unit weeks, 12 for unit months, 4 for unit quarters, 2 for unit half-years (semiannually).

-----
Another issue is whether you want the IRR or the TWR.

The IRR measures the performance of (the timing of) your cash flows.

The TWR measures the (market) performance of your investment.

From your comments above, I suspect that you want the TWR. (That is my preference.)

But that requires more information than you provided; ideally, the end-of-day valuation on each day of a transaction.

----
And finally, there is the issue of how to count days: actual calendar days, or just trade days.

If we use trade days, the multipliers are typically 5 tdays per week, 21 tdays per month, and 252 tdays per year.

-----
Sorry to be so brief. I don't have time for details.

Hopefully, I have said enough for you to express your preferences.

Then if you need further assistance, I can offer suggestions for incorporating them into the your design.

#### joeu2004

##### New Member
The IRR measures the performance of (the timing of) your cash flows.
The TWR measures the (market) performance of your investment.
From your comments above, I suspect that you want the TWR. (That is my preference.)

The attached image (an excerpt) and Excel file demonstrate one way to calculate both.

See the Excel file for the formulas in columns F:H.

In column F, the "cumulative XIRR to date" is the daily XIRR compounded by the number of days since Aug 6, 2021.

In column H, the "cumulative TWR to date" is the product of the monthly TWRs since Aug 6, 2021.

In column G, the monthly TWR is the month-end valuation (column B) minus the sum of the signed cash flows for the month (columns C:D) divided by the previous month-end valuation (minus 1).

(Subtracting all of the monthly signed cash flows from the month-end valuation is a simplification. It seems to be consistent with your calculation.)

Note: I make a special case for Aug 2021 TWR (G5) because we do not have the previous month-end valuation.

PS.... In general, the "cumulative XIRR and "cumulative TWR are not always be so close. For these examples, I think they are close primarily because of the relatively small deposits and withdrawals, and because we do not have valuations for the dates of each transaction.

I'm afraid this is all that I have time for.

-----

#### Attachments

• 21.4 KB Views: 8
Last edited:

#### rsd007

##### Member
No, the XIRR is "correct" insofar as it is the annual(ized) IRR.

But I agree with you -- and I explained this to you in another conversation: IMHO, it is misleading to annualize sub-annual rates of change.

-----

----

-----
Then if you need further assistance, I can offer suggestions for incorporating them into the your design.

The IRR measures the performance of (the timing of) your cash flows.
The TWR measures the (market) performance of your investment.
From your comments above, I suspect that you want the TWR. (That is my preference.)

Thank you for such nice explanation much easy then what someone can understand in a classroom.

I am still digesting all the explanation you have given. Also will read more about TWR this is something I have not read in detail.

Last edited:

#### joeu2004

##### New Member

But 6 years ago, I noticed the one "editor" completely botched it with a major rewrite. I tried to suggest corrections, but he didn't understand. Of course, I'm free to make the changes myself without agreement. But I thought it was a fool's errand; the other "editor" could simply undo my changes.

Earlier this year, having forgotten that history, I again considered submitting some "re-rights" (wink) of my own. But I balked because of the time it would take to get the font usage correct.

I used to think that "time-weighted return" is a misnomer because the key distinction is: the calculation is __not__ normally weighted by the duration of the sub-periods; and it is __not__ "time-valued", as the IRR is.

Eventually, I did see an esoteric justification for the term. But I still consider the term to be misleading, especially for people who are familiar with the IRR.

Anyway, the following is __my__ "wiki" explanation. I tried to leverage as much of the existing text and examples as possible.

-----
The TWR is calculated by the compounded product of the rates of return (plus 1) for each sub-period, regardless of their duration.

The result is the "cumulative" TWR for the entire period; that is, for the entire timeframe covered by the sum of the sub-periods.

The average TWR for any unit of time can be calculated by discounting the cumulative TWR by an appropriate factor. For example, if the cumulative TWR ("cumlTWR") covers a period of "t" months, the monthly TWR is (1 + cumlTWR)(1 / t) - 1.

The sub-periods do not have to be the same duration; and they do not have to be measured in the same units of time.

----- (in a later section) -----

But when the same unit of time is used to measure the duration of every sub-period and the average TWR, the average TWR can be described as the geometric average of the sub-period rates weighted by their duration.

That is why the TWR is called a "time-weighted" rate of return.

But there is no reason to rely on that when calculating the TWR.

This is illustrated by the following examples.

Example 2

Over a period of a 10 years, a portfolio grows by 5% per year for three of those years, and by 10% per year for seven of those years.

If there are no external cash flows, we might calculate the end value as follows:

end value = start value * (1.05)^3 * (1.10)^7

Thus, the 10-year cumulative TWR is (1.05)^3 * (1.10)^7 - 1

And the annual TWR is ( (1.05)^3 * (1.10)^7 )^(1/10) - 1

Alternatively, since the unit of time is "per year" in all cases, the annual TWR could also be calculated by the following weighted geometric average:

annual TWR = (1.05)^(3/10) * (1.10)^(7/10) - 1

Note that is is just an algebraic manipulation of the first annual TWR formula.

Example 2.5 (or renumber subsequent examples)

Over a period of 6 months, the value of an investment grows by 2.50% over 8 weeks, declines by -1.25% 27 days, grows by 3.60% over 2 months, and declines by -0.80% over 37 days.

If there are no external cash flows, the monthly TWR can be calculated by the compounding product of the sub-periods without regard for their durations, namely:

monthly TWR = ( (1+0.0250) * (1-0.0125) * (1+0.0360) * (1-0.0080) )^(1/6) - 1

Alternatively, we could convert the duration of each sub-period to a number of days or fractional months, and then calculate the weighted geometric average.

But it would be difficult and inaccurate to do without knowing the corresponding dates in order to convert between number of days and number of months, because a month is 28, 29, 30 or 31 days.

Moreover, there is no benefit to doing so other than to demonstrate that the TWR is indeed time-weighted.

#### rsd007

##### Member
Hello just found issue which I cannot understand if the money added start from 2nd cell, I get 0.00% both Cells have same date.

#### joeu2004

##### New Member
just found issue which I cannot understand if the money added start from 2nd cell, I get 0.00% both Cells have same date.

The XIRR formulas return 2.98E-9, not zero. I explained that previously: it is because the first cash flow in row 2 is now zero.

And look at the formula in G5:

=IF(A5<>EOMONTH(A5,0)," - ",(B5-SUM(C3:D4))/D2-1)

Since you make D2 zero, we are dividing by zero.

That causes PRODUCT in H5 to return zero because none of the values in G2:G5 is a number.

The formula in G5 is a hack because you start with data on Aug 6 without a previous "value @ month end" on July 31.

To make your change, enter -20 into C2, and delete the value in D3 or delete row 3 altogether.

Last edited:

#### joeu2004

##### New Member
I just discovered a technical error in the Excel file that I attached to posting #12: the formulas in G2:G4 are incorrect, even though they return the correct result (" - ").

The formula in G5 should be:

=IF(A5<>EOMONTH(A5,0)," - ",(B5-SUM(\$C\$2:D5)+\$D\$2)/\$D\$2-1)

Then G5 can be copy-and-pasted into G2:G4.

Last edited:

#### joeu2004

##### New Member
Take a look at posting #17 again. I changed the formula so that it would be more consistent -- but still a hack -- when copy-and-pasted into G2:G4.

#### rsd007

##### Member
Take a look at posting #17 again. I changed the formula so that it would be more consistent -- but still a hack -- when copy-and-pasted into G2:G4.
Thank you for your help, and explanation.

#### rsd007

##### Member
Take a look at posting #17 again. I changed the formula so that it would be more consistent -- but still a hack -- when copy-and-pasted into G2:G4.
Hello,
Can you please comment why my cumulative TWR to date percentage is so high?

Thanks

#### Attachments

• 34.2 KB Views: 2

#### joeu2004

##### New Member
Can you please comment why my cumulative TWR to date percentage is so high?

You copied the special formula in G2 down the entire column. That special formula is intended only for the first month (G2:G4) because it is different.

(Also, the formulas in G2:G4 can be normally-entered, instead of array-entered. That is, press just Enter as usual.)

-----
Instead, starting with the second month in G5, the array-entered formula should be of the form:

Code:
``=IF(A5<>EOMONTH(A5,0), " - ", (B5 - SUM(IF(\$A\$2:A5=EOMONTH(A5,0), \$C\$2:C5 + \$D\$2:D5))) / SUMIF(\$A\$2:A5, EOMONTH(A5,-1), \$B\$2:B5) - 1)``

Note that the highlighted row references must match the row that the formula is entered into.

(And to that end, I made a minor change to the SUMIF ranges to make it easier to adapt the formula.)

After array-entering the formula into G5, copy G5 down the column.

-----
You will discover a #DIV/0 error G22.

The root cause of the error is: the date in A19 is not the "end of month" date. For 2020, Feb 29 is the end of the month.

You have made that mistake before (May 30 [sic]), and I corrected it.

To avoid the mistake in the future, I suggest that you always enter the end-of-month date with a formula of the following form (in A19, for example):

=EOMONTH(A18,0)

That assumes that the row above is in the same month.

If the row above is in the previous month, use a formula of the form =EOMONTH(A18,1)

Last edited:

#### rsd007

##### Member
Hello,

Look like still having issue with the TWR calculation cannot understand what the difference is. Line 14 TWR is way out.

#### Attachments

• 20.2 KB Views: 1

#### joeu2004

##### New Member
Look like still having issue with the TWR calculation cannot understand what the difference is. Line 14 TWR is way out.
Well, we both made mistakes. But mine is the more aggregious one.

My mistake.... At some point in time, I entered the wrong formula into G6 and G7 in "XIRR error v2.xlsx", which I attached to posting #12. It made no difference in that context. But unfortunately, that is the formula that I copied into posting 21. And you dutifully entered that formula into G5:G19 in "QUESTION 4 XIRR 2.xlsx".

To avoid further confusion, I am attaching "XIRR error v3.xlsx" with the correct formulas. Ignore all other attachments and formulas in my previous posting.

And I am attaching "QUESTION 4 XIRR 2 v2.xlsx" with the correct formulas. See G5:19 in the "right" worksheet.

-----
Your mistake.... You entered the wrong formula into G4. See the "wrong" worksheet. Recall that that formula should be used only for the first month: March, but not April. See the correct formula in G4 in the "right" worksheet. I simply copied the corrected formula in G5 into G4.

Sorry for my mistakes.

-----

#### Attachments

• 32.4 KB Views: 1
• 21.4 KB Views: 1

#### rsd007

##### Member
Well, we both made mistakes. But mine is the more aggregious one.

My mistake.... At some point in time, I entered the wrong formula into G6 and G7 in "XIRR error v2.xlsx", which I attached to posting #12. It made no difference in that context. But unfortunately, that is the formula that I copied into posting 21. And you dutifully entered that formula into G5:G19 in "QUESTION 4 XIRR 2.xlsx".

To avoid further confusion, I am attaching "XIRR error v3.xlsx" with the correct formulas. Ignore all other attachments and formulas in my previous posting.

And I am attaching "QUESTION 4 XIRR 2 v2.xlsx" with the correct formulas. See G5:19 in the "right" worksheet.

-----
Your mistake.... You entered the wrong formula into G4. See the "wrong" worksheet. Recall that that formula should be used only for the first month: March, but not April. See the correct formula in G4 in the "right" worksheet. I simply copied the corrected formula in G5 into G4.

Sorry for my mistakes.

-----
Once again Thank you for explaining in-detail everything. learned lot of new things.

Thankyou

#### rsd007

##### Member
Hello,

@joeu2004, Is it possible to calculate the Monthly TWR when we don't have the "portfolio value @ end of month" for every month,

so, we don't get #DIV/0