Mortgage Calculator - Quia

Mortgage Calculator

Inputs Negotiated Cost of House Additional Down Payment Date of First Payment Credit Rating

$ 350,000.00 $ 3,500.00

5/1/2013 Excellent

Constants Property Tax Rate Down Payment to Avoid PMI PMI Rate Term of Loan in Years # of Payments Per Year

0.75% 20.00%

0.50% 30 12

Credit Excellent Good Fair Poor

Down Payment 5%

10% 15% 20%

APR 4.25% 4.50% 5.00% 5.25%

Intermediate Calculations APR Based on Credit Rating Min Down Payment Required Annual Property Tax Annual PMI

Outputs Total Down Payment Amount of the Loan Monthly Payment (P&I) Monthly Property Tax Monthly PMI Total Monthly Payment Date of Last Payment

4.25% $ 17,500.00 $ 2,625.00 $ 1,645.00

$ 21,000.00 $ 329,000.00

$1,618.48 218.75 137.08

$ 1,974.32 4/1/2043

Merge and center title Mortgage Calculator. Apply Bold, 18 point font size, and Aqua, Accent 5, Darker 25% font color.

Create labels in the Intermediate Calculations and Outputs sections. Assign a range name to each cell in the ranges F4:F7 and F10:F12.

Create and format the Inputs and Constants areas as shown. Enter and format the values in column C.

Create the lookup table beginning in cell A16 with the column headings Credit, Down Payment, and APR.

Create formulas in column F for the four items in the Intermediate Calculations section and seven items in the Outputs section.

Format each section with fill color, bold, underline, number formats, borders, and column widths.

Assign range names to cells in column C of the Inputs and Constants sections.

Create a footer on both sheets.

Student Name

Payment

exp2010_e02m2loan_ann_solution

Range Name AddDown APR AvoidPMI Cost CreditRating Date1st Loan MinDown Months Payment PMI PMIRate PropTax PropTaxRate Table TotalDown Years

Location =Payment!$C$5 =Payment!$F$4 =Payment!$C$11 =Payment!$C$4 =Payment!$C$7 =Payment!$C$6 =Payment!$F$11 =Payment!$F$5 =Payment!$C$14 =Payment!$F$12 =Payment!$F$7 =Payment!$C$12 =Payment!$F$6 =Payment!$C$10 =Payment!$A$17:$C$20 =Payment!$F$10 =Payment!$C$13

Paste a list of range names in the Range Names worksheet. At the top of the list type and format column labels.

Center the worksheet horizontally on the page.

Student Name

Range Names

exp2010_e02m2loan_ann_solution

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download