PMI [15]

You just landed your first real job. You are moving to Boston! You signed a 10-year1 contract with Night Gallery Inc. and received a $25,000 sign in bonus plus a generous $70,000 per year salary plus the promise of an annual increase of 3%. Congratulations!

You have searched the market and found the condo of your dreams! No doubt about it. You must have it. The price is $500,000 You are even thinking about renovating the kitchen with high end appliances.

The problem is that you do not have any money for a down payment. Which means you will need to get and pay for Private Mortgage insurance. PMI. Welcome to the real world! This is how it works:

Banks quote you a rate assuming you will bring 20% of the value of the house. That equity or skin in the game is something banks need to lower their exposure in case things go wrong. When people dont have it, banks require you buy insurance If you fail to pay, the PMI pays the bank. As you make payments, the portion of principal you repay starts to build up. As soon as you reach that 20% threshold, the bank drops the PMI and your payment is lowered. PMI is not free. PMI is typically about 1.5% interest on top of the quote you bank gives you. For example, if the bank quotes you a 3% per annum, and PMI is 1.5% you end up paying 4.5% per annum until the equity in your house reaches the 20%.

This whole PMI makes you think that it would be wise to pay something extra every month. You are convinced that a flat extra payment does not make much sense. You reason that $100 a month extra at the beginning of your career would be hard while it may be almost a rounding error as your salary increases. Instead, you decide to each month set aside 1% of your paycheck to submit as extra principal.

For example, if you make monthly payments, your first-year salary is $70,000 so you will send 70,000 x 0.01 / 12 = $58.33 per month. When your salary reaches $100,000, you will send ($100,000 x 0.01) / 12 =$83.33 per month.

Hint You will need to match the salary with the frequency of payments.

The bank is quoting you a few options.

1) A 15-year loan 10% down payment, PMI of 1% with monthly payments and a quoted rate of 3% 2) A 35-year loan with no money down, PMI of 2%, quarterly payments a rate of 3.5% 3) A 10-year loan with 5% down, PMI of 1.8% yearly payments and a balloon of $400,000. . With so many options, you decide to work on creating one spreadsheet to analyze all your options. Your task is this: Write a spreadsheet that works for any loan amount, any percent extra payment, any loan length (up to 35 years), any number of payments per year (for example quarterly payments, monthly, weekly, etc.), any balloon payment, any PMI rate and any annual interest rate. The spreadsheet should return a loan amortization table, the sequence of payments you would make, the number of payments to complete PMI and the number of payments needed to pay off the loan. The spreadsheet should drop PMI payments automatically. The spreadsheet should use only those cells needed. (On this spreadsheet, I should be able to vary the inputs and the spreadsheet should recalculate automatically and correctly) 1) A 15-year loan 10% down payment, PMI of 1% with monthly payments and a quoted rate of 3% 2) A 35-year loan with no money down, PMI of 2%, quarterly payments a rate of 3.5% 3) A 10-year loan with 5% down, PMI of 1.8% yearly payments and a balloon of $400,000. . With so many options, you decide to work on creating one spreadsheet to analyze all your options. Your task is this: Write a spreadsheet that works for any loan amount, any percent extra payment, any loan length (up to 35 years), any number of payments per year (for example quarterly payments, monthly, weekly, etc.), any balloon payment, any PMI rate and any annual interest rate. The spreadsheet should return a loan amortization table, the sequence of payments you would make, the number of payments to complete PMI and the number of payments needed to pay off the loan. The spreadsheet should drop PMI payments automatically. The spreadsheet should use only those cells needed. (On this spreadsheet, I should be able to vary the inputs and the spreadsheet should recalculate automatically and correctly)