Are you looking for a simple way to track how your investment portfolio is performing?
You’ll need to determine your money-weighted rate of return (MWRR), also known as the internal rate of return (IRR). This is the annualized rate of return of an investment portfolio after taking into account the investor’s personal contributions and withdrawals.
However, manually calculating an investment’s money-weighted rate of return is not a trivial task. This is where a spreadsheet comes in handy.
Our money-weighted rate of return excel calculator is an easy-to-use tool that takes the headache out of performance tracking and keeps you up-to-date with your portfolio returns.
How To Use The Money-Weighted Rate of Return Calculator
Using the money-weighted rate of return excel calculator is straightforward.
Here’s what you’ll need to fill in:
- The starting date and starting value of your portfolio
- The ending date (i.e. today’s date) and ending value (i.e. current market value) of your portfolio
- Any contributions or withdrawals made to the portfolio (dates and amounts)
These inputs correspond to the light gray cells in the excel calculator, as shown below.
Once the required inputs are filled in, the spreadsheet calculates the money-weighted rate of return of the portfolio. In the example above, the annualized return of the portfolio was 9.90%.
Note that if the investment period is less than one year, the spreadsheet outputs the actual (instead of annualized) rate of return.
Understanding Your Money-Weighted Rate of Return
The money-weighted rate of return (or internal rate of return) is the discount rate that makes the net present value (NPV) of all cash flows equal to zero.
This benchmark value is the equalizer of all investment opportunities and it allows you to compare the rate of return across various investment types, from a stock portfolio to a rental property, or even a business venture.
Using technical jargon may confuse some unfamiliar with the terms, so here’s an easier way to understand the MWRR.
Think of the money-weighted rate of return to an investment portfolio like the interest rate is to a savings account.
That is, the MWRR could be thought of as the constant rate of return that results in the final investment value, given the specified cash flows into and out of the account.
Compare two accounts, one being a savings account with a 5% interest rate and another being an investment portfolio with a 5% money-weighted rate of return. If both accounts have the same starting value and made the same deposits and withdrawals at the same times, then the final value of these accounts at the end of the period would be equal.
As you can see, this metric gives you an apples-to-apples method to compare the performance of different types of investments and asset classes.
Money-Weighted Rate of Return vs. Time-Weighted Rate of Return
Your portfolio’s money-weighted rate of return is dependent on your specific situation because it takes into account the effects of your individual deposits and withdrawals.
This is in contrast to the time-weighted rate of return (TWRR), which only considers the performance of the underlying stocks and equities that make up your portfolio irrespective of your own personal contributions and withdrawals from the account.
Think of the TWRR as your rate of return if you did not make any deposits or withdrawals during the period, or the rate of return of the “1st dollar” invested into the portfolio.
Think of the MWRR as the “actual” rate of return including the impacts of all deposits and withdrawals.
To further understand the difference, let’s see how the timing of your cash flows affects the relationship between MWRR and TWRR:
- If a deposit is made and the portfolio then increases in value, the gain impacts the portfolio more than if the deposit had not been made, so the money-weighted rate of return > time-weighted rate of return
- If a deposit is made and the portfolio then decreases in value, the loss impacts the portfolio more than if the deposit had not been made, so the money-weighted rate of return < time-weighted rate of return
- If a withdrawal is made and the portfolio then increases in value, the gain impacts the portfolio less than if the withdrawal had not been made, so the money-weighted rate of return < time-weighted rate of return
- If a withdrawal is made and the portfolio then decreases in value, the loss impacts the portfolio less than if the withdrawal had not been made, so the money-weighted rate of return > time-weighted rate of return
- If there were no deposits or withdrawals made during the period, then the money-weighted rate of return = time-weighted rate of return
Therefore, two investors could invest in the same portfolio allocation and have the same TWRR but have drastically different MWRRs. That’s why to calculate your personal rate of return, you should determine your MWRR.
Understanding and being able to determine the money-weighted rate of return of an investment is a critical skill that every investor should have in their investor toolkit. Hopefully, you are now well-equipped to keep track of your financial performance like the professionals.
Have any questions about the money-weighted rate of return or the calculator? Feel free to let me know in the comments below.
Thanks for your Excel Sheet, I try it running on a month to month basis and for some months doesn’t work, I couldn’t figure out why?
Thanks
Thank you for this. I had been struggling with the idea of “time-weighted return” which I understand is how mutual funds compute their published returns. The problem I saw with TWR is it’s misnamed — it isn’t time weighted at all. It just chops up the period under consideration into intervals between deposits/withdrawals, computes their individual returns, and multiplies them. It’s a decent approximation if the deposits and withdrawals are small relative to the initial portfolio value and the intervals between them are short, but not otherwise. Example: you start with $1k Jan. 1st, and after a month you have $2k. Wow! 100% interval return! Then you contribute $10k, and your portfolio stays flat for the rest of the year — zero interval return. TWR still says you had 100% return. Common sense says more like 9%.
MWRR fixes that because a big return early on won’t have undue influence on the final result. It seems much better for individual investors (at least those who aren’t day traders).
I imagine fund managers like TWR better because if they start a fund small and if it has a booming initial year, they can attract a lot of money and ride that initial success for quite a while, whereas small new funds with lousy early returns can be allowed to fade away quietly. Hence the Lake Woebegone effect — more (surviving) funds are above average. Color me cynical…
Using Excel 2016 and adjusting your example to January 2, from January 1 caused an “Invalid Date” to appear.
Great calculator. Very clear instructions, easy to use. Very helpful. Thank you
hello and thank you for the very helpful calculator. Can this same spreadsheet be used without any changes to calculate the percentage return over multiple years? Even if there are monthly contributions and withdraws over the years? Thank you
Hi John,
If the length between the start and the end date is more than 1 year, than the calculator will output your annualized rate of return, as long as all withdrawals or contributions are made within the specified time period.
Hope this helps.