Mortgage Calculator Database

SQL Server Compact

In the last post I gave a basic overview of my reasons and goals for Mortgage Calculator.

I love Microsoft. In particular I love how much time and effort they spend making the developer's life easier.

When I do anything outside my current job I default to .NET and C#.

While there are many capable database technologies out there, I was most certainly going to settle on something Redmond has implemented.

For Mortgage Calculator, I wanted something simple, light and capable for the database back end.

Aside from SQL Server, MS has Access.

SQL Server itself comes in a variety different flavors. The 2 most applicable being:

• SQL Server Express
• SQL Server Compact

MS Access is out, because, well it's Access. Also, I was keen on using LINQ to SQL.

SQL Server Express is more then capable. However, in order to use Express in your application you need to embed the SQL Server Express install into your install. This might be acceptable but I would just prefer to avoid it.

SQL Server Compact is file based. The beauty of which is that installing it to a user's machine involves adding the .sdf file and a few dlls for communicating with the file.

Database Table(s)

Because the concept of Mortgage Calculator is rather simple the information that needs to be stored is also simple.

Remember from the first post we want to keep track of the following values:

• Principal
• Interest
• Extra Payments
• Balance
• Home Value
• Tax
• Insurance
• Association Dues
• Maintenance
• Total Monthly Cost
• Equity
• Loan To Value
• Rental Income
• Cash Flow
• Tax Appreciation
• Insurance Appreciation
• Association Dues Appreciation
• Maintenance Appreciation
• Rent Appreciation
• Home Value Appreciation

Again, due to the simplicity of the app it is assumed there is only one user per install (potential future improvement). All we are left with is individual Mortgages that need to be amortized. Therefore we have 1 table.

Lets create it.

Numeric to Decimal

Because float and double values are not exact (Greg Dolley has a good write up on the Double vs Decimal issue) and it's accepted practice, we are going to be typing our money and interest rates in decimal in C#.

Decimal's counter part in SQL Server Compact is numeric.

Notice we have 2 different numeric types:

1. numeric(18,2)
2. numeric(18,5)

The first number is Precision and determines the number of digits in the value. The second is Scale and determines the number digits to the right of the decimal place.

So our values become something like the following:

1,234,567,890,123,456.78
1,234,567,890,123.45678

Because those values can become large and using decimals can be 20x more slow then doubles or floats we have to be careful to limit the input and processing on the machine.

In a future release I will have to update the interest rates to have more scale.

Currently, rounding is 5 decimal places which means rounding can result in:

• $100,000,000 x 0.000009 = $900, $900 / 12 = $75

This means someone lost $75 a month. We want to aim for less than $0.01 a month.

• $100,000,000 x 0.0000000009 = $0.09, $0.09 / 12 = $0.0075

Now someone is only losing $0.09 a year. 18 places means the interest rate can be 1,234,567.0000000009%. We could cap the interest rates at 1000% and feel comfortable that anything over that is for theoretical purposes and outside the scope of this project.

Show - Expand

There are several "Show" and "Expand" bit/boolean values in the table. These deal with preserving GUI state for different Mortgages and will be covered in a future post.

Null or Not Null

The only columns that are not allowed to be null are the Key, Name and the values necessary to do a basic Amortization.

• Key
• Name
• Principal
• Interest Rate
• Term

For everything else we would prefer not to enforce updating information if we don't have to.

In the next post we will be exploring more of the xmal side of our Navigation and Home Page.


Download the Code Here

Mortgage Calculator App

WPF

In order to get a better understanding of Windows Presentation Foundation (WPF) I've decided to dive right in.

A while back I had created a Mortgage Calculator in C# .NET 1.2 as a Windows Form Application:

However, the GUI implementation left something to be desired. I wanted to avoid using modal forms. Instead the UI needed to be isolated to one window. While a trivial task, adding 2 panels to a form and switching the visible property seemed like a hack and annoying to maintain. Other suggestions seemed to suffer from the same basic problems.

Enter WPF. WPF uses DirectX which pipes all the UI processing to the GPU. This saves the CPU from wasting calculations. The following post on stackoverflow.com has a fairly succinct break down of it.

Instead of generating the GUI with pure code as Windows Form Application does, WPF uses Extensible Application Markup Language (XAML). XAML is an XML based user interface markup language. It's values define UI elements, data binding, events and more.

Using WPF, implementing a form with multiple GUI states doesn't feel as clumsy. Also, databinding takes away much of the coding I used to encounter with loading, validating and updating values in the database.

Mortgage Calculator

The Mortgage Calculator is intended to give your average everyday landlord/amateur investor a rough estimate of what his properties vital stats will be month by month in the amortization schedule.

A problem with most Mortgage Calculators, especially at Real Estate or Mortgage websites, is their lack of data. In particular, leaving out Tax and Insurance from the monthly payment might give someone unrealistic expectations of their out of pocket costs.

Mortgagecalculator.org includes tax but leaves out insurance and Association dues.

As an example lets pull up a listing from http://mihome.mobi/:

Plugging this information into MortgageCalculator.org we get:

$24,375.00 doesn't tell us much. Did the property tax increase over the 30 year amortization or is this a fixed dollar amount every year?

$24,375 breaks down to $812.50 a year.

Again we have no idea about Insurance or Association Dues.

When we scroll down in the listing we find that the tax was underestimated.

Perhaps we had the wrong percentage? Yes, but home values used to calculate property tax themselves are figured out by a city tax assessors who's valuations can differ from the current market value. Simply stated the only tax rate that matters is the current dollar amount listed, which in this case is $582.50 higher.

Principal and Interest Payment: $348.93
Tax: $1,395 / 12 = $116.25
Insurance: $500 / 12 = $41.67
Association Dues: $246

Total Payment: $752.85

Almost double what MortgageCalculator.org showed!

What about the changes in tax, insurance and other costs? If the total payment now is $752.85 it will most certainly become more expensive in the future.

I designed Mortgage Calculator to address 3 main areas where details were lacking:

1. Total cost of owning and maintaining the house.
2. Future appreciation of all costs.
3. Rental stats.

In the amortization schedule I would want to track the following:

• Principal
• Interest
• Extra Payments
• Balance
• Home Value
• Tax
• Insurance
• Association Dues
• Maintenance
• Total Monthly Cost
• Equity
• Loan To Value
• Rental Income
• Cash Flow

On top of this I would want to assign appreciation rates to the following values:

• Tax
• Insurance
• Association Dues
• Maintenance
• Rent
• Home Value

In our next post we will be dealing with creation of the Database.


Download the Code Here