(PLEASE USE EXCEL TO ANSWER THE FOLLOWING)

You have fallen in love with a 4-bedroom house in Exton. The current owner wants to sell it for $520,000. The rate on a 15-year fixed mortgage depends on your credit score. Assume that you have to make a 20% downpayment on the house to qualify for the mortgage.

1. If your credit score is 705, find the mortgage rate (cell B3) corresponding to your score using VLOOKUP. Name the green table "credit.score"

2. Find the monthly mortgage payment (cell B12)

3. Create an amortizaton schedule for the mortgage. Use functions IPMT (interest component) and PPMT (principal component) to calculate the interest and principal components of each mortgage payment over the life of the mortgage.

4. Create a two-way data table to show how a monthly mortgage payment changes as the APR and the house price change over the following ranges: APRs range from 3.25% to 6.5% with 0.1% increments and the house price ranges from $260,000 to $700,000 with $20,000 increments

Excel.Assignment.TEMPLATE (2) - Saved to my Mac View e Share Comments AutoSaveFF AO S E = Home Insert Draw Page Layout Formulas Calibri (Body) 11 A M 3 BIUD A Data Review = = = 21 IE DE Currency $ % Conditional Formatting Format as Table Cell Styles Insert Delete Format AS 2 Sort & Filter Paste Ideas % %- Find & Select Sensitivity X F G H. 1 HOUSE PRICE 2 CREDIT SCORE APR 4 TERM 5 FREQUENCY 6 RATE 7 NPER 8 PV 9 FV 10 TYPE 11 PMT SCORE APR 525 6.500K 640 6.400% HOUSE PRICE 650 6.250K 660 6.100% 670 S.950k 680 S.800k 690 S.GSOK 200 S.S00% 210 S.350K 720 5.200% 230 S.OSOK 4.900k 750 4.750X 260 4.600% 220 4.450% 4.300k 290 4.150% 800 4.000k 810 3.850k 820 3.700X 930 3.5SOK 840 3.400% 950 3.250% MONTH BEG BALANCE PMT IPMT PPMT END BALANCE INSTRUCTIONS SOLUTION + E E 0 - - + 100% Excel.Assignment.TEMPLATE (2) - Saved to my Mac View e Share Comments AutoSaveFF AO S E = Home Insert Draw Page Layout Formulas Calibri (Body) 11 A M 3 BIUD A Data Review = = = 21 IE DE Currency $ % Conditional Formatting Format as Table Cell Styles Insert Delete Format AS 2 Sort & Filter Paste Ideas % %- Find & Select Sensitivity X F G H. 1 HOUSE PRICE 2 CREDIT SCORE APR 4 TERM 5 FREQUENCY 6 RATE 7 NPER 8 PV 9 FV 10 TYPE 11 PMT SCORE APR 525 6.500K 640 6.400% HOUSE PRICE 650 6.250K 660 6.100% 670 S.950k 680 S.800k 690 S.GSOK 200 S.S00% 210 S.350K 720 5.200% 230 S.OSOK 4.900k 750 4.750X 260 4.600% 220 4.450% 4.300k 290 4.150% 800 4.000k 810 3.850k 820 3.700X 930 3.5SOK 840 3.400% 950 3.250% MONTH BEG BALANCE PMT IPMT PPMT END BALANCE INSTRUCTIONS SOLUTION + E E 0 - - + 100%