Identifying Tail Risk
Microsoft Excel has been the default financial modeling tool for decades. Excel is extremely powerful and also flexible. VBA enhances this power by adding a programming language on top of the capabilities within Excel.
Building models is essential for businesses to understand how different scenarios may play out depending on how assumptions change. This is especially important to identify tail risk, or the possibility of a very bad event happening. For example, if you are a regional bank, and you have X in deposits and Y in Treasury bonds, at what level do the bonds become less valuable than the deposits? Then, how much can the bank afford to lose through withdrawals before a liquidity crisis emerges? Each quarter point rate hike can cause these numbers to fluctuate wildly, so it is critical that senior leaders understand the risk in their business!
Some models are simple and can remain in Excel. Other times, it may be important to scale up models and run simulations to see how things play out across 100,000+ scenarios. In these cases, building a model into a programming language such as Python or R becomes essential.
A contracting firm in New York wanted to explore the possibility of becoming a real-estate developer in Manhattan. They wanted to understand where the break-even point was given different scenarios. Without this information, it was very challenging to estimate the ROI of a big investment.
Tony constructed a financial model in Excel that contained over 100 inputs. It allowed the contractor to construct different loan types (interest only, 15/30 year fixed, balloon payments, etc.), purchase price, renovation costs, maintenance activity, revenue through rent, and several other variables. The model then allowed the user to set ranges for specific variables (e.g., interest rates and holding period) to compare the ROI of the same property given different deal structures.
The model helped the contractor avoid several bad purchases that would have lost money or broke even at best.
Most financial advisors will tell you to save money, invest in the stock market, and buy a house. However, the benefits of such activities are only realized after decades and might not be as big as they seem at first. This can make it hard to see the true value of pursuing such activities, especially when budgets are tight.
Tony built out an entire website called Exploring Finance to aid people analyze the impact of such activities. For example, the compounding interest and home buyer model shows how buying a house might not be nearly as beneficial as investing in the stock market over time.
The next question people ask is “how do I invest in the stock market?”. Tony wrote an article on target asset allocation and built a portfolio builder tool that gives users the ability to compare up to 5 different portfolios over any time period.
A consulting group approached Tony about a problem they had when trying to build out a financial trading strategy. The model was very close to working, but they couldn’t quite dial it in to beat out the major benchmarks.
Tony took the model and started scaling it up, modifying different parameters. After running over 50,000 scenarios, he identified where and when the strategy performed best. To validate the backtests, Tony set up an automated trading application that used Python, Alpaca, and AWS Lambda to rebalance portfolios at the end of each trading day. He then used Amazon Simple Email Service to send performance results daily, showing portfolio returns, volatility and portfolio drift.
The backtests and live trading proved so successful that Tony partnered with the consulting firm to form Precision Volatility. Precision Volatility is now working with asset managers to identify a go to market partner.