Method 1 – Applying the Arithmetic Formula
Steps:
- Create an organized dataset.
- Here, we have a situation where a man invested $55,000 as an initial investment in 2019. It is considered as a cash flow as well as a beginning value. This investment became $59,532 at the end of that year. At the beginning of 2020, he invested $15,000 more with the money left after 2019. The money, this time, got devalued at the end of the year. He withdrew $20,000 at the beginning of 2021. It becomes $55,784 as a final output.
- Enter the following formula to calculate the value at the beginning of a time period:
=E5+C6Here,
E5 = Ending Value of a year
C6 = Cashflow
- Enter the following formula:
=(E5-D5)/D5
- Press ENTER to calculate the Return Rate.
- AutoFill the rest cells using the Fill Handle.
- Enter the following formula to calculate the time-weighted value:
=((1+F5)*(1+F6)*(1+F7))-1
- Press the ENTER button to get the result.
Method 2 – Using the GEOMEAN Function
Steps:
- Generate a dataset for a similar case discussed above with a different set of values.
- Enter the following formula to calculate the 1 + Return value:
=1+F5
- Press ENTER to get the value.
- Use the Fill Handle to AutoFill the rests.
- Enter the following formula with the GEOMEAN function to get the time-weighted value:
=GEOMEAN(G5:G7)-1
- Press ENTER to get the time-weighted value.
Read More: How to Convert Percentage to Basis Points in Excel
Download the Practice Workbook
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
Get FREE Advanced Excel Exercises with Solutions!
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
=PRODUCT(1+F5:F7)-1 returns the same answer as the arithmetic example 11.551662148889900%.
Hello Paul Tupper,
Yes, we can use this formula too. Thanks for your suggestions, we really appreciate it.
Regards
ExcelDemy
p.s. =GEOMEAN(1+F5:F7)-1 also gives the same result without the need for a helper column on the GEOMEAN tab.
Hello Paul Tupper,
Of course, you can use this formula too. Both are doing the same calculations. Our formula is simpler nothing else. Thanks for your suggestions, we really appreciate it.
Regards
ExcelDemy