How to Calculate Time Weighted Return in Excel (2 Simple Ways)

Get FREE Advanced Excel Exercises with Solutions!

We need to keep updated about an investment. There are many factors in order to keep track of it. Time Weighted Return is just like a parameter to perform that purpose. In Microsoft Excel, we can calculate the Time Weighted Return quite easily. I will try to explain two simple ways on how to calculate Time Weighted Return in Excel in this article. I hope it will be very helpful for you.


Time Weighted Return: Definition and Formula

Time weighted return (TWR) is a performance measurement tool used to evaluate the overall performance of an investment portfolio. The time weighted return calculation takes into account the timing of cash flows, as well as the size of the cash flows, to give a more accurate picture of the portfolio’s performance.

In order to calculate TWR, we need to consider the investment as well as cashflows and time periods. We can use the following formula to fulfill our purpose:

TWR = [(1+R1)×(1+R2)×⋯×(1+Rn)] − 1

where,
TWR= Time-weighted return
n= Number of periods
R=  (Ending Value−(Initial Value+Cash Flow))/(Initial Value+Cash Flow)


How to Calculate Time Weighted Return in Excel: ​2 Simple Ways

There are two simple and very effective ways to calculate Time Weighted return in Excel. They are:

  1. Use of Arithmetic Formula
  2. Application of GEOMEAN Function

The processes are explained in details in the following section.


1. Apply Arithmetic Formula to Calculate Time Weighted Return

We cam simply calculate the time weighted return by applying simple arithmetic formula. The whole procedure is discussed below.

Steps:

  • The first and foremost task is to create an organized dataset. Here, I have considered a situation where a man invested $55,000 as an initial investment in 2019. It is considered as a cashflow as well as beginning value. This investment became $59,532 at the end of that year. At the beginning of the year 2020, he invested $15,000 more with the money left after 2019. The money this time got devalued at the end of the year. So, he withdrawed $20,000 at the beginning year 2021. It finally becomes $55,784 as a final output.
  • Apply the following formula to calculate the value at the beginning of a time period.
=E5+C6

Here,
E5 = Ending Value of a year
C6 = Cashflow

How to Calculate Time Weighted Return in Excel

  • Next, employ the following formula to have the Return Rate.
=(E5-D5)/D5

  • Press ENTER to have the Return Rate.

Apply Arithmetic Formula to Calculate Time Weighted Return

  • Now, apply the following formula to have the time-weighted value.
=((1+F5)*(1+F6)*(1+F7))-1

How to Calculate Time Weighted Return in Excel

  • Finally, hit the ENTER button to have the result.


2. Use GEOMEAN Function to Calculate Time Weighted Return

The simplest way to calculate the time-weighted return is to use the GEOMEAN function. The whole procedure is discussed below.

Steps:

  • Generate a dataset for a similar case discussed above, just with a different set of values.

Use GEOMEAN Function to Calculate Time Weighted Return

  • Next, input the following formula to calculate the 1 + Return value.
=1+F5

  • Press ENTER to have the value.

How to Calculate Time Weighted Return in Excel

  • Then, use Fill Handle to AutoFill the rests.

  • Now, apply the following formula with the GEOMEAN function to have the time weighted value.
=GEOMEAN(G5:G7)-1

 How to Calculate Time Weighted Return in Excel

  • Finally, hit ENTER to have the time weighted value.

 How to Calculate Time Weighted Return in Excel

Read More: How to Convert Percentage to Basis Points in Excel


Download Practice Workbook


Conclusion

At the end of this article, I like to add that I have tried to explain two simple ways on how to calculate Time Weighted Return in Excel. It will be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below. You can visit our site for more articles about using Excel.


Related Articles


<< Go Back to Excel Formulas for Finance | Excel for Finance | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Naimul Hasan Arif
Naimul Hasan Arif

Naimul Hasan Arif, a BUET graduate in Naval Architecture and Marine Engineering, has been contributing to the ExcelDemy project for nearly two years. Currently serving as an Excel and VBA Content Developer, Arif has written more than 120 articles and has also provided user support through comments His expertise lies in Microsoft Office Suite, VBA and he thrives on learning new aspects of data analysis. Arif's dedication to the ExcelDemy project is reflected in his consistent contributions and... Read Full Bio

4 Comments
  1. Do not believe the calculations using GEOMEAN is correct. The correct TWRR is 13.24% not 4.23%. PLease check and correct if necessary.

    • Reply Avatar photo
      Naimul Hasan Arif Dec 12, 2022 at 11:23 AM

      Thank you CARLOS for your concern.
      As far as I am concerned I have followed the proper calculation process. There is an output difference between the arithmetic mean and the value using GEOMEAN. That’s the reason I think for the variation between the results. For further queries, you can contact [email protected].

  2. this is wrong. per above comment, geomean is not the right way to calculate [(1+R1)×(1+R2)×⋯×(1+Rn)]

    • Hello DAN,

      Firstly, we would like to apologize for the trouble. As you pointed out, the use of the GEOMEAN function does not return the correct answer. In fact, we can apply the PRODUCT function as shown below.

      =PRODUCT(G5:G7)-1

      Here, the G5:G7 range refers to the values in the “1+Return” column.
      Using PRODUCT function to calculate the time weighted return
      Regards,
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo