How to Build Financial Models With Microsoft Excel

Excel can handle financial models easily.

Microsoft Excel is a spreadsheet application. Because of its many formula features and built-in financial functions, the program is well-suited to financial modeling. A financial model is any system that manipulates numbers to display the results of financial scenarios. For example, you might use a financial model to see how different interest rates affect the monthly payments on a loan. In Excel, you can get started building financial models right away.

Things You'll Need

• Microsoft Excel

Instructions

1. Browse the list of financial functions built into Excel. You can do this by either viewing Excel information from the Microsoft website, or by clicking the "fx" button in the Excel program window (under the "Formulas" tab) and then selecting "Financial" from the category list.

2. View the details for the financial function that suits the model you wish to build. For example, if you wish to see how interest rates, principals or loan terms affect monthly loan payments, look at the "PMT" function.

3. View the required parameters for your desired function to work in a financial model. For "PMT," these include "Rate," "Nper" and "Pv" for, respectively, the interest rate per payment, the number of payments and the principal of the loan. In some versions of Excel, there are two optional parameters: fv and type. they are not needed for this example, but you could add them if you wish.

4. Click in cell A1 in Excel and type the first of the required parameters. For function "PMT," this is "Interest Rate." Press "Enter" when you are done typing.

5. Type the rest of the parameters for the function in the cells under A1. For "PMT," this would include "Nper" in A2 and "Pv" in A3. You may type whatever label you wish, as long as it is obvious to you what it represents.

6. Type the values for the financial model in the cells to the right of each of the typed labels. In this example, type an interest rate into cell B1, the total number of payments into B2 and the loan amount in B3.

7. Type the label for the financial model's result into cell A4. For "PMT," you could type "Monthly Payment."

8. Click in cell B4. This is the cell that will contain the formula for the financial model.

9. Click the "fx" button on the Excel formula bar to show the function window.

10. Select the "PMT" function, or any other function you selected, in the list of built-in functions. Press "OK" when finished. A new pop-up window will appear that guides you through the formula creation process.

11. Click in the first field in the function window. Then, click on the cell in Excel that contains the value for that entry. For example, for "PMT," click in "Rate" and then click on cell B1, which contains the interest rate. However, the function window alerts you at the bottom that the rate is per-payment. Thus, add a "/12" after the "B1" to indicate that this rate is divided among 12 months.

12. Click in each of the other fields and select the spreadsheet cell that contains the appropriate information. Press "OK" when finished. The financial model displays the monthly payment based on your criteria.

13. Click in any of the variable cells, such as interest rate, and type a new value. Press "Enter" and the financial model automatically updates with the new monthly payment.

Tips & Warnings

• Note that, if you are creating a financial model for monthly loan payments on a five-year term, the total number of payments would be 5 X 12, or 60, for cell B2.

• As you gain experience working with Excel, you will eventually create more complex models using multiple financial functions.

References

• Microsoft Office: Financial Functions
• Microsoft Office: PMT Function
• Vertex 42: Financial Modeling with Excel Spreadsheets
• Ozgrid: Excel's Insert Function