A fully-automated Mutual Fund Performance Analytics System, built entirely in Excel 2021, designed to calculate professional-grade fund metrics such as CAGR, Sharpe, Sortino, Alpha, Beta, Tracking Error, VaR, Drawdowns, SIP XIRR, Upside/Downside Capture, and more.
This project uses Quant Multi Asset Allocation Fund – Growth Option – Direct Plan data (2020–2025) for live demonstration.
The dashboard calculates 25+ industry-standard metrics, including:
- Absolute Return
- CAGR (Fund + Benchmark)
- Monthly & Annualized Volatility
- Sharpe Ratio / Sortino Ratio
- Beta vs Benchmark
- Alpha (Monthly & Annualized)
- R-Squared
- Tracking Error
- Information Ratio
- Treynor Ratio
- Max Drawdown
- Calmar Ratio
- Upside & Downside Capture
- Hit Ratio
- VaR (95%)
- SIP XIRR
- SIP Value & Units
- Benchmark comparison (NIFTY 500 TRI)
Every calculation is Excel 2021 safe (no XLOOKUP, FILTER, or dynamic arrays).
Includes two visuals:
- Fund NAV Trend (Line Chart)
- Benchmark value (Graph Chart)
The Calc sheet performs all calculations using:
- Monthly returns
- Benchmark returns
- Excess returns
- Drawdown model
- Active return series
- Risk metrics
- SIP modeling engine
This makes the file modular, scalable, and recruiter-friendly.
This model demonstrates professional skills in:
- Financial Modeling
- Portfolio Analysis
- VBA-free Excel Automation
- Risk Measurement
- Data Cleaning & Restructuring
- Dashboarding & Visualization
It replicates real-world Asset Management analytics frameworks used in:
- Fund Management
- Portfolio Research
- Risk Management
- Wealth Advisory
- Microsoft Excel 2021 (100% compatible)
- Power formulas (INDEX/MATCH, Array Calculations, Covariance, Variance, Percentiles)
- Clean charting
- Professional formatting
- Manual data transformation (no Power Query)
- Download the Excel file.
➡️ Click here to download the full Excel model
- Enter your fund’s NAV history in the
NAV_Datasheet. - Enter your Benchmark values into the Benchmark column.
- Summary & Dashboard update automatically.
Quant Multi Asset Allocation Fund – Growth Option – Direct Plan Analysis Period: January 2020 – 11 November 2025
This project showcases real-world capabilities in:
- Portfolio analytics
- Quantitative finance
- Risk modeling
- Excel automation
- Dashboard building
- Benchmark comparisons
- Professional reporting
Recruiters in Asset Management, Investment Research, Risk, Consulting, and Data Analytics can evaluate both technical depth and presentation quality.