Do your own asset allocation analysis with this unique excel tool! 

When it comes to investing, more than half your success lies in proper asset allocation. This is why, time and again, we have reiterated the need to have the right mix. For most of you, the lure of high returns in equities often leads to loading up on the asset class, oblivious to its risks, especially when your goal is short to medium term. This is why we did a series of articles on what should be your asset allocation under various scenarios and summarised our findings based on historical data. These articles written by Aarati Krishnan and published in recent months are given below: 

1. A 100% equity portfolio: Will it work? – PrimeInvestor
2. 70/30, 60/40 or 50/50: Which is the best asset allocation? – PrimeInvestor
3. What asset allocation best fits 10-year, 5-year and 3-year goals? – PrimeInvestor
4. How much should you allocate to gold? – PrimeInvestor

In  this article, we are now sharing the source data and tool for all the above analysis. We are sharing a comprehensive Do-it-yourself (DIY) tool through excel sheets, further enhanced (since the above articles) to perform your own customised asset allocation analysis and check the findings for yourself.

About the source data

The tool uses the Nifty 100 index values to represent equity performance. For gold, we use its price in Rupees. Due to the unavailability of a comprehensive debt index covering the required period, we have used the NAV of the SBI Magnum Gilt Fund (Growth Plan) as a proxy for debt.

This analysis spans more than 21 years, from January 1, 2003, to May 14, 2024. The excel download is given below.

Inputs for the asset allocation analysis tool

This section will explain how to use various sections of the tool.

#1 Asset allocation

In the top-left section of the tool, you can enter your desired allocations for Equity and Gold. The Debt allocation will be automatically calculated based on the remaining percentage. Ensure that the combined total of Equity and Gold allocations does not exceed 100%. If the sum exceeds 100%, the values will be ignored.

#2 Rebalancing

Here you can set the rebalancing mode to one of the following: Periodic, Percentage deviation, and Hybrid.

• Periodic Rebalancing: Enter the period in months. The portfolio will be rebalanced at these specified intervals.
• Percentage Deviation Rebalancing: The portfolio will be rebalanced only if any asset class deviates from its target allocation by a specified percentage. For example, if your initial allocation is 55% Equity, 35% Gold, and 10% Debt. A significant market movement might change your allocation to 60% Equity, 31% Gold, and 9% Debt. Since the equity allocation has deviated by 5% from the original, a rebalance will be triggered.

• Hybrid Rebalancing:The portfolio will be rebalanced both periodically and when a percentage deviation occurs.

#3 Baseline returns

Often, it’s useful to compare your portfolio’s performance against a baseline return to see if it meets your expectations. For instance, you might want to check if your portfolio consistently achieved at least a 6% return over any 3-year period to assess its performance relative to inflation.

#4 Point to Point returns

This section displays the point-to-point returns for the entire period from January 1, 2003, to May 14, 2024, for each asset class. It also shows the returns for the portfolio based on your desired asset allocation and selected rebalancing mode. In the later part of the excel, you will be given an option to change these values, based on your expectation. More on this in the section (further below) – ‘Advanced Inputs’.

What the asset allocation tool shows

In this section, the portfolio and the three asset classes will be analysed across various metrics. The analysis will be conducted for four different rolling return periods: 1 Year, 3 Years, 5 Years, and 10 Years.

The metrics:

• Average returns percentage: Average returns for the asset class/portfolio. In the above example, average 1-year returns of the portfolio is 15.27%

• Sharpe: A measure of risk-adjusted returns. It evaluates returns generated over a risk-free rate, adjusted for volatility. The higher the Sharpe ratio, the better.
• Minimum returns percentage: The lowest return or the worst observed in the period. In the above example, Equity has seen a lowest 1-year return of -57.54% 
• Maximum returns percentage: The highest return observed. In the above example, Gold has seen a higher 1-year return of 77.65%
• Standard deviation: A measure of volatility. The lower the Standard deviation, the better.
• Percentage of instances of losses: The percentage of times the returns were negative. Assume there are 1000 instances of 1-year returns in the observation and 200 of them are negative. In this case, the percentage of instances of losses is 20%. The lower, this is, the better.
• Percentage of instances of returns below baseline: The percentage of times the returns fell below a given baseline return. For example, If you set a 10% baseline return and observe that returns fell below 10% in 30% of the instances, this percentage is 30%. The lower, the better.

Advanced inputs

The analysis is based on historical data, but you can make adjustments to test specific scenarios in the Advanced section, located at the bottom of the sheet after the rolling return analysis. However, when you do this, please make sure your return expectations are realistic 

Customizing Debt Returns

You can change the debt index to a fixed return rate. For example, if you want to see how the portfolio would perform if the debt component had a stable 7% return, select “Yes” in the “Replace Debt Instrument” field and enter a 7% return. You can set the debt return to any value between 2% and 10%.

Adjusting Equity and Gold Returns

You can also adjust the returns for Equity and Gold to see how these changes would impact performance. Instead of replacing the indices, you can apply an additional return to either or both:

• Equity Returns: Increase or decrease returns by up to ±6%. For instance, if you want to see the impact of equity returns being 1 percentage points higher, you can adjust this in the section.

• Gold Returns: Similarly, you can increase or decrease returns by up to ±6%. For example, if you want to see the effect of gold returns being 2 percentage points lower, you can configure this setting accordingly.

Do note that the volatility of modified Equity, and Gold index values will be similar to the original indices.

Let us take an example of finding Optimal Debt Allocation to Minimise Portfolio Returns Below 8%.

Let’s say we want to determine the optimal allocation of a 6% yielding debt to minimise the instances of 1-year portfolio returns falling below 8%.

1.Adjusting Debt Instrument: In the Advanced section, select “Yes” to Replace Debt Instrument. Set the debt return as 6%.

2. Setting Baseline Returns: Set the baseline returns as 8% (illustrative number).

3. Analysis:

• With a 100% equity portfolio, 1-year returns fall below 8% 35.25% of the time.
• A 50% Equity and 50% Debt portfolio experiences this 38.4% of the time.

4. Optimization:

• Experiment with different equity allocations to observe changes in the percentage of instances of returns below the baseline.
• At around 85% equity allocation, the percentage of instances of returns below 8% is minimised.

Warning: Considerations and Limitations

Please note that the tool does not account for tax implications. In practical scenarios, manual rebalancing or changes in investment vehicles may trigger tax events. For instance, switching between funds, especially active mutual funds is a likely scenario over a long period. This may result in capital gains and tax obligations.

Conclusion

It’s crucial to remember that past performance does not ensure future results. While this tool will give you insights, it’s essential to use it for general guidance rather than making precise plans.

Be mindful of any adjustments made in the advanced section, as they can significantly impact the analysis. 

Disclaimer: This tool is not advisory in nature and is meant to equip you with calculations and analysis for various asset allocation scenarios. The final decision of asset allocation must be based on your risk profile, age, income and goal, among many other factors. Consult a financial planner before making investment decisions if you are new to investing or have not devoted the time necessary to understand equity and debt markets.

General disclosures & disclaimers