Creating An Investment Model: Excel Essentials

Creating an investment model in Excel is a valuable skill for anyone looking to make well-informed investment decisions. An investment model is an analytical tool used to assess the financial performance of an asset or portfolio of assets over time. It uses financial data, assumptions, and calculations to forecast the expected return and associated risks of a particular investment.

Excel is a versatile tool that allows users to build financial models that include income statements, balance sheets, and cash flow statements. It also enables users to perform sophisticated financial analyses, such as discounted cash flow (DCF) analysis and sensitivity analysis, and to develop valuation models to assess the worth of businesses and investments.

There are several types of financial models, including valuation models, budgeting models, and forecasting models, each serving a specific purpose. For example, valuation models are used to determine a company's or investment's value, often through methods like DCF analysis. Budgeting models help set financial targets and evaluate performance against budgeted figures, while forecasting models predict future financial performance based on historical data and assumptions.

Building a financial model in Excel requires understanding and utilizing Excel formulas effectively. Different formulas are suited for different types of investment models. For instance, formulas like VLOOKUP and HLOOKUP are useful for retrieving data from different cells, while SUM and AVERAGE are used to aggregate data and calculate means.

Creating an investment model can be a complex and intimidating task, but with some practice and industry knowledge, it can become easier and more reliable. By creating a detailed plan, utilizing reliable data sources, and mastering the tools provided by Excel, users can build accurate and reliable investment models to make smarter and more profitable decisions.

Data sourcing and organisation

Separate data into different worksheets

It is important to keep your data consistent and stored in specific worksheets so that it can be easily found when needed. Depending on the complexity of your model, data can become layered and dense. By creating distinct and clearly labelled worksheets, this job becomes much simpler.

Align data for easier navigation

Manipulating and formatting your data correctly is key. By aligning your data in a neat and visual way, you can avoid confusion and create a professional-looking model. Use formatting tools and functions to emphasise certain pieces of data if necessary. For example, bolding important formulas in a certain colour or using a background fill colour to make certain cells stand out.

Understand Excel formulas

Creating a successful investment model with Excel requires understanding and utilising Excel formulas effectively. Excel formulas allow you to automate calculations and make changes to your model quickly. Different formulas are suited to different kinds of investment models. For example, when analysing market data, you may want to use formulas like VLOOKUP or HLOOKUP to quickly retrieve data from different cells. Or you may use formulas like SUM or AVERAGE to aggregate data and calculate a mean. Knowing which formulas best suit your needs will make the process of creating your model faster and more efficient.

Learn the syntax for writing formulas

Once you've decided which formulas to use, the next step is to learn the syntax for writing them. Fortunately, Excel offers a helpful online guide that explains the syntax for each formula and provides examples of how to use them. Additionally, there are many online guides and tutorials available to further your understanding of how to write specific formulas. Learning the syntax is essential for creating an investment model in Excel, so take the time to familiarise yourself with how the formulas work.

Excel formulas and functions

• VLOOKUP and HLOOKUP: These functions allow you to search for specific data within a range of cells. VLOOKUP is used for looking up data in columns, while HLOOKUP is used for rows. They are particularly useful when dealing with large datasets and can help you retrieve information such as stock prices, financial ratios, or historical data.
• SUM and AVERAGE: These basic functions are used for aggregating data. SUM allows you to add up values in a range of cells, while AVERAGE calculates the mean. They are fundamental for calculating returns, revenue, expenses, and other financial metrics.
• Financial Functions: Excel offers a range of financial functions that are specifically designed for financial analysis. For example, NPV (Net Present Value) is crucial for evaluating investments and calculating intrinsic value. IRR (Internal Rate of Return) helps assess the profitability of investments. Other functions like PMT, PV, and FV are useful for analysing loans, mortgages, and investments with regular cash flows.
• Statistical Functions: For more advanced analysis, Excel provides a suite of statistical functions. For instance, using functions like STDEV.P or STDEV.S, you can calculate the standard deviation of a dataset, which is useful for risk analysis and understanding the volatility of investments. Other functions like COUNT, COUNTIF, and SUMIF help you analyse large datasets by counting or summing data based on specific criteria.
• DATE and TIME Functions: When dealing with time-series data or analysing investments over time, Excel's date and time functions become essential. For example, the TODAY function returns the current date, and the YEAR, MONTH, and DAY functions extract specific components from a given date. You can use these functions to calculate investment horizons, assess quarterly or annual performance, or identify seasonal trends.
• Logical Functions: Logical functions like IF, AND, OR, and IFERROR give you the ability to build logic into your model. For instance, you can use IF to perform different calculations based on certain conditions, such as determining whether an investment is profitable or not. IFERROR is useful for error handling and can help you avoid errors in your model by providing alternative calculations if a specific error occurs.

Mastering Excel formulas and functions is a key step in building robust and accurate investment models. They enable you to manipulate data efficiently, perform complex calculations, and make data-driven decisions with confidence. By leveraging the power of Excel's formulas and functions, you can streamline your investment analysis and gain valuable insights to make informed investment choices.

Presentation and data visualisation

Once you have gathered your data and organised it into a spreadsheet, you will need to decide how to present the information. Excel offers a range of options for displaying data, including line graphs, bar charts, and pie charts. These allow you to present your data in a format that is easy for viewers to interpret. You can also take advantage of the 3D graphs and bubble charts offered by Excel.

It is important to define the metrics that you want to measure. Examples of metrics include gross return, net return, and average annual return. You should also define any additional variables that you want to include, such as risk measures, drawdowns, and sharpe ratios.

Excel's capabilities extend beyond standard financial modelling, offering a wealth of features designed to elevate your modelling expertise. For example, you can delve into advanced tools such as data tables, supporting schedules, and specialised functions like VLOOKUP, HLOOKUP, and INDEX-MATCH. These features empower you to efficiently manage and analyse data, cross-reference information, and create intricate models.

Mastering these advanced Excel functions simplifies the modelling process and ensures that your financial models are accurate, visually appealing, and easily accessible to stakeholders.

When it comes to visualising data, you can create interactive charts and graphs for data visualisation, which will allow you to present your financial findings clearly and effectively.

Additionally, you can develop dynamic models with user-friendly interfaces, making it easier for stakeholders to interact with and understand the intricacies of your financial models.

Model construction

The construction of an investment model in Excel can be broken down into several steps. Firstly, it is important to determine the scope of the model, including the types of data to be used and the desired timeline for completion. This initial planning stage is crucial for ensuring the model is accurate and reflective of market conditions.

Once the scope is defined, the next step is to gather and organise the necessary data. This involves identifying reliable sources of data such as stock exchanges, financial services, and government agencies, and storing the data in separate, clearly labelled worksheets within Excel. This step is crucial for creating an effective and presentable model.

With the data in place, the focus shifts to understanding and utilising Excel formulas. Different formulas are suited to different types of investment models, so it is important to carefully consider the specific needs of your model. Common formulas used in investment modelling include VLOOKUP, HLOOKUP, SUM, and AVERAGE. Learning the syntax for writing these formulas is essential, and Excel provides a helpful online guide with examples.

After deciding on the formulas, the model can be constructed by adjusting the formulas to create the best representation of the data. This involves paying attention to all aspects, from simple subtotals to complex formulas that take various inputs into account. Be sure to add enough decimal points and clearly label each column for accurate interpretation of results.

Finally, the model should be tested to determine its accuracy. This can be done by running "what-if" scenarios and comparing the results with realistic inputs. This step helps identify any errors and make necessary adjustments to improve the model's accuracy. Additionally, data validation techniques can be used to identify incorrect information.

Building an investment model in Excel is a complex task, but with practice, it can become easier and more reliable. By creating a detailed plan, utilising reliable data sources, and mastering Excel tools, you can construct a model that provides valuable insights for making informed investment decisions.

Model testing

• Create Test Scenarios: Develop several test scenarios with realistic input data. Run these scenarios through your model and compare the results with the expected outcomes. This process will help you identify any errors or discrepancies in your model.
• Utilise Data Validation: Excel offers data validation tools that can assist in identifying incorrect or invalid data entries. Implement these tools to ensure the data entered into your model is accurate and within the expected range.
• Adjust Formulas: Pay close attention to the formulas you have used in your model. Review and adjust them as needed to ensure they are appropriate for the type of analysis you are conducting. This includes considering the level of complexity required and making necessary simplifications.
• Run "What-If Scenarios": Test the reliability of your model by adjusting input variables and observing the impact on the results. This will help you understand the sensitivity of your model to changes and the range of possible outcomes.
• Compare with Historical Data: Compare the results of your model with historical data, if available. This will help you validate the accuracy of your model's predictions and identify any deviations that may require further refinement.
• Cross-Reference with Other Sources: Compare your model's outputs with predictions or analyses from other sources, such as industry reports or expert opinions. This will help you identify any significant discrepancies that may indicate a need for further refinement.
• Stress Testing: Apply stress testing techniques to assess the robustness of your model. Modify key assumptions or input variables to represent more extreme or unfavourable conditions. Observe how your model performs under these stressed scenarios to gauge its resilience.
• Peer Review: Collaborate with colleagues or peers who have experience in modelling. Seek their feedback and insights on your model. They may identify potential issues or suggest improvements that you may have overlooked.
• Error Checking: Excel provides error-checking features, such as the "Error Checking" option under the "Formulas" tab. Utilise these tools to identify potential errors or inconsistencies in your formulas, cell references, or data entries.
• Scenario and Sensitivity Analysis: Incorporate scenario and sensitivity analysis into your model. Create toggles or data tables that allow you to easily modify key assumptions and observe the resulting changes in output. This will help you understand the impact of different scenarios on your investment decisions.
• Visualise Results: Utilise Excel's graphing and charting functions to visualise the results of your model. Presenting the data visually can often reveal patterns or anomalies that may have been overlooked in tabular form.
• Continuous Improvement: Model testing is an iterative process. Continuously refine your model based on the insights gained from testing. Identify areas where your model can be improved and make the necessary adjustments.

Remember, the goal of model testing is to validate the accuracy and reliability of your investment model. By following these steps and techniques, you can identify areas of improvement and make the necessary adjustments to increase the confidence in your model's outputs.

Frequently asked questions