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 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 mortgage and loan templates that can be downloaded for free from this site.

The are a number of financial templates on the Microsoft.com site, so 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.

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"

I'm not going to list all of the financial modeling software that is available for Excel, but I will offer a few suggestions:

1. Take a look at the financial functions that are already available in Excel.
   
   
2. Look for add-ins that provide additional financial functions. Many add-ins include specialized functions that you can use to create your own models.
   
   
3. 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.

• Business and Financial Modeling
• Decision and Risk Analysis
• Finance
• Forecasting
• Personal Finance