Identifying Tail Risk
Microsoft Excel has been the go-to tool for financial modeling for decades, offering unparalleled flexibility and power. VBA further enhances Excel’s capabilities with a built-in programming language.
Financial modeling is critical for evaluating scenarios and understanding potential risks, especially tail risks—the possibility of rare but severe events. For example, a regional bank with X in deposits and Y in Treasury bonds must evaluate at what point bond devaluation and withdrawals could trigger a liquidity crisis. Each rate hike can significantly alter these dynamics, making it essential for leaders to understand the risks in their business.
While some models are simple and remain in Excel, scaling up to run simulations across 100,000+ scenarios often requires programming languages like Python or R.
A global tech firm was struggling to accurately forecast cost per head (CPH) for their organization of 100k+ employees. While they could achieve top-line forecasts within 1% of actuals, accuracy broke down significantly at the senior leader level, with misses exceeding 10%.
Tony built a forecasting model that used historical salary data, international tax trends, projected hiring forecasts, and other inputs to create a comprehensive output that came within 1% of actuals at the leader level. This improved accuracy enabled better budget planning and resource allocation across the organization.
Prior to Tony’s involvement, the HR team had requested a team of 41 people (a mix of engineers and product managers). Not only did Tony solve the problem with this model, but he also saved the company from hiring 41 people, representing millions in annual cost avoidance.
A New York contracting firm wanted to evaluate the feasibility of becoming a real estate developer in Manhattan. Without understanding the break-even point across various scenarios, estimating ROI was nearly impossible.
Tony built a detailed financial model in Excel with over 100 inputs, allowing the firm to simulate different loan types (e.g., interest-only, fixed-rate, balloon payments), purchase prices, renovation costs, maintenance, rental revenue, and other variables. Users could set ranges for key variables like interest rates and holding periods to compare ROIs under different deal structures.
This model helped the firm avoid unprofitable deals and focus on opportunities with higher returns.
Traditional financial advice—save money, invest in stocks, and buy a house—often feels abstract and long-term. Without clear insights, many struggle to see the true benefits of these actions, especially on tight budgets.
Tony created Exploring Finance, a website to help individuals analyze financial decisions. Tools like the compounding interest and homebuyer model compare the benefits of buying a home versus investing in the stock market over time.
For users asking, “How do I invest?” Tony wrote an article on target asset allocation and built a portfolio builder tool to compare up to five portfolios over any time period.
A consulting group approached Tony with a volatility financial strategy. Despite its potential, the model needed refinement.
Tony scaled the model, modifying parameters and running over 50,000 scenarios. He validated backtests by setting up an automated trading application using Python, Alpaca, and AWS Lambda. The system rebalanced portfolios daily and used automated emails to report performance metrics like returns, volatility, and drift.
The success of the backtests and live trading led to a partnership with the consulting firm to form WEBs Investments.