💼 Asset Allocation & Return Forecasting Model in Excel VBA

Private Equity Investment Planning under Economic Uncertainty

📄 Project Overview

This project features a dynamic Excel VBA-based model designed to support strategic capital allocation for private equity firms managing large portfolios. The model enables decision-makers to simulate return forecasts across various asset classes while incorporating macroeconomic concerns such as interest rate hikes and recession risks.

Tailored for use by risk managers like William, this tool assists in optimizing the deployment of £50 billion across sectors and time horizons of 1 to 3 years. It provides a structured and user-friendly interface for scenario analysis and return projections.

📊 Objectives

• Allocate capital across three primary asset classes with defined investment constraints.
• Forecast investment returns under best-, worst-, or average-case scenarios.
• Include cash reserves earning bank interest to manage liquidity.
• Enable flexible analysis over 1-, 2-, and 3-year time horizons.
• Validate inputs and provide interactive user feedback via VBA alerts.

📁 Dataset

• Source: Manually entered financial assumptions (not external datasets)

• Input Format: User-defined return percentages for each sector and sub-sector

• Asset Classes:

  • Asset Class 1 (Max 15%)
  • Asset Class 2 (Max 40%)
  • Asset Class 3 (Max 25%)
  • Cash Reserve (Up to 20%)

🧰 Tools Used

• Microsoft Excel with VBA:

  • Input validation using If...Then and MsgBox
  • Dynamic return calculation based on time and capital inputs
  • VBA automation for scenario simulation
  • Macro-enabled forms for user interaction

📂 Repository Structure

capital-allocation-vba-model/
1. code/                  # Project vba code
2. report/                # Project report or documentation
3. README.md              # Project overview and user guide

✅ Outcome

The model helps private equity decision-makers:

• Evaluate multiple investment scenarios under uncertainty.
• Ensure capital allocation stays within strategic bounds.
• Calculate overall expected returns across different timeframes.
• Make confident, data-informed investment decisions through Excel automation.