Question

U NU! UU SU LUUD 22 - EP 000 Font Number Format Cells Themes Title Fill - Clear EE General Merge - $ % , oO Total Formatting Insert Delete Format Themes Aa' I J K L M N O P Q R S T Home Layout Tables Charts SmartArt Formulas Data Review Edit Alignment , Calibri Light (He... - 18 - A- A- = = = abc Wrap Text Este B 1 y 2. SA A1 % fx Titan Off-Campus Shops A B C D E F G H Titan Off-Campus Shops Property Loan Analysis Payment Calculator Property Location Fullerton Payments per year Property Cost $750,000 Total Payments 360 120 Down Payment $75,000 Interest (APR) 4.00% 3.50% Loan Amount $675,000 Payment (per month 3.75% Period (Years) 4.00% Goal #1: Increase Down Payment 4.25% Property Location Payments per year 4.50% Property Cost Total Payments 4.75% Down Payment Interest (APR) 5.00% Loan Amount Payment (per month) 5.25% Period (Years) 5.50% Goal #2: Lower Property Cost 5.75% Property Location Payments per year 6.00% Property Cost Total Payments 6.25% Down Payment Interest (APR) 6.50% Logn Amount Payment (per month) 6.75% Period (Years) 7.00% Payment Options Monthly Payments 180 $750.000 240 300 360 30 ET

1. Re-save the file to either your desktop or other storage device using the name“firstName_LastName_L4_Titan_Property”. (Note firstName and LastName are your own first and last names).

2. The upper left area of the worksheet is a Payment Calculator. Use a function to calculate the monthly payment (D7) using the data provided.

3. In the Payment Calculator, use “Goal Seek” to keep the payment per month at $3,000 by increasing thedown payment.

4. Copy the info from B4:B8 and paste to cells B10:B14. Copy D4:D7 and paste to D10:D13. Note: Remember to use “Paste Special” and use Values and Number Formats.

5. Reset the value in B6 back to $75,000.

6. Perform a second “Goal Seek” in the Payment Calculator again keeping the payment per month at

   $3,000 this time by lowering the property cost.

7. Copy the info from B4:B8 and paste to cells B16:B20. Copy D4:D7 and paste to D16:D19. See note in

   step 4.

8. Reset the value in B5 back to $750,000.

9. The upper right area of the worksheet will display the Payment Options. Create a two-variable Data

   Table to show the monthly payment for each of the interest rates by the number of monthly payments.(Note: all values in each cell will be different. If they aren’t, you can erase the table by highlightingcells H6: L20 and deleting them).

10. Format the numbers in your Data Table so the that top row is Accounting Style with 0 decimal places and the remaining numbers are Comma Style with 0 decimal places.

Answer 1

Titan Off-Campus Shops
Property Loan Analysis
Payment Calculator						Payment Options
Property Location	Fullerton	Payments per Year	12				Monthly Payments
Property Cost	$7,50,000	Total payments	360			$3,223	120	180	240	300	360
Down Payment	$75,000	Interest (APR)	4.00%			3.50%	6,675	4,825	3,915	3,379	3,031
Loan Amount	$6,75,000	Payment (Per Month)	$3,223			3.75%	6,754	4,909	4,002	3,470	3,126
Period (Years)	30					4.00%	6,834	4,993	4,090	3,563	3,223
Goal #1: Increase Down Payment						4.25%	6,915	5,078	4,180	3,657	3,321
Property Location	Fullerton	Payments per Year	12			4.50%	6,996	5,164	4,270	3,752	3,420
Property Cost	$7,50,000	Total payments	360			4.75%	7,077	5,250	4,362	3,848	3,521
Down Payment	$1,21,616	Interest (APR)	4.00%			5.00%	7,159	5,338	4,455	3,946	3,624
Loan Amount	$6,28,384	Payment (Per Month)	$3,000			5.25%	7,242	5,426	4,548	4,045	3,727
Period (Years)	30					5.50%	7,326	5,515	4,643	4,145	3,833
Goal #2: Lower Property Cost						5.75%	7,409	5,605	4,739	4,246	3,939
Property Location	Fullerton	Payments per Year	12			6.00%	7,494	5,696	4,836	4,349	4,047
Property Cost	$7,03,384	Total payments	360			6.25%	7,579	5,788	4,934	4,453	4,156
Down Payment	$75,000	Interest (APR)	4.00%			6.50%	7,664	5,880	5,033	4,558	4,266
Loan Amount	$6,28,384	Payment (Per Month)	$3,000			6.75%	7,751	5,973	5,132	4,664	4,378
Period (Years)	30					7.00%	7,837	6,067	5,233	4,771	4,491

- 2 X _-_L4 Titan Property - Microsoft Excel Developer File Home Insert Page Layout Formulas Data Review View * Cut Calibri = V General 11 AA = = A Wrap Text Merge & Center - BI U D 9 88 Insert Delete Format % Number AutoSum A Fill - Sort & 2 Clear Filter Editing E Copy Paste Format Painter Clipboard M1 А Find & Select Styles Formatting as Table Styles Font Alignment Cells в с H M N | о р E F G Titan Off-Campus Shops Property Loan Analysis 3 12 360 4.00% $3,223 2 Payment Calculator 4 Property Location Fullerton Payments per Year 5 Property Cost $7,50,000 Total payments 6 Down Payment $75,000 Interest (APR) 7 Loan Amount $6,75,000 Payment (Per Month) 8 Period (Years) 30 9 Goal #1: Increase Down Payment 10 Property Location Fullerton Payments per Year 11 Property Cost $7,50,000 Total payments 12 Down Payment $1,21,616 Interest (APR) 13 Loan Amount $6,28,384 Payment (Per Month) 14 Period (Years) 30 15 Goal #2: Lower Property Cost 16 Property Location Fullerton Payments per Year 17 Property Cost $7,03,384 Total payments 18 Down Payment $75,000 Interest (APR) 19 Loan Amount $6,28,384 Payment (Per Month) 20 Period (Years) 12 360 4.00% $3,000 $3,223 3.50% 3.75% 4.00% 4.25% 4.50% 4.75% 5.00% 5.25% 5.50% 5.75% 6.00% 6.25% 6.50% 6.75% 7.00% 120 6,675 6,754 6,834 6,915 6,996 7,077 7,159 7,242 7,326 7,409 7,494 7,579 7,664 7,751 7,837 Payment Options Monthly Payments 180 240 4,825 3,915 4,909 4,002 4,993 4,090 5,078 4,180 5,164 4,270 5,250 4,362 5,338 4,455 5,426 4,548 5,515 4,643 5,605 4,739 5,696 4,836 5,788 4,934 5,880 5,033 5,973 5,132 6,067 5,233 300 3,379 3,470 3,563 3,657 3,752 3,848 3,946 4,045 4,145 4,246 4,349 4,453 4,558 4,664 4,771 360 3,031 3,126 3,223 3,321 3,420 3,521 3,624 3,727 3,833 3,939 4,047 4,156 4,266 4,378 4,491 12 360 4.00% $3,000 30 25 BOU 100% -_ 1_ + Ready 1 Sheet1 Sheet2 Sheet3 2 Type here to search 15:52 o w O P 9 e 2 ^ ¢ ¢* ENG 0 17-02-2020 2 W

- 0 X _-_L4 Titan Property - Microsoft Excel Developer File Home Insert Page Layout Formulas Data Review View * Cut Calibri = V General 11 AA = = A E E Wrap Text Merce Merge & Center - Center BI U D E og 9 % % 0.0 .00 88 Insert Delete Format Conditional Format Formatting as Table Styles Cell Styles E Copy Paste Format Painter Clipboard M1 A AutoSum A Fill - Sort & 2 Clear Filter Editing Find & Select Font Alignment Number Cells B C D | | | | | | | | | | M E F Titan Off-Campus Shops Property Loan Analysis 3 2 Payment Calculator 4 Property Location Fullerton Payments per Year 12 5 Property Cost 750000 Total payments =D4*B8 6 Down Payment 75000 Interest (APR) 0.04 7 Loan Amount =B5-B6 Payment (Per Month) =-PMT(D6/12,D5,B7,0,0) 8 Period (Years) 30 9 Goal #1: Increase Down Payment 10 Property Location Fullerton Payments per Year 12 11 Property Cost 750000 Total payments 12 Down Payment 121616.278637422 Interest (APR) 0.04 13 Loan Amount 628383.721362578 Payment (Per Month) 3000 14 Period (Years) 30 15 Goal #2: Lower Property Cost 16 Property Location Fullerton Payments per Year 12 17 Property Cost 703383.721362577 Total payments 360 18 Down Payment 75000 Interest (APR) 0.04 19 Loan Amount 628383.721362577 Payment (Per Month) 3000 20 Period (Years) 30 =D7 0.035 0.0375 0.04 0.0425 0.045 0.0475 0.05 0.0525 0.055 0.0575 0.06 0.0625 0.065 0.0675 0.07 Payment Options Monthly Payments 120 180 240 300 360 =TABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5,D6) =TABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5,D6) =TABLE(D5, D6) =TABLE(D5,D6) =TABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5,D6) =TABLE(D5, D6) =TABLE(D5,D6) =TABLE(D5, D6) =TABLE(D5, D6) ETABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5,D6) =TABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5,D6) =TABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5, D6) ETABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5,D6) =TABLE(D5, D6) =TABLE(D5,D6) =TABLE(D5, D6) =TABLE(D5,D6) =TABLE(D5,D6) =TABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5,D6) =TABLE(D5, D6) =TABLE(D5,D6) =TABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5,D6) =TABLE(D5, D6) =TABLE(D5,D6) =TABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5, D6) =TABLE(D5,D6) =TABLE(D5, D6) =TABLE(D5,D6) 25 0 Ready Sheet1 Sheet2 Sheet3 2 O Type here to search + o W X O P 9 e di BOU 100% uc A la X ENG -_ 1_ 15:54 - 17-02-2020 2