Answer the following questions beginning in row 12 of the Loan Details worksheet:

1. How much in total interest was paid during the life of option 1?
2. True or False: You will pay more in total interest during the life of Option 1 then you will pay in principle? Explain your answer.
3. How much in total interest was paid during the life of option 2?
4. Which mortgage option is best for you if you plan on owning your home for the next 30 years, and which mortgage is best if you plan on only owning this home for the next 2 years? You should base your answer on interest and out-of-pocket expenses (that do not reduce your principal) not how much you paid total for each option. Use numbers you calculated to support and explain your answers.
5. If you make an extra $100 payment per month to Option 1, how many years will it take you to pay off your mortgage? Copy your Amortization Option 1 data and paste it into the Amortization Option 1 + $100 worksheet. Add $100 to each cell in the additional payment column then add the $100 to the principle amount each month.
6. How much interest will your client save over the life of the loan by making the $100 extra monthly payment?
7. If you can afford only $1,500 per month, how much will the actual amount borrowed be for option 1 and for option 2?
8. You learn that you can offer your client a 5% interest rate lower interest rate on option 1 saving him thousands of dollars. Your company is in financial hardship and the CFO advises you to say nothing so your firm can pocket the savings. What would you do?

Answer Sheet:

Loan Analysis Worksheet
Purchase Price		$350,000
Option #	Down Payment	Nominal Interest Rate/yr	Duration (yrs)	Points	Fees	Loan Value	Actual Amount Borrowed	Monthly Payment
1	10%	6.25%	30	1	400
2	5%	6.75%	30	0	0
1.
2.
3.
4.
	Answer 4 Calculation		Option 1		Option 2
		Points and Fees
		Interest Paid (24 months)
		Interest Paid (360 months)
		Total 24 Months	$ -		$ -
		Total 360 Months	$ -		$ -
5.	d
6.	d
7	Option #1
	Option #2
	Ethics Answer:

Notes. To prepare the mortsheet, develop fummulas for payment 1, then copy down to the maximum number of payments. Absolute vs. Relative cell references play a big part here. Think about what cells to freeze before you fill them For each row, Monthly Payment=Interest - Principle. Across different rous, the Monthly Payment remains constant but the Interest and Principle will be different The interest rate needs to be adjusted for monthly compounding. Check figure the outstanding balance after the 10' payment for option lis 16,456.64 and far opsion 2 is $329,503.68. The Outstanding Balance after the last payment is applied should be $0. Below is an example with different figures. Do not use these for your project. These are for reference only. If you get stud try to build fomnialas ar make calculations that match this table. Consider the following assumptions: Amount Borrowed: 200.000 Interest Rate: 69% Tem 30 Years Marthly Payment constant, computed by PNT, function, adjusting for monthly compounding, The first few TOWS would look like this: Number Lonthly Payment Principle Interest $1.199.10 $1,199.10 $1.199.10 $1.199.10 $1.199.10 $1.199.10 $1.199.10 $1.199.10 + or a $199.10 $200.10 $201.10 5202.10 5203.11 $204.13 5205.15 $206.17 Outstanding Balance $200.000.00 $199.800.90 $199,600.20 $199,399.71 $199.197.60 S198,994.49 $198.79036 $198.585121 $198.9 79.04 $1.000.00 5999.00 $998.00 $997.00 $995.99 5994.97 $993.95 $992.93 8 Analysis: Anger the following questions beginning in tot 12 of the Loan Details worksheet: How much in tatal interest wag paid during the life of aption 12 Notes. To prepare the mortsheet, develop fummulas for payment 1, then copy down to the maximum number of payments. Absolute vs. Relative cell references play a big part here. Think about what cells to freeze before you fill them For each row, Monthly Payment=Interest - Principle. Across different rous, the Monthly Payment remains constant but the Interest and Principle will be different The interest rate needs to be adjusted for monthly compounding. Check figure the outstanding balance after the 10' payment for option lis 16,456.64 and far opsion 2 is $329,503.68. The Outstanding Balance after the last payment is applied should be $0. Below is an example with different figures. Do not use these for your project. These are for reference only. If you get stud try to build fomnialas ar make calculations that match this table. Consider the following assumptions: Amount Borrowed: 200.000 Interest Rate: 69% Tem 30 Years Marthly Payment constant, computed by PNT, function, adjusting for monthly compounding, The first few TOWS would look like this: Number Lonthly Payment Principle Interest $1.199.10 $1,199.10 $1.199.10 $1.199.10 $1.199.10 $1.199.10 $1.199.10 $1.199.10 + or a $199.10 $200.10 $201.10 5202.10 5203.11 $204.13 5205.15 $206.17 Outstanding Balance $200.000.00 $199.800.90 $199,600.20 $199,399.71 $199.197.60 S198,994.49 $198.79036 $198.585121 $198.9 79.04 $1.000.00 5999.00 $998.00 $997.00 $995.99 5994.97 $993.95 $992.93 8 Analysis: Anger the following questions beginning in tot 12 of the Loan Details worksheet: How much in tatal interest wag paid during the life of aption 12