The Mortgage Spreadsheet

[Previous] [Next]

I want to begin by familiarizing you with the mortgage spreadsheet model itself. This model drives most of the Loan Calculation solution, so you need to understand how it works before we talk about how the rest of the solution uses it. You can load the Mortgage.htm file into Excel 2000, or you can load the original Mortgage.xls file. I created the Mortgage.htm file by simply saving the Mortgage.xls file as a web page. Figure 9-1 depicts the spreadsheet loaded into Excel 2000.

click to view at full size.

Figure 9-1. The mortgage spreadsheet in Excel 2000.

The spreadsheet is divided into four sections:

  • The loan variables Include purchase price, percent down, interest rate, years, and additional principal per payment.
  • The calculated variables Include down payment, loan amount, minimum monthly payment, and actual payment.
  • The loan results Include total interest paid, total payments, total number of payments, and date the loan will be paid off.
  • A full payment table Lists each monthly payment, noting the interest paid, payment, and principal amount remaining.

You can change the values of any of the loan variables and see the entire spreadsheet recalculate to reflect the new values.

Most of the formulas used are fairly simple, with a few exceptions. The minimum monthly payment uses the PMT function, and the number of payments and the date the loan is paid off are complex formulas referencing the payment table. Although this is not an overly complex spreadsheet, the techniques I describe in this chapter work equally well with a wide variety of more complex models. There are certain limits, however, which I will discuss momentarily.

As you will no doubt notice, I have formatted the spreadsheet to make the organization clear and to highlight the portions you enter vs. the portions calculated by Excel or the Spreadsheet component. You might also notice that the spreadsheet is protected and that only the loan variable cells are unlocked. This helps preserve the integrity of the model, by prohibiting users from randomly changing the calculations. Additionally, I have created a frozen pane in the spreadsheet so that the three upper sections are always visible while you scroll the payment table below them. I mention this now so that when I discuss returning this model to an interactive Spreadsheet control later, you will note that these features are preserved.

The ability to open and modify the Mortgage.htm file in Excel is what makes this scenario so interesting. In many cases, one person in your organization knows how to create the spreadsheet model and maintain it, but another person is responsible for building the business solution that might employ the model. For example, a mortgage broker would know how to construct this spreadsheet model (and probably could point out all its inaccuracies), but a software developer writing a web site might not be knowledgeable enough to construct the model, since he or she would know how to write ASP scripts but not how to put together a mortgage. The broker can build and maintain the model in the easy and familiar tool Excel, and the web site developer can simply use that model without needing to know how it was built. This is much like using the Microsoft Scripting Runtime library or the BrowserType object described in the next section.

Note, however, that since the Spreadsheet component has only one sheet, any model you want to use with it must exist on one sheet in Excel. The Spreadsheet component cannot load a multisheet model saved from Excel. If you try to use one sheet from a multisheet model, the Spreadsheet component will just use the last result value from any calculation involving cells on the other sheets.



Programming Microsoft Office 2000 Web Components
Programming Microsoft Office 2000 Web Components (Microsoft Progamming Series)
ISBN: 073560794X
EAN: 2147483647
Year: 1999
Pages: 111
Authors: Dave Stearns

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net