Question

Solve using excel:

A. Given the following information, calculate the balloon payment for a partially amortized mortgage. Loan amount: $84,000, Term to maturity: 7 years, Amortization Term: 30 years, Interest rate: 4.5%, Monthly Payment: $425.62. 


B. Given the following information about a fully amortizing loan, calculate the lender's yield (rounded to the nearest tenth of a percent). Loan amount: $166,950, Term: 30 years, Interest rate: 8 %, Monthly Payment: $1,225.00, Discount points: 2.

C. Given the following information, calculate the effective borrowing cost (rounded to the nearest tenth of a percent). Loan amount: $166,950, Term: 30 years, Interest rate: 8 %, Monthly Payment: $1,225.00, Discount points: 2, Other Closing Expenses: $3,611. 


Answer 1

A). PV = -84,000; N (number of payments pending) = 7*12 = 84; PMT (monthly payment) = 425.62; rate (monthly rate) = 4.5%/12 = 0.375%, solve for FV

Balloon payment = 73,101.65

B). Principal amount = loan*(1 - discount points) = 166,950*(1-0.02) = 163,611

PV = -163,611; PMT (monthly payment) = 1,225; FV = 0; N (number pf payments) = 30*12 = 360, solve for RATE.

Monthly rate = 0.68% so annual yield for the lender is 0.68%*12 = 8.21%

C). Principal amount = 166,950*(1-0.02) = 163,611

PV = principal - closing expenses = 163,611 - 3,611 = 160,000

PV = -160,000; PMT = 1,225; FV = 0; N = 30*12 = 260, solve for RATE.

Monthly rate = 0.704% so effective borrowing cost = 0.704%*12 = 8.45%