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

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:

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.

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

• Next, employ the following formula to have the Return Rate.

• Press ENTER to have the Return Rate.

• AutoFill the rest cells using Fill Handle.

• Now, apply the following formula to have the time-weighted value.

• 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.

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

• Press ENTER to have the value.

• Then, use Fill Handle to AutoFill the rests.

• Now, apply the following formula with the GEOMEAN function to have the time weighted value.

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

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

• How to Use Macaulay Duration Formula in Excel
• How to Calculate Tracking Error in Excel
• How to Calculate WACC in Excel
• How to Calculate Mileage Reimbursement in Excel
• How to Calculate Profitability Index in Excel 
• Excel XNPV vs NPV: Comparison with Examples
• XIRR vs IRR in Excel (Differences with Examples)

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