Financial Modeling with Excel Spreadsheets
Many people do not realize that spreadsheets can be used for more than just creating tables and charts and summarizing data. Although spreadsheets were originally designed to function as electronic ledgers and accounting worksheets, they are now commonly used for creating complex mathematical models. Instead of just storing, summarizing, and formatting data, a financial model is used for analysis.
What is a Model? Generally, a financial model consists of one or more input parameters along with data and formulas that are used to perform calculations or make predictions. By changing the values of the input parameters, you can do "what-if" studies to see what happens when the inputs change. Some examples of financial models are the financial calculators that can be downloaded for free from this site.
Financial Templates and Spreadsheets for Excel
The are a number of financial templates on the Microsoft.com site, so besides Vertex42.com, that is the first place I would look for spreadsheets related to personal and business finance and accounting. A search for finance brings up about 100 templates for Excel, Word, PowerPoint, etc.
- Free Financial Spreadsheets - www.exinfm.com - This page by Matt H. Evans on his site, Creating Value through Excellence in Financial Management (exinfm.com), lists over 75 free spreadsheets related to accounting, personal and corporate finance, project management, and cash flow valuation.
- Financial Modeling Spreadsheets - www.FinancialModelingGuide.com - The templates section of Financial Modeling Guide links to free financial model templates submitted by their members. The rest of the site contains hundreds of tutorials and discussions on best practices for laying out spreadsheets for financial modeling, financial analysis, valuation and accounting.
- Excel Financial Templates Gold - Microsoft.com - If you are looking to buy a collection of Excel financial templates, this package comes with a "... bonus pack of 30 additional excel templates providing 'what-if' financial modelling, bond valuations, balanced scorecard templates, NPV tables, financial functions for Excel, financial ratios and many more."
Excel Finance Books and Articles
A number of books on finance, financial modelling, and building models using Excel and VBA are listed on the right. After you have a model built in Excel, one advanced method used in forecasting and decision making is Monte Carlo Simulation. For a basic introduction, take a look at the article, "Monte Carlo Simulation in Excel: A Practical Guide".
One of the attractions of using Excel for financial modeling is the variety of financial formulas at your disposal. For a complete list of the financial functions available in Excel, organized by category, see the article, "Excel Financial Functions and Formulas"
Financial Modeling Software for Excel
I'm not going to list all of the financial modeling software that is available for Excel, but I will offer a few suggestions:
- Take a look at the financial functions that are already available in Excel.
- Look for add-ins that provide additional financial functions. Many add-ins include specialized functions that you can use to create your own models.
- Look for add-ins that allow you to analyze a financial model that you have created. Crystal Ball and @Risk are probably the best that I know of for Monte Carlo simulation, decision, and risk analysis.
You can find a number of different add-ins in our directory of Excel Links. The related categories are listed below.