One of the most commonly used forms of return is the money-weighted rate of return (MWRR). This article will give you a deeper understanding of the metric and teach you how to calculate it.

Jun 04, 2024

Academy

There are various methods for calculating investment returns. One of the most commonly used forms of return is the money-weighted rate of return (MWRR).

This article is for those who want to gain a deeper understanding of the money-weighted rate of return and the calculation of it.

So, stick around if you're ready for a detailed — and slightly (okay, very) nerdy — review of this key return metric.

The money-weighted rate of return is the average annual return on the capital invested at any given time and corresponds to the internal rate of return (IRR) of your investment.

It's a form of return that illustrates the advantages and disadvantages of your decisions to add capital to or withdraw capital from your portfolio at a given time.

This is because both the size and timing of cash flows to and from the portfolio influence this key figure. This contrasts with the time-weighted return, which is a form of return that specifically eliminates the impact of cash flows on the calculated rate of return.

The money-weighted rate of return is the average annual return on the capital invested at any given time.

As a rule of thumb, the money-weighted rate of return is preferable when you want to know the actual return on your investment.

A deep understanding of MWRR is best achieved by reviewing how to manually calculate it.

So that’s what we’ll do.

First, I’ll show you how to manually calculate the money-weighted rate of return using a trial-and-error method, and then I’ll show you how you can easily calculate the key figure in Excel using the XIRR function.

The process consists of four steps overall:

Find the necessary data.

Calculate the present value of all cash flows.

Calculate the net present value.

Find the correct MWRR via trial and error.

Let me walk you through the steps using a simple example.

To be able to calculate the MWRR, we need to know the timing of all cash flows during the investment period and their nominal value — that is, how much was added or withdrawn in dollars and cents.

Both the original investment and the final market value of the portfolio are also considered cash flows.

As such, to calculate the MWRR of an investment for a given period, you need the following information:

The value and timing of the original investment.

The value and timing of all cash flows to and from the portfolio during the period.

The market value of the portfolio at the end of the period.

Let's take a simple example:

You invest $100 million — that's your original investment. After a year, you add $10 million to the portfolio, meaning that a cash flow occurs, and after two years you sell the portfolio for $120 million, which is the final market value.

We now have all the information we need to calculate the MWRR for the period, and we plot it into Excel with a negative sign on the values that are expenses for you. The final market value of $120 million can be seen as income, as it is your (unrealized) gain on the investment, and therefore it should have a positive sign.

It's now time to calculate the Present Value (PV) of the cash flows that occur during the period.

In our case, this means that we need to find out what our capital contribution of $10 million to the portfolio after one year would have been worth on day 0 of the investment period with a given return rate (the MWRR that we don’t know yet) — that is, the present value of the cash flow.

Likewise, we need to find the present value of the portfolio's final market value of $120 million.

I know this might seem complicated if you’re reading about it for the first time but hang in there – it’ll make sense eventually!

The thing about this calculation is that we kind of need the MWRR to calculate the MWRR. Therefore, we need to go ahead and start with a temporary percentage and go from there – that’s why we call it the trial-and-error method.

Let's work with a temporary MWRR of 7%.

Now we can calculate the present value of our two cash flows.

We do this by dividing the future value of our cash flows by our temporary MWRR + 1. If the cash flow needs to be discounted for one year, it should be divided by (1 + MWRR) once, and if it needs to be discounted for two years, it should be divided by (1 + MWRR) twice, and so forth.

For our two cash flows, the calculations look as follows:

PV of CF1 = -$10 million / (1 + 0.07) = -$9.3 million

PV of CF2 = $120 million / (1 + 0.07) / (1 + 0.07) = $104.8 million

As you may have spotted, there is still an empty field in our Excel sheet, namely NPV which stands for Net Present Value.

NPV is calculated by adding together all our present values—including the original investment, which is naturally already a present value since it is the amount invested on day 0.

The net present value is the key to finding the correct MWRR for the period in our example—we'll return to that in step 4.

At this step, we start by calculating the NPV based on the numbers that are based on our temporary MWRR of 7%:

NPV = 104.8 - 9.3 - 100 = -4.5331

We are now ready to find the correct MWRR at the last step.

MWRR is the rate of return that sets the present value of all cash flows during the period equal to an NPV of 0. Or to put it another way: it is the rate of return that sets the original investment added together with the present value of all future cash flows equal to the present value of the final market value.

In our example, we’ve got an NPV of -4.5. We can conclude that our temporary MWRR isn’t correct, since it doesn’t give us an NPV of 0.

At this step, we use a trial-and-error method to find the percentage that results in an NPV of 0.

The fact that our NPV is negative means that we have discounted back with too high a rate of return. Let's try with an MWRR of 5% and see if it brings our NPV closer to 0:

It did, but our NPV is still negative, and the MWRR must therefore be a little lower than 5%.

By trying and adjusting the MWRR a little at a time, we find that an MWRR of 4.66% gives us an NPV of 0.

That is to say, the money-weighted rate of return in our example is 4.66%. That means our investment has had an average annual return of 4.66% of the capital invested at any given time.

In order to really understand what that means, let's do a couple of calculations the other way around:

During the first year of our investment period, $100 million was invested. To find the market value after the first year, we must thus multiply the $100 million by our average annual rate of return (MWRR) once;

$100 million * (1 + 0.0466) = $104.7 million.

After a year, our portfolio thus has a market value of $104.7 million. At that time, we add $10 million to the portfolio. That means that during the last year of the investment period, we have $114.7 million invested. To find our final market value after two years, we must now multiply the $114.7 million by our MWRR once;

$114.7 million * (1 + 0.0466) = $120 million.

I hope this calculation clarifies the meaning of MWRR as the average annual return of 4.66% of the capital invested at any given time.

You can also compare it to getting interest for having money in the bank: you only get interest for the money in the account—and only for the days when that money was in the account.

MWRR is thus not an average annual return on the total capital you’ve invested, which in our example is $110 million. In that case, our final market value would have been:

$110 * (1 + 0.0466) * (1 + 0.0466) = $120.5 million.

That was a detailed review of how to manually calculate the money-weighted rate of return. I hope it has given you a good understanding of the key figure.

Fortunately, you don't have to go through that process every time you want to calculate MWRR. Excel has a function that makes it very easy to calculate it. We'll go through that in the following section.

The process of calculating MWRR in Excel consists of only two steps, and the first is the same as for the manual calculation. We start by finding the data we need to be able to make the calculation.

To calculate the MWRR of an investment for a given period, you need the following information:

The value and timing of the original investment.

The value and timing of all cash flows to and from the portfolio during the period.

The market value of the portfolio at the end of the period.

Let's use the same simple example that we’ve worked with so far:

We invest $100 million—that's our original investment. After a year, we add $10 million to the portfolio, meaning that a cash flow occurs, and after two years we sell the portfolio for $120 million, which is the final market value.

That’s all the information we need to calculate the MWRR for the period, and we plot it into Excel. Be aware that some date formats can cause the Excel function not to work.

When MWRR is calculated using the Excel function, there must be a negative sign on the values that are outflows from the portfolio. The final market value of $120 million can be seen as an outflow because in the calculation context it is seen as the amount received for a sale of the investments and subsequently withdrawn from the portfolio, and therefore it must have a negative sign.

We are now ready to calculate the MWRR.

Click on the MWRR field, type "=", and search for the function XIRR.

When you have selected the function, first mark the column with your cash flows, insert a semicolon, and then mark your dates.

End with a parenthesis, press "enter", and now you have your MWRR:

As mentioned, the money-weighted rate of return illustrates how your decisions to add capital to or withdraw capital from your portfolio affect your return.

We can clarify this with a new example:

Three investors each invest $100 million in the same investment fund on January 1st.

Later that year on August 5th, Investor A chooses to add $25 million to the portfolio and Investor B chooses to withdraw $25 million from the portfolio, while Investor C does nothing.

At the end of the year, Investor A's portfolio has a market value of $134.8 million, Investor B's portfolio a market value of $73.5 million, while Investor C's portfolio is worth $104.1 million.

Now we use the XIRR function to calculate the investors' money-weighted rates of return:

As the picture shows, the three investors achieve widely different money-weighted rates of return, even though they have invested in the same investment fund. This is because the money-weighted rate of return reflects the actual return on the investment and thus the different market values that the investors have achieved. That is precisely why it is called the money-weighted rate of return.

Investor A's decision to add $25 million to his portfolio at that particular time turned out to be a good decision, and this is reflected in this investor's rate of return.

Investor B, on the other hand, withdrew $25 million from his portfolio at the same time, resulting in an overall negative return.

Investor C did nothing during the period and thus achieved a return of 4.1%.

If one calculated the time-weighted rate of return for the three investors, one would get the same result for all three investors, namely 4.1%, since the time-weighted rate of return, unlike the money-weighted rate of return, *eliminates* the effect of cash flows on the final return.

The money-weighted rate of return is by definition the average annual return of the capital invested at any given time.

This means that even if you calculate it for a period of, for example, four months or a year and a half, the money-weighted rate of return will reflect what the portfolio's performance in that period would correspond to in average annual return.

However, you can correct the figure to show the return for a period that is shorter or longer than one year. It would look like this in Excel:

= (1 + XIRR(values;dates)) ^ X – 1

X is the number of years that the period lasts. That is to say, if you want to find the money-weighted rate of return for a period of two years (theoretically, the average two-year return of the capital invested at any given time), you should replace X with 2.

If, for example, the period lasts three months, you can write 0.25, because it corresponds to a quarter of a year.

You can also find the money-weighted rate of return for a specific number of days. Then you just have to find out how many years that number of days corresponds to. If, for example, you want to find the money-weighted rate of return for 40 days, replace X with (40/365).

Let's go back to our example from earlier, where we found a return rate of 4.67%;

However, that was for an investment period of two years. If we want to find the real two-year return on the capital invested at any given time, we use the formula from before and replace X with 2:

That is to say, for the total period of two years, there has been a real return of the capital invested at any given time of 9.6%.

When you correct the MWRR to reflect the length of the investment period you want to find the return for, you get a better overview of how much you really got out of your investments in that period.

The money-weighted rate of return is the average annual return on the capital invested at any given time.

The money-weighted rate of return corresponds to the internal rate of return (IRR) of your investment.

MWRR illustrates the advantages and disadvantages of an investor's decisions to add capital to or withdraw capital from their portfolio at a given time, as both the size and timing of cash flows to and from the portfolio influence the key figure. This contrasts with the time-weighted rate of return, which is a form of return that specifically eliminates the impact of cash flows on the calculated rate of return.

As a rule of thumb, the money-weighted rate of return is preferable when you want to know the actual return on your investment.

MWRR can be calculated with the Excel function XIRR.

IRR is one of the most important metrics when evaluating your private equity investments. We break it down to provide you with a thorough understanding of it.

Jan 25, 2024

Private equity,

Academy

Private equity is an umbrella term for many types of funds with different strategies. I'll walk you through the various types.

Jan 11, 2024

Private equity,

Academy

There are as many types of cost structures as there are funds. Nevertheless, we give you an overview of the most commonly used types of expenses and cost structures to be aware of.

Feb 09, 2024

Private equity,

Academy

A next-generation wealth platform for progressive wealth owners

+45 3171 1026hello@aleta.io

335 Madison Avenue, NY-10017, USA Ingerslevs Boulevard 3, 8000 Aarhus, DK CVR: 43 67 16 85