Naimul Hasan Arif
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.
Download Practice Workbook
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)
2 Easy Ways to Calculate Time Weighted Return in Excel
There are two simple and very effective ways to calculate Time Weighted areturn in Excel. They are:
- Use of Arithmetic Formula
- 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 organised 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+C6Here,
E5 = Ending Value of a year
C6 = Cashflow
- Next, employ the following formula to have the Return Rate.
=(E5-D5)/D5
- 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.
=((1+F5)*(1+F6)*(1+F7))-1
- Finally, hit the ENTER button to have the result.
Read More: Timesheet Formula in Excel (5 Examples)
Similar Readings
- How to Calculate Time Difference Between AM and PM in Excel
- How to Calculate Average Turnaround Time in Excel (4 Methods)
- Excel Formula to Calculate Overtime and Double Time (3 Ways)
- Calculate Hours Between Two Dates and Times in Excel Excluding Weekends
- Excel Calculate Hours between Two Times after Midnight (3 Methods)
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.
=1+F5
- 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.
=GEOMEAN(G5:G7)-1
- Finally, hit ENTER to have the time weighted value.
Read More: Excel Formula to Calculate Overtime and Double Time (3 Ways)
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 Calculate Hours Worked Minus Lunch with Excel Formula
- How to Calculate Overtime over 40 Hours Using Excel Formula
- Calculate Hours Between Two Times in Excel (6 Methods)
- How to Calculate Hours Worked and Overtime Using Excel Formula
- Sum Time in Excel (9 Suitable Methods)
- How to Calculate Hours and Minutes for Payroll Excel (7 Easy Ways)
Do not believe the calculations using GEOMEAN is correct. The correct TWRR is 13.24% not 4.23%. PLease check and correct if necessary.
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].
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)-1Here, the G5:G7 range refers to the values in the “1+Return” column.
Regards,
ExcelDemy